mysql 5.7 online ddl_MySQL数据库之5分钟了解MySQL5.7的Online DDL雷区

本文主要向大家介绍了MySQL数据库之5分钟了解MySQL5.7的Online DDL雷区 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

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 changedmysql> 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: 0mysql> 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=INPLACE，而直接对表进行修改的话用了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: 0mysql> update helei set c1=9999 where id=1;Query OK, 0 rows affected (7.77 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> 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: 0mysql> update helei set c1=9999 where id=1;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0

Tips:心得

经测试，增加、删除字段或索引不锁全表，变更字段长度，锁表。

本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！