慢查询日志是起点
线上反馈“列表页很慢”，第一反应不应该是加索引，而是先找到具体是哪条 SQL 慢。MySQL 8.0 的慢查询日志默认关闭，long_query_time 默认 10 秒，很多几百毫秒的查询根本不会记录。排查前先把阈值调到能覆盖业务感知的范围：
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'OFF';
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
log_queries_not_using_indexes 在表很小的时候也会疯狂刷日志，生产环境谨慎打开。慢日志文件里记录了执行时间、扫描行数、返回行数、锁等待时间和 SQL 原文，先用 mysqldumpslow 按耗时排序，或者用 pt-query-digest 做聚合分析：
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
pt-query-digest /var/lib/mysql/slow.log > slow-report.txt
没有开慢日志时，performance_schema.events_statements_summary_by_digest 也能看到按 SQL 模板聚合的耗时和扫描行数，适合临时排查。
用 EXPLAIN 看“扫了多少行”
EXPLAIN 的核心不是看 type 是不是 ALL，而是看 rows 和 filtered 的乘积，以及 Extra 里有没有 Using filesort、Using temporary。MySQL 8.0.18 之后建议直接用 EXPLAIN ANALYZE，它会给出实际执行行数和耗时：
EXPLAIN ANALYZE
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = 42
AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;
如果执行计划里出现 Sort: orders.created_at DESC，说明排序没走索引；如果 actual rows 远大于最终返回的 20 行，说明索引没把过滤条件用上。联合索引的顺序要同时服务过滤和排序，例如 (user_id, status, created_at) 可以先用前两列过滤，再按第三列有序读取，避免额外排序。
常见慢查询模式
第一类是索引列上做运算或函数：WHERE DATE(created_at) = '2024-06-01'、WHERE id + 1 = 100，都会让索引失效。改成范围查询 created_at >= '2024-06-01' AND created_at < '2024-06-02'。
第二类是隐式类型转换。手机号字段是 VARCHAR，查询写成 WHERE phone = 13800000000，MySQL 会把列转成数字，索引直接失效。参数字符串就传字符串，别依赖数据库做转换。
第三类是以通配符开头的模糊查询 LIKE '%keyword%'。B+ 树索引只能做前缀匹配，前后都带 % 只能全表扫描。数据量大时应该交给 Elasticsearch，而不是在 MySQL 里硬扛。
第四类是多表 JOIN 没有索引。被驱动表的关联字段没有索引，驱动表每返回一行就要全表扫一次，扫描量会按数量级放大。用 EXPLAIN 看被驱动表的 type 和 rows，比看总耗时更快定位。
优化顺序
先确认是单条 SQL 慢，还是数据库整体负载高。单条慢，优先看索引和 SQL 写法；整体慢，要看连接数、锁等待、磁盘 IO 和 Buffer Pool 命中率。顺序反了，容易在错误的层面调参数。
索引也不是越多越好。每个索引都会增加写入成本和磁盘占用，低基数的列（比如性别、状态只有几种值）单独建索引意义不大。联合索引要遵循最左前缀，把等值条件放前面、范围条件放后面，排序字段尽量接在过滤字段之后。能覆盖查询的索引（把 SELECT 的列也放进索引）可以避免回表，但会让索引变大，要权衡。
改写 SQL 时优先减少扫描行数，而不是减少返回行数。SELECT * 会多读不需要的列，大字段还会撑爆临时表；只取需要的列，能让覆盖索引生效。
排查步骤
先看当前有哪些长事务、长查询：
SELECT id, user, time, state, info
FROM information_schema.PROCESSLIST
WHERE command <> 'Sleep'
ORDER BY time DESC;
再看 InnoDB 的锁和事务状态：
SHOW ENGINE INNODB STATUS;
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX;
如果 SQL 本身不慢，但接口慢，要怀疑锁等待：事务持有了行锁迟迟不提交，后面的查询全在等。performance_schema.data_lock_waits 能看到阻塞关系。最后再确认索引是否真的被使用：
SHOW INDEX FROM orders;
ANALYZE TABLE orders;
统计信息过期会让优化器选错索引，ANALYZE TABLE 能刷新基数；但不要频繁执行，大表上会有额外开销。
常见坑
1. 只加索引，不看区分度
在 status 这种只有几个值的列上建单列索引，优化器很可能直接放弃，改成全表扫描。联合索引把高区分度列放前面，效果更稳定。
2. 以为 EXPLAIN 的 rows 是精确值
rows 是优化器的估算值，可能和实际差几个数量级。判断真实扫描量要用 EXPLAIN ANALYZE，或者对比 Handler_read_next 等状态变量的增量。
3. 在索引列上使用函数或表达式
除了 DATE()，SUBSTR()、IFNULL()、CAST() 也会导致索引失效。把运算移到参数侧，或者用 MySQL 8.0 的函数索引，但函数索引的维护成本要提前评估。
4. 忽略排序和分页的成本
查询只返回 20 行，但排序可能要对几十万行做 filesort。ORDER BY 的字段没有进入索引，或者排序方向和索引不一致，都会触发额外排序。深分页的问题更明显，下一篇单独讲。
5. 用 FORCE INDEX 掩盖问题
强制索引能让某条 SQL 暂时变快，但数据分布变化后可能更慢。先分析统计信息和执行计划，实在需要兜底再加，并且留下注释和复查时间。
小结
慢查询优化有一条固定路径：慢日志定位 SQL → EXPLAIN ANALYZE 看扫描行数 → 判断是索引问题、写法问题还是数据量问题 → 用最小改动验证。不要一上来就调 Buffer Pool 或加从库，大多数慢查询的根因在索引和 SQL 本身。
评论