MySQL唯一索引和普通索引

create table t(
    id int primary key,
    name varchar(16),
    k int not null
) engine = InnoDB;

insert into t(id, name, k) values
(1, 'Java', 100),
(2, 'Python', 200),
(3, 'Go', 300),
(5, 'MySQL', 500),
(6, 'Spark', 600)

我们使用上节的SQL进行分析普通索引和唯一索引的区别。

普通索引和唯一索引查询

select * from t where k = 500;

查询语句会在k索引树上的树根开始，按层搜索到叶子节点（点位到右下角的数据页），然后在数据页内部再通过二分法定位记录。

• 普通索引，查找到（500，5）这条记录后，还需要查找下一个记录，直到碰到第一个不满足k=500条件的记录
• 唯一索引，由于索引具有唯一性，所以查找到第一个满足条件的记录后就会停止继续检索

唯一索引带来的查询性能提升几乎微乎其微，因为InnoDB的数据是按照数据页为单位进行读取，当我们select一条记录时，不是将该记录从磁盘读出，而是以页（InnoDB中，每个数据页默认是16KB）为单位从磁盘中读入内存。

当找到k=500的记录的时候，他所在的数据页就都在内存中了，他的下一条记录大概率也在该数据页里，对于普通索引来说只是需要做一次寻址和计算，CPU成本几乎可以忽略不计。

什么是change buffer？

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

change buffer是可以持久化的数据，既可以在内存中，也会被写入磁盘。

change buffer中的操作应用到原始数据页并获取最新结果的过程称之为merge。触发merge的操作主要有：

• 查询访问数据页
• 后台线程定期merge
• 数据库正常关闭的过程中也会执行merge

change buffer使用的是buffer pool的内存，因此不能无限增大，change buffer的大小可以通过innodb_change_buffer_max_size来动态设置。比如设置为30，表示change buffer的大小最多只能占用buffer pool的30%。

唯一索引和普通索引的插入

假设我们在表中需要插入一条新的数据（4, 'Flink', 400），InnoDB对于唯一索引和普通索引的处理有些区别：

1. 假设记录要更新的目标页在内存中，处理流程如下：

• 如果是唯一索引，找到300和500之间的位置，判断没有冲突，插入该值，语句执行结束
• 如果是普通索引，找到300和500之间的位置，插入该值，语句执行结束

1. 假设记录要更新的目标页不在内存中，处理流程如下：

• 如果是唯一索引，需要将数据页读入内存，判断没有冲突插入该值，语句结束
• 如果是普通索引，则将更新记录在change buffer，语句执行结束。

数据从磁盘读入内存设计随机IO，极为耗时，change buffer因为可以减少随机磁盘的访问，所以普通索引的更新性能在特定情况下会远远高于唯一索引。

change buffer的使用场景

普通索引会使用到change buffer，一个数据页在merge之前，change buffer记录的变更越多，收益就越大。

对于写多读少的应用系统特别适合change buffer，因为页面在写完以后被访问到的概率特别小，此时change buffer将会最大可能的发挥作用。

但是如果一个应用系统读多写少，即使更新操作记录在了change buffer，但由于后续很有可能接着访问该数据页，将会立即触发merge操作。这样随机IO的次数不会减少，反而增加了change buffer的维护代价。

唯一索引和普通索引

唯一索引和普通索引在查询性能上基本没有差别，但在更新上普通索引会快于唯一索引。所以在可以选择普通索引的前提下尽可能选择普通索引。

change buffer和redo log

insert into t(id, name, k) values
(0, 'Hive', 50)
(4, 'Flink', 400)

假设k=400的数据页Page2不在内存中，k=50的数据页Page1在内存中，change buffer的更新状态图如下：

插入更新语句设计四个部分：内存、redo log、数据表空间（t.idb）、系统表空间（ibddata1）。主要过程如下：

1. Page1在内存中，可以直接更新内存
2. Page2没有内存，就在内存的chane buffer中记录下这次变更
3. 将上述两个动作记录在redo log中

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