MySQL慢查询优化实战：慢日志分析定位与索引优化方案

MySQL慢查询是数据库运维中最常见的性能问题来源。一条慢SQL拖垮整个库的情况在生产环境反复出现，根因大多是查询计划走错了索引、扫描行数过大或锁等待时间过长。优化的起点是慢日志：把执行时间超过阈值的SQL捞出来，分析执行计划，找到索引和查询结构的短板。本文按”采集-定位-优化-验证”的完整路径，给出慢查询优化的实操方案。

慢查询日志采集：参数配置与常见误区

慢查询日志默认关闭，需要开启并设置阈值。long_query_time设置慢SQL的判定阈值，一般从1秒起步，业务高峰期降到100-200ms才能捕获真正有问题的SQL。

-- 开启慢查询日志（运行时临时生效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 持久化配置（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

常见误区是只开日志不落库。慢日志文件需要配合pt-query-digest这类工具解析聚合，单独看几十条原文很难看出规律。另一个误区是log_queries_not_using_indexes全开，会把大量小表全表扫描的SQL记录进来，日志爆炸，建议先关闭，定位问题时再临时打开。

慢日志分析：用pt-query-digest聚合统计

pt-query-digest是Percona Toolkit里的慢日志分析工具，把慢日志按SQL指纹聚合，输出执行次数、平均耗时、累计耗时排行，一次分析就能定位哪些SQL是耗时大户。

# 安装Percona Toolkit
sudo apt install -y percona-toolkit

# 分析慢日志，输出排行
pt-query-digest /var/log/mysql/slow.log > slow-report.txt

# 只显示TOP10
pt-query-digest --limit 10 /var/log/mysql/slow.log

# 过滤特定表
pt-query-digest --filter '$event->{arg} =~ m/orders/' /var/log/mysql/slow.log

报告里重点看三个指标：Query_time的Avg和Max（平均耗时与最大耗时）、Rows_examined（扫描行数）与Rows_sent（返回行数）的比值、执行次数×单次耗时的累积成本。扫描行数远超返回行数的SQL，几乎都是索引没走对，是优化的主战场。

EXPLAIN解读：看懂执行计划找索引短板

拿到具体慢SQL后，用EXPLAIN看执行计划。关键字段：type（访问类型，从system、const、eq_ref、ref、range到index、ALL逐级变差）、key（实际使用的索引）、rows（预估扫描行数）、Extra（是否Using filesort、Using temporary等危险信号）。

EXPLAIN SELECT o.order_no, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-09-01' AND o.status = 'PAID'
ORDER BY o.created_at DESC
LIMIT 20;

-- 输出要点：
-- type: ref / ALL  -> 出现ALL即全表扫描，必须处理
-- key: idx_status_created  -> 实际使用的索引
-- rows: 480000          -> 预估扫描48万行
-- Extra: Using filesort  -> 排序未走索引，数据量大时是性能杀手

执行计划里出现Using filesort和Using temporary时，90%的情况可以通过调整索引字段顺序解决。排序字段、等值条件字段、范围条件字段的先后顺序直接影响索引是否能覆盖排序和过滤。

SQL索引优化：复合索引设计与最左前缀

复合索引的字段顺序决定索引能覆盖哪些查询，设计原则是”等值条件字段优先、范围字段次之、排序字段收尾”。索引优化案例：上面SQL的优化方案。

-- 原SQL存在的问题：status和created_at分开建了单列索引，type=ALL
-- 优化：复合索引 (status, created_at DESC)
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at DESC);

-- 优化后EXPLAIN
-- type: ref | key: idx_status_created | rows: 850 | Extra: 无filesort

-- 常见索引误区：
-- 1. 对每列单独建索引，查询只能用到其中一个
-- 2. 范围查询字段放复合索引前面，后面字段索引失效
-- 3. 索引列使用函数/表达式，如WHERE DATE(created_at)，索引失效
-- 正确写法：WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

范围查询字段放索引中间会影响后面字段的索引使用：WHERE status=’PAID’ AND created_at>’2026-09-01′ AND channel=’WEB’，若索引为(status, created_at, channel)，channel的索引就失效。顺序应该调成(status, channel, created_at)。函数包裹索引列会让索引完全失效，改写为范围条件是最常见的修复手段。

高并发写场景的慢查询处理

写操作慢常见原因：无主键或主键随机、二级索引过多导致写放大、行锁和间隙锁争抢。写优化重点：确认主键单调递增；控制二级索引数量；避免长事务持有行锁。

-- 检查锁等待
SHOW ENGINE INNODB STATUS\G
-- 查看当前正在执行的SQL
SELECT * FROM performance_schema.events_statements_current WHERE state='LOCK WAIT';

-- 定位大事务
SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS sec
FROM information_schema.innodb_trx
ORDER BY sec DESC LIMIT 10;

-- 批量写优化：合并为批量INSERT
INSERT INTO orders (id, user_id, amount, status) VALUES
(1, 101, 99.00, 'PAID'), (2, 102, 120.00, 'PAID'), ...; -- 500行一批

批量插入比单条循环插入快一个数量级，原因是一次往返网络开销、一次日志刷盘、一次索引更新。事务时间控制在几百毫秒内，避免长事务占着undo log和锁资源。

慢查询治理的持续化机制

慢查询优化不是一次性的SQL改改完，需要把机制建起来：慢查询日志持续采集、日报聚合、新增慢SQL告警、优化后效果对比。

# 每日慢查询日报脚本（crontab每日）
10pt-query-digest --since=1d /var/log/mysql/slow.log | head -80

# 新增慢SQL告警：慢日志数量突增告警（对比上周同期）
# 方案：pt-query-digest输出JSON，用脚本对比每日TOP SQL出现次数
pt-query-digest --format=json /var/log/mysql/slow.log > daily.json

# 治理节奏
# 第一轮：TOP10慢SQL全部优化，通常能消除60%-70%的慢查询时间
# 第二轮：让阈值从1s降到500ms，捕获中等SQL，持续优化
# 之后：每季度复扫一次，应对数据量和流量增长带来的新慢SQL

真正杜绝慢查询靠的是研发流程：新表、新SQL上线前用EXPLAIN把关，ORM生成的SQL定期抽样审查，大表变更走DDL工具避免锁表。MySQL慢查询优化是数据规模增长下的持续功课，机制比单次优化更重要。