MySQL慢查询性能调优与执行计划分析实战指南

MySQL慢查询是数据库运维中最常见的性能瓶颈来源。一条未优化的SQL足以拖垮整个数据库实例，在高并发场景下引发连锁故障。本文从慢查询定位、执行计划解读、索引优化、SQL改写到配置调优，系统演示MySQL性能调优的排查与修复流程。

慢查询日志定位与采集

开启慢查询日志是排查的第一步。动态开启无需重启MySQL实例：

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 100;

-- 验证配置
SHOW VARIABLES LIKE 'slow_query%';

生产环境推荐long_query_time设为1秒（先抓大放小），log_queries_not_using_indexes开启可捕获全表扫描查询。慢查询日志文件增长过快时，配置logrotate轮转。MySQL 8.0支持将慢查询写入mysql.slow_log表，便于SQL聚合分析。

-- 持久化到配置文件my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

使用pt-query-digest分析慢查询

Percona Toolkit的pt-query-digest是分析慢查询日志的事实标准工具：

# 安装Percona Toolkit
yum install percona-toolkit
# 或
apt install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 按数据库过滤分析
pt-query-digest --filter '$event->{db} eq "production"' /var/log/mysql/slow.log

# 只分析最近1小时
pt-query-digest --since "1h ago" /var/log/mysql/slow.log

pt-query-digest将相似SQL指纹聚合，按总耗时排序。报告头部显示统计概览，Ranked输出中关注以下指标：

# Profile 部分示例输出
# Rank Query ID           Response time  Calls  R/Call  V/M   Item
# ==== ================== ============== ====== ======= ===== ========
#    1 0xABC123...         150.5600 45.2%    520  0.2895  0.03 SELECT orders JOIN order_items
#    2 0xDEF456...          80.3200 24.1%    180  0.4462  0.05 SELECT users WHERE email

Response time占比最高的Query ID 1是首要优化目标。Calls列表示执行次数，高频次低单次耗时的SQL累积影响也很大。

EXPLAIN执行计划详解

定位到目标SQL后，使用EXPLAIN分析执行计划：

EXPLAIN SELECT o.order_id, o.user_id, o.total_amount, u.username, ui.phone
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN user_info ui ON u.id = ui.user_id
WHERE o.status = 1 
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

关键字段解读：

type（访问类型，性能从好到差）：system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须优化。range表示索引范围扫描，通常可接受。eq_ref和ref表示通过索引精确匹配，是连接查询的理想类型。

key：实际使用的索引。NULL表示未使用索引。possible_keys列出了可选索引但优化器未使用时，需分析原因。

rows：优化器估算的扫描行数。第一行扫描85000行再JOIN两表，总扫描量大。需要降低rows值。

Extra：Using index表示覆盖索引（不需回表），Using temporary表示用了临时表，Using filesort表示额外排序。后两者在大数据量时严重影响性能。

索引优化策略与实战

上述SQL的问题在于扫描85000行。orders表有idx_status（status单列索引）和idx_created（created_at单列索引），但查询同时用了status过滤和created_at排序，单列索引无法同时覆盖。

创建联合索引优化：

-- 创建联合索引，status在前（等值查询），created_at在后（范围+排序）
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 重新执行EXPLAIN
EXPLAIN SELECT o.order_id, o.user_id, o.total_amount, u.username, ui.phone
FROM orders o FORCE INDEX(idx_status_created)
JOIN users u ON o.user_id = u.id
JOIN user_info ui ON u.id = ui.user_id
WHERE o.status = 1 
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

联合索引使orders表扫描行数从85000降至2000，ORDER BY created_at也能利用索引有序性，消除Using filesort。

联合索引遵循最左前缀原则。索引(a,b,c)可匹配a、a,b、a,b,c三种查询模式，但不能直接匹配b或b,c。索引列顺序遵循：等值查询列在前，范围查询列在后，排序列紧随其后。

覆盖索引消除回表

当查询字段全部包含在索引中时，MySQL直接从索引树返回数据，避免回表查主键索引：

-- 原查询需要回表获取total_amount
SELECT order_id, user_id, total_amount FROM orders WHERE status = 1;

-- 建立覆盖索引
CREATE INDEX idx_status_cover ON orders(status, order_id, user_id, total_amount);

-- EXPLAIN结果Extra列显示Using index，表示覆盖索引命中
-- 扫描方式从ref变为index，但rows大幅减少且无回表

覆盖索引以空间换时间，索引字段不宜过多（索引宽度增加写入开销和存储成本）。核心高频查询适合覆盖索引，低频查询不值得维护额外索引。

SQL改写优化技巧

避免SELECT *，只查需要的列。减少网络传输和内存占用，增加覆盖索引命中概率：

-- 差：SELECT * 传输无用大字段
SELECT * FROM orders WHERE user_id = 10086;

-- 好：只取必要字段
SELECT order_id, status, total_amount FROM orders WHERE user_id = 10086;

分页优化，深分页时OFFSET扫描大量行：

-- 差：OFFSET 100000 需扫描100020行再丢弃前100000行
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- 好：游标分页，利用上一页最后一条记录的索引值
SELECT * FROM orders 
WHERE created_at < '2026-08-05 12:00:00' 
ORDER BY created_at DESC LIMIT 20;

-- 好：子查询先取主键，再JOIN
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000
) t ON o.id = t.id;

子查询IN改JOIN，某些MySQL版本优化器对JOIN处理优于IN子查询：

-- 差：依赖子查询
SELECT * FROM orders WHERE user_id IN (
  SELECT id FROM users WHERE vip_level >= 5
);

-- 好：改为JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level >= 5;

避免在索引列上使用函数或类型转换，否则索引失效退化为全表扫描：

-- 差：DATE函数导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-06';

-- 好：范围查询走索引
SELECT * FROM orders 
WHERE created_at >= '2026-08-06 00:00:00' 
  AND created_at < '2026-08-07 00:00:00';

InnoDB缓冲池与配置优化

innodb_buffer_pool_size是MySQL性能最重要的参数，建议设为物理内存的60%-80%：

-- 查看当前缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- 动态调整（MySQL 5.7+支持在线调整）
SET GLOBAL innodb_buffer_pool_size = 34359738368; -- 32GB

-- my.cnf持久化配置
[mysqld]
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

innodb_buffer_pool_instances在buffer pool大于1GB时建议设为8-16，减少缓冲池mutex争用。innodb_flush_method=O_DIRECT避免双重缓冲（操作系统页缓存+InnoDB缓冲池）。innodb_io_capacity根据磁盘IOPS设置，NVMe SSD可设为5000-10000。

在线DDL与大表变更

添加索引等DDL操作在亿级表上可能锁表数小时。使用pt-online-schema-change或gh-ost实现在线无锁变更：

# pt-online-schema-change添加索引
pt-online-schema-change \
  --alter "ADD INDEX idx_user_status(user_id, status)" \
  --execute \
  --chunk-size=2000 \
  --max-load Threads_running=50 \
  D=production,t=orders,h=127.0.0.1,u=admin,p=password

工具创建影子表、在源表上建立触发器同步增量数据、分批复制数据到影子表、最后原子rename交换表名。–max-load设置负载阈值，超过时自动暂停，保障在线业务不受影响。数据备份恢复方面，变更前务必执行mysqldump或Percona XtraBackup完整备份。