乐趣区

关于mysql:MySQL-Online-DDL-原理和踩坑

jiezi

MySQL 的 DDL(Data Definition Language) 包含增减字段、增减索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作会依照原来的表复制一份，并做相应的批改，例如，对表 A 进行 DDL 的具体过程如下：

  1. 依照表 A 的定义新建一个表 B
  2. 对表 A 加写锁
  3. 在表 B 上执行 DDL 指定的操作
  4. 将 A 中的数据拷贝到 B
  5. 开释 A 的写锁
  6. 删除表 A
  7. 将表 B 重命名为 A

在 2-4 的过程中，如果表 A 数据量比拟大，拷贝到表 B 的过程会耗费大量工夫，并占用额定的存储空间。此外，因为 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无奈提供服务。

因而，MySQL 5.6 减少了 Online DDL，容许在不中断数据库服务的状况下进行 DDL 操作。

用法 

ALTER TABLE tbl\_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALTER 语句中能够指定参数 ALGORITHM 和 LOCK 别离指定 DDL 执行的形式和 DDL 期间 DML 的兵法管制

  1. ALGORITHM=INPLACE 示意执行 DDL 的过程中不产生表拷贝，过程中容许并发执行 DML（INPLACE 不须要像 COPY 一样占用大量的磁盘 I / O 和 CPU，缩小了数据库负载。同时缩小了 buffer pool 的应用，防止 buffer pool 中原有的查问缓存被大量删除而导致的性能问题）。

    如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的形式，采纳表拷贝的形式进行，过程中会阻塞所有的 DML。另外也能够设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保障 DML 并发操作的准则抉择执行形式。

  2. LOCK=NONE 示意对 DML 操作不加锁，DDL 过程中容许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的申请，实用于须要尽快实现 DDL 或者服务库闲暇的场景）、SHARED（容许 SELECT，然而阻塞 INSERT UPDATE DELETE，实用于数据仓库等能够容许数据写入提早的场景）和 DEFAULT（依据 DDL 的类型，在保障最大并发的准则下来抉择 LOCK 的取值）

不过并不是所有的 DDL 操作都能用 INPLACE 的形式执行，具体的反对状况能够在 MySQL Reference Manual — Online DDL Operations 中查看。

例如 Table 14.10 中显示批改列的数据类型不反对 INPLACE

Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Changing the column data type No Yes No No

这时尝试将原类型为 FLOAT 的 column_name 改为 INT

ALTER TABLE tbl\_name MODIFY COLUMN column\_name INT, ALGORITHM=INPLACE, LOCK=NONE;

会报错 

ERROR: 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

执行过程

  1. 初始化：依据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中容许的并发量，这个过程中会获取一个 shared metadata lock，用来爱护表的构造定义
  2. 执行 DDL：依据第一步的状况决定是否将 shared metadata lock 降级为 exclusive metadata lock（仅在语句筹备阶段），而后生成语句并执行。执行期间的 shared metadata lock 保障了不会同时执行其余的 DDL，但 DML 能能够失常执行
  3. 提交：将 shared metadata lock 降级为 exclusive metadata lock，而后删除旧的表定义，提交新的表定义

Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以简直不会阻塞 DML 语句。

不过，在 DDL 执行前或执行时，其余事务能够获取 MDL。因为须要用到 exclusive MDL，所以必须要等到其余占有 metadata lock 的事务提交或回滚后能力执行下面两个波及到 MDL 的中央。

踩坑

后面提到 Online DDL 执行过程中须要获取 MDL，MDL (metadata lock) 是 MySQL 5.5 引入的表级锁，在拜访一个表的时候会被主动加上，以保障读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁。

为了在大表执行 DDL 的过程中同时保障 DML 能并发执行，后面应用了 ALGORITHM=INPLACE 的 Online DDL，但这里依然存在死锁的危险，问题就出在 Online DDL 过程中须要 exclusive MDL 的中央。

例如，Session 1 在事务中执行 SELECT 操作，此时会获取 shared MDL。因为是在事务中执行，所以这个 shared MDL 只有在事务完结后才会被开释。

# Session 1
> START TRANSACTION;
> SELECT \* FROM tbl\_name;
# 失常执行

这时 Session 2 想要执行 DML 操作也只须要获取 shared MDL，依然能够失常执行。

# Session 2
> SELECT \* FROM tbl\_name;
# 失常执行

但如果 Session 3 想执行 DDL 操作就会阻塞，因为此时 Session 1 曾经占用了 shared MDL，而 DDL 的执行须要先获取 exclusive MDL，因而无奈失常执行。

# Session 3
> ALTER TABLE tbl\_name ADD COLUMN n INT;
# 阻塞

通过 `show processlist` 能够看到 ALTER 操作正在期待 MDL。

+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| Id | User            | Host             | db   | Command | Time | State                           | Info            |
│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| 11 | root            | 172.17.0.1:53048 | demo | Query   |    3 | Waiting for table metadata lock | alter table ... |
+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+

因为 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全副阻塞 

# Session 4
> SELECT \* FROM tbl\_name;
# 阻塞

到这一步，后续无论是 DML 和 DDL 都将阻塞，直到 Session 1 提交或者回滚，Session 1 占用的 shared MDL 被开释，前面的操作能力继续执行。

下面这个问题次要有两个起因：

  1. Session 1 中的事务没有及时提交，因而阻塞了 Session 3 的 DDL
  2. Session 3 Online DDL 阻塞了后续的 DML 和 DDL

对于问题 1，不少 ORM（例如 pymysql）都默认将用户语句封装成事务执行，如果客户端程序中断退出，还没来得及提交或者回滚事务，就会呈现 Session 1 中的状况。这时能够在 infomation_schema.innodb_trx 中找出未实现的事务对应的线程，并强制退出 

> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421564480355704
                 trx_state: RUNNING
               trx_started: 2020-07-21 01:49:41
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 9
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.0025 sec)

能够看到 Session 1 正在执行的事务对应的 trx_mysql_thread_id 为 9，而后执行 KILL 9 即可中断 Session 1 中的事务。

对于问题 2，在查问很多的状况下，会导致阻塞的 session 迅速增多，对于这种状况，能够先中断 DDL 操作，避免对服务造成过大的影响。也能够尝试在从库上批改表构造后进行主从切换或者应用 pt-osc 等第三方工具。

退出移动版