MySQL慢查询诊断不能只看执行时间

MySQL慢查询日志的long_query_time参数默认10秒，这个值在生产环境中毫无意义——10秒的查询在用户侧已经是灾难性延迟。实际操作中把long_query_time设为0.5秒甚至更低，配合pt-query-digest做聚合分析，才能抓到真正需要优化的查询。

慢查询调优的正确路径：打开慢查询日志→用pt-query-digest聚合Top SQL→EXPLAIN分析执行计划→针对性建索引或改写SQL→压测对比验证。直接跳到EXPLAIN而不做聚合分析，会陷入逐条优化的低效循环——一个慢查询优化完，同样的模式在另一个查询中再次出现。

慢查询日志配置与聚合分析

-- 慢查询日志配置
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- 超过500ms记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 未用索引的查询也记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看配置是否生效
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

pt-query-digest聚合分析，找出真正的性能杀手：

# 按总执行时间排序Top 20慢查询
pt-query-digest /var/log/mysql/slow.log \
    --order-by Query_time:sum \
    --limit 20 \
    --output slowquery-report.txt

# 输出关键字段：
# Rank - 查询排名
# Query ID - 查询指纹
# Response time - 总响应时间及占比
# Calls - 执行次数
# R/Call - 平均每次执行时间
# V/M - 方差/均值比，越大说明查询时间波动大

重点关注Response time占比超过5%的查询和V/M值大于1.5的查询。前者是效率提升的杠杆点，后者说明查询执行时间不稳定，可能存在数据倾斜或锁等待。

EXPLAIN执行计划深度解读

EXPLAIN输出的每一列都有诊断价值，不能只看type列：

type列（访问类型，从优到差）

| 类型 | 含义 | 出现时的处理建议 |
|——|——|—————–|
| const | 单行查找，主键/唯一索引 | 无需优化 |
| eq_ref | 关联查询中唯一索引查找 | 正常 |
| ref | 非唯一索引查找 | 检查索引选择性 |
| range | 索引范围扫描 | 检查扫描行数 |
| index | 全索引扫描 | 确认是否可加WHERE条件 |
| ALL | 全表扫描 | 必须优化 |

Extra列关键信息

– Using filesort：排序未走索引，额外排序操作。检查ORDER BY字段是否在索引中
– Using temporary：使用了临时表。GROUP BY无索引时常见，需要加组合索引
– Using index condition：索引下推（ICP）生效，好信号
– Using where：存储引擎返回数据后在Server层过滤，说明索引未完全覆盖查询条件

索引优化实战：三种典型场景

场景1：组合索引列顺序错误导致索引失效

-- 问题查询
SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'PAID' 
ORDER BY create_time DESC 
LIMIT 20;

-- 错误索引（create_time在前面，范围排序导致后续列无法利用索引）
ALTER TABLE orders ADD INDEX idx_wrong (create_time, user_id, status);

-- 正确索引（等值条件列在前，排序列在后）
ALTER TABLE orders ADD INDEX idx_correct (user_id, status, create_time);

-- 验证
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'PAID' 
ORDER BY create_time DESC LIMIT 20;
-- type: ref, Extra: Using index condition + Backward index scan

场景2：隐式类型转换导致索引失效

-- 问题：user_id是VARCHAR类型，但查询传入整数
SELECT * FROM users WHERE user_id = 1001;
-- MySQL会将user_id列转换为整数再比较，导致索引失效
-- EXPLAIN显示type=ALL

-- 修复：确保查询参数类型与列类型一致
SELECT * FROM users WHERE user_id = '1001';
-- EXPLAIN显示type=ref

场景3：OR条件导致索引合并效率低下

-- 问题查询：OR条件导致索引合并
SELECT * FROM products 
WHERE category_id = 5 OR brand_id = 10;

-- 索引合并的执行计划
-- type: index_merge, Extra: Using union(idx_category, idx_brand)

-- 优化方案1：UNION ALL改写
SELECT * FROM products WHERE category_id = 5
UNION ALL
SELECT * FROM products WHERE brand_id = 10 AND category_id != 5;

-- 优化方案2：业务层面拆分查询，应用层合并结果

MySQL 8.0特有优化功能

降序索引（Descending Index）

MySQL 8.0真正支持降序索引，不再像5.7那样忽略DESC关键字：

-- 8.0降序索引真实生效
ALTER TABLE orders ADD INDEX idx_time_desc (user_id, create_time DESC);

-- 查询时不再需要filesort
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 
ORDER BY create_time DESC;
-- Extra: Using index condition（无filesort）

隐藏索引（Invisible Index）

删除索引前先设为隐藏，观察业务影响：

-- 设为隐藏索引（查询优化器不再使用）
ALTER TABLE orders ALTER INDEX idx_old SET INVISIBLE;

-- 观察无索引后的查询性能，确认无影响后删除
ALTER TABLE orders DROP INDEX idx_old;

-- 如有问题可快速恢复
ALTER TABLE orders ALTER INDEX idx_old SET VISIBLE;

窗口函数替代复杂GROUP BY

-- 旧写法：找出每个用户最近一笔订单（需要子查询+JOIN）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT user_id, MAX(create_time) as max_time
    FROM orders GROUP BY user_id
) t ON o.user_id = t.user_id AND o.create_time = t.max_time;

-- 8.0窗口函数写法：更清晰且性能更优
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) as rn
    FROM orders
) t WHERE rn = 1;

参数调优与监控闭环

核心参数调整需要基于实际数据量和工作负载：

-- InnoDB缓冲池大小（占物理内存的70-80%，但不超过数据总量）
SET GLOBAL innodb_buffer_pool_size = 16G;

-- 查询缓存8.0已移除，无需配置

-- 连接数根据实际并发设置，不要盲目调大
SET GLOBAL max_connections = 500;

-- 排序缓冲区（ORDER BY内存，默认256KB偏小）
SET GLOBAL sort_buffer_size = 2M;

-- 监控关键指标
-- Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads
-- 比值低于100:1说明缓冲池命中率低，需要调大
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

索引优化和参数调优完成后的验证步骤：用sysbench跑只读压测，对比优化前后的QPS和P99延迟。每次只改一个变量，记录对比数据。生产环境上线前在预发环境做全量回归，确保优化没有引入新的慢查询。