MySQL性能优化（七）— 慢查询全攻略

一、慢查询基础与日志配置

1.1 慢查询日志的核心作用

慢查询日志是MySQL性能诊断的”黑匣子”，通过记录执行时间超过阈值的SQL语句，为开发者提供第一手性能数据。其核心价值体现在：

• 精准定位性能瓶颈：无需猜测，直接锁定低效SQL
• 量化分析：记录执行时间、锁等待时间等关键指标
• 趋势追踪：长期监控数据库性能变化

1.2 配置实践（以MySQL 5.7+为例）

-- 启用慢查询日志（需重启或执行FLUSH LOGS）
SET GLOBAL slow_query_log = 'ON';
-- 设置阈值（单位：秒，建议生产环境设为1-2秒）
SET GLOBAL long_query_time = 1;
-- 指定日志文件路径（确保MySQL有写入权限）
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- 记录未使用索引的查询（生产环境慎用，可能产生大量日志）
SET GLOBAL log_queries_not_using_indexes = 'ON';

关键参数说明：

• long_query_time：阈值设置需结合业务场景，OLTP系统建议0.5-1秒，OLAP系统可适当放宽
• 日志轮转：建议配置logrotate定期清理旧日志，避免磁盘空间耗尽

二、慢查询分析方法论

2.1 工具矩阵对比

工具	适用场景	优势	局限
mysqldumpslow	快速统计慢查询分布	轻量级，无需额外安装	仅支持简单排序
pt-query-digest	深度分析（Percona工具）	支持时间序列分析、执行计划对比	需要单独安装
Performance Schema	实时监控	原生支持，低开销	配置复杂，数据量大

2.2 执行计划深度解析

以典型慢查询为例：

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 1001 
AND order_date > '2023-01-01'
ORDER BY total_amount DESC
LIMIT 100;

关键指标解读：

• type列：ALL（全表扫描）必须优化，range/index/const为理想状态
• key列：显示实际使用的索引，NULL表示未使用索引
• rows列：预估扫描行数，超过1000行需警惕
• Extra列：Using filesort/Using temporary是性能杀手

优化路径：

1. 添加复合索引：ALTER TABLE orders ADD INDEX idx_cust_date (customer_id, order_date)
2. 覆盖索引优化：ALTER TABLE orders ADD INDEX idx_cover (customer_id, order_date, total_amount)
3. 强制索引使用（谨慎使用）：SELECT * FROM orders FORCE INDEX(idx_cust_date) ...

三、索引优化实战

3.1 索引设计原则

1. 最左前缀原则：复合索引(A,B,C)可支持A、A+B、A+B+C查询，但无法支持B+C
2. 选择性计算：优先为高选择性列创建索引

   -- 计算列的选择性（值越接近1越好）
   SELECT COUNT(DISTINCT customer_id)/COUNT(*) FROM orders;

3. 索引长度控制：前缀索引可节省空间

   ALTER TABLE users ADD INDEX idx_name (name(20)); -- 只索引前20个字符

3.2 索引失效场景

1. 函数操作：WHERE DATE(create_time) = '2023-01-01'导致索引失效
2. 隐式转换：列类型为varchar但使用数字查询
3. OR条件：除非所有OR条件都有索引，否则会全表扫描
4. 复合索引顺序不当：如索引为(A,B)但查询条件为B=1

四、SQL改写技巧

4.1 典型低效模式

1. SELECT *：只查询必要字段

   -- 优化前
   SELECT * FROM products WHERE category_id = 5;
   -- 优化后
   SELECT id, name, price FROM products WHERE category_id = 5;

2. 子查询优化：将IN子查询改为JOIN

   -- 优化前（可能产生N+1问题）
   SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE vip=1);
   -- 优化后
   SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.vip=1;

3. 分页优化：避免大偏移量

   -- 优化前（偏移量越大越慢）
   SELECT * FROM logs ORDER BY id DESC LIMIT 100000, 10;
   -- 优化后（使用游标）
   SELECT * FROM logs WHERE id < last_seen_id ORDER BY id DESC LIMIT 10;

五、监控与持续优化

5.1 动态监控方案

-- 实时查看当前慢查询
SHOW PROCESSLIST;
-- 查看全局慢查询统计
SHOW GLOBAL STATUS LIKE 'Slow_queries';
-- Performance Schema高级用法
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

5.2 优化周期建议

1. 日常监控：每小时检查慢查询日志新增量
2. 周度分析：使用pt-query-digest生成周报
3. 月度复盘：结合业务增长调整索引策略

六、真实案例解析

案例背景：某电商系统订单查询接口响应时间突增至5秒

诊断过程：

1. 通过慢查询日志定位到以下SQL：

   SELECT * FROM orders 
   WHERE status = 'completed' 
   AND create_time BETWEEN '2023-01-01' AND '2023-01-31'
   ORDER BY total_amount DESC;

2. 执行计划显示：
   • 使用status单列索引
   • 产生临时表和文件排序
   • 扫描行数超过50万

优化方案：

1. 创建复合索引：(status, create_time, total_amount)
2. 改写SQL使用覆盖索引：

   SELECT id, total_amount FROM orders 
   WHERE status = 'completed' 
   AND create_time BETWEEN '2023-01-01' AND '2023-01-31'
   ORDER BY total_amount DESC 
   LIMIT 100;

3. 实施效果：
   • 响应时间降至0.2秒
   • 扫描行数减少至2万
   • 消除临时表和文件排序

七、进阶优化技术

7.1 索引合并优化

MySQL 5.0+支持索引合并策略，可通过optimizer_switch控制：

-- 查看当前设置
SHOW VARIABLES LIKE 'optimizer_switch';
-- 启用索引合并（默认已启用）
SET optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on';

7.2 直方图统计（MySQL 8.0+）

-- 创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON customer_id, order_date;
-- 查看直方图信息
SELECT * FROM sys.schema_column_statistics WHERE schema_name='your_db' AND table_name='orders';

7.3 查询重写插件

使用MySQL Enterprise的Query Rewrite插件或开源方案如ProxySQL实现自动SQL改写。

八、常见误区警示

1. 过度索引：每个索引增加约10%写入开销，需权衡读写比例
2. 盲目使用FORCE INDEX：可能导致更差的执行计划
3. 忽视数据分布：索引选择性低的列（如性别）不适合单独建索引
4. 忽略锁竞争：慢查询可能引发锁等待，需结合SHOW ENGINE INNODB STATUS分析

九、优化效果验证

实施优化后，建议从以下维度验证：

1. 响应时间：使用pt-query-digest对比优化前后平均执行时间
2. 系统负载：监控CPU、IO利用率是否下降
3. 并发能力：通过压力测试验证QPS提升
4. 资源消耗：检查InnoDB缓冲池命中率是否提高

十、总结与行动指南

1. 立即行动：

   • 启用慢查询日志并设置合理阈值
   • 安装pt-query-digest进行深度分析
   • 对TOP 10慢查询进行索引优化

2. 中期计划：

   • 建立SQL审核流程，防止新慢查询产生
   • 实施Performance Schema监控
   • 定期进行索引维护（重建碎片化索引）

3. 长期战略：

   • 构建自动化慢查询告警系统
   • 将慢查询优化纳入CI/CD流程
   • 持续跟踪MySQL新版本的优化特性

通过系统化的慢查询优化，可使数据库性能提升3-10倍，同时降低系统资源消耗，为企业节省大量硬件成本。记住：优秀的数据库性能不是调优出来的，而是设计出来的，应从应用架构层面预防慢查询的产生。