MySQL索引选择

最近不光做产品方面的研发，同时还支持其他事业部的问题排查和优化，在支持某些业务发开的过程中,发现有很多时候为了业务查询快，在数据库中添加了很多索引，但是这往往不能提高数据库的查询效率，这按逻辑来说其实不应该，但是为什么会这样呢？我也是怀着疑问又重新看了几章 《MySQL实战45讲》 关于索引相关的内容，简单的做了些总结，防止以后遇到相同问题。

在往下看之前，我想推荐一个博文，也是在思考这些问题中查找到的一篇比较好的解释 《InnoDB中的页合并与分裂》

索引的维护

我们都知道MySQL的索引模型是B+树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。具体的大家可以看我之前的总结 《MySQL索引》，里面有介绍主键索引和普通索引的区别，以及索引的查找过程等等，这里就不多做解释了，主要总结一下索引的维护。

索引的选择影响数据页分裂和合并

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以下图为例

索引图.png

如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请 一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自 然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个 页中，整体空间利用率降低大约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

我们可以通过两个方面去分析

• 性能
  • 因为B+ 树为了维护索引有序性，因此选择自增主键的话，在插入的过程中都是追加操作，不会涉及到之前主键索引的挪动，因此也不会触发叶子节点的分裂，降低了页分裂带来的性能影响。如果我们使用UUID这种不但字节占用数比较大，而且还无序，这样在B+树维护索引时，则会触发页分裂，大大的影响了性能。
• 空间
  • 由于每个非主键索引的叶子节点上都是主键的值。如果用UUID或业务上的身份证号等去做主键，那么每个二级 索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整 型（bigint）则是 8 个字节。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

因此在我们选择主键索引的时候尽量选择自增主键往往比较合理，但是由不排除可以使用业务属性作为主键，那就是全表就一个索引，并且是唯一索引，这样的话由于没有其他索引，则不会担心其他索引叶子节点大小的问题，并且查询的条件就是主键。

普通索引和唯一索引的选择

普通索引和唯一索引从字面上的意思我想大家已经可以理解了，我就不百科了，现在让我们先来看一下下图。

索引普通or唯一.png

我们从查询和更新两方面来分析一下区别。

查询

我们执行查询语句

select id from T where k= 5

• 普通索引:查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录， 直到碰到第一个不满足 k=5 条件的记录。
• 唯一索引:由于索引的唯一性，查找到(5,500) 就会停止检索。

但是这真的很影响性能吗？ 结果是微乎其微。

因为InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的 时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。因此如果在当前页查询，多查询几条数据对CPU的消耗可以忽略不计。但是如果真的有跨页查询，那么影响的效率确实会很大。但是，我们之前计算过，对于整型字段，一个数据页可以放近千个 key，因此出现这种情 况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

更新

InnoDB storage engine

之前我们介绍了总结了《MYSQL语句执行大体流程》 其中涉及到了存储引擎层，我们今天来简单看一下存储引擎层的架构，如图

存储引擎层架构.png

这里我们主要看右侧的In-Memory Structures 其中我们看到了 Buffer Pool，而在Buffer Pool中包含了Change Buffer。我们主要了解这两个内容，如果想了解其他的可以查看 官方文档

Buffer Pool

Buffer Pool是主内存中的一个区域，InnoDB在访问它时在其中缓存表和索引数据。 缓冲池允许直接从内存中处理经常使用的数据，从而加快了处理速度。 在专用服务器上，通常将多达80％的物理内存分配给缓冲池。

为了提高大容量读取操作的效率，缓冲池被分为多个页面，这些页面可能包含多个行。 为了提高缓存管理的效率，缓冲池被实现为页面的链接列表。 使用LRU算法的变体将很少使用的数据从缓存中老化掉。

Change Buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在 内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的 时候，将数据页读入内存，然后执行 Change Buffer 中与这个页有关的操作。通过这种方 式就能保证这个数据逻辑的正确性。

Change Buffer 实际上它是可以持久化的数据,后台有线程会定期的Merge操作，当访问更新的数据页时或数据库正常关闭时也会执行Merge操作。

需要重点了解的是 Change Buffer主要是减少读磁盘，因此语句的执行速度会得 到明显的提升，而且数据读入内存是需要占用 Buffer Pool 的，所以这种方式还能够避 免占用内存，提高内存利用率。

Change Buffer的使用

我们接着上图来看

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要 插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数 据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使 用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

由于数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。因此这是选择普通索引在更新时候的好处。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更 新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过 程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以， 对于这种业务模式来说，change buffer 反而起到了副作用。

小结

对于如何选择索引，主要是针对特定的业务场景。当数据更新后立马有查询操作的时候会影响Change Buffer的维护，因此在我们业务上，过多的索引会导致MYSQL的查询性能下降，那么到底普通索引和唯一索引应该怎么选择。其实，这两类索引在查询 能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索 引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你 有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注 这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历 史数据”表的数据写入速度。