MySQL 慢查询排查指南：EXPLAIN 执行计划拆解与索引失效避坑实战

数据库慢查询是拖垮业务接口响应速度、耗尽连接池资源的核心杀手。多数性能问题并非源于硬件瓶颈，而是因为索引设计缺陷或 SQL 书写不当触发了全表扫描。

本文系统梳理从慢日志定位、EXPLAIN 执行计划分析，到常见索引失效场景与优化方案的完整排查链路。

1. 慢查询快速捕获与定位

1.1 开启与配置慢查询日志

在线上遇到偶发性卡顿时，首先检查 MySQL 慢日志是否启用：

-- 查看慢查询日志状态与阈值
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时动态开启（需管理员权限，重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录执行时间超过 1 秒的 SQL

1.2 日志分析工具

对于海量日志文件，建议使用自带的 mysqldumpslow 进行聚合统计：

# 获取访问次数最多的前 10 条慢 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 获取平均执行耗时最长的前 10 条慢 SQL
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

2. EXPLAIN 执行计划深度拆解

拿到慢 SQL 后，在前面加上 EXPLAIN 查看执行计划，重点关注以下核心字段：

EXPLAIN SELECT * FROM orders WHERE user_id = 10024 AND status = 1;

2.1 type（访问类型性能排序）

性能从最优到最差依次为：
system > const > eq_ref > ref > range > index > ALL

• const / eq_ref：主键或唯一索引等值匹配，极快。
• ref：非唯一普通索引等值扫描。
• range：索引范围扫描（BETWEEN、<、>、IN）。
• index：全索引树扫描，通常只比全表快一点。
• ALL：全表扫描，生产环境大表必须消除。

2.2 Extra（关键附加信息）

• Using index：命中覆盖索引，无需回表查询聚簇索引，性能极佳。
• Using filesort：排序无法利用索引，在内存或磁盘进行了外部排序，需重点优化 ORDER BY。
• Using temporary：使用了临时表存放中间结果（常见于 GROUP BY 或 DISTINCT），消耗内存与 CPU。

3. 经典索引失效反例与修复

假设表建立联合索引：idx_user_status_created (user_id, status, created_at)

3.1 违背最左前缀原则

• 失效：WHERE status = 1（跳过了 user_id）
• 原因：B+Tree 是按联合索引由左至右逐级排序的，前导列缺失无法定位叶子节点。
• 修复：遵循最左匹配，或根据业务查询频次补充独立索引。

3.2 索引列上进行函数计算或表达式计算

• 失效：WHERE DATE(created_at) = '2026-04-01'
• 失效：WHERE user_id + 1 = 10025
• 修复：将计算逻辑移至等号右侧常数端：

  WHERE created_at >= '2026-04-01 00:00:00' 
    AND created_at < '2026-04-02 00:00:00'
  

3.3 隐式类型转换

• 失效：phone 字段为 VARCHAR 类型，但查询传入数字：WHERE phone = 13800000000
• 原因：MySQL 会隐式调用 CAST(phone AS SIGNED)，相当于在索引列上加函数，导致全表扫描。
• 修复：显式匹配字段数据类型，参数务必加引号：WHERE phone = '13800000000'。

3.4 头部模糊匹配

• 失效：WHERE nickname LIKE '%test%' 或 WHERE nickname LIKE '%test'
• 生效：WHERE nickname LIKE 'test%'（前缀已知，可利用索引范围检索）。

4. 大分页（Deep Pagination）性能优化

当数据量达到千万级时，LIMIT 1000000, 20 会扫描前 1000020 条记录再丢弃前 100 万条，产生大量无意义的回表操作。

优化方案：延迟关联（Deferred Join）

先利用覆盖索引获取目标主键，再与原表内联关联获取完整字段：

-- 原低效语句
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 优化后语句：利用聚簇索引延迟回表
SELECT o.* 
FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) tmp ON o.id = tmp.id;

5. 慢 SQL 排查 CheckList

• [ ] type 字段是否达到 ref 或 range 级别，杜绝大表 ALL。
• [ ] 联合索引是否严格遵循最左匹配，避免范围查询阻断后续字段索引命中。
• [ ] Extra 是否出现 Using filesort 或 Using temporary，针对排序列构建联合索引。
• [ ] 核心查询是否可改写为覆盖索引（Covering Index）避免回表。
• [ ] 分页接口是否限制了最大页码深度，或改用“滚动游标”（基于上页最后一条 ID）代替物理分页。