数据库索引及建立原则这个话题，几乎每个写过SQL的人都被问过，也几乎每个人都在这上面栽过跟头。我印象很深的一次，某业务系统的订单列表页在凌晨开始变慢，一条按状态加时间的分页查询从几十毫秒涨到两秒多，监控上CPU和IO都没有明显异常，最后定位下来就是一张两千万行的表上缺一个联合索引。加索引的语句只花了三分钟写，但定位花了整整一个通宵。这件事之后我对“索引”这两个字的理解彻底变了——它不是建表时随手加的装饰品，而是需要算账、需要验证、需要长期治理的基础设施。这篇内容我想把数据库索引从底层结构到建立原则再到线上治理这一整条链路讲透，包括我踩过的坑和那些文档里不太会写的东西。不管你是刚接触SQL的新手，还是已经能写复杂查询但不确定索引建得对不对的开发者，应该都能从里面拿到能直接用的东西。

1. 索引不是越多越好：先把这本成本账算清楚

1.1 一次慢查询暴露的三个典型问题

回到开头那个订单表的例子。当时那张表上有七个索引，其中五个是单列索引，状态一列、用户ID一列、创建时间一列、商户ID一列、订单号一列。开发同学的直觉是“每个常用条件都建一个”，听起来很合理，但实际执行时数据库只能选其中一个索引来用，剩下四个条件都要在回表之后逐行过滤。数据量小的时候看不出问题，一旦单状态的数据量占到全表的三成以上，优化器干脆放弃索引走全表扫描，查询时间就失控了。

这件事暴露了三个问题。第一，单列索引的堆叠不等于联合索引，多个条件同时出现在WHERE里时，数据库通常只会挑一个选择性最好的索引走，其余条件变成回表后的过滤条件。第二，索引的选择性会随数据分布变化，同一个索引在测试环境好用，在生产环境数据倾斜之后可能完全失效，因为某个枚举值占了绝大多数行。第三，索引不是免费的，每一个索引都意味着写入时要多维护一棵B+树，插入、更新、删除的代价都会上升。

我在实际项目里见过最夸张的一张表建了十二个索引，写操作的耗时是同类表的四倍。后来做了一次索引瘦身，砍掉六个冗余索引，写入TPS直接回升了百分之三十多，而读查询因为合并成了两个联合索引，反而更快了。所以“索引越多越好”这个认知必须尽早纠正，它更接近“索引越准越好”。

1.2 读写成本的量化对比

要把这本账算清楚，得知道索引到底在哪些环节收钱。下面这张表是我自己整理的一个粗略对照，帮助在评审时快速建立直觉。

这张表里最容易被忽视的是最后一行。索引数量增加之后，优化器的决策空间变大，一旦统计信息滞后或者数据分布发生突变，就可能出现“明明有更优索引却选了差的”这种情况。我遇到过一条查询在数据导入前后执行计划完全不同，导入前走索引扫描几十毫秒，导入后突然改走全表扫描。排查下来是统计信息没刷新，优化器对选择性的估算还停留在旧数据上。所以索引治理里有一条隐性原则：索引越多，统计信息的维护就越要跟上，否则收益会被决策失误吃掉。

我的经验值是，单表索引数量控制在五个以内比较健康，超过八个就要开始逐个审视必要性。这个数字不是教条，但它能提醒你在加索引之前先问一句：这个索引真的会被用到吗，还是只是让人心里踏实一点。

2. B+树为什么成了主流：底层结构的取舍逻辑

2.1 从页、扇出到树高只有三层

要理解索引原则背后的“为什么”，绕不开B+树。很多人背过“B+树矮胖、叶子节点有链表”，但不太清楚这些特性怎么直接影响我们建索引的决策。

数据库读写磁盘的最小单位是页，InnoDB默认一页十六KB。B+树的每个非叶子节点存的不是数据本身，而是键值和指向子节点的指针。假设一个键值八个字节、一个指针六个字节，那么一页大约能放下十六KB除以十四字节，一千一百多个指针。这就是所谓的扇出。扇出乘以层数就是能容纳的索引条目数量。三层树，第一层一千一百，第二层一千一百，第三层一千一百，总量已经超过十亿。换句话说，十亿级别的表，走索引查一条数据只需要三次页访问，而全表扫描需要读几十万页。

这个数字解释了两件事。第一，为什么索引对大数据量表的收益是数量级级别的，而不是百分之几十。第二，为什么索引页很容易全部缓存在内存里——三层的非叶子节点加起来不过几MB，常驻内存毫无压力，真正的IO开销都在叶子节点上。理解了这一点，就能明白为什么索引字段越短越好：键值变短，扇出变大，同样的数据量下树高可能少一层，而且索引本身占用的缓冲池空间也更少。

我做过一次对比实验，把一张表的联合索引里的一个长整型字段换成一个更紧凑的类型，索引文件大小下降了接近四成，缓冲池命中率明显提升。所以建索引时字段类型的选择不是无关紧要的细节，尤其是联合索引里的每一个字段，字节数都在被乘上记录数。

2.2 聚簇索引与二级索引：回表这件事到底贵在哪

B+树还有一个关键区别：聚簇索引的叶子节点存的是整行数据，二级索引的叶子节点存的是主键值。这个设计决定了很多查询的实际开销。

按主键查，一次B+树查找直接拿到整行，路径最短。按二级索引查，先在二级索引里找到主键，再拿主键去聚簇索引里查一次完整行，这个动作叫回表。回表意味着两棵树各走一遍，如果结果集有一千行，那就是一千次额外的树查找。虽然这些查找大概率命中缓冲池，但代价仍然存在，在结果集较大的时候尤其明显。

这也是为什么覆盖索引这么重要。如果一个查询需要的所有列都在索引里，数据库就不用回表，直接在二级索引上完成扫描。举个具体例子，假设有个联合索引建在状态和创建时间两列上，查询只要这两列，那就能走覆盖索引；如果查询还要取订单金额，就得回表取。我经常在慢查询优化里做的第一件事，就是看能不能把查询改造成覆盖索引——有时候只需要把SELECT里那几个不必要的列去掉，或者把它们补进联合索引，性能立刻上一个台阶。

代价当然也有：联合索引里塞太多列，索引会变胖，写入成本上升，扇出下降。所以覆盖索引的取舍标准是高频查询优先，别为了偶尔跑一次的报表查询去撑大一个核心表的索引。

3. 联合索引的字段顺序：最左前缀不是玄学

3.1 最左前缀原则的机械原理

联合索引的字段顺序是建索引时最需要动脑子的地方，也是面试里被问得最多的地方。但很多人只知道“最左前缀”，不知道为什么。

联合索引在B+树里的排序方式是先按第一列排序，第一列相同时按第二列排序，第二列相同时按第三列排序。这跟电话簿按姓氏排、同姓按名字排是一个道理。所以当查询条件里只有第二列时，第二列在整个索引里是乱序的，数据库没法用二分查找定位，只能扫全部索引条目再过滤，这时候优化器通常干脆不用这个索引。

这里有个细节值得展开。假设联合索引是（A, B, C），查询条件是A=1 AND C=3。很多人以为会用到A这一列的前缀然后过滤C，实际上确实是这样，A的部分能用上，但B在中间断了，C无法用索引定位，只能在A=1的结果集里过滤C。如果A=1的数据量很大，这个索引的效果就接近没用。所以联合索引设计时要考虑的不是“这些字段是否经常一起出现”，而是“这些字段能否形成一个连续的可定位前缀”。

3.2 字段顺序的三条排序规则

字段顺序怎么定，我给三条自己常用的规则，基本能覆盖九成场景。

第一条，等值条件在前，范围条件在后。原因很直接，等值条件能用二分快速收窄范围，而一旦遇到范围条件，后面的字段就失去了定位能力。比如索引（状态, 创建时间），查询是状态=已完成 AND 创建时间在某个区间，这个顺序是对的，因为先靠等值把范围收窄到“已完成”的那批，再在批内做时间区间扫描。反过来（创建时间, 状态）就差得多，因为时间区间一开，状态就没法用于定位了。

第二条，区分度高的字段优先。区分度指的是某个字段的不同取值数量，可以简单算成不重复值数量除以总行数。这个值越接近一，说明越能通过它唯一定位到少量行。一个状态字段只有五种取值，区分度接近零，放在联合索引首位能筛掉的量很有限；而一个用户ID区分度接近一，放首位能瞬间把范围压到几行。不过这条规则要给第一条让路——如果高区分度字段是范围条件，还是得排在等值条件之后。

第三条，ORDER BY和GROUP BY的字段要能接在等值条件后面。如果查询里带了排序，而我们希望数据库利用索引的有序性省掉排序操作，那排序列就必须紧跟在所有等值条件之后，并且顺序、方向一致。这一点常被忽视，很多人建了索引但排序还是走filesort，就是因为排序列在索引里的位置不对。

3.3 一个字段顺序决策的完整推演

拿一个实际的查询来推演。假设订单表有一个高频查询：按商户ID和状态筛选，按创建时间倒序分页，SQL大致是下面这样。

SELECT id, order_no, amount, created_at FROM t_order WHERE merchant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20;

候选的联合索引顺序有六种排列，我们用上面三条规则逐一排除。首先，merchant_id和status都是等值条件，created_at是排序列，所以created_at必须排在两者之后，这就排除掉了所有created_at不在末位的组合。剩下（merchant_id, status, created_at）和（status, merchant_id, created_at）。再看区分度，商户ID的取值数量通常远大于状态，所以把merchant_id放前面能把范围压得更小。最终选（merchant_id, status, created_at）。

这个索引能同时满足过滤和排序两个需求，LIMIT 20的查询基本只需要扫二十条索引条目加二十次回表。如果反过来建成（status, created_at, merchant_id），虽然也能用上状态这一列，但排序会断掉，数据库需要把满足status的全部行取出来再排序，深分页时性能差异能有几十倍。

提示：这里还有一个容易忽略的点——如果created_at的排序方向与索引不一致，比如索引按ASC建的而查询要DESC，在MySQL 8.0之前是无法利用索引排序的。8.0之后支持降序索引，可以显式指定DESC建索引。如果版本较老，要么调整索引方向，要么接受filesort。

4. 建立索引的五条硬原则与量化方法

4.1 用选择性算清楚一个索引值不值得建

“这个字段要不要建索引”这个问题，靠感觉判断很容易翻车。我习惯先算选择性，公式很简单：选择性 = 不重复值数量 / 总行数。结果越接近一，索引的过滤能力越强。

SELECT COUNT(DISTINCT status) / COUNT(*) AS status_selectivity, COUNT(DISTINCT merchant_id) / COUNT(*) AS merchant_selectivity, COUNT(DISTINCT order_no) / COUNT(*) AS order_no_selectivity FROM t_order;

假设总行数两千万，status有五种取值，选择性大概0.00000025，这个数字看着小，但含义是“用状态过滤平均只能把范围缩到四百万行”，显然单靠它建索引意义有限。merchant_id有五十万个不同值，选择性约0.025，平均每个商户四十行，这个过滤力度就非常可观了。order_no是唯一键，选择性等于一，属于最优。

经验门槛是：选择性低于0.001的字段，单独建索引基本没意义，除非它总是跟其他条件组合成联合索引，并且组合后整体选择性足够高。这里要强调“组合后”三个字——联合索引的选择性不等于各列选择性相乘，但如果组合后的唯一值数量接近表行数，那这个联合索引就很值。

4.2 哪些字段坚决不该建索引

反过来，有几类字段我基本不会给它们建索引，理由各自不同。

更新极其频繁的字段。比如某些计数列、状态流转列，如果每次业务操作都要改，那索引维护成本会非常高。我见过一个场景，一个状态字段每秒被更新上千次，建了索引之后写入延迟明显上升，最后改成在业务层做异步汇总才解决。

取值极少的枚举字段单独建索引。性别、是否删除、类型这类字段，单独建索引几乎必然被优化器放弃，除非数据极度倾斜——比如百分之九十九的数据都是“已删除”，而查询永远只查未删除的那百分之一，这种情况下可以考虑，但更好的做法往往是归档。

长度很大的文本字段。如果一定要在长字符串上查，优先建前缀索引而不是全字段索引。前缀长度的选择标准是让前缀的选择性接近全字段选择性，可以从几个长度开始试算。

SELECT COUNT(DISTINCT LEFT(remark, 8)) / COUNT(*) AS sel_8, COUNT(DISTINCT LEFT(remark, 16)) / COUNT(*) AS sel_16, COUNT(DISTINCT LEFT(remark, 32)) / COUNT(*) AS sel_32 FROM t_order;

选一个选择性已经足够接近饱和的长度，通常能省下一半以上的索引空间。代价是前缀索引无法用于覆盖索引，也无法用于排序。

低频查询涉及的字段。一个每天跑一次的报表查询，为它建一个索引，代价是全天候的写入开销。这种情况下更好的方案是做只读副本或者离线汇总表。

已经能被其他索引前缀覆盖的字段。这是最典型的冗余，后面治理章节会细讲。

4.3 索引设计与查询写法必须互相迁就

有个认知我觉得很重要：索引不是被动适应查询的，查询也要主动迁就索引。很多性能问题不是索引建错了，而是SQL把本来能用的索引用废了。

举个最常见的例子：在索引列上做函数运算。WHERE DATE(created_at) = '2024-01-01'这种写法会让created_at上的索引用不上，因为数据库必须先对每一行算出日期再比较。改成范围写法created_at >= '2024-01-01' AND created_at < '2024-01-02'就能正常走索引。这个改动的收益在千万级表上通常是从秒级到毫秒级的差别。

还有隐式类型转换。如果订单号在表里是字符串类型，而查询传的是数字，数据库会把列转换成数字再比较，索引直接失效。这类问题在有强类型语言的团队里反而更常见，因为参数从接口传下来可能已经变成数字了。我在排查慢查询时习惯先看一眼参数类型和列类型是否一致，这个检查往往能在五分钟内定位问题。

5. 实操：从建索引到验证的完整闭环

5.1 建索引的写法选择与在线变更注意事项

建索引的语法本身很简单，但在生产环境上执行需要考虑锁和时长。普通写法如下。

ALTER TABLE t_order ADD INDEX idx_merchant_status_time (merchant_id, status, created_at);

大表上直接执行这个语句，在早期版本里会锁表，现在主流版本支持在线DDL，但仍有几个细节要盯住。第一，确认是否真的走了在线模式，有些操作会降级成拷贝表，比如某些版本里对索引类型或列类型有特殊要求时。第二，观察主从延迟，大表加索引会在主库产生大量写入，从库回放可能滞后，如果业务读从库，要评估影响窗口。第三，避开业务高峰，即使是Online DDL也会消耗IO和CPU，凌晨执行比白天安全得多。

我自己的习惯是先在预发环境用生产数据量的副本跑一遍，记录耗时，再决定是直接执行还是用工具分批处理。预发环境的数据量如果只有生产的十分之一，那测出来的时间基本没有参考价值，这一点必须提前对齐。

5.2 用EXPLAIN把执行计划逐列读透

索引建完不等于生效，必须验证。EXPLAIN是基本工具，但很多人只看type列，这就浪费了大半信息。

key_len这一列特别有用。假设联合索引是（merchant_id BIGINT, status TINYINT, created_at DATETIME），如果key_len只显示了8，说明只用到merchant_id；显示9说明用到了merchant_id和status；全部用上会是17左右（具体取决于是否可空）。用这个数字可以直接判断前缀用到了第几列，比猜测靠谱得多。

还有一种情况是EXPLAIN看不出问题的，比如实际执行时优化器因为参数不同选了不同的计划。这时候可以用EXPLAIN ANALYZE，它会真正执行查询并给出实际耗时和行数，MySQL 8.0.18之后可用。对于参数敏感的查询，我一般会拿几个典型参数值分别跑一遍，确认计划稳定。

5.3 上线前的压测与回滚预案

索引变更属于结构变更，必须有回滚方案。回滚本身很简单，DROP INDEX即可，但要注意两点：一是删除索引同样是在线DDL，耗时可能比创建短但不是瞬间完成；二是如果这个索引是某个查询的唯一支撑，删除后该查询会立刻退化，所以要提前确认没有强依赖。

压测环节我通常会做三组对比：变更前的基线、变更后的读性能、变更后的写性能。读性能看目标查询的P99延迟，写性能看同一张表上的插入和更新吞吐。曾经有一次加索引之后目标查询快了，但写入吞吐掉了百分之十五，评估之后认为可以接受，因为写操作不在高峰期。如果反过来是写密集表，那就得重新权衡。

6. 索引失效的典型场景与排查实录

6.1 失效场景速查表

索引失效的原因看着五花八门，归纳下来就那么几类。我整理成一张表，排查时对着过一遍基本能覆盖。

表里最隐蔽的是“优化器主动放弃”。这种情况索引明明存在，条件也符合最左前缀，但优化器算了一笔账，认为走索引要回表太多次，不如全表扫描。数据倾斜严重时特别容易出现——比如某个商户ID占了全表一半的数据，查这个商户时走索引反而不如全扫。这种不是bug，是优化器的合理选择，但如果业务上确实需要优化这个查询，就得从数据分布或者查询改写入手，而不是硬加索引。

6.2 一次线上排查的完整过程

讲一个具体的排查过程，比列条目更有参考价值。

某天下午收到反馈，某个后台页面的列表加载超时。第一步，从慢查询日志里捞出对应的SQL，确认是那条带三个条件的查询。第二步，跑EXPLAIN，发现type是ALL，key是NULL，说明压根没走索引。第三步，看表结构，发现这三个条件对应的字段确实都在一个联合索引里，但顺序是（状态, 创建时间, 商户ID），而查询条件是商户ID加状态加时间区间。商户ID在索引最后一位，用不上。第四步，确认索引顺序设计有问题，新加了一个（商户ID, 状态, 创建时间）的索引，同时把老索引标记为待下线。第五步，验证新索引的key_len符合预期，P99从两秒多降到三十毫秒。

整个过程中最容易走弯路的是第三步到第四步之间——如果只看“有没有索引”，会觉得索引是存在的，问题可能出在别处。真正的判断依据是索引的字段顺序是否匹配查询条件的形态，这一点必须结合EXPLAIN的key_len一起看。

注意：排查时不要急着改代码或加索引，先把执行计划、表结构、数据量、参数值四样东西凑齐。少任何一样，结论都可能是错的。我吃过一次亏，凭猜测加了个索引，结果发现真正的原因是参数传了空字符串导致条件失效，白折腾了半天。

7. 索引治理：冗余识别与长期维护机制

7.1 用系统表找出冗余和重复索引

索引治理不是一次性动作，得定期做。最省事的入口是数据库自带的信息表，可以直接查出所有索引及其字段顺序。

SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols, COUNT(*) AS col_cnt FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db' GROUP BY TABLE_NAME, INDEX_NAME ORDER BY TABLE_NAME, col_cnt DESC;

拿到结果之后，按表分组人工扫一遍，重点找两类问题。第一类是完全重复，也就是两个索引的字段和顺序完全一样，只是名字不同，这种直接删。第二类是前缀包含，比如已有索引（A, B, C），又建了（A）和（A, B），后两个都是冗余的，因为前者能被完整覆盖。这类冗余在大表上很常见，往往是不问历史、只加不删积累出来的。

还有一类需要谨慎判断的，是字段集合相同但顺序不同的索引，比如（A, B）和（B, A）。它们在服务不同查询时都有价值，不能一删了之。判断标准是有没有查询只能用其中一个——如果两个都有明确的查询在依赖，就都留着。

另外别忘了看一眼索引的使用统计。很多数据库提供了索引使用次数的视图，运行一段时间后，使用次数为零的索引就是明确的清理候选。清理之前建议先把它标记出来观察一两周，避免刚好碰上低频查询的周期。

7.2 把索引评审固化进开发流程

最后说一个流程层面的事。索引问题反复出现，根本原因往往不是技术难度，而是缺少评审环节。我的做法是在建表或者变更表结构的评审清单里，强制包含以下几项：

• 新增索引必须附上对应的查询SQL和EXPLAIN截图，证明它确实被使用；
• 联合索引必须说明字段顺序的理由，特别是为什么排序列放在那个位置；
• 新增索引后必须做一次写入压测，确认写性能下降在可接受范围内；
• 每个索引都要有负责人和创建时间备注，方便后续清理时找到上下文；
• 表结构变更单里必须包含回滚语句。

这套流程刚推行时有人觉得繁琐，但两个月后效果很明显——新增索引的数量下降了，但慢查询的数量也下降了，说明之前那些索引里有相当一部分是无效建设。

8. 我在索引这件事上的几点实际体会

关于索引字段类型，我有个教训值得分享。早期建表时习惯用大整数做主键，觉得反正是自增，无所谓。后来在一张超大表上算了一笔账，主键字段每多几个字节，所有二级索引的叶子节点都要跟着变长，因为二级索引里存的是主键值。一张表如果有五个二级索引，主键从八字节缩到四字节，省下来的空间要乘以五。这个优化在千万级表上能实实在在地提升缓冲池命中率，属于改一次长期受益的事。

关于统计信息，我现在的习惯是在大批量数据导入或者大范围更新之后，主动刷新一次统计信息。这个动作花不了多少时间，但能避免优化器拿着过时的估算做出错误决策。有些团队会把它写进数据导入脚本的最后一步，我觉得这是个好习惯。

关于索引数量，我从“多建几个保险”彻底转向了“少而准”。现在每加一个索引，我都会问自己三个问题：它服务哪条具体的查询；这条查询有多高的频率；去掉它之后那条查询会慢多少。三个问题答不上来，就先不加。这个习惯让我负责的几张核心表索引数量都控制在四到六个，写性能和读性能都比较均衡。

最后提一个容易被忘的点：索引也会随业务演进而过期。半年前高频的查询条件，可能随着功能下线就没人用了，但索引还在那里收写入成本。所以我一般在每个季度末花半小时扫一遍核心表，看看有没有可以清理的索引。这个动作本身不产生任何功能价值，但它能让系统在长期运行中保持一个健康的状态。