本文分析一下普通索引和唯一索引在查询和更新上的性能差异。

讨论使用这两个的区别的前提是建议在业务上保证了唯一性。

查询过程

select id from T where k = 5

对于普通索引,会在索引树上找到第一个满足的k记录后,继续查找下一个,直到不满足条件为止。

对于唯一索引,由于索引的“唯一性”,直接定位到第一个满足条件的记录后,就停止。

这里的性能差异微乎其微!虽然看着是唯一索引更快一些?

分析一下普通索引,InnoDB读取数据是以页为单位,每个数据页默认大小是16KB。继续往下查找,也只是在数据页中读下一个,只要多一次指针寻址和一次计算判断而已。

再者,正好取的这条数据是该数据页的最后一条,也只是操作上复杂一点,它需要读取下一个数据页。但是从概率上说,这种情况很低,对于一个整型字段,可以放上千个key。因此,计算平均性能差异时,这个操作对现在的CPU可以忽略不计。

更新过程

change buffer

当需要更新一个数据页时,如果数据页在内存,就会直接更新;如果不在内存中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中,这样就不需要从磁盘中读入这些数据页了。等下次查询需要访问这个数据页时,将数据页读入内存,然后执行change buffer中与这个页有关的操作,保证数据逻辑的正确性。

虽然它叫change buffer,但是它是可以持久化的,它在内存中有拷贝,也会存到磁盘中。

另外,我们将change buffer中操作到原数据页,得到新结果的过程叫做merge。除了访问会造成merge,后台有线程定期merge,关闭的过程也会merge。

使用条件

对于唯一性索引,由于插入的时候需要校验唯一性,因此必须将相关记录读入内存才能判断。都读到内存了,就直接更新,没必要使用change buffer了。

因此,只有普通索引可以用。

大小

它使用的是buffer pool的内存,不能无限增大。可以通过参数innodb_change_buffer_max_size动态设置,比如当它设置成50时,就表示最大占用它的50%。

插入过程

比如,要插入新记录k=4。

  • 对于唯一索引,直接找到3和5之间的位置,判断有没有冲突。

    • 记录在内存中
    • 记录不在内存中,读数据页
      • 判断没有冲突,执行,结束。
  • 对于普通索引,找到3和5之间的位置。

    • 记录在内存中,直接插入值
    • 记录不在内存中,更新记录至change buffer,结束。

优势

减少了随机磁盘访问,改为了内存操作,提升性能!

使用场景

普通索引的所有场景,使用change buffer都能起到加速作用吗?

上面说到,真正的数据更新是在merge的时候完成的。而change buffer是将变更动作记录下来,所以在merge之前,如果change buffer记录更多,效果越好。

因此,对于一些写多读少的业务,在页面写完之后被访问的概率小,此时使用change buffer效果最好,比如一些账单类、日志类的系统。

反过来,如果一个业务的更新模式是更新后马上会做查询,既然满足条件,由于需要查询,会访问数据页,立即触发了merge过程,这样IO次数不减少,反而增加了维护change buffer的负担。

change buffer和redo log

待记入,其实是和上面的过程一样的,只不过是举一个例子。

一条更新语句,你会发现它涉及了四个部分:内存、redo log(ib_log_fileX)、 数据表空间(t.ibd)、系统表空间(ibdata1)。

980a2b786f0ea7adabef2e64fb4c4ca3.png

更新完之后,想再读取,则

6dc743577af1dbcbb8550bddbfc5f98e.png

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。