慢查询日志与分析

慢查询日志记录执行时间超过阈值的SQL，是性能问题定位的核心工具。

开启慢查询日志

配置参数

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;  -- 超过1秒记录

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';

配置文件设置

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1
min_examined_row_limit = 100  -- 检查行数超过100才记录

慢查询日志格式

标准日志格式

# Time: 2026-05-15T10:30:00.123456Z
# User@Host: app_user[app_user] @ 192.168.1.100 []
# Query_time: 5.123456  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 500000
SET timestamp=1715777400;
SELECT * FROM orders WHERE user_id = 1 AND status LIKE '%pending%';

字段说明

分析慢查询日志

mysqldumpslow工具

# 按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按扫描行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 参数说明
-s: 排序方式（t:时间, c:次数, r:扫描行数, l:锁时间）
-t: 显示前N条
-g: 匹配模式

pt-query-digest工具

# Percona Toolkit提供更强大的分析
pt-query-digest /var/log/mysql/slow.log

# 输出摘要报告，包含：
# 1. 查询指纹（抽象SQL）
# 2. 执行次数、总时间、平均时间
# 3. 执行时间分布
# 4. 示例SQL

pt-query-digest输出示例

# Attribute      pct   total     min     max     avg     95%  stddev  median
# ============   ===   =====     ===     ===     ===     ===  ======  =====
# Count           45      150
# Exec time       80   1200s      1s    300s     8s    180s    50s     5s
# Lock time       60    0.5s   100us   100ms   3ms    50ms   10ms   1ms
# Rows sent       30    3000       1     100      20      90      25      10
# Rows examined   80  15.00M     100   500K  100K   400K   120K    80K

# Query fingerprint:
SELECT * FROM orders WHERE user_id = ?

# Sample query:
SELECT * FROM orders WHERE user_id = 12345 AND status LIKE '%pending%'

定位慢查询原因

分析扫描行数比例

Rows_examined / Rows_sent 比例判断：

比例 ≈ 1：高效查询，扫描少
比例 > 10：需优化，扫描过多
比例 > 100：严重问题，全表扫描或索引失效

示例：
Rows_sent: 100, Rows_examined: 500000
比例: 5000 → 索引失效或查询设计问题

常见慢查询原因

查看实时慢查询

performance_schema监控

-- 开启events_statements_history
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

-- 查看最近慢查询
SELECT
    TIMER_START/1000000000000 AS start_time,
    TIMER_WAIT/1000000000000 AS duration_sec,
    SQL_TEXT,
    ROWS_EXAMINED,
    ROWS_SENT
FROM performance_schema.events_statements_history_long
WHERE TIMER_WAIT/1000000000000 > 1
ORDER BY TIMER_START DESC;

查看当前运行查询

-- 查看正在执行的查询
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    info AS query
FROM information_schema.processlist
WHERE command = 'Query' AND time > 1;

-- 查看长时间运行的查询
SELECT * FROM information_schema.innodb_trx;

慢查询优化流程

1. 定位慢查询
   └── 查看慢查询日志
   └── 使用pt-query-digest分析
      ↓
2. 分析执行计划
   └── EXPLAIN 查看索引使用
   └── 检查 Rows_examined 比例
      ↓
3. 确定优化方向
   ├── 添加索引
   ├── 修复索引失效
   ├── 改写SQL
   ├── 设计覆盖索引
   └── 优化表结构
      ↓
4. 实施优化
   └── 添加/修改索引
   └── 改写查询语句
      ↓
5. 验证优化效果
   └── 对比执行时间
   └── 确认索引使用

监控慢查询指标

-- 查看慢查询统计
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 查看慢查询日志状态
SHOW GLOBAL STATUS LIKE 'Slow_query_log%';

-- 计算慢查询比例
SELECT
    VARIABLE_VALUE AS slow_queries,
    (SELECT VARIABLE_VALUE FROM global_status WHERE VARIABLE_NAME = 'Questions') AS total_queries,
    VARIABLE_VALUE / (SELECT VARIABLE_VALUE FROM global_status WHERE VARIABLE_NAME = 'Questions') AS slow_ratio
FROM global_status WHERE VARIABLE_NAME = 'Slow_queries';

要点总结

• 开启慢查询日志，设置合理阈值（建议1秒）
• 使用mysqldumpslow或pt-query-digest分析日志
• 关注Rows_examined/Rows_sent比例判断效率
• 通过EXPLAIN确认索引使用情况
• performance_schema可实时监控慢查询
• 定位原因后针对性优化索引或SQL
• 建立定期慢查询分析机制，持续优化