数据库索引：决定查询快慢的数据结构

在排查数据库性能问题时，人们首先想到的往往是加机器、加内存、加缓存。但在实际项目中，同一条 SQL 语句，在数据量相同、硬件相同的情况下，执行时间可能从几毫秒变成几十秒。造成这种差距的最常见原因，是这条查询有没有用上合适的索引。

本文介绍数据库索引的底层结构与工作原理，分析聚簇索引、联合索引和索引失效等问题，并总结设计索引的常见方法。文中以常见的关系型数据库为例，具体细节因产品和存储引擎不同而有差异。

一、没有索引时会发生什么

数据库中的表，本质上是存放在磁盘上的大量记录。如果没有任何辅助结构，要找出满足 WHERE user_id = 42 的记录，数据库只能从第一条记录开始逐条读取、逐条比较，直到读完整张表。这种方式称为全表扫描。

对于只有几百行的小表，全表扫描完全可以接受；但当表中有数千万行时，每次查询都需要读取数 GB 的数据，其中绝大部分与查询结果毫无关系。

索引的作用，就像书末的关键词索引：先在一个有序的、体积小得多的结构中定位目标，再直接翻到对应的位置读取数据，而不必把整本书从头读一遍。

二、存储层次与数据页

要理解索引为什么这样设计，需要先了解数据库面对的存储环境：

具体数值因设备不同而有差异，但数量级关系基本稳定：一次磁盘随机读取的时间，足够在内存中完成成千上万次比较。因此，衡量一次查询的代价，最重要的指标不是比较了多少次，而是读了多少次磁盘。

数据库与磁盘之间交换数据的最小单位不是一条记录，而是页（Page）。常见的页大小为 4 KB 至 16 KB。即使只需要读取一条几十字节的记录，也要把它所在的整个页加载进内存。数据库会在内存中维护一块缓冲池，缓存最近访问过的页，命中缓冲池时无需访问磁盘。

三、B+ 树：为磁盘设计的查找结构

既然磁盘访问的代价远高于内存中的比较，理想的索引结构就应当让一次查找读取尽可能少的页。

为什么不用二叉查找树

平衡二叉树的查找复杂度是 O(log n)，看起来已经很好。但它每个节点只有两个分支，一千万条记录需要大约 24 层。如果每层节点位于不同的页，一次查找就需要 24 次磁盘读取。

为什么不用哈希表

哈希表的等值查找只需要常数时间，但它不保存键的顺序，无法高效地支持范围查询（BETWEEN、>、<）、排序（ORDER BY）和前缀匹配。而这些操作在实际业务中极为常见。

B+ 树的结构

B+ 树是绝大多数关系型数据库采用的索引结构，它有以下特点：

• 多路分支：每个节点占据一个页，一个页中可以存放数百乃至上千个键，因此每个节点有数百乃至上千个子节点；
• 数据只在叶子节点：内部节点只保存键和指向子节点的指针，不保存完整记录，从而容纳更多的键；
• 叶子节点有序且相互链接：所有叶子节点按键的顺序排列，并用指针串成链表，范围查询只需找到起点，然后沿链表顺序读取。

一次估算

以 16 KB 的页、8 字节的整数主键、6 字节的指针为例，一个内部节点大约可以存放 16384 ÷ 14 ≈ 1170 个键。如果每条记录约 1 KB，一个叶子节点可以存放约 16 条记录。那么：

• 两层 B+ 树可以容纳约 1170 × 16 ≈ 1.9 万条记录；
• 三层 B+ 树可以容纳约 1170 × 1170 × 16 ≈ 2000 万条记录。

也就是说，在两千万行的表中，按主键查找一条记录只需读取 3 个页。而根节点和上层节点访问频繁，通常常驻缓冲池，实际的磁盘读取往往只有一次。

四、聚簇索引与二级索引

聚簇索引

在许多存储引擎中，表的数据本身就是按主键组织的一棵 B+ 树，叶子节点中存放的是完整的记录。这种索引称为聚簇索引。一张表只能有一个聚簇索引，因为数据只能按一种顺序物理存放。

二级索引与回表

在其他列上建立的索引称为二级索引。二级索引同样是一棵 B+ 树，但它的叶子节点中只保存索引列的值和对应记录的主键。

通过二级索引查询时，数据库先在二级索引中找到主键，再拿着主键到聚簇索引中查找完整的记录。第二步称为回表。如果一次查询命中了大量记录，每条记录都要回表一次，而这些主键在聚簇索引中往往分散在不同的页上，就会产生大量的随机读取。

覆盖索引

如果查询需要的所有列恰好都包含在某个二级索引中，数据库就可以直接从索引中返回结果，无需回表。这种情况称为覆盖索引。例如，在 (user_id, created_at) 上建立索引后，下面的查询完全不需要访问表数据：

SELECT created_at FROM orders WHERE user_id = 42;

覆盖索引是最有效的查询优化手段之一，代价是索引会占用更多空间。

五、联合索引与最左前缀原则

在多个列上建立的索引称为联合索引。联合索引 (a, b, c) 中的记录，先按 a 排序，a 相同时按 b 排序，b 也相同时按 c 排序，就像电话簿先按姓、再按名排序一样。

这种排序方式决定了联合索引的使用规则，即最左前缀原则：

• WHERE a = 1：可以使用索引；
• WHERE a = 1 AND b = 2：可以使用索引；
• WHERE a = 1 AND b = 2 AND c = 3：可以完整使用索引；
• WHERE b = 2：无法使用索引定位，因为在 a 不确定的情况下，b 的值在索引中并不有序；
• WHERE a = 1 AND c = 3：只能用 a 定位，c 的条件需要在定位后的范围内逐条过滤。

还有一个容易被忽视的细节：范围条件之后的列无法用于定位。对于 WHERE a = 1 AND b > 10 AND c = 3，索引可以定位到 a = 1 且 b > 10 的起点，但在这个范围内 c 并不有序，只能逐条检查。因此，设计联合索引时，通常把等值条件的列放在前面，把范围条件的列放在后面。

六、索引失效的常见场景

建了索引并不代表查询一定会用上它。以下几种写法，常常让索引无法发挥作用：

对索引列使用函数或运算。 WHERE YEAR(created_at) = 2026 需要对每一行计算函数值才能比较，索引中保存的原始值无法直接使用。应当改写为范围条件：WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'。

隐式类型转换。 当字符串类型的列与数字比较时，例如 WHERE phone = 13800000000，数据库可能会把每一行的列值转换为数字再比较，效果等同于对列使用了函数。

以通配符开头的模糊匹配。 LIKE 'abc%' 可以利用索引的顺序，而 LIKE '%abc' 无法确定起点，只能扫描全部记录。

选择性过低。 如果某列只有少数几种取值，例如性别或状态标志，按它过滤仍然会命中大量记录，加上回表的开销，优化器可能判断全表扫描反而更快。

OR 连接不同列的条件。 WHERE a = 1 OR b = 2 中，如果 a 和 b 不是都有可用的索引，数据库往往只能扫描全表。

需要注意的是，是否使用索引最终由查询优化器根据统计信息估算代价后决定。同样的语句，在数据分布不同时可能选择不同的执行方式。

七、索引的代价

索引能加快查询，但它并非没有成本：

1. 写入变慢：每次插入、删除或修改索引列时，数据库都要同步更新每一个相关的索引。一张表上有十个索引，一次插入就要修改十一棵 B+ 树；
2. 占用空间：索引本身需要存储，并与表数据竞争缓冲池的内存。索引过多时，真正常用的数据页反而更容易被挤出缓冲池；
3. 页分裂：当新记录需要插入一个已满的页时，数据库要把该页拆分为两个，并调整上层节点。页分裂不仅开销大，还会让页的填充率下降，造成空间浪费。

页分裂与主键的选择密切相关。使用自增整数作为主键时，新记录总是追加在聚簇索引的末尾，几乎不会引起分裂；而使用随机值（例如随机生成的 UUID）作为主键时，新记录会插入到索引的任意位置，频繁引起页分裂和随机写入。此外，二级索引的叶子节点中都保存着主键，主键越长，所有二级索引也越大。

八、设计索引的常见方法

1. 从查询出发，而不是从表结构出发

索引应当服务于实际执行的查询。先梳理出系统中最频繁、最关键的查询语句，再根据它们的过滤条件、排序方式和返回列来设计索引，而不是给每个看起来重要的列都建一个索引。

2. 优先考虑高选择性的列

选择性是指一列中不同取值的数量与总行数之比。选择性越高，按该列过滤后剩下的记录越少，索引的效果越好。用户 ID、订单号等列通常选择性很高，状态、类型等列通常选择性很低。

3. 用一个联合索引代替多个单列索引

对于经常一起出现在条件中的多个列，一个设计得当的联合索引，往往比多个单列索引更有效。同时，联合索引 (a, b) 已经能够服务只查询 a 的语句，再单独建立 (a) 就是冗余索引，只会增加写入开销。

4. 让排序也用上索引

如果查询包含 ORDER BY，而排序的列顺序与索引一致，数据库可以直接按索引顺序读取，省去额外的排序步骤。例如 WHERE user_id = ? ORDER BY created_at DESC LIMIT 20，在 (user_id, created_at) 上建立索引后，只需读取 20 条记录即可返回。

5. 选择短小、递增的主键

主键会出现在每一个二级索引中，也决定了聚簇索引的插入方式。在没有特殊需求时，使用自增整数或按时间递增生成的 ID，能够减少页分裂，也能让所有索引更紧凑。

6. 对长字符串使用前缀索引

对于很长的字符串列，可以只索引前若干个字符，以较小的空间换取足够的选择性。代价是前缀索引无法用于覆盖索引，也无法完全支持排序。

九、分析工具

索引是否生效，很难仅凭阅读 SQL 判断，需要借助工具进行验证。

主流数据库都提供了执行计划功能，通常通过在语句前加上 EXPLAIN 查看。执行计划会展示优化器为该语句选择的访问方式、使用的索引、预计扫描的行数，以及是否需要额外的排序或临时表。

在分析时，常用的关注点包括：

• 访问方式是否为全表扫描；
• 实际使用的是哪个索引，使用了联合索引中的几个列；
• 预计扫描的行数与最终返回的行数相差是否悬殊，相差越大说明过滤越低效；
• 是否出现了额外的文件排序或临时表。

此外，慢查询日志可以记录执行时间超过阈值的语句，帮助定位真正需要优化的查询；部分数据库还提供索引使用情况的统计视图，可以找出从未被使用过的索引，将其删除以降低写入开销。

结语

索引是数据库性能优化中最基础、也最有效的手段。它在大多数情况下由优化器自动选择，对应用透明。但透明并不意味着可以忽视：索引的列顺序、查询条件的写法、主键的选择，都直接决定了一条查询是读取三个页，还是扫描整张表。

理解索引的工作原理，并不要求为每条语句都精心设计专门的索引。在绝大多数情况下，根据核心查询建立合适的联合索引、避免在条件中对索引列做运算、选择短小递增的主键，就足以获得良好的查询性能。而当面对真正的性能瓶颈时，打开执行计划，从数据如何被读取的视角审视查询，往往能发现加机器之外的优化空间。