IT技术互动交流平台

MySQL Online DDL 黄金准则

作者:茁壮的小草  来源:IT165收集  发布日期:2016-12-19 20:31:58

作为一名DBA,对数据库进行DDL操作非常多,如添加索引,添加字段等等。对于MySQL数据库,DDL支持的并不是很好,一不留心就导致了全表被锁,经常搞得刚入门小伙伴很郁闷又无辜,不是说MySQL支持Online DDL么,不是说不会锁表的么?是的,令人高兴的是从MySQL5.6开始就支持部分DDL Online操作了,但并不是全部喔,今天这里就对我们常用的DDL进行总结和说明,让操作DDL的小伙伴从此做到心中有数,得心应手,让老板们再也不用担心我们做DDL咯。

我自己遵守的一条黄金准则:DDL永远不要在业务高峰期间执行

环境说明:本次的测试服务器配置如下

      CPU:32 cores
      MEM:128G
      DISK: SSD(固态硬盘)
      MySQL版本:5.6.27以上

一、MySQL执行DDL原理

     MySQL各版本,对于DDL的处理方式是不同的,主要有三种:

Copy Table方式: 这是InnoDB最早支持的方式。顾名思义,通过临时表拷贝的方式实现的。新建一个带有新结构的临时表,将原表数据全部拷贝到临时表,然后Rename,完成创建操作。这个方式过程中,原表是可读的,不可写。但是会消耗一倍的存储空间。 Inplace方式:这是原生MySQL 5.5,以及innodb_plugin中提供的方式。所谓Inplace,也就是在原表上直接进行,不会拷贝临时表。相对于Copy Table方式,这比较高效率。原表同样可读的,但是不可写。 Online方式:这是MySQL 5.6以上版本中提供的方式,也是今天我们重点说明的方式。无论是Copy Table方式,还是Inplace方式,原表只能允许读取,不可写。对应用有较大的限制,因此MySQL最新版本中,InnoDB支持了所谓的Online方式DDL。与以上两种方式相比,online方式支持DDL时不仅可以读,还可以写,对于dba来说,这是一个非常棒的改进。

二、常用DDL执行方式总结

操作 支持方式 Allow R/W 说明
add/create index online 允许读写 FULLTEXT索引除外,需要锁表,阻塞写
add fulltext index in-place 仅支持读,阻塞写 创建表上第一个fulltext index用copy table方式,除非表上有FTS_DOC_ID列。之后创建fulltext index用in-place方式,经过测试验证,第一次时5.6 innodb会隐含自动添加FTS_DOC_ID列,也就是5.6都是in-place方式
drop index online 允许读写 操作元数据,不涉及表数据。所以很快,可以放心操作
optimize table in-place or copy table(fulltext) 允许读写or阻塞写(fulltext) 5.6.17版本以上才支持inplace,之前版本是copy-table;当带有fulltext index的表用copy table方式并且阻塞写
alter table...engine=innodb in-place or copy table(fulltext) 允许读写or阻塞写(fulltext) 5.6.17版本以上才支持inplace,之前版本是copy-table,当带有fulltext index的表用copy table方式并且阻塞写
add column in-place 允许读写,(增加自增列除外) 1、添加auto_increment列要锁表,阻塞写;2、虽采用in-place方式,但是表数据需要重新组织,所以增加列依然是昂贵的操作,小伙伴尤其注意啦(测试已验证)
drop column in-place 允许读写(增加自增列除外) 同add column,重新组织表数据,,昂贵的操作
Rename a column online 允许读写 操作元数据;不能改列的类型,否则就锁表(已验证)
Reorder columns in-place 允许读写 重新组织表数据,昂贵的操作
Make column NOT NULL in-place 允许读写 重新组织表数据,昂贵的操作
Change data type of column copy table 仅支持读,阻塞写 创建临时表,复制表数据,昂贵的操作(已验证)
Set default value for a column online 允许读写 操作元数据,因为default value存储在frm文件中,不涉及表数据。所以很快,可以放心操作
alter table xxx auto_increment=xx online 允许读写 操作元数据,不涉及表数据。所以很快,可以放心操作
Add primary key in-place 允许读写 昂贵的操作(已验证)
Convert character set copy table 仅支持读,阻塞写 如果新字符集不同,需要重建表,昂贵的操作

 【注】:红色部分都需要注意的操作,会影响线上数据库性能

二、测试常用DDL执行方式

测试用表:表大小70M,行数13659 初始表结构:
CREATE TABLE `t_mysql` (
  `checksum` bigint(20) unsigned NOT NULL,
  `sample` text NOT NULL,
  `content` text ,
  `content1` text ,
  `content2` text ,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
测试机器开启profiling:
root:test> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

1、add fulltext index

1) 用例1:该语句执行期间是否锁表?

开两个session。session 1:创建fulltext index

dbadmin:test> alter table t_mysql add fulltext index idx_1(sample);
执行中.......

session 2:进行insert数据,会一直等待中,阻塞写了

【结论1】:创建全文索引时,仅支持读,阻塞写;dba小伙伴加索引时要注意啦,而且执行时间超级超级长,在执行ddl时,尽量不要手动kill,可能会导致异常,这里有个知识点。

2) 用例2:创建表上第一个fulltext index用copy table方式,除非表上有FTS_DOC_ID列。之后创建fulltext index用in-place方式?

创建第一个全文索引:
root:test> alter table t_mysql add fulltext index idx_1(sample);
Query OK, 0 rows affected, 1 warning (15.21 sec)
Records: 0  Duplicates: 0  Warnings: 1

这个时候发现0 rows affected,也就是说没有用copy table方式。这是为什么,官方文档上说第一个全文索引采用copy table方式的,难道官方文档上错误?再看下执行过程:

root:test> show profile for query 10;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000378 |
| checking permissions           |  0.000038 |
| checking permissions           |  0.000035 |
| init                           |  0.000032 |
| Opening tables                 |  0.000101 |
| setup                          |  0.000079 |
| creating table                 |  0.001043 |
| After create                   |  0.000217 |
| System lock                    |  0.000031 |
| preparing for alter table      |  0.023248 |
| altering table                 | 15.164399 |
| committing alter table to stor |  0.016108 |
| end                            |  0.000043 |
| query end                      |  0.000327 |
| closing tables                 |  0.000021 |
| freeing items                  |  0.000081 |
| logging slow query             |  0.000121 |
| cleaning up                    |  0.000060 |
+--------------------------------+-----------+
18 rows in set, 1 warning (0.00 sec)

在这上面也没有发现copy tmp table字样,说明确实没有进行表copy。等等,在上面执行建全文索引时,有一个warning,看下这个warning:

root:test> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

到这里就明白了,原来当我们建第一个全文索引时,5.6以上版本innodb会默认的为我们自动添加FTS_DOC_ID,这样就避免了copy table了,所以相对会快些。

【结论2】:5.6以上版本innodb会默认的为我们自动添加FTS_DOC_ID,所以第一次创建全文索引时避免了copy table。我们可以自此认为5.6以上版本创建全文索引都是in-place方式。

2、optimize table & alter table...engine=innodb

注:测试前清除上面创建的全文索引,恢复表为初始

1) 用例:该语句执行期间是否锁表

1.1)不存在全文索引:

session1执行:

root:test> alter table t_mysql engine=innodb;
Query OK, 0 rows affected (1.38 sec) #没有数据受影响
Records: 0  Duplicates: 0  Warnings: 0

session2同时执行:

dbadmin:test> insert into t_mysql values(0113,'测试全文索引','darrenllllllllllllll');
Query OK, 1 row affected (0.14 sec)

当表上不存在全文索引时,optimize table 或者 alter table t_mysql engine=innodb 很快执行完成,并且不阻塞写;

1.2)存在全文索引时:

 步骤一:添加全文索引

CREATE TABLE `t_mysql` (
  `checksum` bigint(20) unsigned NOT NULL,
  `sample` text NOT NULL,
  `content` text,
  FULLTEXT KEY `idx_1` (`sample`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

步骤二:session1:执行optimize table或者alter table ... engine=innodb

root:test> alter table t_mysql engine=innodb;
执行中.......

Query OK, 13661 rows affected (42.13 sec)   #说明进行copy table数据了
Records: 13661  Duplicates: 0  Warnings: 0
root:test> show profile for query 14;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000355 |
| checking permissions |  0.000071 |
| Opening tables       |  0.000151 |
| System lock          |  0.000188 |
| init                 |  0.000027 |
| Opening tables       |  0.000958 |
| setup                |  0.000062 |
| creating table       |  0.001235 |
| After create         |  0.000127 |
| System lock          |  0.045863 |
| copy to tmp table    | 43.937449 |
| rename result table  |  0.529001 |
| end                  |  0.000172 |
| Opening tables       |  0.000759 |
| System lock          |  0.002615 |
| query end            |  0.000402 |
| closing tables       |  0.000011 |
| freeing items        |  0.000022 |
| cleaning up          |  0.000033 |
+----------------------+-----------+

session 2:模拟插入数据:

dbadmin:test> insert into t_mysql values(0113,'测试全文索引','darrenllllllllllllll'); 

等待中.......

当表上存在全文索引时,我们执行optimize table 或者 alter table t_mysql engine=innodb 采用copy table方式,而且锁全表,阻塞写;

 【结论1】:当表上不存在全文索引时,optimize table 或者 alter table t_mysql engine=innodb 采用in-place方式,并且不阻塞写;

                 当表上存在全文索引时,我们执行optimize table 或者 alter table t_mysql engine=innodb 采用copy table方式,而且锁全表,阻塞写;

 3、add column

 1)用例1:添加auto_increment列要锁表,阻塞写?

 session 1 :

root:test> alter table t_mysql add column id int not null primary key auto_increment;
Query OK, 0 rows affected (1.41 sec)

session 2:

dbadmin:test> insert into t_mysql(checksum,sample,content) values(0113,'测试全文索引','darrenllllllllllllll');

waitting......
......
......
......
......
......
Query OK, 1 row affected (0.97 sec)

当添加自增列时,会阻塞写。

 2)用例2:添加普通列,online?

session 1:

root:test> alter table t_mysql add column content1 text;
Query OK, 0 rows affected (1.36 sec)  #in-place方式
Records: 0  Duplicates: 0  Warnings: 0

session 2:

dbadmin:test> insert into t_mysql(checksum,sample,content) values(0113,'测试全文索引','darrenllllllllllllll');
Query OK, 1 row affected (0.01 sec)

当添加一个普通列时,是online的,不阻塞写入。

4、change column type

session 1:

root:test> alter table t_mysql change content1 content1 longtext;  
Query OK, 13674 rows affected (1.37 sec)  # copy table
Records: 13674  Duplicates: 0  Warnings: 0
root:test> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000302 |
| checking permissions | 0.000027 |
| checking permissions | 0.000045 |
| init                 | 0.000024 |
| Opening tables       | 0.000097 |
| setup                | 0.000067 |
| creating table       | 0.001379 |
| After create         | 0.000165 |
| System lock          | 0.004105 |
| copy to tmp table    | 1.327642 |  #copy table
| rename result table  | 0.034565 |
| end                  | 0.000473 |
| query end            | 0.001067 |
| closing tables       | 0.000263 |
| freeing items        | 0.000414 |
| logging slow query   | 0.000478 |
| cleaning up          | 0.001074 |
+----------------------+----------+

session 2:并发DML

dbadmin:test> insert into t_mysql(checksum,sample,content1) values(0113,'测试全文索引','darrenllllllllllllll');

WAITTING.......
.......
.......
.......
.......
.......
Query OK, 1 row affected (0.95 sec)

【结论】:修改列类型DDL采用copy table方式并且阻塞写入,在线上操作必须谨慎再谨慎!

以上就是我经常进行线上的DDL操作了,如果还有其他DDL请查看下面的官方链接。从此,DBA小伙伴进行DDL操作不再侥幸也不再盲目,做到心中有杆秤。

另外,我的一些建议:

 1、尽量不要在业务高峰期间进行DDL,即使是online DDL;

 2、对于大表(G级别)DDL,最好在测试库上做一遍,预估下时间,不至于到线上执行时心慌手乱;(线上和测试环境数据量差不多)

 3、小心使得万年船,谨记上面博主为大家总结的”黄金准则”;

【参考文档】

 http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

 http://www.cnblogs.com/cchust/p/4639397.html

延伸阅读:

Tag标签: 准则   黄金  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规