问题0
假设你在维护一个市民系统，每个人都有唯一的身份证号，业务已经保证身份证号不会重复。如果系统需要按身份证号查询姓名，就会执行操作：select name from Citizens where id='271xxxxxxxxxxxxxxxxx';，所以，需要考虑在id上建立索引。

由于身份证号字段比较大，不适合用来作为主键。

因为使用普通索引查询需要回表，其索引树的叶子节点存放了主键值，如果主键字段过大，会造成非主键索引树占用空间过大。

那么可以选择在id字段上建立唯一索引或者普通索引，由于业务保证了id字段的唯一性，所以这两种索引的选择都是符合逻辑的。

那么该如何选择呢？

首先分析一下两种索引的查询过程

假设，执行的查询语句是 select id from T where k=5;，首先在k的索引树上找到k=5这条数据所在的数据页，数据页内部使用二分法来定位记录。

1. 如果在id上建立的是普通索引

查找到一个满足条件的记录(5, 300)，需要查找到下一个记录，直到碰到第一个不满足k=5的记录。

2. 如果在id上建立的是唯一索引

由于索引定义了唯一性，当查找到第一个满足条件的记录后，就会停止继续检索

那么这两种索引的查询数据性能差距有多少呢？

微乎其微！
因为InnoDB的数据是按页读取的，每个数据页默认是16KB。所以当找到k=5这条数据时，会将这条数据所在的数据页都读入内存。
那么对于普通索引，查找和判断下一条记录就只需要依次指针寻找和计算。

接下来是change buffer对两种索引更新数据性能的影响

首先甚么是change buffer？

当需要更新一个数据页时，如果这页数据在内存中，那么直接更新。

如果不在内存中，在不影响数据一致性的前提下，InnoDB会先将这些更新操作缓存到change buffer中，这样就不需要从磁盘读入这个数据页了。
当下次查询需要访问这个数据页时，将数据页写入内存，然后执行change buffer中与这页有关的操作。
将change buffer中的操作应用到原数据上并得到最新结果的操作成为merge

change buffer是可以持久化的数据，在内存中有拷贝，也会写入到磁盘上。

change buffer起到了甚么作用？

如果先将操作写入change buffer，减少了读磁盘，语句执行速度会有明显的提升。
而且数据读入内存需要占用buffer pool空间，所以change buffer的使用还能避免占用内存，提高了内存利用率。

甚么条件下可以使用change buffer 呢？

对于唯一索引，更新一条数据前都要判断这个操作是否违反了唯一性约束，比如插入(6,800)，就要先判断k=6这条数据是不是已经存在，这就必须先将数据页读入内存，那直接更新内存就行了，不需要change buffer，实际上也只有普通索引可以使用change buffer。

change buffer 使用的是buffer pool中的内存，所以不能无限增大。可以通过参数innodb_change_buffer_max_size设置change buffer在buffer pool中的占比。

举个例子分析一下两种索引更新数据的过程

执行：insert into T values(4, 1000);

情况一，这个记录要更新的目标页在内存中

• 唯一索引：找到3和5之间的位置，判断是否有冲突，插入值，结束
• 普通索引：找到3和5之间的位置，插入值，结束。

这种情况，唯一索引只比普通索引多了个判断，耗费微小的CPU时间

情况二，这个记录要更新的目标页不在内存中

• 唯一索引：将数据页读入内存，判断冲突，插入值，结束
• 普通索引：将更新记录写入change buffer，结束

将数据从磁盘读入内存需要随机IO访问，是数据库操作中成本最高的之一。
change buffer的使用减少了随机IO访问，对更新性能的提升十分显著。

change buffer 的使用场景

普通索引在所有场景下使用change buffer 都会提升性能吗？

因为只有merge是真正进行数据更新的过程，所以在merge前，写入change buffer的操作越多，对性能的提升越大。
所以对于写多读少的业务来说，页面在更新后马上被访问的概率较小，使用change buffer效果最好，比如账单类、日志类的系统。

反之，如果一个业务更新完数据后马上就要查询，那及时先将操作写入change buffer ，但马上就要将数据页读入内存，会立即触发merge。这样随机IO访问不会减少，反而增加了change buffer的维护代价。

索引的选择和实践

如上论述，建议尽量选择普通索引

如果所有更新后面都要立刻对这个记录进行查询，则应该关闭change buffer。在其他情况下，都可使用change buffer 提升性能。
尤其是使用机械硬盘，比如存储历史数据的库，把change buffer 尽量开大，可以显著提升性能。

change buffer 和 redo log

现在需要执行这条语句：

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

假设k1所在的数据页在内存中，k2所在的数据页不在内存中，执行这条语句的更新状态如下图：

这条语句的执行涉及到四个部分：

• 内存(buffer pool)
• redo log(ib_log_fileX)
• 数据表空间(t.ibd)
• 系统表空间(ibdata1)

这条语句的操作顺序如下：

1. page1在内存中，直接修改内存
2. page2不在内存中，于是在内存的change buffer中记录下“我要往page2中插入一行”这个信息
3. 将上述两个动作记录到redo log中

所以执行这条语句的成本是写入两处内存，一处磁盘(1，2操作合在一起写入redo log，图中的3和4)。
图中的两处虚线是后台操作，不影响更新的响应时间。

这次更新之后的读操请求的处理流程如下：

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

如果读操作发生在数据更新后不久，那数据都还在内存中。

1. 读page1时，直接从内存返回。
2. 读page2时，先把page2从磁盘读入内存，然后应用change buffer里的操作日志，生成一个正确的版本并返回结果。

可见，直到需要读page2的时候，这个数据页才会被读入内存

综上，redo log节省了随机写磁盘的IO消耗（转成顺序写），change buffer 主要节省了随机都磁盘的IO消耗

change buffer一开始是写内存的，那么如果这是机器掉电重启，会不会导致change buffer丢失？

答：不会丢失，事务提交的时候，change buffer 的操作也会记录到redo log中，所以崩溃恢复时，change buffer 也可以找回来。

change buffer的merge操作流程是啥？

1. 从磁盘读数据页到内存（旧版本）
2. 从change buffer中找到这个数据页的change buffer记录，依次应用，得到新版本的数据页
3. 写redo log。redo log包含了数据的变更和change buffer的变更。
4. merge结束

注（帮助理解上述过程）：

• redo log落盘时，会将change buffer的变更同步到ibdata中
• redo log记录了数据页的修改，和新写入的change buffer，在上述读操作发生后，page2读入内存并应用change buffer的修改后，事务提交，redo log就会直接记录page2上数据的更新
• 掉电后恢复数据有以下几种情况
  • change buffer 写入，redo log prepare，binlog未fsync()到磁盘，这部分数据丢失
  • change buffer 写入，redo log prepare， binlog 已经fsync() 到磁盘，先从binlog恢复redo log，再从redo log恢复change buffer
  • change buffer 写入，redo log、binlog都已经fsync()，直接从redo log恢复