2021 年我做一个订单管理后台。有一个运营天天用的功能:按"用户 ID"查出某个用户的全部订单。第一版 SQL 写得很直白:SELECT * FROM orders WHERE user_id = ?。订单表刚上线那会儿,只有几万行,这个查询几十毫秒就返回,飞快。可半年之后,订单表涨到了几千万行,这个查询慢得吓人——一次要五六秒,运营点一下要对着转圈干等,接口频繁超时。我第一反应,几乎是条件反射:"查询慢?加个索引啊。"我在 user_id 这一列上,CREATE INDEX 建了个索引,满心以为这下稳了。结果——还是慢。几乎没有任何改善。我当时是真的懵了:索引不就是用来加速查询的吗?我都加上了,它凭什么还慢?我把那条慢 SQL 拎出来,在前面加了个 EXPLAIN 跑了一遍,盯着那个执行计划看了很久,才终于明白过来:我的查询,压根没用上我建的那个索引。它还在老老实实地全表扫描——一行一行地翻那几千万行数据。我建了索引,数据库却没理它。后来我又陆陆续续踩了一串坑:在某一列上明明建了索引,可只要 SQL 里把这列用函数包一下、或者类型没对齐、或者 LIKE 以 % 开头,那个索引就悄无声息地失效了;我还一度觉得"索引多多益善",在一张表上建了七八个索引,结果写入慢得一塌糊涂。我盯着这些现象想了很久才彻底想明白,我最初错在一个根本的认知上:我以为"加了索引,查询就一定会变快"。我把"建了索引"和"用上了索引"当成了同一件事。可它们根本不是。索引建在表上,只是提供了一种可能;而一次具体的查询到底用不用这个索引,取决于你这条 SQL 怎么写——写法稍微一偏,数据库就用不了它,只能退回全表扫描。所以我真正缺的,不是"多加几个索引",而是搞清楚索引到底是什么、它在什么条件下才生效、又在什么写法下会失效。这篇文章就把它梳理一遍:为什么没索引的查询会慢、索引为什么能加速、怎么用 EXPLAIN 看清查询有没有用上索引、最左前缀原则是怎么回事、索引失效的几种典型写法,以及索引不是越多越好、区分度、覆盖索引这些把索引真正用对要避开的坑。
问题背景
先把那次的现象和我的误判讲清楚,后面所有的设计都是冲着纠正这个误判去的。
现象:一个订单查询接口,WHERE user_id = ?。表小的时候几十毫秒返回;数据涨到几千万行后,一次查询要五六秒。在 user_id 上建了索引,查询几乎没变快。EXPLAIN 一看,查询根本没用上索引,仍在全表扫描。
我当时的错误认知:"查询慢就加索引,只要在那一列上建了索引,查询就一定会变快。"
真相:"建了索引"和"用上了索引"是两件事。索引建在表上只是提供一种可能,一次查询到底走不走索引,取决于这条 SQL 怎么写——列被函数包裹、类型不匹配触发隐式转换、LIKE 以 % 开头、不满足联合索引的最左前缀,任何一种,索引都会失效,查询退回全表扫描。而且索引不是免费的,每个索引都会拖慢写入,不是越多越好。
要把索引用对,需要几块认知:
- 没有索引的查询为什么是全表扫描、为什么慢;
- 索引为什么能加速,它的底层结构带来什么特性;
- 怎么用
EXPLAIN看清一条查询到底有没有用上索引; - 联合索引的最左前缀原则,以及索引失效的几种典型写法;
- 索引不是越多越好、区分度、覆盖索引这些工程坑怎么处理。
一、为什么没有索引的查询会慢
先把这件最根本的事钉死:没有索引,数据库找一行数据的唯一办法,就是从头到尾把每一行都翻一遍。
这个"从头翻到尾"的动作,有个专门的名字,叫全表扫描(full table scan)。设想一下:一张几千万行的订单表,你要找出 user_id = 12345 的那些行。数据库不知道这些行在哪,它只能一行一行地读:读第一行,看 user_id 是不是 12345,不是,读下一行……一直读到最后一行。下面这条 SQL,就是我那个慢查询:
-- 反面教材:user_id 上没有索引时,这条查询会全表扫描。
SELECT * FROM orders WHERE user_id = 12345;
-- 表只有几万行时:全表扫描也就扫几万行,几十毫秒,看不出问题。
-- 表涨到几千万行时:这条 SQL 要逐行翻完几千万行才能确定
-- 哪些匹配 —— 一次查询五六秒,接口必然超时。
这条 SQL 本身的语法没有任何错。它慢,不慢在 SQL,慢在表底下没有索引。它的耗时,和表的行数是成正比的——这就是最要命的地方:表小的时候,全表扫描扫几万行,快得让你完全察觉不到问题;可数据是会一直涨的,涨到几千万行,同样一条 SQL、同样的写法,扫描的代价就翻了上千倍。
所以"没索引的查询慢",本质是一个会随数据增长而不断恶化的问题。它在上线初期潜伏着,等数据涨够了才爆发。要根治它,就得让数据库不必再逐行翻找——这正是索引要做的事。
二、索引为什么能加速
把上一节的痛点接过来:全表扫描慢,是因为数据库没有线索,只能挨个翻。索引,就是给数据库的那条线索。
打个最贴切的比方:一本几百页的书,你想找"索引"这个词在哪一页。没有目录,你只能从第一页一页页翻——这是全表扫描。而书末尾那个按拼音/笔画排好序的索引页,让你能瞬间定位到"索引"这个词,直接告诉你它在第 87 页。数据库的索引,就是这个"索引页"。它的关键,在于有序——数据库索引底层通常是一种叫 B+ 树的结构,它把被索引那一列的值排好序组织起来。正因为有序,数据库查一个值时,就能像查字典一样折半逼近,几步之内定位到,而不必逐行扫。给 user_id 建索引,就一行 SQL:
-- 在 user_id 列上建索引,给数据库一条"线索"。
CREATE INDEX idx_user_id ON orders (user_id);
-- 建完之后,WHERE user_id = 12345 这类查询,数据库就能
-- 通过这个有序的索引快速定位,而不必再逐行翻全表。
但这里要立刻补上一个关键的认知,它正是我当初栽跟头的地方:你把索引建出来了,不等于你的查询就一定会用它。索引是一个独立于查询而存在的结构——它建在表上,静静地待在那。一次具体的查询走不走它,是数据库的查询优化器在执行时临场判断的,而这个判断,极度依赖你这条 SQL 写成了什么样。所以,"建了索引之后到底有没有生效",这件事你不能靠猜,必须有办法看——这就是下一节的 EXPLAIN。
三、用 EXPLAIN 看清查询到底走没走索引
判断一条查询有没有用上索引,靠脑补是不行的,得用数据库给你的工具:EXPLAIN。它的作用是,让数据库把这条 SQL 的执行计划摊开给你看——它打算怎么执行这条查询。
-- 在任何一条查询前面加 EXPLAIN,看它的执行计划。
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;
跑出来,你会得到一行(或几行)执行计划。其中最该盯住的是两列:type 和 key。下面是没用上索引时的样子:
type | key | rows | Extra -------+-------+-----------+------------- ALL | NULL | 28941200 | Using where
type = ALL,意思就是全表扫描——最差的一档;key = NULL,意思是没有用任何索引;rows 那个接近三千万的数字,是数据库预估要扫描的行数。这三个信号合在一起,就是在明明白白告诉你:这条查询在硬扛全表。而当索引正常生效时,同样这条查询,执行计划会变成:
type | key | rows | Extra -------+--------------+-------+------------- ref | idx_user_id | 18 | Using where
type 从 ALL 变成了 ref(走索引等值查找),key 从 NULL 变成了你那个索引的名字 idx_user_id,rows 从两千多万暴跌到 18。这才是索引生效的样子。所以从今往后,凡是写一条重要的查询,养成习惯:在它前面加个 EXPLAIN 跑一下,确认 type 不是 ALL、key 不是 NULL。不要假设索引生效了,要亲眼看到它生效。
四、最左前缀原则:联合索引的命门
实际业务里,查询常常不止一个条件。比如:查某个用户、某个状态下的订单——WHERE user_id = ? AND status = ?。这时你会建一个联合索引(把多列放进一个索引):
-- 联合索引:把 user_id、status 两列按这个顺序放进一个索引。
CREATE INDEX idx_user_status ON orders (user_id, status);
联合索引有一条必须刻进脑子的规矩,叫最左前缀原则:这个索引,只有当你的查询条件从最左边那一列开始、连续地用,才能用得上。(user_id, status) 这个索引,可以被 user_id 单独用、可以被 user_id + status 一起用;但不能被 status 单独用。
-- 能用上 idx_user_status 索引的:
SELECT * FROM orders WHERE user_id = 1; -- 用到最左列
SELECT * FROM orders WHERE user_id = 1 AND status = 2; -- 最左连续用
-- 用不上的:跳过了最左列 user_id,直接从 status 查
SELECT * FROM orders WHERE status = 2; -- 索引失效,全表扫描
为什么会这样?回到第二节那个比喻:联合索引是先按 user_id 排序、user_id 相同时再按 status 排序的——就像电话簿先按姓、姓相同再按名排。你知道"姓"(user_id),能在电话簿里快速翻到;可你只知道"名"(status)、不知道"姓",这本按姓排的电话簿就帮不上忙了,因为同名的人散落在整本电话簿的各个角落。所以建联合索引时,列的顺序是一个需要认真设计的决策:把最常单独作为查询条件的列,放在最左边。
五、索引失效的几种典型写法
这一节是重点——也是我当初栽得最惨的地方。即使你正确地建了索引、查询条件也命中了索引列,只要 SQL 写法不对,索引照样会悄无声息地失效。下面是几种最常见的。
失效场景 1:在索引列上套函数或做运算。这是我那次最隐蔽的坑。比如要查"2024 年 1 月 1 日的订单",我写了 WHERE DATE(created_at) = '2024-01-01'。created_at 上明明有索引,却失效了——因为索引存的是 created_at 原本的值,而你比较的是 DATE(created_at) 这个函数算出来的值,数据库为了求这个函数值,只能把每一行都算一遍,索引那份"原值的有序排列"就用不上了。
-- 索引失效:created_at 被 DATE() 函数包住了
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';
-- 改写:不动列,改成对【范围】的比较,索引就能用上
SELECT * FROM orders
WHERE created_at >= '2024-01-01 00:00:00'
AND created_at < '2024-01-02 00:00:00';
规矩就一句:WHERE 后面那个索引列,必须保持"光秃秃"的原样,不能被函数包、不能参与运算(WHERE amount * 2 > 100 同样失效,要改写成 WHERE amount > 50)。
失效场景 2:隐式类型转换。假设 user_no 是个字符串类型的列(VARCHAR),上面有索引。你查的时候写了 WHERE user_no = 12345——传了个数字。类型对不上,数据库会偷偷地把列的值转成数字再比较,这个"转一下",本质上等同于在列上套了个函数,索引照样失效。
-- 索引失效:user_no 是字符串列,却用数字去比,触发隐式转换
SELECT * FROM orders WHERE user_no = 12345;
-- 改写:让值的类型和列的类型严格对齐,加上引号
SELECT * FROM orders WHERE user_no = '12345';
失效场景 3:LIKE 以通配符开头。LIKE 'abc%' 能用上索引(它在找"以 abc 开头"的值,这正是有序索引擅长的);但 LIKE '%abc' 或 LIKE '%abc%' 用不上——"以 abc 结尾/包含 abc",在一份"按开头排序"的索引里是散乱分布的,没法快速定位。真要做"包含"式的模糊搜索,该上专门的全文检索(如 Elasticsearch),而不是硬用 LIKE '%...%'。
-- 能用上索引:前缀匹配
SELECT * FROM goods WHERE name LIKE '苹果%';
-- 用不上索引:前导通配符,只能全表扫描
SELECT * FROM goods WHERE name LIKE '%苹果%';
六、工程坑:索引不是越多越好、区分度与覆盖索引
知道了怎么让索引生效,还要知道怎么合理地建索引。这里有几个绕不开的工程坑。
坑 1:索引不是越多越好。这是我后来另一个教训。索引能加速查询,但它不是免费的:每一个索引,都是一份需要同步维护的有序结构。你每次 INSERT、UPDATE、DELETE,数据库不光要改表数据,还要把这一行涉及的每一个索引统统更新一遍。一张表上建了八个索引,你写入一行,背后就是八次索引维护——写入性能会被显著拖垮。所以索引要按真实的查询需要来建,没有查询会用到的索引,就是纯粹的负担,该删。
-- 查一张表上现有的索引,定期 review,删掉没人用的
SHOW INDEX FROM orders;
-- 确认某个索引确实没有查询在用之后,删掉它
DROP INDEX idx_some_unused ON orders;
坑 2:区分度太低的列,建索引没意义。"区分度",指一列里不同值的多少。在一个 gender(性别,只有"男/女"两个值)这种列上建索引,几乎毫无价值——因为通过它最多也只能把范围缩小到"一半的行",数据库一算,还不如直接全表扫。索引应该建在区分度高的列上,比如 user_id、订单号这种几乎每行都不同的列。可以先算一下区分度再决定:
-- 算一列的区分度:不同值的数量 / 总行数,越接近 1 越值得建索引
SELECT COUNT(DISTINCT gender) / COUNT(*) AS gender_sel,
COUNT(DISTINCT user_id) / COUNT(*) AS user_sel
FROM orders;
-- 结果可能是 gender_sel≈0.0000001、user_sel≈0.3 ——
-- gender 上建索引基本没用,user_id 上才值得。
坑 3:善用覆盖索引,省掉一次回表。当查询走 普通索引定位到行之后,如果它还需要索引里没有的列(比如 SELECT *),数据库得拿着主键再回主表去取一次完整的行——这一步叫回表。如果一个索引已经包含了查询所需要的全部列,数据库光读索引就够了,不必回表——这就是覆盖索引。下面这张图,把一条查询从发起到拿到结果的判断路径串起来:
-- 假设建了联合索引 (user_id, status)
-- 这条查询只 SELECT 了 status,而 status 就在索引里 ——
-- 索引本身就够用,不必回表,这就是覆盖索引
SELECT status FROM orders WHERE user_id = 1;
-- 这条 SELECT *,要的列索引里没有,定位到行后还得回表取完整行
SELECT * FROM orders WHERE user_id = 1;
所以一个小技巧是:查询时只 SELECT 你真正需要的列,别习惯性 SELECT *——列要得少,就更有机会被索引覆盖,省掉回表那一下。下面这张图,把"一条查询到底走不走索引"的判断流程串起来:
关键概念速查
| 概念 / 手段 | 说明 |
|---|---|
| 全表扫描 | 没有索引时数据库逐行翻找,耗时正比于表行数,数据涨大后爆发 |
| 索引是有序结构 | 底层多为 B+ 树,把列值排好序,查找可折半逼近而不必逐行扫 |
| 建索引不等于用索引 | 索引建在表上只是提供可能,查询走不走它取决于 SQL 怎么写 |
| EXPLAIN | 看执行计划,type 为 ALL 或 key 为 NULL 说明没走索引 |
| 最左前缀原则 | 联合索引须从最左列开始连续使用,跳过最左列则失效 |
| 函数与运算失效 | 索引列被函数包裹或参与运算,索引失效,要保持列光秃秃 |
| 隐式类型转换失效 | 值与列类型不匹配,数据库偷偷转换等同于套函数,索引失效 |
| 前导通配符失效 | LIKE 以百分号开头无法用索引,要做包含搜索应上全文检索 |
| 索引不是越多越好 | 每个索引都要随写入同步维护,索引过多会显著拖垮写入性能 |
| 区分度与覆盖索引 | 低区分度列建索引没意义,索引含全部所需列可免回表 |
避坑清单
- 没有索引时查询是全表扫描,耗时正比于行数,表小看不出问题,数据涨大后集中爆发。
- 建了索引不等于用上了索引,一次查询走不走索引取决于这条 SQL 怎么写。
- 重要查询都要加 EXPLAIN 确认,type 不是 ALL、key 不是 NULL,别假设索引生效要亲眼看到。
- 联合索引遵守最左前缀原则,必须从最左列开始连续使用,跳过最左列索引失效。
- 建联合索引时列的顺序要设计,把最常单独作查询条件的列放在最左边。
- 索引列被函数包裹或参与运算会失效,要保持列光秃秃,把运算挪到等号另一边。
- 值与列类型不匹配会触发隐式转换,等同于在列上套函数,索引失效,类型要严格对齐。
- LIKE 以通配符开头用不上索引,真要做包含式模糊搜索应上全文检索而非硬用 LIKE。
- 索引不是越多越好,每个索引都要随写入同步维护,定期 review 删掉没人用的索引。
- 区分度低的列建索引没意义,索引建在高区分度列上,只查所需列善用覆盖索引免回表。
总结
回头看那次"明明加了索引、查询还是慢得要命"的事故,以及我后来在索引上接连踩的坑,最该记住的不是某一条 SQL 的写法,而是我动手前那个想当然的判断——"查询慢,加个索引就好了"。这句话错在它把"建了索引"和"用上了索引"当成了同一件事。它们之间,隔着一整条你这条 SQL 写得对不对的鸿沟。索引,只是你在表上准备好的一种能力;而每一次查询能不能调用这个能力,是数据库在执行那一刻、对照着你的 SQL 写法,临场决定的。你把列用函数包了一下、类型没对齐、LIKE 前面多了个 %、联合索引没从最左列查起——任何一个小动作,都会让数据库放弃那个你辛苦建好的索引。所以用好索引,真正要修炼的,不是"记得加索引"这个动作,而是对"这条 SQL 到底会怎么被执行"有感觉。
所以做索引优化,真正的工程量不在 CREATE INDEX 那一行。那一行谁都会敲。真正的工程量,在于你有没有换一种眼光去看你写下的每一条查询:这个 WHERE 条件,会落到哪个索引上?我有没有不小心把索引列套进了函数?这是个联合索引,我的条件从最左列查起了吗?我建的这个索引,真的有查询在用吗,还是它只在白白拖累写入?我习惯性写下的 SELECT *,是不是毁掉了一次覆盖索引的机会?这篇文章的几节,其实就是顺着这条思路展开的:先想清楚没索引为什么慢,再看索引为什么能加速、怎么用 EXPLAIN 验证,然后是最左前缀、几种失效写法这两段主干,最后是索引不是越多越好、区分度、覆盖索引这几个把索引真正用对的工程细节。
你会发现,索引的思路,和我们生活里处理"如何在一大堆东西里快速找到一样"的经验完全相通。一座大图书馆,几十万册书,你要找一本,靠的从来不是一个书架一个书架地翻——那是全表扫描。你靠的是那套检索目录:目录按书名/作者排好了序,你几下就能定位到这本书在哪个区、哪一排。但这套目录也有它的脾气:它按书名首字排序,你知道书名,查得飞快;你只记得书名中间那两个字,这套目录就帮不上忙了——这正是 LIKE '%...%' 用不上索引的道理。索引的智慧,从来不是"多抄几份目录",而是"建一套合理的目录,并且用对查它的方式"。
最后想说,索引用没用对,差距永远不会在开发期暴露——开发库里你就造几百几千行测试数据,有没有索引、索引有没有生效,查询都是一瞬间返回,跑起来一模一样。它只在真实的、被时间喂养到几千万上亿行的生产数据面前才显形。那时候它会用最难堪的方式给你结账:一个上线时毫无问题的查询接口,在数据悄悄涨了一年之后,某天突然开始大面积超时,运营在群里喊"后台点不动了";你紧急加上索引,却发现没用,因为那条 SQL 的写法根本走不到索引上;你又发现另一张核心表上,前人留下了七八个没人记得为什么建的索引,把写入拖得很慢却没人敢删。所以别等生产数据涨到那一天,在你写下第一条查询、建下第一个索引的时候就该想清楚:这张表数据涨到一千万行,这条查询还快吗?我建的索引,这条 SQL 真的走得到吗?EXPLAIN 一下,我亲眼确认过吗?这几个问题都有了答案,你的查询才不只是开发库里那个数据量小时跑得飞快的样子,而是一个无论数据涨到多大,都能稳稳地、可预期地快速返回的可靠系统。
—— 别看了 · 2026