MySQL 慢查询从定位到根治:索引失效、深分页、长事务的排查与优化清单

那天下午两点,运营群里炸了:核心经营看板转圈转了快三十秒还没出数,有人刷新了七八次,直接把数据库的活跃连接顶到了上限,后面所有依赖这个库的接口跟着一起超时。我盯着监控里那条几乎贴着 100% 的 CPU 曲线,心里大概有数了——又是某条慢 SQL。我们这套订单分析库跑了四五年,表从几十万行长到了三千多万行,很多当年"跑得挺快"的查询,只是因为数据量小才显得快。数据量一上来,那些写法里埋的雷一个接一个被踩响。

这篇不打算写成"几个人熬了多少天打了多少仗"那种复盘流水账,我更想把它整理成一份能直接照着查的清单:一条慢查询到手,从怎么定位、到八类最常见的反模式怎么识别、再到每一类的正解和它背后的原理,挨个讲清楚。后面这些场景我全都亲手踩过,数字也都是从我们生产库里真实测出来的,不是编的。

现场:一条 SQL 拖垮整块看板

出事的是经营看板首页那个"按城市汇总今日成交"的接口。代码逻辑很朴素:先查出今天所有订单,再在应用层按城市分组求和。问题是订单表 orders 已经三千多万行,而那条查询的 WHERE 写成了 WHERE DATE(created_at) = CURDATE()。就这一个 DATE() 函数,把 created_at 上那条本来好好的索引彻底废掉了,整条查询退化成全表扫描,扫三千万行、回表、再传一大坨数据回应用层。单条 SQL 执行时间从设计期的几毫秒,涨到了线上的 18 秒。

更糟的是并发。看板是给一二十个运营同时看的,18 秒的查询意味着连接被长时间占住,连接池很快被这一类慢查询挤爆,新请求拿不到连接,在等待队列里排队超时,最后表现成"整个系统都挂了"——但根因只是一条没走索引的 SQL。

第一步永远是定位,不是猜

线上出慢查询,最忌讳的就是凭感觉猜"是不是哪条 SQL 慢"。MySQL 自己就能告诉你答案,先把慢查询日志打开,把阈值压到 1 秒(默认 10 秒太松,很多 2~3 秒的查询会漏掉):

# my.cnf —— 打开慢查询日志并把阈值压到 1 秒
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
# 把没走索引的查询也记下来(哪怕它很快),这类是定时炸弹
log_queries_not_using_indexes = 1
# 限速,避免没走索引的小查询把日志刷爆
log_throttle_queries_not_using_indexes = 60

日志开起来之后,别用肉眼一行行翻,用 pt-query-digest 把慢日志聚合成"按总耗时排序的 TOP N",一眼就能看出哪几条 SQL 在拖后腿:

# 用 Percona Toolkit 聚合慢日志,按总响应时间排序
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 报告头部会列出:Query ID、占总耗时百分比、调用次数、平均/95% 耗时
# 我们那次第一名占了全部慢查询耗时的 71%,就是那条 DATE(created_at) 的汇总查询

# 如果一时装不了 pt-query-digest,临时看实时正在跑的慢 SQL:
mysql -e "SELECT id, time, state, LEFT(info, 120) AS sql_snippet
          FROM information_schema.processlist
          WHERE command = 'Query' AND time > 2
          ORDER BY time DESC;"

锁定到具体 SQL 后,真正的判官是 EXPLAIN。它会告诉你这条查询打算怎么执行:走没走索引、扫了多少行、有没有"文件排序"和"临时表"这两个性能杀手。下面这张表是我每次看 EXPLAIN 输出时最先盯的几列,以及它们出问题时的信号:

把这张表记熟,90% 的慢查询你扫一眼 EXPLAIN 就知道病根在哪。下面从我们实际踩过的八类反模式逐个拆,每一类都给出"为什么慢"和"怎么改"。

一、索引失效:明明建了却用不上

这是最高频、也最隐蔽的一类。索引就在那儿,EXPLAIN 的 key 却是 NULL。开篇那条 DATE(created_at) = CURDATE() 就是典型:只要你在索引列上套了函数,或者让它参与运算,B+ 树的有序性就用不上了——因为索引存的是 created_at 的原值,不是 DATE(created_at) 的结果,优化器没法用一个变了形的值去树里做范围查找,只能退化成逐行计算再比较,也就是全表扫描。

索引失效的常见触发方式,我整理成一组对照。左边是会让索引失效的写法,右边是等价但能走索引的写法:

-- ❌ 在索引列上套函数:created_at 索引失效,全表扫描
SELECT city, SUM(amount) FROM orders
WHERE DATE(created_at) = CURDATE()
GROUP BY city;

-- ✅ 改成范围查询,让 created_at 索引可用:用 [今天 0 点, 明天 0 点) 的半开区间
SELECT city, SUM(amount) FROM orders
WHERE created_at >= CURDATE()
  AND created_at <  CURDATE() + INTERVAL 1 DAY
GROUP BY city;

-- ❌ 隐式类型转换:phone 是 varchar,传了数字,等于在列上做 CAST,索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 类型对齐,加引号,索引可用
SELECT * FROM users WHERE phone = '13800138000';

-- ❌ 前导通配符,B+ 树无法用前缀定位,退化成全表扫
SELECT * FROM users WHERE name LIKE '%森';

-- ✅ 改成后缀通配(能用最左前缀);确需前导模糊就交给全文检索/ES
SELECT * FROM users WHERE name LIKE '森%';

还有一类是联合索引的"最左前缀"原则。比如建了 (city, status, created_at) 的联合索引,查询里如果跳过了 city 直接用 status 过滤,这个索引一样用不上。下面这张图把索引能不能命中的判断逻辑画出来,排查时照着走一遍基本不会错:

这一类的根本认知是:索引是"按原值有序排列"的数据结构,任何让列的值发生变形、或破坏有序前提的写法,都会让有序性失效。所以治理思路永远是"把变形从索引列上挪走"——能用范围代替函数就用范围,能对齐类型就别让数据库帮你隐式转换,能把模糊匹配交给专门的检索引擎就别硬塞给 LIKE。

二、深分页:LIMIT 100000, 20 的真实代价

第二类是分页。运营要翻订单列表,翻到第五千页,SQL 是 LIMIT 100000, 20。很多人以为分页只取 20 行应该很快,其实 LIMIT 100000, 20 的真实含义是:先取出前 100020 行,再把前面 100000 行全部丢弃,只留最后 20 行。越往后翻,要扫描和丢弃的行越多,翻到第十万页时已经在扫上百万行,而且每一行还要回表取完整数据,代价巨大。

我们当时实测,orders 表上 LIMIT 20(第一页)只要 3ms,LIMIT 1000000, 20 直接飙到 4.2 秒。两种成熟的解法,延迟关联和游标分页:

-- ❌ 深分页:扫描并丢弃前 100 万行,再回表取 20 行的完整数据
SELECT * FROM orders
ORDER BY id
LIMIT 1000000, 20;

-- ✅ 解法一:延迟关联(deferred join)
-- 先只在覆盖索引上翻页拿到 20 个 id(不回表),再用这 20 个 id 取完整行
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) AS t ON o.id = t.id;
-- 内层只走主键索引、不回表,丢弃成本远低于直接 SELECT *

-- ✅ 解法二:游标分页(seek method)—— 真正的零丢弃
-- 记住上一页最后一条的 id,下一页从它之后开始取,直接用索引定位
SELECT * FROM orders
WHERE id > 1000000   -- 上一页最后一行的 id
ORDER BY id
LIMIT 20;
-- 不管翻到第几页,都是 O(20),恒定快

游标分页是最优解,翻到第几页都是恒定 20ms,代价是用户体验上没法"跳到任意页码",只能"上一页/下一页"或无限滚动。对订单流水、信息流这类场景完全够用;如果产品死活要页码跳转,就退而求其次用延迟关联,把回表成本降下来。分页这件事的本质是:别让数据库为你"数到"某一页,而要让它"定位到"某一页——前者是 O(N),后者是 O(1)。

三、SELECT *:你以为省事,其实在搬运垃圾

第三类反模式最不起眼,却拖累面广:SELECT *。订单表有四十多个字段,其中好几个是 text 类型的备注和 JSON 快照,单行能到几 KB。可列表页其实只需要订单号、金额、状态、时间这四个字段。一条 SELECT * 把那几十个用不上的字段、连同大字段一起从磁盘读出来、走网络传回应用层,白白搬运了一堆垃圾。

更关键的是,SELECT * 几乎注定要"回表"。InnoDB 的二级索引叶子节点只存索引列和主键,如果你查的字段都在索引里,引擎读完索引就能直接返回,这叫覆盖索引(Covering Index),EXPLAIN 的 Extra 会显示 Using index;而 SELECT * 要的字段索引里没有,就得拿主键再回聚簇索引里捞一遍完整行,这就是回表。回表多一次随机 IO,行数一多代价非常可观。

-- 假设有联合索引 idx_city_status_time (city, status, created_at)

-- ❌ SELECT *：索引里没有的字段逼着引擎逐行回表
SELECT * FROM orders
WHERE city = 'SH' AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

-- ✅ 只取需要的列,并让这些列被索引覆盖,Extra 出现 Using index、零回表
-- 这里把 amount 也加进联合索引末尾,做成覆盖索引:
--   idx_cover (city, status, created_at, amount)
SELECT city, status, created_at, amount FROM orders
WHERE city = 'SH' AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

-- 验证是否覆盖:EXPLAIN 看 Extra 是否为 "Using index"(而不是 "Using index condition")
EXPLAIN SELECT city, status, created_at, amount FROM orders
WHERE city = 'SH' AND status = 'PAID'
ORDER BY created_at DESC LIMIT 50;

这一改,那个列表接口从 320ms 降到 35ms,降幅近十倍,全靠"少搬数据 + 覆盖索引免回表"。这一类的本质认知是:查询的成本不只在"扫多少行",还在"每行搬多少字节"和"要不要回表"。把 SELECT * 当成默认写法,等于默认接受最差的 IO 模式。明确列出字段,既能利用覆盖索引,也能在表结构变更时不被新增的大字段悄悄拖慢。

四、N+1 查询:循环里藏着的一千次往返

第四类是 ORM 用户最容易中招的 N+1。代码读起来人畜无害:先查出一页 50 条订单,然后循环遍历,在循环体里根据 user_id 逐条去查用户名。结果就是 1 次查订单 + 50 次查用户 = 51 次数据库往返。每次往返单独看都只有 1~2ms,但 50 次串起来,加上网络 RTT,整个接口就拖到了三四百毫秒;要是页大小调到 200,直接破秒。

-- ❌ N+1：1 次查订单
SELECT id, user_id, amount FROM orders WHERE city = 'SH' LIMIT 50;
-- 然后应用层 for 循环里,对每个 user_id 各发一条:
SELECT name FROM users WHERE id = ?;   -- 这条被执行了 50 次!

-- ✅ 解法一:批量 IN,把 N 次合并成 1 次
-- 应用层先收集所有 user_id,再一次性查回来,内存里建 map 关联
SELECT id, name FROM users WHERE id IN (101, 102, 103, ...);

-- ✅ 解法二:能在 SQL 层 JOIN 就 JOIN,一趟取齐
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.city = 'SH'
LIMIT 50;

到底用 IN 还是 JOIN,取决于关联数据是否容易缓存、以及 JOIN 后结果集会不会膨胀。我们的经验是:列表展示这种"主表分页 + 附带几个关联字段"的场景,JOIN 通常最干净;而当关联对象本身有热点缓存(比如用户信息在 Redis 里)时,批量 IN 配合缓存反而更省数据库。N+1 的本质是把"集合操作"错误地拆成了"逐元素操作",而数据库最擅长的恰恰是集合操作。识别信号也很简单:只要你在应用代码的循环体里看到数据库查询,几乎可以断定有 N+1。

五、count(*) 与分页总数:最容易被忽视的全表扫

第五类藏在分页的"总条数"里。前端分页控件要显示"共 1280 万条,第 3 页",于是每次翻页都跟着一条 SELECT COUNT(*) FROM orders WHERE ...。在没有合适过滤条件、或过滤条件走不了索引时,COUNT(*) 就是一次实打实的全表/全索引扫描,三千万行数过去,轻松上秒。更尴尬的是,这个总数翻页时根本不变,却每页都重算一遍。

-- ❌ 每次翻页都精确 count 一遍全表,纯属浪费
SELECT COUNT(*) FROM orders WHERE status = 'PAID';

-- ✅ 思路一:总数与数据分离,只在第一页算一次,后续翻页复用(缓存到 Redis/会话)

-- ✅ 思路二:不需要精确值时,用统计信息估算,毫秒级返回
SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders';
-- 注意:TABLE_ROWS 是估算值,适合"大约一千多万条"这种展示,不适合精确对账

-- ✅ 思路三:产品层面把"共 N 条/跳到任意页"改成游标式"加载更多",
--          从根上去掉对总数的依赖(配合第二节的游标分页最香)

这里要破除一个执念:用户真的需要"精确到个位的总数"吗?绝大多数列表场景,展示"约 1280 万条"和"12,803,418 条"对用户毫无差别,但后者的代价是每次翻页一次全表扫描。把"精确总数"降级为"估算总数"或"干脆不显示总数",是性价比极高的优化。真正需要精确计数的对账场景,则应该走离线统计或维护一张计数汇总表,而不是在在线查询的热路径上实时 COUNT。

六、长事务与锁等待:一个忘了提交的事务能拖垮一片

第六类是事务。我们出过一次更隐蔽的事故:一个批处理任务开了事务,中间调用了一个外部的对账接口,那个接口偶发性地卡住几十秒,而事务一直没提交。在这几十秒里,这个事务持有的行锁、间隙锁全都不释放,所有想更新同一批订单的请求全部卡在锁等待上,表面看是"更新订单接口集体变慢",根因却是那个迟迟不提交的长事务。InnoDB 里,一个未提交的事务不光占着锁,还会让 undo log 不断堆积、purge 线程没法回收旧版本,连带拖慢整个实例。

长事务的治理有几条铁律:事务里绝不掺杂网络 IO(尤其是调外部接口);事务范围尽量小,只包住真正需要原子性的那几条写;能拆批就拆批,别在一个事务里一次性更新几十万行。下面这几条 SQL 是我们排查锁问题时的固定动作:

-- 看当前有没有跑了很久还没结束的事务(超过 10 秒的拎出来)
SELECT trx_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS run_seconds,
       trx_rows_locked, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10
ORDER BY run_seconds DESC;

-- 看谁在等锁、被谁挡住(MySQL 8.0 的锁等待视图)
SELECT * FROM performance_schema.data_lock_waits;

-- 实在被卡死、影响线上,定位到那个长事务的线程后果断 KILL
-- (先确认它确实是元凶,别误杀正常的大批处理)
KILL ;

顺带说死锁。死锁是两个事务各持一把锁、又互相等对方的锁,谁也不让谁。InnoDB 会自动检测并回滚其中一个(报 Deadlock found),所以死锁本身不致命,真正要做的是降低它的发生概率:让所有事务按同一个固定顺序去加锁(比如永远先锁订单、再锁库存,绝不反过来),死锁就基本绝迹了。这一类的本质认知是:锁的持有时间,等于你事务的存活时间;事务活多久,就把别人挡多久。所以优化锁竞争,十有八九不是去调锁本身,而是去缩短事务。

七、文件排序:ORDER BY 没踩在索引上

第七类是排序。EXPLAIN 的 Extra 里一旦出现 Using filesort,就说明这次排序没能借上索引的有序性,数据库得把结果集捞出来在内存(放不下就落磁盘)里重新排一遍。数据量小时无感,几十万行的结果集做一次磁盘 filesort,延迟能从几十毫秒涨到好几秒。

关键点在于:B+ 树索引本身就是有序的,如果排序字段恰好是索引的顺序,引擎顺着索引读出来就已经是排好的,根本不用再排。所以消除 filesort 的核心,是让 WHERE 的等值条件加上 ORDER BY 的字段,共同构成一个连续的、方向一致的联合索引。

-- 已有索引 idx_city (city)

-- ❌ 按 city 过滤、再按 created_at 排序:过滤能走索引,但排序得 filesort
SELECT id, amount FROM orders
WHERE city = 'SH'
ORDER BY created_at DESC
LIMIT 50;   -- Extra: Using filesort

-- ✅ 把排序字段并进联合索引:idx_city_time (city, created_at)
-- 等值列 city 在前、排序列 created_at 在后,引擎顺着索引读即有序,免排序
ALTER TABLE orders ADD INDEX idx_city_time (city, created_at);
-- 改造后同一条查询:Extra 不再有 filesort,LIMIT 50 直接顺序取前 50 行就停

-- 注意方向:若同时按两列排且方向相反(一个 ASC 一个 DESC),
-- 8.0 之前无法用同一索引消除排序,8.0 起支持「降序索引」可解
ALTER TABLE orders ADD INDEX idx_mix (city, created_at DESC, id ASC);

这里有个很实用的细节:ORDER BY 配 LIMIT 时,如果排序走了索引,引擎取够 LIMIT 的行数就能停下,根本不用扫全部匹配行;而一旦是 filesort,它必须把所有匹配行都捞出来排完才能取前 N 行。也就是说,索引排序和 filesort 在带 LIMIT 的场景下,差距会被进一步放大。排序优化的本质,是"复用索引已经排好的顺序",而不是"让数据库每次现排"。

八、隐式转换与字符集不一致:看不见的全表扫

第八类最坑,因为它"看起来完全正常"。两张表 JOIN,关联字段一边是 utf8mb4、一边是老的 utf8(或者排序规则 collation 不一致),JOIN 时数据库需要先把字符集统一,这个隐式转换会让本该命中的索引失效,JOIN 退化成嵌套全表扫。类似地,前面提过的"varchar 列传数字"也是隐式转换的一种。这类问题在 EXPLAIN 里很难一眼看穿,得留意 Extra 里有没有 Using where 配着异常大的 rows。

-- 排查:看两张表关联列的字符集和排序规则是否一致
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shop'
  AND COLUMN_NAME = 'user_id';
-- 若 orders.user_id 是 utf8mb4_general_ci、users.id 是 utf8_general_ci,JOIN 必失效

-- ✅ 把关联列统一到同一字符集与排序规则(以 utf8mb4 为准)
ALTER TABLE orders
  MODIFY user_id VARCHAR(32)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- ✅ 数值主键就老老实实用整型,别用字符串存数字 id,从根上免掉这类转换

这一类给我的教训是:建表时就把类型和字符集定准,比上线后到处补救便宜得多。关联字段两边类型必须严格一致,数值就用整型、字符串就统一 utf8mb4,别让"反正能存进去"成为偷懒的借口——数据库会用一次次隐式转换,把这笔技术债连本带利地还给你。

八类反模式的优化效果对比

把这八类挨个治理下来,核心几条查询的实测前后对比如下(同一套生产快照数据、同样的硬件上测的,数字取多次执行的中位数):

这张表我贴在团队 wiki 最显眼的地方。它的价值不在那几个具体数字,而在于:几乎每一类的优化幅度都是一到三个数量级——这说明慢查询治理的杠杆极高,一条查询改对,胜过堆十台只读从库。

拿到一条慢查询,照着这棵树走

把上面八类串成一个排查流程,我画成了一棵决策树。线上再遇到慢查询,不用慌,从根节点一路问下去,基本都能落到某一类上:

我们后来定下的几条硬规矩

事故之后,这些规矩进了我们的代码评审 checklist,违反任何一条 PR 直接打回:

1. 任何上线的查询必须先 EXPLAIN,type=ALL 或 key=NULL 一律不准合并,除非是确定的小表全扫且有注释说明。
2. 索引列上禁止套函数、禁止隐式类型转换。日期范围一律用半开区间 >= 当天 AND < 次日,不准 DATE(col)=...。
3. 禁止裸 SELECT *,字段必须显式列出;高频列表查询尽量做成覆盖索引。
4. 分页默认走游标,产品确需页码跳转才用延迟关联;深分页(offset 大)一律评审。
5. 应用代码的循环体里不准出现数据库查询,关联数据用批量 IN 或 JOIN 一次取齐。
6. 事务里禁止任何网络 IO(尤其调外部接口),事务范围只包真正需要原子性的写操作。
7. 关联字段类型与字符集必须严格一致,新表统一 utf8mb4,数值 id 用整型。

这套规矩看着朴素,但正是因为朴素才好执行——它不依赖任何人是"数据库高手",只要照着 checklist 过一遍,绝大多数慢查询在写出来的那一刻就被拦住了,而不是等上线后再来救火。

写在最后

回头看这次事故,真正值钱的不是某一条 SQL 怎么改,而是那套"定位 → 看 EXPLAIN → 归到某一类反模式 → 用对应正解"的方法。数据库的查询优化,八成的问题都集中在这有限的几类反模式上,而它们背后其实是同一组朴素的原理:让索引的有序性能用上、少搬不必要的数据、把集合操作交给数据库而不是在应用层循环、别让事务和锁拖着不放。把这几条原理吃透,再遇到没见过的慢查询,你也能顺着原理推回到根因。

慢查询不是什么高深的玄学,它更像是债——你在写下那条查询时图一时方便埋下的债,数据量涨上来时连本带利地找你还。与其等它在某个流量高峰把系统压垮,不如在每一次写查询、建索引、开事务时,都多问一句"数据量翻十倍,这条还扛得住吗"。把这个问题前置,远比事后通宵救火划算。

—— 别看了 · 2026