MySQL大表删除工具pt-osc​

//

MySQL大表删除工具pt-osc

//

业务场景介绍

早上刚来，有个业务需求，是要变更一张表的表结构，我登陆到服务器上看了看之前的变结构，大概信息如下：

表数据量：690w左右， 表字段数量：40个， 包含索引个数：6个， 表空间ibd文件：3G左右

看到这个信息，我询问业务方这个表里面的数据是冷数据还是热数据，还有没有线上使用，得到的回复是一直在使用。直观上来讲，这个已经不能直接使用alter table的方法来变更表结构了，如果非要这么做，那就请提前准备好故障报告再做。哈哈

为了验证我的直观上的猜想，我把数据导入到了测试环境上，然后测试了一下性能，谁知道，这个导入就花费了好长时间，来看我的测试结果：

3G大小的表空间ibd文件，导入mysql数据库，时间大概80min
[root@tk-dba-mysql-194 mysql_4308]# time /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P4308 -D db_name < /data/mysql/db_name_1028.sql
Enter password: 

real    80m31.491s
user    0m39.040s
sys     0m3.851s

这个结果我看着也很意外，测试MySQL实例的buffer_pool大小是1024M，3个G的数据导入了80min，感觉有点不正常，分析下原因，这个可能是因为表本身包含的索引很多，有6个索引，在进行insert操作的时候，需要重建索引，因此导致花费的时间非常长。这是一个优化点，可以在导入之前先把索引给去掉，然后导入数据，导入数据完成之后再创建索引，这样会起到一定的效果。

导入完成之后，我们在测试环境上直接使用alter table的操作进行处理， 这里说说alter操作对表造成的影响，在MySQL5.6之前，在alter这个时间段里面，表是被加了锁的(写锁)，加写锁时其他用户只能select表不能update、insert表。表数据量越大，耗时越长。 mysql在线ddl(加字段、加索引等修改表结构之类的操作）过程如下： 1、对表加锁(表此时只读) 2、复制原表物理结构 3、修改表的物理结构 4、把原表数据导入中间表中，数据同步完后，锁定中间表，并删除原表 5、rename中间表为原表 6、刷新数据字典，并释放锁

在测试环境上进行了测试，得到的测试结果如下：

mysql >>select count(*) from table_name;  
+----------+
| count(*) |
+----------+
|  6899928 |
+----------+
1 row in set (2.21 sec)


mysql >>ALTER TABLE `table_name` ADD `field_type` smallint(4) unsigned NOT NULL DEFAULT '0' ;
Query OK, 6899928 rows affected (3 min 27.44 sec)
Records: 6899928  Duplicates: 0  Warnings: 0


`#在另外一个窗口查看连接`
mysql--dba_admin@127.0.0.1:(none) 13:36:38>>show processlist;
+-----+-----------+-----------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id  | User      | Host            | db          | Command | Time | State             | Info                                                                                                 |
+-----+-----------+-----------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 227 | dba_admin | 127.0.0.1:51627 | db_name     | Query   |   13 | copy to tmp table | ALTER TABLE `table_name` ADD `field_type` smallint(4) unsigned NOT NULL DEFAULT '0'          |
| 255 | dba_admin | 127.0.0.1:52565 | NULL        | Query   |    0 | NULL              | show processlist                                                                                     |
+-----+-----------+-----------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+




mysql >>ALTER TABLE `table_name` drop column `field_type`;
Query OK, 6899928 rows affected (3 min 24.72 sec)
Records: 6899928  Duplicates: 0  Warnings: 0

`#在另外一个窗口查看连接`
mysql >>show processlist;
+-----+-----------+-----------------+-------------+---------+------+-------------------+-----------------------------------------------+
| Id  | User      | Host            | db          | Command | Time | State             | Info                                          |
+-----+-----------+-----------------+-------------+---------+------+-------------------+-----------------------------------------------+
| 227 | dba_admin | 127.0.0.1:51627 | db_name     | Query   |   14 | copy to tmp table | ALTER TABLE `table_name` drop column field_type |
| 255 | dba_admin | 127.0.0.1:52565 | NULL        | Query   |    0 | NULL              | show processlist                              |
+-----+-----------+-----------------+-------------+---------+------+-------------------+-----------------------------------------------+
2 rows in set (0.00 sec)

可以看到，直接进行alter table add的操作，会导致锁表3min24s左右，这对线上业务的影响是非常大的，而直接进行alter table drop的操作也是一样，会造成线上的服务不可用。

值得注意的一点是，在进行alter table的过程中，可以看到show processlist的state字段提示是“copy to tmp table”,也就是alter table操作会将当前的表拷贝到一个临时的表结构中，有个数据中转的过程。

使用pt-osc工具修改表结构

pt-osc工具是PT工具包里面的一种，它的全称是pt-online-schema-change，看这个名字，不难猜出来，它是为了在线修改表结构来才创建出来的，所谓的在线修改表，也就是不影响线上业务从而实现修改表结构的效果。

pt-osc工具的工作原理及步骤 ： 1. 创建需要执行alter操作的原表的一个临时表，然后在临时表中更改表结构。 2. 在原表中创建触发器（3个）三个触发器分别对应insert,update,delete操作 3. 从原表拷贝数据到临时表，拷贝过程中在原表进行的写操作都会更新到新建的临时表。

4. Rename 原表到old表中，在把临时表Rename为原表，最后将原表删除，将原表上所创建的触发器删除。

pt-online-schema-change工具包含了很多参数，我们可以使用--help的方法去查看，这里我不再一一介绍，只说几个常用的：

--alter：

结构变更语句，不需要alter table关键字。可以指定多个更改，用逗号分隔

--alter-foreign-keys-method：

这个参数是用来处理需要修改的表上具有外键的情况的，如果表上有外键，则需要使用该参数来处理，该参数有4个值，分别是auto、rebuild_constraints、drop_swap、none,一般情况下，选用auto即可，默认值也是auto

--execute

确定修改表，则指定该参数。真正执行。

--charset=utf8

使用utf8编码，避免中文乱码

--chunk-size

对每次导入行数进行控制，已减少对原表的锁定时间。

--print

打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句

--user=

连接mysql的用户名

--password=

连接mysql的密码

--host=

连接mysql的地址

P=

连接mysql的端口号

D=

连接mysql的库名

t=

连接mysql的表名

--recursion-method

发现从的方法， 默认是show processlist，可以指定none来不检查Slave

这里，我打印出来了执行表结构修改的pt-online-schema-change命令和日志，并对日志做了一定解释，如下：

[root@ mysql_4308]# pt-online-schema-change --user=dba_admin --password=XXXXXXXXXX -h127.0.0.1 -P4308 --alter " ADD column field_type smallint(4) unsigned NOT NULL DEFAULT '0' " D=db_name,t=table_name --alter-foreign-keys-method=auto --recursion-method=none --print --charset=utf8 --execute  
'#1查看是否有从节点'
No slaves found.  See --recursion-method if host tk-dba-mysql-194 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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

'#2查看是否有外键'
No foreign keys reference `db_name`.`table_name`; ignoring --alter-foreign-keys-method.
Altering `web_service`.`workorder`...

'#3创建新表'
Creating new table...
CREATE TABLE `db_name`.`_table_name_new` (
 XXX
) ENGINE=InnoDB AUTO_INCREMENT=13175873 DEFAULT CHARSET=utf8

'#4修改新表结构'
Created new table  `db_name`.`_table_name_new` OK.
Altering new table...
ALTER TABLE `db_name`.`_table_name_new`  ADD column field_type smallint(4) unsigned NOT NULL DEFAULT '0' 
Altered `db_name`.`_table_name_new` OK.

'#5创建触发器，保证拷贝过程中的数据同步'
2019-10-28T14:34:13 Creating triggers...
2019-10-28T14:34:13 Created triggers OK.
2019-10-28T14:34:13 Copying approximately 7245076 rows...
INSERT LOW_PRIORITY IGNORE INTO `db_name`.`_table_name_new`  SELECT XXXXX FROM `db_name`.`table_name` FORCE INDEX(`PRIMARY`) WHERE XXXXX LOCK IN SHARE MODE /*pt-online-schema-change 46120 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db_name`.`table_name` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

'#6拷贝表数据'
Copying `db_name`.`table_name`:  14% 02:52 remain
Copying `db_name`.`table_name`:  27% 02:38 remain
Copying `db_name`.`table_name`:  38% 02:22 remain
Copying `db_name`.`table_name`:  49% 02:00 remain
Copying `db_name`.`table_name`:  60% 01:37 remain
Copying `db_name`.`table_name`:  71% 01:11 remain
Copying `db_name`.`table_name`:  81% 00:46 remain
Copying `db_name`.`table_name`:  91% 00:21 remain
2019-10-28T14:38:23 Copied rows OK.
2019-10-28T14:38:23 Swapping tables...

'#7.rename表'
RENAME TABLE `db_name`.`table_name` TO `db_name`.`_table_name_old`, `db_name`.`_table_name_new` TO `db_name`.`table_name`
2019-10-28T14:38:24 Swapped original and new tables OK.
2019-10-28T14:38:24 Dropping old table...

'#8.删除旧表'
DROP TABLE IF EXISTS `db_name`.`_table_name_old`
2019-10-28T14:38:24 Dropped old table `db_name`.`_table_name_old` OK.
2019-10-28T14:38:24 Dropping triggers...

'#9.删除触发器'
DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_del`
DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_upd`
DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_ins`
2019-10-28T14:38:24 Dropped triggers OK.
Successfully altered `db_name`.`table_name`.

简单总结一下

1、除了上面使用的参数外，pt-online-schema-change还有很多参数，我们可以使用--help的命令来查看。 2、从变更结果来看，变更的时间大约在3分钟，变更的过程中会利用百分比的方式输出进度，这一点比较友好。 3、除此之外，我们需要知道pt-online-schema-change并不能缩短表变更的时间，它只是会减少表变更过程中对线上带来的影响，让本来会锁表的操作变成平滑的操作，业务没有感知。 4、在线变更的过程并不能保证完全没有问题，如果在变更的过程中，原表又发生了alter table的DDL操作，或者出现大事务的回滚操作，那么结果是不确定的，所以，最好的方法还是在业务的低峰期去做比较合适。