实验的目的是对比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的结果是一样的。