EXPLAIN FORMAT=JSON SELECT id, user_id, amount FROM orders WHERE user_id = 1001 AND status = 'PAID' ORDER BY create_time DESC LIMIT 20;
做完执行计划分析，我通常会把视线转到线上真实发生的慢查询上。执行计划是显微镜，慢查询日志是雷达。两者配合，才能从海量 SQL 里捞出真正拖慢业务的那几条。这一章我按排查顺序，把开启日志、定位工具、高频场景、JOIN 分页、配置资源、锁与事务、治理闭环串一遍。里面不少坑是我亲身踩过的，写出来希望你能绕开。
2.1 慢查询日志：开启、阈值设置、采集与日志分析
我拿到一台新数据库，第一件事是确认慢查询日志有没有开。SHOW VARIABLES LIKE 'slow_query_log' 返回 OFF，我就去 my.cnf 里加上 slow_query_log = ON，slow_query_log_file = /var/log/mysql/slow.log，long_query_time = 0.5。阈值设多少看业务，接口 RT 要求 200ms，我就设 0.2 秒。日志量太大，磁盘吃不消，我会先设 1 秒，观察几天再往下调。log_queries_not_using_indexes 我一般不长期开，除非专门排查索引问题，否则日志里全是小查询，分析起来头疼。
有次大促前，我把阈值从 1 秒调到 0.3 秒，当天日志涨了 20 倍。用 pt-query-digest 一跑，发现 80% 的慢查询来自同一个报表 SQL，它每天凌晨跑一次，白天被缓存挡住，大促缓存失效后直接打 DB。我把这个 SQL 改成预计算表，慢查询数量立刻掉下来。采集日志我习惯用 log_output = FILE，再通过 Filebeat 送到 ELK。也有团队用 log_output = TABLE，写 mysql.slow_log，查起来方便，但高并发下表会锁，我不太推荐。
分析日志时，我关注几个维度：执行次数、总耗时、平均耗时、扫描行数、锁时间。mysqldumpslow -s t -t 10 能快速看总耗时 Top 10。pt-query-digest 输出更细，能按 SQL 指纹聚合。我最怕看到“执行次数少、单次耗时极高”的 SQL，这种往往是漏加索引或者统计信息过期。还有一种“执行次数多、单次几十毫秒”的 SQL，单看不够慢，累积起来把 CPU 吃满。所以慢查询日志要配合 performance_schema 一起看，不能只盯阈值以上的。
2.2 慢查询定位工具链：performance_schema、sys schema、SHOW PROFILE与监控平台
慢查询日志有延迟，不能实时定位正在发生的慢 SQL。我常用 performance_schema 补这个缺口。开 performance_schema = ON 后，events_statements_current 表能看到当前线程执行的 SQL、耗时、扫描行数。有次线上 CPU 突然飙高，我查 events_statements_current，发现一个没加索引的 SELECT 在反复执行，每次扫描几十万行。杀掉对应线程，临时加索引，CPU 五分钟内降回来。performance_schema 有一定内存开销，我会按需开 consumer，不用的 instrument 关掉。
sys schema 是我日常用得最多的视图集合。它把 performance_schema 和 information_schema 的数据封装成易读的视图。sys.statement_analysis 按总延迟排序，sys.schema_table_statistics 看表级 IO，sys.innodb_lock_waits 直接给出锁等待关系。我每天早上会跑一遍 sys.statement_analysis，看看有没有新冒出来的高延迟 SQL。SHOW PROFILE 在 MySQL 8 里已经标记为废弃，我基本不用了，换成 performance_schema 的 stage 表。
监控平台是另一个维度。我工作过的团队用 Prometheus + Grafana 采集 MySQL 指标，慢查询数量、QPS、连接数、缓冲池命中率都画在面板上。有次业务反馈订单查询变慢，我看 Grafana 发现慢查询数量没涨，但 InnoDB 逻辑读暴涨。顺着逻辑读去查 sys.schema_table_statistics，发现一张配置表被全表扫描，原因是统计信息过期导致优化器选错索引。监控平台能帮我发现“不慢但很耗资源”的 SQL，这类 SQL 往往是未来慢查询的种子。
2.3 高频慢查询场景优化：索引失效、隐式类型转换、深分页、函数操作与OR条件
索引失效是我遇到最多的一类。有次开发同事写 WHERE user_id = '12345'，user_id 是 BIGINT，MySQL 把字符串转成数字，索引还能用。反过来 WHERE phone = 13800138000，phone 是 VARCHAR，数字转字符串，索引直接失效。执行计划里 type=ALL，扫描行数几十万。我让开发改成 phone = '13800138000'，type 变成 ref，响应从 1.2 秒降到 15 毫秒。隐式类型转换很隐蔽，代码里拼参数时类型不一致就会触发。
函数操作也会让索引失效。WHERE DATE(create_time) = '2024-01-01' 用不上 create_time 索引。我改成 create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'，范围扫描走索引。OR 条件同样麻烦。WHERE user_id = 1 OR status = 'PAID'，如果两个列都有索引，优化器可能选 index_merge，也可能全表扫描。我通常改写成 UNION ALL，每个子查询走各自索引，再在外层去重或聚合。深分页在上一章提过，LIMIT 100000, 20 会扫描前十万行再丢弃。我后来用延迟关联加覆盖索引，或者直接游标分页，性能稳定很多。
还有一种场景是 LIKE '%keyword%'。前导通配符用不上 B+ 树索引。我遇到过商品搜索，name LIKE '%手机%' 全表扫描。后来上 Elasticsearch 做搜索，MySQL 只存主键。如果数据量小，也可以用全文索引，但中文分词要配 ngram。函数操作里 ORDER BY RAND() 也是坑，它会生成临时表再排序，大表直接卡死。我一般改成应用层随机取 ID，或者用 WHERE id > random_id LIMIT 1 近似随机。
2.4 JOIN与分页慢查询优化：驱动表选择、连接顺序、延迟关联与游标分页
JOIN 慢查询我首先看驱动表。MySQL 优化器会选“小表驱动大表”，但统计信息不准时可能选反。有次订单表关联用户表，订单表 500 万行，用户表 10 万行。执行计划显示订单表作为驱动表，扫描 500 万次，每次去用户表主键查。我把 SQL 改成 STRAIGHT_JOIN 强制用户表驱动，扫描 10 万次，每次去订单表索引查，整体耗时从 4 秒降到 300 毫秒。STRAIGHT_JOIN 是兜底手段，我一般先更新统计信息，再看能不能通过索引引导优化器。
连接顺序也影响临时表和排序。多表 JOIN 时，Extra 里出现 Using temporary 和 Using filesort，我就要考虑是不是连接顺序导致中间结果集太大。EXPLAIN FORMAT=JSON 里的 cost_info 能看出各阶段代价。我常把大表放后面，小表先过滤，减少参与 JOIN 的行数。如果 SQL 里有 GROUP BY 和 ORDER BY，我会尽量让索引覆盖排序字段，避免文件排序。
分页优化我走过不少弯路。延迟关联在上一章有例子，核心是子查询只取主键，减少回表。游标分页更适合无限滚动。WHERE create_time < 'last_time' ORDER BY create_time DESC LIMIT 20，每次翻页带上上一页最后一条的时间。有次我帮一个社交 App 优化消息列表，从 LIMIT 100000, 20 改成游标分页，P99 从 2.3 秒降到 40 毫秒。游标分页的缺点是不能跳页，业务上要接受。如果非要跳页，我会限制最大页数，或者用预计算缓存。
2.5 MySQL配置与资源优化：InnoDB缓冲池、临时表、连接数、排序缓冲与并发参数
配置优化不能拍脑袋。innodb_buffer_pool_size 是我最先看的参数。它决定数据和索引在内存里缓存多少。专用数据库服务器上，我通常设成物理内存的 50% 到 70%。有次一台 64G 内存的机器，缓冲池只设了 8G，命中率 85%，大量读走磁盘。调到 40G 后，命中率升到 99%，慢查询少了一半。但也不能无限大，要留内存给连接、排序、临时表。innodb_buffer_pool_instances 在缓冲池大于 8G 时可以设成 8 或 16，减少锁竞争。
临时表和排序缓冲也常出问题。tmp_table_size 和 max_heap_table_size 太小，复杂查询的临时表会落盘，产生大量磁盘 IO。我遇到过报表 SQL 创建 200M 的临时表，落盘后跑了 30 秒。把两个参数调到 256M，临时表留在内存，耗时降到 3 秒。sort_buffer_size 和 join_buffer_size 是会话级参数，设太大容易内存溢出。我一般保持默认，除非明确看到 Using filesort 且数据量不大。max_connections 要结合连接池，设太高会导致线程上下文切换开销。我见过连接数 2000，但活跃连接只有 50，大量空闲连接占内存。
并发参数里 innodb_thread_concurrency 我很少动，MySQL 8 默认 0 让 InnoDB 自己控制。innodb_io_capacity 和 innodb_io_capacity_max 对 SSD 可以调高，比如 2000 和 4000。有次磁盘 IO 到 100%，慢查询排队，我把 innodb_io_capacity 从 200 调到 2000，刷脏页速度跟上，慢查询明显减少。所有配置调整后，我会观察 SHOW GLOBAL STATUS 里的 Innodb_buffer_pool_read_requests、Created_tmp_disk_tables、Threads_running，用数据验证效果。
2.6 锁等待、事务与并发慢查询：行锁、间隙锁、死锁、长事务与隔离级别
锁等待导致的慢查询，执行计划往往看不出问题。有次一个 UPDATE 语句单次执行 0.1 秒，但并发一高就超时。我查 sys.innodb_lock_waits，发现它被一个长事务阻塞。长事务里跑了多个 SELECT，持有行锁不释放。我把长事务拆成小事务，锁等待消失。行锁和间隙锁在 REPEATABLE READ 隔离级别下更明显。范围更新会加间隙锁，如果两个事务交叉更新不同范围，可能死锁。我见过 UPDATE ... WHERE id BETWEEN 10 AND 20 和 UPDATE ... WHERE id BETWEEN 15 AND 25 互相等待，InnoDB 检测到死锁后回滚一个。
死锁日志在 SHOW ENGINE INNODB STATUS 里。我一般先看 LATEST DETECTED DEADLOCK，找到两个事务持有的锁和等待的锁。有次死锁是因为两个事务更新同一张表的不同行，但索引顺序不一致。我把更新 SQL 改成按主键排序，死锁频率大幅下降。隔离级别也会影响锁行为。READ COMMITTED 下间隙锁少很多，很多互联网业务用 RC 减少锁冲突。但 RC 有幻读问题，需要业务能接受。我一般保持默认 RR，除非锁等待成为主要瓶颈。
长事务是慢查询的隐形推手。information_schema.innodb_trx 里 trx_started 很早、trx_rows_modified 很大的事务，我会重点关注。有次一个定时任务开启事务后，中间调用外部 HTTP 接口，接口超时 30 秒，事务一直不提交。期间所有更新该表的 SQL 都排队。我把外部调用移到事务外，先查数据，再开事务更新。长事务还会导致 undo log 膨胀，回滚段变大，影响 purge 线程。监控 trx_duration 超过 10 秒就告警，能避免很多并发问题。
2.7 慢查询治理闭环：基线监控、压测验证、上线审查与持续优化机制
慢查询治理不能靠运动式优化。我习惯先建基线。把核心接口的 SQL 收集起来，记录执行计划、扫描行数、平均耗时、P99。每周跑一次 sys.statement_analysis，对比基线。有次新上线一个功能，订单查询 P99 从 50ms 涨到 200ms，基线对比发现是新增的 LEFT JOIN 导致的。回滚后恢复。基线监控让我在用户投诉前发现问题。
压测验证是上线前的关卡。新 SQL 或索引变更，我会在预发环境用生产数据量的 1/10 到 1/5 压测。并发从 50 开始，逐步加到 200，观察 QPS、P99、CPU、IO、锁等待。有次我加了一个联合索引，单条 SQL 快了，但压测写入 QPS 掉了 30%。索引维护成本被低估了。后来我改成覆盖索引，减少回表，写入影响控制在 5% 以内。压测还要模拟数据倾斜，比如某个用户订单特别多，WHERE user_id = 1 可能比其他用户慢十倍。
上线审查我坚持看三样：执行计划、索引变更、事务边界。开发提交 DDL 和 SQL 时，我会用 EXPLAIN 跑一遍，确认 type 不是 ALL，Extra 没有 Using filesort 和 Using temporary。事务边界要短，不能包含外部调用。上线后灰度发布，监控慢查询日志和 performance_schema。持续优化机制里，我每月做一次慢查询复盘，把 Top 20 慢查询分给对应开发，限期优化。优化后回归验证，结果集必须一致，性能提升要量化。这套闭环跑顺后，慢查询数量能稳定在一个可控范围。
