MySQL 5.6 Online DDL
一 .Fast index Creation
MySQL 5.5和更高版本并且MySQL 5.1 innodb plugin支持Fast index Creation,对于之前的版本对于索引的添加或删除这类DDL操作,MySQL数据库的操作过程为如下:
(1)首先创建新的临时表,表结构通过命令ALTAR TABLE新定义的结构
(2)然后把原表中数据导入到临时表
(3)删除原表
(4)最后把临时表重命名为原来的表名
上述过程我们不难发现,若我们对一张大表进行索引的添加或者删除,需要很长的时间,致命的是若有大量的访问请求,意味着无法提供服务。
innodb存储引擎从1.0.x版本开始支持Fast index Creation(快速索引创建)。简称FIC。对于辅助索引的创建,会对创建索引的表加一个S锁。在创建的过程中,不需要重建表,因此速度有明显提升。对于删除辅助索引innodb存储引擎只需要更新内部视图,并将辅助索引的空间标记为可用,同时删除MySQL 数据库内部视图上对该表的索引定义即可。特别需要注意的时,临时表的创建路径是通过参数tmpdir设置的。必须确保tmpdir有足够的空间,否则将会导致辅助索引创建失败。由于在创建辅助索引时加的是S锁,所以在这过程中只能对该表进行读操作,若有事务需要对该表进行写操作,那么数据库服务同样不可用。需要注意的是,FIC方式只限定于辅助索引,对于主键的创建和删除同样需要重建一张表。
二 . Oline Schema Change
Online Schema Change(在线架构改变,简称OSC),最早是由Facebook实现的一种在线DDL的方式。所谓"在线"是指在添加字段,添加索引这类DDL操作时,事务对表的读写操作不会受到阻塞。
三 .Online DDL
FIC可以让innodb存储引擎避免创建临时表,提高索引创建效率。虽然FIC不会阻塞读操作,但是DML操作还是照样阻塞的。MySQL 5.6版本开始支持Online DDL(在线数据定义)操作,其允许辅助索引创建的同时,还允许其他诸如INSERT,UPDATE,DELETE这类DML操作。此外不仅是辅助索引,以下这几类DDL操作都可以通过”在线“的方式进行:
(1)辅助索引的创建于删除
(2)改变自增长值
(3)添加或删除外键约束
(4)列的重命名
通过新的ALTER TABLE,可以选择索引的创建方式
mysql [localhost] {msandbox} ((none)) > select version(); +-----------+ | version() | +-----------+ | 5.6.19 | +-----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) >
? alter table | ALGORITHM [=] {DEFAULT|INPLACE|COPY} | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
ALGORITHM指定了创建或删除索引的算法,COPY表示按照MySQL 5.1版本之前的方法,即创建临时表。INPLACE表示创建索引或删除索引操作不需要创建临时表。DEFAULT表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法,改参数默认为OFF,表示采用INPLACE的方式
mysql [localhost] {msandbox} ((none)) > show variables like ‘%old_alter%‘; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | old_alter_table | OFF | +-----------------+-------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) >
LOCK部分为索引创建或删除时对表添加锁的情况,可选择的如下:
(1)NONE,执行索引创建或者删除操作时,对目标表不添加任何锁,即事务仍然可以进行读写操作,不会收到阻塞,该模式可以获得最大的并发。
(2)SHARE,和Fast index Creation类似,执行索引创建或删除操作时,对目标表加一个S锁。对于并发读事务,依然可以执行。但是遇到写事务,将会发生等待操作,如果存储引擎不支持SHARE模式,将返回一个错误信息。
(3)EXCLUSIVE,执行索引创建或删除时,对目标表加上一个X锁。读写事务均不能进行。会阻塞所有的线程。这和COPY方式类似,但是不需要像COPY方式那样创建一张临时表。
(4)DEFAULT,该模式首先会判断当前操作是否可以使用NONE模式,若不能,则判断是否可以使用SHARE模式,最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式。
innodb存储引擎实现Online DDL的原理是在执行创建或者删除操作同时,将INSERT,UPDATE,DELETE这类DML操作日志写入到一个缓存中,待完成索引创建后再将重做应用到表上,以此达到数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制,默认大小为128MB。
mysql [localhost] {msandbox} ((none)) > show variables like ‘%online%‘; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | innodb_online_alter_log_max_size | 134217728 | +----------------------------------+-----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) > select 134217728 / 1024 / 1024; +-------------------------+ | 134217728 / 1024 / 1024 | +-------------------------+ | 128.00000000 | +-------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} ((none)) >
如果待更新的表比较大,并且创建过程中有大量的写事务,如果遇到innodb_online_alter_log_max_size的空间不能存放日志时,会抛出相应的错误,这个我们后面进行测试。
如果遇到改错误,我们可以调大该参数,以此获得更大的日志缓存空间。此外我们可以设置ALTER
TABLE的模式为SHARE,这样在执行过程中不会有写事务发生。因此不需要进行DML日志的记录。
通过上面的简单说明,相信大家心里都有谱了。那我们来实际测试一下。我这里使用sysbench生成1000w行测试数据
[root@mysql-server ~]# sysbench --test=oltp --oltp-table-size=10000000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=1800 --mysql-user=msandbox --mysql-socket=/tmp/mysql_sandbox5619.sock --mysql-password=msandbox --db-driver=mysql --mysql-table-engine=innodb --oltp-test-mode=complex prepare
1.首先测试添加一个辅助索引
在session 1中执行添加索引操作,在session 2中执行DML操作;
session 1 (alter table选择默认的执行方式,即让innodb存储引擎自行判断该加什么锁)
mysql [localhost] {msandbox} (sbtest) > select count(*) from sbtest; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (2.28 sec) mysql [localhost] {msandbox} (sbtest) > show create table sbtest\G *************************** 1. row *************************** Table: sbtest Create Table: CREATE TABLE `sbtest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT ‘0‘, `c` char(120) NOT NULL DEFAULT ‘‘, `pad` char(60) NOT NULL DEFAULT ‘‘, PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=latin1 1 row in set (0.04 sec) mysql [localhost] {msandbox} (sbtest) >
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add key idx_pad ( pad );
session 2(可以发现并未锁表,一切正常)
mysql [localhost] {msandbox} (sbtest) > delete from sbtest where id=10; Query OK, 1 row affected (0.16 sec) mysql [localhost] {msandbox} (sbtest) > show processlist; +----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+ | 17 | msandbox | localhost | sbtest | Query | 4 | altering table | alter table sbtest add key idx_pad ( pad ) | | 18 | msandbox | localhost | sbtest | Query | 0 | init | show processlist | +----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (sbtest) > update sbtest set k=11 where id=100; Query OK, 1 row affected (1.20 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql [localhost] {msandbox} (sbtest) > show processlist; +----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+ | 17 | msandbox | localhost | sbtest | Query | 53 | altering table | alter table sbtest add key idx_pad ( pad ) | | 18 | msandbox | localhost | sbtest | Query | 0 | init | show processlist | +----+----------+-----------+--------+---------+------+----------------+--------------------------------------------+ 2 rows in set (0.26 sec) mysql [localhost] {msandbox} (sbtest) >
2.测试添加一个字段
session 1
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add age int after pad;
session 2
mysql [localhost] {msandbox} (sbtest) > delete from sbtest where id=20; Query OK, 1 row affected (1.02 sec) mysql [localhost] {msandbox} (sbtest) > update sbtest set k=101 where id=1111; Query OK, 1 row affected (1.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql [localhost] {msandbox} (sbtest) > show processlist; +----+----------+-----------+--------+---------+------+----------------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+--------+---------+------+----------------+------------------------------------------+ | 23 | msandbox | localhost | sbtest | Query | 120 | altering table | alter table sbtest add age int after pad | | 24 | msandbox | localhost | sbtest | Query | 1 | init | show processlist | +----+----------+-----------+--------+---------+------+----------------+------------------------------------------+ 2 rows in set (0.38 sec) mysql [localhost] {msandbox} (sbtest) >
可以发现添加字段依然不会影响DML操作。是不是很爽?爽的话就升级吧。
如果我们在mysql 5.5中添加字段会是怎样的情况呢?在mysql
5.5中添加字段是会锁表的,读写都阻塞(增加,删除索引会加S锁,阻塞写操作)。如果还没有使用mysql
5.6的同学也不用担心,因为目前有两个工具非常好用,一个是oak-online-alter-table,另外一个是pt-online-schema-change。后续我会说明这两个工具的用法以及一些注意事项。现在来看看mysql
5.5添加字段的情况
mysql> select version(); +------------+ | version() | +------------+ | 5.5.37-log | +------------+ 1 row in set (0.03 sec) mysql>
mysql> alter table sbtest add address char(30) after pad;
另外一个会话查看
mysql> show processlist; +----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+ | 9 | root | localhost | sbtest | Query | 6 | copy to tmp table | alter table sbtest add address char(30) after pad | | 10 | root | localhost | sbtest | Query | 4 | Waiting for table metadata lock | delete from sbtest where id=100 | | 11 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------------------+ 3 rows in set (0.00 sec) mysql>
可以看见锁表了,并且在创建临时表。
不过MySQL 5.6不是一定不会锁表,有种特殊情况,那就是如果有一条大结果的查询在查询某个表,这时如果执行ALTER TABLE时,是会锁表的。我们做一个简单测试。
session 1
mysql [localhost] {msandbox} (sbtest) > select * from sbtest;
session 2
mysql [localhost] {msandbox} (sbtest) > alter table sbtest add age int after pad;
session 3
mysql [localhost] {msandbox} (sbtest) > show processlist; +----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+ | 27 | msandbox | localhost | sbtest | Query | 5 | Sending data | select * from sbtest | | 28 | msandbox | localhost | sbtest | Query | 3 | Waiting for table metadata lock | alter table sbtest add age int after pad | | 29 | msandbox | localhost | sbtest | Query | 0 | init | show processlist | +----+----------+-----------+--------+---------+------+---------------------------------+------------------------------------------+ 3 rows in set (0.22 sec) mysql [localhost] {msandbox} (sbtest) >
可以看见已经导致锁表咯。所以,我们在上线的时候,一定要观察是否有某个慢SQL或者比较大的结果集的SQL在运行,否则在执行ALTER TABLE时将会导致锁表发生。当然不清楚oak-online-alter-table和pt-online-schema-change是否有这个限制。抽时间需要测试一下。
参考资料:
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
http://www.mysqlperformanceblog.com/2014/02/26/monitor-alter-table-progress-innodb_file_per_table/
《MySQL技术内幕--innodb存储引擎第2版》
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。