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

09 借索引选择初窥change buffer

创建时间：2021年11月13日11:21:22
编辑时间：2021年11月15日01:41:41

——————————————————————————————

查询过程

问：索引数据页的内部的查询是怎么样的？

答：类似于跳表的形式

问：查询过程中，普通索引和唯一索引的区别？

答：普通索引会判断下一个值是否满足条件，而唯一索引不会，性能差距微乎其微。原因是MySQL读写数据是按数据页进行的，而每个数据页大小是16kb，其中包含了多个索引，所谓判断下一个值是否满足条件，往往也仅仅是多做几次指针寻找和计算而已。

更新过程

问：什么是change buffer？

答：一块用来缓存二级索引更新操作的内存空间。当频繁写，少读时，可以提高更新性能（若每次更新都访问数据库，实际上是磁盘随机访问，而改成缓存更新，当有查询对应数据页sql时，需要读取的数据页被加载进内存，change buffer再将其同步数据更新到内存对应数据页上，这便减少了磁盘的随机访问，提高了写性能）。

占用内存比例查询命令是

show variables like '%innodb_change_buffer_max_size%' 

问：change buffer会丢失吗？

答：不会。change buffer本身的记录会写入磁盘（系统表空间）（后台线程），而redo log也会记录change buffer的变动记录，以提供恢复的可能。

问：一个更新语句是怎么将数据落地到磁盘的？

答：MySQL进行数据更新时，若数据更新涉及到二级索引，且目标数据页不在内存中，则会记录一条变更记录在change buffer中，并记录一条change buffer的变动记录到redo log中，如果目标数据页在内存中，则会记录一条变更记录到redo log中。change buffer会在适合的时机（内存中加载有对应的数据页时），将数据同步到内存，内存中的数据最后同步到磁盘。本质上，redo log的作用是crash safe，例如在change buffer中的数据还没同步，但数据库断电了，此时通过redo log回复数据。

问：为什么change buffer不能适用于更新非二级索引？

答：因为唯一索引，更新时需要判定索引键冲突，便需要加载整颗索引树，此时直接将更新落地到内存对应索引树性能最好，change buffer主要针对庞大的二级索引树，当需要更新时，也只是更新对应索引树的部分数据页而已。

问：什么时候change buffer会merge？

答：访问对应数据页时，此外后台线程会定期merge，数据库正常关闭时也会merge。
merge的时候，redo log也会记录内存中数据页的数据变更和change buffer的变动。

问：更新过程中，普通索引和唯一索引的区别？

答：

如果对应数据页在内存当中，则唯一索引比普通索引多了一个判读环节，性能差别微乎其微；

如果对应数据页不在内存当中，唯一索引场景下，会加载数据页进内存，判断是否冲突，而普通索引则是直接插入到change buffer当中。

由于change buffer减少了随机磁盘访问，所以普通索引的更新场景大部分比唯一索引要性能更高效。

问：change buffer的使用场景是什么？

答：将频繁的二级索引更新记录缓存起来，当对应的数据页被加载进内存时，进行merge。这个环节可以减少随机磁盘访问，大大提高更新性能，但是更新后立即读时，change buffer会成为性能累赘（相当于系统要多维护一个中间商），反之，频繁写的场景下，change buffer记录的数据越多，性价比越高。总而言之，change buffer适合频繁写二级索引数据的场景。

问：索引应该如何选择？

答：综合考虑，查询时唯一索引和普通索引无多大区别，而更新时建议选用普通索引。

问：索引应该如何选择？

答：综合考虑，查询时唯一索引和普通索引无多大区别，而更新时建议选用普通索引。