MySQL online create index实现原理

发表于   |   分类于

引言

国内较多的互联网公司都是采用MySQL作为数据库系统，随着业务的发展，难免会碰到需要新建索引来优化某些SQL执行性能的情况。在MySQL实现online create index之前，新建索引意味着业务要停止写入，这是非常影响用户使用体验的，为此，MySQL引入了online create index，极大地减少了业务停写的时间，使得新建索引期间业务能够持续正常的工作。本文主要是对其实现原理的总结以及关键步骤的解释说明。

MySQL online create index原理

在MySQL中表格至少需要设置一个主键，如果用户未指定主键的话，内部会自动生成一个。对于带主键的表格，MySQL会以聚集索引的方式实现，即表格的数据都是完整的存储在聚集索引上的。对于主键的变更，相当于对聚集索引进行变更，这个过程目前MySQL还是以停写的方式实现的，本文主要讨论的是新建二级索引的实现，为了方便描述，以一个例子来说明本文要讨论的场景。

1
2
3
4
5
create table t1(
	c1 int primary key,
	c2 int,
	c3 int,
);

刚开始业务中的SQL都是以主键c1来做查询的，后来随着业务的发展，可能出现了以c2做查询的SQL，此时，为了优化此类SQL的执行性能，需要在c2列上构建索引，即

1
create index index_c2 on t1(c2);

MySQL online create index主要分为两个阶段，第一阶段为从主表读取索引列并排序生成索引表的数据，称为基线数据；第二阶段为把新建索引阶段索引表的增量数据更新到第一阶段的基线数据上。具体来看，主要过程如下。

  1. 用户执行create index
  2. 等待当前所有事务执行结束，但不影响新事务的开启；新开启的事务更新时会把新建索引的记录到增量数据，称为Row Log
  3. 开始构建索引，主要是从主表读出数据并排序
  4. 把新建索引表期间产生的增量数据更新到索引表中
  5. 构建的收尾工作

接下来将略过不太重要的步骤1和步骤5，主要描述步骤2-4的详细实现。

等事务结束

在执行create index语句之后，MySQL会先等待之前开启的事务先结束后，再真正开始索引的构建工作，这么做的原因是在执行create index之前开启的事务可能已经执行过某些更新SQL语句，这些SQL语句没有生成新建索引表的增量数据(Row Log)，如果不等待这部分事务结束，可能会出现基线数据中没有此部分数据，且Row Log中也没有此部分数据，最终该部分数据在索引表中不存在。

MySQL的等事务结束是通过MDL(Meta Data Lock)实现的，MDL会按序唤醒锁等待者，这样就能保证create index之前开启的事务一定执行完成了。

实际测试中，可以观察到当create index之前的事务一直没有结束时，create index语句会一直卡在thd->mdl_context.upgrade_shared_lock(sql_table.cc:7381)上。

排序

索引构建的第一阶段的工作是根据主表的数据，来构建索引表的数据。此过程总共有两个步骤，第一是读取主表中所需要的索引列数据；第二是将数据按照索引列排序。

其中读取主表数据和普通的全表扫描区别不大，而将数据按照索引列排序则是一个外部排序的过程。MySQL对外部排序实现较为简单，仅为最普通的单线程两路归并算法，优点是实现简单，占用内存资源少，缺点是性能较差。

更新增量数据到构建好的索引表

一般地，对于数据量较大的表格，构建索引的时间较长，通常是小时级别的，这期间往往会有新事务的提交，其中就可能包含对新建索引表的修改。因此，在索引基线数据构建好之后，还需要把构建期间的增量数据更新到索引表中，那么问题来了，在更新增量数据到索引表中会不断的有新事务修改数据，这样何时才能保证所有的修改都更新到索引表上呢？答案是加锁，粗暴一点的加锁方式是在整个增量数据更新到索引表期间停写，完成之后，再放开写入。但是，因为索引构建时间长，增量数据的数据量一般也较大，如果更新整个增量数据到索引表期间都停写的话，会较大地影响用户使用体验。因此，MySQL对加锁过程做了优化。

首先Row Log会被拆分为多个较小的Block，事务的更新会把数据写入到最后一个Block中，因此，普通的DML更新的时候会对最后一个Block加锁。同样的，在更新每个Block到索引表的时候，会先加锁，如果当前Block不是最后一个Block时，会把锁释放，如果是最后一个Block，则保持加锁状态，直到更新结束。因此，在更新Row Log到索引表期间，加锁的时间比较短，仅在最后一个Block更新到索引表时会持有锁一段时间。

总结

MySQL online create index的整体思路分为两步构建基线以及更新增量，构建基线时采用的归并算法比较简单，资源占用少，但性能会比较差；在更新增量时，采用将增量切分成更小的块，来减少停写的时间，是比较通用的方法。

PS:
本博客更新会在第一时间推送到微信公众号，欢迎大家关注。

qocde_wechat

参考文献