TXSQL Parallel DDL功能建设

TXSQL Parallel DDL

功能建设

DDL（Data Definition Language）是用来修改数据库和表结构的一类操作，是数据库所有操作中最高危也是最重要的一类操作，常见的DDL操作包括：加减列、修改列类型、加减索引等。由于DDL操作涉及到数据库表结构、表数据的重构，尤其是在云数据库场景下，表的数据量急速上涨，DDL操作的效率受到了极大的挑战，一条慢速的DDL操作甚至需要花费几天的时间来完成，在这期间DDL操作持续持有锁，意味着业务可能会面临长时间等待锁的情况，几天的等待对于业务来说是灾难性的。因此，DDL操作执行的效率成为业务检验一个数据库产品是否成熟的重要标志。

TXSQL 是腾讯云深度定制、基于官方 MySQL 版本进行二次开发的的 MySQL 分支，其中 TXSQL 是 Tencent-MySQL 的缩写。TXSQL 作为 TDSQL-C & CDB 产品的内核，除了 MySQL 社区版的功能外，还提供审计、KMS加密、线程池等诸多企业级特性。TXSQL 对内支持集团内部业务的发展，如：QQ 空间、微信红包、腾讯广告等众多内部业务都使用了 TXSQL 内核；对外为客户提供稳定、高可用的关系数据库服务，如拼多多、哔哩哔哩、微盟等都在云上使用TXSQL 作为其关系型数据库服务的核心。

为了解决用户使用DDL操作效率低下的问题，TXSQL做了一系列DDL深度优化，本文将详细介绍其中的Parallel DDL工作。

01

背景

由于TXSQL Parallel DDL对不同的DDL操作的优化原理不同，因此，我们首先对MySQL中DDL操作做一个概览。

按照MySQL实现不同DDL操作使用的算法，DDL操作可以分为下列三种：

Inplace DDL、Copy DDL和Instant DDL。

我们耳熟能详的Online DDL是根据DDL是否阻塞DML来区分的，在DDL操作执行期间不阻塞DML操作的，则认为该DDL操作是Online的。

1.1  Copy DDL

使用方式

mysql> ALTER TABLE xxx, ALGORITHM = COPY;

何为Copy DDL?

官方文档中给出的解释如下：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

COPY: Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. Concurrent DML is not permitted.# COPY：对原表的一个副本进行操作，将表数据从原表逐行复制到新表中。# 不允许并发DML。

Copy DDL是最浅显易懂的DDL执行方式，试想如果我们需要对某张表新增一个索引，那么只需要

• 新建跟原表schema一致的临时表，并在该临时表上新增一个索引
• 锁原表，不允许DML，允许查询
• 从原表中一条一条读取数据行，并插入到临时表中（这个过程是没有排序的
• 迭代完所有的行之后将原表删除，并将临时表rename成原表即可。

Copy DDL站在存储引擎的上层--执行层，调用引擎层的接口仅仅包括查询+插入，并不涉及到文件上的直接操作。任何DDL操作都可以抽象成这样的执行方式，因此所有的DDL操作都可以使用Copy算法执行。

它的问题在于一条一条数据读取与插入造成IO次数较高，执行调用的路径较长，SQL执行的时间很长，效率较低。并且由于执行整个期间会锁表，不允许并发DML，对业务影响也较大。

1.2 Inplace DDL

使用方式

mysql> ALTER TABLE xxx, ALGORITHM = INPLACE;

何为Inplace DDL?

官方文档中给出的解释如下：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

INPLACE: Operations avoid copying table data but may rebuild the table in place.An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation.Typically, concurrent DML is supported.# Inplace: 操作避免复制表数据，但可能会就地重建表。# 在操作的准备和执行阶段可能会短暂地对表进行独占元数据锁定。# 通常，支持并发DML。

对于某些DDL操作，Inplace DDL能够直接在存储引擎层完成，因此执行路径比Copy DDL短，并且能够减少IO，再次以创建索引为例，Inplace DDL的执行流程如下：

在存储引擎层：

• 新建frm临时文件
• 读取原表数据文件（是按照聚集索引排序的），按照需要的索引列排序，排序后插入到新的索引页中
• 进行rename操作，替换frm文件，完成DDL过程

Inplace DDL跟Copy DDL的本质区别是，Copy DDL是在执行层的操作，Inplace DDL是在存储引擎层对数据文件的直接操作。只需要在原来的ibd文件上，新建所需要的索引页，对数据进行批量读取、排序与插入，能够极大的减少IO、change buffer操作，减少DDL执行所需的时间。

Inplace DDL的效率较高，但是并不能支持所有的DDL操作，例如，修改列数据类型、修改表字符集等操作，这些只能使用Copy算法执行。

为什么这些操作只能使用Copy算法执行？

这些DDL操作需要对原来的数据行做类型转换、编码转换（类似于SQL中的cast、convert功能函数），这些转换接口在执行层有一套完整的接口实现，而在存储引擎层则没有。因此，需要做转换的DDL操作只能在执行层执行。

// 拷贝string类型static void do_field_string(Copy_field *, const Field *from_field,                            Field *to_field) {  StringBuffer<MAX_FIELD_WIDTH> res(from_field->charset());  res.length(0U);
  from_field->val_str(&res);  to_field->store(res.ptr(), res.length(), res.charset());}
// 拷贝decimal类型static void do_field_decimal(Copy_field *, const Field *from_field,                             Field *to_field) {  my_decimal value;  to_field->store_decimal(from_field->val_decimal(&value));}
// decimal转stringtype_conversion_status Field_str::store_decimal(const my_decimal *d) {  ASSERT_COLUMN_MARKED_FOR_WRITE;  double val;  /* TODO: use decimal2string? */  int err = my_decimal2double(E_DEC_FATAL_ERROR & ~E_DEC_OVERFLOW, d, &val);  warn_if_overflow(err);  const type_conversion_status res = store(val);
  return (err != E_DEC_OK) ? decimal_err_to_type_conv_status(err) : res;}

Online DDL

在MySQL 5.6之前，Inplace DDL跟Copy DDL一样，是不允许并发DML的，在整个操作过程中需要锁原表。

MySQL 5.6版本实现了部分Inplace DDL不阻塞DML操作，真正意义上实现了Online DDL。

DDL操作阻塞DML操作的本质原因是由于DDL在表级别上的加的元数据锁(Metadata Lock, MDL)阻塞了DML请求的MDL锁。

Online DDL的原理是将整个执行过程分成三个阶段：prepare、execute、commit，仅在需要修改表的元数据信息的prepare和commit阶段加MDL排他锁，而在执行时间最长的execute阶段，将MDL排他锁降级，以保证并发的DML语句能够执行。

在execute阶段，会将并发DML的增量操作缓存下来，然后在commit阶段，将降级的MDL锁再次升级，并且重做缓存的增量日志。

当前不支持Online的DDL操作仅有以下几种：

• 新增全文索引
• 新增空间索引
• 删除主键 （只支持Copy算法）
• 修改列数据类型（只支持Copy算法）
• 指定表字符集
• 修改表字符集（只支持Copy算法）

从以上的分析中可以得出结论：Copy DDL一定不是Online DDL， Inplace DDL不一定是Online DDL。

1.3 Instant DDL

使用方式

mysql> ALTER TABLE xxx, ALGORITHM = INSTANT;

何为Instant DDL?

官方文档中给出的解释如下：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

INSTANT: Operations only modify metadata in the data dictionary.An exclusive metadata lock on the table may be taken briefly during the execution phase of the operation.Table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)# INSTANT: 操作只会修改元数据信息。在操作执行期间可能会短暂地持有排他的元数据锁。表数据不会被影响，因此操作可以瞬间完成（MySQL 8.0.12引入该功能）

Instant DDL，顾名思义，就是能瞬间完成的DDL操作，只需修改数据字典中的元数据，无需拷贝数据也无需重建表，原表数据也不受影响。

跟Inplace Online DDL的流程相比，Instant DDL在第二阶段execute阶段可以直接跳过。

业界目前支持的Instant DDL操作总结下来有以下几种：

• Change index option 
• Rename table (in ALTER way) 
• SET/DROP DEFAULT COLUMN 
• Add/drop virtual columns 
• Add columns(non-generated)

官方8.0.28和8.0.29在此基础上加强了Instant add column，可支持新加的列到任意位置，并且额外支持了drop column和rename column操作。

这些操作能够使用Instant算法执行的本质原因是，它们通过对元数据的修改就可以完成，如果表数据有影响（例如加减列操作对表数据是有影响的），也不需要重建整个数据，而是在读取数据时根据元数据信息进行相应的补充和删减即可。

TXSQL Instant DDL支持情况

目前TXSQL已经支持的Instant DDL操作包括，Instant add column及Instant modify column。

• Instant add column in TXSQL于2020年上线（5.7, 8.0版本均已支持），该功能使得对表级加列只修改数据字典信息，不需要进行数据的重构与拷贝，同时，优化了清理AHI的算法，避免高并发下的性能抖动问题。
• Instant modify column in TXSQL 2021年上线（5.7, 8.0版本均已支持），该功能使得修改表级的某个列数据类型只修改数据字典信息，避免数据拷贝，TXSQL的该功能是业界首创。（由于篇幅限制，本文不重点展开这部分工作，详细实现可期待后续公众号文章）

修改列类型是用户执行比较频繁的DDL之一。但，Instant modify column in TXSQL也有一些限制，例如，目前仅支持以下类型的转换：char和varchar之间，binary和varbinary之间,  以及 tinyint/smallint/mediumint/int/bigint之间互转。

其他类型之间的转换，例如char和int之间，仍然需要重构表数据，不能够通过Instant算法执行，需要通过Copy算法来执行。

txsql> show create table sbtest1;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table   | Create Table                                                                                                                                                                                                                                                                                           |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| sbtest1 | CREATE TABLE `sbtest1` (  `id` int NOT NULL AUTO_INCREMENT,  `k` int NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

# int列改成bigint列，支持instant算法 txsql> alter table sbtest1 modify column k bigint not null, algorithm=instant;Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0

# bigint列改成char(10)列，不支持instant算法 txsql> alter table sbtest1 modify column k char(10) not null, algorithm=instant;ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

# bigint列改成char(10)列，不支持inplace算法 txsql> alter table sbtest1 modify column k char(10) not null, algorithm=inplace;ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

# bigint列改成char(10)列，只支持copy算法 txsql> alter table sbtest1 modify column k char(10) not null, algorithm=copy;Query OK, 1000 rows affected (0.15 sec)Records: 1000  Duplicates: 0  Warnings: 0

1.4 总结

尽管Instant DDL的效果十分诱人，它一方面能够使得DDL语句瞬间完成，另一方面，又能够有效减少DDL操作阻塞DML语句的时间，但是由于其实现的复杂度以及操作本身的特征带来的限制，目前能够支持的DDL操作类型较少，

大多数DDL操作仍然需要通过Inplace算法甚至Copy算法来执行，因此，加速Inplace DDL和Copy DDL的执行效率依然是十分必要和紧迫的。

TXSQL分别为这两种算法的DDL操作实现了并行化，这样一来，用户使用Instant DDL和Parallel DDL就能够使得绝大多数DDL操作的效率得到优化，大大缩短了用户DDL操作的执行时间。

02

 业界Parallel DDL支持情况

2.1 MySQL生态

2.1.1 官方MySQL

社区版MySQL在8.0.27版本发布了Parallel DDL功能来加速索引创建的流程，具体执行步骤可以概括如下：

• 扫描阶段，n线程扫描Btree，并行生成n个文件；
• 排序阶段，n线程分别对n个文件进行排序，此时的结果是n个文件内是有序的，但是n个文件间是无序的；
• 最后使用单线程多路归并创建btree实现全局有序。

可以看到在前两个阶段，该方案是能够做到完全并行的，但是为了实现全局有序，最后使用单线程多路归并去将n个文件间无序的文件合并成一个全局有序的状态。

在一个多阶段并行的任务中，最终的性能受限于其中的串行部分，因此该方案的扩展性并不理想（我们在3.3对比了官方的性能数据）。

2.1.2 其他产品

PolarDB-并行DDL

PolarDB在8.0.1.1.10版本新增支持并行DDL的功能。目前并行DDL加速仅支持创建二级索引（不包括聚簇索引、全文索引、空间索引和虚拟列上的二级索引）的DDL操作。

华为GaussDB for MySQL-并行创建索引

华为云GaussDB(for MySQL)使用的并行创建索引，是一个全链路的并行技术。

尤其对数据的归并排序做了多种优化，使得常规的归并排序能够充分的并行，充分利用CPU、内存和IO的资源。在并行创建索引之后的合并步骤，也使用了一套简化的算法，正确处理各种索引结构的场景。

GaussDB(for MySQL)的并行创建索引功能，目前支持的索引为Btree二级索引。

特别要注意的是，主键索引的创建和COPY算法的DDL操作目前也是不支持并行的，而对于INPLACE算法，如果创建索引用的是非rebuild的方式，都可以受益于该优化；一旦需要使用rebuild的方式创建索引，因为涉及到主键索引的建立，将无法使用并行创建索引的算法。

2.2 非MySQL生态

非MySQL生态的并行DDL主要以分布式数据库为例，这里介绍了TiDB和Oceanbase的方案。

TiDB

TiDB早期创建索引的流程分为两个部分：首先扫描全表数据，之后根据扫描的数据构造索引 KV 对，按照 tidb_ddl_reorg_batch_size 设置的大小分批次以事务方式提交索引记录到 TiKV。该方案引入了事务的开销和锁的开销。

TiDB对该方案做了改进，将事务批量写入模式改进为文件批量导入的模式。

首先仍是扫描全表数据，之后根据扫描的数据构造索引 KV 对并存入 TiDB 的本地存储，在 TiDB 对于 KV 进行排序后，最终以 Ingest 方式将索引记录写入 TiKV。

新方案消除了两阶段事务的提交时间开销以及索引回填事务与用户事务提交冲突回滚的开销。

Oceanbase

Oceanbase使用分布式排序和旁路导入的方式将原表的数据迁移到新表，基于并行执行框架设计了DDL的分布式执行计划，计划分为两部分，第一部分是采样和扫描算子，第二部分是排序和扫描算子。

得益于Oceanbase的并行框架，DDL操作可以充分利用分布式和单机并行能力。

一方面，并行子计划可以运行在多台机器上同时调度起来。另一方面，在每个分区中，数据可以被拆成多份由不同的sort算子进行处理，这样可以使得每个分区的数据划分相对均衡，避免分区数据倾斜造成的并行程度低的问题。

03

TXSQL Parallel Inplace DDL

3.1

简介

Parallel Inplace DDL in TXSQL 已在2022年上线（5.7, 8.0版本均已支持），目前采用三阶段全并行的方法来加快创建索引的速度，并且在此基础上做了一系列优化，性能领先业界及官方Parallel Create Index。

受益的DDL语句不仅仅只有创建索引，还支持部分表级、列级等需要rebuild table的DDL操作，这些DDL操作的重建数据阶段也可以受益于该优化。

3.2

设计方案

3.2.1 主要思想

Inplace DDL操作中最耗时的部分在于读取原表数据，排序并创建索引的流程。

创建索引主要包括以下三个阶段：

1. 扫描主键索引生成文件.
2. 将扫描后产生的文件按照新索引进行外部排序.
3. 对排序好的文件构建B+tree.

原本的MySQL 8.0.26及之前的版本中这三个阶段都是单线程执行的，Parallel Inplace DDL旨在加速这三个阶段的执行时间。

设扫描并发度为scan_parallel_num，排序和建btree的并发度为parallel_ddl_threads。

两个阶段采用的并行化方案主要思想是：

• 为了提高外部排序的并行度，将数据按照分位点先分区，分成parallel_ddl_threads个区，然后每个分区内的数据分别用一个线程进行merge sort，这样就会产生parallel_ddl_threads个有序的临时文件，并且这些文件从1号文件到parallel_ddl_threads号文件是全局有序的。
• 构建btree阶段，parallel_ddl_threads个线程分别负责排序阶段产生的parallel_ddl_threads个临时文件，为它们分别构建子btree，由于这些文件是全局有序的，这样将所有的子btree再进行合并成一个大的btree就是我们想要的最终的结果。

3.2.2 并行扫描及构建分位点

并行扫描阶段主要有两个任务，一是为每个待创建的索引扫描主键记录，生成<二级索引，主键索引>的数据文件。二是为第二阶段做采样工作以生成分位点。 设共需要构建n个索引，那么我们需要为这n个索引分别采样数据，后续再为它们构建分位点。 因此我们为每一个索引都分配一个`Quantiler`对象。 每一个`Quantiler`对象需要有scan_parallel_num个采样器`ThreadSampler`对象，这就意味着每一个扫描线程针对每一个索引有一个局部的`ThreadSampler`对象用于数据采样，该对象的主要成员为`std::vector<mtuple_t> sample_mtuple_vector`，用于保存采样的数据。

边扫描边采样的示意图如下所示：

我们得到了每个索引的采样数据，接下来可以为采样数据构建分位点了。

为某一个index构建分位点的步骤如下所示：

1. 先将所有采样数据汇总到total_sample_vector。
2. 将total_sample_vector中的数据按照待构建索引的顺序排序。
3. 按照比例找到sort_parallel - 1个分位点，保存在quantiles中。

3.2.3 数据分区及外部排序

3.2.2为每个索引生成了分位点数据，通过这些分位点（quantiles），我们并行处理并行扫描生成的数据文件，并将数据根据quantiles分到parallel_ddl_threads个分区中去。

为某一个index进行数据分区的主要函数是parallel_partition_file()。parallel_ddl_threads个线程并行调用该函数，将并行扫描生成的一个数据文件分区分成parallel_ddl_threads个临时文件。

对一个扫描产生的文件按照分位点进行分区的示意图如下所示：

注意到当两个线程的partition buffer需要写同一个文件时可能出现冲突，此时只需要分配好对应的文件区间即可并行写文件。

该过程完成后，得到一组partition过的临时文件，File 1的所有数据小于第一个分位点的值，File 2的所有数据的值位于第一个分位点和第二个分位点之间，以此类推。

此时可以进入并行外排阶段。

外部排序阶段比较简单，parallel_ddl_threads个线程完全独立并行地对数据分区阶段产生的parallel_ddl_threads个临时文件进行排序即可。

3.2.4 构建btree阶段

3.2.3阶段生成了parallel_ddl_threads个有序的临时文件，该阶段对这些临时文件并行构建子树，并最终形成一颗完整的btree。 每个线程负责一个临时文件，构建一个子树。

接着需要合并parallel_ddl_threads颗子树，合并的流程如下：

首先，将parallel_ddl_threads个子树的各层水平串起来。

如果各个子树高不一样，则按最高的子树，将矮子树补齐树高。

其次，生成一个新的树根。

最后，对子树树根这一层尝试压缩树高。最终得到的btree就是我们想要的索引树。

3.3

性能

测试环境

sysbench表结构，5亿条记录 114G数据。

测试语句

alter table sbtest1 add index k_1(k);

以下是对比了5亿条数据和15亿条记录的加速比测试结果。最高加速比可达到24倍。

与官方Parallel DDL对比

04

TXSQL Parallel Copy DDL

4.1

简介

尽管Instant DDL和Parallel Inplace DDL已经使得很多DDL操作的性能得到极大的提升，但是并不能覆盖所有的DDL操作。例如常见的修改列类型的操作，如果修改的列类型是char和int之间，需要重构表数据，不能够通过Instant算法执行，需要通过Copy算法来执行。

如前文所述，Copy DDL是从Server层执行的，因此，几乎所有DDL操作都可以指定Copy算法执行。因此，优化Copy DDL是十分必要的。

4.2

设计方案

Copy DDL操作的原理简单来说就是：

1）create DDL操作后的表结构；

2）查询原表数据；

3）插入数据到新表；

4）重命名新表

代码执行路径如下：

mysql_alter_table  if (use_inplace) {      mysql_inplace_alter_table() //如果能使用inplace alter table，走inplace方式  }  // 否则走copy方式  ha_create_table();  lock_tables();  copy_data_between_tables();  wait_while_table_is_used();  mysql_rename_table();

Copy DDL执行过程中关键的函数是copy_data_between_tables，该函数会在Server层构建一个TableScanIterator，逐行从Innodb层的原表中读上来数据并且调用ha_write_rows插入到新建的临时表中去。这个函数比较耗时，尤其在表数据量比较庞大的时候。

我们将copy_data_between_tables函数下推到innodb层执行，innodb层使用Parallel Reader框架，并行读取原表的数据，并插入到新建的临时表中去。

该方案需要在InnoDB层做数据行格式的转换，如果是新增列，需要将所有行对应的位置添加default值，如果是修改列，需要将对应列的数据转换成修改后的类型的值格式。

当涉及到不同类型的转换（例如char(10)转int，int转decimal）时，目前InnoDB层并没有实现相关的功能，这里我们利用原本Server层提供的接口，在InnoDB层构建源表和目标表之间的Copy_field，调用invoke_do_copy实现不同类型之间的转换，如果转换出错，返回对应的error code。

下图展示了Parallel Copy DDL的具体执行流程：

1. 数据按照btree切分范围

2. 每个线程扫描一段范围内的数据

3. 并行调用Server层提供的转换拷贝接口转换数据格式

4. 并行调用InnoDB层的Insert接口插入数据

4.3

性能

使用sysbench测试，sbtest表，5亿条数据

测试语句，修改列类型，从int类型修改为char(10)类型：

alter table sbtest1 change column k k char(10) not null, algorithm=copy;

原本需要一个半小时完成的Copy DDL操作，现在最快只需要十几分钟就可以完成。

05

总结

TXSQL Parallel DDL功能从并行化的角度分别优化了Inplace DDL操作和Copy DDL操作，使得DDL操作在创建索引、重建表等方面的优化的执行速度和通用性得到提升，几乎覆盖了所有常见的DDL操作。

未来，TXSQL将继续关注业务使用DDL操作存在的痛点问题，持续完善MySQL DDL生态。

-END-