慢查询是数据库性能的瓶颈根源
线上MySQL响应变慢，90%的概率是慢查询导致的。一条全表扫描的SQL在百万级表上耗时数秒，在千万级表上可能直接拖垮整个实例。定位慢查询、分析执行计划、设计最优索引，这套流程是DBA和后端开发的核心技能。
开启慢查询日志与pt-query-digest分析
配置慢查询日志
-- my.cnf或运行时设置
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 查看确认
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';Percona pt-query-digest分析
pt-query-digest将慢查询日志聚合为可读的统计报告，按执行时间排序，快速定位最消耗资源的SQL：
# 安装Percona Toolkit
yum install -y percona-toolkit # CentOS
apt install -y percona-toolkit # Ubuntu
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
# 只分析最近1小时
pt-query-digest --since '1h' /var/log/mysql/slow.log
# 过滤特定数据库
pt-query-digest --filter '$event->{db} =~ /production/' /var/log/mysql/slow.log报告输出关键信息：
# Rank: 按查询时间占比排名
# Query ID: SQL指纹（参数替换后的哈希）
# Response time: 总响应时间和占比
# Calls: 执行次数
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== ============== ============== ===== ====== ====
# 1 0x52A3BA3E 1250.0000 62% 5000 0.250 0.01
# 2 0xA8B4F1D2 450.0000 22% 300 1.500 0.05Query ID为0x52A3BA3E的SQL贡献了62%的响应时间，优先优化这条。
EXPLAIN执行计划深度解读
拿到目标SQL后用EXPLAIN分析执行路径：
EXPLAIN SELECT o.order_id, o.amount, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 50;执行计划关键字段解读：
| 字段 | 含义 | 需要关注 |
|——|——|———|
| type | 访问类型 | ALL(全表扫描)、index(索引全扫)、range(范围扫描)、ref(索引等值)、const(主键等值) |
| key | 实际使用索引 | NULL表示没用索引 |
| rows | 预估扫描行数 | 值越大越慢 |
| Extra | 额外信息 | Using filesort(额外排序)、Using temporary(临时表)、Using where(回表过滤) |
常见问题模式
type=ALL且rows=1000000，全表扫描，需要加索引。
Extra=Using filesort，排序没走索引，需要优化ORDER BY。
Extra=Using index condition，索引下推(ICP)生效，这是好的信号。
索引设计：从单列到覆盖索引
单列索引的问题
在orders表上分别建status和created_at两个单列索引，MySQL优化器只能选一个使用：
-- 查看现有索引
SHOW INDEX FROM orders;
-- 创建单列索引
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created ON orders(created_at);EXPLAIN结果显示key=idx_status，type=ref，但rows仍然很大——因为status过滤后数据量还是很多，created_at条件在回表后才能过滤。
联合索引优化
将status和created_at合并为联合索引：
-- 最左前缀原则：等值条件在前，范围条件在后
CREATE INDEX idx_status_created ON orders(status, created_at);现在EXPLAIN结果显示type=range，key=idx_status_created，rows大幅减少。
覆盖索引消除回表
如果查询只需要order_id、amount、status、created_at四列，可以把它们全部放进索引：
CREATE INDEX idx_cover ON orders(status, created_at, order_id, amount);EXPLAIN的Extra列出现”Using index”，表示直接从索引树读取数据，不需要回表。覆盖索引在大数据量下性能提升可达5-10倍，代价是索引体积增大。
索引设计三原则：
1. 联合索引遵循最左前缀，等值条件在前，范围条件在后
2. 高选择性列放前面（区分度高的列先过滤掉更多行）
3. 尽量用覆盖索引消除回表开销
线上索引变更安全操作
大表加索引会锁表，影响线上服务。MySQL 5.6+的Online DDL允许在加索引期间继续DML操作：
-- Online DDL（推荐）
ALTER TABLE orders ADD INDEX idx_status_created(status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;
-- 查看DDL进度
SHOW PROCESSLIST;
-- 或查看progress
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';如果Online DDL执行时间过长（超大表），使用pt-online-schema-change：
pt-online-schema-change \
--alter "ADD INDEX idx_status_created(status, created_at)" \
--host=127.0.0.1 --port=3306 --user=admin --password=xxx \
D=production,t=orders \
--chunk-size=5000 --max-load=Threads_running=100 \
--critical-load=Threads_running=200 \
--executept-osc通过创建影子表+增量同步+原子切换的方式，在不停机情况下完成索引变更。
SQL改写优化实战
避免索引失效的常见写法
-- 错误：索引列使用函数
WHERE DATE(created_at) = '2026-07-30'
-- 正确：范围查询
WHERE created_at >= '2026-07-30' AND created_at < '2026-07-31'
-- 错误：隐式类型转换
WHERE user_id = '123' -- user_id是int，字符串导致索引失效
-- 正确
WHERE user_id = 123
-- 错误：OR导致索引合并
WHERE status = 'PAID' OR status = 'SHIPPED'
-- 正确：IN
WHERE status IN ('PAID', 'SHIPPED')
-- 错误：LIKE前缀通配符
WHERE name LIKE '%keyword%'
-- 正确：前缀匹配可走索引
WHERE name LIKE 'keyword%'分页优化：避免深分页
-- 慢：OFFSET 100000 需要扫描前100000行再丢弃
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 快：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- 次选：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t
ON o.id = t.id;延迟关联的原理：子查询只需要走索引列，不需要回表，速度快；外层查询通过主键精确回表，只取20行。
慢查询诊断到索引优化是一个闭环：pt-query-digest定位问题SQL、EXPLAIN分析执行路径、设计最优索引、Online DDL安全上线、监控验证效果。持续迭代这个闭环，数据库性能才能稳定可控。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/mysql-man-cha-xun-zhen-duan-yu-suo-yin-you-hua-cong/
