9 MySQL: 普通索引和唯一索引，应该怎么选择？

假设维护一个市民系统，每个人都有个唯一的身份证号，而且业务代码也保证不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的SQL语句：

//身份证号比较长，不适合做主键。
//那给这个字段是创建 唯一索引 还是 普通索引 ？？？
select name from CUser where id_card = 'xxxxx'

我们从两种索引对查询语句和更新语句的性能影响来进行分析

查询过程

• 普通索引，查到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录
• 唯一索引，查找到第一个满足条件的记录后，就停止继续检索
• 两种性能差距微乎其微，InnoDB以数据页为单位来读写，读一条记录时，这条记录所在的页整体读入内存；所以读到记录的时候，它所在的数据页已在内存了；若这条记录刚好是最后一条记录，则需要读取下一个数据页。

更新过程

change buffer

• 当需要更新一个数据页时，如果数据页在内存中就直接更新，否则在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页。下次查询需要访问这个数据页时，将数据页读入内存，然后执行change buffer中与这个页有关的操作
• chagne buffer 在内存中拷贝，也会被写入到磁盘上。
• 将chagne buffer中的操作应用到原数据页，得到最新的过程称为merge。

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

• 对于唯一索引：所有的操作都必须进行唯一性约束，也就是需要判断当前更新的数据是否已经存在，而数据只能在已经读入内存才能判断。这里都已经在内存了，就没必要使用change buffer
• change buffer 用的是 buffer pool的内存，因此不能无线增大。可以通过参数 innodb_change_buffer_max_size来动态设置。

更新过程

举例：参考“4 索引1”中的例子，如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的

图 1 InnoDB 的索引组织结构

1. 记录要更新的目标页在内存中

• 对唯一索引，找到3 和 5 之间的位置，判断到没有冲突，插入，执行结束
• 对普通索引，找到3 和 5 之间的位置，插入，执行结束

2. 目标页不在内存中

• 对唯一索引，将数据页读入内存，判断有没有冲突，插入，结束
• 对普通索引，将更新记录在change buffer中，执行结束

change buffer 减少了随机磁盘访问，提升了更新的性能

chagne buffer 使用场景

merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），性能提升越明显

• 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统
• 更新之后马上查询的这种业务，更新先记录在change buffer，由于要马上查询这个数据页，立即触发merge，导致随机访问IO次数多，反而增加 change buffer的维护代价。

索引选择和实践

普通索引和唯一索引在查询上的性能差别不大，主要是更新性能的影响。尽量选择普通索引

在实际使用中，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的

change buffer 和 redo log

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如图 所示是带 change buffer 的更新状态图。

如图： change buffer的更新过程
上图涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。

系统表空间：用来放系统信息的，比如数据字典什么的，对应的磁盘文件是ibdata1
数据表空间：就是一个个的表数据文件，对应的磁盘文件就是 表名.ibd

这条更新语句做了如下的操作（按照图中的数字顺序）：

1. Page 1 在内存中，直接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中（图中 3 和 4）。

执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

接下来我们现在要执行

select * from t where k in (k1, k2)。

这里，两个读请求的流程图。如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。图中就没画出这两部分。

如图 带 change buffer 的读过程

1. 读 Page 1 的时候，直接从内存返回。WAL 之后如果读数据，是不是一定要读盘，是不是一定要从 redo log 里面把数据更新以后才可以返回？其实是不用的。你可以看一下图 3 的这个状态，虽然磁盘上还是之前的数据，但是这里直接从内存返回结果，结果是正确的。
2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。
3. 可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。

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

思考题时间

change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？
change buffer 丢失可不是小事儿，再从磁盘读入数据可就没有了 merge 过程，就等于是数据丢失了。会不会出现这种情况呢

不会丢失，在事务提交的时候，我们把 change buffer 的操作也记录到 redo log（crash safe） 里了，所以崩溃恢复的时候，change buffer 也能找回来。
**merge 的执行流程**：
 从磁盘读入数据页到内存（老版本的数据页）；
 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

本文是通过学习极客时间关于MySQL相关的课程，做的学习笔记，有错误的地方，请网友提出，大家共同学习，后续陆续更新！扫描下方二维码，可以共同学习。