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

原创作品，允许转载，转载时请务必以超链接形式标明文章  原始出处 、作者信息和本声明。否则将追究法律责任。 http://suifu.blog.51cto.com/9167728/1855872

 

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

转载于:https://www.cnblogs.com/suifu/p/5908199.html