5分钟了解MySQL5.7的Online DDL雷区

MySQL · suifu · 于 1年前发布 · 652 次阅读
Part1:写在最前
Online DDL,当新手听到这个名字的时候,非常高兴,以为无论什么情况下,修改表结构都不会锁表,理想很丰满,现实很骨感!读完本文,教你如何避开这些雷区,安全的修改表结构。话不多说,我们分别来看下MySQL5.6和MySQL5.7在修改表结构上的相同和异同。


Part2:5.6.25的表现
①首先我们构造数据并进行测试
mysql> create database helei;
Query OK, 1 row affected (0.01 sec)
mysql> use helei;
Database changed
mysql> create table helei(
    -> id int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> c1 int(10) NOT NULL DEFAULT '0',
    -> c2 int(10) unsigned DEFAULT NULL,
    -> c5 int(10) unsigned NOT NULL DEFAULT '0',
    -> c3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> c4 varchar(200) NOT NULL DEFAULT '',
    -> PRIMARY KEY(id),
    -> KEY idx_c1(c1),
    -> KEY idx_c2(c2)
    -> )ENGINE=InnoDB ;
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter $$
mysql> drop procedure if exists `insert_helei` $$
and()*row_num),now(),repeat('su', floor(rand()*20)));
set i = i+1;
 END while;
end$$
delimiter ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create procedure `insert_helei`(in row_num int )
    -> begin
    ->  declare i int  default 0;
    ->  while i < row_num do
    -> insert into helei(c1, c2, c5,c3, c4) values( floor(rand()*row_num),floor(rand()*row_num),floor(rand()*row_num),now(),repeat('su', floor(rand()*20)));
    -> set i = i+1;
    ->  END while;
    -> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call insert_helei(1000000);
Query OK, 1 row affected (18 min 52.88 sec)
mysql> desc helei;
+-------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type             | Null | Key | Default           | Extra                       |
+-------+------------------+------+-----+-------------------+-----------------------------+
| id    | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| c1    | int(10)          | NO   | MUL | 0                 |                             |
| c2    | int(10) unsigned | YES  | MUL | NULL              |                             |
| c5    | int(10) unsigned | NO   |     | 0                 |                             |
| c3    | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| c4    | varchar(200)     | NO   |     |                   |                             |
+-------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.01 sec)
mysql> alter table helei add c6 varchar(60) not null default '';
Query OK, 0 rows affected (9.66 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select count(*) from helei;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.14 sec)
mysql> alter table helei ALGORITHM=INPLACE,modify c6 varchar(80) not null default '';
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> alter table helei modify c6 varchar(80) not null default '';
Query OK, 1000000 rows affected (9.44 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
Tips:心得
可以看出5.6.25这里并不支持ALGORITHM,而直接对表进行修改的话用了9.44s。
②接下来我们测试下变更varchar长度是否锁表
mysql> alter table helei modify c6 varchar(40) not null default '';
Query OK, 1000000 rows affected (9.26 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
mysql> update helei set c1=9999 where id=1;
Query OK, 0 rows affected (7.77 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> show processlist;
+----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+
| Id | User | Host      | db    | Command | Time | State                           | Info                                                        |
+----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+
|  6 | root | localhost | helei | Query   |    2 | copy to tmp table               | alter table helei modify c6 varchar(40) not null default '' |
|  7 | root | localhost | helei | Query   |    1 | Waiting for table metadata lock | update helei set c1=9999 where id=1                         |
|  8 | root | localhost | NULL  | Query   |    0 | init                            | show processlist                                            |
+----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
可以看到在对c6字段修改表结构时,对c1字段无法更新,被锁
mysql> alter table helei add c7 varchar(60) not null default '';
Query OK, 0 rows affected (8.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> update helei set c1=9999 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
Tips:心得
经测试,增加、删除字段或索引不锁全表,删除主键锁全表
Part3:5.7.15的表现
mysql> select count(*) from helei;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.11 sec)
mysql> alter table helei ALGORITHM=INPLACE,modify c6 varchar(80) not null default '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Tips:心得
可以看到,这里用时0.01sec,和5.6的9.44sec效率差了相当多吧~

Warning:警告1
不过,ALGORITHM用法只对varcahr类型有效哦,比如我们对c1列int型进行变更:
c1原为int(10) 变更为int(11) unsigned则无效
mysql> alter table helei ALGORITHM=INPLACE,modify c1 int(11) unsigned not null;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Tips:心得
只变更int的位数,是可以的,不过这没什么意义,因为无论你int多少,最多都只能存10位,这也就是为什么我们生产库开发规范要定义所有的int都用int(10)。
生产库开发规范详见:
http://suifu.blog.51cto.com/9167728/1792604


Warning:警告2
如果字段属性大于并等于varchar(256)(这里的256是指字节(UTF8占用3字节)或者把varchar(80)减少到varchar(70)或者更少),则仍需要拷贝数据且锁全表。
mysql> alter table helei ALGORITHM=INPLACE,modify c6 varchar(40) not null default '';
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> alter table helei ALGORITHM=INPLACE,modify c6 varchar(70) not null default '';
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
Warning:警告3
添加字段alter table时,对该表的增删改查均不会锁表。而在这之前,当该表被访问时,需要等其执行完毕后才可以执行alter table。




——总结——
在varchar变更字段长度方面,5.7的新特性ALGORITHM参数可以快速调整varchar类型的字段长度。5.7同5.6一样,增加,删除字段或索引不锁全表,删除主键锁全表。因此,在上线时,一定要执行show processlist命令并观察,此刻是否有某个慢SQL对该表进行操作,以免alter table表时出现锁表现象。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。

本文出自 “岁伏” 博客,请务必保留此出处http://suifu.blog.51cto.com/9167728/1855872

本文由 suifu 创作,采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用,但需署名作者且注明文章出处。


如果这篇文章对您有帮助,不妨微信小额赞助我一下,让我有动力继续写出高质量的帖子。

共收到 3 条回复 mysql5.7 online ddl
ruyi#11年前 0 个赞

inplace算法在5.6已经支持了吧。

suifu#211个月前 0 个赞

@ruyi #1楼 mysql> alter table helei ALGORITHM=INPLACE,modify c6 varchar(80) not null default ''; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 5.6.25报错的说~

ruyi#311个月前 0 个赞

我之前测试加字段 索引都是可以的,可能是你这种场景不能用

回复本帖 (需要登录)