如何修改线上表的表结构

虽然说在进行数据库设计的时候就应该尽可能的考虑以后的业务发展,但是有时候再严密的考虑也赶不上变化,同时设计太多没有意义的预留字段也不是一个好的方案。所以无法避免的,我们偶尔会遇到需要修改线上表结构的情况。

那么对于一个提供 7 * 24 服务的 InnoDB 的 MySQL 而言,以什么方式修改线上表结构才是安全的呢?


直接修改表结构会带来什么问题

首先,MySQL 除了有表数据的读写锁以外,还有元数据的读写锁。什么是元数据?简单来说就是除了表数据以外的数据,最主要的就是指表结构。

表数据的存储位置

表数据的存储位置主要取决于参数 innodb_file_per_table,当参数为 OFF 时,代表所有的表格数据都存放在 InnoDB 的系统表空间中(.ibdata1 文件),是一个共享表空间;当参数为 ON 的时候,代表每个 InnoDB 表格都单独存放在一个文件中(.idb 文件)。该参数默认为 ON。

在任何情况,都建议将该参数设置为 ON。每个表单独存储为一个文件更容易管理。

当在不需要这张表的时候,通过 drop table 命令,系统会直接删除这个文件;而如果是放在共享表空间中,即使表删掉了,空间也不会回收的。


表结构的存储位置

表数据的存储位置会受参数控制,而表结构是固定存放在 .frm 文件中。

当设定了 innodb_file_per_table 参数为 ON 之后,每张表的表数据将会被存放在 .idb 文件中,而表结构则存放在于 .idb 文件的同级目录下的 .frm 文件内。例如在 InnoDB 中新建了 user 表,就会有 user.idb 和 user.frm 文件分别存放表数据和表结构。


直接执行 DDL 会导致什么后果

如果是我们本地运行的 MySQL ,直接执行 DDL 当然没有任何问题。但是对于一个正在线上运行的 MySQL 服务,随刻都有查询/修改数据的事务正在执行,如果这时候直接执行 DDL 语句的话。会引发 MDL(mete data lock)waiting:正在对表数据进行查询/更新的事务获取了多个 MDL 读锁,而 DDL 语句执行需要申请 MDL 写锁,这时候执行 DDL 语句的事务会处于阻塞等待状态,同时由于有事务正在等待 MDL 写锁,导致后面那些想对表数据进行查询/更新(申请 MDL 读锁)的事务也进入阻塞状态。最终表现为整个表无法进行读写

Session A Session B Session C Session D
BEGIN;
SELECT *
FROM t;
(持有 MDL 读锁)
SELECT *
FROM t;
(持有 MDL 读锁)
ALTER TABLE t
ADD c INT;
(需要 MDL 写锁,blocked)
SELECT *
FROM t;

or

UPDATE t
SET c = 1
WHERE id = 1;

(“增删改查”需要 MDL 读锁,
但由于 MDL 写锁正在等待,
blocked)

既然让申请 MDL 写锁的事务一直处于等待状态不行,那么一个较为温和的方法是为该事务设置一个超时时长,要是在超时时长内获取到了 MDL 锁就代表 DDL 能够顺利执行,要是超过了超时时长,那么先放弃 DDL 事务,等待下次重试。

这个方案看着可行,但是只适用于吞吐量不大的数据库,或者非热点小表,同时还要求修改表结构需求不那么迫切。对于一个有一定吞吐量的数据库而言,TPS 基本上就不会低于 500,这时候 DDL 事务根本连“见缝插针”的机会都没有,DDL 事务只会一直处于不断超时,不断重试的循环中 … 如果仍然选择强行重试,最终数据库会表现为 TPS 隔一段时间就下降(DDL 事务正在申请 MDL 写锁,导致其他正常事务 blocked)。


修改线上表结构的方式

既然直接执行 DDL 不行,目前有哪些可靠的修改线上表结构的方式呢?

copy & inplace

首先 DDL 分为 copy 和 inplace 两种:

copy 方式是在 server 层执行的,意味着支持所有引擎。copy 方式会在操作过程中生成临时表,用于写入原表修改过的数据,同时在原表路径下会生成临时表的 .frm 和 .ibd 文件。对于 MyISAM 存储引擎只有 copy 操作,而 InnoDB 只有不支持 inplace 的操作才会使用 copy。

inplace 方式所有操作在 InnoDB 引擎层完成,不需要经过临时表的中转。其中根据“是否修改记录格式”为基准又分为 rebuild 和 no-rebuild。rebuild 需要重建表(重新组织记录),比如优化表、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等都属于 rebuild;no-rebuild 是指只需要修改表的元数据,比如删除索引、修改列名、修改列默认值、修改列自增值等都属于 no-rebuild。

rebuild 涉及表的重建,在原表路径下创建新的 .frm 和 .ibd 文件,消耗的 IO 较多。重建期间的 DML 操作会被记录到申请的 row log 空间中,在 DDL 提交阶段应用新的表空间中;no-rebuild 部分由于不涉及表的重建,除创建添加索引,会产生部分二级索引的写入操作外,其余操作均只修改元数据项,即只在原表路径下产生 .frm 文件,不会申请 row log,不会消耗过多的 IO,速度很快。


offline & online

除了 copy 和 inplace 概念以外,还有 offline 和 online 概念:

offline 是指在执行 DDL 过程中,会一直占用这 DML 写锁,其他 DML 操作都不能进行,意味着 DDL 过程中表不能进行增删改查(需要获取 DML 读锁)。

online 是指在执行 DDL 过程中,只会占用 DML 读锁(主要是为了保护自己,确保不会有其他 DDL 同时执行),由于表的增删改查只需要获取 DML 读锁,所以可以进行。

copy 方式的都是 offline 的,inplace 方式除了添加全文索引和添加空间索引以外,都是 online 的。


各种操作的分类

  • copy offline :DDL 过程中,不支持表增删改查(offline),需要借助临时表(copy)

    1. 删除主键

    2. 更改列数据类型

    3. 转换字符集

    4. 表空间加密

    5. 分区操作

  • inplace online :DDL 过程中,支持表增删改查(online),不需要借助临时表(inplace)

    • rebuild:涉及修改记录格式,需要重建表

      1. 添加主键
      2. 删除主键同时添加主键
      3. 添加/删除列
      4. 修改 ROW_FORMAT
      5. 修改 KEY_BLOCK_SIZE
      6. 指定字符集
      7. 重建表
    • on-rebuild:不涉及修改记录格式,只需要修改表的元数据

      1. 创建/添加二级索引

      2. 删除/重命名二级索引

      3. 更改索引类型

      4. 修改列名

      5. 修改列默认值

      6. 拓展 varchar 列大小:仅限当在 0-255 之间调整,大于 255 需要使用 copy 模式,因为 0-255 占一个字节,而超过 255 之后需要占用两个字节

      7. 添加/删除外键

      8. 修改表名

  • inplace but offline :DDL 过程中,不支持表增删改查(offline),不需要消耗额外空间(inplace)

    1. 添加全文索引(FULLTEXT index)
    2. 添加空间索引(SPATIAL index)

其中 更改列数据类型转换字符集添加全文索引添加空间索引 是需要我们特别注意的,因为这几种操作在日常数据库维护中出现的概率较高,而它们又是 offline(阻塞表的增删改查事务)的。


InnoDB 的 Online DDL

Online DDL 主要包括 3 个阶段:Initialization 阶段,Execution 阶段,Commit 阶段。rebuild 方式比 no-rebuild 方式多了一个 Execution 阶段,Execution 阶段和 Commit 阶段类似。

Online DDL 执行过程中包括三个阶段:

  • Initialization 阶段:

    1. 创建新的临时 .frm 文件

    2. 持有 MDL 写锁,禁止读写(由于表的增删改查都需要 MDL 读锁,所以这时候表的增删改查都无法进行)

    3. 根据 Alert 类型,确定执行方式(copy / online rebuild / online no-rebuild)

    4. 更新数据字典的内存对象

    5. 如果是 rebuild,分配 row_log 对象记录增量

    6. 如果是 rebuild,生成新的临时 .ibd 文件

  • Execution 阶段(如果是 no-rebuild 的话则没有这一阶段):

    1. 降级 MDL 锁(MDL 写锁降级为 MDL 读锁),允许读写(表能够进行增删改查)

    2. 扫描 old_table 的聚集索引每一条记录 rec

    3. 遍历新表的聚集索引和二级索引,逐一处理

    4. 根据 rec 构造对应的索引项

    5. 将构造索引项插入 sort_buffer 块

    6. 将 sort_buffer 块插入新的索引

    7. 处理 ddl 执行过程中产生的增量(仅 rebuild 类型需要)

  • Commit 阶段:

    1. 升级到 MDL 锁(MDL 读锁升级为 MDL 写锁),禁止读写(表不能进行增删改查)
    2. 重做最后 row_log 中最后一部分增量
    3. 更新 InnoDB 的数据字典表
    4. 提交事务(刷事务的 redo log)
    5. 修改统计信息
    6. rename 临时 .idb 文件和 .frm 文件

我对其中每个阶段的“主要”内容进行了加粗显示。


Online DDL 的特点:

  • 优点:索引的创建和删除、修改默认值速度很快,几乎没有性能影响。
  • 缺点:写请求较多的情况下,执行 DDL 会影响性能,也有因为获取锁超时而失败;同样是写请求较大的情况下,有可能因为用于记录增加修改的内存不足而失败;可能会导致主从延迟;一旦开始无法暂停。

具体例子

以 DBA 最常见优化表操作(OPTIMIZE TABLE table_name)为例,简单说明一下 Online DDL 的流程。

与重建表操作相关的命令有三个:

  • ALTER TABLE :真正的重建表

  • ANALYZE TABLE :对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁(当 MySQL 优化器选错执行方案时,执行一下该命令可能会有帮助)

  • OPTIMIZE TABLE :重建表并重新统计索引信息( ALTER TABLE + ANALYZE TABLE

你可能会有疑问,没事为什么需要重建表?

当一个表运行时间长了,除了有数据插入,通常还伴随着大量的数据删除,而 MySQL 是使用 B+ 数结构,将数据按页进行存储的。数据删除通常只会将对应的记录位置标记为可复用,当有新数据的插入位置落在可复用位置,该位置才会被重新利用。对于使用数据库自增 id 作为主键的表,新数据的 id 由于是一直自增,那些因为数据删除而被标记为可复用的记录位置,根本不会被再次使用。这也就是为什么通常我们删除了表中的部分数据,而数据文件并没有因此变小的原因。这时候就应该进行“重建表”操作,让数据文件中的数据重新变得“紧凑”起来。

不难看出,重建表中耗时最长的是将旧表数据导入新表的过程。值得注意的是 InnoDB 的这种 Online DDL 的操作只有开始和结束的时候才需要获取 MDL 写锁,在中间过程使用的是读锁。也就是为什么 Online 的原因(只有开始和结束两个很短的时间,表的读写会受影响,其余时间表仍能进行增删改查)。

以真实的重建表为例( ALTER TABLE t ENGINE = InnoDB ), Online DDL 的大致流程为:

  1. 扫描旧表的所有数据页,生成一个临时文件 a
  2. 使用临时文件 a 中的数据生成 B+ 树,存储到临时文件 b 中
  3. 将旧表的数据导入到临时文件(新表)当中
  4. 在生成临时文件的过程中,将对旧表的操作记录存放在单独日志文件(row log)中
  5. 临时文件 b 生成后,将日志应用到临时文件 b 中,确保此时的数据和旧表一致
  6. 使用临时文件替换旧表的数据文件

其他 DDL 的操作流程和重建表的操作流程基本相似。重建表流程属于 inplace online ,所以我们能够在正常提供服务(表的增删改查)的同时,对表进行重建,使数据/索引重新变得紧凑。但不意味着我们能够想什么时候重建表就什么时候重建表。因为 DDL 过程仍然需要消耗服务器 CPU 和 IO。


开源工具

PT Online Schema Change(PT-OSC)

PT-OSC(PT Online Schema Change)工具特点与优势是支持并发 DML 操作。核心原理是利用触发器监听原表的 DML 操作。

使用 PT-OSC 进行 DDL 的大致流程为:

  1. 创建一张与旧表相同结构的新表
  2. Alert 新表
  3. 在原表上创建“增删改”三种类型的触发器
  4. 将旧表数据导到新表中
  5. 通过触发器将发生在旧表的 DML 操作在新表执行一遍
  6. 处理外键
  7. 将旧表重命名为 Old 表,将新表重命名为原表名
  8. 删除 Old 表

PT-OSC 的特点:

  • 优点:支持并发 DML 较好;支持通过参数进行各种限制,如限制 CPU 、线程数量等等。
  • 缺点:使用触发器会存在额外开销;触发器与原始查询共享相同的事务空间,原始查询在表上有锁竞争;触发器无法暂停,当主库 LOAD 变高,希望停止变更时,但触发器是不会停止。
  • 限制:原表必须存在主键 PRIMARY KEY 或者 UNIQUE KEY;原表不能存在触发器,否则会导致触发器冲突;外键的处理需要指定 alter-foreign-keys-method 参数,存在风险。

GitHub’s Online Schema Transformer(GH-OST)

GH-OST 是 GitHub 的在线表定义转换器,与 PT-OSC 的最大区别,在于 GH-OOST 的无触发器设计。

使用 GH-OST 进行 DDL 的大致流程为:

  1. 测试 DB 是否可联通,验证 DB 是否存在
  2. 确认链接实例是否正确
  3. 权限校检
  4. binlog 验证、row 格式验证
  5. 修改 binlog 格式后重启 replicate
  6. 原表的存储引擎、外键、触发器校检
  7. 行数预估
  8. 初始化 stream 链接,增加 binlog 监听
  9. 初始化 applier 链接
  10. 创建 ghosttable 和 changelogtable
  11. 并发执行 row、copy 和 binlog copy
  12. CUT OVER

GH-OST 的特点:

  • 优点:可动态修改参数来实现“可快可慢”,对主机性能影响很低;无触发器的设计。
  • 缺点:不能对有外键关系及触发器的表进行 Online DDL;要求所连接的获取增量数据的 MySQL binlog 为 row 格式(会强制转换);若有同名但是字母大小写不同的表如:MYtable 和 myTable,则无法对这两张表进行修改。

Online DDL vs PT-OSC vs GH-OST

从执行 DDL 性能来说,Online DDL 是最好的。但是从执行 DDL 过程对 MySQL 服务的影响来说,使用 GH-OST 是最友好的。如果选择使用 PT-OSC,需要特别注意要在 MySQL 低峰时段执行。


参考文献

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html

https://cloud.tencent.com/developer/article/1005177

不带政治色彩的港乐有多好听 Java 对象详解

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×