MySQL & MariaDB Online DDL参考指南( 二 ) 概述在早期的MySQL版本中

ALTER TABLE tbl_name [WAIT n|NOWAIT] ... CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ... DROP INDEX ... [WAIT n|NOWAIT] DROP TABLE tbl_name [WAIT n|NOWAIT] ... LOCK TABLE ... [WAIT n|NOWAIT] OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT] RENAME TABLE tbl_name [WAIT n|NOWAIT] ... SELECT ... FOR UPDATE [WAIT n|NOWAIT] SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT] TRUNCATE TABLE tbl_name [WAIT n|NOWAIT]
评估 Online DDL 操作的性能Online DDL 操作的性能取决于是否发生了表的重建。在对大表执行 DDL 操作之前，为了避免影响正常业务操作，最好是先评估一下 DDL 语句的性能再选择如何操作。

复制表结构，创建一个新的表
在新创建的表中插入少量数据
在新表上面执行 DDL 操作
检查执行操作后返回的 rows affected 是否是 0 。如果该值非 0 ，则意味着需要拷贝表数据，此时对 DDL 的上线需要慎重考虑，周密计划

比如

修改某一列的默认值（快速，不会影响到表数据）Query OK, 0 rows affected (0.07 sec)
添加索引（需要花费一些时间，但是 0 rows affected 说明没有发生表拷贝）Query OK, 0 rows affected (21.42 sec)
修改列的数据类型（需要花费很长时间，并且重建表）Query OK, 1671168 rows affected (1 min 35.54 sec)

由于在执行 Online DDL 过程中需要记录并发执行的 DML 操作发生的变更，然后在执行完 DDL 操作之后再应用这些变更，因此使用 Online DDL 操作花费的时间比不使用 Online 模式执行要更长一些。
Online DDL 支持情况INSTANT 算法支持：MariaDB 10.3.2+ ， MySQL 8.0.12+ 。 NOCOPY 只支持 MariaDB 10.3.2 以上版本，不支持 MySQL ，这里就暂且忽略了。
重点关注是否重建表和支持并发 DML：不需要重建表，支持并发 DML 最佳。

文章插图
二级索引

文章插图
说明：

① 第一次添加全文索引字段时需要重建表，之后就不需要了

主键

文章插图
说明：

重建聚簇索引总是需要拷贝表数据（InnoDB 是“索引组织表”），所以最好是在创建表的时候就定义好主键
如果创建表是没有指定主键， InnoDB 会选择第一个 NOT NULL 的 UNIQUE 索引作为主键，或者使用系统生成的 KEY
② 对聚簇索引来说，使用 INPLACE 模式比 COPY 模式要高效一些：不会产生 undo log 和 redo log ，二级索引是有序的，所以可以按顺序加载，不需要使用变更缓冲区

普通列

文章插图
说明：

③ 并发 DML：当插入一个自增列时，不支持并发的 DML 操作，添加自增列时，大量的数据会被重新组织，代价高昂
③ 重建表：添加列时， MySQL 5.7及之前版本需要重建表， MySQL 8.0 当 ALGORITHM=INPLACE 时，需要重建表， ALGORITHM=INSTANT 时不需要重建
③ INSTANT算法：添加列时，使用 INSTANT 算法有下面这些限制添加列操作不能和其它不支持 INSTANT 算法的操作合并为一条 ALTER TABLE 语句新增的列只能添加到表的最后，不能放到其它列的前面，在 MariaDB 10.4 之后，支持在任意位置添加不能将列添加到 ROW_FORMAT=COMPRESSED 的表中不能将列添加到包含 FULLTEXT 的表中不能将列添加到临时表中，临时表只支持 ALGORITHM=COPY不能将列添加到驻留在数据字典表空间中的表中在添加列的时候不会计算行的大小限制，该限制在执行 DML 操作插入或者更新表时才会被检查
④ 删除列时，大量的数据需要被重新组织，代价高昂，在 MariaDB 10.4 之后，删除列支持 INSTANT 算法
⑤ 重命名列时，确保只改变列名，不改变数据类型，这样才能支持并发的 DML 操作
⑥ 扩展 VARCHAR 长度时， INPLACE 是有条件的，必须保证用于标识字符串长度的长度字节不变（这里说的都是字节，不是 VARCHAR 的字符长度，字节占用与采用的字符集有关， utf8 字符集下，一个字符占 3 个字节，utf8mb4 则 4 个字节）当 VARCHAR 列长度在 0-255 个字节时，长度标识占用一个字节当 VARCHAR 列长度大于 255 个字节时，长度标识占用两个字节

因此， INPLACE 只支持 0-255 个字节之间或者 256 个字节到更大的长度之间的变更。 VARCHAR 列长度减小是不支持 INPLACE 的。

⑦ 自增列值变更是修改的内存中的值，不是数据文件
- 上一页
- 1
- 2
- 3
- 下一页

MySQL &amp; MariaDB Online DDL参考指南( 二 )

MySQL & MariaDB Online DDL参考指南( 二 )