在给 MySQL 建立二级索引的时候，从唯一性约束来说可以分为普通索引和唯一索引，但你知道这两种索引在查询和更新数据的时候性能有什么区别吗？然后 change buffer 和 redo log 的联系是什么呢？这篇文章就来学习一下普通索引和唯一索引的区别，以及涉及到的change buffer 和 redo log的联系。
1.笔记图
2.索引类型对查询性能的影响
- select id from T where k=5
- 普通索引：对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到第一个不满足 k=5 条件的记录
- 唯一索引：对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索
Tips：两者查询性能差距微乎其微。
3.索引类型对更新性能的影响
- change buffer：
- 当需要更新一个数据页时，如果数据页在内存中就直接更新
- 如果数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中
- 下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作
- change buffer 在内存中有拷贝，也会被写入到磁盘上
- 将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升，数据读入内存是需要占用 buffer pool 的，这种方式还能够避免占用内存，提高内存利用率
- merge
- 将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge
- 访问数据页会触发 merge，系统有后台线程会定期 merge
- 在数据库正常关闭（shutdown）的过程中，也会执行 merge
- 什么条件下可以使用 change buffer？
- 普通索引：
- 普通索引的更新可以使用 change buffer
- change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置，这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%
- 唯一索引：对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，唯一索引的更新就不能使用 change buffer
4.普通索引和唯一索引该怎么选择
- 这两种索引在查询能力上是没差别的，主要考虑的是对更新性能的影响，由于唯一索引不能使用到 change buffer，索引尽量使用普通索引
- 如果所有的更新后面，都马上伴随着对这个记录的查询，应该关闭 change buffer
- 普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的
5.change buffer 和 redo log
- 插入数据：insert into t(id,k) values(id1,k1),(id2,k2);
- 假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中
- Page 1 在内存中，直接更新内存
- Page 2 没有在内存中，就在内存的 change buffer 区域，记录下 我要往 Page 2 插入一行 这个信息
将上述两个动作记入 redo log 中
- 查询数据：select * from t where k in (k1, k2)
- 读 Page 1 的时候，直接从内存返回
- 读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果
- redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗
