一条慢 SQL 打满数据库:那些让你索引白建的失效陷阱

那天下午的告警来得猝不及防:接口大面积超时,数据库 CPU 几乎贴着 100% 不下来。我第一反应是"是不是流量突增",但看监控,QPS 跟平时没什么两样。真正反常的是一条查询——平时它稳定在几毫秒,那天却要跑十几秒,而且越积越多,把连接池里的连接一个个拖死,最后整个库被它一条 SQL 拽进了泥潭。

我把那条 SQL 拎出来,在它前面加了个 EXPLAIN,结果一行字让我心里咯噔一下:type: ALL。全表扫描。这张表几百万行,每来一次查询,数据库就老老实实从头到尾翻一遍。可问题是——我明明给那个查询条件的字段建了索引啊。索引白纸黑字地躺在那儿,查询却对它视而不见,宁可去全表扫描。那一刻我才真正意识到:建了索引,和"查询会用上索引",完全是两回事。

顺着这条没走索引的慢 SQL 查下去,根因是一个我自己都没注意到的小改动:有人在那个字段上套了一层函数。就这么一层,索引瞬间失效。这篇就从这条打满数据库的慢查询讲起,把 MySQL 的索引与查询优化讲透:怎么用 EXPLAIN 看穿一条查询到底走没走索引、索引失效的那几个经典陷阱(函数包裹、隐式类型转换、前导模糊匹配、最左前缀)、联合索引和覆盖索引怎么省掉回表、深分页为什么越翻越慢又该怎么治——以及一套"建了索引就一定要确认它真的生效"的排查习惯。

先认清:索引失效的这几个经典场景

在拆解之前,先把那些"索引建了却用不上"的典型场景摆出来。它们的共同点是:你以为查询会走索引,优化器却悄悄选择了全表扫描,而且不报错、不提示。

这张表的共同主题是:索引是一棵按"原始值"排好序的 B+ 树,任何让查询条件"对不上这棵树的排序"的操作,都会让它失效。函数改了值、类型转换改了值、前导 % 让前缀无从定位、最左前缀没满足让排序对不上——本质都是同一件事。下面先把诊断这一切的工具 EXPLAIN 讲清楚,这是所有慢查询排查的起点。

第一件事:索引为什么快,失效又为什么慢

要理解失效,得先知道索引凭什么快。MySQL(InnoDB)的索引是一棵 B+ 树:数据按索引列的值有序组织,查找时像查字典一样层层缩小范围,几百万行也只需要三四次磁盘 IO 就能定位。而全表扫描(type: ALL)则是把每一行都读出来逐个比对。两者的差距,画出来一目了然:

注意那条虚线——它列的就是把查询从"几毫秒"推向"十几秒"的那几个开关。它们的共性在于:都破坏了"查询条件"和"B+ 树有序结构"之间的对应关系。索引列上的值是原样存进树里的,一旦你在查询时给这个列套了函数、做了运算、或让它发生类型转换,数据库就没法再拿这棵按原值排序的树去定位了——只好退回最笨的办法:全表扫描。理解了这个底层原理,后面每一种失效场景就都不再是需要死记硬背的"规则",而是这条原理的自然推论。下一节,先学会用 EXPLAIN 把这一切看在眼里。

第二件事:学会读 EXPLAIN,慢查询才有抓手

排查慢查询,第一步永远是 EXPLAIN——它让数据库把"我打算怎么执行这条查询"摊开给你看,不用猜。先看那条出事的查询被 EXPLAIN 之后的样子:

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

-- 输出里最该盯住的几列:
-- +----+-------+------+---------+---------+----------+-------------+
-- | id | type  | key  | key_len | rows    | filtered | Extra       |
-- +----+-------+------+---------+---------+----------+-------------+
-- |  1 | ALL   | NULL | NULL    | 3271044 |    10.00 | Using where |  ← 全表扫描!
-- +----+-------+------+---------+---------+----------+-------------+

这几列里,有四个是排查的关键信号,记住它们的含义,你就能一眼看出一条查询健不健康:

type 这一列有个大致的效率阶梯,从好到坏是:const > eq_ref > ref > range > index > ALL。看到 ALL,基本就等于"这条查询在全表扫描",在大表上几乎必慢;看到 key 是 NULL,说明你建的索引一个都没派上用场。而 Extra 里如果出现 Using filesort(排序没用上索引、得在内存/磁盘里另外排)或 Using temporary(用了临时表),也是常见的性能元凶。把这几列连起来读,慢查询的病灶就藏不住了。下面就用 EXPLAIN 当放大镜,逐个揪出让索引失效的元凶。

第三件事:元凶之一——别在索引列上套函数

我那次的根因,正是有人在 created_at 这个建了索引的字段上,套了一层日期函数:

-- 反例:在索引列上用函数,索引彻底失效 → type: ALL
SELECT * FROM orders
WHERE DATE(created_at) = '2026-05-29';
-- B+ 树里存的是完整的 datetime 原值,而你比较的是"被 DATE() 加工后的值",
-- 数据库没法用这棵按原值排序的树去定位,只能全表扫一遍再逐行算 DATE()

道理就是前面那条原理的直接推论:索引存的是列的原始值,你一旦在列上做任何加工(函数、+ - * / 运算、字符串拼接),比较的就不再是原始值,树也就用不上了。正确的做法是把加工挪到"常量那一侧",让索引列保持"裸"的状态:

-- 正解一:改写成范围查询,索引列保持原样 → 能走索引
SELECT * FROM orders
WHERE created_at >= '2026-05-29 00:00:00'
  AND created_at <  '2026-05-30 00:00:00';

-- 正解二(MySQL 5.7+):如果非要按表达式查,可建"函数索引/生成列索引"
-- ALTER TABLE orders ADD INDEX idx_created_date ((DATE(created_at)));

记住这条铁律:WHERE 条件里,索引列左边要"干净"——不包函数、不做运算、不被转换。需要按某种加工后的值频繁查询时,要么把逻辑改写成对原始列的范围查询(像正解一),要么干脆为那个表达式专门建一个函数索引(正解二),而不是放任查询在几百万行上反复做无用功。

第四件事:最隐蔽的元凶——隐式类型转换

函数包裹是"看得见"的失效,而隐式类型转换是"看不见"的——它不需要你写任何函数,只要类型对不上,数据库就在背后偷偷给你套了一层转换。最经典的场景:一个 varchar 类型的字段,你用数字去查它:

-- 表结构:phone 是 varchar(20),建了索引
-- 反例:用数字字面量查字符串列,触发隐式转换 → 索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- MySQL 的规则是:字符串和数字比较时,把字符串转成数字。
-- 等价于 WHERE CAST(phone AS DOUBLE) = 13800138000 —— 列上又套了"函数",失效!

这个坑特别阴,因为代码层面常常是 ORM 或拼 SQL 时,把一个本该是字符串的值当数字传了进去,人眼极难发现,只有 EXPLAIN 会告诉你 type: ALL。修法就是让两边类型严格一致:

-- 正解:给字符串列传字符串,类型对齐,索引正常生效
SELECT * FROM users WHERE phone = '13800138000';

反过来,如果字段是 int 而你传了字符串(WHERE id = '123'),MySQL 会把字符串转成数字——转换发生在常量那一侧,索引反而不失效。所以核心规律是:转换一旦落在"索引列"那一侧,索引就废了;落在常量侧则无妨。实践中最稳妥的做法,就是从应用层就保证传给数据库的值,类型和列定义完全匹配。

第五件事:联合索引的最左前缀,和省掉回表的覆盖索引

单列索引讲完,再说实战中更常用的联合索引(复合索引)。假设我们建了 idx(user_id, status, created_at) 这个三列联合索引,它的关键规则是"最左前缀匹配":查询条件必须从最左列开始、连续地用,索引才能生效。

-- 索引:idx(user_id, status, created_at)

-- ✅ 能用上(从最左列开始,连续)
WHERE user_id = 1;
WHERE user_id = 1 AND status = 'paid';
WHERE user_id = 1 AND status = 'paid' AND created_at > '2026-01-01';

-- ❌ 用不上 / 用不全(跳过了最左列 user_id)
WHERE status = 'paid';                      -- 跳过 user_id,整个索引用不上
WHERE user_id = 1 AND created_at > '...';   -- status 断档,created_at 这段用不到

把它想象成查字典:联合索引是先按 user_id 排,user_id 相同再按 status 排,再按 created_at 排。你不能跳过 user_id 直接按 status 找,就像字典里不能跳过首字母直接按第二个字母查。所以建联合索引时,列的顺序至关重要——把最常用作等值过滤、区分度最高的列放在最左边。

联合索引还有一个常被忽略的威力:覆盖索引(covering index),它能省掉"回表"这个隐藏开销。InnoDB 的二级索引叶子节点存的是主键值,如果你 SELECT 的列索引里没有,数据库还得拿着主键再去主键索引里捞一次完整行——这就是"回表"。但如果你要的列全都在索引里,就不必回表了:

-- idx(user_id, status, created_at)
-- ✅ 覆盖索引:要的列都在索引里,EXPLAIN 的 Extra 会显示 Using index,无需回表
SELECT user_id, status, created_at FROM orders WHERE user_id = 1;

-- ❌ SELECT *:要了一堆索引外的列,每命中一行都要回表捞一次
SELECT * FROM orders WHERE user_id = 1;

这也是"少写 SELECT *"这条老建议背后的硬道理之一:只取你真正需要的列,不仅省网络传输,还可能让查询直接被索引覆盖、彻底免掉回表。EXPLAIN 里看到 Extra: Using index,就是覆盖索引生效的标志。

第六件事:深分页,为什么越翻到后面越慢

最后一个高频翻车点是深分页。LIMIT 1000000, 20 看起来只取 20 行,却慢得离谱:

-- 反例:深分页。数据库要先扫描+丢弃前 100 万行,才拿到要的 20 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- LIMIT offset, n 的语义是"定位并跳过 offset 行,再取 n 行"——
-- offset 越大,被白白扫描又丢弃的行越多,越翻越慢

问题的本质是 OFFSET 必须先把前面那 100 万行都扫出来再扔掉。两种常见优化:

-- 优化一:延迟关联。先用覆盖索引快速定位到那 20 个主键,再回表取完整行
SELECT * FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
  ON o.id = t.id;

-- 优化二(更优):书签/游标分页。记住上一页最后一条的 id,直接跳过去
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 不再用 OFFSET,而是用 WHERE 走索引直接定位,无论翻到多后面都一样快

书签分页(基于上一页最后一个值往后取)是深分页的最优解——它把"跳过 N 行"换成了"从某个值开始取",彻底利用了索引的有序性,代价是不能随机跳页(只能上一页/下一页)。但对于"无限下拉加载"这类场景,它几乎是唯一正确的做法。下面这张图,把慢查询的排查思路收个尾:

几条可以直接抄走的铁律

1. 建了索引 ≠ 用上了索引。上线前、改完 SQL 后,用 EXPLAIN 确认 type 不是 ALL、key 不是 NULL。
2. WHERE 里索引列左侧要"干净"。不套函数、不做运算、不让它发生隐式类型转换——任何加工都会让索引失效。
3. 给字段传值,类型严格对齐列定义。varchar 列传字符串,int 列传数字,杜绝隐式转换这个"隐形杀手"。
4. 联合索引遵守最左前缀。从最左列开始连续使用;建索引时把高频等值、高区分度的列放最左。
5. 能覆盖就别回表,能不写 SELECT * 就别写。只取需要的列,争取 Extra: Using index。
6. 深分页用书签分页或延迟关联,别用大 OFFSET。
7. like 模糊查询别以 % 开头。'abc%' 能走索引,'%abc' 不能;真要全文/前后模糊,考虑全文索引或搜索引擎。

那些关于索引的常见误解

误解一:"索引越多越好。"——大错。每个索引都要占空间,更要命的是:每次 INSERT/UPDATE/DELETE 都得同步维护所有相关索引,索引越多,写入越慢。索引是用"写的代价"换"读的速度",该建在真正高频、且区分度高的查询条件上,而不是给每个字段都来一个。

误解二:"在性别、状态这种字段上建索引能加速。"——这类字段区分度太低(性别就两三个值),一个值就对应了半张表,优化器算下来还不如全表扫,多半会主动放弃这个索引。区分度低的列单独建索引,基本是浪费;它更适合作为联合索引里靠后的一列。

误解三:"EXPLAIN 显示走了索引,就一定快。"——不一定。还要看 rows(预估扫描行数)大不大、有没有 Using filesort/Using temporary、是不是大量回表。走索引只是及格线,扫描行数少、能覆盖、不排序、不回表,才是真正的快。EXPLAIN 要整体读,不能只看一眼 key 非空就放心。

别等告警:把慢查询日志变成常态监控

我那次之所以被动,本质是因为没人盯着慢查询——直到它把 CPU 打满、接口雪崩,才被动地知道出了事。其实 MySQL 自带的慢查询日志,几乎是零成本的预警:开启它,把超过某个阈值的查询全都记下来,定期回顾,就能在问题酿成事故前把它揪出来。

-- 开启慢查询日志,记录执行超过 1 秒的查询(可按需调小)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
-- 顺手把"没用上索引的查询"也一并记下来,这正是本文主角
SET GLOBAL log_queries_not_using_indexes = ON;

光有日志还不够,得有趁手的工具去归纳。生产里最常用的是 mysqldumpslow 或 Percona 的 pt-query-digest——它们能把成千上万条慢查询按"查询模板"聚合,告诉你"哪一类 SQL 累计耗时最多、被调用了多少次、平均多慢"。很多时候真正拖垮库的,不是某一条偶发的巨慢查询,而是一条单看还行、但每秒被调用几千次的"温水煮青蛙"查询——这种只有靠聚合统计才看得出来。

把"慢查询日志 + 定期 digest 回顾"做成一道例行流程,你对数据库的姿态就从"出事后救火"变成了"事前体检"。这和给应用接监控、看 P99 延迟是一个道理:长跑的系统,它的查询性能该像 CPU、内存一样被持续盯着,而不是等它把整个库拖垮那天,你才第一次去 EXPLAIN 它。

再补一点:前缀索引与索引选择性

还有一个实战中很实用、却常被忽略的细节:对很长的字符串列(比如一段 URL、一个长 token)建索引时,没必要把整列都索引进去——那样索引会非常臃肿。可以只索引前缀的若干个字符:

-- 只对 url 列的前 20 个字符建索引,索引体积大幅缩小
ALTER TABLE logs ADD INDEX idx_url_prefix (url(20));

前缀长度的选择是一门权衡:取太短,区分度不够(很多行前缀都一样,索引筛选效果差);取太长,又失去了节省空间的意义。判断办法是算一下不同前缀长度下的"选择性"(不同值的数量 / 总行数),找到一个选择性接近完整列、长度又尽量短的拐点。这背后还是那个贯穿全文的关键词——选择性(区分度):一个索引值不值得建、该建多长,根本上取决于它能把数据筛得多干净。

一个延伸:优化器偶尔也会"选错"索引

前面都在讲"怎么让查询能走索引",但偶尔会遇到一种更让人困惑的情况:索引明明建对了、条件也写干净了,优化器却选了另一个不太好的索引,或者干脆又退回全表扫描。这通常和统计信息不准有关——优化器是靠表的统计信息(各列大致的数据分布)来估算成本的,如果这些信息因为大批量增删而过期,它就可能算错账、选错路。

遇到这种情况,有两个手段。第一是更新统计信息,让优化器重新算:

ANALYZE TABLE orders;   -- 重新采集统计信息,多数"选错索引"会自愈

第二,在你确实比优化器更懂数据分布的极少数场景下,可以用 FORCE INDEX 强制指定:

SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 1 AND status = 'x';

但 FORCE INDEX 要慎用——它是把优化器的判断写死了,一旦将来数据分布变了、或表结构调整了,这条被你钉死的执行计划反而可能成为新的慢查询。所以它更像应急的"创可贴",而非长久之计。绝大多数时候,优先 ANALYZE TABLE 让优化器自己回到正轨,才是更稳妥的做法。记住:优化器是你的队友,大多数时候它比手动指定更聪明,真要跟它对着干之前,先确认你是不是真的比它更了解这张表。

写在最后

那次事故的修复,最后只是把 WHERE DATE(created_at) = '...' 改回了 created_at >= '...' AND created_at < '...' 的范围写法——一行 SQL 的改动,查询从十几秒回到了几毫秒,CPU 曲线肉眼可见地塌了下去。但它给我留下的习惯,比这行改动值钱得多:从那以后,凡是写下或改动一条会跑在大表上的查询,我都会顺手在前面加个 EXPLAIN 看一眼,确认它真的走了我期望的索引。

这件事说到底，是一个认知的转变:"我建了索引"是一种良好的愿望,"EXPLAIN 显示 type=ref、key 是我那个索引、rows 只有几行"才是事实。数据库不会因为你建了索引就感激你、就一定用它——它只认那个冰冷的成本估算,只认查询条件和 B+ 树的有序结构对不对得上。你写的每一个函数、每一次类型不匹配、每一个跳过的最左列,它都看在眼里,然后默默地、不动声色地退回去全表扫描,等着某个流量高峰把这笔欠账连本带利地还给你。

所以,把 EXPLAIN 当成写查询的"拼写检查"吧——它就一行,几秒钟,却能让你在上线前就看清:这条查询到底是在走索引的高速路,还是在几百万行里徒步。索引这门功课,会建只是入门,会用 EXPLAIN 确认它真的生效,才算真正上道。

最后留个可立刻执行的小动作:挑出你系统里调用最频繁的那几条查询,逐一 EXPLAIN 一遍,看看 type 和 key 是否如你所愿。这件事花不了一杯咖啡的时间,却很可能让你提前发现一条正在悄悄全表扫描、只等流量高峰引爆的慢查询。

—— 别看了 · 2026