浅谈MySQL–Sorted Index Builds

索引创建概述

在早期的MySQL版本,创建索引是需要复制整表数据的;在5.5版本中,MySQL利用快速创建索引(fast index creation)技术,优化了索引的创建算法,使其执行时不需要再进行复制表数据的操作,同时允许在创建索引时,其他事务并发读;而在5.6以上的版本,在创建索引时允许其他事务并发读写,并将此索引创建方式称为Sorted Index Builds。以下介绍一下5.5前后版本,MySQL在索引创建方式上的优化变迁。

5.5以前创建方式

其实就是表复制的方式。当需要创建索引时,MySQL会根据创建的索引,按照所需的表结构新建一个空表,然后锁定源表,将数据一行一行地插入到空表中,同时更新维护空表上的索引。当数据全部复制完毕,索引也就建成了,最后源表被删除,而新建的表则更名为源表的名字。

这种方式弊端非常多,不但需要复制全表数据,十分耗时,而且在复制数据期间,源表是被锁定的状态,排斥任何读写操作。

5.5以后创建方式

称为Sorted Index Builds

当需要创建索引时,分为三个阶段:

1.扫描源表,即主键聚集索引,将要创建的索引的条目(即索引键和主键),先存放在排序缓冲区(sort buffer)。当排序缓冲区写满后,里面的索引条目将会进行一次排序,并写出到一个临时中间文件;

2.经过多次写满排序缓冲区并写出到临时中间文件,主键聚集索引遍历完后,在临时中间文件中将会对所有索引条目进行一次合并排序;

3.最后排好序的索引条目插入到目标B树索引,索引创建完成。

Sorted Index Builds优势

在引入Sorted Index Builds之前,创建索引的过程是一遍扫描源表,一遍将得到的数据插入到新建的空表中,也就是边扫描边插入B树索引中。这种方式由于索引键的插入是乱序的,所以在插入的过程中需要不断地寻找和确认插入的位置,期间必然伴随着索引页的分裂和合并。

而Sorted Index Builds的方式,由于事先已经将索引条目排序,插入B树索引时,只需要顺序地往索引页里插入数据,再索引页写满时,再向右分配一个索引页即可,整个索引创建过程中,都是在写入最右的叶子节点,无需去寻找确认插入的位置,也不会引发索引页的分裂合并。

redo日志会记录吗

Sorted Index Builds不会写redo记录,而是用一个检查点来将脏页刷到磁盘。而页清理线程会及时地将索引脏页刷到磁盘,以减少执行检查点所花时间和开销。

同时也是由于创建索引不记录redo,所以在xtrabackup备份期间,如果执行了创建索引操作,备份将会终止,因为xtrabackup无法从redo中获取创建索引的操作,在利用备份apply-log时也就无法重现创建索引的操作。

发表评论

相关文章