1. 一次线上慢查询，让我重新理解了索引

上个月排查线上问题，有个查询平时几十毫秒，某天突然跑到三秒多，直接拖垮了下游服务。看了一圈代码逻辑没毛病，数据量也才百万级，问题只能出在索引上。查看执行计划，发现优化器放弃了我以为“必然命中”的联合索引，从头到尾扫表。那一次之后我意识到，很多写了好几年SQL的人都停留在“会加索引”的阶段，真正遇到索引失效、执行计划飘忽不定的场景，还是一头雾水。

这篇内容就是给想进阶SQL优化、把索引吃透的同学准备的。我会从底层存储结构讲起，解释索引为什么能提速、为什么有时加了索引反而更慢，再结合具体的失效场景和EXPLAIN执行计划定位问题，最后分享一些我从实际项目里总结出来的索引设计教训。无论你是后端开发、DBA还是刚接触数据库优化的新手，这里的内容都直接可落。

索引不是什么玄学。把一个查询变快，本质是让数据库少读数据；让查询变慢，本质是优化器选了一个高成本的路径。搞懂索引，先得理解数据在磁盘上怎么存的，然后你才能看懂优化器在做什么。

2. 索引提速的本质：从全表扫描到B+树查找

2.1 全表扫描慢在哪里

很多人没想过一个问题：一张表在磁盘上是怎么组织的？绝大多数关系型数据库，表数据以数据页（Page）为单位存储，一个数据页通常是16KB大小，可以存若干行记录。查询数据时，磁盘IO的最小单位就是数据页——哪怕你只需要一行，数据库也会把这16KB整个读进内存。

全表扫描之所以慢，不是因为数据库“一行一行找”，而是它要把这张表占用的所有数据页都读一遍。假设一张表100GB，分布在几万个数据页上，全表扫描就是几万次磁盘IO。磁盘顺序读虽然快一些，但和内存访问速度相比还是有量级差距；如果是机械硬盘更是灾难级别。

关键点在于：全表扫描的代价和数据量严格正相关。数据量翻倍，扫描代价翻倍。这就是为什么你的表到百万、千万级别之后，原来“还能跑”的查询突然变得不可接受。数据量增长，SQL性能问题才真正爆发。

2.2 B+树查找为什么快

索引的核心结构是B+树。为什么是B+树而不是二叉搜索树或哈希表，这是数据库设计者反复权衡后的结果。B+树的每个节点可以容纳多个键值（通常一个节点就是一个数据页大小，能放几百个键），树的高度极低——三千万行数据，B+树高度通常只有3到4层。这意味着从根节点到叶子节点，最多4次磁盘IO就能定位到目标数据页。

对比二叉搜索树，三千万行数据需要约25层，最坏情况25次磁盘随机IO，性能差了一个数量级。B+树的“宽矮”形态天然适配磁盘存储：层数少，IO次数少；节点大小和页大小对齐，一次IO拉一个完整节点。

哈希索引定位是O(1)，看起来比B+树更优，但它不支持范围查询、不支持排序、不支持前缀匹配。业务查询中“大于某值”、“位于区间”、“按某个字段排序”的场景极其普遍，哈希索引根本接不住。所以绝大多数关系型数据库的默认索引结构是B+树。

2.3 回表对性能的隐性影响

搞清楚回表之前，先理解一个底层事实：一张表的InnoDB主键索引就是表本身。数据行挂在主键索引的叶子节点上，这就是“聚簇索引”。除了主键以外的所有二级索引，叶子节点上存的是主键值，不是行数据。

那么问题来了：如果你在name字段上建了二级索引，查询条件WHERE name = '张三'，查找过程是两步——先在二级索引中定位到name对应的叶子节点，取到主键id；再用这个id到主键索引（聚簇索引）中查一次，取出完整的行。第二步就叫回表。

回表本质是一次按主键查找，通常是点查，成本不算高。但如果你的查询过滤条件很宽，命中了大量二级索引记录，比如返回5000行，那就意味着5000次主键点查。这5000次如果是随机磁盘IO，性能直接崩掉。所以我强调：每次SQL写完之后，问自己一句“这个查询会不会大量回表”。如果会，就想想有没有办法避免——覆盖索引通常是答案，后面第四章我会展开讲。

3. 索引失效场景复盘：不只是“函数别套列”这么简单

3.1 隐式类型转换，优化器直接不认账

我见过最多的索引失效场景，就是隐式类型转换。表中user_id字段定义成varchar，存储的其实是数字字符串，查询时传入了整数：

SELECT * FROM user_info WHERE user_id = 12345;

如果你以为MySQL会自动把12345转成'12345'没事，你就踩坑了。实际情况是：当字段是字符串类型、传入的是数值类型时，MySQL的隐式转换会选择把字段列那一侧做类型转换，也就是对user_id这一列套了一次CAST函数。一旦索引列出现在函数内，B+树的排序结构就失效了，优化器只能放弃索引。

判断标准很简单：看等号两侧，哪一侧是字段本身，若是字符串列和数值比较，则字段被转换。反过来，如果你传入的是加了引号的字符串'12345'，则不需要转换字段列，索引可以正常使用。

这类问题很难通过看代码发现，最有效的排查手段就是EXPLAIN看type字段是不是从ref退化成ALL。我在项目里做过一次统计，这类隐式类型转换导致的慢查询占了索引相关故障的三成以上。

3.2 对索引列做运算，优化器无从下手

B+树索引能快速查找的前提，是查询条件能够利用列的原始排序结构。一旦索引列参与了运算，这个前提就没了。

常见的形式有三类：

-- 函数处理 SELECT * FROM orders WHERE DATE(create_time) = '2025-03-01'; -- 隐式运算 SELECT * FROM orders WHERE price + 10 > 100; -- 字段与字段运算 SELECT * FROM orders WHERE a + b > 20;

DATE(create_time)虽然写法直观，但你让优化器怎么用索引？它不知道B+树里哪个节点对应DATE(create_time) = '2025-03-01'这个计算结果。正确姿势是改成范围查询：

SELECT * FROM orders WHERE create_time >= '2025-03-01 00:00:00' AND create_time < '2025-03-02 00:00:00';

这样优化器可以直接在B+树上做区间定位，快速找到起始位置再顺序扫。后面这个写法少一次函数计算，却能让索引真正生效。类似的，price + 10 > 100可以改写成price > 90。永远记住：索引列要保持“裸”状态，所有计算都放到查询条件的一侧去。

3.3 LIKE前缀模糊匹配，索引的天然缺陷

LIKE '%keyword%'这种写法，是索引无法使用的经典场景。B+树索引结构是按值排序的，它能帮你定位“开头的值”，但没法定位“包含某个子串的值”。keyword%这样以通配符结尾的模式没问题，索引可以用；%keyword或%keyword%，索引就直接失效。

不过有一种折中方案。如果确实需要做包含式搜索，且数据量大、检索频繁，我会建议业务方引入全文索引或者专门做分词服务，而不是在数据库里硬扛。如果数据量不大，LIKE模糊查询也无所谓，但一定要明白它走的是全表扫描。另一个可行思路是使用前缀索引配合额外字段：比如存一个name_reverse字段反转头尾顺序，LIKE '%keyword%'可以变相转换成LIKE 'keyword%'的查询，这个技巧适合有特定业务形态的场景，但一般用户用不上，了解即可。

3.4 联合索引顺序错乱，最左前缀原则

联合索引(a, b)和两个单列索引(a)、(b)是完全不同的东西。联合索引先按a排序，a相同的记录再按b排序。这意味着只有查询条件里用了a，才能享受到这个索引的顺序结构。

• WHERE a = 1 AND b = 2：能走到，两个条件都命中
• WHERE a = 1：能走到
• WHERE b = 2：无法使用联合索引——b在B+树上不是第一排序键
• WHERE b = 2 AND a = 1：优化器会调整顺序，依然能走

很多同学把“最左前缀”理解成“SQL语句里where条件的书写顺序”，这是不对的。优化器最终会进行条件重排，判断的是实际用到了哪些列。关键是：联合索引的第一列必须出现在查询条件中，且这个条件不能是范围之后被阻断的字段。

还有一个很容易忽略的点：范围查询会阻断后续索引列。比如WHERE a > 1 AND b = 2，联合索引的a列被范围条件占据后，b列无法继续作为索引条件进行等值匹配。优化器会选择range方式，a列的索引效果还在，但b列只能做过滤，索引本身不会再参与。这就是为什么设计联合索引时，需要把高频等值条件的列放前面，范围条件的列放后面。

3.5 优化器说“不用索引”，不代表索引没用

有一种情况最容易让人困惑：明明索引没失效，符合所有使用条件，但执行计划显示type=ALL全表扫描。

为什么优化器会放弃索引？因为它回表成本太高。举个真实案例：某个表有个status字段，取值范围只有0、1、2三个值，分布严重不均，90%数据是status=1。你给status建了索引，查WHERE status = 1的时候，优化器一算，如果走索引，需要回表90%的行，等于一次范围大的随机IO风暴，还不如直接全表顺序扫描来得快。于是它放弃索引，选择全表扫描。

这不是优化器蠢，而是它比你更清楚成本模型。此时如果你强制FORCE INDEX，大概率性能反而更差。我的建议是：遇到这种“选择性太差”的字段，不要建普通索引，更不要强迫走索引，而是考虑改变存储结构（比如加汇总表、引入全文索引或使用覆盖索引）。索引不是越多越好，选择性太差的字段建索引，基本是负收益。

4. 用EXPLAIN执行计划给索引“拍CT”

4.1 type字段里的性能等级，直接翻译成本

EXPLAIN输出的第一行type字段，是判断一条SQL是否高效的最直接指标。从好到差大致是：

我来解释一下日常排查的优先级。如果看到ref或range，说明索引正常工作，可以继续看rows和filtered确认扫描代价；如果看到ALL，先回头看WHERE条件是不是触发了第三章说的失效场景，再决定要不要加索引。index全索引扫描常常被忽视——它虽然用了索引，但本质上是遍历整棵索引树，性能并不比全表扫描好太多，区别只是它读取的是更小的索引页而不是更大的数据页。

4.2 rows和filtered，别只看“有没有走索引”

很多工程师看执行计划只看type和key，觉得“走了索引就没事”。这个认知太粗糙了。同样一条ref类型的查询，可能扫描100行就结束，也可能扫描100万行。差别就在rows和filtered上。

rows是优化器估算的需要读取的记录数，filtered是经过查询条件过滤后剩下的比例。例如rows=100000、filtered=1，意思是优化器预计读取10万条记录，最终只剩1%即1000条。这里有一个重要信息：如果filtered很低，说明索引选的过滤字段区分度不够，大量行被读出来却用不上。优化器的估算通常基于统计信息，如果你的表长时间没更新统计信息，或者数据分布发生明显变化，估算就可能失真——这也是为什么要定期ANALYZE TABLE。

我在排查线上慢查询时，遇到filtered很低的情况，第一反应不是加索引，而是重新审视查询条件的设计：是不是去掉某个过滤条件后可以走一个更窄的索引？或者改变查询条件的顺序让选择性更高的列先参与索引匹配？这些判断，离开EXPLAIN是没法完成的。

4.3 一个完整的慢查询定位案例

前阵子某项目有个查询开始变慢，原始SQL大致长这样：

SELECT * FROM payment_record WHERE order_no = '20250315001' AND status = 2 ORDER BY create_time DESC LIMIT 10;

EXPLAIN结果：type=ALL，key=NULL，rows=800000。一眼看出这是全表扫描。奇怪的是，order_no明明有单列索引，为什么没走？

继续看表结构发现，order_no字段类型是varchar(64)，但查询传入的'20250315001'本身是字符串，类型没有隐式转换问题。再看一眼，原来存储引擎是MyISAM？不对，是InnoDB，所以问题不在这。低头细看，那张表的order_no单列索引虽然存在，但所有查询走的是(order_no, status)这个联合索引，单列索引其实是冗余的。但为什么联合索引也没被用上？

最后通过SHOW INDEX FROM payment_record查索引基数，发现统计信息严重过期：这张700万行的表，索引基数记录只有几百。优化器以为通过索引要扫描所有行，计算后认为回表成本高于全表扫描，于是放弃。执行ANALYZE TABLE payment_record刷新统计信息后，同样的SQL变成了type=ref，查询耗时从2.8秒降到30毫秒。

这个案例的核心教训是：执行计划并不是每次查询都实时计算最优路径的，它依赖统计信息做成本估算。表数据量剧烈变化后，记得关注统计信息是否更新。

5. 联合索引与覆盖索引的高级用法：不止是“建索引”而已

5.1 联合索引的底层逻辑：最左前缀背后的排序规则

很多人建联合索引就是凭感觉，把需要的列排个序，建完拉到。但要知道，联合索引(a, b, c)本质上是建立了一个复合排序规则：先按a排序，a相同再按b，b相同再按c。所以想要在一条SQL里最大化利用这个索引，查询条件的组合就必须符合这个顺序。

最经典的例子：有个表查询列是(province, city, district)，如果命中SQL是WHERE province = '广东' AND city = '深圳'，联合索引完美命中；如果SQL变成WHERE district = '南山'，索引完全用不上。因为district是第三列，前面没有province和city的等值条件，它在B+树上的顺序对当前查询而言是“打乱的”，优化器没有能力跳过前两列直接按第三列定位。

设计联合索引时，我有一套自己的判断流程：

1. 找出高频查询里的等值条件列，按出现频率排出候选顺序
2. 区分度高的列放前面，区分度低的放后面
3. 范围查询列必须放在所有等值条件列之后
4. 结合业务实际，避免在联合索引里混入每次查询都不同的列

这套流程不一定产出理论最优索引，但能保证最常用的大多数查询命中率高，并且不会造出一堆乱序组合。

5.2 覆盖索引：把回表直接消灭在索引层

覆盖索引是索引优化里最实用的进阶技巧之一。它的定义很简单：一个索引包含了SQL查询所需要的所有列，那么查询过程就不需要回表，直接从索引树里拿到全部数据。

假设表t_order有联合索引(buyer_id, create_time)，现在要查询某买家最近10笔订单的下单时间：

SELECT create_time FROM t_order WHERE buyer_id = 10086 ORDER BY create_time DESC LIMIT 10;

这条SQL不需要回表：buyer_id用于索引定位，create_time就是索引的第二列，数据直接从索引树的叶子节点取出。相比每次回表去聚簇索引里拿行数据，覆盖索引省掉了大量的随机IO。

但覆盖索引不能滥用。如果SQL里有SELECT *，无论索引设计得多好，都必然回表，因为二级索引叶子节点不存储整行数据。所以一个常用的优化手段是：把查询需要用到的字段尽量收敛，用覆盖索引消除回表。

我在实际项目里见过一个极端案例：一个查询返回的五个字段全部包含在联合索引里，SQL从1.2秒优化到40毫秒，只是把查询字段从*改成了需要的五列。优化没有增加任何索引，只是让已有索引从“部分利用”变成了“完全覆盖”。这类优化收益极高，改动却极低，很值得在日常开发中养成“查询字段收敛”的习惯。

5.3 索引下推：优化器在索引层帮你做了预过滤

关于索引下推（Index Condition Pushdown，ICP），很多资料讲得比较晦涩，我用一个案例说明。

假设表t_user有联合索引(age, name)，查询：

SELECT * FROM t_user WHERE age > 20 AND name LIKE '张%';

在旧版本数据库没有ICP机制时，执行逻辑是：先用索引定位age > 20的区间，然后逐条回表拿到整行，在服务层判断name LIKE '张%'。也就是说，name条件虽然也在联合索引里，但只被当作“事后过滤”条件。

有了ICP之后，执行逻辑变成：在索引遍历的过程中，直接在索引层就检查name LIKE '张%'，不满足条件的不回表。这个优化直接减少了从索引到聚簇索引的回表次数。前提是name条件原本就在索引列中，否则ICP也帮不上忙。

ICP的效果在过滤条件多、WHERE里可下推条件占比大的查询里尤其明显。查看执行计划里Extra字段显示Using index condition，就说明ICP生效了。MySQL 5.6之后默认开启ICP，不需要额外配置。这部分机制比较底层，但对理解“为什么我给联合索引加了一个‘没用’的列后查询反而快了”非常有帮助。

6. 索引设计的避坑经验：有些坑不是踩一次就完了

6.1 冗余索引的代价，比表面看到的更大

大多数人对冗余索引的认知停留在“浪费存储”上，所以他们觉得问题不大。但实际上，索引对写入的影响比存储的浪费严重得多。每插入一行数据，所有相关索引都要同步更新；每次UPDATE如果改了索引列的值，对应的索引树节点要重新调整位置。索引越多，写入放大的倍数越大，TPS越高，这个代价越明显。

举一个真实例子。某订单表本来有联合索引(order_no, status, create_time)，后来某个开发图省事，又建了单列索引(order_no)。从查询角度看，后者完全冗余，前者天然支持WHERE order_no = ...。但因为多了一个索引，每次批量插入订单时，数据库需要额外维护一颗索引树，批量导入1万条数据的时间从2秒变成了5秒。验证之后删掉冗余索引，写入时间恢复。删除冗余索引之前，先跑一遍业务核心SQL的EXPLAIN，确认没有其他查询依赖这个单列索引，再动手。

6.2 低区分度字段，建了也白建

有一张用户表，里面有个gender字段，值只有男、女、未知。我给这个字段建了索引，回调查了一遍各值占比，发现查询流量基本集中在gender = '男'，而“男”占全表的48%。这种字段的索引基本无效——走索引回表一半数据，不如直接全表扫。

低区分度字段正确的处理方式不是“建索引并祈祷”，而是要么接受全表扫描，要么用组合索引让这个字段和其他高区分度字段组合起来使用。实际项目里，(gender, last_login_time)这样的联合索引是有效的，因为gender负责粗筛，last_login_time负责把范围缩小。单独给低区分度字段建索引，大概率是负收益。

另一个常见误区是给状态字段建唯一性不强的索引。订单表里status字段若只有0、1、2、3四个值，分布可能相对均匀，查status = 0能过滤掉75%数据，但依然回表量不小。这种场景最稳妥的做法还是加组合索引，或者让范围查询配上时间字段。

6.3 前缀索引与长字段的取舍

如果要索引的字段是超长字符串，例如URL、备注、或者某个很长的标识串，直接建索引会导致索引树节点容纳的条目数变少，树变高，IO变多。解决办法是前缀索引——只取字段前N个字符建索引。

但前缀索引有两个坑得注意：一是无法用于ORDER BY和GROUP BY，因为索引里只存了前N个字符，完整值没存；二是选择性可能下降，如果字段前几个字符区分度很低，比如存的是带固定前缀的编号，你取10个字符做前缀索引，可能还是大面积重复。

实际选择前缀长度时，可以用“估算区分度”的方法：执行

SELECT COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS selectivity FROM t_page;

对比不同前缀长度下的区分度比例，找到一个接近全列区分度且长度可接受的值。一般建议区分度在0.9以上再考虑使用，太小的话不如直接全列索引或者另想方案。

6.4 删除索引的窗口期与验证策略

最近有个项目因为一张大表索引太多（7个索引），写入延迟明显上升，业务方要求清理。我的做法是分三步走：

1. 统计所有索引的使用频率，从慢查询日志、开启performance_schema后的统计信息里找出哪些索引从未被命中
2. 对于疑似冗余的索引，在测试环境用业务核心查询脚本回归所有核心SQL的EXPLAIN，确认没有生产依赖
3. 选择业务低峰期删除，先删一个，观察几个小时的CPU、IO、慢查询情况，确认无误再删下一个

这个策略听上去稳妥，其实也依赖一个前提：慢查询日志覆盖度够高。如果业务有大量低频但重要的报表查询没有跑在统计窗口，你删掉的索引很可能在月末结算那天引发事故。说句实在话，我开始也删错过一个看起来“完全没用”的索引，后来月末对账查询直接全表扫描，被业务同事找了好几次。所以删除索引这件事，宁可保守，不要激进。

6.5 全表扫描不一定最差，索引全扫描才是隐形杀手

有一种很容易被忽视的性能陷阱：type=index，即全索引扫描。表面看执行计划里用了索引，但实际是把整棵索引树从上到下扫一遍。

这种情况常常出现在排序查询里：ORDER BY create_time，且create_time刚好是某个索引的列。优化器可能选择直接扫描索引树，利用索引有序性避免filesort，但代价是它要读取这个索引的所有叶子节点。如果你的索引很大，这个代价接近全表扫描甚至更糟，因为索引页比数据页密度高，读的页数可能更多。

判断标准依然是EXPLAIN的rows和实际耗时。如果type=index但rows接近表行数，就该考虑查询本身是不是数据量过大、过滤条件太少。这类问题的根治方案通常是缩小查询范围，而不是调整索引。

7. 写在最后：把索引思维变成下意识

我接触过不少开发同学，谈到索引能说出不少理论，但一处理实际问题就抓瞎。差距不在理论储备，而在“是否把索引当成查询设计的一部分，而不是事后补救的工具”。每写一条SQL，如果脑子里能自动跑一遍：字段类型匹配吗？函数有没有套在列上？联合索引的顺序对不对？会不会大量回表？EXPLAIN的type是什么级别？很多慢查询事故压根不会发生。

还有个小习惯我特别推荐：在自己负责的业务模块里建立一个查询清单，把核心SQL对应的执行计划快照定期留档。数据量增长后，执行计划是否变化、索引是否还在生效，通过对比快照一目了然。这个方法不能解决所有性能问题，但至少能让你在业务数据翻倍之前，提前发现隐患。

索引优化不是做完一次就结束了，它是和数据增长同步演进的持续工作。翻过“会加索引”这道坎，真正理解优化器怎么选路、为什么选这条路，你在SQL这条路上才算真的进阶了。