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

查询性能

• 「where = 」时，唯一索引找到了立马返回，普通索引需要找到下一个不等于的值
• 因为 InnoDB 的数据是按数据页为单位读写，所以性能差距微乎其微
  • 对于整形字段，一个 16KB 的数据页可以放近千个 key

Change Buffer

• 当需要更新一个数据页时，如果数据页在内存中就直接更新

• 如果数据页还没有在内存中，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中

• 下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行第 2 步缓存在 change buffer 中与这个页有关的操作

  • 这个过程称为 merge
  • 除了访问数据页会触发 merge，系统有后台线程会定期 merge
  • 在数据库正常关闭的过程中，也会执行 merge 操作

• change buffer 是可以被持久化的数据

  • 在内存中有拷贝，也会被写到磁盘上

• 减少读磁盘的次数，语句的执行速度得到明显提升

• 数据读入内存需要占用 buffer pool，所以 change buffer 这种方式还能够避免占用内存，提高内存利用率

• 什么条件下可以使用 change buffer？

  • 只有普通索引可以使用
  • 唯一索引的更新不能使用
    • 所有的更新操作都要先判断是否违反唯一性约，必须要将数据页读入内存才能判断

• change buffer 用的是 buffer pool 里的内存，因此不能无限增大

  • 通过参数 innodb_change_buffer_max_size 动态设置，如 50 表示只能占用 buffer pool 的 50%

• 更新数据的处理流程

  • 更新的目标页在内存中
    • 唯一索引：找到要插入的位置，判断没有冲突，插入
    • 普通索引：找到要插入的位置，插入
  • 不在内存
    • 唯一索引：读入内存，判断没有冲突，插入
    • 普通索引：将更新记录在 change buffer，完成

• 使用场景

  • 写多读少的业务：效果最好，如账单类、日志类的系统
    • 视情况可以尽量开大
  • 写完马上做查询的业务：不适用，起反作用

• change buffer 和 redo log

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

Merge 的执行流程

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从 change buffer 里找出这个数据页的 change buffer 记录（可能有多个），依次应用，得到新版数据页；
3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。 a. 此时 merge 过程结束，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据的操作属于另外一个过程。 b. 因为 redo log 也记录了 change buffer 的操作，所以崩溃恢复的时候能找回。

评论区

• 系统表空间和数据表空间两个概念
  • 系统表空间用来放系统信息，比如数据字典，对应的磁盘文件是 ibdata1
  • 数据表空间是一个个的表数据文件，对应的磁盘文件就是 表名.ibd
• change buffer 相当于推迟了更新操作，那对并发控制是否有影响，比如加锁？我一直以为加锁需要把具体的数据页读到内存中来，才能加锁，然而并不是？
  • 锁是一个单独的数据结构，如果数据页上有锁，change buffer 在判断“是否能用”的时候，就会认为否
• 在 change buffer 中有此行记录的情况下，再次更改，增加一条记录
• merge 行为之后应该不会再产生 redo log 了吧？
  • 分成两步考虑
    1. merge 是从磁盘读数据页到内存，然后应用，这一步是更新内存，同时写 redolog
    2. 内存变成脏页，跟磁盘数据不一样。之后就走刷脏页的流程
• change buffer 跟普通数据页一样存在磁盘里，区别在于 change buffer 是在共享表空间 ibdata1 里。
• redo log 有两种，一种记录普通数据页的改动，一种记录 change buffer 的改动。
• 对数据的修改记录在 change buffer 里的时候，内存里没有这个物理页，不存在脏页。
• 真正对磁盘数据页的修改是通过内存里脏页的数据刷回磁盘来完成的，而不是根据 redo log。
• change buffer 和数据页一样，也是物理页的一个组成部分，数据结构也是一颗 B+ 树，这棵 B+ 树放在共享表空间，默认在 ibdata1 中。
• change buffer 的写盘策略跟数据一样，内存放不下会触发落盘，还有 checkpoint 推进的时候也可能会触发。