MySQL Index--CREATE INDEX在各版本的优化
FIC(Fast index creation)特性
在MySQL 5.5版本中引入FIC(Fast index creation)特性,创建索引时无需再拷贝整表数据,以提升索引的创建速度。
FCI 操作流程:
(1)对表加共享S锁,允许其他会话读操作,但禁止写操作,
(2)扫描Cluster index的数据来构建新索引
(3)新索引创建完成,解除S锁,允许读写。
FCI 优点:
(1)创建索引不需要拷贝整表数据,创建速度快,
(2)创建索引过程中,可以快速中止。
FCI限制:
(1)FCI特新仅限于复制索引,不试用于聚集索引,
(2)索引创建期间,表只允许读不允许写。
Online DDL特性
在MySQL 5.6.7中引入Online DDL特性,通过Row Log记录DDL操作前期的数据变化并在DDL操作后期进行“重放”,使得整个DLL操作期间仅需要短暂的表锁。
Online create index流程
(1)扫描Cluster Index的数据来构建新索引
(2)使用RowLog来记录构建新索引中的数据变化
(3)锁定表禁止写,重放Rowlog到新索引上
(4)索引创建完成,新索引数据和Cluster Index数据保持一致,释放表锁。
Online DDL优点:
(1)在整个创建索引周期内,大部分时间原表可读写。
Bulk load for create index特性
在MySQL 5.7.5版本中引入Bulk load for create index特性,通过bulk load方式替换原有单行插入方式,大幅提升索引创建速度。
操作流程:
(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