大表Online-DDL操作问题初探

大表Online-DDL操作问题初探

今天下午在执行一个表结构变更的时候，出现了一个问题，拿着分析了分析，对于online-ddl又有了新的认识，这里写篇文章记录下。

01

问题描述

表数量级：线上一个百万级别的表

mysql  20:25:01>>select count(*) from game;
+----------+
| count(*) |
+----------+
|  1719127 |
+----------+
1 row in set (0.52 sec)

表特点：该表的数据量不变，但是由于使用了insert...on duplicate key update的语句执行插入，自增id时时刻刻都在增长。

需要进行的操作：给该表新增4个字段

02

执行方案

看到这个问题，我进行了两个测试：

首先将该表进行备份，并且在测试环境上恢复了一份，不加任何的写入操作，直接在测试环境上执行alter table的操作，执行时间是16.6s，这个时间从业务的角度讲，是有问题的；

然后又重新模拟线上的写入操作，重新执行该alter操作，在执行的时候出现下面的问题：

ERROR 1062 (23000): Duplicate entry '25979438' for key 'PRIMARY'，直接报错，这肯定是不能接受的，不过这个报错结果看着似乎很奇怪，报的错误是冲突的主键，当我用这个主键为条件在表中进行搜索的时候，结果是提示该记录不存在。

看到了这俩结果，首先可以确定的是，这些SQL不能再线上的环境上执行成功，所以我们需要使用pt-osc这类工具来执行这个alter table的SQL，之前写过一篇关于pt-osc的文章，这里给个传送门：

MySQL大表删除工具pt-osc

具体的语法细节我就不写了，文章中有，今天在使用这个工具的时候，遇到了一个小错误，这块儿也写出来，供大家参看，可能有点帮助吧：

该工具中有个参数是--[no]-check-replication-filter，该参数是用来屏蔽掉alter table过程中对于复制过滤器的检测的，关于复制过滤器，大家可以参看之前的文章：

MySQL动态修改复制过滤器

这个参数我第一次是添加在pt-online-schema-change工具中的，但是在执行的过程中遇到了以下的报错，(这个操作在之前没有遇到过)

[root@  ~]# pt-online-schema-change --user=dba_admin --password=XXXXXXXXXX -h127.0.0.1 -P4316 --alter " ADD COLUMN s tinyint(1) unsigned NOT NULL DEFAULT '0' , ADD COLUMN g varchar(255) NOT NULL DEFAULT '' COMMENT , ADD COLUMN m varchar(255) NOT NULL DEFAULT '' , ADD COLUMN t2 int(11) unsigned NOT NULL DEFAULT '0'" D=game,t=game   --alter-foreign-keys-method=auto  --recursion-method=none --print --charset=utf8 --execute 
Usage: pt-online-schema-change [OPTIONS] DSN

Errors in command-line arguments:
  * Specify only one DSN on the command line

pt-online-schema-change alters a table's structure without blocking reads or
writes.  Specify the database and table in the DSN.  Do not use this tool before
reading its documentation and checking your backups carefully.  For more
details, please use the --help option, or try 'perldoc
/usr/bin/pt-online-schema-change' for complete documentation.

该错误的解决办法就是在语法中去掉上面的参数--[no]-check-replication-filter，关于其他参数，可以通过pt-online-schema-change --help的命令去查看。

去掉该参数之后，最终pt-osc修改表结构执行成功。

03

关于online-ddl的一点说明

MySQL的online ddl操作是在5.6版本引入的，在不同的版本下，有不同的执行过程：

1、在MySQL5.5版本前，我们是使用表copy的方式来进行alter table操作的，需要借助一个临时表将全部数据拷贝，然后进行rename操作，这个过程中，是需要锁表的，原表只能读不能写。而且需要消耗一倍的空间。

2、MySQL5.5版本下引入了表inplace的方式进行该操作，该操作不需要拷贝旧表的数据，但是依旧会锁住原表。仅支持添加、删除索引两种ddl，其他操作还是根据5.5版本之前的copy的方法进行的。

3、MySQL5.6版本下开始支持online-ddl的操作，该方法和上面两种最大的不同是在执行DDL的时候，不会锁原表，原表不仅可以读，还可以写，当然，需要注意的是，该特性仅支持部分DDL操作。我们可以在DDL操作的最后面，通过执行algorithm的方法指定它的执行算法，如果algorithm=inplace，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发；

algorithm=copy，需要拷贝原始表，所以不允许并发DML写操作；这个参数可以添加在alter table语句的最后面，类似官方文档中的那样：

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

4、在MySQL5.7中，沿用了5.6中的online-ddl的方法，但是稍有不同，从官方文档上看，5.7和5.6中的区别在于5.7中支持的alter table的操作类型更多了。

在MySQL官网上，我们可以看到，online-ddl分为了很多种类，如下：

索引操作

主键操作

列操作

外键操作

表操作

表空间操作

分区操作

不同的操作类型中，又包含了不同的操作，例如列操作中包含了生成列、删除列、修改列属性、修改列名称等等、不同的情况下是否需要重建表、是否支持并发、是否会加锁、以及并发是如何处理的，情况都不太一样，本来想用一篇文章写完这些，但是感觉篇幅会非常冗长，这块儿还需要研究一下再给出一个比较满意的答案。

本周将会用其他的篇幅来说明这个问题，时间关系，今天的内容就先到这里了。

给出官方文档的链接，针对在线DDL过程中的duplicate key的错误，大家可以先看看能不能找到原因：

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-performance.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-failure-conditions.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html