如何排查和优化 MySQL 慢查询

排查 MySQL 慢查询的标准路径是四步：先开慢查询日志把慢 SQL 捞出来，再用 EXPLAIN 看执行计划确认是否走索引，接着按「索引失效 → SQL 写法 → 表结构 → 架构层」的顺序逐级优化，最后用优化前后的扫描行数和响应时间对比验证。绝大多数慢查询（社区经验里 80% 以上）是索引缺失或索引失效导致的，真正需要动表结构或加缓存的只是少数。

第一步：怎么开启慢查询日志并捞出慢 SQL？

结论：MySQL 慢查询日志默认是关闭的，long_query_time 默认 10 秒，必须手动调小才能抓到问题 SQL。

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 生产环境建议 0.1~1 秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON;  -- 仅短时开启，否则日志暴涨

long_query_time 是「执行时间超过多少秒的语句被记录」的阈值，MySQL 8.0 支持到微秒精度。注意 SET GLOBAL 重启即失效，要持久化得写进 my.cnf 的 [mysqld] 段。日志出来后用两种工具分析：mysqldumpslow -s t -t 10 slow.log 按总耗时取前 10 条，或者用 Percona 的 pt-query-digest slow.log 输出更细的统计报告。如果线上不方便开日志，也可以用 SHOW FULL PROCESSLIST 抓正在跑的慢 SQL。

EXPLAIN 怎么看？重点盯哪几个字段？

结论：EXPLAIN 里最该看的是 type、key、rows、Extra 四个字段，其中 type=ALL 表示全表扫描，是必须优化的信号。

字段含义速查：

• type：访问类型，从好到坏依次是 system > const > eq_ref > ref > range > index > ALL。出现 ALL 或 index（全索引扫描）就要警惕。
• key：实际使用的索引。显示 NULL 说明没走索引；possible_keys 有值但 key 为 NULL，说明优化器评估后放弃了索引。
• rows：预估扫描行数。这个数越大越慢，是判断优化效果最直观的指标。
• Extra：Using index 是好消息（覆盖索引，不用回表）；Using filesort 表示需要额外排序；Using temporary 表示用了临时表，常见于 GROUP BY、DISTINCT、UNION。

MySQL 8.0.18 之后可以用 EXPLAIN ANALYZE SELECT ...，它会真实执行 SQL 并给出实际耗时和实际行数，比普通 EXPLAIN 的估算值可靠得多。

索引为什么没走？常见的索引失效场景有哪些？

结论：索引失效最常见的五个原因是隐式类型转换、列上套函数、前导通配符、违反最左前缀、OR 连接了非索引列。

1. 隐式类型转换：手机号字段是 varchar，却写 WHERE phone = 13800000000，MySQL 会把列转成数字，索引直接失效。必须写成 WHERE phone = '13800000000'。
2. 列上套函数：WHERE DATE(create_time) = '2024-01-01' 失效，应改成 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。
3. 前导通配符：LIKE '%abc' 无法用索引，LIKE 'abc%' 可以。
4. 违反最左前缀：联合索引 (a, b, c) 只能从 a 开始匹配，WHERE b = 1 用不上。
5. OR 混用：WHERE a = 1 OR b = 2，只要 b 没索引，整体就走全表扫描。

另外，如果某列区分度极低（比如性别、状态位只有两三个值），优化器可能判定走索引还不如全表扫描，这是正常行为，不用强行 FORCE INDEX。

SQL 本身怎么写才能更快？

结论：减少扫描行数和回表次数是 SQL 层优化的核心，具体手段是覆盖索引、避免 SELECT *、优化深分页。

• 用覆盖索引：把 SELECT 需要的列都放进索引里，让 Extra 出现 Using index，避免回表。例如经常执行 SELECT id, name FROM user WHERE age = 20，就建联合索引 (age, name)。
• 别写 SELECT *：多取的列会导致无法使用覆盖索引，还会增加网络传输。
• 深分页改写：LIMIT 1000000, 20 会先扫描 100 万行再丢弃。改成基于游标的分页 WHERE id > 1000000 LIMIT 20，或者用延迟关联 SELECT * FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 20) x USING(id)。
• 批量操作替代循环单条：1000 条 INSERT 合并成一条批量插入，性能差距通常在 10 倍以上。

优化完怎么验证效果？

结论：验证标准是 EXPLAIN 的 rows 显著下降且 type 提升到 range 及以上，配合压测的实际响应时间对比。

具体做法：优化前后各跑一次 EXPLAIN，记录 type 和 rows；再用 SHOW STATUS LIKE 'Handler_read%' 对比扫描行数；最后在业务低峰期用真实数据量压测，确认真实耗时下降且没有引入新问题。索引不是越多越好，每多一个索引，写入就要多维护一棵 B+ 树，更新频繁的表建议单表索引控制在 5 个以内。

总结一下：开日志抓 SQL → EXPLAIN 看 type/key/rows/Extra → 优先修索引失效 → 再改 SQL 写法 → 最后才考虑表结构和架构层拆分。按这个顺序走，绝大多数慢查询都能在一次迭代内解决。