MySQL慢查询优化实战：执行计划解读与深分页性能陷阱的修复方案

MySQL慢查询诊断：从定位到优化的闭环方法论

MySQL性能问题的排查不是一条EXPLAIN命令走天下，而是一套从慢查询日志抓取、执行计划解读、索引优化到SQL改写的完整闭环。线上数据库的性能瓶颈90%来自不当的SQL写法和缺失的索引，剩余10%来自参数配置和硬件瓶颈。本文以生产环境真实场景为背景，覆盖慢查询定位、索引策略、执行计划深度解读和常见反模式修复。

慢查询日志配置与采集策略

慢查询日志是诊断的起点，默认关闭。线上开启需注意I/O开销和磁盘空间：

-- 动态开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;    -- 超过100ms记录
SET GLOBAL min_examined_row_limit = 100;  -- 扫描行数≥100才记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未用索引的查询也记录

-- 指定日志路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

生产环境建议long_query_time=0.1而非默认的10秒。100ms是OLTP场景的合理阈值，超过这个值的查询大概率有优化空间。采集量过大时用pt-query-digest聚合分析：

# 分析慢查询日志Top 20
pt-query-digest /var/log/mysql/slow.log --limit 20

# 按查询指纹分组统计执行次数、平均耗时、扫描行数
# 输出示例：
# Rank Query ID           Response time  Calls  R/Call  V/M
# 1    0xF6A8E5C3B2...   125.4s  42.3%  3421  0.037   0.01
# 2    0xA7B2C9D4E1...   89.7s   30.2%  1205  0.074   0.03

如果磁盘I/O敏感，改用Performance Schema替代慢查询日志：

-- Performance Schema采集慢查询
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

-- 查询耗时Top SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC LIMIT 20;

EXPLAIN执行计划深度解读：type列与Extra列

EXPLAIN的type列从最优到最差排序：

system > const > eq_ref > ref > range > index > ALL

-- system/const：单行匹配，主键或唯一索引等值查询
-- eq_ref：JOIN时被驱动表通过主键/唯一索引匹配
-- ref：非唯一索引等值查询
-- range：索引范围扫描 (BETWEEN, >, <)
-- index：全索引扫描（覆盖索引但无过滤条件）
-- ALL：全表扫描，必须优化

Extra列的几个危险信号：

-- Using filesort：额外排序，未走索引排序
-- Using temporary：创建临时表，GROUP BY无索引时常见
-- Using where; Using join buffer (Block Nested Loop)：JOIN无索引
-- Select tables optimized away：直接从索引取值，无需回表（好的信号）

实战案例：一个包含排序和分页的慢查询：

-- 原始SQL
SELECT order_id, user_id, amount, created_at
FROM orders
WHERE status = 'PAID' AND merchant_id = 1001
ORDER BY created_at DESC
LIMIT 20;

-- EXPLAIN结果
-- type: ref  key: idx_merchant_status  rows: 45820  Extra: Using where; Using filesort

-- 问题：idx_merchant_status(merchant_id, status)索引无法覆盖ORDER BY
-- MySQL需要找到45820行数据后做filesort

-- 优化：创建覆盖排序的复合索引
ALTER TABLE orders ADD INDEX idx_merchant_status_created(merchant_id, status, created_at DESC);

-- 优化后EXPLAIN
-- type: ref  key: idx_merchant_status_created  rows: 820  Extra: Using where; Using index condition

复合索引遵循最左前缀原则：等值条件放前面，排序字段放后面。(merchant_id, status, created_at)使得WHERE筛选和ORDER BY都能走索引，filesort消失，扫描行数从45820降到820。

索引优化策略：覆盖索引与索引下推

覆盖索引是指查询所需的所有列都在索引中，无需回表查主键数据。这是减少I/O最有效的手段：

-- 查询：只取索引包含的列
SELECT user_id, order_count
FROM user_stats
WHERE user_id = 1001;

-- 如果存在索引 idx_user_count(user_id, order_count)
-- Extra列显示 "Using index" = 覆盖索引，0次回表

-- 对比：SELECT * 强制回表
SELECT * FROM user_stats WHERE user_id = 1001;
-- 即使有索引也需要回表读取所有列数据

索引下推（ICP, Index Condition Pushdown）是MySQL 5.6+的优化，将WHERE条件中的索引列过滤下推到存储引擎层执行，减少回表次数：

-- 索引 idx_name_age(name, age)
-- 查询
SELECT * FROM users WHERE name LIKE '张%' AND age > 25;

-- 无ICP：存储引擎按name LIKE '张%'扫描索引 → 回表 → Server层过滤age > 25
-- 有ICP：存储引擎按name LIKE '张%'扫描索引 → 在索引中直接过滤age > 25 → 回表

-- EXPLAIN中 Extra: Using index condition 表示ICP生效

ICP的生效条件：索引的最左前缀无法完全匹配WHERE条件时（如LIKE前缀匹配+范围条件），剩余的索引列条件会被下推。但这仅限于二级索引，主键索引天然就是数据本身，没有”回表”概念。

分页查询优化：深分页的性能陷阱

LIMIT 100000, 20这种深分页查询，MySQL需要扫描前100020行再丢弃前100000行，代价巨大：

-- 问题SQL：扫描100020行，返回20行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 方案1：游标分页（推荐）
-- 前端记录上一页最后一条的ID，下次查询时从该ID开始
SELECT * FROM orders WHERE id > 100020 ORDER BY id LIMIT 20;

-- 方案2：延迟关联
-- 先通过子查询在索引上定位到需要的20个主键ID，再回表
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;

-- 方案3：between分页（适合有序ID）
SELECT * FROM orders
WHERE id BETWEEN 100001 AND 100020;

三种方案的性能对比（100万行数据）：

LIMIT 100000, 20       → 350ms  (扫描100020行)
游标分页 id > 100020   → 2ms    (扫描20行)
延迟关联              → 15ms   (索引扫描100020行 + 20次回表)

游标分页性能最优，但不支持跳页（直接跳到第5000页）。如果业务必须支持跳页，用延迟关联方案，比原始深分页快一个数量级。

参数调优：InnoDB缓冲池与redo日志配置

除了SQL和索引层面的优化，InnoDB的关键参数对性能影响显著：

[mysqld]
# InnoDB缓冲池大小：独占服务器设物理内存70-80%，共享服务器设50%
innodb_buffer_pool_size = 16G
# 多缓冲池实例减少锁争用（每个实例≥1GB）
innodb_buffer_pool_instances = 8

# Redo日志大小：影响写入吞吐和崩溃恢复时间
innodb_log_file_size = 2G
innodb_log_files_in_group = 2

# 刷盘策略：1=每次事务刷盘（最安全），2=每秒刷盘（推荐）
innodb_flush_log_at_trx_commit = 2

# 二进制日志同步：1=每次事务同步（最安全），0=由OS缓存刷新
sync_binlog = 100

# 连接数与超时
max_connections = 500
wait_timeout = 28800
interactive_timeout = 28800

# 排序与连接缓冲
sort_buffer_size = 2M
join_buffer_size = 4M
read_rnd_buffer_size = 4M

innodb_flush_log_at_trx_commit = 2是OLTP场景的推荐值。设为1保证ACID但I/O开销大，设为2意味着每秒刷盘一次，崩溃时最多丢1秒事务——对多数业务可接受。sync_binlog同理，100表示每100次事务同步一次binlog到磁盘。两个参数从1调到2/100，TPS通常提升3-5倍，代价是崩溃恢复时可能丢失最后1秒数据。

在线诊断工具箱：应急场景快速排查

线上突发慢查询时的应急排查流程：

# 1. 查看当前正在执行的SQL
SHOW PROCESSLIST;
# 或更详细的
SELECT * FROM information_schema.processlist
WHERE COMMAND != 'Daemon' AND TIME > 5;

# 2. 杀死阻塞的查询（慎重操作）
KILL <thread_id>;

# 3. 查看InnoDB锁等待
SELECT * FROM performance_schema.data_lock_waits\G

# 4. 查看InnoDB状态（含死锁信息）
SHOW ENGINE INNODB STATUS\G

# 5. 查看缓冲池命中率
SELECT (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) AS hit_rate
FROM (SELECT variable_value AS Innodb_buffer_pool_reads
      FROM performance_schema.global_status
      WHERE variable_name = 'Innodb_buffer_pool_reads') r,
     (SELECT variable_value AS Innodb_buffer_pool_read_requests
      FROM performance_schema.global_status
      WHERE variable_name = 'Innodb_buffer_pool_read_requests') req;

缓冲池命中率低于95%说明内存不足，频繁读盘；死锁日志中看到两个事务互相等待对方持有的锁，需要调整事务边界或加锁顺序。性能调优是持续工程，不是一次性动作——建立慢查询日报机制，每日Review新增慢查询，将优化动作持续化。