MySQL索引创建原理：Sorted Index Builds

朱雀 2022-04-08 10:15 173阅读 0赞

在早期的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树索引时，只需要顺序地往索引页里插入数据，再索引页写满时，再向右分配一个索引页即可，整个索引创建过程中，都是在写入最右的叶子节点，无需去寻找确认插入的位置，也不会引发索引页的分裂合并。

四、Sorted Index Builds和redo日志  
Sorted Index Builds不会写redo记录，而是用一个检查点来将脏页刷到磁盘。而页清理线程会及时地将索引脏页刷到磁盘，以减少执行检查点所花时间和开销。  
同时也是由于创建索引不记录redo，所以在xtrabackup备份期间，如果执行了创建索引操作，备份将会终止，因为xtrabackup无法从redo中获取创建索引的操作，在利用备份apply-log时也就无法重现创建索引的操作。