MySQL训练营-onlineDDL问题

为了跟深刻理解一些DDL工具，本课程从如何自制展开。

自制online DDL-触发器

基础回顾

replace into

创建表&插入数据：

DROP TABLE IF EXISTS t;

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
unique KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

replace into t values(10,15,11);结果是什么?

REPLACE INTO 是 MySQL 中的一个 SQL 语句，它结合了 INSERT 和 UPDATE 的功能。当执行 REPLACE INTO 时：

如果表中不存在与主键或唯一索引冲突的记录，则执行插入操作
如果存在冲突，则先删除原有记录，然后插入新记录

所以这个场景会先删除两条冲突的记录，再插入新纪录。总共影响3条记录。

触发器

创建表t1结构与t一样：

CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
unique KEY `c` (`c`)
) ENGINE=InnoDB;

定义如下触发器：

CREATE TRIGGER `ex_tri` AFTER INSERT ON `test`.`t`
FOR EACH ROW
REPLACE INTO `test`.`t1` (id,c,d) VALUES (NEW.`id`, NEW.c, NEW.d);

触发条件：每当向 test.t 表插入一条新记录时
执行操作：将新插入的数据（通过 NEW 伪记录访问）以 REPLACE INTO 方式写入 test.t1 表
- 如果 t1 表中不存在相同 id 的记录，则插入新记录
- 如果已存在相同 id 的记录，则先删除原有记录再插入新记录
数据流转：将 t 表新插入行的 id、c 和 d 列的值复制到 t1 表的对应列

执行以下sql，事务未提交新会话能查询t1的结果是什么?replace into是否触发触发器？

begin;
replace into t values(7,7,7);

空表，会触发。

replace into t values(7,7,7);

存在一条数据。

如果在执行上面replace into语句时，t1表已经被锁，上述语句能否执行成功？

不能，是同一个事务。

insert ... select

insert into t1 select * from t limit 2;语句会加哪些锁？

t1写锁、t加读锁

验证，一个会话执行，不提交：

begin;

insert into t1 select * from t limit 2;

另一个会话执行：

select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,index_name,OBJECT_INSTANCE_BEGIN,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;

对t1表会加意向写锁。对t表加意向读锁，对t表中的两条数据加读锁。

RR 可重复读隔离级别

begin;
select * from t1;

重复读t1表，就算被其他事物更新，该事务每次读出来的结果是相同的。类似与创建了一个快照，后面都能从快照中读取同样的值。

自制 online DDL V1

若要给t添加索引：

create table t1 like t;
alter table t1 add index(c);
insert into t1 select * from t;
交换表名 : rename table t to t_old, t1 to t;

问题1:锁表时间过长，上面其实就是copy算法，因为insert into t1 select * from t语句会对t表加读锁。

解法: 分段

问题2:insert 过程中有更新

解法: 触发器

自制 online DDL V2

create table t1 like t;
alter table t1 add index(c);

1. 创建触发器
insert --> replace
delete --> delete
update --> delete+replace

2. insert into t1 select * from t limit 1,10000;
insert into t1 select * from t limit 10000,20000;

问题：分页不准。limit可能因为delete、insert被打乱。

解法:start transaction with consistent snapshot

问题2:锁等待问题。insert into会对t1加写锁，对t的操作可能会因为触发器等待锁。

解法:log table

自制 online DDL V3

create table t1 like t;
alter table t1 add index(c);

1. 创建触发器
insert -->insert into log_table("replace...")
delete --> insert into log_table("delete...")
update --> insert("delete"); insert into("replace") 2. start ...

2. start ...

select * from t insert into t1

3. lock table t read;

select alter_log from log_tables apply alter_log

问题:锁表时间不可控

解法:递归增量

自制 online DDL V4

create table t1 like t;
alter table t1 add index(c);

1.创建触发器
insert -->insert into log_table("replace...")
delete --> insert into log_table("delete...")
update --> insert("delete"); insert into("replace") 

2. start ...
select * from t insert into t1

3. start ...
select aid, alter_log from log_table ; apply aler_log
delete from log_tables where id =$aid

4. repeat step 3

5. lock table t; repeat step 3;

不断读取alter_log，在t1表中回放。当数据量不多时，直接锁表，回放完剩下的数据。

pt-osc工具原理

问题:表上已经有trigger时，不能使用该方案

解法:换binlog方案

自制 online DDL V5

create table t1 like t;
alter table t1 add index(c);

1. start ... // 启动事务
select * from t insert into t1

select语句后，在t上面的操作，记录到binlog，再select语句执行后回放binlog。

问题：阻塞t表时间过长

gh-ost工具方案

为解决锁表时间过长问题，可以借鉴V4版本，分段复制。

优缺点分析

1. MySQL原生INPLACE方案

优点：

原生支持：无需额外工具，直接使用ALTER TABLE语法
锁时间较短：对于支持INPLACE的操作，锁表时间相对较短
资源消耗低：不需要创建临时表，磁盘空间占用较少
操作简单：单条SQL语句即可完成

缺点：

仍会锁表：虽然时间较短，但在大表上仍可能导致业务阻塞
不支持所有操作：不是所有ALTER操作都支持INPLACE方式
无进度监控：操作进度难以监控，无法预估完成时间
失败风险：操作失败可能需要回滚，对大表来说代价高

2. pt-online-schema-change (pt-osc)

优点：

真正在线：通过触发器实现，基本不影响业务读写
进度可控：可以监控进度，暂停和恢复操作
成熟稳定：Percona官方工具，经过大量生产环境验证
灵活配置：可以控制复制延迟、负载等参数

缺点：

触发器开销：触发器机制会给数据库带来额外负载
需要额外空间：需要创建完整的临时表
主键要求：表必须有主键或唯一索引
外键限制：对有外键约束的表处理复杂

3. gh-ost (GitHub's Online Schema Migration)

优点：

无触发器：通过binlog实现，避免了触发器性能问题
更安全：可动态暂停、限流，降低对生产环境影响
可测试：支持先在从库测试，再在主库执行
实时监控：提供详细的进度和性能指标
灵活切换：支持多种切换策略(自动/手动)

缺点：

配置复杂：参数较多，需要更细致的调优
binlog要求：需要开启ROW格式的binlog
学习成本：比pt-osc更复杂，需要更多学习
资源消耗：需要处理binlog，可能消耗较多资源

综合比较

特性	MySQL INPLACE	pt-osc	gh-ost
锁表时间	短	极短(仅切换时)	极短(仅切换时)
性能影响	中等	较高(触发器)	较低
空间需求	低	高(完整副本)	高(完整副本)
进度监控	无	有	详细
操作中断	困难	可暂停	可暂停
外键支持	是	有限	有限
主键要求	无	需要	需要
适用场景	小表/允许短锁	常规大表	超大表/高负载

选择建议：

对小表或允许短时间锁表的场景，优先考虑INPLACE
对常规大表，pt-osc是成熟稳定的选择
对超大表或高负载环境，gh-ost更适合
对外键复杂的表，可能需要特殊处理或选择INPLACE

自制 online DDL V6

以上问题都存在拷贝时间太久的问题

create table t1 like t;
alter table t1 discard tablespace;
flush table t for export;
//cp t.ibd t1.ibd
unlock tables
alter table t1 import tablespace;

这组 SQL 语句的目的是 快速复制一个 InnoDB 表的数据（从表 t 到表 t1），利用了 MySQL 的 表空间传输（Transportable Tablespace） 技术。以下是逐步解析：

1. CREATE TABLE t1 LIKE t;

作用：创建一个与表 t 结构完全相同 的空表 t1（包括列定义、索引、约束等，但不包含数据）。
关键点：
- LIKE 语法会复制原表的 CREATE TABLE 定义，但不会复制数据。
- 此操作是原子的，不会阻塞原表 t 的读写。

2. ALTER TABLE t1 DISCARD TABLESPACE;

作用：删除表 t1 的表空间文件（.ibd 文件），使 t1 变为一个“空壳”。
关键点：
- 此操作会物理删除 t1.ibd 文件（表数据文件），但保留表结构（.frm 或数据字典中的元数据）。
- 执行后，t1 会处于不可用状态（查询会报错），直到导入新的表空间。

3. FLUSH TABLE t FOR EXPORT;

作用：锁定表 t 并生成一个临时的表空间配置文件（.cfg 文件），准备导出数据。
关键点：
- 此操作会：
  1. 阻塞表 t 的所有写入（DML 操作），但允许读取（除非有其他锁冲突）。
  2. 在磁盘上生成 t.cfg 文件（包含表空间元数据，如列映射、索引信息等）。
- 必须尽快执行后续步骤，否则会长时间阻塞原表 t 的写入！

4. cp t.ibd t1.ibd（操作系统命令）

作用：将表 t 的数据文件（t.ibd）复制到表 t1 的数据文件（t1.ibd）。
关键点：
- 需确保复制的 .ibd 文件与 .cfg 文件匹配（FLUSH FOR EXPORT 后生成的 .cfg 文件是必需的）。
- 文件权限需正确（MySQL 用户需有读写权限）。

5. UNLOCK TABLES;

作用：释放表 t 的锁，恢复其正常读写。
关键点：
- 必须在 FLUSH FOR EXPORT 后执行，否则表 t 会一直处于锁定状态。
- 执行后，t.cfg 文件会被自动删除。

6. ALTER TABLE t1 IMPORT TABLESPACE;

作用：将复制的 t1.ibd 文件导入到表 t1 中，完成数据加载。
关键点：
- MySQL 会校验 .ibd 文件与表 t1 的结构是否兼容（如列定义、索引等）。
- 导入后，表 t1 的数据将与表 t 完全一致（截至 FLUSH FOR EXPORT 时的快照）。

N个分表拆成2N怎么做 - 分区表

分区表(Partitioned Table)是MySQL提供的一种将大表数据物理分割存储的技术，通过将一张表的数据按照特定规则分布到不同的物理子表中(称为分区)，从而提升查询性能和管理效率。

如：

PARTITION BY list(shopid%32)
(
PARTITION p0 VALUES IN (0),
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2),
...
PARTITION p31 VALUES IN (31)
);

方案：

@slave
set sql_log_bin =off;stop slave;
create orders_new partition=64;
insert into orders_new select from orders swapname of orders and orders_new start slave

这段命令是在 MySQL 从库（Slave）上执行的分区表扩容操作，目的是将 orders 表从当前分区数扩展到 64 个分区，同时避免这些操作被复制到其他实例。以下是逐步解析：

1. `set sql_log_bin = off;`

作用：临时关闭当前会话的二进制日志（binlog）记录。
为什么需要：
- 在从库上执行 DDL/DML 操作时，如果不关闭 sql_log_bin，这些操作会被记录到 binlog 并可能传播到其他从库或级联复制环境，导致数据不一致。
- 这里仅影响当前会话，不影响全局 binlog 记录。

2. `stop slave;`

作用：停止从库的复制线程（SQL 线程和 IO 线程）。
为什么需要：
- 防止在数据迁移过程中，主库的变更被应用到从库，导致数据冲突。
- 确保操作期间从库数据处于静态状态。

3. `create orders_new partition=64;`

作用：创建一个新的分区表 orders_new，并指定分区数为 64。

实际 SQL 可能是：

CREATE TABLE orders_new (
    -- 原表结构
    ...
) PARTITION BY HASH(id)  -- 假设按 id 列 HASH 分区
PARTITIONS 64;

为什么需要：
- 原表 orders 可能是 32 个分区，现在要扩容到 64 个分区。
- 直接 ALTER TABLE 修改分区数可能锁表较久，而创建新表再迁移数据对业务影响更小。

**4. `insert into orders_new select * from orders;`**

作用：将原表 orders 的所有数据插入到新表 orders_new。
为什么需要：
- 新表已经按 64 个分区设计，需要将原数据完整迁移过去。
- 如果数据量很大，此操作可能较慢，可以分批插入（如 LIMIT 分页）减少长事务风险。

5. `swap names of orders and orders_new;`

作用：交换 orders 和 orders_new 的表名，使新表生效。

实际 SQL 可能是：

RENAME TABLE orders TO orders_old, orders_new TO orders;

为什么需要：
- 原子性操作，瞬间完成表切换，业务几乎无感知。
- 原表 orders 被重命名为 orders_old，新表 orders_new 变成 orders。

6. `start slave;`

作用：重新启动从库的复制线程。
为什么需要：
- 恢复从库与主库的同步，继续接收主库的变更。
- 由于之前关闭了 sql_log_bin，从库不会将本地执行的 DDL/DML 传播出去。

posted @ 2025-04-20 16:38 余为民同志阅读(44) 评论(0) 收藏举报

刷新页面返回顶部

cnyuyang

余为民同志

MySQL训练营-onlineDDL问题

自制online DDL-触发器

基础回顾

replace into

触发器

insert ... select

RR 可重复读隔离级别

自制 online DDL V1

自制 online DDL V2

自制 online DDL V3

自制 online DDL V4

自制 online DDL V5

优缺点分析

1. MySQL原生INPLACE方案

2. pt-online-schema-change (pt-osc)

3. gh-ost (GitHub's Online Schema Migration)

综合比较

自制 online DDL V6

N个分表拆成2N怎么做 - 分区表

1. `set sql_log_bin = off;`

2. `stop slave;`

3. `create orders_new partition=64;`

**4. `insert into orders_new select * from orders;`**

5. `swap names of orders and orders_new;`

6. `start slave;`

公告

cnyuyang

余为民同志

MySQL训练营-onlineDDL问题

自制online DDL-触发器

基础回顾

replace into

触发器

insert ... select

RR 可重复读隔离级别

自制 online DDL V1

自制 online DDL V2

自制 online DDL V3

自制 online DDL V4

自制 online DDL V5

优缺点分析

1. MySQL原生INPLACE方案

2. pt-online-schema-change (pt-osc)

3. gh-ost (GitHub's Online Schema Migration)

综合比较

自制 online DDL V6

N个分表拆成2N怎么做 - 分区表

1. set sql_log_bin = off;

2. stop slave;

3. create orders_new partition=64;

4. insert into orders_new select * from orders;

5. swap names of orders and orders_new;

6. start slave;

公告

1. `set sql_log_bin = off;`

2. `stop slave;`

3. `create orders_new partition=64;`

**4. `insert into orders_new select * from orders;`**

5. `swap names of orders and orders_new;`

6. `start slave;`