2021 年我负责一个订单系统。订单表 orders 已经有几百万行。有个"查我的订单"的接口,越来越慢,从几百毫秒一路劣化到好几秒。我看了下它执行的 SQL:WHERE user_id = ? ORDER BY create_time DESC。我想当然地判断:慢,无非是没索引——加一个就好了。我在 user_id 上 CREATE INDEX,信心满满地上线——还是慢。我又想,可能 create_time 也得有索引,再加一个——还是慢。我甚至把 user_id、status、create_time 一人给了一个独立索引,心想这下总该快了吧——查询依然是几秒。我盯着这个慢查询百思不得其解,直到一位老同事让我在 SQL 前面加上 EXPLAIN 看一眼。结果当场傻眼:执行计划里 type 那一列,赫然写着 ALL——全表扫描。我辛辛苦苦建的那几个索引,数据库一个都没用。我盯着这个 type=ALL 想了很久才彻底想明白,第一版错在一个根本的认知上:我以为"查询慢,就是没索引;加上索引,查询就会快"。这句话错得很彻底。它漏掉了索引这件事里最关键的一环——你把索引建出来,和查询真的会去走这个索引,是两回事。索引是数据库可以选择的一条快路,但用不用、用哪一条,由优化器根据你的 SQL 写法来决定。你 SQL 写得不对,索引建得再多,优化器照样把它晾在一边,老老实实去全表扫描。真正的索引优化,核心不是"给慢查询加索引",而是让你的 SQL 写法,能够真正命中你建的索引。这篇文章就把数据库索引梳理一遍:为什么加了索引还是全表扫描、联合索引的最左前缀到底怎么命中、哪些写法会让索引悄悄失效、回表和覆盖索引为什么决定快慢、低区分度的列建索引为什么等于白建,以及范围查询、排序、索引维护成本这些把索引真正用对要避开的坑。
问题背景
先把那次慢查询的现象和我的误判讲清楚,后面所有的优化都是冲着纠正这个误判去的。
现象:一张几百万行的订单表,"查我的订单"接口慢到几秒。我陆续在 user_id、status、create_time 上各建了一个单列索引,查询依然几秒。EXPLAIN 一看,type=ALL——全表扫描,索引一个没走。
我当时的错误认知:"查询慢就是没索引,加上索引就会快;索引建得越多越保险。"
真相:建了索引,不等于查询会用索引。走不走索引,由优化器根据 SQL 的写法决定。在索引列上套函数、发生隐式类型转换、用前导模糊匹配,都会让索引失效;联合索引不遵守最左前缀也用不上;选择性太低的列建索引优化器也不屑于走。索引优化的核心,是让 SQL 写法配得上索引,而不是无脑堆索引。
要把索引用对,需要几块认知:
- 为什么加了索引还全表扫描——建索引和走索引是两回事;
- 最左前缀——联合索引到底按什么规则被命中;
- 索引失效的写法——函数、隐式转换、前导模糊匹配;
- 回表与覆盖索引——为什么
SELECT *会拖慢查询; - 选择性、范围查询、排序、维护成本这些工程坑怎么处理。
一、为什么加了索引还是全表扫描
先把这件最根本的事钉死:索引是一个你建好放在那里的数据结构,但一条具体的查询走不走它,是优化器在执行前现做的决定;你的 SQL 只要写成优化器"没法用索引"的样子,它建得再多也只能干瞪眼,最终还是退回全表扫描。
先把那张订单表和我加的索引摆出来。下面是建表和我当时陆续补上的几个单列索引:
CREATE TABLE orders (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
status TINYINT NOT NULL, -- 0 待付款 1 已付款 2 已完成
amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL
);
-- 我当时的做法:看到慢,就给每个 WHERE 用到的列建一个单列索引
CREATE INDEX idx_user ON orders (user_id);
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_ctime ON orders (create_time);
表里几百万行,索引看起来一应俱全。可那条"查我的订单"——它其实不只按 user_id 过滤,业务上还要只看已付款的、按时间倒序。我当时写成了这样:
-- 反面教材:WHERE 里对 status 套了函数,优化器无法用 idx_status
SELECT id, amount, create_time
FROM orders
WHERE user_id = 10086
AND IFNULL(status, 0) = 1 -- 破绽:索引列被函数包住了
ORDER BY create_time DESC;
-- 在这条 SQL 前加 EXPLAIN,看优化器到底怎么执行它
EXPLAIN SELECT id, amount, create_time FROM orders
WHERE user_id = 10086 AND IFNULL(status, 0) = 1
ORDER BY create_time DESC;
EXPLAIN 的输出,把真相摊在了我面前——它的几个关键列,每一个都该盯紧:
id table type key rows Extra
1 orders ref idx_user 37 Using where; Using filesort
-- type=ref :用上了索引(这里是 idx_user),不是全表扫描
-- key :实际选用的索引名 —— 只走了 idx_user,没走 idx_status
-- rows :预计要扫描的行数,越小越好
-- Extra :Using filesort 表示排序没走索引,在内存/磁盘里另排了一遍
这一看就全明白了。优化器确实用上了 idx_user,把范围从几百万行缩到了 rows=37——这一步没问题。问题出在另外两处:其一,IFNULL(status, 0) = 1 这个写法,把 status 这一列包在了函数里——优化器面对的不再是"status 等于某个值",而是"某个函数的计算结果等于 1"。索引是按列的原始值排好序的,它没法按"IFNULL 之后的值"去查——于是 idx_status 彻底用不上。其二,Extra 里的 Using filesort 说明 ORDER BY create_time 这个排序,数据库是把数据捞出来之后另外排了一遍,没有借助任何索引的天然有序性。我那几个单列索引,在这条 SQL 面前各自为战,谁也帮不上完整的忙。问题的根子清楚了:索引能不能被用上,取决于 SQL 的写法和索引的结构能不能对上;接下来要做的,就是把这两件事一项一项对齐。
二、最左前缀:联合索引到底怎么命中
我犯的第一个结构性错误,是给三个列各建一个单列索引。但这条查询要同时按 user_id 和 status 过滤、再按 create_time 排序——它需要的不是三个零散的索引,而是一个把这三列按顺序组合起来的联合索引。联合索引最关键的规则,叫最左前缀。
-- 删掉零散的单列索引,建一个联合索引
DROP INDEX idx_user ON orders;
DROP INDEX idx_status ON orders;
DROP INDEX idx_ctime ON orders;
-- 列的顺序是精心安排的:等值过滤的列在前,排序的列在后
CREATE INDEX idx_user_status_ctime
ON orders (user_id, status, create_time);
这个联合索引,你可以想象成一本按多列排好序的电话簿:它先按 user_id 排,user_id 相同的再按 status 排,两者都相同的最后按 create_time 排。最左前缀的意思就是:你的查询条件,必须从这个索引最左边的列开始、连续地用,中间不能断。
-- 能命中索引:从最左列 user_id 开始,连续地用
WHERE user_id = 10086; -- 用到第 1 列
WHERE user_id = 10086 AND status = 1; -- 用到前 2 列
WHERE user_id = 10086 AND status = 1
AND create_time > '2021-01-01'; -- 三列全用上
-- 用不上索引:跳过了最左列 user_id,前缀断了
WHERE status = 1; -- 没有 user_id,失效
WHERE status = 1 AND create_time > '2021-01-01'; -- 同样失效
WHERE create_time > '2021-01-01'; -- 最左列没用,失效
为什么必须从最左列开始?道理就藏在那本电话簿里:电话簿先按姓氏排,你知道姓氏,翻起来飞快;可你只知道名字、不知道姓氏,这本按姓氏排的电话簿就帮不上忙——因为同一个名字的人,散落在每一个姓氏下面,你还是得一页页翻。status 在联合索引里是第二列,它的有序性是建立在 user_id 相同的前提下的;你不给 user_id,status 在整张表范围看就是乱的,索引自然用不上。这也顺带解释了列的顺序为什么要精心安排:把等值查询(=)的列放前面、把排序或范围的列放后面,是因为前面的等值列把范围锁死之后,后面的列才是有序可用的。最左前缀讲清楚了,但还有一类更隐蔽的失效——索引列明明用上了,却还是不走索引。
三、让索引失效的写法:函数、隐式转换、前导模糊
就算你老老实实从最左列开始写,索引仍然可能失效。原因是:索引是按列的原始值排好序的,任何在查询时改变了这个列的值或类型的写法,都会让"索引里排好的顺序"和"你要查的东西"对不上。第一类,就是第一节那个坑——在索引列上套函数或做运算:
-- 第一类:对索引列 create_time 套了 DATE() 函数 —— 失效
WHERE DATE(create_time) = '2021-06-01';
-- 第一类:对索引列做了算术运算 —— 失效
WHERE amount + 100 > 1000;
-- 正确:把变形从列移到值那一侧,让索引列保持光秃秃的原始值
WHERE create_time >= '2021-06-01 00:00:00'
AND create_time < '2021-06-02 00:00:00';
-- 第二类:隐式类型转换。假设 phone 列是 VARCHAR,上面有索引
-- 失效:传进来一个数字,数据库被迫把每行 phone 转成数字来比较,
-- 等价于在 phone 列上悄悄套了一个转换函数
SELECT * FROM users WHERE phone = 13800138000;
-- 正确:传字符串,类型与列对齐,索引正常命中
SELECT * FROM users WHERE phone = '13800138000';
这两类失效,根子是同一个:索引列的左边,必须永远是它光秃秃的原始样子——任何要做的变形、计算,都要挪到等号或不等号的右边去。第一类套函数是显式的变形,写出来一眼能看见;第二类隐式类型转换就隐蔽得多:列是字符串、你传了数字,数据库为了能比较,会把列里每一行的值都转成数字——这等价于在列上套了一个转换函数。它特别容易踩,因为它不报错——SQL 照常返回正确结果,只是悄悄地慢。第三类,是前导模糊匹配:
-- 失效:通配符 % 在最前面,索引按前缀排序,帮不上忙
WHERE name LIKE '%android%';
-- 能命中:通配符在后面,本质是一次"前缀范围查询"
WHERE name LIKE 'android%';
LIKE 'android%' 能走索引,是因为它本质是查"以 android 开头"的所有值——索引正是按前缀排序的,这就是一次范围查询。而 LIKE '%android%' 要找"中间含有 android"的值,它可能出现在任何位置,按前缀排好的索引完全无能为力,只能全表逐行匹配。函数、隐式转换、前导模糊讲完了,接下来是一个即使索引命中、也未必快的关键概念。
四、回表与覆盖索引:为什么 SELECT * 拖慢了你
要理解这一节,得先知道一件事:在 InnoDB 里,二级索引(也就是你自己建的那些索引)的叶子节点上,只存了"索引列的值"和"主键 id",并不存整行数据。所以当你用二级索引查到了几行、但还需要索引里没有的列时,数据库要拿着主键 id,再回到主键索引里去捞整行——这个动作叫回表。
-- 假设有联合索引 idx_user_status_ctime (user_id, status, create_time)
-- 需要回表:要查的 amount 不在索引里,每命中一行都得拿 id 回表捞一次
SELECT id, user_id, status, create_time, amount
FROM orders
WHERE user_id = 10086 AND status = 1;
-- 不需要回表:要查的列(id 是主键,本就在索引里)全在索引上,
-- 数据库从索引里就能直接把结果拼齐 —— 这叫"覆盖索引"
SELECT id, user_id, status, create_time
FROM orders
WHERE user_id = 10086 AND status = 1;
第二条查询要的列,全部都在 idx_user_status_ctime 这个索引里(id 作为主键,本来就跟在每个二级索引项后面)。这种"查询要的所有列,一个索引就全包了"的情况,叫覆盖索引——它完全不需要回表,数据库从索引这一个结构里就把结果拼齐了,快得多。EXPLAIN 的 Extra 列会用 Using index 来明确告诉你:
-- 覆盖索引的查询,Extra 里会出现 Using index
id table type key rows Extra
1 orders ref idx_user_status_ctime 37 Using index
-- 看到 Using index = 这次查询没有回表,全程只读了索引,最快的情况
-- 没有 Using index = 命中索引后仍需逐行回表去主键索引捞数据
这下就能解释,为什么到处写 SELECT * 是个坏习惯了。SELECT * 意味着你要了这一行的所有列,而一个二级索引几乎不可能包含所有列——于是 SELECT * 注定要回表,注定用不上覆盖索引这个优化。如果你的查询命中了几万行,那就是几万次回表,每一次都是一笔额外的随机 IO。正确的习惯是:只 SELECT 你真正需要的列;如果某个高频查询只差一两列就能被索引覆盖,甚至值得专门把那一两列加进联合索引,让它彻底不回表。回表讲清楚了,接下来是一个决定优化器愿不愿意走你索引的概念。
五、索引选择性:给低区分度的列建索引等于白建
还有一种情况:你的索引建对了、SQL 也写对了,优化器看了一眼,却还是选择全表扫描——它不是出 bug,而是算过账之后,觉得走你这个索引并不划算。这背后是一个叫选择性(也叫区分度)的概念:一个列上不同值的数量,占总行数的比例。
-- 算一个列的选择性:不同值的数量 / 总行数,越接近 1 越适合建索引
SELECT
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity
FROM orders;
-- 典型结果:
-- status_selectivity ≈ 0.0000008 (几百万行,status 只有 3 种值)
-- user_selectivity ≈ 0.6 (user_id 区分度高,适合建索引)
这个比值说明了一切。user_id 的选择性很高——给一个 user_id,索引能立刻把范围缩到很小一撮,走索引非常划算。而 status 只有 3 种值,选择性低到尘埃里——假设订单里一半都是"已完成",那 WHERE status = 2 会命中全表一半的行。优化器一算账:走索引要先扫索引、再对这一半的行逐行回表,这一来一回的随机 IO,还不如直接顺序扫一遍全表来得快——于是它果断放弃你那个 status 索引。这就是为什么给"性别""状态""是否删除"这类只有寥寥几个值的列单独建索引,基本等于白建。它给优化器的指导意见结论很实在:
-- 反面:给低选择性的列单独建索引,优化器多半不会走它
CREATE INDEX idx_status ON orders (status); -- 基本白建
-- 正面:低选择性的列,要放进联合索引,且通常排在高选择性列的后面
-- 让高选择性的 user_id 先把范围缩小,status 在小范围里再做精筛
CREATE INDEX idx_user_status ON orders (user_id, status);
低选择性的列不是不能用,而是不能单独用——把它放进联合索引、排在高选择性的列后面,让前面的列先把范围狠狠缩小,它在那一小撮里做精筛,就有价值了。选择性讲完,最后是几个绕不开的工程坑。
六、工程坑:范围查询、排序、索引维护成本
五块认知之外,还有几个工程坑,不处理就会在生产上栽跟头。坑 1:范围查询会"截断"联合索引后面的列。在联合索引里,一旦某个列用了范围查询(>、<、BETWEEN、LIKE 前缀),那么它后面的列就无法再用于索引查找了。
-- 联合索引 (user_id, create_time, status)
-- create_time 用了范围查询,它【后面】的 status 就用不上索引精筛了
WHERE user_id = 10086
AND create_time > '2021-01-01' -- 范围查询,从这里开始截断
AND status = 1; -- status 只能在结果里逐行过滤
-- 解决:把等值列排在范围列前面 —— 索引顺序改成 (user_id, status, create_time)
-- 这样 user_id、status 都走等值,create_time 最后再做范围,索引利用最充分
道理还是那本电话簿:create_time 一旦是个范围,它命中的就是一段连续区间,而在这段区间里,status 的值是乱的——所以把等值的列尽量往前排、把范围的列放到最后,是联合索引列序设计的铁律。坑 2:利用索引的有序性,消灭 filesort。第一节那个 Using filesort 就是排序没走索引。索引本身是有序的,只要 ORDER BY 的列跟在索引里、顺序也对得上,排序就能直接借用索引的天然顺序,一行不用额外排:
-- 联合索引 (user_id, status, create_time)
-- user_id、status 等值锁定后,create_time 在索引里本就是有序的,
-- 所以 ORDER BY create_time 直接借用索引顺序,EXPLAIN 里不再有 filesort
SELECT id, amount, create_time FROM orders
WHERE user_id = 10086 AND status = 1
ORDER BY create_time DESC;
坑 3:索引不是越多越好,它有实打实的维护成本。每建一个索引,就多一份要维护的有序结构:每次 INSERT、UPDATE、DELETE,数据库都要同步更新所有相关索引。索引越多,写入越慢,占用的磁盘空间也越大。一张表上堆十几个索引,写性能会被明显拖累。坑 4:小心冗余索引。如果你已经有了联合索引 (a, b, c),那么再单独建一个 (a) 的索引就是冗余的——因为按最左前缀,(a, b, c) 已经覆盖了单查 a 的场景。坑 5:优化器靠"统计信息"估算成本,统计信息过期会让它误判。优化器决定走不走索引,依赖它对表里数据分布的统计;表数据变化很大后,这份统计可能过期失真,导致它选错索引——必要时可以手动 ANALYZE TABLE 刷新它。下面这张图,把"一条查询要不要、能不能走索引"的判断链路串起来:
关键概念速查
| 概念 / 手段 | 说明 |
|---|---|
| 建索引 ≠ 走索引 | 索引是可选的快路,走不走由优化器按 SQL 写法决定 |
| EXPLAIN | 查执行计划,看 type、key、rows、Extra 判断索引有没有被用上 |
| type=ALL | 全表扫描,索引一个没走,大表上这是性能红灯 |
| 最左前缀 | 联合索引必须从最左列开始连续使用,跳列则前缀断裂失效 |
| 函数导致失效 | 索引列被函数或运算包住,优化器无法用索引,变形要挪到值那侧 |
| 隐式类型转换 | 列类型与传入值不一致,数据库逐行转换列值,等价于套函数,索引失效 |
| 前导模糊失效 | LIKE 通配符在最前面用不上索引,在后面是前缀范围查询可命中 |
| 回表 | 二级索引只存索引列和主键,缺的列要拿主键回主键索引再捞一次 |
| 覆盖索引 | 查询列全在索引里无需回表,EXPLAIN 显示 Using index 最快 |
| 选择性 | 列的不同值占总行数的比例,太低的列单独建索引优化器不走 |
避坑清单
- 慢查询先用 EXPLAIN 看执行计划,别凭感觉加索引,看 type 是不是 ALL。
- 建了索引不等于走索引,走不走由优化器按你的 SQL 写法来决定。
- 多个条件一起查,要建联合索引,而不是给每列各建一个单列索引。
- 联合索引遵守最左前缀,查询条件要从最左列开始连续用,不能跳列。
- 别在索引列上套函数或做运算,变形要挪到等号另一侧,保持列是原始值。
- 列类型和传入值类型要对齐,字符串列别传数字,否则隐式转换让索引失效。
- LIKE 查询通配符别放最前面,前导模糊匹配用不上索引。
- 少用 SELECT *,只取需要的列,让高频查询尽量被覆盖索引命中不回表。
- 低选择性的列如状态性别别单独建索引,放进联合索引并排在高选择性列后。
- 索引不是越多越好,每个索引都拖慢写入占用空间,及时清理冗余索引。
总结
回头看那次"加了索引查询还是慢、EXPLAIN 一看根本没走索引"的事故,以及我后来在索引优化上接连踩的坑,最该记住的不是某一条 CREATE INDEX 语句,而是我动手前那个想当然的判断——"查询慢就是没索引,加上索引就会快"。这句话错在它把索引当成了一个"装上就生效的开关"。我以为优化是一个非常机械的动作:哪里慢,就往哪里怼一个索引。可索引根本不是开关——它是一个你提前备好的数据结构,而用不用它、怎么用它,是数据库的优化器在每一次查询时,根据你这一条 SQL 的具体写法,现场算账算出来的决定。索引优化这件事想清楚的,正是这个:它表面上是在"建索引",本质上是在让你的 SQL 写法,和你建的索引结构严丝合缝地对上。索引建得对不对,只是一半;SQL 写得能不能命中它,是另一半,而且常常是被忽略的那一半。
所以做索引优化,真正的工程量不在"CREATE INDEX"那一行建索引上。那一行,任何数据库教程的第一章就教完了。真正的工程量,在于你要能站在优化器的角度,去看自己写的每一条 SQL:这个 WHERE 条件,从最左列开始连续用了吗?这个索引列,有没有被我无意间套上一个函数?我传进去的值,类型和列对得上吗?我 SELECT 的这些列,能不能被一个索引全覆盖、省掉回表?我建索引的这个列,选择性到底高不高、值得不值得优化器走它?这篇文章的几节,其实就是顺着这条思路展开的:先用 EXPLAIN 看清"到底走没走索引",再用最左前缀理解联合索引怎么命中,然后是函数、隐式转换、前导模糊这些悄悄让索引失效的写法,接着是回表与覆盖索引决定快慢的细节,最后是选择性、范围查询、排序、维护成本这几个把索引真正用扎实的工程考量。
你会发现,数据库索引的思路,和现实里怎么用一本字典查字完全相通。字典之所以能秒查,是因为它按部首、按拼音事先排好了序——这就是索引。可"字典排好了序",和"你能用上这个序",是两回事。你知道一个字的拼音,顺着音序表哗哗就翻到了(这是命中索引);你只知道这个字长什么样、不知道读音,音序表就帮不上忙,你只能改用部首、甚至一页页翻(这是索引失效、全表扫描)。你查一个多音字,得先定一个读音才能往下查(这是最左前缀)。你查到了字、词条里正好就有你要的解释,一步到位(这是覆盖索引);可词条里只写了"见某页",你还得再翻到那一页(这是回表)。一本字典的价值,从来不只在于它排得有多整齐,而在于你查字的方法,能不能顺着它排好的那个序走下去。
最后想说,索引用得对不对,差距永远不会在数据量小的时候暴露——表里只有几千行的时候,走不走索引,查询都是一眨眼的事,你甚至会觉得索引可有可无。它只在表悄悄涨到几百万、几千万行之后才显形。那时候它会用最难堪的方式给你结账:做不好,你会像我一样,明明建了一堆索引,却看着一条本该毫秒级的查询慢成几秒,看着 EXPLAIN 里刺眼的 type=ALL,看着数据库 CPU 被一片全表扫描拖到报警;而做对了,无论表里是几百万行还是几千万行,那条查询都能顺着索引精准地跳到它要的那几十行上,毫秒返回,稳得像表里只有几千行一样。所以别等查询慢成事故才去翻 EXPLAIN,在你写下每一条会被高频执行的 SQL 时就该想清楚:我这个写法,优化器看得懂、用得上我建的索引吗?我的 WHERE 和 ORDER BY,和索引的列、顺序对得齐吗?这几个问题都有了答案,你的索引才不只是"建了就放心"的心理安慰,而是一套无论数据怎么涨都稳得住的查询性能保障。
—— 别看了 · 2026