如何平滑的变更单表超 100000000 条记录的数据库结构

众所周知，很多互联网业务都面临着无法停机，需要在线变更数据库结构的情况。但是在线修改数据量较大的表，可能对线上业务产生较大影响，比如：

在线修改大表的表结构执行时间往往不可预估，一般时间较长。
由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作。
如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入。
修改大表结构容易导致数据库 CPU、IO 等性能消耗，使 MySQL 服务器性能降低。
在线修改大表结构容易导致主从延时，从而影响业务读取。

Percona-Toolkit 源自 Maatkit 和 Aspersa 工具，这两个工具是管理 MySQL 的最有名的工具，但 Maatkit 已经不维护了，全部归并到 Percona-Toolkit。Percona Toolkit 是一组高级的命令行工具，用来管理 MySQL 和系统任务，主要包括以下功能：

验证主节点和复制数据的一致性
有效的对记录行进行归档
找出重复的索引
总结 MySQL 服务器
从日志和 tcpdump 中分析查询
问题发生时收集重要的系统信息
在线修改表结构

pt-online-schema-change 是 Percona-Toolkit 工具集中的一个组件，很多 DBA 在使用 Percona-Toolkit 时第一个使用的工具就是它，同时也是使用最频繁的一个工具。它可以做到在修改表结构的同时（即进行 DDL 操作）不阻塞数据库表 DML 的进行，这样降低了对生产环境数据库的影响。

在 MySQL 5.6.7 之前是不支持 Online DDL 特性的，即使在添加二级索引的时候有 FIC 特性，但是在修改表字段的时候还是会有锁表并阻止表的 DML 操作。这样对于 DBA 来说是非常痛苦的，好在有 pt-online-schema-change 工具在没有 Online DDL 时解决了这一问题，pt-online-schema-change 其主要特点就是在数据库结构修改过程中不会造成读写阻塞。

pt-online-schema-change 安装

pt-online-schema-change 安装非常简单，官方已经为我们准备好了各主流平台的安装包，只需下载对应版本安装即可。目前最新版本是 3.1.0，这里我们以 CentOS 7 为例：

# 安装相关依赖包
$ yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
$ wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/redhat/7/x86_64/percona-toolkit-3.1.0-2.el7.x86_64.rpm
$ rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

更多平台的安装包可以直接在官网地址下载：https://www.percona.com/downloads/percona-toolkit/LATEST/

pt-online-schema-change 语法说明

常用选项说明

$ pt-online-schema-change [OPTIONS] DSN

--alter
变更结构选项，不需要ALTER TABLE关键字，如果表有多个变更可以使用逗号分隔。

限制：

1.在绝大部分情况下表都需要有主键或者是唯一索引。因为这个工具会在运行的时候创建一个DELETE触发器，这是为了保证在变更中新表能够与旧表保持更新一致性。值得注意的是，如果在需要变更的列上创建主键或是唯一索引时，则会以这些列创建触发器；
2.不能使用RENAME子句为表进行重命名；
3.字段不能通过删除再重添加的方式进行重命名，这种方式是不会拷贝原字段的数到新字段上；
4.如果新增 NOT NULL 的列并且没有指定 default 值，工具就会执行失败，它并不会指定默认值；
5.涉及到删除外键时，需要指定 _constraint_name，工具会在新表上创建一个前面加了下划线的外键名称，这个外键名称与原致。如需要删除外键 fk_foo，则指定 '--alter "DROP FOREIGN KEY _fk_foo"'。

--alter-foreign-keys-method
采用何种方式修改外键以便关联到新表上。有外键约束的表需要被特殊处理，为了确保外键依然能够关联到正确的表上。当工具重命名外键关联的父表时，确保外键也必须关联到重命名后的父表。

主要有以下几种方式：

auto：让工具自动选择使用。优先选择 rebuild_constraints，如果不成功，则选择 drop_swap；
rebuild_constraints：这种方式使用 ALTER TABLE 先删除然后重建外键关联到新父表。这是首选的方式，如果一张或多张子表过大会导致 ALTER 需要很长时间，子表会被阻塞；
drop_swap：禁用外键约束 (FOREIGN_KEY_CHECKS=0) ，在进行重命名新父表之前删除原父表，这与常规转换旧表与新表的方式不同，这个 RENAME 操作是原子性的并且对应用客户端无感知。

这种方式更快速并且不会阻塞，但是也有隐患：

1.删除原父表以及重命名新表这段时间很短，如果这段时间更改子表有可能会报错；
2.如果重命名新表发生失败，而原父表已经永久删除了，这时就需要人工进行干预了。

这种方式强制使用选项 '--no-swap-tables' 和 '--no-drop-old-table'。

none：这种方式类似于 drop_swap，不同在于不进行 swap 原父表。子表有任何外键关联父表都将变成关联一张不存在的表，这会使得子表的外键约束失效，可以通过 SHOW ENGINE INNODB STATUS 查看。

--[no]analyze-before-swap
默认值：yes
在新表与旧表完成转换之前对新表执行 ANALYZE TABLE 操作，默认会在 MySQL 5.6 及之后版本并且开启 innodb_stats_persistent 的情况下执行。

--ask-pass
命令行提示密码输入，保护密码安全，前提需安装模块 perl-TermReadKey。

--[no]check-alter
默认值：yes

解析变更选项的内容，发出表变更警告，主要警告项为：

1.字段重命名

在工具的早期版本中，通过指定 CHANGE COLUMN name new_name 进行字段重命名会导致数据库的丢失，现在的版本已经通过代码解决了数据一致性问题。但这段代码并不能保证能够确保数据的不丢失。所以当涉及到字段名变更时应通过添加选项 '--dry-run' 和 '--print' 查看变更是否可以正确执行。

2.删除主键

如果 '--alter' 选项中包含 DROP PRIMARY KEY 删除主键的操作，除非指定选项 '--dry-run'，否则工具将退出。变更表的主键是十分危险的，工具变更时建立的触发器，尤其是 DELETE 触发器，是基于主键的，在做主键变更前先添加选项 '--dry-run' 和 '--print' 验证触发器是可用的。

--[no]check-replication-filters
默认值：yes
如果服务器指定了任何主从复制过滤选项，该工具会查询是否有复制过滤选项，一旦发现，工具都会中止并报错。

--check-slave-lag
指定暂停旧表与新表的数据拷贝直到主从复制小于选项 '--max-lag' 指定的值。

--skip-check-slave-lag
DSN 类型，可重复使用
指定 DSN 连接从库时跳过主从延迟检查，可以指定多个从库检查。

--check-interval
默认值：1s
指定因为选项 '--max-lag' 检查之间休眠时间。

--chunk-index
指定使用哪个索引对表进行 chunk 分块操作。默认情况下会选择最优的索引，工具会在 SQL 语句中添加 FORCE INDEX 子句。

--chunk-index-columns
指定使用选项 '--chunk-index' 的索引使用最左前缀几个索引字段，只适用于复合索引。

--chunk-size
默认值：1000
指定表分块的 chunk 大小，每个 chunk 需要拷贝的表行数，允许的后缀单位为 k、M、G。
当指定了这个选项会覆盖工具默认动态调整 chunk 块大小以便在选项 '--chunk-time' 指定时间内完成行拷贝的行为。

--chunk-time
默认值：0.5
动态调整每个 chunk 的大小使相应的表行数都在指定的时间内完成拷贝查询。如果该选项值设置为 0，则不会动态调整 chunk 的大小，就有可能造成每次拷贝查询的时间不同，但每个 chunk 大小还是一致的。

--host，-h
指定连接的数据库 IP 地址。

--port，-P
指定连接的数据库 Port 端口。

--user，-u
指定连接的数据库用户。

--password，-p
指定连接的数据库用户密码。

--database，-D
指定连接的数据库。

--charset，-A
指定连接字符集。

--max-lag
默认值：1s
指定允许主从复制延迟时长的最大值，单位秒。如果在每次拷贝查询之后主从延迟超过指定的值，则操作将暂停执行，暂停休眠时间为选项 '--check-interval' 指定的值。待休眠时间结束之后再次检查主从延迟时长，检查方法是通过从库查询的 'Seconds_Behind_Master' 值来确定。如果主从复制延迟一直大于该参数指定值或者从库停止复制，则操作将一直等待直到从库重新启动并且延迟小于该参数指定值。

--max-load
数组类型，默认值：Threads_running = 25
在变更拷贝完每个 chunk 数据之后，运行 SHOW GLOBAL STATUS 检查所指定变量值高于该参数指定变量的阈值时将暂停操作。如果有多个变量阈值，可以用 ','(逗号)进行分隔，参数指定型式可以为变量名 =MAX_VALUE 或变量名 :MAX_VALUE。
如果只是指定变量名，没有为其指定阈值，则检查当前值并增加 20% 作为阈值。如：
    --max-load=Threads_running：没有指定具体值，以当前查询值增加 20% 作为阈值，如当前为 100，阈值为 120；
    --max-load=Threads_running:10：以当前指定值为阈值。

--critical-load
数组类型，默认值：Threads_running = 50
指定需中止操作的状态变量阈值。用法可以参考选项 '--max-load'。

--preserve-triggers 指定保留旧表的触发器。

从 MySQL 5.7.2 起开始支持在同一张给定的表上定义具有相同触发事件和触发时间的多个触发器。这意味着如果表原来已有触发器，那么工具所需的触发器也可以创建成功。如果指定了该选项，则工具将旧表上所有的触发器复制到新表上，然后再进行表数据行的拷贝操作。

限制：

1.如果旧表上的触发器引用了将被工具删除的字段，则触发器失效；
2.该选项不能与选项 '--no-drop-triggers'、'--no-drop-old-table' 和 '--no-swap-tables' 一起使用，因为该选项需要删除旧表的触发器并在新表上重新创建，因为表不可能有多个同名的触发器。

--null-to-not-null
指定可以将允许NULL的字段转换为 NOT NULL 字段。其中如有包含 NULL 行的字段值转换为字段默认值，如果没有字段值，则根字段类型来分配默认值。如：字符串类型为 ''(空字符串)，数值类型为 0。

--new-table-name
字符串类型，默认值：%T_new
指定旧表和新表交换之前新表的名称。%T会替换为旧表名称。

--[no]drop-new-table
默认值：yes
指定如果拷贝旧表数据到新表时失败，则删除新表。
如果指定选项 '--no-drop-new-table' 以及 '--no-swap-tables' 将保留一份变更后的副本，但不会对旧表进行修改。

限制：当选项 '--alter-foreign-keys-method' 指定的方式为 drop_swap 时，选项 '--no-drop-new-table' 不生效。

--[no]drop-old-table
默认值：yes
指定在完成旧表与新表交换重命名之后删除旧表。如果之间发生了错误，则会保留旧表。指定选项 '--no-swap-tables' 同样不会删除旧表。

--[no]drop-triggers
默认值：yes
指定旧表上删除触发器。如果指定了选项 '--no-drop-triggers' 就会强制指定 '--no-drop-old-table'。

--[no]swap-tables
默认值：yes
指定变更交换旧表和新表。
如果指定选项 '--no-swap-tables' 也会运行整个过程，只是最后不进行旧表与新表的交换，并且删除新表。

--dry-run
指定创建和变更新表，但是不创建触发器，也不拷贝数据和变更原始表。

--execute
指定需要执行真正的变更操作。当确定要执行变更操作时必须指定该选项，如果不指定该选项，则工具会进行安全检查之后退出。

--[no]check-unique-key-change
默认值：yes
当工具要进行添加唯一索引的变更时停止运行。因为工具使用语句 INSERT IGNORE 从旧表进行数据拷贝插入新表，如果插入的值违返唯一性约束，数据插入不会明确提示失败但这样会造成数据丢失。

--recursion-method
默认值：processlist，hosts
指定获取从库的方式。
METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST   
hosts        SHOW SLAVE HOSTS   
dsn=DSN      DSNs from a table
none         Do not find slaves
==========================================================
processlist：通过SHOW PROCESSLIST方式找到 slave，为默认方式，当 SHOW SLAVE HOSTS 不可用时。一旦实例运行在非 3306 端口上时，hosts 方式就会变为默认方式；
hosts：通过 SHOW SLAVE HOSTS 方式找到 slave，hosts 方式要求从库配置 '--report_host' 和 '--report_port' 这两个参数；
dsn：通过读取表中从库的 DSN 信息进行连接。

--recurse
指定搜寻从库的层级，默认无限级。

--set-vars
默认：
    wait_timeout=10000
    innodb_lock_wait_timeout=1
    lock_wait_timeout=60
运行检查时指定参数值，如有多个用','(逗号)分隔。如 `--set-vars=wait_timeout=5000`。

--sleep
默认值：0s
指定表变更拷贝数据时的间隔时间。

--print
打印工具执行过程中的语句到 STDOUT。可以结合 '--dry-run'一起使用。
 
--progress
打印工具执行过程的进度提示到 STDERR。选项值有两部分组成，用逗号进行分隔，第一部分为百分比，时间和迭代。第二部分为根据第一部分数据更新频率，也分为百分比，时间和迭代。

--quiet，-q
不打印工具执行过程的信息到 STDOUT (禁用'--progress')。但错误和警告还是打印到 STDERR。

--statistics
打印内部计数的统计信息。

--version
显示工具的版本并退出。

--[no]version-check
默认值：yes
检查 Percona Toolkit、MySQL 和其他程序的最新版本。

DSN 选项(DSN)

可以使用 DSN 方式来连接数据库，DSN 选项为 key=value 方式，在等号的两侧不能有空格出现，并且区分大小写，多个选项之前以’,’(逗号)隔开，主要选项如下：

A 指定字符集
D 指定变更表所在数据库
t 指定需要变更的表
h 指定要连接的 HOST
P 指定要连接的 PORT
S 指定连接所使用的 SOCKET 文件(Unix systems)
u 指定连接的用户名
p 指定连接的用户名密码

示例：

1	h=192.168.58.3,P=3306,D=employees,t=employees

pt-online-schema-change 使用限制

要求需要执行变更的表有主键 (Primary key) 或唯一索引 (Unique index)，否则工具会执行失败，参考选项 --alter 说明；
如果检测到表有外键约束 (Foreign key)，工具除非选项 --alter-foreign-keys-method，否则不会执行变更；
如果检测到主从复制中存在过滤，则工具不会执行，参考选项 --[no]check-replication-filters 说明；
如果检测到主从复制有延迟，则工具有可能会暂停数据拷贝，参考选项 --max-lag 说明；
如果检测到连接当前服务器负载过高，则工具有可能暂停执行或中止退出，参考选项 --max-load 各 --critical-load 说明。

pt-online-schema-change 使用实例

测试数据准备

本文基于 MySQL 官方示例数据库 employee：Example Databases 进行测试。

-- employees：
mysql root@localhost:employees> show create table employees\G;
***************************[ 1. row ]***************************
Table        | employees
Create Table | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.008s

-- dept_emp：
mysql root@localhost:employees> show create table dept_emp\G;
***************************[ 1. row ]***************************
Table        | dept_emp
Create Table | CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s

-- departments：
mysql root@localhost:employees> show create table departments\G;
***************************[ 1. row ]***************************
Table        | departments
Create Table | CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.012s

mysql root@localhost:employees> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024   |
+----------+
1 row in set
Time: 0.342s
mysql root@localhost:employees> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
| 331603   |
+----------+
1 row in set
Time: 0.306s
mysql root@localhost:employees> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9        |
+----------+
1 row in set
Time: 0.050s

添加一个字段

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --charset=utf8

因为 employees 表中的 emp_no 字段被其他表外建关联，以下命令执行时会报如下错误：

1	You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.

根据报错信息的提示，加入选项 --alter-foreign-keys-method 重新执行并通过选项 --dry-run 查看执行过程主要信息：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --alter-foreign-keys-method=auto --charset=utf8 --dry-run
Enter MySQL password:

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
Child tables:
  `employees`.`dept_emp` (approx. 331143 rows)
  `employees`.`dept_manager` (approx. 24 rows)
Will automatically choose the method to update foreign keys.
Starting a dry run.  `employees`.`employees` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not determining the method to update foreign keys because this is a dry run.
Not swapping tables because this is a dry run.
Not updating foreign key constraints 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.
2019-03-25T13:30:05 Dropping new table...
2019-03-25T13:30:05 Dropped new table OK.
Dry run complete.  `employees`.`employees` was not altered.

-- 确保信息无误之后可以真正执行变更操作
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --alter-foreign-keys-method=auto --charset=utf8 --execute

……省略……
Will automatically choose the method to update foreign keys.
Altering `employees`.`employees`...
Creating new table...
Created new table employees._employees_new OK.
Altering new table...
Altered `employees`.`_employees_new` OK.
2019-03-25T13:35:25 Creating triggers...
2019-03-25T13:35:25 Created triggers OK.
2019-03-25T13:35:25 Copying approximately 299512 rows...
2019-03-25T13:35:31 Copied rows OK.
2019-03-25T13:35:31 Max rows for the rebuild_constraints method: 99266
Determining the method to update foreign keys...
2019-03-25T13:35:31   `employees`.`dept_emp`: too many rows: 331143; must use drop_swap
2019-03-25T13:35:31 Drop-swapping tables...
2019-03-25T13:35:31 Analyzing new table...
2019-03-25T13:35:31 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2019-03-25T13:35:31 Dropping triggers...
2019-03-25T13:35:31 Dropped triggers OK.
Successfully altered `employees`.`employees`.

修改一个字段

将表 employees 的 comment 字段的字符集修改为 utf8mb4。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column comment varchar(50) character set utf8mb4" --alter-foreign-keys-method=auto --charset=utf8 --execute

删除字段

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "drop column comment" --alter-foreign-keys-method=auto --charset=utf8 --execute

添加索引

为表 dept_emp 的字段 from_date 和 to_date 创建复合索引 idx_fr_to_date。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add index idx_fr_to_date(from_date,to_date)" --alter-foreign-keys-method=auto --charset=utf8 --execute

删除索引

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop index idx_fr_to_date" --alter-foreign-keys-method=auto --charset=utf8 --execute

修改字段允许 NULL

将表 dept_emp 的字段 to_date 指定为允许 NULL。

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "modify column to_date date null" --alter-foreign-keys-method=auto --charset=utf8 --execute

修改字段不允许 NULL (NOT NULL)

为表 employees 添加字段 ptosc_num 并允许 NULL，字段类型为 int，没有指定默认值。

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add ptosc_num int null" --alter-foreign-keys-method=auto --charset=utf8 --execute

修改字段 ptosc_num 为不允许 NULL (NOT NULL)，需要通过指定选项 --null-to-not-null，否则会报错。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "modify column ptosc_num int not null" --alter-foreign-keys-method=auto --null-to-not-null --charset=utf8 --execute

-- 因为字段ptosc_num没有指定默认值，字段类型为int，所以默认值为0
mysql root@localhost:employees> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | ptosc_num |
+--------+------------+------------+-----------+--------+------------+-----------+
| 10001  | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 | 0         |
| 10002  | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 | 0         |
| 10003  | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 | 0         |
| 10004  | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 | 0         |
| 10005  | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 | 0         |
+--------+------------+------------+-----------+--------+------------+-----------+
5 rows in set
Time: 0.022s

删除外键

需要为外键指定名称为 _forigen_key，因为在创建新表时候默认为新表上的外键创建这样的名称，如果没这样指定则无法删除。

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "drop foreign key _dept_emp_ibfk_1" --alter-foreign-keys-method=auto --charset=utf8 --execute

重建表

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "engine=InnoDB" --alter-foreign-keys-method=auto --charset=utf8 --execute

变更后保留旧表

如果是涉及外键关联的父表进行变更，则建议选项 --alter-foreign-keys-method=rebuild_constraints，这样在子表中会重命名外键约束名，如果选项 --alter-foreign-keys-method 有可能取值 drop_swap 时，则会强制使用选项 --no-swap-tables 和--no-drop-old-table，其中 --no-swap-tables 并不会有旧表的产生，就不存在保留之说了。

1	# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-old-table --charset=utf8 --execute

以上语句执行完成后会在数据库中生成名为 _dept_emp_old 的表，即变更之前的旧表。

变更后保留新表

顾名思义，就是先做一次完整的表变更操作，但是不进行旧表与新表的交换，也不删除变更之后的新表，通过指定选项 --no-drop-new-table 和 --no-swap-tables 实现，可以通过选项 --new-table-name 指定新表名，当选项 --alter-foreign-keys-method=drop_swap 时，--no-drop-new-table 不生效，与保留旧表的情形一致。

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-new-table --no-swap-tables --new-table-name='dept_emp_bak' --charset=utf8 --execute

以上语句执行完成后会在数据库中生成名为 dept_emp_bak 的表，即变更之后的新表，但对旧表不会做任何修改。

添加主键

如果是 InnoDB 表没有主键，真的不敢想像啊，但还是要进行测式下。这里测试基于 employees 表创建 employees_ptosc 表：

mysql root@localhost:employees> create table employees_ptosc as select * from employees;
Query OK, 300024 rows affected
Time: 2.010s
mysql root@localhost:employees> show create table employees_ptosc;
+-----------------+--------------------------------------+
| Table           | Create Table                         |
+-----------------+--------------------------------------+
| employees_ptosc | CREATE TABLE `employees_ptosc` (     |
|                 |   `emp_no` int(11) NOT NULL,         |
|                 |   `birth_date` date NOT NULL,        |
|                 |   `first_name` varchar(14) NOT NULL, |
|                 |   `last_name` varchar(16) NOT NULL,  |
|                 |   `gender` enum('M','F') NOT NULL,   |
|                 |   `hire_date` date NOT NULL          |
|                 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------+
1 row in set
Time: 0.022s

对 employees_ptosc 表添加主键：

-- 如果 employees_ptosc 表没有任何索引和约束会报如下信息，工具执行失败
Cannot chunk the original table `employees`.`employees_ptosc`: There is no good index and the table is oversized. at /usr/bin/pt-online-schema-change line 5882.

-- 先为 employees_ptosc 表创建基于 first_name 的索引 idx_first_name，再次执行添加主键
mysql root@localhost:employees> create index idx_first_name on employees_ptosc(first_name);
Query OK, 0 rows affected
Time: 1.175s

-- 如果没有加选项 --no-check-unique-key-change 会报如下信息
……省略……
Altering `employees`.`employees_ptosc`...
`employees`.`employees_ptosc` was not altered.
You are trying to add an unique key. This can result in data loss if the data is not unique.
Please read the documentation for the --check-unique-key-change parameter.
You can check if the column(s) contain duplicate content by running this/these query/queries:

SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
       'Yes, the desired unique index currently contains only unique values',
       'No, the desired unique index contains duplicated values. There will be data loss'
) AS IsThereUniqueness FROM `employees`.`employees_ptosc`;

Keep in mind that these queries could take a long time and consume a lot of resources

大致意思就是工具无法确定需要创建主键基于的字段值是否唯一，一旦有重复值出现，在数据拷贝的时候容易出现数据丢失，并给出了检查的语句。

mysql root@localhost:employees> SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
                                       'Yes, the desired unique index currently contains only unique values',
                                       'No, the desired unique index contains duplicated values. There will be data loss'
                                ) AS IsThereUniqueness FROM `employees`.`employees_ptosc`;

+---------------------------------------------------------------------+
| IsThereUniqueness                                                   |
+---------------------------------------------------------------------+
| Yes, the desired unique index currently contains only unique values |
+---------------------------------------------------------------------+
1 row in set
Time: 0.274s

使用选项 --no-check-unique-key-change 再次执行添加主键操作：

# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees_ptosc --user=admin --ask-pass --alter "add primary key(emp_no)" --charset=utf8 --no-check-unique-key-change --charset=utf8 --execute

pt-online-schema-change 工作流程

为了了解 pt-online-schema-change 工具是如何做到不阻塞 DML 的，还是通过 General log 来了解。

以添加字段的执行语句获得的 General log 为例说明：

-- 初始的一些检查数据库参数、负载信息这里不再细说。
…………省略…………

-- 查看需要执行变更的表状态
200 Query   SHOW TABLES FROM `employees` LIKE 'employees'
200 Query   SELECT VERSION()

-- 查看表是否存在触发器
200 Query   SHOW TRIGGERS FROM `employees` LIKE 'employees'

-- 查看表的建表语句
200 Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
200 Query   USE `employees`
200 Query   SHOW CREATE TABLE `employees`.`employees`
200 Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

-- 查询表的执行计划，确定表是否有外键关联
200 Query   EXPLAIN SELECT * FROM `employees`.`employees` WHERE 1=1
200 Query   SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='employees' AND referenced_table_name='employees'
200 Query   EXPLAIN SELECT * FROM `employees`.`dept_emp` WHERE 1=1
200 Query   EXPLAIN SELECT * FROM `employees`.`dept_manager` WHERE 1=1
200 Query   SHOW VARIABLES LIKE 'wsrep_on'
200 Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

-- 创建'_'(下划线)开头相同表结构的新表，并先在新表上执行变更操作
200 Query   USE `employees`
200 Query   SHOW CREATE TABLE `employees`.`employees`
200 Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
200 Query   CREATE TABLE `employees`.`_employees_new` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
200 Query   ALTER TABLE `employees`.`_employees_new` add comment varchar(50) not null default 'pt-osc'

-- 在原表上分别创建 DELETE、UPDATE、INSERT 三个触发器
200 Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
200 Query   USE `employees`
200 Query   SHOW CREATE TABLE `employees`.`_employees_new`
200 Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

……省略……

200 Query   CREATE TRIGGER `pt_osc_employees_employees_del` AFTER DELETE ON `employees`.`employees` FOR EACH ROW DELETE IGNORE FROM `employees`.`_employees_new` WHERE `employees`.`_employees_new`.`emp_no` <=> OLD.`emp_no`
200 Query   CREATE TRIGGER `pt_osc_employees_employees_upd` AFTER UPDATE ON `employees`.`employees` FOR EACH ROW BEGIN DELETE IGNORE FROM `employees`.`_employees_new` WHERE !(OLD.`emp_no` <=> NEW.`emp_no`) AND `employees`.`_employees_new`.`emp_no` <=> OLD.`emp_no`;REPLACE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (NEW.`emp_no`, NEW.`birth_date`, NEW.`first_name`, NEW.`last_name`, NEW.`gender`, NEW.`hire_date`);END
200 Query   CREATE TRIGGER `pt_osc_employees_employees_ins` AFTER INSERT ON `employees`.`employees` FOR EACH ROW REPLACE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (NEW.`emp_no`, NEW.`birth_date`, NEW.`first_name`, NEW.`last_name`, NEW.`gender`, NEW.`hire_date`)

-- 根据执行计划判断 chunk 包含的行数，以 chunk 数为单位拷贝数据，为在拷贝过程中为这些行加共享读锁
200 Query   EXPLAIN SELECT * FROM `employees`.`employees` WHERE 1=1
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` LIMIT 1 /*first lower boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX (`PRIMARY`) WHERE `emp_no` IS NOT NULL ORDER BY `emp_no` LIMIT 1 /*key_len*/
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `employees`.`employees` FORCE INDEX (`PRIMARY`) WHERE `emp_no` >= '10001' /*key_len*/
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) ORDER BY `emp_no` LIMIT 999, 2 /*next chunk boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '10001')) AND ((`emp_no` <= '11000')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/

-- 每次拷贝完 chunk 中数据后，查看是否有警告，查看服务器的负载情况，这是在每个 chunk 拷贝完成后进行的
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 12909, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) ORDER BY `emp_no` LIMIT 12909, 2 /*next chunk boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '23910')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '11001')) AND ((`emp_no` <= '23910')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'
200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) ORDER BY `emp_no` LIMIT 19857, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) ORDER BY `emp_no` LIMIT 19857, 2 /*next chunk boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) AND ((`emp_no` <= '43768')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '23911')) AND ((`emp_no` <= '43768')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'

……省略……

200 Query   EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) ORDER BY `emp_no` LIMIT 26664, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) ORDER BY `emp_no` LIMIT 26664, 2 /*next chunk boundary*/
200 Query   SELECT /*!40001 SQL_NO_CACHE */ `emp_no` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) ORDER BY `emp_no` DESC LIMIT 1 /*last upper boundary*/
200 Query   EXPLAIN SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) AND ((`emp_no` <= '499999')) LOCK IN SHARE MODE /*explain pt-online-schema-change 31797 copy nibble*/
200 Query   INSERT LOW_PRIORITY IGNORE INTO `employees`.`_employees_new` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) SELECT `emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date` FROM `employees`.`employees` FORCE INDEX(`PRIMARY`) WHERE ((`emp_no` >= '480121')) AND ((`emp_no` <= '499999')) LOCK IN SHARE MODE /*pt-online-schema-change 31797 copy nibble*/
200 Query   SHOW WARNINGS
200 Query   SHOW GLOBAL STATUS LIKE 'Threads_running'

-- 当拷贝数据完成之后，及时分析表进行统计信息的收集
200 Query   EXPLAIN SELECT * FROM `employees`.`dept_emp` WHERE 1=1
200 Query   SHOW VARIABLES LIKE 'version%'
200 Query   SHOW ENGINES
200 Query   SHOW VARIABLES LIKE 'innodb_version'
200 Query   ANALYZE TABLE `employees`.`_employees_new` /* pt-online-schema-change */

-- 完成旧表与新表的交换，主要受选项 --alter-foreign-keys-method 取值不同来进行
'
当 --alter-foreign-keys-method=drop_swap 时，先禁用外键约束检查，删除旧表，将临时表重命名为原旧表名，完成变更
'
200 Query   SET foreign_key_checks=0
200 Query   DROP TABLE IF EXISTS `employees`.`employees`
200 Query   RENAME TABLE `employees`.`_employees_new` TO `employees`.`employees`

'
当 --alter-foreign-keys-method=rebuild_constraints 时，做一个原子性的交换重命名表的操作，删除旧表的操作在删除触发器时一并操作
'
203 Query     ANALYZE TABLE `employees`.`_employees_new` /* pt-online-schema-change */
203 Query     RENAME TABLE `employees`.`employees` TO `employees`.`_employees_old`, `employees`.`_employees_new` TO `employees`.`employees`

-- 删除 3 个触发器
'
当 --alter-foreign-keys-method=drop_swap 时，直接删除。
'
200 Query   DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
200 Query   DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
200 Query   DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
200 Query   SHOW TABLES FROM `employees` LIKE '\_employees\_new'
201 Quit    
200 Quit

'
当 --alter-foreign-keys-method=rebuild_constraints 时，对于关联的外键表执行重建外键操作，删除旧表，完成变更。
'
203 Query     USE `employees`
203 Query     SHOW CREATE TABLE `employees`.`dept_emp`
203 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query     ALTER TABLE `employees`.`dept_emp` DROP FOREIGN KEY `_dept_emp_ibfk_1`, ADD CONSTRAINT `__dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE
203 Query     /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */

203 Query     USE `employees`
203 Query     SHOW CREATE TABLE `employees`.`dept_manager`
203 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
203 Query     ALTER TABLE `employees`.`dept_manager` DROP FOREIGN KEY `__dept_manager_ibfk_1`, ADD CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees`.`employees` (`emp_no`) ON DELETE CASCADE

203 Query     DROP TABLE IF EXISTS `employees`.`_employees_old`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_del`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_upd`
203 Query     DROP TRIGGER IF EXISTS `employees`.`pt_osc_employees_employees_ins`
203 Query     SHOW TABLES FROM `employees` LIKE '\_employees\_new'
204 Quit
203 Quit

整个工作流程总结如下：

查询当前数据库服务器信息，包括参数设置，负载信息等，判断表是否有存在触发器，是否有外键关联；
创建一张与旧表结构相同的新表，表名为_旧表名；
在新创建的表上做变更操作；
旧表上创建 DELETE、UPDATE、INSERT 3 个触发器；
拷贝旧表数据到新表上，以 chunk 为单位进行，拷贝期间涉及的行会持有共享读锁；
拷贝期间如果旧表如有 DML 操作，则通过触发器更新同步到新表上；
当拷贝数据完成之后旧表与新表进行重命名；
如果有涉及到外键，根据工具指定选项进行外键处理；
删除旧表；
删除旧表上触发器。

总结

当业务量较大时，修改操作会等待没有数据修改后，执行最后的 rename 操作。因此，在修改表结构时，应该尽量选择在业务相对空闲时，至少修改表上的数据操作较低时，执行较为妥当。由于可能存在一定的风险，在操作之前，建议对数据表进行备份，可以使得操作更安全、可靠。

pt-online-schema-change 工具对于任意的 DDL 语句都是通过创新表拷贝数据来进行，期间都支持 DML，而 Online DDL 根据 DDL 类型的来区分是否需要对表进行 COPY TABLE 操作，有点类似于工具的创建临时表进行变更，而不需要 COPY TABLE 操作的 DDL 语句在执行期间支持DML。

关于在对表进行 DDL 时使用 MySQL 原生的 Online DDL 特性还是使用 pt-online-schema-change 工具，通过以上对工具使用的说明与用法测试可以总结如下：

如果 MySQL 版本不支持 Online DDL 特性，比如早于 5.6 版本的 MySQL，则使用 pt-online-schema-change 工具；
如果 MySQL 版本支持 Online DDL 特性，则优先考虑使用 Online DDL，因为毕竟原生的支持较好，同时不容易产生不可预知的错误；
如果 DDL 语句在使用 Online DDL 时需要进行 COPY TABLE 操作，建议使用 pt-online-schema-change 工具，因为期间支持 DML 操作。
如果表存在触发器的情况下，优先使用 Online DDL，对于 MySQL 5.7.2 之后版本则可以 pt-online-schema-change 工具并通过指定选项 --preserve-triggers；
如果涉及外键关联的表，优先考虑使用 Online DDL。

如何平滑的变更单表超 100000000 条记录的数据库结构

pt-online-schema-change 安装

pt-online-schema-change 语法说明

pt-online-schema-change 使用限制

pt-online-schema-change 使用实例

pt-online-schema-change 工作流程

总结

参考文档

FEATURED TAGS

FRIENDS