MySQL慢查询日志分析实战：从pt-query-digest到索引优化全流程

慢查询日志的开启与配置

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;

参数说明：long_query_time设为1秒而非默认的10秒——生产环境1秒以上的查询就需要关注。log_queries_not_using_indexes记录所有未使用索引的查询。min_examined_row_limit设为100，过滤掉检查行数极少的查询（即使未使用索引也不影响性能）。

持久化到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
log_output = FILE

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

Percona Toolkit的pt-query-digest是分析慢查询日志的标准工具。它将相似查询聚合，按总耗时排序，快速定位TOP N问题查询。

# 基础分析
pt-query-digest /var/log/mysql/slow.log

# 分析指定时间段
pt-query-digest --since '2026-08-04 00:00:00' \
                --until '2026-08-04 06:00:00' \
                /var/log/mysql/slow.log

# 只分析某个数据库
pt-query-digest --filter '$arg->{db} eq "production"' \
                /var/log/mysql/slow.log

# 输出到文件
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

分析报告的关键字段解读：

• Rank——查询排名，按总执行时间排序
• Response Time——该查询的总响应时间及占所有慢查询的百分比
• Calls——执行次数
• R/Call——平均每次执行时间
• V/M——方差均值比，值越大说明执行时间波动越大

重点关注R/Call高且Calls多的查询——这类查询对系统整体性能影响最大。V/M值大的查询通常与数据分布有关（某些参数命中索引，某些没有）。

EXPLAIN执行计划解读

定位到问题查询后，使用EXPLAIN分析执行计划：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = 'paid' 
  AND created_at >= '2026-07-01' 
ORDER BY created_at DESC 
LIMIT 20;

EXPLAIN输出的关键字段：

type字段的性能排序从好到差：system > const > eq_ref > ref > range > index > ALL。生产环境至少要达到range级别，理想状态是ref或eq_ref。

Using filesort表示MySQL无法通过索引顺序直接返回排序结果，需要在内存或磁盘中排序。这对性能的影响在大结果集下非常严重。Using temporary表示需要创建临时表，通常出现在GROUP BY和DISTINCT操作中。

复合索引设计原则

慢查询优化的核心手段是索引设计。复合索引遵循最左前缀原则——查询条件必须从索引最左列开始连续使用。设计复合索引的步骤：

第一步：分析查询模式的组合频率。将过滤性最强的列放在最左边：

-- 查询模式1: WHERE user_id = ? AND status = ?
-- 查询模式2: WHERE user_id = ? AND created_at >= ?
-- 查询模式3: WHERE user_id = ? ORDER BY created_at DESC

-- 复合索引设计
ALTER TABLE orders ADD INDEX idx_user_status_created 
  (user_id, status, created_at);

这个索引同时覆盖三种查询模式：模式1完整使用三列索引；模式2使用前缀(user_id)；模式3使用前缀(user_id)并利用created_at做索引排序避免filesort。

第二步：检查是否可以利用覆盖索引消除回表。如果查询的列都在索引中，MySQL直接从索引返回数据，不需要回表读取数据行：

-- 覆盖索引示例
SELECT user_id, status, created_at FROM orders 
WHERE user_id = 12345;

-- Extra列显示 Using index，表示覆盖索引命中

第三步：避免索引失效的常见写法：

-- 索引失效：对索引列使用函数
WHERE YEAR(created_at) = 2026  -- 全表扫描
-- 修正：改为范围查询
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- 索引失效：隐式类型转换
WHERE phone = 13800138000  -- phone是varchar，传入int导致全表扫描
-- 修正：传入字符串
WHERE phone = '13800138000'

-- 索引失效：OR条件中有一个列无索引
WHERE user_id = 12345 OR order_no = 'ORD001'
-- 修正：为order_no添加索引，或改用UNION
SELECT * FROM orders WHERE user_id = 12345
UNION
SELECT * FROM orders WHERE order_no = 'ORD001'

分页查询的优化方案

深分页（LIMIT 100000, 20）是慢查询的重灾区。MySQL需要扫描前100020行再丢弃前100000行，效率极低。

方案一：延迟关联，先通过覆盖索引获取主键，再关联查询：

-- 原始查询（慢）
SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 100000, 20;

-- 延迟关联（快）
SELECT t.* FROM orders t
INNER JOIN (
    SELECT id FROM orders 
    WHERE user_id = 12345 
    ORDER BY created_at DESC 
    LIMIT 100000, 20
) tmp ON t.id = tmp.id;

方案二：游标分页，记录上一页最后一条记录的值，用范围查询替代LIMIT：

-- 第一页
SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC, id DESC 
LIMIT 20;

-- 下一页（使用上一页最后一条记录的created_at和id）
SELECT * FROM orders 
WHERE user_id = 12345 
  AND (created_at < '2026-08-03 10:30:00' 
       OR (created_at = '2026-08-03 10:30:00' AND id < 98765))
ORDER BY created_at DESC, id DESC 
LIMIT 20;

游标分页的性能恒定，不受页码深度影响。局限是不支持跳页——只能上一页/下一页。

SQL查询优化的其他手段

子查询改JOIN——MySQL 5.6+对子查询做了优化，但JOIN通常仍有性能优势：

-- 子查询
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE level >= 5);
-- 改为JOIN
SELECT o.* FROM orders o 
INNER JOIN users u ON o.user_id = u.id 
WHERE u.level >= 5;

GROUP BY优化——MySQL 8.0默认开启only_full_group_by，GROUP BY列必须出现在SELECT中或被聚合函数包裹。GROUP BY使用索引可以避免Using temporary：

-- 确保GROUP BY的列有索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 查询利用索引完成GROUP BY
SELECT user_id, status, COUNT(*) as cnt 
FROM orders 
GROUP BY user_id, status;

批量INSERT优化——单条INSERT的性能远低于批量INSERT：

-- 慢：逐条插入
INSERT INTO logs (msg) VALUES ('log1');
INSERT INTO logs (msg) VALUES ('log2');

-- 快：批量插入
INSERT INTO logs (msg) VALUES ('log1'), ('log2'), ('log3');

-- 更快：LOAD DATA（适合大批量导入）
LOAD DATA INFILE '/tmp/logs.csv' 
INTO TABLE logs 
FIELDS TERMINATED BY ',' (msg);

持续监控与优化闭环

慢查询优化不是一次性工作。MySQL性能调优需要建立持续监控机制：

• 每天使用pt-query-digest生成慢查询报告，对比前一天的变化
• 关注performance_schema.events_statements_summary_by_digest表中的TOP SQL
• 将慢查询数量和TOP 10查询的平均响应时间接入监控告警
• 每次数据库变更（加索引、改表结构）后，回归测试TOP 20慢查询的执行计划

通过sys.schema_unused_indexes视图定期清理无用索引——冗余索引会降低写入性能并浪费存储空间。sys.schema_redundant_indexes视图直接列出冗余索引，删除前确认该索引不在慢查询执行计划中使用。数据备份恢复场景下，索引重建也应纳入流程，避免索引碎片影响查询性能。