MySQL 优化方案

一、MySQL 查询过程

MySQL 查询过程是指从客户端发送 SQL 语句到 MySQL 服务器，再到服务器返回结果集的整个过程。这个过程涉及多个组件的协作，包括连接管理、查询解析、优化、执行和结果返回等。
在这里插入图片描述

1.1 查询过程的关键组件
  1. 连接管理器：管理客户端连接。
  2. 解析器：解析 SQL 语句。
  3. 优化器：生成执行计划。
  4. 执行引擎：执行查询。
  5. 存储引擎：存储和检索数据。
1.2 查询过程的详细步骤

  1. 客户端发送查询请求

    • 客户端（如应用程序、命令行工具）通过 MySQL 协议（如 TCP/IP）向 MySQL 服务器发送 SQL 查询请求。
    • 请求内容可以是 SELECT、INSERT、UPDATE、DELETE 等 SQL 语句。

  2. 连接管理

    • MySQL 服务器接收到请求后，首先由 连接管理器 处理。
    • 连接管理器负责：
      • 验证客户端的用户名和密码。
      • 检查客户端的权限（是否有权限执行该查询）。
      • 分配一个线程来处理该请求（MySQL 是多线程架构，每个连接由一个线程处理）。

  3. 查询缓存（MySQL 8.0 之前）

    • 在 MySQL 8.0 之前，服务器会检查查询缓存（Query Cache）。
      • 如果查询缓存中已经存在该查询的结果，则直接返回缓存结果。
      • 如果查询缓存未命中，则继续后续步骤。
    • 在MySQL 8.0 移除了查询缓存功能，因为在高并发场景下，查询缓存可能成为性能瓶颈。

  4. 查询解析

    • 解析器（Parser） 对 SQL 语句进行词法分析和语法分析。
      • 词法分析：将 SQL 语句拆分为关键字、表名、列名、操作符等 token。
      • 语法分析：检查 SQL 语句是否符合 MySQL 的语法规则。
    • 如果 SQL 语句有语法错误，解析器会返回错误信息。

  5. 查询优化

    • 查询优化器（Optimizer） 对 SQL 语句进行优化，生成一个高效的执行计划。
      • 优化器会考虑以下因素：
        • 使用哪些索引。
        • 表的连接顺序（JOIN 的顺序）。
        • 是否可以使用覆盖索引。
        • 是否可以使用索引合并（Index Merge）。
      • 优化器会生成多个可能的执行计划，并选择成本最低的一个。
    • 可以通过 EXPLAIN 命令查看优化器生成的执行计划。

  6. 查询执行

    • 执行引擎（Execution Engine） 根据优化器生成的执行计划，调用存储引擎的接口执行查询。
      • 执行引擎负责：
        • 打开表。
        • 读取数据（通过索引或全表扫描）。
        • 执行排序、分组、聚合等操作。
        • 处理 JOIN 操作。
      • 执行引擎与存储引擎（如 InnoDB、MyISAM）交互，获取数据。

  7. 存储引擎处理

    • 存储引擎（Storage Engine） 负责数据的存储和检索。
      • 存储引擎根据执行引擎的请求，从磁盘或内存中读取数据。
      • 存储引擎会将数据返回给执行引擎。

  8. 结果返回

    • 执行引擎将处理后的数据返回给客户端。
      • 如果查询涉及排序、分组或聚合，执行引擎会在返回结果前完成这些操作。
      • 结果集通过 MySQL 协议发送给客户端。
    • 客户端接收到结果后，可以继续处理数据（如显示、存储或进一步计算）。

  9. 日志记录

    • MySQL 会根据配置记录相关日志：
      • 二进制日志（Binlog）：记录所有修改数据的操作（如 INSERT、UPDATE、DELETE），用于主从复制和数据恢复。
      • 慢查询日志（Slow Query Log）：记录执行时间超过阈值的查询，用于性能分析。
      • 通用日志（General Log）：记录所有查询请求，用于调试。

  10. 连接关闭

    • 查询完成后，客户端可以选择关闭连接或继续发送新的查询请求。
    • 如果连接空闲时间超过 wait_timeout，MySQL 会自动关闭连接以释放资源。

二、SQL 优化方案

2.1 索引优化

索引是提高查询性能的核心手段，但需要合理使用。

  1. 创建合适的索引
    • 单列索引：对经常用于 WHERE、JOIN、ORDER BY 和 GROUP BY 的列创建索引。
      CREATE INDEX idx_name ON table_name(column_name);
    • 复合索引：对多个列的组合查询创建复合索引。
      CREATE INDEX idx_name ON table_name(column1, column2);
    • 前缀索引：对文本列的前缀创建索引，减少索引大小。
      CREATE INDEX idx_name ON table_name(column_name(10));
  2. 避免过度索引
    • 索引会增加写操作（INSERT、UPDATE、DELETE）的开销，因此不要为不常用的列创建索引。
    • 删除未使用或重复的索引。
      DROP INDEX idx_name ON table_name;
  3. 使用覆盖索引
    • 如果查询只需要从索引中获取数据，而不需要回表查询数据行，可以显著提升性能。
      SELECT column1, column2 FROM table_name WHERE column1 = 'value';
-- 确保 (column1, column2) 上有索引
  4. 避免索引失效
    1. 避免在索引列上使用函数或表达式
      • 示例：
        -- 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;
      • 原因：MySQL 无法对 YEAR(created_at) 使用索引，因为它需要对每一行的 created_at 进行计算。
      • 优化方法：
        -- 优化后
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
    2. 避免在索引列上使用 NOT、!= 或 <>
      • 示例：
        -- 索引失效
SELECT * FROM users WHERE status != 'active';
      • 原因：NOT、!= 或 <> 需要扫描所有不等于条件的值，无法有效利用索引。
      • 优化方法：尽量避免使用 !=，尝试改写查询逻辑。
    3. 避免在索引列上使用 OR 条件
      • 示例：
        -- 索引失效
SELECT * FROM users WHERE age = 25 OR name = 'John';
      • 原因：如果 name 列没有索引，MySQL 无法使用 age 列的索引。
      • 优化方法：
        -- 优化后
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE name = 'John';
    4. 避免在索引列上使用 LIKE 以通配符开头
      • 示例：
        -- 索引失效
SELECT * FROM users WHERE name LIKE '%John%';
      • 原因：当通配符%出现在查询字符串的开头时，MySQL无法利用索引的前缀部分来加速查询。
      • 优化方法：
        • 尽量避免以 % 开头的模糊查询。
        • 如果必须使用，考虑全文索引（FULLTEXT）或其他搜索引擎（如 Elasticsearch）。
    5. 避免数据类型不匹配
      • 示例：
        -- 索引失效
SELECT * FROM users WHERE phone = 123456789;
      • 原因：如果 phone 列是字符串类型，而查询条件是数字类型，MySQL 会对索引字段进行隐式类型转换，导致索引失效。
      • 优化方法：
        -- 优化后
SELECT * FROM users WHERE phone = '123456789';
    6. 避免复合索引未遵循最左前缀原则
      • 示例：
        -- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);-- 索引失效
SELECT * FROM users WHERE age = 25;
      • 原因：没有遵循最左前缀原则，MySQL 无法利用复合索引的有序性，从而导致索引失效。
      • 优化方法：确保查询条件包含复合索引的最左列。
        -- 优化后
SELECT * FROM users WHERE name = 'John' AND age = 25;
    7. 避免在低区分度的字段上建索引
      • 示例：
        -- 索引失效
SELECT * FROM users WHERE sex = '男';
      • 原因：在低区分度的字段上，索引树中每个键值对应的数据行数非常多。查询时，MySQL 需要扫描大量数据行，索引的效果几乎等同于全表扫描。
      • 优化方法：尽量避免对低选择性的列创建索引。
2.2 查询重构

优化查询语句本身可以显著提升性能。

  1. 避免 SELECT *

    • 只选择需要的列，减少数据传输和内存占用。
      -- 不推荐
SELECT * FROM table_name;
-- 推荐
SELECT column1, column2 FROM table_name;

  2. 使用 LIMIT 时避免高偏移量

    • 当 OFFSET 值很大时，MySQL 需要扫描大量数据才能找到起始位置，导致性能下降。
      -- 不推荐
SELECT * FROM table_name LIMIT 10 OFFSET 100000;
-- 推荐
SELECT * FROM table_name WHERE id > 100000 ORDER BY id LIMIT 10;

  3. 避免子查询

    • 子查询的内部执行计划是先执行子查询再执行外层查询，由于每次执行子查询都需要创建并删除临时表，会消耗大量资源，从而影响数据库性能。
      -- 不推荐
SELECT * FROM table_name WHERE column1 IN (SELECT column1 FROM table2);
-- 推荐
SELECT t1.* FROM table_name t1 JOIN table2 t2 ON t1.column1 = t2.column1;

  4. JOIN 查询优化

    • 确保 JOIN 列上有索引：JOIN 条件中的列（通常是外键列）必须有索引。
    • 小表驱动大表：MySQL 通常会选择较小的表作为驱动表，以减少扫描的行数。
    • 过滤数据：在 JOIN 之前，使用 WHERE 条件减少参与 JOIN 的数据量。
      SELECT * FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t1.column = 'value';

  5. 合理使用 IN 和 EXISTS

    • IN
      • 用法：用于判断某个值是否在子查询返回的结果集中。
      • 适用场景：当子查询返回的结果集较小时，IN 的性能较好。
      • 执行过程：
        1. 执行子查询，获取结果集。
        2. 将结果集加载到内存中。
        3. 对外部查询的每一行，检查是否在结果集中。
    • EXISTS
      • 用法：用于判断子查询是否返回任何行。如果子查询返回至少一行，EXISTS 返回 TRUE，否则返回 FALSE。
      • 适用场景：当子查询返回的结果集较大时，EXISTS 的性能较好。
      • 执行过程：
        • 对外部查询的每一行，执行子查询。
        • 如果子查询返回至少一行，则返回 TRUE。

  6. 使用 EXPLAIN 分析查询

    • 使用 EXPLAIN 查看查询执行计划，找出性能瓶颈。
    • 关注 type（访问类型）、key（使用的索引）、rows（扫描的行数）等字段。
      EXPLAIN SELECT * FROM table_name WHERE column1 = 'value';
2.3 表结构优化
  1. 选择合适的数据类型
    • 使用最小的数据类型存储数据，例如：
      • 使用 INT 而不是 BIGINT。
      • 使用 VARCHAR 而不是 TEXT。
    • 避免使用 NULL，尽量使用默认值。
  2. 规范化与反规范化
    • 规范化：减少数据冗余，提高数据一致性。
    • 反规范化：在查询频繁的场景下，适当冗余数据以减少 JOIN 操作。
  3. 分区表
    • 对大表进行分区，提升查询性能。
      CREATE TABLE table_name (id INT,created_at DATE
) PARTITION BY RANGE (YEAR(created_at)) (PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022)
);
2.4 配置优化
  1. 调整缓冲区大小
    • 增加 innodb_buffer_pool_size，使其足够容纳常用数据。
      SET GLOBAL innodb_buffer_pool_size = 1G;
  2. 调整查询缓存
    • 在 MySQL 8.0 之前，可以启用查询缓存（适用于读多写少的场景）。
      SET GLOBAL query_cache_size = 64M;
  3. 调整连接数
    • 增加最大连接数，避免连接耗尽。
      SET GLOBAL max_connections = 500;
  4. 调整日志配置
    • 关闭不必要的日志（如慢查询日志、二进制日志）以减少 I/O 开销。
      SET GLOBAL slow_query_log = OFF;
2.5 其他优化技巧
  1. 批量操作
    • 使用批量插入或更新，减少单条语句的开销。
      INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2'), ('value3', 'value4');
  2. 使用缓存
    • 使用 Redis 或 Memcached 缓存热点数据，减少数据库压力。
  3. 分库分表
    • 对于超大规模数据，考虑分库分表（如使用 ShardingSphere 或 MyCat）。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/34442.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

服务性能防腐体系：基于自动化压测的熔断机制

服务性能防腐体系：基于自动化压测的熔断机制

01# 背景 在系统架构的演进过程中&#xff0c;项目初始阶段都会通过压力测试构建安全护城河&#xff0c;此时的服务性能与资源水位保持着黄金比例关系。然而在业务高速发展时期&#xff0c;每个冲刺周期都被切割成以业务需求为单位的开发单元&#xff0c;压力测试逐渐从必选项…
阅读更多...
六十天前端强化训练之第二十天React Router 基础详解

六十天前端强化训练之第二十天React Router 基础详解

欢迎来到编程星辰海的博客讲解 看完可以给一个免费的三连吗&#xff0c;谢谢大佬&#xff01; 目录 一、核心概念 1.1 核心组件 1.2 路由模式对比 二、核心代码示例 2.1 基础路由配置 2.2 动态路由示例 2.3 嵌套路由实现 2.4 完整示例代码 三、关键功能实现效果 四、…
阅读更多...
grad_traj_optimization 开源项目

grad_traj_optimization 开源项目

开源项目 grad_traj_optimization 使用教程-CSDN博客 ubuntu如何切换到root用户_ubuntu切换到root用户-CSDN博客 catkin_make: command not found 解决办法_catkin-make not found-CSDN博客 这就说明需要编译的package虽然存在&#xff0c;但不在指定的目录下。catkin_make命…
阅读更多...
深圳南柯电子｜净水器EMC测试整改：水质安全与电磁兼容性的双赢

深圳南柯电子｜净水器EMC测试整改：水质安全与电磁兼容性的双赢

在当今注重健康生活的时代&#xff0c;净水器作为家庭用水安全的第一道防线&#xff0c;其性能与安全性备受关注。其中&#xff0c;电磁兼容性&#xff08;EMC&#xff09;测试是净水器产品上市前不可或缺的一环&#xff0c;它直接关系到产品在复杂电磁环境中的稳定运行及不对其…
阅读更多...
要登录的设备ip未知时的处理方法

要登录的设备ip未知时的处理方法

目录 1 应用场景... 1 2 解决方法&#xff1a;... 1 2.1 wireshark设置... 1 2.2 获取网口mac地址&#xff0c;wireshark抓包前预过滤掉自身mac地址的影响。... 2 2.3 pc网口和设备对接... 3 2.3.1 情况1&#xff1a;... 3 2.3.2 情…
阅读更多...
GHCTF web方向题解

GHCTF web方向题解

upload?SSTI! import os import refrom flask import Flask, request, jsonify,render_template_string,send_from_directory, abort,redirect from werkzeug.utils import secure_filename import os from werkzeug.utils import secure_filenameapp Flask(__name__)# 配置…
阅读更多...
Vision Transformer (ViT)：将Transformer带入计算机视觉的革命性尝试（代码实现）

Vision Transformer (ViT)：将Transformer带入计算机视觉的革命性尝试（代码实现）

Vision Transformer (ViT)&#xff1a;将Transformer带入计算机视觉的革命性尝试 作为一名深度学习研究者&#xff0c;如果你对自然语言处理&#xff08;NLP&#xff09;领域的Transformer架构了如指掌&#xff0c;那么你一定不会对它在序列建模中的强大能力感到陌生。然而&am…
阅读更多...
蓝耘携手通义万象 2.1 图生视频：开启创意无限的共享新时代

蓝耘携手通义万象 2.1 图生视频：开启创意无限的共享新时代

在科技飞速发展的今天&#xff0c;各种新奇的技术不断涌现&#xff0c;改变着我们的生活和工作方式。蓝耘和通义万象 2.1 图生视频就是其中两项非常厉害的技术。蓝耘就像是一个超级大管家&#xff0c;能把各种资源管理得井井有条&#xff1b;而通义万象 2.1 图生视频则像是一个…
阅读更多...
IEC61850标准下MMS 缓存报告控制块 ResvTms详细解析

IEC61850标准下MMS 缓存报告控制块 ResvTms详细解析

IEC61850标准是电力系统自动化领域唯一的全球通用标准。IEC61850通过标准的实现&#xff0c;使得智能变电站的工程实施变得规范、统一和透明&#xff0c;这大大提高了变电站自动化系统的技术水平和安全稳定运行水平。 在 IEC61850 标准体系中&#xff0c;ResvTms&#xff08;r…
阅读更多...
【DeepSeek应用】DeepSeek模型本地化部署方案及Python实现

【DeepSeek应用】DeepSeek模型本地化部署方案及Python实现

DeepSeek实在是太火了，虽然经过扩容和调整，但反应依旧不稳定，甚至小圆圈转半天最后却提示“服务器繁忙，请稍后再试。” 故此，本文通过讲解在本地部署 DeepSeek并配合python代码实现，让你零成本搭建自己的AI助理，无惧任务提交失败的压力。 一、环境准备 1. 安装依赖库 …
阅读更多...
蓝思科技冲刺港股上市，双重上市的意欲何为？

蓝思科技冲刺港股上市，双重上市的意欲何为？

首先&#xff0c;蓝思科技冲刺港股上市&#xff0c;这一举措是其国际化战略进入实质性阶段的重要标志。通过港股上市&#xff0c;蓝思科技有望进一步拓宽融资渠道&#xff0c;这不仅能够为公司带来更加多元化的资金来源&#xff0c;还能够降低对单一市场的依赖风险&#xff0c;…
阅读更多...
深入探讨RAID 5的性能与容错能力：实验与分析(磁盘阵列)

深入探讨RAID 5的性能与容错能力：实验与分析(磁盘阵列)

前言—— 本实验旨在探讨 RAID 5 的性能和容错能力。通过创建 RAID 5 阵列并进行一系列读写性能测试及故障模拟&#xff0c;我们将观察 RAID 5 在数据冗余和故障恢复方面的表现&#xff0c;以验证其在实际应用中的可靠性和效率。 首先说明&#xff1a;最少三块硬盘, 使用 4 块…
阅读更多...
excel中两个表格的合并

excel中两个表格的合并

使用函数&#xff1a; VLOOKUP函数 如果涉及在excel中两个工作表之间进行配对合并&#xff0c;则&#xff1a; VLOOKUP(C1,工作表名字!A:B,2,0) 参考&#xff1a; excel表格中vlookup函数的使用方法步骤https://haokan.baidu.com/v?pdwisenatural&vid132733503560775…
阅读更多...
基于ssm的宠物医院信息管理系统(全套)

基于ssm的宠物医院信息管理系统(全套)

一、系统架构 前端&#xff1a;html | layui | vue | element-ui 后端&#xff1a;spring | springmvc | mybatis 环境&#xff1a;jdk1.8 | mysql | maven | tomcat | idea | nodejs 二、代码及数据库 三、功能介绍 01. web端-首页1 02. web端-首页…
阅读更多...
UE小：UE5.5 PixelStreamingInfrastructure 使用时注意事项

UE小：UE5.5 PixelStreamingInfrastructure 使用时注意事项

1、鼠标默认显示 player.ts中的Config中添加HoveringMouse:true 然后运行typescript\package.json中的"build":npx webpack --config webpack.prod.js
阅读更多...
iOS底层原理系列01-iOS系统架构概览-从硬件到应用层

iOS底层原理系列01-iOS系统架构概览-从硬件到应用层

1. 系统层级结构 iOS系统架构采用分层设计模式&#xff0c;自底向上可分为五个主要层级&#xff0c;每层都有其特定的功能职责和技术组件。这种层级化结构不仅使系统更加模块化&#xff0c;同时也提供了清晰的技术抽象和隔离机制。 1.1 Darwin层&#xff1a;XNU内核、BSD、驱动…
阅读更多...
Ubuntu从源代码编译安装QT

Ubuntu从源代码编译安装QT

1. 下载源码 wget https://download.qt.io/official_releases/qt/5.15/5.15.2/single/qt-everywhere-src-5.15.2.tar.xz tar xf qt-everywhere-src-5.15.2.tar.xz cd qt-everywhere-src-5.15.22. 安装依赖库 sudo apt update sudo apt install build-essential libgl1-mesa-d…
阅读更多...
深度学习基础：线性代数本质4——矩阵乘法

深度学习基础：线性代数本质4——矩阵乘法

两个矩阵相乘的意义就是复合变换 1. 复合变换 例如先进行旋转变换再进行剪切变换&#xff0c;这很明显是两个变换&#xff0c;但是从总体上看可以看作是一个复合变换&#xff0c;是旋转和剪切作用的总和。 和其他线性变换一样&#xff0c;我们也可以通过记录变换后的 i j 来实…
阅读更多...
前端技巧第五期JavaScript函数

前端技巧第五期JavaScript函数

函数定义 1. 传统定义方式 // 1. 函数声明&#xff08;存在提升&#xff09;function sum(a, b) { return a b;} // 2. 函数表达式const multiply function(a, b) { return a * b;}; // 3. 构造函数&#xff08;不推荐&#xff09;const div new Function(a, b, return …
阅读更多...
钉钉（excel）能让表格中不是‘北京’的字符串自动加亮显示（方便查看）以及隔行填充严颜色是斑马色（方便查看）嘛

钉钉（excel）能让表格中不是‘北京’的字符串自动加亮显示（方便查看）以及隔行填充严颜色是斑马色（方便查看）嘛

在钉钉在线表格中&#xff0c;虽然功能相对Excel有所简化&#xff0c;但仍然可以通过条件格式实现对特定内容的高亮显示。以下是具体步骤&#xff1a; 一、在钉钉在线表格中设置条件格式 1. 打开钉钉在线表格 登录钉钉&#xff0c;进入“钉钉在线表格”应用。 打开你需要操作…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023