首页 > 代码库 > 5分钟了解MySQL5.7的Online DDL雷区
5分钟了解MySQL5.7的Online DDL雷区
Part1:写在最前
Online DDL,当新手听到这个名字的时候,非常高兴,以为无论什么情况下,修改表结构都不会锁表,理想很丰满,现实很骨感!读完本文,教你如何避开这些雷区,安全的修改表结构。话不多说,我们分别来看下MySQL5.6和MySQL5.7在修改表结构上的相同和异同。
Part2:5.6.25的表现
①首先我们构造数据并进行测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | 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` $$ 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长度是否锁表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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字段无法更新,被锁
1 2 3 4 5 6 7 | 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的表现
1 2 3 4 5 6 7 8 9 10 | 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则无效
1 2 | 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)或者更少),则仍需要拷贝数据且锁全表。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> alter table helei ALGORITHM=INPLACE, modify c6 varchar (84) not null default ‘‘ ; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table helei ALGORITHM=INPLACE, modify c6 varchar (85) not null default ‘‘ ; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table helei ALGORITHM=INPLACE, modify c6 varchar (86) 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 (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
5分钟了解MySQL5.7的Online DDL雷区