第九章 普通索引和唯一索引

查询过程

普通索引 和 唯一索引 在执行上的区别 ?

• 普通索引的等值查询，会继续遍历到第一个不相等的值才会结束
• 唯一索引的等值查询，命中则结束（性能差距微乎其微）

InnoDB 的数据是按​

​数据页​

​为单位来读写的

• 当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存
• 在 InnoDB 中，每个数据页的大小默认是​

  ​16KB​

  ​
• 读取数据页分为两种情况：

• 要查询的记录不在页尾，则只需要读取当前记录所在的数据页即可
• 要查询的记录在页尾，则在读取当前记录所在数据页的同时还需要读取下一个数据页
• 两者的性能差距也是微乎其微

更新过程

关于 ​

​change buffer​

​

• ​

  ​change buffer​

  ​​ 只针对二级索引树的数据页，并不是​

  ​聚簇索引​

  ​即主键树的数据页
• 如果涉及到索引字段的更新，也是要更新对应的索引数据的
• 如果索引树对应的数据页不在内存中，则​

  ​change buffer​

  ​​ 会先保存这个数据，不需要从磁盘中读入这个数据页，之后会和对应的数据页进行​

  ​merge​

  ​ 过程
• ​

  ​redo log​

  ​ 也是会记录这一动作的，所以更新对应索引树的数据不会丢失

关于 ​

​merge​

​

• 将​

  ​change buffer​

  ​​ 中的操作应用到原数据页，得到最新结果的过程称为​

  ​merge​

  ​
• 除了访问这个数据页会触发 merge 外，系统有后台线程会定期​

  ​merge​

  ​
• 在数据库​

  ​正常关闭（shutdown）​

  ​​的过程中，也会执行​

  ​merge​

  ​ 操作

理解 ​

​change buffer​

​

• MySQL 数据存储在主键索引树的叶子节点中
• 普通索引 和 唯一索引也都有自己的索引树，树的叶子节点存储的是​

  ​主键的值​

  ​
• 做更新操作（插入、更新、删除）会同时更新所有的索引树结构

• insert 过程：主键索引树和唯一建索引树的肯定都要更新，肯定是无法用到​

  ​change buffer​

  ​ 的；但是普通索引树的更新，是可以使用change buffer的
• update 过程：只要涉及到相关字段更新，就要同时更新相应的索引树。道理同上
• 显然，​

  ​insert​

  ​ 操作的影响更大，如果有多个唯一索引，insert 对内存命中率会有极大影响

• 减少读磁盘：仅仅减少的是对二级普通索引页的读磁盘操作，而对于其他类型的页 (​

  ​唯一索引、主键索引​

  ​) 还是要读磁盘的
• 减少内存占用：​

  ​change buffer​

  ​​ 虽然还是需要内存占用 (记录数据更新操作)，但相比于数据页来说 (​

  ​默认16K​

  ​)，所占的内存还是小了很多的

​

​change buffer​

​ 不能无限增大

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

• Buffer Pool 是一片内存空间，受制于内存空间大小。 可以通过设置​

  ​innodb_buffer_pool_size​

  ​​ 来控制​

  ​Buffer Pool​

  ​ 的大小

• change buffer 的大小，可以通过参数​

  ​innodb_change_buffer_max_size​

  ​ 来动态设置

• 这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%

如果要插入一个新纪录，InnoDB 的处理流程是怎样的 ?

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

• 对于唯一索引，找到插入位置，判断冲突，进行插入
• 对于普通索引，找到插入位置，进行插入

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

• 对于唯一索引，需要将数据页读入内存，判断冲突，进行插入
• 对于普通索引，将更新记录在 change buffer，语句执行结束

• 将数据从磁盘读入内存涉及​

  ​随机 IO​

  ​ 的访问，是数据库里面成本最高的操作之一
• change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的

change buffer 的使用场景

普通索引的​

​所有场景​

​​，使用 ​

​change buffer​

​ 都可以起到加速作用吗 ?

• 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好，这种业务模型常见的就是账单类、日志类的系统
• 假设一个业务的更新模式是​

  ​写入之后马上会做查询​

  ​​，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了​

  ​副作用​

  ​

索引选择和实践

• 普通索引与唯一索引，普通索引在更新时速度更快，尽量选普通索引
• 更新之后马上就是查询时，不使用​

  ​change buffer​

  ​
• ​

  ​change buffer​

  ​ 更适合普通索引

change buffer 和 redo log

• 不同之处在于优化了整个变更流程的不同阶段。 先不考虑​

  ​redo log​

  ​​、​

  ​change buffer​

  ​ 机制，简化抽象一个变更(insert、update、delete)流程：

• 步骤一：从磁盘读取待变更的行所在的数据页，读取至内存页中
• 步骤二：对内存页中的行，执行变更操作
• 步骤三：将变更后的数据页，写入到磁盘中

• 步骤一涉及到随机读磁盘 IO
• 步骤三涉及到随机写磁盘 IO
• change buffer 机制：优化了步骤一，节省了随机读磁盘IO
• redo log 机制：优化了步骤三，节省了随机写磁盘 IO，将​

  ​随机写磁盘​

  ​​，优化为了​

  ​顺序写磁盘​

  ​ (写redo log，确保crash-safe)
• 在 InnodB 中，​

  ​change buffer​

  ​​ 机制不是一直会被应用到，仅当待操作的数据页当前​

  ​不在内存中​

  ​​，需要先读磁盘加载数据页时，​

  ​change buffer​

  ​ 才有用武之地
• ​

  ​redo log​

  ​ 机制，为了保证 crash-safe，一直都会用到
• 有没有用到​

  ​change buffer​

  ​​ 机制，对于​

  ​redo log​

  ​ 这步的区别在于：

• 用到了​

  ​change buffer​

  ​​ 机制时，在​

  ​redo log​

  ​​ 中记录的本次变更，是记录​

  ​new change buffer item​

  ​ 相关的信息，而不是直接的记录物理页的变更