但如果在alter之前有大事务在执行，会阻塞ddl以及后续的所有请求：
12
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 **SESSION1**
mysql> select * from sbtest1 where c='long select before alter';
Empty set (4.36 sec)
**SESSION2**
mysql> alter table sbtest1 add index k_1(k);
Query OK, 0 rows affected (16.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
**SESSION3**
mysql> select * from sbtest1 where c='long select after alter execution but not complete';
Empty set (5.89 sec)
**SESSION4**
mysql> show processlist;
+----+-----------------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------+
| 5 | root | localhost | confluence | Query | 3 | Sending data | select * from sbtest1 where c='long select before alter' |
| 7 | root | localhost | NULL | Query | 0 | init | show processlist |
| 13 | root | localhost | confluence | Query | 2 | Waiting for table metadata lock | alter table sbtest1 add index k_1(k) |
| 14 | root | localhost | confluence | Query | 1 | Waiting for table metadata lock | select * from sbtest1 where c='long select after alter execution but not complete' |
+----+-----------------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
3.2 添加列示例
添加新列是ddl操作里面相对较多的一类操作。从上文表中可以看到
12
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 **SESSION1**
mysql> ALTER TABLE `sbtest2` \
ADD COLUMN `f_new_col1` int(11) NULL DEFAULT 0, \
ADD COLUMN `f_new_col2` varchar(32) NULL DEFAULT '' AFTER `f_new_col1`;
Query OK, 0 rows affected (1 min 57.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
**SESSION2**
mysql> update sbtest2 set c="update when add colomun ddl start" where c='33333';
Query OK, 0 rows affected (4.41 sec)
Rows matched: 0 Changed: 0 Warnings: 0
**SESSION3**
mysql> select * from sbtest2 where c='select when add colomun ddl start';
Empty set (3.44 sec)
**SESSION4**
mysql> show processlist;
+-----+-----------------+-----------+------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| 5 | root | localhost | confluence | Query | 4 | altering table | ALTER TABLE `sbtest2` ADD COLUMN `f_new_col1` int(11) NULL DEFAULT 0, ADD COLUMN `f_new_col2` varch |
| 7 | root | localhost | NULL | Query | 0 | init | show processlist |
| 161 | root | localhost | confluence | Query | 2 | Searching rows for update | update sbtest2 set c="update when add colomun ddl start" where c='33333' |
| 187 | root | localhost | confluence | Query | 1 | Sending data | select * from sbtest2 where c='select when add colomun ddl start' |
+-----+-----------------+-----------+------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)