MySQL进阶五之海量数据下修改表结构事故复盘[上]
事故场景
我们开发的阅读快应用在一些投放渠道做推广，用户点击广告素材会快速进入快应用，如果用户第一次打开快应用，则需要为用户自动注册为新用户。但目前的User表数据接近1亿条记录，创建新用户涉及好几张用户基本信息表，导致用户体验不佳。
目前设计方案是使用雪花算法快速生成用户ID，并返回给快应用。采用MQ的削峰填谷异步保存用户信息数据提高用户体验，但我们的User表主键类型是int , 需要升级为 BIGINT(20)。我们考虑到修改字段类型会锁表，在凌晨1点开始执行相关升级SQL语句。
ALTER TABLE `user`
CHANGE COLUMN `id` `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID' ;执行以上SQL，总耗时60多分钟，期间User表数据无法更新，且造成长时间主从延迟
■【腾讯云可观测平台告警】
您好！您账号（账号ID: ********，昵称: ********）的腾讯云可观测平台告警持续触发
告警内容: 云数据库-MySQL-主机监控 | 主从延迟时间 > 10 秒
当前数据: 3762 秒 (主从延迟时间)
告警对象: ********(实例名:*****-prod_ro_1,IP地址:127.0.0.45:3306),实例类型:ro
项目|地域: 默认项目 | 北京
告警策略: mysql数据库监控报警
触发时间: 2024-02-27 02:17:00 (UTC+08:00)
持续时间: 1小时0分钟
您可以登录腾讯云可观测平台控制台查看告警详情，或在腾讯云助手小程序查看告警详情
事故发生后，我们进行了复盘，并查询相关资料，了解到 MySQL数据库 Online DDL 目前主流的有三种工具：
- 原生 Online DDL；
- pt-osc(online-schema-change)，
- gh-ost
本文主要讲解 MySQL 原生 Online DDL的使用。
什么是Online DDL？
什么是Online DDL？在了解Online DDL之前，我们先来了解一下什么是DDL。说DDL，就不得不提一下它的另外两个兄弟：DML和DCL。
DDL：Data Definition Language，即数据定义语言，用来定义数据的语句。包括：新建、修改、删除等；相关的命令有：CREATE，ALTER，DROP等。
DML：Data Manipulation Language，即数据操作语言，即处理数据库中数据的操作就是DML，包括：选取，插入，更新，删除等；相关的命令有：SELECT,INSERT,UPDATE,DELETE，还有 LOCK TABLE。
DCL：Data Control Language，数据控制语言，用来定义数据访问控制的语句。例如我们平时的grant，revoke等。
我们可以认为：
- CREATE，ALTER ，DROP，TRUNCATE，定义相关的命令就是DDL;
- SELECT，INSERT，UPDATE，DELETE，操作处理数据的命令就是DML；
DDL、DML区别：
- DML操作是可以手动控制事务的开启、提交和回滚的。
- DDL操作是隐性提交的，不能rollback，一定要谨慎哦！
而Online DDL是指在线的数据定义语言，所谓的在线是指：数据表在不停止服务的情况下来执行我们的DDL语句，即表在应用DDL语句的时候，仍然可以提供读写的服务。
InnoDB and Online DDL
MySQL Online DDL 官方文档
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
在线DDL功能提供了对即时和就地表更改以及并发DML的支持。此功能的优点包括：
1、在繁忙的生产环境中提高了响应能力和可用性，在这种环境中，不会出现使表在几分钟或几小时内不可用。
2、对于就地操作，可以使用LOCK子句在DDL操作期间调整性能和并发性之间的平衡。
3、与表复制方法相比，磁盘空间使用率和I/O开销更少。
通常，您不需要做任何特殊的事情来启用在线DDL。默认情况下，MySQL会在允许的情况下立即或就地执行操作，并尽可能少地锁定。
ALTER TABLE tbl_name
ADD PRIMARY KEY (column),
ALGORITHM=INPLACE, LOCK=NONE;其中的ALGORITHM有如下选项
- INPLACE：替换：直接在原表上面执行DDL的操作。表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。
- COPY：复制：使用一种临时表的方式，克隆出一个临时表，在临时表上执行DDL，然后再把数据导入到临时表中，在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间，表不允许DML的操作。
- DEFAULT：默认方式，由MySQL自己选择，优先使用INPLACE的方式。
其中的LOCK有如下选项
- SHARE：共享锁，执行DDL的表可以读，但是不可以写。
- NONE：没有任何限制，执行DDL的表可读可写。
- EXCLUSIVE：排它锁，执行DDL的表不可以读，也不可以写。
- DEFAULT：默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT，那就是交给MySQL子句去决定锁还是不锁表。不建议使用，如果你确定你的DDL语句不会锁表，你可以不指定lock或者指定它的值为default，否则建议指定它的锁类型。
执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。
注意:在执行Online DDL之前，要在非业务高峰期去执行，并要确认待执行的表上面没有未提交的事务、锁等信息。
Copy和Inplace算法
Copy方式：
- 按照原表定义创建一个新的临时表；
- 对原表加写锁（禁止DML，允许select）；
- 在步骤1 建立的临时表执行 DDL；
- 将原表中的数据 copy 到临时表；
- 释放原表的写锁；
- 将原表删除，并将临时表重命名为原表。
- 从上可见，采用 copy 方式期间需要锁表，禁止DML，因此是非Online的。比如：删除主键、修改列类型、修改字符集，这些操作会导致行记录格式发生变化（无法通过全量 + 增量实现 Online）。
Inplace方式：
在原表上进行更改，不需要生成临时表，不需要进行数据copy的过程。根据是否行记录格式，又可分为两类：
- rebuild：需要重建表（重新组织聚簇索引）。比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等；
- no-rebuild：不需要重建表，只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。
对于 rebuild 方式实现 Online 是通过缓存 DDL 期间的 DML，待 DDL 完成之后，将 DML 应用到表上来实现的。例如，执行一个 alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下：
- 建立一个临时文件，扫描表 A 主键的所有数据页；
- 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
- 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
- 用临时文件替换表 A 的数据文件。
说明：
- 在 copy 数据到新表期间，在原表上是加的 MDL 读锁（允许 DML，禁止 DDL）；
- 在应用增量期间对原表加 MDL 写锁（禁止 DML 和 DDL）；
- 根据表 A 重建出来的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是”inplace”名称的来源。
使用Inplace方式执行的DDL，发生错误或被kill时，需要一定时间的回滚期，执行时间越长，回滚时间越长。
使用Copy方式执行的DDL，需要记录过程中的undo和redo日志，同时会消耗buffer pool的资源，效率较低，优点是可以快速停止。
不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以在（在线 DDL 操作) 中查看。
官网支持列表：
Operation | Instant | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
Adding a column | Yes* | Yes | No* | Yes* | Yes |
Dropping a column | Yes* | Yes | Yes | Yes | Yes |
Renaming a column | Yes* | Yes | No | Yes* | Yes |
Reordering columns | No | Yes | Yes | Yes | No |
Setting a column default value | Yes | Yes | No | Yes | Yes |
Changing the column data type | No | No | Yes | No | No |
Extending VARCHAR column size | No | Yes | No | Yes | Yes |
Dropping the column default value | Yes | Yes | No | Yes | Yes |
Changing the auto-increment value | No | Yes | No | Yes | No* |
Making a column NULL | No | Yes | Yes* | Yes | No |
Making a column NOT NULL | No | Yes* | Yes* | Yes | No |
Modifying the definition of an ENUM or SET column | Yes | Yes | No | Yes | Yes |
栗子
添加索引语法
ALTER TABLE `table_name`
ADD INDEX `index_name`(`a`,`b`),
ALGORITHM=INPLACE, LOCK=NONE;下面例子是给创意的记录表添加索引的例子[creative_date表数据是200W]，执行SQL
ALTER TABLE `creative_date`
ADD INDEX `idx_advertiserId_createTime` (`advertiser_id` ASC, `create_time` ASC),
ALGORITHM=INPLACE, LOCK=NONE;Action | Response | Duration/Fetch Time |
执行SQL | row(s) affected Records: 0 Duplicates: 0 Warnings: 0 | 6.165 sec |
- QueryOK,2671168 rows affected(1min35.54 sec)：这样的语句表示：执行速度明显慢很多，并且表中的数据被重新构建。
- QueryOK,0rows affected(6.165 sec)：执行速度有点慢，但是没有copy表中的数据到临时表中，也就是没有用到临时表。
- QueryOK,0rows affected(0.07 sec)：执行速度很快，没有影响到表中已经存在的数据。
上述的3种情况中，第一种是最不理想的，最后一种是最理想的。如果是上诉情况第1种情况，我们就需要好好的衡量一下我们的Online DDL语句，指定一个可行的执行计划，避免主从不同步的问题发现。此时可以考虑在session级别关闭binlog日志的记录功能，然后在每一个数据库节点上都执行一遍DDL语句，避免用binlog去做数据同步而产生的主从延迟。