慢 SQL 定位与优化

举报
yd_287908631 发表于 2026/11/03 13:43:49 2026/11/03
【摘要】 从慢日志采集到根因归类，给出一套可复用的慢 SQL 定位与优化流程

线上接口变慢，第一反应往往是"数据库是不是有问题"，但真正能落到具体 SQL 的团队并不多。问题通常出在三个环节：慢日志没开或阈值太高、采集了但没聚合、聚合了但不会读执行计划。这篇文章给出一套从发现到验证的完整流程，包含可直接执行的命令、真实输出示例和优化前后的对比方法。基准 MySQL 8.0 + InnoDB，5.7 的工具差异单独说明。

一、开启慢查询日志

-- 动态开启（重启失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 单位秒，支持小数如 0.5
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 确认
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

持久化到配置文件：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_replica_statements = 1
min_examined_row_limit = 100

min_examined_row_limit = 100 用来过滤"执行快但扫描行数少"的噪音，log_queries_not_using_indexes 在小表多的库上会产生大量日志，建议配合 min_examined_row_limit 一起用。

long_query_time 只统计执行时间，不包括结果集发送到客户端的时间。如果一条 SQL 在数据库里 50 ms 执行完，但要返回 10 万行、网络传输花了 3 秒，慢日志不会记录它——这类问题要看应用侧的调用耗时，或者用 performance_schema 的 events_statements_summary_by_digest 对比 SUM_ROWS_SENT。

注意：long_query_time 是会话级变量，修改后只对新连接生效。SET GLOBAL 后需要重新连接或用 SET SESSION 才能在当前会话看到新值。

二、读慢日志

一条典型的慢日志记录：

# Time: 2026-10-20T09:12:33.512345+08:00
# User@Host: app[app] @ 10.0.3.21 []  Id: 88231
# Query_time: 4.218831  Lock_time: 0.000142 Rows_sent: 10  Rows_examined: 998213
SET timestamp=1792545153;
SELECT * FROM t_order WHERE DATE(created_at) = '2026-10-01' ORDER BY id DESC LIMIT 10;

关键字段：

  • Query_time：总耗时，包含锁等待与网络。
  • Lock_time：锁等待时间，占比高说明是锁冲突而非扫描问题。
  • Rows_sent：返回行数。
  • Rows_examined：扫描行数。Rows_examined / Rows_sent 是判断索引效率的核心比值，超过 1000 基本可以确定索引有问题。

三、聚合分析

单条日志没有统计意义，需要按 SQL 指纹聚合。pt-query-digest 是 Percona Toolkit 中最常用的工具：

# 安装
sudo apt-get install percona-toolkit   # Debian/Ubuntu
sudo yum install percona-toolkit       # RHEL/CentOS

# 分析最近 1 小时的慢日志
pt-query-digest --since 1h /var/log/mysql/slow.log > /tmp/slow_report.txt

# 按总耗时排序，只看前 10
pt-query-digest --since 1h --limit 10 /var/log/mysql/slow.log

输出开头是总体画像：

# Overall: 12.31k total, 38 unique, 0.09 QPS, 2.41x concurrency
# Time range: 2026-10-20 08:00:00 to 09:00:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          8923s      1s     18s    725ms    2.1s    1.3s    410ms
# Lock time            12s       0    320ms      1ms     4ms     8ms     1ms
# Rows sent          1.2M       0    1000      98      500    120       10
# Rows examine      98.2M      10   1.2M     7.9k     42k    88k      1.2k

2.41x concurrency 说明同一时刻平均有 2.41 条慢 SQL 在跑，Rows examine 的 95 分位是 42k，说明长尾扫描严重。往下是按 Query ID 排序的明细，第一段就是最该优化的 SQL。

8.0 也可以用 performance_schema 实时看：

SELECT DIGEST_TEXT,
       COUNT_STAR,
       ROUND(SUM_TIMER_WAIT / 1e12, 2)   AS total_sec,
       ROUND(AVG_TIMER_WAIT / 1e9, 2)    AS avg_ms,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

注意：8.0 默认 performance_schema 已开启，events_statements_summary_by_digest 只保留一定数量（performance_schema_digests_size，默认 10000）的指纹，超出后归入 OTHER。

四、根因归类

拿到 TOP SQL 后，按下面的顺序判断，能覆盖 90% 的场景。

1. 没走索引

Rows_examined 远大于 Rows_sent，EXPLAIN 显示 type=ALL 或 key=NULL。处理方式见索引失效的各类场景：函数、隐式转换、最左前缀、低区分度。

2. 走了索引但回表太多

type=range 但 rows 很大，Extra 没有 Using index。典型是大范围 SELECT *。优化手段：覆盖索引、减少返回列、分页。

3. 排序与临时表

Extra 出现 Using filesort 或 Using temporary。前者靠联合索引消除，后者靠改写 GROUP BY 或加索引。

4. 锁等待

Lock_time 占 Query_time 比例高。查 performance_schema.data_lock_waits 找到阻塞源，通常是长事务或未提交的 FOR UPDATE。

5. 数据量本身太大

分页深翻、大表全量统计、历史数据未归档。这类问题改 SQL 收益有限，要从分页方式、归档、分表上解决。

6. 连接数与连接池

有时候 SQL 本身很快，接口却慢。此时看 Threads_connected 与 Threads_running：前者远大于后者，说明连接被占着但没在干活，通常是应用侧连接泄漏、或者事务里做了远程调用。连接池的 maximumPoolSize 不是越大越好，超过数据库实例的处理能力只会把排队从应用层挪到数据库层。

SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

Max_used_connections / max_connections 长期接近 1，说明连接数已经是瓶颈；但盲目调大 max_connections 会带来内存与上下文切换开销，每个连接都有独立的会话缓冲区。

五、实战案例：深分页

-- 原始 SQL，LIMIT 1000000, 20
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;

LIMIT offset, n 的语义是"扫描 offset + n 行，丢弃前 offset 行"。offset 越大，扫描越多，即使走主键索引也要回表 100 万次。

改写方案一，延迟关联：

SELECT o.*
FROM t_order o
JOIN (SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20) t ON t.id = o.id;

子查询只查主键，走覆盖索引，避免 100 万次回表。

改写方案二，游标分页（推荐）：

-- 第一页
SELECT * FROM t_order ORDER BY id LIMIT 20;
-- 后续页，传上一页最后一个 id
SELECT * FROM t_order WHERE id > 1000020 ORDER BY id LIMIT 20;

游标分页是 O(1) 定位，但要求排序键唯一且不支持跳页。如果业务必须支持随机跳页，可以限制最大页数（如只允许前 1000 页），超出后引导用户缩小筛选条件——这是电商、日志类产品普遍采用的做法。

还有一类"伪深分页"来自 OFFSET 与 ORDER BY 不匹配：如果排序键不是索引顺序，MySQL 需要先取出所有行做 filesort，再丢弃前 offset 行，代价比按主键排序高出一个量级。所以深分页优化的第一步是确认 ORDER BY 的列有索引，第二步才是延迟关联或游标。

造数据验证：

CREATE DATABASE IF NOT EXISTS slow_demo DEFAULT CHARACTER SET utf8mb4;
USE slow_demo;

CREATE TABLE t_order (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED NOT NULL,
  amount     DECIMAL(10,2)   NOT NULL,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

SET SESSION cte_max_recursion_depth = 10000000;

INSERT INTO t_order (user_id, amount)
WITH RECURSIVE s AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM s WHERE n < 2000000)
SELECT n % 50000, ROUND(RAND() * 1000, 2) FROM s;

ANALYZE TABLE t_order;

-- 对比
EXPLAIN SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;
EXPLAIN SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20;
EXPLAIN SELECT o.* FROM t_order o
JOIN (SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20) t ON t.id = o.id;

六、实战案例：COUNT 与 GROUP BY

-- 慢：统计全表
SELECT COUNT(*) FROM t_order WHERE user_id = 123;

-- 有 idx_user_id 时为 index range scan，仍然要扫所有匹配行
-- 优化：如果只需要"有没有"，用 EXISTS
SELECT EXISTS(SELECT 1 FROM t_order WHERE user_id = 123);

COUNT(*) 在 InnoDB 中必须遍历索引（选最小的二级索引），没有 MyISAM 的元数据快路径。需要精确总数时接受成本，需要近似值时用 SHOW TABLE STATUS 的 Rows（误差可能很大）或维护计数表。

COUNT(*) 与 COUNT(1) 在 8.0 中已被优化为等价，COUNT(列) 则要额外判断 NULL，理论上更慢。真正影响性能的是扫描的索引大小：如果有多个二级索引，优化器会选最小的那个，因此"为 COUNT 单独建一个窄索引"在超大表上是有效手段。

GROUP BY 在 8.0 中默认不再隐式排序（5.7 会按分组列排序），如果业务依赖顺序必须显式写 ORDER BY。这是一个从 5.7 升级到 8.0 后结果顺序变化的高频问题。

-- GROUP BY 无索引会产生临时表
EXPLAIN SELECT user_id, COUNT(*) FROM t_order GROUP BY user_id;
-- 有 idx_user_id 时通常可走索引，Extra 无 Using temporary

七、优化后的验证

改完不能只看"感觉快了"，要用同一份数据对比：

-- 8.0.18+ 看实际耗时
EXPLAIN ANALYZE SELECT ...;

-- 清空缓存做冷读对比（生产慎用）
-- 5.7 可用 SHOW PROFILES
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

SHOW PROFILES 在 8.0.0 起已废弃，8.0 应使用 performance_schema.events_statements_history_long：

SELECT SQL_TEXT, TIMER_WAIT / 1e9 AS ms
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE '%t_order%'
ORDER BY TIMER_WAIT DESC LIMIT 10;

八、常见坑 / 最佳实践

  1. long_query_time 不要设成 10 秒。默认值 10 秒会漏掉绝大多数"不慢但很频繁"的 SQL，它们累计起来才是 CPU 的主要消耗。建议 0.5~1 秒，配合 pt-query-digest 按总耗时排序。
  2. 关注总耗时而不是单次耗时。一条 20 ms 但每秒执行 5000 次的 SQL，总消耗远大于一条 5 秒但每小时 1 次的报表 SQL。
  3. log_queries_not_using_indexes 必须配 min_examined_row_limit，否则小表查询会把日志撑爆，磁盘写满反而引发故障。
  4. 不要在生产直接 EXPLAIN ANALYZE 大查询。它会真正执行，可能拖垮实例。先在从库或影子库验证，必要时加 LIMIT。
  5. 建立慢 SQL 的例行巡检。每天凌晨用 pt-query-digest 生成日报，关注新出现的 Query ID，比等告警更早发现问题。

九、小结

  • 慢日志是起点，Rows_examined / Rows_sent 比值是最快的索引效率判断指标。
  • pt-query-digest 按指纹聚合，看总耗时和 95 分位，不要被单条最大值带偏。
  • 根因按"没走索引 → 回表多 → 排序/临时表 → 锁等待 → 数据量"顺序排查。
  • 深分页用延迟关联或游标分页，后者是 O(1) 定位，但牺牲跳页能力。
  • 优化必须用同一份数据做前后对比，8.0 用 EXPLAIN ANALYZE，5.7 用 SHOW PROFILES（8.0 已废弃）。
【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 性能优化
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入