MySQL--CREATE INDEX在各版本的优化

在MySQL 5.5版本中引入FIC（Fast index creation）特性，提升索引的创建速度。

FCI 操作流程：
（1）对表加共享S锁，允许其他会话读操作，但禁止写操作，
（2）扫描Cluster index的数据来构建新索引
（3）新索引创建完成，解除S锁，允许读写。

FCI 优点：
（1）创建索引不需要拷贝整表数据，创建速度快，
（2）创建索引过程中，可以快速中止。

FCI限制：
（1）FCI特新仅限于复制索引，不试用于聚集索引，
（2）索引创建期间，表只允许读不允许写。

 

在MySQL 5.6.7版本中引入Online DDL特性，允许联机创建索引。

Online create index流程
（1）扫描Cluster Index的数据来构建新索引
（2）使用RowLog来记录构建新索引中的数据变化
（3）锁定表禁止写，重放Rowlog到新索引上
（4）索引创建完成，新索引数据和Cluster Index数据保持一致，释放表锁。

Online DDL优点：
（1）在整个创建索引周期内，大部分时间原表可读写。

在MySQL 5.7.5版本中引入Bulk load for create index特性，进一步提升索引创建的速度，并提升索引“质量”。

操作流程：
（1）扫描Cluster Index数据，将数据放入sort buffer中排序，soft buffer写满后将数据写入临时文件
（2）对临时文件中的有序记录进行归并排序
（3）将排序后的数据写入到索引结构中，自下而上生产索引树。

非bulk load方式的缺点：
（1）排序效率，需要对每行数据在新索引中进行查找定位，确定该行数据的索引位置，定位需要从索引的根节点开始查找到索引的叶子节点。
（2）redo log，需要使用redo log来记录每行数据插入索引所产生的数据变化。
（3）undo log，需要使用undo log来记录每行数据的位置
（4）索引碎片和页拆分，随着数据行的不断插入，很容易导致索引页的拆分和索引树的自平衡，无法保证索引页的填充度。

bulk load方式的优点：
（1）排序效率，数据在sort buffer和临时文件中牌序合并，然后直接生成索引的叶子节点页，无需单独定位每行数据的位置。
（2）redo log，使用checkpoint来持久化生成的索引页，不会产生redo log。
（3）undo log，仅需要使用undo log来记录索引页的分配情况，产生的undo log较少。
（4）可以按照innodb_fill_factor参数来生成索引页，且不会导致页拆分，索引树需要平衡的次数较少。

bulk load方式的缺点：
（1）bulk load使用临时文件来存放sort buffer的结果，需要关注磁盘空间的使用率
（2）bulk load不产生redo log，数据库从write-ahead logging方式退化成direct persist data，导致依赖redo log实现的工具失效，如xtrabackup。
（3）bulk load需要不断地唤醒page cleaner线程来对新索引页进行checkpoint操作。
（4）仅支持普通索引和全文索引，不支持空间索引。

 参考资料：
https://dev.mysql.com/doc/refman/5.7/en/sorted-index-builds.html
https://dev.mysql.com/worklog/task/?id=7277
https://yq.aliyun.com/articles/50758

##========================================================================##