文章目录

一、原生DDL、Online DDL
- 1.1 Online ddl操作原理
- 1.2 DDL操作的两个参数
- 1.3 Online DDL 操作空间存储的要求
二、pt-online-schema-change
- 2.1 pt-osc的大致流程：
- 2.2 pt-osc的特点
- 2.3 使用pt-osc的约束
- 2.4 操作示例
三、gh-ost
- 3.1 gh-ost的三种模式
- 3.2 gh-ost的特点
- 3.3 使用gh-ost的约束

线上环境的大表DDL操作一般都是比较让人头疼的，对于大表的DDL我们主要担心的风险点主要有：

对当前数据库的资源消耗（CPU、IO、磁盘）的影响
DDL执行期间，是否会锁表，是否会阻塞业务正常DML请求的影响
在主从复制架构中，对从库的复制延迟的影响

作为一名DBA，对于DDL执行消耗的评估，选择最佳方式执行DDL就是非常重要了，以下主要讲解一些线上环境执行DDL的方法和工具

一、原生DDL、Online DDL

从MySQL5.6开始支持Online DDL，Online DDL相对于普通的DDL操作在对数据库并发性支持以及锁、IO、空间资源消耗上都有较大的优化。但是，并不是所有类型的DDL都是执行Online的。我们在执行一个DDL变更时，为减少为生产环境的影响，必须要清楚该操作的基本属性以及其带来的资源消耗。

DDL操作带来的资源消耗：

对业务并发的影响，DDL操作是否会影响到正常业务的DML请求
对数据库本身性能的影响，DDL操作执行耗时以及带来的IO资源消耗
对数据库服务器空间存储带来的影响，服务器剩余空间是否满足执行DDL语句
对数据库架构中从库复制延迟的影响，

1.1 Online ddl操作原理

Online DDL操作可以分为以下三个阶段：

初始化阶段

该阶段会判断DDL语句执行需要的算法以及锁模式，尽可能减少DDL执行期间对数据库并发和资源的消耗，该阶段需要持有变更表的MDL读锁/

执行阶段

该阶段准备执行DDL语句，也是DDL操作中最耗时的一个阶段，需要将MDL读锁提升为MDL写锁，若DDL操作支持并发DML可快速将MDL写锁降级为MDL读锁，开始进行表数据重建等操作。

提交表定义阶段

该阶段是表定义提交阶段，会将MDL读锁再次升级为MDL写锁，变更表结构定义，完成DDL操作，这是一个比较快的操作。

1.2 DDL操作的两个参数

1、ALGORITHM（算法）

Algorithm主要有3中参数选择：Inplace、Copy、default，Inplace > copy DDL操作执行消耗很大程度上取决于该操作是否可使用inplace策略，是否需要重建表

Copy：MySQL5.6之前默认使用的方式，DDL操作会在Server层创建一个临时表用于数据拷贝，当拷贝结束后最后通过rename进行替换，资源消耗比较巨大。
Inplace：MySQL5.6引入online DDL后默认优先使用的方式，inplace算法下又分为是否需要重建表两种，对于重建表操作，在引擎层内部创建临时表来完成DDL操作，不涉及Server层的数据拷贝；对于非重建表操作，基本上可直接在其表元数据信息进行变更即可，整体来讲inplace算法的执行效率相对于copy算法都是要好很多的。
Default：默认我们不显式添加algorithm或者指定algorithm=default时，MySQL默认评估DDL操作执行，选择最佳算法执行

2、Lock（锁）

lock是影响数据库并发性的关键指标，主要有以下几种情况：

LOCK=NONE ：DDL执行期间允许并发查询以及并发DML
LOCK=SHARED ：DDL执行期间允许并发查询，不允许并发DML
LOCK=DEFAULT ：DDL执行期间，尽可能选择最佳的方式执行DDL，避免对数据库业务并发的影响
LOCK=EXCLUSIVE ：DDL执行期间禁止并发查询以及并发DML

1.3 Online DDL 操作空间存储的要求

临时日志文件空间消耗

在Online DDL执行期间允许并发DML操作，这些并发的DML操作会记录在临时的日志空间中，该空间按需申请，没吃申请单位由参数innodb_sort_buffer_size 控制，最大上限由参数innodb_online_alter_log_max_size 控制。若执行DDL期间临时日志文件空间消耗超过innodb_online_alter_log_max_size 参数，则DDL操作执行失败并回滚期间仍未提交的事物。

临时排序文件空间消耗

Online DDL执行期间重建表的操作是需要使用到临时排序空间的，，

临时中间表文件空间消耗

二、pt-online-schema-change

2.1 pt-osc的大致流程：

创建_tblname_new表，其表结构与DDL原表（tblname）表结构一致
对_tblname_new表进行DDL变更操作
在原表tblname上分别针对Insert/Update/Delete操作创建触发器
将原表中的历史数据拷贝至_tblname_new表，同时通过触发器在拷贝数据期间原表发生过的DML操作映射到_tblname_new表上
如果原表有外键约束，处理外键约束
将原表tblname重命名为_tblname_old，将_tblname_new重命名为tblname表
删除_tblname_old表，DDL变更结束

2.2 pt-osc的特点

通过数据拷贝与触发器来实现DDL变更操作
可控制进行DDL操作期间最大负载与slave最大延迟
DDL操作需要的空间消耗包括：表数据空间一倍多的空间消耗+binlog日志空间消耗

2.3 使用pt-osc的约束

表需要有主键，因为pt-osc进行事物拆分时使用了“force index(primary)”
避免原表上存在触发器，以防止与pt工具产生干扰
在使用pt-osc工具进行变更期间，容易因为触发器与正常的业务事物产生死锁

2.4 操作示例

# pt-online-schema-change -h127.0.0.1 -P3306 -uroot -p'123' --recursion-method=none --no-version-check --charset=utf8 --dry-run --print --alter="add index idx_id1(id1)" D=db1,t=t5
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `db1`.`t5` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `db1`.`_t5_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id1` int(11) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57622883 DEFAULT CHARSET=utf8mb4
Created new table db1._t5_new OK.
Altering new table...
ALTER TABLE `db1`.`_t5_new` add index idx_id1(id1)
Altered `db1`.`_t5_new` OK.
Not creating triggers because this is a dry run.
-----------------------------------------------------------
Skipped trigger creation:
Event : DELETE
Name  : pt_osc_db1_t5_del
SQL   : CREATE TRIGGER `pt_osc_db1_t5_del` AFTER DELETE ON `db1`.`t5` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `db1`.`_t5_new` WHERE `db1`.`_t5_new`.`id` <=> OLD.`id`; END
Suffix: del
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Skipped trigger creation:
Event : UPDATE
Name  : pt_osc_db1_t5_upd
SQL   : CREATE TRIGGER `pt_osc_db1_t5_upd` AFTER UPDATE ON `db1`.`t5` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `db1`.`_t5_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `db1`.`_t5_new`.`id` <=> OLD.`id`; REPLACE INTO `db1`.`_t5_new` (`id`, `id1`, `name`) VALUES (NEW.`id`, NEW.`id1`, NEW.`name`); END
Suffix: upd
Time  : AFTER
-----------------------------------------------------------
-----------------------------------------------------------
Skipped trigger creation:
Event : INSERT
Name  : pt_osc_db1_t5_ins
SQL   : CREATE TRIGGER `pt_osc_db1_t5_ins` AFTER INSERT ON `db1`.`t5` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `db1`.`_t5_new` (`id`, `id1`, `name`) VALUES (NEW.`id`, NEW.`id1`, NEW.`name`);END
Suffix: ins
Time  : AFTER
-----------------------------------------------------------
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `db1`.`_t5_new` (`id`, `id1`, `name`) SELECT `id`, `id1`, `name` FROM `db1`.`t5` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 1008 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`t5` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t5_del`
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t5_upd`
DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_t5_ins`
2021-06-26T17:45:21 Dropping new table...
DROP TABLE IF EXISTS `db1`.`_t5_new`;
2021-06-26T17:45:21 Dropped new table OK.
Dry run complete.  `db1`.`t5` was not altered.

三、gh-ost

3.1 gh-ost的三种模式

gh-ost工具主要有三种模式：connect to replica 、 connect to master 、 migrate/test on replica，前两种模式的区别是binlog的读取是从主库还是从库进行读取，第三种模式主要是用来做一定的测试，以connect to replica为例，其DDL操作流程主要如下

根据原表结构_tblname_gho，对_tblname_gho表进行DDL变更
gh-ost会链接master数据库实例用于历史数据拷贝，
连接slave数据库实例进行增量数据的binlog读取并应用
cut-over是最后一步，锁住主库的源表，等待binlog 应用完毕，然后替换gh-ost表为源表。

3.2 gh-ost的特点

不依赖触发器而是通过读取数据库binlog进行日志回放
单线程回放binlog来替换触发器，所以增量DML回放效率不如触发器
可控制进行DDL操作期间最大负载与slave最大延迟
DDL操作需要的空间消耗包括：表数据空间一倍多的空间消耗+binlog日志空间消耗
对于DDL操作的灵活度掌控，可暂停，可动态修改参数；DBA可以根据执行情况来快速调整预设的参数，可快可慢，实现DDL操作性能和对业务影响的平衡；
更为稳健的切表控制：将-cut-over-lock-timeout-seconds和-default-retries 配合使用，可以对切表进行灵活的控制。避免pt-osc切表异常导致对业务造成严重影响；

3.3 使用gh-ost的约束

# gh-ost --ok-to-drop-table --host="127.0.0.1" --port=3306 --user="root" --password="123" --database="db1" --table="t1" --verbose --alter="add index idx_id1(id1)" --panic-flag-file=/tmp/t1.ghost.panic.flag --allow-on-master  --throttle-flag-file /tmp/t1.log --execute
2021-06-26 19:59:57 INFO starting gh-ost 1.1.2
2021-06-26 19:59:57 INFO Migrating `db1`.`t1`
2021-06-26 19:59:57 INFO inspector connection validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO User has ALL privileges
2021-06-26 19:59:57 INFO binary logs validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO Restarting replication on 127.0.0.1:3306 to make sure binlog settings apply to replication thread
2021-06-26 19:59:57 INFO Inspector initiated on mdw:3306, version 5.7.23-log
2021-06-26 19:59:57 INFO Table found. Engine=InnoDB
2021-06-26 19:59:57 INFO Estimated number of rows via EXPLAIN: 23
2021-06-26 19:59:57 INFO Recursively searching for replication master
2021-06-26 19:59:57 INFO Master found to be mdw:3306
2021-06-26 19:59:57 INFO log_slave_updates validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO streamer connection validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO Connecting binlog streamer at mysql-bin.000012:328158254
[2021/06/26 19:59:57] [info] binlogsyncer.go:133 create BinlogSyncer with config {99999 mysql 127.0.0.1 3306 root    false false <nil> false UTC true 0 0s 0s 0 false}
[2021/06/26 19:59:57] [info] binlogsyncer.go:354 begin to sync binlog from position (mysql-bin.000012, 328158254)
[2021/06/26 19:59:57] [info] binlogsyncer.go:203 register slave for master server 127.0.0.1:3306
2021-06-26 19:59:57 INFO applier connection validated on 127.0.0.1:3306
[2021/06/26 19:59:57] [info] binlogsyncer.go:723 rotate to (mysql-bin.000012, 328158254)
2021-06-26 19:59:57 INFO rotate to next log from mysql-bin.000012:0 to mysql-bin.000012
2021-06-26 19:59:57 INFO applier connection validated on 127.0.0.1:3306
2021-06-26 19:59:57 INFO will use time_zone='+08:00' on applier
2021-06-26 19:59:57 INFO Examining table structure on applier
2021-06-26 19:59:57 INFO Applier initiated on mdw:3306, version 5.7.23-log
2021-06-26 19:59:57 INFO Dropping table `db1`.`_t1_ghc`
2021-06-26 19:59:57 INFO Table dropped
2021-06-26 19:59:57 INFO Creating changelog table `db1`.`_t1_ghc`
2021-06-26 19:59:57 INFO Changelog table created
2021-06-26 19:59:57 INFO Creating ghost table `db1`.`_t1_gho`
2021-06-26 19:59:57 INFO Ghost table created
2021-06-26 19:59:57 INFO Altering ghost table `db1`.`_t1_gho`
2021-06-26 19:59:57 INFO Ghost table altered
2021-06-26 19:59:57 INFO Altering ghost table AUTO_INCREMENT value `db1`.`_t1_gho`
2021-06-26 19:59:57 INFO Ghost table AUTO_INCREMENT altered
2021-06-26 19:59:57 INFO Intercepted changelog state GhostTableMigrated
2021-06-26 19:59:57 INFO Waiting for ghost table to be migrated. Current lag is 0s
2021-06-26 19:59:57 INFO Handled changelog state GhostTableMigrated
2021-06-26 19:59:57 INFO Chosen shared unique key is PRIMARY
2021-06-26 19:59:57 INFO Shared columns are id,id1
2021-06-26 19:59:57 INFO Listening on unix socket file: /tmp/gh-ost.db1.t1.sock
2021-06-26 19:59:57 INFO Migration min values: [1]
2021-06-26 19:59:57 INFO Migration max values: [23]
2021-06-26 19:59:57 INFO Waiting for first throttle metrics to be collected
2021-06-26 19:59:57 INFO First throttle metrics collected
# Migrating `db1`.`t1`; Ghost table is `db1`.`_t1_gho`
# Migrating mdw:3306; inspecting mdw:3306; executing on mdw
# Migration started at Sat Jun 26 19:59:57 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-flag-file: /tmp/t1.log
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/t1.ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.db1.t1.sock
Copy: 0/23 0.0%; Applied: 0; Backlog: 0/1000; Time: 0s(total), 0s(copy); streamer: mysql-bin.000012:328160456; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
Copy: 0/23 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000012:328164813; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: N/A
2021-06-26 19:59:58 INFO Row copy complete
Copy: 23/23 100.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 1s(copy); streamer: mysql-bin.000012:328165755; Lag: 0.01s, HeartbeatLag: 0.02s, State: migrating; ETA: due
2021-06-26 19:59:58 INFO Grabbing voluntary lock: gh-ost.24.lock
2021-06-26 19:59:58 INFO Setting LOCK timeout as 6 seconds
2021-06-26 19:59:58 INFO Looking for magic cut-over table
2021-06-26 19:59:58 INFO Creating magic cut-over table `db1`.`_t1_del`
2021-06-26 19:59:58 INFO Magic cut-over table created
2021-06-26 19:59:58 INFO Locking `db1`.`t1`, `db1`.`_t1_del`
2021-06-26 19:59:58 INFO Tables locked
2021-06-26 19:59:58 INFO Session locking original & magic tables is 24
2021-06-26 19:59:58 INFO Writing changelog state: AllEventsUpToLockProcessed:1624708798317058699
2021-06-26 19:59:58 INFO Intercepted changelog state AllEventsUpToLockProcessed
2021-06-26 19:59:58 INFO Handled changelog state AllEventsUpToLockProcessed
2021-06-26 19:59:58 INFO Waiting for events up to lock
Copy: 23/23 100.0%; Applied: 0; Backlog: 1/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000012:328171119; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2021-06-26 19:59:59 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1624708798317058699
2021-06-26 19:59:59 INFO Done waiting for events up to lock; duration=977.509552ms
# Migrating `db1`.`t1`; Ghost table is `db1`.`_t1_gho`
# Migrating mdw:3306; inspecting mdw:3306; executing on mdw
# Migration started at Sat Jun 26 19:59:57 +0800 2021
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-flag-file: /tmp/t1.log
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# panic-flag-file: /tmp/t1.ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.db1.t1.sock
Copy: 23/23 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: mysql-bin.000012:328171605; Lag: 0.01s, HeartbeatLag: 0.02s, State: migrating; ETA: due
2021-06-26 19:59:59 INFO Setting RENAME timeout as 3 seconds
2021-06-26 19:59:59 INFO Session renaming tables is 18
2021-06-26 19:59:59 INFO Issuing and expecting this to block: rename /* gh-ost */ table `db1`.`t1` to `db1`.`_t1_del`, `db1`.`_t1_gho` to `db1`.`t1`
2021-06-26 19:59:59 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2021-06-26 19:59:59 INFO Checking session lock: gh-ost.24.lock
2021-06-26 19:59:59 INFO Connection holding lock on original table still exists
2021-06-26 19:59:59 INFO Will now proceed to drop magic table and unlock tables
2021-06-26 19:59:59 INFO Dropping magic cut-over table
2021-06-26 19:59:59 INFO Releasing lock from `db1`.`t1`, `db1`.`_t1_del`
2021-06-26 19:59:59 INFO Tables unlocked
2021-06-26 19:59:59 INFO Tables renamed
2021-06-26 19:59:59 INFO Lock & rename duration: 1.013337139s. During this time, queries on `t1` were blocked
[2021/06/26 19:59:59] [info] binlogsyncer.go:164 syncer is closing...
[2021/06/26 19:59:59] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
2021-06-26 19:59:59 INFO Closed streamer connection. err=<nil>
2021-06-26 19:59:59 INFO Dropping table `db1`.`_t1_ghc`
[2021/06/26 19:59:59] [info] binlogsyncer.go:179 syncer is closed
2021-06-26 19:59:59 INFO Table dropped
2021-06-26 19:59:59 INFO Dropping table `db1`.`_t1_del`
2021-06-26 19:59:59 INFO Table dropped
2021-06-26 19:59:59 INFO Done migrating `db1`.`t1`
2021-06-26 19:59:59 INFO Removing socket file: /tmp/gh-ost.db1.t1.sock
2021-06-26 19:59:59 INFO Tearing down inspector
2021-06-26 19:59:59 INFO Tearing down applier
2021-06-26 19:59:59 INFO Tearing down streamer
2021-06-26 19:59:59 INFO Tearing down throttler
# Done

MySQL大表DDL方式对比一、原生DDL、Online DDL二、pt-online-schema-change三、gh-ost

文章目录

一、原生DDL、Online DDL

1.1 Online ddl操作原理

1.2 DDL操作的两个参数

1.3 Online DDL 操作空间存储的要求

二、pt-online-schema-change

2.1 pt-osc的大致流程：

2.2 pt-osc的特点

2.3 使用pt-osc的约束

2.4 操作示例

三、gh-ost

3.1 gh-ost的三种模式

3.2 gh-ost的特点

3.3 使用gh-ost的约束

继续阅读

2022秋招面试总结（cpp+java+测开）百度测开一面字节后端一面虾皮后端一面虾皮后端二面

数据库之DDL操作数据库DDL操作数据库DDL操作数据表

数据库之DQL操作数据库

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件