MySQL 5.6 Online DDL.md

一 .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://www.cnblogs.com/gomysql/p/3776192.html
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版》

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 140,587评论 1 295
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 60,384评论 1 254
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 92,492评论 0 206
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 40,584评论 0 171
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 48,270评论 1 250
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 38,517评论 1 167
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 30,227评论 2 266
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,001评论 0 161
  • 想象着我的养父在大火中拼命挣扎,窒息,最后皮肤化为焦炭。我心中就已经是抑制不住地欢快,这就叫做以其人之道,还治其人...
    爱写小说的胖达阅读 28,705评论 6 223
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 32,339评论 0 211
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,105评论 2 211
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 30,432评论 1 222
  • 白月光回国,霸总把我这个替身辞退。还一脸阴沉的警告我。[不要出现在思思面前, 不然我有一百种方法让你生不如死。]我...
    爱写小说的胖达阅读 24,131评论 0 31
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 26,950评论 2 208
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 31,335评论 3 200
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,571评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 25,897评论 0 163
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 33,295评论 2 226
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 33,414评论 2 228

推荐阅读更多精彩内容