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版》

posted @ 2014-06-08 17:13 yayun 阅读(...) 评论(...) 编辑 收藏