一、慢查询日志的核心价值与工作原理

慢查询日志是MySQL提供的核心性能诊断工具，通过记录执行时间超过阈值的SQL语句，为优化提供数据支撑。其工作机制包含三个关键环节：

1. 执行时间阈值判定：当SQL执行时间超过long_query_time参数值（默认10秒）时触发记录。该参数支持微秒级精度，可通过SET GLOBAL long_query_time=2动态调整。
2. 日志内容结构：每条记录包含时间戳、用户、主机、查询时间、锁等待时间等12个字段。示例日志：

   # Time: 2023-05-15T10:23:45.123456Z
   # User@Host: root[root] @ localhost []
   # Query_time: 5.678901  Lock_time: 0.123456 Rows_sent: 100
   SELECT * FROM orders WHERE create_time > '2023-01-01';

3. 存储引擎影响：InnoDB引擎会额外记录Rows_examined字段，显示扫描的行数，这对识别全表扫描至关重要。

二、慢查询日志的配置与最佳实践

1. 基础配置方案

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
-- 设置阈值为2秒
SET GLOBAL long_query_time = 2;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

配置建议：生产环境建议将阈值设为业务可接受的合理值（如0.5-2秒），避免日志量过大。

2. 高级配置技巧

• 动态阈值调整：通过SET SESSION long_query_time=0.1实现会话级临时调整，便于测试环境模拟。
• 日志轮转管理：结合logrotate工具实现日志自动切割，示例配置：

  /var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql adm
  }

• 临时禁用日志：执行大批量操作时，可通过SET GLOBAL slow_query_log = 'OFF'临时禁用，避免产生无效日志。

三、慢查询日志分析方法论

1. 基础分析工具

• mysqldumpslow工具：

  # 按平均查询时间排序
  mysqldumpslow -s at /var/log/mysql/mysql-slow.log
  # 查看前10条耗时最长的查询
  mysqldumpslow -t 10 -s t /var/log/mysql/mysql-slow.log

• pt-query-digest工具（Percona Toolkit）：

  pt-query-digest /var/log/mysql/mysql-slow.log > report.txt

  生成包含查询指纹、执行次数、总耗时等维度的分析报告。

2. 深度分析指标

• 查询指纹：通过# Query_time: 5.678901 Lock_time: 0.123456 Rows_sent: 100 Rows_examined: 1000000可识别：
  • 高Rows_examined/Rows_sent比值（如1000:1）表明存在大量无效扫描
  • 长Lock_time可能引发并发问题
• 执行计划验证：对慢查询执行EXPLAIN FORMAT=JSON，重点关注：
  • type列出现ALL表示全表扫描
  • possible_keys为空而key非空可能存在索引选择不当
  • Extra列出现Using filesort或Using temporary表明需要优化

四、基于慢查询的优化策略

1. 索引优化方案

• 覆盖索引设计：对高频查询的列组合建立复合索引，如：

  ALTER TABLE orders ADD INDEX idx_create_time_status (create_time, status);

• 索引选择性优化：通过SELECT COUNT(DISTINCT column)/COUNT(*) FROM table计算选择性，优先为选择性高的列建索引。
• 索引维护策略：定期执行ANALYZE TABLE orders更新统计信息，避免索引选择偏差。

2. SQL重写技巧

• 避免SELECT *：明确指定所需列，减少I/O压力

  -- 优化前
  SELECT * FROM users WHERE id = 100;
  -- 优化后
  SELECT id, name, email FROM users WHERE id = 100;

• 分页查询优化：对大数据量分页使用WHERE id > ? LIMIT ?替代LIMIT offset, size

  -- 优化前（offset越大越慢）
  SELECT * FROM orders ORDER BY id LIMIT 10000, 10;
  -- 优化后（恒定时间）
  SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 10;

3. 数据库设计优化

• 分区表应用：对时间序列数据按范围分区，如：

  CREATE TABLE logs (
    id BIGINT,
    create_time DATETIME,
    ...
  ) PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
  );

• 读写分离架构：将慢查询导向读库，通过/*FORCE_SLAVE*/提示实现

  SELECT /*FORCE_SLAVE*/ * FROM products WHERE category = 'electronics';

五、持续优化体系构建

1. 监控告警机制：通过Prometheus+Grafana监控Slow_queries指标，设置阈值告警
2. 优化流程标准化：
   • 每周分析慢查询日志TOP20
   • 制定优化方案并A/B测试
   • 跟踪优化前后性能对比
3. 知识库建设：将典型慢查询案例及优化方案归档，形成组织知识资产

六、典型案例分析

案例1：全表扫描优化

• 问题：SELECT * FROM orders WHERE customer_id = 100执行时间3.2秒
• 分析：Rows_examined: 1,200,000，type: ALL
• 优化：添加索引ALTER TABLE orders ADD INDEX idx_customer_id (customer_id)
• 结果：查询时间降至0.02秒

案例2：排序优化

• 问题：SELECT * FROM products ORDER BY price DESC LIMIT 10执行时间1.8秒
• 分析：Extra: Using filesort，扫描行数500,000
• 优化：添加索引ALTER TABLE products ADD INDEX idx_price (price)
• 结果：查询时间降至0.05秒，消除文件排序

通过系统化的慢查询日志分析与优化实践，某电商系统将平均查询响应时间从2.3秒降至0.4秒，CPU利用率下降35%，充分验证了该方法论的有效性。建议开发者建立定期分析机制，将慢查询优化纳入持续集成流程，实现数据库性能的渐进式提升。