09 | 普通索引和唯一索引，应该怎么选择？

以下内容出自《MySQL 实战 45 讲》

09 | 普通索引和唯一索引，应该怎么选择？

查询过程

性能差距：微乎其微。

InnoDB 的数据是按数据页（默认为 16KB）为单位来读写的。对于普通索引，因为符合条件的行附近的行也在这个页中，所以查找附近的行只需要在内存中操作，会很快。

更新过程

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新。如果数据页不在内存中，在不影响数据一致性的前提下，InnoDB 会将更新操作缓存在 change buffer 中。在下次查询需要访问这个数据页的时候，将数据页读入内存中，然后执行 change buff 中与这个页有关的操作（称为 merge）。

change buffer 在内存中有拷贝，也会被写入到磁盘上。

使用场景：写多读少的场景。

触发 merge 的情况：

• 访问数据页
• 后台线程定期 merge
• 数据库正常关闭（shutdown）

使用 change buffer 的好处

• 减少读磁盘，语句的执行速度会提升。
• 减少内存占用，提升内存利用率： change buffer 虽然还是需要内存占用(记录数据更新动作)，但相比于数据页来说(默认16K)，所占的内存还是小了很多的。

什么条件下可以使用 change buffer ?

对于唯一索引来说，更新要判断是否违反唯一性约束。需要将数据页读入内存，因为无法用到 change buffer。

实际上，只有普通索引可以使用 change buffer 。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 25 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 25%。

-- 默认 25
show variables like 'innodb_change_buffer_max_size';

更新流程

如果向表中插入一条数据：

• 目标页在内存中

  • 唯一索引：找到位置，判断是否有冲突，然后插入
  • 普通索引：找到位置，然后插入

• 目标页不在内存中

  • 唯一索引：数据页读入内存，判断是否有冲突，然后插入
  • 普通索引：更新记录到 change buffer

索引选择

普通索引和唯一索引，这两类索引在查询能力上是没差别的，在更新性能上，普通索引更好，所以尽量选择普通索引。

如果所有更新后面，都马上伴随着这个记录的查询，则应该关闭 change buffer。

redo log 和 change buffer

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。