MySQL OnlineDDL发展历程及各算法介绍
发布于:2024-10-17
最后更新: 2024-10-18
次查看
MySQL
Online DDL
URL
date
AI summary
slug
status
tags
summary
type

COPY

MySQL 5.5之前的版本，对于DDL只支持以COPY表的方式来进行。COPY是一种性能较低的算法，是由Server层通过创建一个临时表的方式来进行数据复制，大致流程如下：
  1. 创建临时表（也叫影子表）
  1. 对临时表应用DDL
  1. 对原表加写锁，禁止DML
  1. 把原表记录逐行从复制到临时表中
  1. 对原表加读锁，禁止查询
  1. 删除原表，把临时表名更新成原表名
下图展示了过程中的数据变化：
notion image
如果使用的是这些版本的MySQL，对于大表结构变更用的比较多的 DDL 操作工具是 pt-online-schema-change 或是 gh-ost，当然现在用的也比较多。

INPLACE

而从MySQL 5.5开始，推出了另一种算法：INPLACE。这种算法和COPY不同的是：INPLACE完全是在InnoDB存储引擎层自行处理，不需要Server介入，所以得名“原地更新”。
INPLACE算法最初的应用就是在Fast Index Creation上。FIC改变了二级索引的添加和删除流程，不再需要使用COPY的方式，把整个表结构和数据都复制一遍，只需要通过读取聚簇索引来构建新的二级索引即可，不仅减少了Redo log、Undo log等的写入，也减少了聚簇索引以及其他二级索引的复制。FIC的大致流程如下：
  1. 对原表加写锁，禁止DML
  1. 读取聚簇索引
  1. 使用二级索引字段排序，构建新的二级索引树
  1. 把二级索引数合并到ibd文件
虽然FIC对整体的性能做了不少的提升，但是在执行期间还是无法进行DML操作。所以MySQL 5.6在FIC（INPLACE）的基础上，正式推出了Online DDL的概念。
什么是Online DDL呢？区分是否Online的唯一标准就是在DDL执行期间，能不能对原表并发执行DML。当时的Online DDL基本等同于INPLACE算法，但是随着MySQL的发展，在8.0.12版本还引入了INSTANT算法，这是一种更高效的Online DDL算法（当然它也属于inplace更新）。所以下面我们会用具体的算法名称来介绍具体的算法流程。当时的INPLACE算法能做到支持并发DML的主要是通过区分了DDL的类型，比如：
  1. 区分了DDL是否Only Modifies Metadata
    1. 比如删除二级索引，Only Modifies Metadata
    2. 重命名索引，Only Modifies Metadata
  1. 区分了DDL是否需要Rebuilds Table
    1. 比如删除字段，需要 rebuilds table
    2. 比如修改字段类型，需要 rebuilds table
对于第一种类型，只修改元数据，本身执行速度就非常快，所以几乎也不存在影响读写请求。而对于第二种类型里不需要Rebuilds Table的DDL，多数其实都属于Only Modifies Metadata，有一种比较特殊且常见，就是添加二级索引，这个我们一会再说。下面先看看成本最高的，需要Rebuilds Table的大致流程，整体分成了三个阶段：
  1. Initialization
    1. 加X锁，防止其他DDL并发执行
    2. 根据DDL语句上指定的ALGORITHM和LOCK来计算Execution阶段需要升级到的锁粒度
    3. 更新数据字典的内存对象
    4. 如果需要Rebuilds Table，那么去创建临时表文件
    5. 如果需要Rebuilds Table或者是创建二级索引，那么去创建row_log文件
  1. Execution
    1. 根据Initialization的评估，来决定锁是否降级以及降级到哪种粒度
    2. 如果需要Rebuilds Table，那么去读取聚簇索引，构建新的聚簇索引树和二级索引树，并合并写入临时表文件
    3. 如果是创建二级索引，那么去读取聚簇索引，构建新的二级索引树，并合并写入原表文件
    4. 如果支持并发读写，那么对于并发执行的DML，记录到row_log中
    5. 应用row_log里的变更记录到新表中（非最后一个block）
  1. Commit Table Definition
    1. 升级至X锁，禁止读写
    2. 应用row_log里最后一个block的变更记录到新表中
    3. 更新 InnoDB 的数据字典
    4. 提交 DDL 事务
    5. 清理操作 Clean Up
可以看到过程中也需要禁止原表读写，不过时间相对来说都比较短，并且不会随着数据量的增加而明显变长。而对于其中最耗时的部分已经Online了。
看到这里你可能会问，INPLACE算法下的Rebuilds Table也是要复制表，那和之前的COPY算法有什么区别呢？他们的主要区别是：COPY算法是Server层通过创建了一张临时表，然后逐行复制，其中会写redo log、undo log等等。而Rebuilds Table是直接通过读取ibd文件生成新的索引树，对于磁盘和cpu的消耗会降低不少。下图展示了过程中的数据变化：
notion image
💡
之前我理解的应用row_log和执行DDL变更是不同的线程在执行的，但是看了网上的一些源码分析，感觉上应该是单线程执行的。在通过原表聚簇索引构建完新的索引之后，就会进入到应用row_log的流程中，并且row_log是按照一个个block组织的（block的大小由innodb_sort_buffer_size确定，默认值为1M）。每切换到一个block，都会短暂的加一下X锁，如果该block不是最后一个block，则先解锁，再应用。直到切换到最后一个block，此时会一直加X锁直到全部row_log应用完，至此新老索引达到一个一致性的状态。

INSTANT

8.0.12版本

可以看到，INPLACE的性能问题主要还是存在于Rebuilds Table的场景。是否Rebuilds Table主要取决于数据格式有没有更改，比如新增字段，删除字段，对数据格式都有改动，所以都只需要Rebuilds Table，而新增字段又是一个业务迭代过程中非常频繁的一个诉求。所以在MySQL 8.0.12版本，引入了一种新的算法——INSTANT。这种算法可以快速的添加新列。它是如何做到的呢？下面这张图可以给你一个直观的感受：
notion image
简单来说就是对于新增字段，不再去重建整张表，而是通过
  1. 在表元数据里增加instant add column之前的原列数来标识instant列
  1. 在列元数据里增加了列是否有默认值以及对应默认值来标识instant列
  1. 在row_format里增加行记录的instant标识，以及行记录里的字段数量
      2. notion image
然后在读取数据的时候结合上面两部分信息做一个简单的计算：
首先判断行记录里的instant标识
  1. 如果为0，代表是instant加列前的数据行，那么需要追加新增的那几列，且都为默认值
  1. 如果为1，代表instant加列后产生的数据行，那么还需要结合数据行里的字段数量和当前表字段数量判断
    1. 如果两者相等，那么直接返回行数据即可
    2. 如果不相等，那么数量之差就是instant列，按列元数据追加最后几列即可
可以看到，此版本对于元数据的记录比较简单，就是通过一个原列数来识别哪些是instant列（原列数之后的列）。那也不难理解对于instant加列的其中的一个约束：新增字段位置必须添加在表字段最后。

可观测

information_schema.innodb_tables 表里的instant_cols字段代表的是instant add column时的原列数，而不是instant列数。具体哪些列是instant列，可以查看information_schema.innodb_columns 表，has_default=1表示instant列，default_value表示默认值
mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            0 |
+----------+---------+--------------+
1 row in set (0.22 sec)

mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
|     1065 | a    |           0 | NULL          |
|     1065 | b    |           0 | NULL          |
+----------+------+-------------+---------------+
2 rows in set (0.38 sec)

mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            2 |
+----------+---------+--------------+
1 row in set (0.03 sec)

mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
|     1065 | a    |           0 | NULL          |
|     1065 | b    |           0 | NULL          |
|     1065 | c    |           1 | NULL          |
|     1065 | d    |           1 | 800003e8      |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)

mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            2 |
+----------+---------+--------------+
1 row in set (0.03 sec)

mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+--------------------------+
| table_id | name | has_default | default_value            |
+----------+------+-------------+--------------------------+
|     1065 | a    |           0 | NULL                     |
|     1065 | b    |           0 | NULL                     |
|     1065 | c    |           1 | NULL                     |
|     1065 | d    |           1 | 800003e8                 |
|     1065 | e    |           1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)

8.0.29版本

而在8.0.29版本，MySQL为了更进一步支持instant删列，不再使用表元数据上的原列数来判断instant列了，而是通过在列元数据上增加版本号以及在数据行里增加版本号来计算行数据。这顺带也让instant加列支持了任意位置。先看下行格式的变化，使用到了另一个预留位来标识这种类型的数据。然后增加了一个字节来存储version信息
notion image
下面再通过一个具体的实例来展示列元数据的变化：
create table t_instant(a int, b int, primary key(a));
alter table t_instant add c varchar(30), algorithm=instant;
alter table t_instant add c2 varchar(30), algorithm=instant;
alter table t_instant drop b, algorithm=instant;


mysql>desc t_instant;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int         | NO   | PRI | NULL    |       |
| c     | varchar(30) | YES  |     | NULL    |       |
| c2    | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------++

mysql> select t2.name, t2.ordinal_position as ord_no, t2.se_private_data,
    t2.hidden, t2.type, t2.char_length
from mysql.tables t1, mysql.columns t2
where t1.id = t2.table_id
and t1.name = 't_instant';

+--------------------------+--------+--------------------------------------------------------------+---------+---------------------+-------------+
| name                     | ord_no | se_private_data                                              | hidden  | type                | char_length |
+--------------------------+--------+--------------------------------------------------------------+---------+---------------------+-------------+
| a                        |      1 | physical_pos=0;table_id=1086;                                | Visible | MYSQL_TYPE_LONG     |          11 |
| c                        |      2 | default_null=1;physical_pos=4;table_id=1086;version_added=1; | Visible | MYSQL_TYPE_VARCHAR  |         120 |
| c2                       |      3 | default_null=1;physical_pos=5;table_id=1086;version_added=2; | Visible | MYSQL_TYPE_VARCHAR  |         120 |
| DB_TRX_ID                |      4 | physical_pos=1;table_id=1086;                                | SE      | MYSQL_TYPE_INT24    |           6 |
| DB_ROLL_PTR              |      5 | physical_pos=2;table_id=1086;                                | SE      | MYSQL_TYPE_LONGLONG |           7 |
| !hidden!_dropped_v3_p3_b |      6 | physical_pos=3;version_dropped=3;                            | SE      | MYSQL_TYPE_LONG     |          11 |
+--------------------------+--------+--------------------------------------------------------------+---------+---------------------+-------------+
可以看到列c、c2的se_private_data里都记录了添加的版本，b字段被删掉了，也记录了对应的版本。而每一行数据里也记录了这行数据更新时的版本。所以读取一行数据的流程，需要判断行数据上的版本号和表最新的版本号，如果两者相等，则可以直接返回。如果不相等，则需要构建出该版本和最新版本之间的差异数据（少的要补，多的要删）

版本限制

一张表的row version当前的上限是64，如果版本号已经达到64，那么后续就没法走instant加列或者删列了，只能退化成inplace算法。在达到限制后，强制执行instant算法则会报错：
ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
可以通过下面的SQL来观察表的row version
SELECT NAME,TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
另外需要注意的是，如果说想要instant drop column，该列上不能有索引。如果有索引的话需要先调整相关索引之后再instant drop column。

参考

  1. 创建二级索引相关
    1. 创建二级索引
    2. MySQL 二级索引分析
    3. MySQL5.7——Online Create Index
  1. INSTANT算法相关
    1. MySQL 8.0: InnoDB now supports Instant ADD COLUMN
    2. MySQL 8.0 INSTANT ADD and DROP Column(s)
    3. 图解MySQL | [原理解析] MySQL 为表添加列 是怎么”立刻”完成的
    4. MySQL · 引擎特性 · 8.0 Instant Add Column功能解析
  1. MySQL InnoDB Online DDL
  1. MySQL Online DDL的改进与应用
  1. 浅谈 DDL 技术解密
  1. MySQL · 源码分析 · Row log分析
  1. 图解MySQL | MySQL DDL为什么成本高？
  1. MySQL · 源码阅读 · 白话Online DDL
  1. MySQL谬误集02: DDL锁表
  1. MySQL Online DDL 原理和踩坑
  1. MySQL · 特性分析 · MDL 实现分析
  1. MySQL · 源码分析 · 8.0 · DDL的那些事
  1. MySQL · 源码分析 · 常用SQL语句的MDL加锁源码分析
  1. MySQL online DDL原理
  1. MySQL DDL 执行方式 -Online DDL 介绍
  1. Mysql 不同版本online DDL的处理策略
相关文章
2024-07-29
MySQL 连接阶段
2024-07-24
记一次mysql连接问题
2024-07-18
一文说透批量SQL
2024-07-15
你真的懂PreparedStatement吗？
2024-07-11
惊！一条SQL竟有如此威力
2024-06-13
MySQL JSON字段部分更新实验
关于怎么搭建一个这样的blogMySQL 连接阶段