下面改成Online 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
28 **SESSION1**
mysql> set old_alter_table=0;
mysql> alter table sbtest1 drop index k_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
索引秒删
mysql> alter table sbtest1 add index k_1(k);
Query OK, 0 rows affected (13.99 sec)
Records: 0 Duplicates: 0 Warnings: 0
**SESSION2**
mysql> update sbtest1 set k=2481887 where id=1;
Query OK, 1 row affected (0.00 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 | 10 | altering table | alter table sbtest1 add index k_1(k) |
| 118666 | root | localhost | confluence | Sleep | 9 || NULL |
| 118847 | root | localhost | NULL | Query | 0 | init | show processlist |
+--------+-----------------+-----------+------------+---------+--------+------------------------+--------------------------------------+
4 rows in set (0.00 sec)
添加普通索引，并未出现阻塞update操作，而且速度更快。从 rows affected 可以看出有没有copy table。