MySQL 慢查询分析与 EXPLAIN 执行计划优化详解

开启与配置慢查询日志

排查 SQL 性能问题的首要步骤是获取执行缓慢的 SQL 语句。MySQL 提供了慢查询日志功能，但默认情况下该功能可能未启用或配置宽松。我们需要按照以下步骤进行开启和参数调整。

第1步：检查慢查询状态

首先确认当前是否开启了慢查询日志功能。执行以下命令查看 log_slow_queries 变量的值：

SHOW VARIABLES LIKE 'log_slow_queries';

如果结果为 ON，表示已开启；若为 OFF，则需手动开启。

第2步：开启慢查询日志

使用全局变量设置开启慢查询日志。注意，此操作在运行时生效，无需重启服务：

SET GLOBAL log_slow_queries = ON;

第3步：设置慢查询时间阈值

Mysql 默认将超过 10 秒的查询记录为慢查询，这对于现代高并发应用来说容忍度过高。通常建议将其调整为 1 秒或更低，以便尽早发现问题。

查看当前阈值的命令：

SHOW GLOBAL VARIABLES LIKE 'long_query_time';

将阈值设置为 1 秒的命令：

SET GLOBAL long_query_time = 1;

设置后再次执行查看命令，确认值已变为 1.000000。

第4步：查看慢查询日志文件位置

了解日志存储路径有助于后续的文件分析：

SHOW VARIABLES LIKE 'slow_query_log_file';

到达指定目录下即可查看慢 SQL 的具体内容。

解析 EXPLAIN 执行计划

获取到慢 SQL 后，最直接的分析手段是使用 EXPLAIN 命令（或其别名 DESCRIBE）来获取 SQL 的执行计划。

EXPLAIN 提供了关于 SQL 语句如何被 MySQL 引擎执行的关键信息，主要包括以下维度：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用或被实际使用
• 表之间的引用关系
• 每张表有多少行被优化器预估参与查询

id: SELECT 标识

id 代表 SELECT 查询的序列号，反映了执行顺序：

• id 相同：执行顺序由上到下。
• id 不同：如果是子查询，id 值越大，优先级越高，越先被执行（通常是 FROM 子句中的派生表）。
• id 既同又不同：若存在多组相同的 id，同一组内从上到下执行；在组间比较时，id 值大的组优先执行。

select_type: 查询类型

常见的查询类型及其含义：

• SIMPLE：简单的 SELECT 查询，不包含 UNION 或子查询。
• PRIMARY：查询中包含任何复杂的子部分时，最外层的 SELECT 标记为该值。
• SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询。
• DERIVED：FROM 列表中包含的子查询被标记为此值，MySQL 会递归执行并将结果放入临时表。
• UNION：如果第二个 SELECT 出现在 UNION 之后，会被标记为此值。若 UNION 包含在 FROM 子句中，外层标记为 DERIVED。
• UNION RESULT：从 UNION 表中获取结果的 SELECT。

table: 表名

显示当前行正在访问哪张表。

type: 访问类型（重点关注）

type 显示了指明 MySQL 如何查找表中的行，评估查询优劣的核心指标。从最好到最差依次为：

1. system：表只有一行记录（等于系统表），是 const 的特例。
2. const：表最多只有一行匹配的记录，一次索引查到结果（如主键或唯一索引查询）。
3. eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录匹配。常见于主键或唯一索引关联。
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行。属于查找和扫描的混合体。
5. range：只检索给定范围的行，使用一个索引来检索行（常见于 BETWEEN、>、<、IN 等操作）。
6. index：全索引扫描，遍历索引树。比 ALL 快，因为索引文件通常小于数据文件，但依然涉及大量 I/O。
7. ALL：全表扫描，性能最差，应尽量避免。

目标：保证查询至少达到 range 级别，最好能达到 ref 或更优。

possible_keys: 可能使用的索引

显示 MySQL 认为在此查询中可能使用的索引。注意，这里列出的索引不一定被实际使用。

key: 实际使用的索引

显示 MySQL 实际上使用的索引名称。如果为 NULL，表示未使用索引。若使用了覆盖索引，此处也会显示对应的索引名。

key_len: 索引长度

表示索引中所使用的字节数。在不损失精确性的情况下，长度越短越好。该值是根据表结构定义计算得出的最大可能长度。

ref: 关联字段

显示索引的哪一列被使用了。如果是常数等值查询，显示 const；如果是连接查询，则显示关联的字段名。

rows: 预估扫描行数

根据统计信息估算找出所需记录需要读取的行数。该数值越小，通常意味着性能越好。

Extra: 额外信息（高危预警）

此字段包含 MySQL 解决查询的详细信息，对调优极具参考价值：

• Using filesort：表明 MySQL 需要使用外部排序（文件排序），无法利用索引完成排序。数据量大时极其危险，务必优化。
• Using temporary：使用了临时表保存中间结果，常见于 ORDER BY 或 GROUP BY 操作。“十死无生”，急需优化。
• Using index：表示使用了覆盖索引，直接通过索引获取数据，无需回表，效率很高。
• Using where：表明索引被用来执行索引键值的查找。

优先级参考：Using index > Using filesort > Using temporary。

SQL 优化核心技巧

结合慢查询分析与执行计划，以下是日常开发中提升 MySQL 性能的实用技巧：

1. 索引设计相关

• 避免全表扫描：在 WHERE 及 ORDER BY 涉及的列上建立合适的索引。
• 复合索引的最左前缀原则：如果索引是复合索引，必须使用索引的第一个字段作为条件，否则索引失效。尽量让查询字段顺序与索引定义顺序一致。
• 合理使用 EXISTS 替代 IN：在很多场景下，使用 EXISTS 代替 IN 能获得更好的查询性能。
• 关注索引基数：当索引列数据重复度极高（如性别字段）时，索引效果甚微。且索引并非越多越好，过多索引会降低 INSERT/UPDATE 的效率（因需重建索引），一般建议单表索引不超过 6 个。

2. 书写规范与禁忌

• 避免函数与表达式运算：不要在 WHERE 子句中对字段进行函数操作、算术运算或使用 !=/<>，这会导致引擎放弃索引走全表扫描。
• 慎用 OR 和 IN：避免使用 OR 连接条件；慎用 IN，能用 BETWEEN 尽量用 BETWEEN。模糊查询（尤其是左模糊 %xx）也会导致全表扫描。
• 避免 NULL 判断：不在 WHERE 子句中对字段进行 NULL 值判断。
• 不要 SELECT *：使用具体字段列表代替 *，减少不必要的网络传输和内存消耗。
• 字段类型选择：尽量使用数字型字段而非字符型处理纯数字数据；尽可能使用 VARCHAR 代替 CHAR 以节省空间和提升搜索效率。

3. 高级特性使用

• 控制临时表创建：避免频繁创建和删除临时表。一次性大数据量插入可考虑 SELECT INTO，小数据量先 CREATE TABLE 再 INSERT。
• 谨慎使用游标：游标效率较低，建议优先寻找基于集合（Set-based）的解决方案。若必须使用，小型数据集可使用 FAST_FORWARD 游标。
• 事务控制：尽量避免大事务操作，以提高系统的并发能力。
• 数据返回量控制：避免向客户端返回过大的数据量，必要时考虑业务逻辑拆分。