【MySQL学习笔记】普通索引和唯一索引

普通索引和唯一索引

查询过程比较

   从B+树的树根开始，按层搜索到叶子节点。

  对于普通索引，查到满足条件的第一个记录后，继续查找下一个记录，找到碰到第一个不满足条件的记录为止。

   对于唯一索引，由于索引定义了唯一性，查找到第一个记录，就会停止搜索。

   这二者的性能差距有多大？

   Innodb的数据是按数据页为单位来读取的，大小默认为16KB。

   由于引擎是按页读取的，因此，找到第一条记录时，其他相关的记录，就只是指针寻找的工作了。当然，如果记录刚好在一页的末尾，则需要读取下一页数据了。

所以，从查询性能上来说，二者差距不是很大。

  

更新过程比较

change buffer的背景知识

   在需要更新数据页时，如果数据页就在内存中，则直接更新，如果数据页不在内存中，则在不影响数据一致性的前提下，Innodb会将这些更新操作缓存在change buffer中，这样，就不需要从磁盘读取数据页了，下次某查询需要访问该数据页时，将数据页读入内存，则会执行change buffer中与该页相关的操作，该过程称为merge。

   change buffer实质上也是可持久化数据，在内存中有拷贝，也会被写入到磁盘上。发生merge过程的，除了访问这个数据页时触发merge外，系统有后台线程也会定期merge，数据库正常关闭时，也会merge。

   这样，将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显提升。且，数据读入内存是需要占用buffer pool的，所以，change buffer还能避免占用内存，提高内存利用率。

  

change buffer的使用

   对于唯一索引，每次插入时，都必须要求数据页在内存中，从而判断唯一性约束，这样，数据页已经在内存中了，则就没有必要使用change buffer了。所以，change buffer适用于普通索引。

  对于一条插入语句的操作：

  当数据页在内存中时：

    对于唯一索引，找好位置，判断有没有冲突，插入数据，完成操作；

    对于普通索引，找好位置，直接插入，完成操作；

  上 述操作过程，二者性能没太大变化；

 

当数据页不在内存中时：

    对于唯一索引，读入数据页，找好位置，判断有没有冲突，插入数据，完成操作；

    对于普通索引，写入change buffer，完成操作；

   上述操作过程，二者区别就有了，一个需要读数据页，一个不用。

 

   将数据从磁盘读入内存涉及随机IO的访问，是数据库里成本最高的操作之一，change bufer减少了磁盘的随机访问，因此对更新插入删除操作的性能提升会很明显。

  

使用场景

change buffer是为了减少磁盘的随机读取次数来提高效率，因此仅适用于写多读少的业务场景。

如果写了后，就要读，那如果用了changebuffer，反而增加IO降低效率：先写change buffer，这需要IO，再读数据页到内存，也需要IO，然后还要merge，效率还不如不用change buffer。

 

唯一索引和普通索引的选择

这两类索引在查询能力上没太大差别，主要考虑的是对更新性能的影响，因此建议尽量选择普通索引。

    如果所有的更新后，都伴随着查询，那就需要关闭change buffer，其他情况下，changebuffer则都能提升性能。

【注】在实际应用中，普通索引和change buffer的配合使用，对于数据量大的表的更新性能优化是很有明显。

   

change buffer和redo log

WAL提升性能的核心机制，也是尽量减少随机读写。以一个插入语句来看看change buffer 和 redolog的工作相关性。

 

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

假设k1所在的数据页在内存中（Innodb buffer pool),k2所在的数据页不在内存中，

操作流程：

  1. 更新k1的数据页；

  2. 更新change buffer对k2的操作；

  3. 写redolog，将k1对数据页的操作，k2对change buffer的操作写入redolog；

  4. 事务完成

可见，该语句操作成本很低，写了两处内存，写了一处磁盘。

 

假设更新语句完成后，就进行查询操作：

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

对于k1，由于数据页在内存中，直接读内存即可；

对于k2，需要将数据页读入内存，与change buffer进行merge，然后完成查询；

从上可见，redo log主要节省随机写磁盘的消耗，change buffer则节省随机读磁盘的IO消耗。