AliSQL DDL最佳实践_云数据库 RDS(RDS)-阿里云帮助中心

DDL是MySQL中最常见的变更动作，MySQL发展至今，DDL能力也在不断增强。本文主要介绍MySQL DDL能力的发展过程，常用的DDL操作说明，并给出AliSQL中DDL操作的优化和最佳实践。

DDL能力发展

MySQL 5.5及之前：仅支持COPY算法。
- 执行过程：先创建临时表，然后按行拷贝数据，最后表交换。
- 限制：DDL执行期间表只读，无法写入数据。
MySQL 5.6 & 5.7：支持INPLACE算法。
- 执行过程：引擎层处理DDL，无需临时表。
- 优势：DDL执行期间支持读写数据表。
- 限制：在DDL开始和结束时，可能会短暂锁表（不可读写），且会消耗临时空间。
MySQL 8.0：支持INSTANT算法。
- 执行过程：仅修改元数据，无需数据拷贝。
- 优势：秒级完成，显著缓解DDL对业务的影响。
- 限制：支持的DDL类型有限（加列、删列），在DDL开始和结束时，仍需要短暂持有表上的MDL X锁（不可读写）。

说明

所有算法均需在DDL的开始和结束阶段（上图阶段1和阶段3）获取MDL X锁，导致短暂锁表（不可读写），AliSQL对MDL锁等待进行了优化，详见AliSQL DDL相关优化功能。

MySQL常用DDL操作说明

MySQL支持在执行DDL操作时指定算法，若不指定，MySQL默认根据DDL操作类型选择最佳算法。以下为常见操作的执行逻辑及影响（默认InnoDB引擎）。

表操作

DDL操作	DDL命令	默认算法及是否支持修改	DDL执行期间读写属性	可能影响说明
重命名表	RENAME	支持修改算法，默认值： 5.6、5.7：INPLACE 8.0：INSTANT	读写	仅修改元数据，无影响。
重建表	OPTIMIZE / ALTER ENGINE	不支持修改算法，5.6、5.7和8.0默认值：INPLACE。	读写说明 AliSQL 8.0数据归档期间表只读不可写。	重建表会进行数据拷贝，需预留磁盘空间。大表耗时长。可能导致备库和只读实例复制延迟。
更新统计信息	ANALYZE	不支持修改算法。	读写	仅更新统计信息，但某些场景可能导致业务阻塞，详见特殊情况说明页签里的ANALYZE TABLE的阻塞场景。
修改字符集	CONVERT CHARSET	5.6、5.7不支持修改算法，默认值COPY。 8.0：支持修改算法，默认值COPY，仅特殊场景支持INPLACE，详见特殊情况说明页签里的修改字符集（CONVERT CHARSET）时使用INPLACE算法。	只读，无法进行写操作。	会拷贝数据到临时表，需预留磁盘空间。大表耗时长。可能导致备库和只读实例复制延迟。
修改表备注	ALTER COMMENT	不支持修改算法，5.6、5.7和8.0默认值：INPLACE。	读写	仅修改元数据，无影响。

列操作

DDL操作	DDL命令	默认算法及是否支持修改	DDL执行期间读写属性	可能影响说明
重命名列	RENAME	支持修改算法，默认值： 5.6、5.7：INPLACE 8.0：INSTANT	读写	仅修改元数据，无影响。
增加列	ADD	支持修改算法，默认值： 5.6、5.7：INPLACE 8.0：INSTANT	读写	5.6和5.7：会重建表，需预留磁盘空间。 8.0：仅修改元数据，无影响。
删除列	DROP	支持修改算法，默认值： 5.6、5.7：INPLACE 8.0：INSTANT	读写	5.6和5.7：会重建表，需预留磁盘空间。 8.0：仅修改元数据，无影响。
修改列类型	CHANGE / MODIFY	支持修改算法，5.6、5.7和8.0默认值：COPY，VARCHAR特殊场景支持INPLACE，详见修改VARCHAR类型字段长度时使用INPLACE算法。	只读，无法进行写操作。	会拷贝数据到临时表，需预留磁盘空间。大表耗时长。可能导致备库和只读实例复制延迟。
修改列字符集	ALTER CHARSET	5.6、5.7不支持修改算法，默认值COPY。 8.0：支持修改算法，默认值COPY，仅特殊场景支持INPLACE，详见修改字符集（CONVERT CHARSET）时使用INPLACE算法）。	只读，无法进行写操作。	会拷贝数据到临时表，需预留磁盘空间。大表耗时长。可能导致备库和只读实例复制延迟。
修改列备注	ALTER COMMENT	支持修改算法，默认值： 5.6、5.7：INPLACE 8.0：INSTANT	读写	仅修改元数据，无影响。

索引操作

DDL操作	DDL命令	默认算法及是否支持修改	DDL执行期间读写属性	可能影响说明
重命名索引	RENAME	支持修改算法，默认值： 5.6、5.7：INPLACE 8.0：INSTANT	读写	仅修改元数据，无影响。
创建索引	ADD / CREATE	支持修改算法，5.6、5.7和8.0默认值：INPLACE。	读写	需构建B+树，并写入数据，索引占用空间。大表耗时长。可能导致备库和只读实例复制延迟。
删除索引	DROP	支持修改算法，5.6、5.7和8.0默认值：INPLACE。	读写	仅修改元数据，无影响。

特殊情况说明

修改VARCHAR类型字段长度时使用INPLACE算法

VARCHAR类型字段长度存在一个临界值，当您需要使用INPLACE算法修改VARCHAR类型字段长度时，必须保证修改前的长度和修改后的长度，均小于等于该临界值，或均大于该临界值。如需跨临界值变更，则需要使用COPY算法。不同字符集对应的临界值（字符数）不同：

字符集	临界值（字符数）	长度变更区间	可选算法
utf8 / utf8mb3	85	修改前后长度均在`(0,85]`	INPLACE、COPY
		修改前后长度均在`(85,65535]`
		跨临界值变更	COPY
utf8mb4	63	修改前后长度均在`(0,63]`	INPLACE、COPY
		修改前后长度均在`(63,65535]`
		跨临界值变更	COPY
latin1	255	修改前后长度均在`(0,255]`	INPLACE、COPY
		修改前后长度均在`(255,65535]`
		跨临界值变更	COPY

临界值说明

InnoDB底层在存储VARCHAR类型数据时，会使用1个或者2个字节来保存数据长度。1个字节能够保存的最大长度是2^8 - 1 = 255，长度超过255后需要用2字节保存，所有字符集的临界值（字节数）均为255。

但是，一般来说，DDL命令中会使用字符数设定字段长度值，如VARCHAR（10）指该字段长度为10个字符。在不同的字符集中，字符与字节的换算比例不同（例如utf8字符集中1字符换算3字节，utf8mb4字符集中1字符换算4字节）。所以，如果字段长度以字符数进行统计，则不同字符集的临界值（字符数）也会不同。

字符集	临界值（字节数）	临界值（字符数）	换算比例
utf8 / utf8mb3	255	85	1字符等于3字节
utf8mb4		63	1字符等于4字节
latin1		255	1字符等于1字节

INPLACE算法不支持通过变更存储空间大小的方式修改字段长度，故修改前后的字段长度值，必须均为1字节存储（小于等于临界值），或均为2字节存储（大于临界值）。

修改字符集（CONVERT CHARSET）时使用INPLACE算法

字符集修改操作会对表中所有带字符集信息的列进行修改，MySQL当前包含字符集信息的列有CHAR、VARCHAR、TEXT、ENUM和SET。当您需要使用INPLACE算法修改字符集时，需要满足以下条件：

MySQL版本为8.0及以上。
如果表中存在CHAR或VARCHAR类型字段时，该列不能为索引（或索引的一部分），且列长度需要小于等于两个临界值（修改前后字符集临界值）中的最小值，大于两个临界值中的最大值。

以utf8 / utf8mb3字符集变更为utf8mb4字符集为例：

utf8 / utf8mb3字符集临界值为85。
utf8mb4字符集临界值为63。

当表内含有CHAR或VARCHAR类型字段且不为索引列时，对应字段长度需要在(0,63]或(85,65535]区间内才能使用INPLACE算法修改字符集。

ANALYZE TABLE的阻塞场景

问题：若表存在未结束的慢SQL，ANALYZE后所有表访问需等待慢SQL结束才能刷新表缓存。
解决方案：MySQL 8.0已修复此问题。

DDL执行时间评估

方式一：基于Performance Schema（PFS）评估

从MySQL 5.7版本开始在 PFS加入了DDL相关Stage统计信息，可以通过processlist和P_S库下的events_stages_current表查看DDL进度，并且评估剩余时间。

适用场景：DDL执行中需实时监控进度。

查询语句：

SELECT * FROM information_schema.processlist WHERE ID = <替换为实际ID>;
SELECT THREAD_ID, EVENT_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 AS PROGRESS  
FROM performance_schema.events_stages_current WHERE THREAD_ID = <替换为实际ID>;

说明

执行SHOW Processlist命令根据执行的SQL获取ID。

示例：本地Sysbench 10 GB的表，查询结果TIME=116，PROGRESS=83.9961%，如下图所示：
- 计算时间 = TIME / PROGRESS = 116 / (83.9961%) = 138.1 s
- 实际执行时间：2 min 3.14 s = 123.14 s

方式二：基于表空间大小评估

适用场景：无需开启PFS（消耗内存且会有性能影响，RDS MySQL默认关闭）的估算。
步骤：
1. 查询表大小：
```
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA =  <替换为实际的数据库名> AND TABLE_NAME = <替换为实际的表名>;  
```
2. 根据查询到的DATA_LENGTH大小，直接估算DDL的时间，以OPTIMIZE TABLE操作为例：
  - 云盘实例（高性能云盘和ESSD云盘）：按照30~60 MB/s的速度进行估算。
  - 高性能本地盘实例：按照50~100 MB/s的速度进行估算。
    说明
    30~60 MB/s和50~100 MB/s的速度是根据实例的IO能力估算的值，实际可能会有偏差。
3. （可选）索引影响：如果表存在很多二级索引，还需考虑二级索引排序和构建的时间，按主表速度的1/3估算索引构建时间。
示例：本地Sysbench 10 GB的表，查询结果如下：
- 计算时间 = DATA_LENGTH / 1024 / 1024 / 100 + INDEX_LENGTH / 1024 / 1024 / 100 * 3 = 10270785536 / 1024 / 1024 / 100 + 665829376 / 1024 / 1024 / 100 * 3 = 117 s
- 实际执行时间：2 min 3.14 s = 123.14 s