慢查询分析与定位

问题

如何定位和分析 MySQL 中的慢查询？慢查询日志怎么开启和使用？除了 EXPLAIN 还有哪些诊断工具？

答案

慢查询排查流程

慢查询日志

开启慢查询日志

-- 查看当前状态
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;  -- 记录未使用索引的查询

-- 永久生效（my.cnf）
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 1
-- log_queries_not_using_indexes = 1

慢查询日志内容

# Time: 2024-01-15T10:30:45.123456Z
# User@Host: app_user[app_user] @ web-server [10.0.1.100]
# Query_time: 3.456789  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 1500000
SET timestamp=1705315845;
SELECT * FROM orders WHERE user_id = 12345 AND create_time > '2024-01-01';

关键字段：

Query_time：查询总耗时
Lock_time：等待锁的时间
Rows_sent：返回给客户端的行数
Rows_examined：实际扫描的行数（远大于 Rows_sent 说明需要优化）

mysqldumpslow 工具

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

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

# 按平均查询时间排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

输出示例：

Count: 3  Time=2.50s (7.50s)  Lock=0.00s (0.00s)  Rows=100.0 (300)
SELECT * FROM orders WHERE user_id = N AND status = 'S'

pt-query-digest

Percona Toolkit 的 pt-query-digest 功能更强大，生产环境推荐使用：

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

它能自动聚合相似 SQL、计算百分位数、生成可视化报告。

SHOW PROFILE

SHOW PROFILE 可以查看 SQL 执行的每个阶段耗时：

-- 开启 profiling
SET profiling = 1;

-- 执行 SQL
SELECT * FROM orders WHERE user_id = 100;

-- 查看最近的查询列表
SHOW PROFILES;
-- +----------+------------+-----------------------------------------------+
-- | Query_ID | Duration   | Query                                         |
-- +----------+------------+-----------------------------------------------+
-- |        1 | 0.00356789 | SELECT * FROM orders WHERE user_id = 100      |
-- +----------+------------+-----------------------------------------------+

-- 查看某条 SQL 的详细耗时
SHOW PROFILE FOR QUERY 1;
-- +----------------------+----------+
-- | Status               | Duration |
-- +----------------------+----------+
-- | starting             | 0.000045 |
-- | checking permissions | 0.000005 |
-- | Opening tables       | 0.000020 |
-- | init                 | 0.000015 |
-- | System lock          | 0.000008 |
-- | optimizing           | 0.000010 |
-- | statistics           | 0.000030 |
-- | preparing            | 0.000012 |
-- | executing            | 0.000003 |
-- | Sending data         | 0.003200 |  ← 主要耗时在这里
-- | end                  | 0.000005 |
-- | query end            | 0.000005 |
-- | closing tables       | 0.000008 |
-- +----------------------+----------+

-- 查看 CPU 和 IO 信息
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

Sending data 阶段耗时长意味着：数据量大、或频繁回表、或涉及临时表/排序。

Performance Schema 替代

MySQL 官方声明 SHOW PROFILE 将在未来弃用，推荐使用 Performance Schema：

-- 查询执行阶段耗时
SELECT event_name, timer_wait/1000000000 AS duration_ms
FROM performance_schema.events_stages_history_long
WHERE nesting_event_id = <event_id>
ORDER BY timer_start;

Performance Schema

Performance Schema 是 MySQL 内置的性能监控框架，记录各种执行事件。

-- 查看最耗时的 SQL（按总执行时间）
SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_time_s,
    ROUND(AVG_TIMER_WAIT/1000000000000, 4) AS avg_time_s,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看哪些表被全表扫描
SELECT 
    OBJECT_SCHEMA, OBJECT_NAME,
    COUNT_READ, COUNT_FETCH,
    SUM_TIMER_WAIT/1000000000 AS total_wait_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE COUNT_FETCH > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0
  AND OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema');

OPTIMIZER TRACE

查看优化器的详细决策过程：

-- 开启 trace
SET optimizer_trace = 'enabled=on';

-- 执行 SQL
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

-- 查看 trace
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关闭
SET optimizer_trace = 'enabled=off';

trace 中可以看到：

优化器考虑了哪些索引
每个索引的估算成本
最终选择了哪个索引以及原因

诊断工具对比

工具	用途	粒度	生产可用
慢查询日志	捕获慢 SQL	SQL 级	✅ 推荐
EXPLAIN	分析执行计划	SQL 级	✅ 推荐
SHOW PROFILE	查看 SQL 内部阶段耗时	阶段级	⚠️ 将弃用
Performance Schema	全方位性能监控	事件级	✅ 推荐
OPTIMIZER TRACE	优化器决策详情	决策级	⚠️ 开销大
pt-query-digest	慢查询聚合分析	SQL 级	✅ 推荐

常见面试问题

Q1: 如何定位线上慢 SQL？

答案：

开启慢查询日志：设置 long_query_time（建议 1-2 秒），log_queries_not_using_indexes
APM 工具：SkyWalking/Pinpoint 等自动采集慢 SQL
pt-query-digest 分析：聚合相似 SQL，按总耗时排序，优先处理 Top SQL
EXPLAIN 分析：查看 type 是否全表扫描、key 是否为空、Extra 是否有 filesort
SHOW PROFILE：定位慢在哪个阶段（Sending data 多半是数据量大或索引问题）

Q2: Rows_examined 远大于 Rows_sent 说明什么？

答案：

说明 MySQL 扫描了大量行但只返回了少量数据，原因可能是：

没有使用索引（全表扫描）
索引选择性差（索引列重复值多）
需要回表（非覆盖索引）
WHERE 条件过滤了大量数据（筛选率低）

优化方向：添加合适索引、使用覆盖索引、优化 WHERE 条件。

Q3: 如何找出数据库中未使用的索引？

答案：

通过 Performance Schema 查询：

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_STAR = 0
  AND OBJECT_SCHEMA = 'your_database';

未使用的索引应考虑删除，因为：

占用磁盘空间
拖慢写入性能（INSERT/UPDATE/DELETE 需要维护索引）
干扰优化器选择

Q4: 开启慢查询日志对性能有影响吗？

答案：

影响很小，生产环境建议开启：

只在 SQL 执行完毕后做一次判断和写日志
可将日志输出到文件或表（log_output = FILE/TABLE，FILE 性能更好）
建议 long_query_time 不要设太低（1-2 秒），否则日志量大

问题​

答案​

慢查询排查流程​

慢查询日志​

开启慢查询日志​

慢查询日志内容​

mysqldumpslow 工具​

SHOW PROFILE​

Performance Schema​

OPTIMIZER TRACE​

诊断工具对比​

常见面试问题​

Q1: 如何定位线上慢 SQL？​

Q2: Rows_examined 远大于 Rows_sent 说明什么？​

Q3: 如何找出数据库中未使用的索引？​

Q4: 开启慢查询日志对性能有影响吗？​

相关链接​

问题

答案