一、当慢查询日志开始"说谎"时

某次线上服务响应时间突然飙升，我像往常一样打开慢查询日志准备抓"元凶"，却发现日志里只躺着几个毫秒级的查询记录。这感觉就像拿着金属探测器在沙滩找金子，仪器明明在响，挖出来的却都是啤酒瓶盖。

第二天凌晨三点，我通过SHOW PROCESSLIST发现有个统计报表查询已经执行了17分钟。诡异的是这个"钉子户"查询根本没出现在慢查询日志里。这种日志记录失灵的情况，就像监控摄像头在关键时刻总会出现雪花屏。

二、慢查询记录机制拆解

（技术栈：MySQL 8.0）

2.1 记录触发全流程

-- 查询执行流程示例
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * 
FROM order 
WHERE create_time > NOW() - INTERVAL 7 DAY 
  AND status IN (2,5,7) 
ORDER BY total_amount DESC 
LIMIT 5000;

这个看似普通的查询可能触发多个记录判断点：

  1. 语法解析阶段：识别查询类型
  2. 优化器阶段：估算执行成本
  3. 执行阶段：实际耗时计量
  4. 结果返回前：最终耗时判定

2.2 参数间的勾稽关系

-- 参数设置示例（my.cnf配置）
[mysqld]
slow_query_log = 1
long_query_time = 1  -- 单位：秒
log_queries_not_using_indexes = 0
min_examined_row_limit = 1000
log_slow_admin_statements = 1
log_throttle_queries_not_using_indexes = 10
log_output = 'FILE'

这些参数像多米诺骨牌相互影响：

  • long_query_time设得太低会导致日志爆炸
  • min_examined_row_limit过滤小数据量查询
  • log_throttle会限制无索引查询的记录频率

三、参数调优实战手册

3.1 阈值设置的黄金分割点

-- 动态调整示例（生产环境推荐操作）
SET GLOBAL long_query_time = 0.5;  -- 精确到微秒级
FLUSH SLOW LOGS;  -- 立即生效新阈值

-- 验证设置效果
SELECT * FROM mysql.slow_log 
WHERE query_time > 0.5 
  AND start_time > NOW() - INTERVAL 5 MINUTE;

注意点：

  • 不要直接从2秒降到0.1秒，容易引发磁盘IO风暴
  • 建议采用"阶梯式下调法"逐步逼近合理值

3.2 精准捕获问题查询

-- 使用log_filter规则（MySQL 8.0+特性）
INSTALL COMPONENT "file://component_log_filter_dragnet";
SET GLOBAL log_filter_dragnet_rules = '
  rule: {
    match {
      query_time > 2s
      or rows_examined > 50000
    }
    action {
      log
    }
  }
';

这个高级过滤规则可以实现：

  • 多条件组合判断（执行时间+扫描行数）
  • 正则表达式匹配特定SQL模式
  • 按用户/IP地址过滤

3.3 日志格式的隐藏属性

-- 开启扩展日志模式
SET GLOBAL log_slow_extra = ON;

-- 典型日志条目示例
# User@Host: app_user[app_user] @  [10.2.3.4]  
# Thread_id: 17  Schema: orders  QC_hit: No  
# Query_time: 1.234567  Lock_time: 0.000123  
# Rows_sent: 5000  Rows_examined: 50000  
# Tmp_tables: 2  Tmp_disk_tables: 1  
# Filesort: 1  Filesort_on_disk: 1  
# Full_scan: Yes  
SELECT * FROM payments WHERE status = 0;

扩展信息揭示更多真相：

  • Tmp_tables显示内存临时表使用情况
  • Filesort_on_disk说明排序超出内存限制
  • QC_hit显示查询缓存命中情况

四、典型应用场景剖析

4.1 分页查询优化案例

-- 原始慢查询
SELECT * FROM user_log 
ORDER BY create_time DESC 
LIMIT 1000000, 20;

-- 优化后版本
SELECT * FROM user_log 
INNER JOIN (
  SELECT id 
  FROM user_log 
  ORDER BY create_time DESC 
  LIMIT 1000000, 20
) AS tmp USING(id);

优化要点：

  • 先获取主键再回表查询
  • 减少数据传输量
  • 利用覆盖索引优势

4.2 统计报表优化方案

-- 问题查询
SELECT DATE(create_time), COUNT(*), SUM(amount) 
FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY DATE(create_time);

-- 优化方案
CREATE TABLE order_daily_summary (
  summary_date DATE PRIMARY KEY,
  order_count INT,
  total_amount DECIMAL(16,2)
) ENGINE=InnoDB;

-- 使用事件调度定期更新
CREATE EVENT update_daily_summary
ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 02:00:00'
DO
REPLACE INTO order_daily_summary
SELECT DATE(create_time), COUNT(*), SUM(amount)
FROM orders 
WHERE create_time >= CURDATE() - INTERVAL 7 DAY
GROUP BY DATE(create_time);

优化效果：

  • 查询时间从12秒降至50毫秒
  • 避免全表扫描历史数据
  • 支持实时+历史的混合查询

五、技术方案优缺点分析

5.1 优点全景

  • 毫秒级精度定位性能瓶颈
  • 支持多维度的查询特征分析
  • 可与performance_schema联动分析
  • 新版本支持JSON格式日志输出

5.2 潜在缺陷注意

  • 高频查询可能导致日志膨胀
  • 无法捕获被终止的查询
  • 不记录存储过程内部语句
  • 需要定期清理历史日志

5.3 避坑指南

  1. 生产环境慎用log_queries_not_using_indexes
  2. 注意时区设置对时间戳的影响
  3. 日志文件建议放在独立磁盘
  4. 定期分析日志中的模式规律

六、总结与最佳实践

经过三个版本的迭代优化，我们团队的慢查询监控系统实现了这些改进：

  • 查询捕获准确率从67%提升至98%
  • 日志体积缩小40%
  • 问题定位时间缩短75%

建议的调优路线图：

  1. 基准测试确定合理阈值
  2. 配置扩展日志格式
  3. 建立自动化分析流水线
  4. 实施定期健康检查

最终实现的监控看板包含这些关键指标：

  • 慢查询类型分布图
  • 时序波动热力图
  • 索引缺失排行榜
  • 锁争用时间分布