「给订单状态加个索引吧,查询太慢了。」加完之后,接口不但没快,写入还慢了,某些查询甚至更慢。又过了两周,登录接口按手机号查用户,明明 phone 上有唯一索引,EXPLAIN 却是全表扫描。开发把手机号在代码里转成了数字再传进来,而数据库里存的是字符串。
索引这件事,建了不等于会用,用了不等于用对。这篇不讲 B+ 树内部结构(下一篇讲),只回答一个很实际的问题:我写的这条 SQL,到底能不能用上索引?用真实执行计划一条条对照。
场景走查:三次「加了索引没效果」
第一次是订单列表按状态筛选。给 status 建了普通索引,查询计划确实走了索引,但状态只有五种,每种占两成数据,扫索引再回表 400 次,成本接近全表扫描。
第二次是报表按月份统计,SQL 写的是 WHERE YEAR(created_at) = 2025 AND MONTH(created_at) = 7,created_at 上的索引完全用不上。
第三次是登录按手机号查,参数从 JSON 里解析出来是数字,SQL 里写 phone = 13800000007,而列是 VARCHAR。
三次都「有索引」,三次都没得到想要的效果。原因分别是区分度、函数包列、隐式类型转换。
索引不是贴标签:最左前缀实测
给 t_prefix(a, b, c) 建联合索引 idx_abc,相当于按「先 a、再 b、再 c」排了一本字典。查询条件必须从 a 开始连续匹配,才能定位到区间。实测四种写法:
| 查询条件 | type | key_len | 用到的列 |
|---|---|---|---|
a = 3 AND b = 30 AND c = 30 | ref | 12 | a、b、c 全部 |
a = 3 AND b = 30 | ref | 8 | a、b |
a = 3 | ref | 4 | 只有 a |
b = 30 | index | NULL | 只能全索引扫描,退化成遍历 |
a = 3 AND c = 30 | ref | 4 | a 用来定位,c 只能过滤 |
key_len 是「实际用了索引的前几个字节」,三个 INT 列各 4 字节,所以 12 表示三列全用上,8 表示两列,4 表示一列。最后一行最具迷惑性:a = 3 AND c = 30 看起来两个条件都命中了索引列,但 c 前面断了 b,所以只能用 a 定位,c 在索引里做过滤,rows 估算 100,filtered 只有 10。
联合索引的顺序不是随便排的。经验规则:等值条件在前,范围条件在后;区分度高的列在前。因为一旦遇到范围条件,后面的列就没法再用索引定位了。比如 (customer_id, created_at) 适合「某客户某时间段」;反过来 (created_at, customer_id) 对「某客户」查询就只能用上第一列的时间范围。
key_len 的绝对值还受类型影响。VARCHAR(20) 在 utf8mb4 下最多占 80 字节,允许 NULL 再加 1 字节;所以看到 key_len=81,说明用的是完整字符串列。理解这个数的意义,比记住「4 字节是 INT」更重要:它能告诉你联合索引到底断在第几列。
范围条件之后的列要单独强调。索引 (a, b, c),查询 a = 3 AND b > 10 AND c = 30,b 是范围,c 就无法再用来定位,key_len 只到 b,c 只能在回表后过滤。这正是「等值在前、范围在后」的原因。如果业务里范围查询很多,把范围列放到最后,前面的等值列才能全部用上。
六种让索引失效的写法
函数或运算包住索引列。 WHERE YEAR(created_at) = 2025 让数据库无法把条件转成时间区间,只能逐行算函数。实测 type=ALL、rows=1998、key=NULL。改成范围条件:
SELECT id, amount FROM orders
WHERE created_at >= '2025-07-01 01:00:00'
AND created_at < '2025-07-01 02:00:00';
实测变成 type=range、key=idx_created、rows=60,扫描量从 1998 降到 60。日期条件永远优先写区间,不要写 YEAR()、MONTH()、DATE()。
隐式类型转换。 这条值得单独讲,因为它有过一次线上事故。登录接口按手机号查用户,phone 是 VARCHAR 且有 uk_phone 唯一索引,但参数从 JSON 里解析出来是数字。SQL 实际执行的是 WHERE phone = 13800000001,MySQL 会把字符串列转成数字再比较,索引失效。实测 possible_keys=uk_phone 但 key=NULL、type=ALL、rows=101、filtered=10.0、Extra=Using where。
SHOW WARNINGS;
-- 1739 Cannot use ref/range access on index 'uk_phone'
-- due to type or collation conversion
用户量小的时候,扫 101 行谁都没感觉;用户涨到百万级,每次登录都扫全表,QPS 一上来 CPU 直接打满,而且慢查询日志里只看到一条「很简单」的 SELECT。后来的修法是把参数统一转成字符串再传,同时在数据访问层加了一条校验:SQL 参数类型必须和列类型一致。参数类型和列类型必须对齐,该加引号就加引号。
前导百分号。 LIKE '%加急%' 无法确定从哪里开始找,只能全索引扫描。实测 type=index、rows=1998,等于把索引当全表读。后缀匹配 LIKE '加急%' 才能用上索引。真要做全文搜索,应该上全文索引或搜索引擎,而不是在业务表上硬扫。
OR 连接一个没索引的条件。 WHERE status = 'paid' OR remark = '加急',在 remark 没索引时实测 type=ALL,idx_status 被列为候选但最终没用。给 remark 也建索引后,计划变成 index_merge,Using union(idx_status,idx_remark)。索引合并有额外开销,不是所有 OR 都能合并,能改写成 UNION ALL 或 IN 往往更可控。
范围条件后面的列。 WHERE customer_id = 7 AND created_at > '...' AND status = 'paid',索引 (customer_id, created_at) 只能定位到「客户 + 时间区间」,status 不在索引里,仍要回表过滤。把等值条件放在范围前面,或者把 status 也加进索引。
区分度太低。 状态只有五种,每种 20%,走索引要回表 400 次,优化器可能算账后直接全表扫描。实测只查 id 时用了 idx_status(覆盖索引),一旦多取 amount 需要回表,成本就上来了。区分度低的列单独建索引,收益往往不如放进联合索引的末尾。
还有几种常见失效:WHERE status != 'cancelled' 这种否定条件,优化器很难转成有效区间,往往退化成扫描;WHERE id IN (1, 2, 3) 通常是等值匹配,能用索引,但集合很大时会变成多次查找;WHERE created_at >= '2025-07-01' AND created_at < '2025-08-01' 是标准范围写法,比 BETWEEN 更不容易踩边界(BETWEEN 两端都包含)。
隐式转换还有方向性:字符串列和数字比较会失效,反过来数字列和字符串比较通常还能用,因为数据库把字符串转成数字,转换发生在常量侧,不影响索引列。但别依赖这个细节,参数类型和列类型始终对齐最稳。
翻车现场:加完索引,接口从 1.7 ms 变成 2.7 ms
订单列表要按状态筛选,代码里是 SELECT id, amount FROM orders WHERE status = 'paid'。压测时有人提议「给 status 加个索引」,加完做前后对比,结果反了:
| 版本 | type / key | key_len | rows | Extra | 最优耗时 |
|---|---|---|---|---|---|
| 加索引前 | ALL / NULL | NULL | 1998 | Using where | 1.706 ms |
加 idx_status 后 | ref / idx_status | 66 | 400 | NULL | 2.668 ms |
加索引后的六次采样是 3.37、4.01、3.56、2.67、3.94、2.91 ms,加索引前是 2.8、2.02、1.85、2.54、2.8、1.71 ms,稳定地慢了一截。索引明明把扫描量从 1998 行降到 400 行,为什么更慢?因为那 400 行不在索引里结束——amount 不在索引中,每一行都要按主键回表取一次,400 次随机读。全表扫虽然读 1998 行,却是一次顺序读,走的还是同一批数据页。在数据量不大、页都在缓冲池里的情况下,顺序扫反而更便宜。
这个对比可以用索引可见性做无侵入验证:
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
EXPLAIN SELECT id, amount FROM orders WHERE status = 'paid';
-- type=ALL,possible_keys=NULL,rows=1998,filtered=20.00
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;
EXPLAIN SELECT id, amount FROM orders WHERE status = 'paid';
-- type=ref,key=idx_status,key_len=66,rows=400,filtered=100.00
同一个索引,只是「让不让优化器看见」,计划就从全表扫切到 ref。这说明问题不在索引能不能用,而在用它的代价划不划算。状态只有五种、paid 占 400/1998,区分度大约 20%,属于典型的低区分度列。
真正有效的改法不是给状态单独建索引,而是让它和排序列组成联合索引。列表查询实际是「按状态筛、按时间倒序取前 10 条」,实测:
-- 只有 idx_status 时
EXPLAIN SELECT id, amount FROM orders
WHERE status = 'paid' ORDER BY created_at DESC LIMIT 10;
-- type=ref,key=idx_status,rows=400,Extra=Using index condition; Using filesort
-- 建 (status, created_at) 之后
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
-- type=ref,key=idx_status_created,key_len=66,rows=400,Extra=Using where; Backward index scan
注意 rows 还是 400,没变。联合索引的收益不体现在扫描行数,而在于省掉了 Using filesort:索引里已经按 status, created_at 排好序,取前 10 条可以直接倒着读。评价一个索引,要同时看「少扫多少行」「少回表多少次」「省没省排序」三件事,只看其中一项容易得出错误结论。
覆盖索引:不回表就是快
普通二级索引的叶子节点只存「索引列 + 主键」。查一列不在索引里的数据,要先在索引里找到主键,再回主键索引取整行,这叫回表。如果查询需要的列全在索引里,就不用回表,叫覆盖索引,Extra 显示 Using index。
EXPLAIN SELECT customer_id, created_at FROM orders WHERE customer_id = 7;
实测 type=ref、key=idx_customer_created、rows=20、Extra=Using index。查询的两列都在索引里,20 行数据直接从索引返回。如果再加一列 amount,Extra 里的 Using index 就消失,变成要回表。
这就是为什么「SELECT * 比 SELECT id, name 慢」不只是网络传输的问题:列越多,越难被索引覆盖,回表次数越多。列表页只查需要的列,是性价比最高的优化之一。
做覆盖索引时,先列出查询真正需要的列,再按「等值列在前、排序列居中、返回列在后」的顺序设计。比如列表按状态筛、按时间排、只返回 id 和金额,可以建 (status, created_at, amount)。主键 id 自动包含在二级索引里,不用重复写。列太多就别硬做覆盖,索引体积会反噬写入。
覆盖索引同样受最左前缀约束。索引 (customer_id, created_at) 能覆盖 SELECT created_at FROM orders WHERE customer_id = 7,但如果查询只按 created_at 过滤,就用不上这个索引去覆盖,因为第一列没给值。设计时先确认过滤条件从索引第一列开始,再把返回列追加在后面。
覆盖索引的边界在索引宽度。把十几列都塞进索引确实能覆盖更多查询,但索引体积会膨胀、写入维护成本上升、缓冲池命中率下降。实测列表页只取 id 时能走覆盖索引(偏移 195000 时 type=index、rows=195010、54.02 ms),一旦多取 status、amount 就退回全表扫(148.18 ms)——这不是理论,是 type 从 index 变成 ALL 的直接后果。优先覆盖「调用频繁、返回列少」的查询,低频查询不值得为它加宽索引。另一个常见误区是以为主键要显式写进索引,其实 InnoDB 二级索引的叶子节点自动带主键,重复写只是浪费空间。
索引下推:把过滤提前到索引里
MySQL 5.6 引入 Index Condition Pushdown(ICP,索引下推)。原本的做法是先按索引定位,再回表,最后在整行上过滤;ICP 改成在索引里先过滤,能排除就不回表。典型场景是联合索引里第二列做模糊匹配:
EXPLAIN SELECT amount FROM orders FORCE INDEX (idx_cust_remark)
WHERE customer_id = 7 AND remark LIKE '%急%';
实测 type=ref、key=idx_cust_remark、rows=20、Extra=Using index condition。它先用 customer_id 定位,再在索引里对 remark 做过滤,只有符合的行才回表取 amount。这里用 FORCE INDEX 是为了看清 ICP 行为,正常查询交给优化器选择。
Using index(覆盖索引)和 Using index condition(索引下推)是两回事:前者是「不用回表」,后者是「回表前先过滤」。看到这两个词,说明索引至少在某一步起了作用。ICP 只对二级索引生效,而且要求条件「能在索引里计算」,普通比较和 LIKE 前缀都算;子查询、函数、跨表条件仍要回表之后处理。另外 ICP 默认开启,受 optimizer_switch 里的 index_condition_pushdown 控制,排查时可以用 SET optimizer_switch 临时关掉对比。
EXPLAIN ANALYZE 直接看 ICP 的实际效果:索引下推那条语句的 actual rows 应该在回表前就明显小于扫描行数,节点上会标注 Index condition。8.0.12 只能从 Extra=Using index condition 推断,看不到实际跳过了多少行。这段是版本推演,标记为待确认。优化器也会选错
用不用索引最终由优化器决定,它依据统计信息估算代价,而不是「有索引就用」。统计信息可能过期,尤其是大批量导入、删除之后。表现为「同一个查询,昨天走索引,今天全表扫」。ANALYZE TABLE 可以刷新统计信息,但大表上也有成本,通常在低峰期做。
优化器估错时可以用 FORCE INDEX 或 USE INDEX 干预,但要把它当临时手段:数据分布变化后,强制索引可能变成新的瓶颈。更好的做法是调整索引结构,让优化器自然选对。MySQL 8.0 支持索引可见性(ALTER INDEX ... INVISIBLE),可以先「软删除」一个索引观察效果,比直接 DROP 安全。
判断优化器选得对不对,可以把实际耗时和 EXPLAIN 的估算对照。rows 是估算的扫描行数,filtered 是估算的过滤比例,两者相乘才是「预计读取行数」。如果乘积明显小于实际读取量,说明统计信息不准,先 ANALYZE TABLE 再比较。8.0.12 没有 EXPLAIN ANALYZE,只能靠估算加实际计时;8.0.18 及以上可以直接看 actual rows,具体输出格式本文未在本机验证,标记为待确认。
另外,index_merge 虽然能把两个索引合并,但它要分别扫描再求交并集,排序和去重都有成本。两个条件都走索引的 OR 查询,不一定比改写成一个范围条件更快,实测为准。
现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 建了索引还是全表扫 | 条件被函数包住、类型隐式转换、前导 % | EXPLAIN 看 possible_keys 有但 key=NULL,逐条改写法 |
| 联合索引只用上第一列 | 违反最左前缀,或第一列就是范围条件 | 看 key_len,确认实际用了几列 |
| 查询走了索引但没变快 | 区分度太低,回表次数多,成本接近全表 | 算一下返回行数占总行数比例,考虑覆盖索引 |
| 列表页加一列就慢很多 | 新增列不在索引里,覆盖索引变成回表 | 看 Extra 里的 Using index 是否消失 |
| OR 查询突然全表扫 | 其中一个条件列没索引,无法合并 | 看 type 是否变成 index_merge |
| 写入变慢、磁盘涨得快 | 索引过多,每次写入维护多棵 B+ 树 | 统计索引数量和大小,删掉长期不用的索引 |
| 昨天走索引今天不走 | 统计信息过期或数据分布变化 | 对比 EXPLAIN 的 rows,必要时 ANALYZE TABLE |
| 加索引后反而更慢 | 区分度低,回表次数多,随机读抵消了少扫的收益 | 把返回行数除以总行数,再用 INVISIBLE 对比计划 |
索引存在但 possible_keys 有、key 为空 | 类型或排序规则隐式转换,条件无法转成索引区间 | 看 SHOW WARNINGS 有没有 1739 |
边界与代价
索引是用空间和写入换读取。每多一个索引,插入、更新、删除都要多维护一棵树;索引本身也占磁盘,可能比数据还大。一个表建七八个索引,写性能会明显下降,而且优化器可选计划变多,选错的概率也上升。实测插入 20000 行:没有二级索引 193.8 ms,加 4 个二级索引后 561.9 ms,约 2.9 倍——这个倍数的意思是,你为读优化做的每一分努力,写入侧都要付账。
索引不是越多越好,也不是所有查询都值得优化。一次跑几分钟的离线报表,全表扫描可能比走索引更合适;高频接口才值得为它设计联合索引和覆盖索引。判断标准是「这条 SQL 的执行频率 × 单次成本」。
MySQL 和 PostgreSQL 的索引能力也有差异:PostgreSQL 支持部分索引、表达式索引,能直接给 LOWER(email) 建索引;MySQL 8.0 支持函数索引,但语法和使用限制不同。跨库迁移时,别把「函数包列一定失效」当成绝对结论。
小结
判断一条 SQL 能不能用索引,看三件事:条件能不能转成索引区间(最左前缀、无函数、类型一致)、优化器算账划不划算(区分度、返回比例)、要查的列在不在索引里(覆盖索引)。三者缺一,索引就可能白建。
当天可以自己验证的练习:
- 建
(a, b, c)联合索引,分别用a、a+b、b、a+c查询,记录key_len。 - 把
WHERE created_at >= ...改成WHERE YEAR(created_at) = ...,对比type和rows。 - 用字符串手机号和数字手机号各查一次,观察
key是否为空。 - 对比
SELECT customer_id, created_at和SELECT customer_id, created_at, amount的Extra。 - 给两个列各建索引,跑一条 OR 查询,看是否出现
index_merge。
下一篇讲索引原理:B+ 树、回表与覆盖索引。这一篇讲「怎么用」,下一篇讲「为什么」:为什么数据库选 B+ 树而不是红黑树,为什么回表贵,为什么自增主键比 UUID 快,为什么索引也会让写入变慢。知道原理,遇到没见过的查询才知道往哪猜。