MySQL DDL 变更表结构推荐方式及对应风险

推荐

  • 增加字段/索引直接执行SQL
    • 原因：加字段/索引支持 online ddl，且较 pt-osc 不用分段加锁，在能接受主从延迟较大的场景下，此方式对业务影响更小。
    • 不适用场景：
      • 不接受主从延迟：主库执行后，从库会开始执行ddl，从库执行ddl期间会存在主从延迟(即DDL执行多久，主从会延迟多久)
      • 表变更极多，DDL期间表上的事务链会很长，严重影响性能
  • 变更字段长度超过临界值、变更字段类型，使用pt-osc(pt-online-schema-change)工具
    • 原因：MySQL更改类型和更改字段长度超过临界值时，不支持 online ddl，整个ddl变更期间会导致表不可读写，相比较而言 pt-osc 分段加锁影响更小。
    • 不适用场景：
      • 变更期间会分段加锁(insert into 新表 select * from 旧表 limit 条数)，如果表上锁冲突严重影响业务，需考虑其他方式实现或减少工具chunksize

DDL工作流程及风险点

直接执行SQL

  • MySQL 在表上加 metadata lock(表上数据不可读写)，拿到后创建临时新表。新表创建后，锁降级，表恢复可读可写。
    • 说明：正常境况下，开始执行DDL时，MySQL会拿一下表上的表级锁，瞬间降级，恢复可读可写
    • 风险点：
      • 如果这个表上有慢SQL或者事务，导致DDL拿不到锁，DDL语句会排队，processlist 内语句状态为 Waiting for table metadata lock
        • 事务拿锁补充说明：事务锁持有的锁会在语句开始执行时持有，事务提交时释放。如果表上有未提交的事务，即使事务内没有正在执行的sql，也会导致DDL无法拿表锁导致锁表。
      • 而正在排队的DDL语句又会导致表上的后续查询、变更操作也处于Waiting for table metadata lock状态，导致表处于不可读、不可写的锁表状态
    • 推荐处理方式：
      • 开始执行前使用SQL：SELECT * FROM information_schema.innodb_trx; 查询正在执行的事务，规避长事务的影响
      • 开始执行前查看 processlist：select * from information_schema.processlist where COMMAND <> ‘Sleep’ and INFO is not NULL order by TIME ; 规避慢SQL的影响
      • 查询和ddl语句执行时仍存在时间差，无法完全规避慢SQL和长事务的影响，开始执行后关注 processlist，如果出现时锁的情况，及时kill导致ddl无法拿锁的慢sql或ddl语句本身。
      • 如果多次尝试无法顺利拿锁，联系负责人处理完慢sql或长事务后再考虑执行DDL
  • 锁降级后，MySQL开始拷贝数据进新表，拷贝期间磁盘IO使用率会比较高(100%)
    • 拷贝进度可通过查看对应库下 ibd 文件大小确定
      • MySQL5.7较老版本由于工作机制原因，近比较ibd文件大小会预估不准，大小一致后仍需执行较长时间，较新版本无此问题
      • 新表临时文件名字多为随机数字后缀，如*#sql-ib8059-1773712601.ibd*，可在执行一段时间后，查看对应库下超过1G、名字为随机数字，且大小快速增长的ibd文件确定。
  • 数据拷贝的同时，MySQL会暂存这段时间表上的变更，数据拷贝完成之后，会再次拿表锁，应用拷贝数据期间的变更，替换ibd文件
    • 风险点：
      • 此时，MySQL 要再次拿表级锁，如果有慢SQL和长事务的影响，也会导致出现类似锁表的情况，原因同上。
      • 且此时通过sql执行时间排序，无法确定具体是哪个或者哪些SQL/长事务导致的，kill阻塞的SQL/事务需要时间较长，无法避免此问题。
      • SQL DDL执行时间较长，无法确定多久会再次拿锁，如果会话刷新不及时，会出现人工处理锁不及时的情况
    • 推荐处理方式：
      • 重点平台变更表尽量多刷新会话，关注执行情况，并和项目负责人强调风险
      • 遇到锁表，条件允许的情况只能多 kill 当前执行慢的会话，且要避免误操作 kill DDL 语句

pt-osc

  • 创建一个新的临时表，比如原表明为 t1，则创建一个新表 _t1_new ，然后在 _t1_new 表上执行要执行的DDL语句
  • 创建 增/删/改 三个触发器，t1 表有 增/删/改语句时，会同步在新表上执行(update触发器遇到新表上没原始数据时，会写入新数据)
    • 风险点
      • 触发器会引入额外开销，因为原始表上发生的变更操作，现在会执行两次
      • 表上如果有触发器/长事务，会阻塞创建事务的语句，情况和直接执行DDL时一样
    • 推荐处理方式
      • 针对慢sql和长事务可能导致的问题，解决方案和直接执行DDL语句时一样
      • 针对性能开销的问题，负载较高的平台或业务敏感的平台，尽量错开业务高峰期
  • 执行 insert _t1_new select * from t1 where id >= ? and id <= ? 逐步将旧表内的数据写入新表
    • 风险点
      • insert into select 会对旧表查询到的部分加锁，如果和业务的sql存在锁冲突，可能影响业务
    • 推荐处理方式
      • 通过 –chunk-size 参数设置单个语句写入较少的数据(默认1000，可设置为500)，但通知也会延长任务的执行时间，但也无法完全避免锁冲突
  • 待数据完全转储完后，新旧表交换表名(RENAME TABLE t1 to t1_old; RENAME TABLE _t1_new to t1; DROP TABLE t1;)，删除触发器。
    • 风险点：和直接执行SQL结束时风险点一样，无法拿锁则RENAME TABLE语句会排队导致表无法读写
    • 推荐处理方式：和直接执行SQL一样

pt-osc

安装

参数说明

  • –max-load “Threads_running=25”：执行时，查询 Threads_running ，如果超过指定值，则 waiting ，等降低后在继续执行
    • Threads_running 查询：show global status like ‘Threads_running’
  • –critical-load：默认 50，Threads_running 超过 50 时会自动中止
    • –critical-load “Threads_running=125”
  • –no-drop-old-table：执行结束后，不删除旧表
  • –no-drop-triggers：拷贝结束后，不删除旧表触发器
  • –no-swap-tables：拷贝结束后，不切换新旧表(结合 –no-drop-triggers –no-drop-old-table 可拷贝生成新表并保持数据实时同步)
  • –new-table-name：指定生成的临时新表的表名，需为不存在的表，否则无法继续
  • –charset=utf8：指定使用 utf8 编码

主从相关

  • –check-interval：两次检验主从延迟间的间隔
  • –max-lag：容忍的主从延迟时间
  • –recursion-method=none：配置监控主从延迟的方式
    • processlist：通过 show processlist 获取 slave 节点监控延迟，适用于使用 3306 端口的情况，实测 3.3.0+ 版本会报错，需要将从库信息维护在库里
    • hosts：通过SHOW SLAVE HOSTS获取从库，实测获取不到 IP，仅能获取端口
    • DSN：通过 DSN 指定从库节点
  • –check-slave-lag DSN：配置连接单个从库监控延迟

使用

  • 常用变更语句关键字
1
2
3
4

-- 更改字段编码
MODIFY COLUMN card_number varchar(100) COLLATE utf8_general_ci,CONVERT TO CHARSET utf8mb4
-- 重建主键，整理表空间
engine=InnoDB
  • 例：
1
2
3
4
5

pt-online-schema-change --alter="engine=InnoDB" \
--host=127.0.0.1 --user=xxx --port=3306 --ask-pass \
--charset=utf8 --max-load="Threads_connected=500,Threads_running=50" \
--critical-load="Threads_connected=800,Threads_running=100" \
--execute D=test,t=mall_orders

主从场景下的使用

  • 3.3.0 以上版本(不包含 3.3.0)，使用自动获取 slave 节点时存在问题，会出现如下报错，需通过参数指定 slave 节点连接方式
1
2

`test`.`mall_orders` was not altered.
        (in cleanup) 2023-06-15T17:16:41 Error copying rows from `test`.`mall_orders` to `test`.`_mall_orders_new`: Use of uninitialized value in string eq at /usr/bin/pt-online-schema-change line 4321.

监控单个从库延迟

  • 通过 –check-slave-lag 指定
1

--check-slave-lag h=192.168.212.102,P=3306,u=xxx,p=123456
例：
1
2
3
4
5
6
7

pt-online-schema-change --alter="MODIFY COLUMN update_time varchar(900)  AFTER creation_time" \
--host=127.0.0.1 --user=xxx --port=3306 --ask-pass \
--charset=utf8 --check-interval 5 --max-lag 2  \
--max-load="Threads_connected=500,Threads_running=50" \
--critical-load="Threads_connected=800,Threads_running=100" \
--execute D=test,t=mall_orders \
--check-slave-lag h=192.168.212.102,P=3306,u=xxx,p=123456

监控多个从库延迟(监控单个从库也推荐此方式)

  • 通过 –recursion-method 参数的 DSN 实现

  • 使用此配置需要先创建一个表，然后 在参数中指定从这个表中读取从节点的 DSN 信息

  • 建表语句

1
2
3
4
5
6

CREATE TABLE `slavedsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
例：
  • 创建库表
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 创建库
CREATE DATABASE `percona` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
-- 切换库
use percona;
-- 创建表
CREATE TABLE `slavedsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
  • 写入从节点信息
1
2

insert into slavedsns(dsn) values("h=192.168.212.102,u=xxx,p=123456,P=3306");
insert into slavedsns(dsn) values("h=192.168.212.103,u=xxx,p=123456,P=3306");
  • 更改表结构
1
2
3
4
5
6
7

pt-online-schema-change --alter="MODIFY COLUMN update_time varchar(900)  AFTER creation_time" \
--host=127.0.0.1 --user=xxx --port=3306 --ask-pass \
--charset=utf8 --check-interval 5 --max-lag 2  \
--max-load="Threads_connected=500,Threads_running=50" \
--critical-load="Threads_connected=800,Threads_running=100" \
--execute D=test,t=mall_orders \
--recursion-method dsn=D=percona,t=slavedsns