数据库慢查询日志分析与优化实战

题目描述
慢查询日志是数据库记录执行时间超过指定阈值的SQL语句的日志文件，是性能优化的核心工具。面试常考察如何配置慢查询日志、分析日志内容以及基于日志进行SQL调优的完整流程。

一、慢查询日志配置与开启

1. 核心参数说明

   • slow_query_log：设置为ON开启慢查询日志（默认OFF）
   • long_query_time：定义慢查询阈值（单位：秒），如10.0表示超过10秒的查询被记录
   • slow_query_log_file：指定日志文件存储路径
   • log_queries_not_using_indexes：是否记录未使用索引的查询（需注意可能产生大量日志）

2. 配置示例（MySQL）

   -- 动态设置（重启失效）  
   SET GLOBAL slow_query_log = 1;  
   SET GLOBAL long_query_time = 2;  
   SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';  
   
   -- 永久生效需写入配置文件my.cnf  
   [mysqld]  
   slow_query_log = 1  
   long_query_time = 2  
   slow_query_log_file = /var/log/mysql/slow.log  
   

二、慢查询日志内容解析
单条慢查询记录包含以下关键信息（以MySQL为例）：

# Time: 2023-10-01T10:00:00.123456Z  
# User@Host: user[root] @ localhost []  
# Query_time: 5.002031  Lock_time: 0.001000 Rows_sent: 1  Rows_examined: 1000000  
SET timestamp=1696154400;  
SELECT * FROM orders WHERE create_date < '2023-01-01';  

• Query_time：SQL实际执行时间（核心指标）
• Lock_time：等待表锁的时间
• Rows_sent：返回给客户端的行数
• Rows_examined：服务器层检查的行数（扫描行数越多通常性能越差）

三、慢查询日志分析工具

1. mysqldumpslow（MySQL官方工具）

   # 统计最耗时的3个慢查询  
   mysqldumpslow -s t -t 3 /var/log/mysql/slow.log  
   
   # 统计锁定时间最长的查询  
   mysqldumpslow -s l -t 3 /var/log/mysql/slow.log  
   

   • -s 排序方式（t: 按时间, l: 按锁时间, r: 返回行数）
   • -t 限制输出条数

2. pt-query-digest（Percona Toolkit）

   # 生成详细分析报告  
   pt-query-digest /var/log/mysql/slow.log > slow_report.txt  
   

   输出包含：

   • 总查询次数与执行时间分布
   • 每个SQL的响应时间占比
   • 执行次数最多的查询语句

四、基于日志的优化实战步骤

1. 定位高开销查询

   • 优先优化执行频率高且平均耗时长的查询（公式：频率×单次耗时）
   • 关注Rows_examined远大于Rows_sent的查询（可能存在全表扫描）

2. 分析具体SQL的执行计划

   EXPLAIN SELECT * FROM orders WHERE create_date < '2023-01-01';  
   

   • 检查type列是否为ALL（全表扫描）
   • 观察key列是否使用索引

3. 常见优化手段

   • 添加索引：为WHERE条件字段create_date创建索引

     ALTER TABLE orders ADD INDEX idx_create_date(create_date);  
     

   • 重写查询：避免SELECT *，仅返回必要字段
   • 分页优化：将LIMIT 10000,10改为WHERE id > 上次最大ID

4. 验证优化效果

   • 对比优化前后Query_time和Rows_examined的下降比例
   • 使用EXPLAIN确认索引生效情况

五、进阶实践技巧

1. 动态开启日志采样

   -- 业务高峰时段开启1%的慢查询记录  
   SET GLOBAL log_slow_rate_limit = 1;  
   SET GLOBAL log_slow_rate_type = 'query';  
   

2. 监控日志文件大小
   • 定期归档历史日志（如按日切割）
   • 使用log_rotation_size参数限制单文件大小

通过系统化运用慢查询日志，可精准定位数据库性能瓶颈，实现从“被动救火”到“主动预防”的优化闭环。