MySQL&MariaDB-Online-DDL-参考指南

549次阅读  |  发布于3年以前

图文无关

概述

在早期的 MySQL 版本中，DDL 操作（如创建索引等）通常都需要对数据表加锁，操作过程中 DML 操作都会被阻塞，影响正常业务。MySQL 5.6 和 MariaDB 10.0 开始支持 Online DDL，可以在执行 DDL 操作的同时，不影响 DML 的正常执行，线上直接执行 DDL 操作对用户基本无感知（部分操作对性能有影响）。

不同版本的数据库对各种 DDL 语句的支持存在一定的差异，本文将会针对 MySQL 和 MariaDB 对 Online DDL 的支持情况做一个汇总，在需要执行 DDL 操作时，可以参考本文的 Online DDL 支持情况 部分。

本文将会持续修正和更新，最新内容请参考我的 GITHUB 上的 程序猿成长计划 项目，欢迎 Star，更多精彩内容请 follow me。

在 ALTER TABLE 语句中，支持通过 ALGORITHM 和 LOCK 语句来实现 Online DDL：

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM 支持的算法

ALGORITHM 说明
DEFAULT 默认算法，自动使用可用的最高效的算法
COPY 最原始的方式，所有的存储引擎都支持，不使用 Online DDL，操作时会创建临时表，执行全表拷贝和重建，过程中会写入 Redo Log 和大量的 Undo Log，需要添加读锁，非常低效
INPLACE 尽可能避免表拷贝和重建，更确切的名字应该是 ENGINE 算法，由存储引擎决定如何实现，有些操作是可以立即生效的（比如重命名列，改变列的默认值等），但有些操作依然需要全表或者部分表的拷贝和重建（比如添加删除列、添加主键、改变列为 NULL 等）
NOCOPY 该算法是 INPLACE 算法的子集，用于避免聚簇索引（主键索引）的重建造成全表重建，也就说用该算法会禁止任何引起聚簇索引重建的操作
INSTANT 用于避免 INPLACE 算法在需要修改数据文件时异常低效的问题，所有涉及到表拷贝和重建的操作都会被禁止

NOCOPY 算法支持：MariaDB 10.3.2+，MySQL 不支持该算法。

INSTANT 算法支持：MariaDB 10.3.2+，MySQL 8.0.12+。

算法使用规则：

ALGORITHM 优劣

MySQL 服务主要为 Server 层 和 存储引擎层 两部分组成，Server 层包含了 MySQL 大部分核心功能，所有的内置函数，跨存储引擎的功能如存储过程、触发器、视图等。存储引擎层负责数据的存储和读取，采用了插件式的架构模式。

COPY 算法 作用在 Server 层，其执行过程都是在 Server 层，因此所有存储引擎都支持使用该算法，执行过程如下图

COPY算法执行过程

INPLACE 算法 作用于存储引擎层，是 InnoDB 存储引擎特有的 DDL 算法，执行过程如下图所示

INPLACE 算法执行过程

LOCK 策略

默认情况下，MySQL/MariaDB 在执行 DDL 期间会使用尽可能少的锁，如果必要，可以通过 LOCK 子句控制在执行 DDL 时允许对表加锁的级别。如果指定的操作所要求的限制级别不满足（EXCLUSIVE > SHARED > NONE），则语句执行失败并报错。

策略 说明
DEFAULT 使用当前操作支持的粒度最小的锁策略
NONE 不获取任何表锁，允许所有的 DML 操作
SHARED 对表添加共享锁（读锁），只允许只读的 DML 操作
EXCLUSIVE 对表添加排它锁（写锁），不允许任何 DML 操作

为了避免执行 DDL 时，由于锁表导致生产服务不可用，在执行表结构变更语句时，可以添加 LOCK=NONE 子句，如果语句需要获取共享锁或者排它锁，则会直接报错，这样就可以避免意外锁表，造成线上服务不可用了。

Online DDL 执行过程

Online DDL 操作主要分为三个阶段：

Online DDL 执行过程

元数据锁（MDL，Metadata Lock）主要用于 DDL 和 DML 操作之间的并发访问控制，保护表结构（表定义）的一致，保证读写的正确性。MDL 不需要显式的使用，在访问表时会自动加上。

MDL

由于上面三个阶段中对元数据锁的独占， Online DDL 过程必须等待已经持有元数据锁的并发事务提交或者回滚才能继续执行。

注意：当 Online DDL 操作正在等待元数据锁时，该元数据锁会处于挂起状态，后续的所有事务都会被阻塞。在 MariaDB 10.3 之后，可以通过添加 NO WAIT 或者 WAIT n 来控制等待所得超时时间，超时立即失败。

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 语句的性能再选择如何操作。

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

比如

由于在执行 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 最佳。

Online DDL Select Path

二级索引

操作 INSTANT INPLACE 重建表 并发 DML 只修改元数据
创建或者添加二级索引 ❌ ✅ ❌ ✅ ❌
删除索引 ❌ ✅ ❌ ✅ ✅
重命名索引 （⚠️MySQL 5.7+，MariaDB 10.5.2+） ❌ ✅ ❌ ✅ ✅
添加 FULLTEXT 索引 ❌ ✅ ① ❌ ① ❌ ❌
添加 SPATIAL 索引（⚠️MySQL 5.7+，MariaDB 10.2.2+） ❌ ✅ ❌ ❌ ❌
修改索引类型 ✅ ✅ ❌ ✅ ✅

说明：

主键

操作 INSTANT INPLACE 重建表 并发 DML 只修改元数据
添加主键 ❌ ✅ ② ✅ ② ✅ ❌
删除主键 ❌ ❌ ✅ ❌ ❌
删除一个主键同时添加一个新的 ❌ ✅ ✅ ✅ ❌

说明：

普通列

操作 INSTANT INPLACE 重建表 并发 DML 只修改元数据
列添加 ✅ ③ ✅ ❌ ③ ✅ ③ ❌
列删除 ❌ ④ ✅ ✅ ✅ ❌
列重命名 ❌ ✅ ❌ ✅ ⑤ ✅
改变列的顺序 ❌ ⑫ ✅ ✅ ✅ ❌
设置默认值 ✅ ✅ ❌ ✅ ✅
修改数据类型 ❌ ❌ ✅ ❌ ❌
扩展 VARCHAR 长度（⚠️MySQL 5.7+, MariaDB 10.2.2+） ❌ ⑬ ✅ ❌ ⑥ ✅ ✅
删除列的默认值 ✅ ✅ ❌ ✅ ✅
改变自增值 ❌ ✅ ❌ ✅ ❌ ⑦
设置列为 NULL ❌ ✅ ✅ ⑧ ✅ ❌
设置列为 NOT NULL ❌ ✅ ⑨ ✅ ⑨ ✅ ❌
修改 ENUM 和 SET 列的定义 ✅ ✅ ❌ ⑩ ✅ ✅

说明：

生成列

操作 INSTANT INPLACE 重建表 并发 DML 只修改元数据
添加 STORED 列 ❌ ❌ ✅ ❌ ❌
修改 STORED 列的排序 ❌ ❌ ✅ ❌ ❌
删除 STORED 列 ❌ ✅ ✅ ✅ ❌
添加 VIRTUAL 列 ✅ ✅ ❌ ✅ ✅
修改 VIRTUAL 列的排序 ✅ ❌ ✅ ❌ ❌
删除 VIRTUAL 列 ✅ ✅ ❌ ✅ ✅

外键

操作 INSTANT INPLACE 重建表 并发 DML 只修改元数据
添加外键约束 ❌ ✅ ⑭ ❌ ✅ ✅
删除外键约束 ❌ ✅ ❌ ✅ ✅

说明：

表

操作 INSTANT INPLACE 重建表 并发 DML 只修改元数据
修改 ROW_FORMAT ❌ ✅ ✅ ✅ ❌
修改 KEY_BLOCK_SIZE ❌ ✅ ✅ ✅ ❌
设置持久表统计信息 ❌ ✅ ❌ ✅ ✅
指定字符集 ❌ ✅ ✅ ⑮ ❌ ❌
转换字符集 ❌ ❌ ✅ ⑯ ❌ ❌
优化表 ❌ ✅ ⑰ ✅ ✅ ❌
使用 FORCE 选项重建表 ❌ ✅ ⑱ ✅ ✅ ❌
执行空的重建 ❌ ✅ ⑲ ✅ ✅ ❌
重命名表 ✅ ✅ ❌ ✅ ✅

说明：

表空间

操作 INSTANT INPLACE 重建表 并发 DML 只修改元数据
重命名常规表空间 ❌ ✅ ❌ ✅ ✅
启用或者禁用常规表空间加密 ❌ ✅ ❌ ✅ ❌
启用或者禁用 file-per-table 表空间加密 ❌ ❌ ✅ ❌ ❌

限制

写在最后

本文将会持续修正和更新，最新内容请参考我的 GITHUB 上的 程序猿成长计划 项目，欢迎 Star，更多精彩内容请 follow me。

参考

Copyright© 2013-2020

All Rights Reserved 京ICP备2023019179号-8