MySQL性能屠龙刀：EXPLAIN与慢查询日志深度排查及优化终极指南

开篇：一次线上事故引发的血案

凌晨三点，监控告警疯狂尖叫——数据库CPU飙升到95%，接口响应从50ms骤增至8秒，用户投诉如潮水般涌入。运维火速上线，发现是一条看似人畜无害的SQL，扫了一张千万级大表，磁盘IO打满，数据库近乎瘫痪……

这类“深夜惊魂”的元凶，十有八九是慢查询。如何揪出它们？如何看懂执行计划？如何从根源上彻底消灭性能瓶颈？本文将以慢查询日志为雷达，以EXPLAIN为手术刀，带你深入MySQL查询优化的微观世界，献上一份“高屋建瓴、拳拳到肉”的硬核指南。

一、开启上帝视角：慢查询日志

找不到慢SQL，优化就无从谈起。慢查询日志是MySQL自带的第一道追踪器。

1.1 核心参数配置

sql

-- 开启慢查询日志 SET GLOBAL slow_query_log = ON; -- 设定慢查询时间阈值（单位：秒），超过此时间即被记录。生产建议设0.1~0.5秒 SET GLOBAL long_query_time = 0.2; -- 记录未使用索引的查询（记录后分析，不一定都要优化，但可用来发现遗漏） SET GLOBAL log_queries_not_using_indexes = ON; -- 将慢日志输出到表 mysql.slow_log，方便SQL查询分析 SET GLOBAL log_output = 'TABLE';

写入配置文件my.cnf永久生效：

text

slow_query_log = 1 long_query_time = 0.2 log_queries_not_using_indexes = 1 log_output = TABLE

1.2 慢日志分析利器

直接查询系统表就能获取最新慢查询：

sql

SELECT start_time, query_time, lock_time, rows_sent, rows_examined, sql_text FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

但更专业的分析，推荐使用mysqldumpslow或 Percona 的pt-query-digest：

bash

# 统计出现次数最多、耗时最长的查询 mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log

pt-query-digest能生成带执行计划和建议的详细报告，是DBA的屠龙宝刀。

二、庖丁解牛：EXPLAIN 执行计划详解

抓到一条“嫌犯”SQL之后，立刻用EXPLAIN查看执行计划。它揭示了MySQL优化器将如何访问表、使用索引、连接顺序等关键信息。

sql

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 'paid' ORDER BY create_time DESC;

2.1 核心字段深度解读

🔸id：查询执行顺序

id相同按从上到下顺序执行；id不同，值越大越优先执行。嵌套子查询或UNION时常见不同id。

🔸select_type：查询类型

• SIMPLE：简单SELECT，不使用UNION或子查询

• PRIMARY：最外层查询

• SUBQUERY：SELECT中的子查询

• DERIVED：FROM中的子查询（衍生表）

• UNION：UNION中第二个及之后的SELECT

• UNION RESULT：UNION的结果集

🔸type：访问类型（性能从好到差）

这是判断SQL生死的核心依据！

• system：表仅一行，Const的特例

• const：通过主键或唯一索引等值匹配，最多返回一行

• eq_ref：关联查询时，驱动表每行匹配被驱动表唯一索引。性能极佳

• ref：非唯一索引等值查找，返回匹配的所有行。常见于普通索引列关联

• ref_or_null：类似ref，但额外搜索NULL值

• index_merge：使用了多个索引的合并扫描（MySQL智能选择多个索引取交集/并集）

• range：索引范围扫描，常见<、>、BETWEEN、IN、LIKE 'abc%'

• index：全索引扫描，扫描整个索引树（比ALL稍好，因为索引通常小于数据）

• ALL：全表扫描，灾难级性能，必须优化

口头禅记忆：system > const > eq_ref > ref > range > index > ALL。一般至少要求到range级别，最好到ref。

🔸possible_keys vs key

• possible_keys：可能使用的索引（候选）

• key：优化器实际选择的索引
  如果possible_keys非空而key为 NULL，说明优化器放弃索引（可能是索引选择性不高、类型不匹配等原因），值得警惕。

🔸key_len：使用的索引字节数

它帮你判断联合索引用了几个列。例如idx(user_id, status)，key_len=4 说明只用了user_id部分；如果等于两个列总长度，则充分使用。这是验证“最左前缀”的杀手锏。

🔸rows：估算需要扫描的行数

基于统计信息的估值，数值越小越好。与实际rows_examined比较可判断统计信息是否准确。

🔸filtered：满足查询条件的行数百分比

rows × filtered表示最终返回给上一层的行数估算。低 filtered 且高 rows 往往意味着索引筛选力度不足。

🔸Extra：额外关键信息

• Using index：覆盖索引，不回表，性能最优

• Using where：MySQL服务器层进行行过滤，说明部分条件没用到索引

• Using index condition：使用了索引下推（ICP），在存储引擎层过滤，减少回表

• Using temporary：使用了临时表（常见于GROUP BY、DISTINCT、ORDER BY不加索引），性能杀手

• Using filesort：无法利用索引排序，需额外排序操作，消耗CPU和内存

• Using join buffer：关联字段缺少索引，用了连接缓存，性能差

三、刀刀见血：从慢查询到完美执行计划的优化实战

场景还原

千万级订单表orders，核心查询：

sql

SELECT order_id, user_id, amount, status, create_time FROM orders WHERE user_id = 9527 AND status IN ('paid','shipped') ORDER BY create_time DESC LIMIT 20;

慢日志显示该查询平均耗时3.5秒，扫描行数800万+。

Step 1：EXPLAIN 诊断

sql

EXPLAIN SELECT ... \G

结果：

text

id: 1 select_type: SIMPLE table: orders type: ALL possible_keys: idx_user_id, idx_status key: NULL rows: 9850000 Extra: Using where; Using filesort

惨不忍睹：type=ALL全表扫描，Extra里Using filesort说明排序没用上索引。

Step 2：问题定位

当前索引：idx_user_id(user_id),idx_status(status)。但查询条件同时包含user_id和status，且需要按create_time排序，单列索引无法高效满足。

Step 3：创造完美的联合索引

根据最左前缀原则与排序优化，量身打造：

sql

ALTER TABLE orders ADD INDEX idx_usr_sts_ctime (user_id, status, create_time);

索引列顺序逻辑：

1. user_id等值查询在前，缩小扫描范围；

2. statusIN 条件，虽然范围查询会中断后续索引的排序利用，但我们通过索引条件下推 + 覆盖依然能大幅减少回表；

3. create_time放在最后尝试利用索引排序，但由于status IN是多个值，排序仍可能无法完全依赖索引。不过我们可以更进一步改写SQL或考虑覆盖索引。

为了追求极致，可以再观察SELECT列。如果我们创建覆盖索引包含所有返回列，甚至不用回表：

sql

ALTER TABLE orders ADD INDEX idx_full_cover (user_id, status, create_time, order_id, amount);

Step 4：再次EXPLAIN验证

text

type: range key: idx_full_cover rows: 1350 Extra: Using where; Using index

从ALL蜕变为range（IN算range），rows从千万降到千级，Using index表示完全覆盖索引，性能质变！实际执行时间从3500ms暴跌至8ms。

四、进阶核武器：现代MySQL的深层洞察

4.1 EXPLAIN FORMAT=JSON

传统的EXPLAIN信息有时不够精细，JSON格式提供代价评估、索引使用详情等：

sql

EXPLAIN FORMAT=JSON SELECT ... \G

输出片段：

json

"query_block": { "cost_info": { "query_cost": "1250.35" }, "table": { "access_type": "range", "key": "idx_full_cover", "used_key_parts": ["user_id", "status"], "rows_examined_per_scan": 1340, ... } }

通过query_cost优化器成本、used_key_parts能精确知道索引列的使用程度。

4.2 EXPLAIN ANALYZE（MySQL 8.0.18+）

真正执行SQL并输出实际执行时间与行数，打破“估算”的幻想：

sql

EXPLAIN ANALYZE SELECT ...

结果示例：

text

-> Limit: 20 row(s) (actual time=8.212..8.220 rows=20 loops=1) -> Sort: orders.create_time DESC (actual time=8.210..8.213 rows=20 loops=1) -> Index range scan on orders using idx_full_cover (actual time=0.035..7.890 rows=1320 loops=1)

actual time告诉我们每一步真实耗时，任何隐藏的性能问题都无所遁形。

4.3 Optimizer Trace 追踪决策过程

为什么优化器选择了这个索引而不是那个？它能告诉你：

sql

SET optimizer_trace='enabled=on'; SELECT ... ; -- 你的SQL SELECT * FROM information_schema.OPTIMIZER_TRACE \G

输出会展示 “range_analysis”、“considered_execution_plans” 等决策树，当遇到“索引选错”疑难杂症时，它就是最后一张王牌。

五、建立长效治理机制：慢查询的持续排查与防护

单次优化只是灭火，我们更需要防火体系：

1. 定时采集慢日志：配置slow_query_log_file并结合pt-query-digest生成日报，发送到监控平台。

2. SQL审核工单：上线前使用EXPLAIN强制审查，设定扫描行数、type等级红线。

3. 索引治理：定期分析无用的冗余索引、缺失索引，利用sys.schema_unused_indexes和sys.schema_redundant_indexes。

4. 低峰期模拟压测：用EXPLAIN ANALYZE在预发环境进行真实负载回放。

5. 参数调优：通过join_buffer_size、sort_buffer_size等辅助优化，但要主靠索引治本。

结语

慢查询SQL的排查与优化，并非一场遭遇战，而是一场持久战。EXPLAIN就像X光机，让隐藏的执行轨迹暴露无遗；慢查询日志则是哨兵，第一时间报告异常。掌握这两大利器，配合现代MySQL的EXPLAIN ANALYZE和Optimizer Trace，你便拥有了性能调优的上帝视角。

下一次凌晨告警再响起时，你将从容不迫地打开终端，在几分钟之内让系统回归丝滑——这，就是硬核工程师的底气。