1. 慢查询日志的前世今生

如果把数据库比作人体，慢查询日志就是专业的体检报告。它忠实地记录着所有执行时间超过阈值的SQL语句，就像医生手里的X光片，能让我们清晰看到数据库的"病灶"所在。

在日均访问量百万级的电商系统中，我曾遇到一个诡异现象：每到上午10点订单量激增时，数据库CPU就会飙升到90%。最终就是通过分析慢查询日志，发现了一个隐藏的"订单状态统计查询"在高峰时段拖垮了整个数据库。

2. 开启慢查询日志的正确姿势

技术栈：MySQL 8.0+

先来看如何正确配置这个"数据库听诊器"：

-- 临时开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 单位：秒
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';

-- 永久生效配置（需要修改my.cnf）
[mysqld]
slow_query_log = 1
long_query_time = 1
log_output = FILE
slow_query_log_file = /var/lib/mysql/slow.log
log_queries_not_using_indexes = 1  -- 记录未走索引的查询
min_examined_row_limit = 1000      -- 扫描超过1000行才记录

这里有个新手常踩的坑：修改long_query_time后，需要重新连接数据库才会生效。就像调整了手表时间，但必须重新启动才会走新的时间。

3. 慢查询日志的实战分析方法

3.1 原始日志解读

一条典型的慢查询记录长这样：

# Time: 2023-08-20T10:30:45.123456Z
# User@Host: shop_user[shop_user] @  [192.168.1.100]  Id:  1234
# Query_time: 3.456789  Lock_time: 0.000123 Rows_sent: 5  Rows_examined: 500000
SET timestamp=1692520245;
SELECT * FROM orders 
WHERE user_id=123 
AND status IN (1,2,5) 
AND create_time BETWEEN '2023-08-01' AND '2023-08-20';

关键指标解析：

  • Query_time：3.45秒（明显超过阈值）
  • Rows_examined：50万行（说明存在全表扫描）
  • Rows_sent：5行（查询效率极低）

3.2 使用分析工具

推荐使用mysqldumpslow进行初步分析：

# 查看前10条慢查询
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 输出示例
Count: 200  Time=2.12s (424s)  Lock=0.00s (0s)  Rows=5.0 (1000)
  SELECT * FROM products WHERE category_id=N

更专业的pt-query-digest使用示例：

pt-query-digest /var/lib/mysql/slow.log

# 输出摘要会显示：
# Rank   Response time    Calls  R/Call  Query
# ====   =============    =====  ======  =====
# 1     1124.3610 75.2%    528   2.1290  SELECT orders
# 2      248.1356 16.6%    123   2.0173  SELECT products

4. 案例分析：电商系统的慢查询优化

4.1 场景还原

某电商平台促销期间出现数据库响应变慢，通过慢查询日志发现大量类似查询：

SELECT * FROM order_items 
WHERE product_id = 123 
AND status = 2 
AND create_time > '2023-08-01';

执行计划分析：

EXPLAIN SELECT * FROM order_items WHERE product_id = 123;
-- 输出显示type=ALL，key=NULL，说明全表扫描

4.2 优化方案

添加复合索引：

ALTER TABLE order_items 
ADD INDEX idx_product_status_time(product_id, status, create_time);

优化后执行计划变为：

EXPLAIN SELECT * FROM order_items WHERE product_id = 123;
-- type=ref, key=idx_product_status_time, rows=50

4.3 效果对比

指标 优化前 优化后
查询时间 2.3s 0.02s
扫描行数 50万 50
CPU消耗 85% 5%

5. 延伸讨论：关联技术的配合使用

5.1 执行计划深度解析

EXPLAIN的每个字段都值得关注：

EXPLAIN SELECT * FROM users WHERE phone='13800138000';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100000 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

重点关注：

  • type：ALL表示全表扫描
  • rows：预估扫描行数
  • Extra：Using filesort表示需要排序

5.2 索引优化原则

  • 最左前缀原则：索引(a,b,c)能优化a=？、a=？ AND b=？，但无法优化b=？ AND c=？
  • 避免过度索引：每个额外索引会增加约5%的写入开销
  • 索引长度优化：对于VARCHAR(255)的字段，可以只索引前20个字符

6. 慢查询分析的优缺点与注意事项

6.1 优势所在

  • 精准定位：直接反映生产环境真实情况
  • 历史追溯：可分析某时间段的性能变化
  • 成本低廉：几乎不产生额外性能消耗

6.2 局限性

  • 事后诸葛亮：只能记录已经发生的慢查询
  • 日志膨胀风险：高并发场景可能产生GB级日志
  • 阈值依赖：设置不当可能遗漏重要信息

6.3 避坑指南

  1. 生产环境建议设置long_query_time=1秒
  2. 定期清理日志（推荐保留7天）
  3. 不要开启log_queries_not_using_indexes，除非排查特定问题
  4. 结合Percona Monitoring进行实时监控

7. 总结与建议

慢查询日志就像数据库的"黑匣子"，记录了所有关键操作。建议建立定期分析机制：

  1. 每日：检查慢查询Top10
  2. 每周：分析慢查询变化趋势
  3. 每月：复盘索引优化效果

记住，优化是个持续的过程。某次优化后的快速查询，可能在数据量增长后再次变慢。就像汽车需要定期保养，数据库也需要持续的性能调优。

最后送大家一个检查清单：

  • [ ] 是否所有慢查询都有合适索引？
  • [ ] 是否存在重复索引？
  • [ ] 是否合理使用覆盖索引？
  • [ ] 是否定期进行索引碎片整理？

通过慢查询日志这个显微镜，我们不仅能解决当前问题，更能培养出预防性能问题的敏感度。毕竟，最好的优化就是不让慢查询发生。