最近经常在线上经常遇到有性能问题的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工具包（安装依赖）
2）openark软件包下载：（一般只有海外服务器才可以直接在服务器上下载，下载个包相信难不了大家的）
3）openark的安装：
该工具提供了以下三种基本功能：
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、添加一个字段
很明显提示有触发器，也是上面提到的使用限制。
提示了有外键约束，无论是父表还是子表 ，如果有外键的话，都是不允许添加字段的。
下面给t1表添加一个辅助索引看看
在name字段添加普通索引：
可以看到提示说没有唯一键，也是上面提及到的使用限制。我们添加一个唯一键再看看：
可以看到已经添加成功了，我们查看下表结构，是否真的成功了：（上面的输出有一个警告，不用理会，是因为我是空表，没有记录）
在添加字段时如果设置了NOT NULL，但没有给默认值，也会报警告
oak-online-alter-table还有更多的操作，同学们自己测试哈。
下面测试一下各种操作是否会锁表，这也是我们最关心的问题。
1、我们直接在mysql终端直接执行添加字段看看有什么情况：
相信大家都看到，在添加表字段的时候，会把整个表锁住，所以看到session2窗口插入数据时，要等待释放锁。
2、我们再测试上添加普通索引看看会不会锁表：
可以看到session2窗口也一样要等待的。如果对一个很大的表直接进行添加或者删除字段和索引的时候，会阻塞所有对该表的DML操作的，所以在线上谨慎直接添加或者删除字段和索引
我们用oak-online-alter-table
在seesion2进行DML操作：
可以看到执行成功的返回时间都是很少的，执行操作的时候并没有锁等待。
我们添加索引看看，是否要锁等待：
show processlist 查看是否有锁表：
可以看到使用工具在线添加索引和字段都不会锁表，有兴趣的朋友，可以测试删除字段和索引的，其实一样的，我这里就不测试了。mysql5.6虽然支持了Online DDL,但mysql 5.6 Online DDL的时候测试过如果在执行alter table之前已经有一个慢查询或者结果集比较大的查询，那么此时执行ALTER TABLE是会导致锁表的,用oak-online-alter-table也是一样的要等待锁的释放，本人已经测试过。（mysql5.6 online ddl的博客下次有空会补上）
oak-online-alter-table工具的使用，暂时说到这里，有兴趣的朋友可以参考官网
二、pt-online-schema-change
该工具是percona-toolkit工具包中其中的一个工具，简单说和oak-online-alter-table有着一样的功能。都是实现在线架构改变的工具。其他的我就不多说了。重点说工作原理和注意事项。
（1）安装依赖包：
（2）下载软件：
（3）安装软件
大概工作原理如下：
（1）如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，针对相应的设置进行处理。
（2）创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。
（3）创建触发器，在复制数据开始之后，将对源数据表继续进行数据修改的操作记录下来，以便在数据复制结束后执行这些操作，保证数据不会丢失。
（4）复制数据，从源数据表中复制数据到新表中。
（5）修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
（6）更改源数据表为old表，把新表改为源表名，并将old表删除。
（7）删除触发器。
存在如下限制：
（1）对操作的表必须要有主键或者唯一键
（2）增加的字段如果为NOT NULL，会报错，需要添加默认值才可以成功。
测试：
使用方法：
测试表test2结构如下：
给表test2添加一个字段:
可以发现提示表没有主键或者唯一键，所以添加失败。添加主键以后再进行测试。
可以看到添加了主键后，就成功添加了。但是设置NOT NULL，但是不给默认值，看看神马情况
可以看到没有给默认值的情况下，添加字段失败，下面给一个默认值再看看：
可以看见已经成功了，相信更多的小伙伴想看看对大表添加字段和索引会不会锁表，莫急，下面我们一起来道道：
下面对一个有六百多万行数据的测试表进行添加字段看是否会锁表：（如果想自己造数据的同学可以用sysbench生成的1000w数据）
测试1、在线添加字段并查看是否有阻塞DML操作
可以看到并没有阻塞DML操作，上面执行成功的返回时间有点久，可以忽略，因为在虚拟机测试生成的报告，所以同学们懂的！！
测试2：在线添加索引，看是否阻塞DML操作
相信大家都看到了吧，在线添加索引，对DML操作是木有阻塞的。最后查看一下表结构：
成功添加了name字段，成功的在列name上添加了普通索引。
测试3：测试一下在执行alter table之前有一个大的查询，看是否导致锁等待，这个在MySQL 5.6以及oak-online-alter-table都有这个问题
在session1窗口：
在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
|
您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。
|