mysql 5.6/5.7 online ddl的add/drop index实验

实验的目的是对比mysql 5.6的online ddl功能，验证5.6以后的版本在进行部分DDL的同时，另外一个session不影响其dml。

old_alter_table默认是关闭的，我们将它设为on来验证一下不用online ddl的时候ddl会不会阻塞dml语句：

先建立一个比较大的表，保证session1的ddl语句还没有结束的时候，同时打开session2的dml语句

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.39    |
+-----------+
1 row in set (0.00 sec)

mysql> create database l5m;
Query OK, 1 row affected (0.00 sec)

mysql> use l5m
Database changed
mysql> create table test_emp( 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.03 sec)

mysql> delimiter //
mysql> create procedure insert_test_emp(in row_num int )
    -> begin
    -> declare i int  default 0;
    -> while i < row_num do
    -> insert into test_emp(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> call insert_test_emp(100000);
    -> //
Query OK, 1 row affected (2 min 36.41 sec)



mysql> delimiter ;

session1:

mysql>  show variables like "old_alter_table";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_alter_table | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> set old_alter_table=1;
Query OK, 0 rows affected (0.01 sec)

mysql>  show variables like '%commit%';  --确保autocommit是off的。
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | OFF   |
| binlog_order_commits           | ON    |
| innodb_api_bk_commit_interval  | 5     |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
5 rows in set (0.00 sec)

mysql> alter table test_emp drop index idx_c2;  

在altert语句还没有结束的时候迅速打开session2.

Session 2:

mysql> update test_emp set c4='bb' where id=1; --这个时候是阻塞的，在等待session的ddl结束

Session 3:

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                   |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
|  2 | root | localhost | l5m  | Query   |    4 | copy to tmp table               | alter table test_emp drop index idx_c2 |
|  3 | root | localhost | l5m  | Query   |    3 | Waiting for table metadata lock | update test_emp set c4='bb' where id=1 |
|  4 | root | localhost | NULL | Query   |    0 | init                            | show processlist                       |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+

通过show processlist可以看到update语句正在等待获得MDL，ddl语句正在用copy的方式 进行alter操作。

继续测试一下新的online ddl功能：

session 1:

mysql>  set old_alter_table=0;
Query OK, 0 rows affected (0.14 sec)

mysql> alter table test_emp drop index idx_c2;--索引被秒删了
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_emp add index idx_c2(c2);-建立索引的时候还是需要时间的

这时立即打开session 2

Session 2:

mysql> update test_emp set c4='bb' where id=1; --并没有被ddl阻塞
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Session 3:

mysql> show processlist;  --第一次show processlist的进修ddl语句在online ddl的第二阶段，也就是execut阶段，没有发现任何阻塞
+----+------+-----------+------+---------+------+----------------+-------------------------------------------+
| Id | User | Host      | db   | Command | Time | State          | Info                                      |
+----+------+-----------+------+---------+------+----------------+-------------------------------------------+
|  2 | root | localhost | l5m  | Query   |    6 | altering table | alter table test_emp add index idx_c2(c2) |
|  3 | root | localhost | l5m  | Sleep   |    4 |                | NULL                                      |
|  4 | root | localhost | NULL | Query   |    0 | init           | show processlist                          |
+----+------+-----------+------+---------+------+----------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> show processlist;--ddl快结束的时候发现在等待获得MDL，在Online ddl的最后一个阶段是需要获得MDL的X锁，需要等session2的dml语句结束
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                      |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
|  2 | root | localhost | l5m  | Query   |   20 | Waiting for table metadata lock | alter table test_emp add index idx_c2(c2) |
|  3 | root | localhost | l5m  | Sleep   |   18 |                                 | NULL                                      |
|  4 | root | localhost | NULL | Query   |    0 | init                            | show processlist                          |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)
S

Session 2:

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session 2提交之后，ddl语句顺序结束 。所以online ddl是可以保证不因为ddl语句而影响了dml语句的执行，不一直独占X锁，只是ddl语句开始和结束的时候短暂的获取X锁。

Session 1:

mysql> alter table test_emp add index idx_c2(c2);
Query OK, 0 rows affected (2 min 29.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

在ddl语句还没有结束的时候，如果有其它的session再来访问或者操作test_emp表，则会被阻塞。所以即使是Online DDL，正式环境也绝对不能在业务繁忙的时候进行。

下面就是如果有新事务进来的情况：

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                    |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------+
|  2 | root | localhost | l5m  | Sleep   |    7 |                                 | NULL                                                    |
|  3 | root | localhost | l5m  | Query   |    6 | Waiting for table metadata lock | alter table test_emp add index idx_c2(c2)               |
|  6 | root | localhost | NULL | Query   |    0 | init                            | show processlist                                        |
|  7 | root | localhost | l5m  | Query   |    6 | Waiting for table metadata lock | select * from test_emp where c3='long select after ddl' |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------------------------+

官方文档介绍，当添加列是auto-increment，不允许DML并发的。

接着同样对mysql5.7做了同样的试验，5.7和5.6的结果是一样的。