最近经常在线上经常遇到有性能问题的SQL，有些表没有合理添加索引，有些表添加的索引不合理，各种各样的问题，导致SQL的执行效率不高。这时DBA们不得不重构SQL，使其达到最好的性能，这时我们往往要在线添加或者删除索引、字段等等的操作。如果是MySQL 5.5的版本在DDL方面是要付出代价的，虽然已经有了Fast index Creation，但是在添加字段还是会锁表的，而且在添加删除辅助索引是会加S锁，也就是无法进行写操作。所以，这里就有相关工具的出现，那就是pt-online-schema-change和oak-online-alter-table，都实现了Online DDL，但是每个工具都有相应自己的限制。如果是MySQL5.6以及更高的版本，已经开始支持Online DDL,我们下面的研究，针对MySQL5.6之前的版本。
主要介绍两个工具：oak-online-alter-table & pt-online-schema-change
一、oak-online-alter-table安装及使用
openark工具包是一组用于MySQL的实用工具，该工具集解决日常维护任务，这些工作比较复杂和耗时。其中oak-online-alter-table就是该工具集中的一个工具，该工具执行非阻塞ALTER TABLE的操作。当然还有其他的工具，有关openark工具说明可以参考http://code.openark.org/forge/openark-kit
1）安装openark工具包（安装依赖）
[root@xuanzhi-mysql ~]# yum install python-mysqldb MySQL-python -y
2）openark软件包下载：（一般只有海外服务器才可以直接在服务器上下载，下载个包相信难不了大家的）
[root@xuanzhi-mysql ~]# wget https://openarkkit.googlecode.com/files/openark-kit-196-1.noarch.rpm
3）openark的安装：
[root@xuanzhi-mysql ~]# rpm -ivh openark-kit-196-1.noarch.rpm Preparing... ########################################### [100%] 1:openark-kit ########################################### [100%] [root@xuanzhi-mysql ~]#
该工具提供了以下三种基本功能：
1、一个非阻塞ALTER TABLE操作，以下几种情况都是支持的
1）添加列 (新列必须有一个默认值)
2）删除列 (旧表必须有一个单列的唯一索引)
3）修改列 (改变字段类型,包括唯一键的列)
4）添加索引 (普通索引,唯一索引,全文索引)
5）删除索引(旧表必须有一个单列的唯一索引)
6) 修改表引擎:当处理非事务性引擎应该格外注意
7）添加外键约束
2、(可能会在未来版本不再支持):创建一个镜像表,与原始表同步,只要不发生如下操作:
1）对原始表ALTER TABLE操作
2）对原始表TRUNCATE操作
3）使用LOAD DATA INFILE向原始表导入数据
4）对原始表OPTIMIZE TABLE操作
3、一个空的ALTER,重建一个表:释放磁盘空间和重新组织表，相当于优化表。
oak-online-alter-table的一些使用限制进行说明：
1、在该表上面至少有一个单列的UNIQUE KEY
2、更改原始表为单个字段的唯一索引
3、该表没有定义触发器“AFTER”（oak会自己创建触发器）
4、应该没有外键约束（FOREIGN KEY）
5、表名长度不超过57个字符
oak-online-alter-table的工作原理：
该工具运行时，它允许INSERT，UPDATE，DELETE，REPLACE原始表，但是不允许TRUNCATE,ALTER,REPAIR OPTIMIZE或者其他方式对原表进行操作。该工具适用于InnoDB表，MyISAM表，或以其他任何表级锁的存储引擎（MEMORY, ARCHIVE）。该工具工作原理是创建一个镜像表的同时，它慢慢与原始表同步。直到同步完成，要做到这一点，该工具必须在原始表创建AFTER INSERT, AFTER UPDATE, AFTER DELETE触发器。镜像表与原始表同步发生在几个步骤。在这些步骤中，数据被从原始表复制到镜像表。这是以行块进行，这个大小是可以用chunk-size选项配置的。当一个块被复制，在（MyISAM，ARCHIVE,MEMORY）存储引擎上有读锁，或包含在该块上面的行记录（innodb），较小的块——更快的锁被移除,允许更大的并发性。对于写密集型应用，它可能是可取的，允许对块之间的停顿，以使尽可能减少影响。这可以使用sleep-ratio选项进行配置。而块之间停顿时没有被加锁。即便如此，对性能的影响是在运行应用程序时，这是由于触发器被添加到表上和DML语句在向镜像表同步。它需要有足够的磁盘空间来容纳改变的表（如一个正常的ALTER TABLE）。在操作完成时才出现磁盘空间恢复（取决于你的存储引擎和配置）。
测试如下：
1、添加一个字段
[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.film --alter="ADD COLUMN name VARCHAR(64) DEFAULT ''" -- Connecting to MySQL -- Table sakila.film is of engine innodb -- ERROR: Errors found. Initiating cleanup -- Tables unlocked -- ERROR: Table must not have any 'AFTER' triggers defined. [root@xuanzhi-mysql ~]#
很明显提示有触发器，也是上面提到的使用限制。
[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.actor --alter="ADD COLUMN name VARCHAR(64) DEFAULT ''" -- Connecting to MySQL -- Table sakila.actor is of engine innodb -- ERROR: Errors found. Initiating cleanup -- Tables unlocked -- ERROR: Table must not have any foreign keys defined (neither as parent nor child).
提示了有外键约束，无论是父表还是子表 ，如果有外键的话，都是不允许添加字段的。
下面给t1表添加一个辅助索引看看
mysql> show create table t1G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>
在name字段添加普通索引：
[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.t1 --alter="ADD KEY(name)" -- Connecting to MySQL -- Table sakila.t1 is of engine innodb -- Checking for UNIQUE columns on sakila.t1, by which to chunk -- Possible UNIQUE KEY column names in sakila.t1: -- ERROR: Errors found. Initiating cleanup -- Tables unlocked -- ERROR: Table must have a UNIQUE KEY on a single column [root@xuanzhi-mysql ~]#
可以看到提示说没有唯一键，也是上面提及到的使用限制。我们添加一个唯一键再看看：
mysql> alter table t1 add unique key (id); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.t1 --alter="ADD KEY(name)" -- Connecting to MySQL -- Table sakila.t1 is of engine innodb -- Checking for UNIQUE columns on sakila.t1, by which to chunk -- Possible UNIQUE KEY column names in sakila.t1: -- - id -- Table sakila.__oak_t1 has been created -- Table sakila.__oak_t1 has been altered -- Checking for UNIQUE columns on sakila.__oak_t1, by which to chunk -- Possible UNIQUE KEY column names in sakila.__oak_t1: -- - id -- Checking for UNIQUE columns on sakila.t1, by which to chunk -- - Found following possible unique keys: -- - id (int) -- Chosen unique key is 'id' -- Shared columns: id, name -- Created AD trigger -- Created AU trigger -- Created AI trigger -- Attempting to lock tables -- Tables locked WRITE /usr/local/bin/oak-online-alter-table:84: Warning: No data - zero rows fetched, selected, or processed num_affected_rows = cursor.execute(query) -- id (min, max) values: ([None], [None]) -- Tables unlocked -- Table sakila.t1 has been renamed to sakila.__arc_t1, -- and table sakila.__oak_t1 has been renamed to sakila.t1 -- Table sakila.__arc_t1 was found and dropped -- ALTER TABLE completed [root@xuanzhi-mysql ~]#
可以看到已经添加成功了，我们查看下表结构，是否真的成功了：（上面的输出有一个警告，不用理会，是因为我是空表，没有记录）
mysql> show create table t1G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` char(20) DEFAULT NULL, UNIQUE KEY `id` (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>
在添加字段时如果设置了NOT NULL，但没有给默认值，也会报警告
[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=sakila.t1 --alter="ADD COLUMN sex VARCHAR(64) not null" -- Connecting to MySQL -- Table sakila.t1 is of engine innodb -- Checking for UNIQUE columns on sakila.t1, by which to chunk -- Possible UNIQUE KEY column names in sakila.t1: -- - id -- Table sakila.__oak_t1 has been created -- Table sakila.__oak_t1 has been altered -- Checking for UNIQUE columns on sakila.__oak_t1, by which to chunk -- Possible UNIQUE KEY column names in sakila.__oak_t1: -- - id -- Checking for UNIQUE columns on sakila.t1, by which to chunk -- - Found following possible unique keys: -- - id (int) -- Chosen unique key is 'id' -- Shared columns: salarey, id, name -- Created AD trigger -- Created AU trigger -- Created AI trigger -- Attempting to lock tables -- Tables locked WRITE -- id (min, max) values: ([1L], [1L]) -- Tables unlocked -- - Reminder: altering sakila.t1: ADD COLUMN sex VARCHAR(64) not... -- Copying range (1), (1), progress: 100% /usr/local/bin/oak-online-alter-table:84: Warning: Field 'sex' doesn't have a default value num_affected_rows = cursor.execute(query) -- Copying range 100% complete. Number of rows: 1 -- - Reminder: altering sakila.t1: ADD COLUMN sex VARCHAR(64) not... -- Deleting range (1), (1), progress: 100% -- Deleting range 100% complete. Number of rows: 0 -- Table sakila.t1 has been renamed to sakila.__arc_t1, -- and table sakila.__oak_t1 has been renamed to sakila.t1 -- Table sakila.__arc_t1 was found and dropped -- ALTER TABLE completed [root@xuanzhi-mysql ~]#
mysql> desc t1; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(20) | YES | MUL | NULL | | | sex | varchar(64) | NO | | NULL | | +---------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql>
oak-online-alter-table还有更多的操作，同学们自己测试哈。
下面测试一下各种操作是否会锁表，这也是我们最关心的问题。
1、我们直接在mysql终端直接执行添加字段看看有什么情况：
mysql> show processlist; +----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------+ | 25 | root | localhost | distribute | Query | 53 | copy to tmp table | alter table test1 add age int(3) | | 26 | root | localhost | distribute | Query | 28 | Waiting for table metadata lock | insert into test1(name,code) values ('xuanhi','aa102') | | 29 | root | localhost | distribute | Query | 0 | NULL | show processlist | +----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------+ 3 rows in set (0.00 sec)
相信大家都看到，在添加表字段的时候，会把整个表锁住，所以看到session2窗口插入数据时，要等待释放锁。
2、我们再测试上添加普通索引看看会不会锁表：
可以看到session2窗口也一样要等待的。如果对一个很大的表直接进行添加或者删除字段和索引的时候，会阻塞所有对该表的DML操作的，所以在线上谨慎直接添加或者删除字段和索引
我们用oak-online-alter-table
[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=distribute.test1 --alter="ADD COLUMN address VARCHAR(64)" ..... ...... -- Deleting range (6999930), (7000930), progress: 99% -- Deleting range (7000930), (7001930), progress: 99% -- Deleting range (7001930), (7003029), progress: 99% -- Deleting range (7003029), (7004073), progress: 99% -- Deleting range (7004073), (7005073), progress: 99% -- Deleting range (7005073), (7006073), progress: 99% -- Deleting range (7006073), (7007898), progress: 99% -- Deleting range (7007898), (7009605), progress: 99% -- Deleting range (7009605), (7011189), progress: 99% -- - Reminder: altering distribute.test1: ADD COLUMN address VARCHAR(64)... -- Deleting range (7011189), (7012194), progress: 99% -- Deleting range (7012194), (7013286), progress: 99% -- Deleting range (7013286), (7014146), progress: 99% -- Deleting range (7014146), (7014146), progress: 100% -- Deleting range 100% complete. Number of rows: 0 -- Table distribute.test1 has been renamed to distribute.__arc_test1, -- and table distribute.__oak_test1 has been renamed to distribute.test1 -- Table distribute.__arc_test1 was found and dropped -- ALTER TABLE completed
在seesion2进行DML操作：
mysql> delete from test1 where name="xuanhi4"; Query OK, 1 row affected (2.69 sec) mysql> insert into test1(name,code) values ('xuanhi4','aa1333'); Query OK, 1 row affected (0.04 sec) mysql> select * from test1 where code='aa1333'; +---------+---------+--------+ | id | name | code | +---------+---------+--------+ | 7014150 | xuanhi4 | aa1333 | +---------+---------+--------+ 1 row in set (1.12 sec) mysql> update test1 set name="xuanhi5" where id=7014150; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
可以看到执行成功的返回时间都是很少的，执行操作的时候并没有锁等待。
我们添加索引看看，是否要锁等待：
[root@xuanzhi-mysql ~]# oak-online-alter-table -uroot -p123456 -S /data/mysql-5.5.40/mysql.sock --table=distribute.test1 --alter="ADD KEY (name,age)" ... .... ...... -- Deleting range (7004073), (7005073), progress: 99% -- Deleting range (7005073), (7006073), progress: 99% -- Deleting range (7006073), (7007898), progress: 99% -- Deleting range (7007898), (7009605), progress: 99% -- Deleting range (7009605), (7011189), progress: 99% -- - Reminder: altering distribute.test1: ADD KEY (name,age)... -- Deleting range (7011189), (7012194), progress: 99% -- Deleting range (7012194), (7013286), progress: 99% -- Deleting range (7013286), (7014151), progress: 99% -- Deleting range (7014151), (7014151), progress: 100% -- Deleting range 100% complete. Number of rows: 0 -- Table distribute.test1 has been renamed to distribute.__arc_test1, -- and table distribute.__oak_test1 has been renamed to distribute.test1 -- Table distribute.__arc_test1 was found and dropped -- ALTER TABLE completed
show processlist 查看是否有锁表：
mysql> show processlist; +----+------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | 25 | root | localhost | distribute | Sleep | 544 | | NULL | | 26 | root | localhost | distribute | Query | 1 | NULL | show processlist | | 29 | root | localhost | distribute | Sleep | 1260 | | NULL | | 31 | root | localhost | distribute | Query | 1 | Sending data | INSERT IGNORE INTO distribute.__oak_test1 (`age`, `code`, `id`, `name`, `address`) (SELE | +----+------+-----------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ 4 rows in set (0.45 sec) mysql>
可以看到使用工具在线添加索引和字段都不会锁表，有兴趣的朋友，可以测试删除字段和索引的，其实一样的，我这里就不测试了。mysql5.6虽然支持了Online DDL,但mysql 5.6 Online DDL的时候测试过如果在执行alter table之前已经有一个慢查询或者结果集比较大的查询，那么此时执行ALTER TABLE是会导致锁表的,用oak-online-alter-table也是一样的要等待锁的释放，本人已经测试过。（mysql5.6 online ddl的博客下次有空会补上）
mysql> show processlist; +----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ | 25 | root | localhost | distribute | Query | 0 | NULL | show processlist | | 26 | root | localhost | distribute | Sleep | 961 | | NULL | | 29 | root | localhost | distribute | Query | 19 | Sending data | select * from test1 | | 32 | root | localhost | distribute | Query | 6 | Waiting for table metadata lock | CREATE TRIGGER distribute.test1_AD_oak AFTER DELETE ON distribute.test1 FOR EACH ROW | +----+------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+ 4 rows in set (0.25 sec) mysql>
oak-online-alter-table工具的使用，暂时说到这里，有兴趣的朋友可以参考官网
http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html（自备梯子）
二、pt-online-schema-change
该工具是percona-toolkit工具包中其中的一个工具，简单说和oak-online-alter-table有着一样的功能。都是实现在线架构改变的工具。其他的我就不多说了。重点说工作原理和注意事项。
（1）安装依赖包：
[root@xuanzhi-mysql ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes -y
（2）下载软件：
[root@xuanzhi-mysql ~]# wget http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.8-1.noarch.rpm
（3）安装软件
[root@xuanzhi-mysql ~]# rpm -ivh percona-toolkit-2.2.8-1.noarch.rpm warning: percona-toolkit-2.2.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... ########################################### [100%] 1:percona-toolkit ########################################### [100%] [root@xuanzhi-mysql ~]#
大概工作原理如下：
（1）如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，针对相应的设置进行处理。
（2）创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。
（3）创建触发器，在复制数据开始之后，将对源数据表继续进行数据修改的操作记录下来，以便在数据复制结束后执行这些操作，保证数据不会丢失。
（4）复制数据，从源数据表中复制数据到新表中。
（5）修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
（6）更改源数据表为old表，把新表改为源表名，并将old表删除。
（7）删除触发器。
存在如下限制：
（1）对操作的表必须要有主键或者唯一键
（2）增加的字段如果为NOT NULL，会报错，需要添加默认值才可以成功。
测试：
使用方法：
[root@xuanzhi-mysql ~]# pt-online-schema-change
Usage: pt-online-schema-change [OPTIONS] DSN
测试表test2结构如下：
mysql> show create table test2G *************************** 1. row *************************** Table: test2 Create Table: CREATE TABLE `test2` ( `id` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>
给表test2添加一个字段:
[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column name char(20)" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock D=sakila,t=test2 --execute No slaves found. See --recursion-method if host redis-master has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `sakila`.`test2`... Creating new table... Created new table sakila._test2_new OK. Altering new table... Altered `sakila`.`_test2_new` OK. 2015-05-27T22:48:07 Dropping new table... 2015-05-27T22:48:07 Dropped new table OK. `sakila`.`test2` was not altered. The new table `sakila`.`_test2_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger. [root@xuanzhi-mysql ~]#
可以发现提示表没有主键或者唯一键，所以添加失败。添加主键以后再进行测试。
mysql> alter table test2 add primary key (id); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column name char(20)" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock D=sakila,t=test2 --execute No slaves found. See --recursion-method if host redis-master has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `sakila`.`test2`... Creating new table... Created new table sakila._test2_new OK. Altering new table... Altered `sakila`.`_test2_new` OK. 2015-05-27T22:51:54 Creating triggers... 2015-05-27T22:51:54 Created triggers OK. 2015-05-27T22:51:54 Copying approximately 1 rows... 2015-05-27T22:51:54 Copied rows OK. 2015-05-27T22:51:54 Swapping tables... 2015-05-27T22:51:54 Swapped original and new tables OK. 2015-05-27T22:51:54 Dropping old table... 2015-05-27T22:51:54 Dropped old table `sakila`.`_test2_old` OK. 2015-05-27T22:51:54 Dropping triggers... 2015-05-27T22:51:54 Dropped triggers OK. Successfully altered `sakila`.`test2`. [root@xuanzhi-mysql ~]#
可以看到添加了主键后，就成功添加了。但是设置NOT NULL，但是不给默认值，看看神马情况
[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column last_name char(20) not null" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock D=sakila,t=test2 --execute No slaves found. See --recursion-method if host redis-master has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `sakila`.`test2`... Creating new table... Created new table sakila._test2_new OK. Altering new table... Altered `sakila`.`_test2_new` OK. 2015-05-27T23:00:36 Creating triggers... 2015-05-27T23:00:36 Created triggers OK. 2015-05-27T23:00:36 Copying approximately 1 rows... 2015-05-27T23:00:36 Dropping triggers... 2015-05-27T23:00:36 Dropped triggers OK. 2015-05-27T23:00:36 Dropping new table... 2015-05-27T23:00:36 Dropped new table OK. `sakila`.`test2` was not altered. 2015-05-27T23:00:36 Error copying rows from `sakila`.`test2` to `sakila`.`_test2_new`: 2015-05-27T23:00:36 Copying rows caused a MySQL error 1364: Level: Warning Code: 1364 Message: Field 'last_name' doesn't have a default value Query: INSERT LOW_PRIORITY IGNORE INTO `sakila`.`_test2_new` (`id`, `age`, `name`) SELECT `id`, `age`, `name` FROM `sakila`.`test2` LOCK IN SHARE MODE /*pt-online-schema-change 4369 copy table*/ [root@xuanzhi-mysql ~]#
可以看到没有给默认值的情况下，添加字段失败，下面给一个默认值再看看：
[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column last_name char(20) not null default 'xuanzhi'" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock D=sakila,t=test2 --execute No slaves found. See --recursion-method if host redis-master has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `sakila`.`test2`... Creating new table... Created new table sakila._test2_new OK. Altering new table... Altered `sakila`.`_test2_new` OK. 2015-05-27T23:03:22 Creating triggers... 2015-05-27T23:03:22 Created triggers OK. 2015-05-27T23:03:22 Copying approximately 1 rows... 2015-05-27T23:03:22 Copied rows OK. 2015-05-27T23:03:22 Swapping tables... 2015-05-27T23:03:22 Swapped original and new tables OK. 2015-05-27T23:03:22 Dropping old table... 2015-05-27T23:03:22 Dropped old table `sakila`.`_test2_old` OK. 2015-05-27T23:03:22 Dropping triggers... 2015-05-27T23:03:22 Dropped triggers OK. Successfully altered `sakila`.`test2`. [root@xuanzhi-mysql ~]#
可以看见已经成功了，相信更多的小伙伴想看看对大表添加字段和索引会不会锁表，莫急，下面我们一起来道道：
下面对一个有六百多万行数据的测试表进行添加字段看是否会锁表：（如果想自己造数据的同学可以用sysbench生成的1000w数据）
mysql> show create table xuanzhiG *************************** 1. row *************************** Table: xuanzhi Create Table: CREATE TABLE `xuanzhi` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` varchar(32) DEFAULT NULL, `age` int(3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code_index` (`code`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=7014157 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select count(*) from xuanzhi; +----------+ | count(*) | +----------+ | 6862437 | +----------+ 1 row in set (0.03 sec) mysql>
测试1、在线添加字段并查看是否有阻塞DML操作
[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="add column name char(20) not null default 'man'" --user=root --password=123456 --socket=/data/mysl-5.5.40/mysql.sock D=sakila,t=xuanzhi --execute
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `sakila`.`xuanzhi`...
Creating new table...
Created new table sakila._xuanzhi_new OK.
Altering new table...
Altered `sakila`.`_xuanzhi_new` OK.
2015-05-28T01:38:44 Creating triggers...
2015-05-28T01:38:44 Created triggers OK.
2015-05-28T01:38:44 Copying approximately 6862437 rows...
Copying `sakila`.`xuanzhi`: 29% 01:13 remain
Copying `sakila`.`xuanzhi`: 51% 00:55 remain
Copying `sakila`.`xuanzhi`: 66% 00:45 remain
Copying `sakila`.`xuanzhi`: 73% 00:43 remain
Copying `sakila`.`xuanzhi`: 78% 00:40 remain
Copying `sakila`.`xuanzhi`: 85% 00:31 remain
Copying `sakila`.`xuanzhi`: 91% 00:20 remain
Copying `sakila`.`xuanzhi`: 95% 00:10 remain
2015-05-28T01:43:07 Copied rows OK.
2015-05-28T01:43:07 Swapping tables...
2015-05-28T01:43:21 Swapped original and new tables OK.
2015-05-28T01:43:21 Dropping old table...
2015-05-28T01:43:21 Dropped old table `sakila`.`_xuanzhi_old` OK.
2015-05-28T01:43:21 Dropping triggers...
2015-05-28T01:43:21 Dropped triggers OK.
Successfully altered `sakila`.`xuanzhi`.
[root@xuanzhi-mysql ~]#
可以看到并没有阻塞DML操作，上面执行成功的返回时间有点久，可以忽略，因为在虚拟机测试生成的报告，所以同学们懂的！！
测试2：在线添加索引，看是否阻塞DML操作
[root@xuanzhi-mysql ~]# pt-online-schema-change --alter="ADD KEY (name)" --user=root --password=123456 --socket=/data/mysql-5.5.40/mysql.sock D=sakila,t=xuanzhi --execute No slaves found. See --recursion-method if host redis-master has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `sakila`.`xuanzhi`... Creating new table... Created new table sakila._xuanzhi_new OK. Altering new table... Altered `sakila`.`_xuanzhi_new` OK. 2015-05-28T01:53:59 Creating triggers... 2015-05-28T01:53:59 Created triggers OK. 2015-05-28T01:53:59 Copying approximately 6862438 rows... Copying `sakila`.`xuanzhi`: 15% 02:38 remain Copying `sakila`.`xuanzhi`: 30% 02:19 remain Copying `sakila`.`xuanzhi`: 42% 02:00 remain Copying `sakila`.`xuanzhi`: 52% 01:49 remain Copying `sakila`.`xuanzhi`: 60% 01:38 remain Copying `sakila`.`xuanzhi`: 67% 01:28 remain Copying `sakila`.`xuanzhi`: 72% 01:22 remain Copying `sakila`.`xuanzhi`: 77% 01:10 remain Copying `sakila`.`xuanzhi`: 83% 00:54 remain Copying `sakila`.`xuanzhi`: 88% 00:39 remain Copying `sakila`.`xuanzhi`: 93% 00:21 remain Copying `sakila`.`xuanzhi`: 99% 00:02 remain 2015-05-28T02:00:05 Copied rows OK. 2015-05-28T02:00:05 Swapping tables... 2015-05-28T02:00:22 Swapped original and new tables OK. 2015-05-28T02:00:22 Dropping old table... 2015-05-28T02:00:22 Dropped old table `sakila`.`_xuanzhi_old` OK. 2015-05-28T02:00:22 Dropping triggers... 2015-05-28T02:00:22 Dropped triggers OK. Successfully altered `sakila`.`xuanzhi`. [root@xuanzhi-mysql ~]#
相信大家都看到了吧，在线添加索引，对DML操作是木有阻塞的。最后查看一下表结构：
mysql> show create table xuanzhiG *************************** 1. row *************************** Table: xuanzhi Create Table: CREATE TABLE `xuanzhi` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` varchar(32) DEFAULT NULL, `age` int(3) DEFAULT NULL, `name` char(20) NOT NULL DEFAULT 'man', PRIMARY KEY (`id`), UNIQUE KEY `code_index` (`code`) USING BTREE, KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=7014161 DEFAULT CHARSET=utf8 1 row in set (0.08 sec) mysql>
成功添加了name字段，成功的在列name上添加了普通索引。
测试3：测试一下在执行alter table之前有一个大的查询，看是否导致锁等待，这个在MySQL 5.6以及oak-online-alter-table都有这个问题
在session1窗口：
mysql> select * from xuanzhi;
在session2和session3窗口查看：
并没有发现锁表的情况，但在网上看到的资料是会锁表的，希望同学们也自己测试下，大家一起学习哈！！
总结：
一、如果要在线上进行Online DDL操作，一定要做好数据备份，要考虑数据的大小情况及主从框架中如果从是提供读的，要考虑到延时等问题。
二、pt-online-schema-change比oak-online-alter-table工具更好用，并且存在的限制较少，oak-online-alter-table对有外键的表是没有办法的
三、已经执行了大的查询，这时恰好执行ALTER TABLE操作，都会导致锁表，MySQL5.6版本也有这样的情况。
四、MySQL5.6版本已经支持Online DDL了，这是一个不错的改进。如果对一个大数据表DDL，一般选择避开业务高峰期执行，所以还是要在业务量较低且没有大查询时执行Online DDL
参考资料：
https://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html（自备梯子）
https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
http://www.cnblogs.com/gomysql/p/3777607.html
|
作者：陆炫志 出处：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111 您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。 |