跳至主要內容

十万个why：为什么索引多了会影响性能，根本原因分析

程序员小富大约 5 分钟

大家好，我是小富～

做业务开发，只要遇到查询变慢，大部分人的第一反应都是：“加个索引不就行了？”

结果时间一长，表里的索引越建越多。最后发现查询没快多少，写入性能反倒像拉稀一样直线下滑，甚至连普通的更新操作也开始卡顿。

大家都知道“索引建多了会影响写性能”，但到底为什么影响？底层的根本原因是什么？

今天我们就从数据库底层原理出发，盘一盘索引建多了背后的几大致命硬伤。

致命原因一：DML 操作的“写放大”开销

在数据库中，索引并不是一个虚拟的概念，它在物理磁盘上是一个个实实在在的 B+Tree 数据结构。

当我们往表里插入（INSERT）、删除（DELETE）或者更新（UPDATE）一条记录，数据库除了要修改主键索引（聚簇索引）之外，还必须去修改这颗表上关联的每一个二级索引。

我们来算一笔账：

假设一张表有 10 个二级索引。当你往里插入一条数据：

  • 数据库先写 Undolog 和 Redolog。
  • 接着写入主键索引的叶子节点。
  • 随后，必须依次去更新这 10 个二级索引的 B+Tree，把新数据对应的索引键值和主键值插进去。

一次写入，在底层变成了 11 次写操作。 这就是典型的“写放大”。

即使 InnoDB 引入了 Change Buffer 机制来缓存非唯一二级索引的写操作以减少随机 I/O，但在并发写量很大或者事务提交时，这些缓存依然要被 merge 到磁盘上，该还的账迟早要还。

致命原因二：页分裂（Page Split）引发的磁盘 I/O 震荡

二级索引的 B+Tree 节点在物理上是存储在 16KB 大小的页（Page） 里的。为了保证快速查找，页内的数据必须按照索引列的顺序进行排列。

  • 顺序写入：如果我们的索引列是自增的，那数据只需要在页的尾部追加即可，效率极高。
  • 乱序写入：如果我们建了多个索引，在插入新行时，很多索引列的值是随机无序的。这意味着数据必须插入到某些 Page 中间的某个位置。

如果目标 Page 已经装满了（16KB 全占满了），为了塞进新数据，InnoDB 就必须执行 页分裂（Page Split） 操作：

图上要写的内容： 【页分裂过程】

  1. 发现 Page A 已经装满，但有新数据需要随机插入 Page A 中间。
  2. 申请一个新的 Page B。
  3. 将 Page A 后面一半的数据移动到 Page B。
  4. 插入新数据，并更新 Page A 和 Page B 的前后双向指针。

页分裂不仅涉及大量的内存数据移动，还需要将分裂后的脏页同步写回磁盘。更糟糕的是，频繁的页分裂会导致 B+Tree 的层高增加，物理磁盘上产生大量碎片，使原本高效的顺序写入退化为慢得要死的随机 I/O。

致命原因三：榨干 Buffer Pool 的物理内存

内存是数据库的生命线。InnoDB 为了加速读写，在内存中开辟了一块巨大的区域叫 Buffer Pool，用来缓存磁盘上的数据页和索引页。

数据库的内存置换算法（LRU）会尽可能把最常访问的“热数据”留在 Buffer Pool 里。

如果我们表里建了大量的索引：

  • 每一个索引都需要占用 Buffer Pool 的空间来缓存其 B+Tree 的节点。
  • 索引文件甚至可能比原始的数据文件还要大。
  • 内存被大量的索引页占满，导致真正需要频繁读写的业务“热数据页”被无情地挤出内存，丢到了慢速磁盘上。

一旦发生这种情况，后续的查询就必须频繁去触发物理磁盘 I/O，导致系统的整体吞吐量暴跌。

致命原因四：优化器“选择困难症”延长执行计划时间

MySQL 在执行一条 SQL 之前，会通过 优化器（Optimizer） 来计算出一条开销最低的执行路径（也就是执行计划）。

计算过程是基于成本估算的（Cost-based Optimizer）。如果一张表上只有 1~2 个索引，优化器的计算几乎瞬间就能完成。

但如果一张表上建了 10 几个甚至 20 个索引，优化器就得遍历并估算这 10 几个索引各自的扫描行数、回表代价。

permutation（排列组合）计算量呈指数级上升，这会导致 解析 SQL 生成执行计划的时间（Planning Time）大幅变长。有时你查一条数据执行本身只要 2 毫秒，但网关/优化器光是纠结选哪个索引就花掉了 50 毫秒。

业内合理的索引规划套路

既然索引多了有这么多害处，我们在实际开发中该怎么规范索引设计？

目前大厂和业内比较通用的最佳实践如下：

  1. 单表索引总数控制在 5 个以内：这是性价比较高的红线。非必要不新增索引。
  2. 善用联合索引（Composite Index）代替单列索引： 联合索引 (A, B, C) 相当于同时建了 (A)、(A, B)、(A, B, C) 三个索引。利用好“最左匹配原则”可以大幅减少物理索引树的数量。
  3. 定期清理无用索引： 有些索引是历史业务遗留的，或者建了之后从来没被使用过。可以通过查询系统的 sys.schema_unused_indexes 视图直接找出这些“僵尸索引”并干掉它们。
-- 查询数据库中从未被使用过的无用索引
SELECT * FROM sys.schema_unused_indexes;

说在最后

其实说白了，索引的管理只有一句话：

索引不是免费的午餐，它是用“明天的写性能”去透支“今天的读便利”。

设计表结构时，宁可在业务上线初期少建几个索引，等后期看慢查询日志按需补充，也绝对不要图省事一次性把所有字段都建上索引。少一点无用索引，数据库在线上就能跑得更稳当。

上次编辑于: