MySQL 5.6 原生Online DDL解析(4)

我这里使用sysbench产生的表测试（500w数据）：

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   mysql> select version();
+------------+
| version() |
+------------+
| 5.6.30-log |
+------------+
1 row in set (0.00 sec)

mysql> show create table sbtest1;
CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin MAX_ROWS=1000000


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

旧模式下，创建删除普通索引：

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   **SESSION1:**
mysql> set old_alter_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table sbtest1 drop index idx_k_1;
Query OK, 5000000 rows affected (44.79 sec)
Records: 5000000 Duplicates: 0 Warnings: 0

mysql> alter table sbtest1 add index idx_k_1(k);
Query OK, 5000000 rows affected (1 min 11.29 sec)
Records: 5000000 Duplicates: 0 Warnings: 0


**SESSION2:**
mysql> select * from sbtest1 limit 1;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 2481886 | 08566691963-88624...106334-50535565977 | 63188288836-9235114...351-49282961843 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update sbtest1 set k=2481885 where id=1;
Query OK, 1 row affected (45.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0


**SESSION3:**
mysql> show processlist;
+--------+-----------------+-----------+------------+---------+--------+---------------------------------+-----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------------+-----------+------------+---------+--------+---------------------------------+-----------------------------------------+
| 118652 | root | localhost | confluence | Query | 19 | copy to tmp table | alter table sbtest1 add index k_1(k) |
| 118666 | root | localhost | confluence | Query | 3 | Waiting for table metadata lock | update sbtest1 set k=2481885 where id=1 |
| 118847 | root | localhost | NULL | Query | 0 | init | show processlist |
+--------+-----------------+-----------+------------+---------+--------+---------------------------------+-----------------------------------------+
4 rows in set (0.00 sec)

同时在datadir目录下可以看到
-rw-rw---- 1 mysql mysql 8.5K May 23 21:24 sbtest1.frm
-rw-rw---- 1 mysql mysql 1.2G May 23 21:24 sbtest1.ibd
-rw-rw---- 1 mysql mysql 8.5K May 23 20:48 #sql-1c6a_1cf7c.frm
-rw-rw---- 1 mysql mysql 638M May 23 20:48 #sql-1c6a_1cf7c.ibd  

传统ddl方式有 copy to tmp table 过程，dml更新操作期间被堵住45s：Waiting for table metadata lock。