相传mysql 5.5 版本对于非聚集索引添加、删除有很大的改善……
5.1.61
在5.1 版本中，add/drop index（包括聚集和非聚集索引），都会先copy 一个 tmp table，如：
CREATE TABLE `t1` (
`col1` int(11) NOT NULL AUTO_INCREMENT,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`col4` int(11) DEFAULT NULL,
`col5` varchar(80) DEFAULT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8
mysql> alter table t1 add index col2_index(col2);
Query OK, 10000000 rows affected (1 min 51.66 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+-------------------------------------------+
| 3 | root | localhost | test | Query | 98 | copy to tmp table | alter table t1 add index col2_index(col2) |
| 4 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------------------+-------------------------------------------+
mysql> alter table t1 drop index col2_index;
Query OK, 10000000 rows affected (52.95 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
5.5.18
传说5.5对于非聚集索引添加、删除性能上做了很大改善，在5.5之前版本中，add/drop index需要拷贝整个表的，接下来测试一下5.5版本……
还是这张表：
CREATE TABLE `t1` (
`col1` int(11) NOT NULL AUTO_INCREMENT,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`col4` int(11) DEFAULT NULL,
`col5` varchar(80) DEFAULT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8
mysql> alter table t1 add index col2_index(col2);
Query OK, 0 rows affected (26.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------+-------------------------------------------+
| 2 | root | localhost | test | Query | 5 | manage keys | alter table t1 add index col2_index(col2) |
| 5 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------------+-------------------------------------------+
mysql> alter table t1 drop index col2_index;
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------+--------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------+--------------------------------------+
| 2 | root | localhost | test | Query | 0 | manage keys | alter table t1 drop index col2_index |
| 5 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------------+--------------------------------------+
相比5.1版本的同样的操作，5.5版本对于非聚集索引，果真很有速度……
接下来，在5.5版本中加主键试试……
原来有主键，先删除：alter table t1 drop primary key;
add primary key
mysql> alter table t1 add primary key (col1);
Query OK, 0 rows affected (1 min 18.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------+---------------------------------------+
| 2 | root | localhost | test | Query | 78 | manage keys | alter table t1 add primary key (col1) |
| 5 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------+---------------------------------------+
| 2 | root | localhost | test | Query | 79 | rename result table | alter table t1 add primary key (col1) |
| 5 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+---------------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> alter table t1 drop primary key;
Query OK, 10000000 rows affected (48.92 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+---------------------------------+
| 2 | root | localhost | test | Query | 8 | copy to tmp table | alter table t1 drop primary key |
| 5 | root | localhost | test | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------------------+---------------------------------+
2 rows in set (0.00 sec)
可见，5.5 版本对于主键索引（聚集索引）的 drop/add 操作 还是需要copy tmp table，跟5.1的操作类似。
接下来，测试一下unique 索引：
上面的t1表的初始化数据有点多了，删除一些，剩下：1000000 rows
在t1表上，添加unique 索引：
mysql> alter table t1 add unique key uk_t1(col3,col4);
Query OK, 0 rows affected (2.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------+------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------+------------------------------------------------+
| 2 | root | localhost | test | Sleep | 569 | | NULL |
| 5 | root | localhost | test | Query | 2 | manage keys | alter table t1 add unique key uk_t1(col3,col4) |
| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------------+------------------------------------------------+
3 rows in set (0.00 sec)
mysql> alter table t1 drop index uk_t1;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
跟上面的非聚集索引（普通索引）添加、删除一样，很有速度……
对t1表结构做一下调整：
mysql> alter table t1 modify col3 int(11) not null DEFAULT '0';
mysql> alter table t1 modify col4 int(11) not null DEFAULT '0';
删除主键索引：
mysql> alter table t1 drop primary key;
Query OK, 1000000 rows affected (4.58 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> show index from t1;
Empty set (0.00 sec)
再对 col3,col4 添加 unique key：
mysql> alter table t1 add unique key uk_t1(col3,col4);
Query OK, 0 rows affected (5.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
再执行删除：
mysql> alter table t1 drop index uk_t1;
Query OK, 1000000 rows affected (4.57 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+---------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+---------------------------------+
| 2 | root | localhost | test | Sleep | 948 | | NULL |
| 5 | root | localhost | test | Query | 3 | copy to tmp table | alter table t1 drop index uk_t1 |
| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------------------+---------------------------------+
3 rows in set (0.01 sec)
这里跟上面的情况不太一样，alter table t1 drop index uk_t1; 出现了 copy tmp table
重新加上unique 索引：
mysql> alter table t1 add unique key uk_t1(col3,col4);
Query OK, 0 rows affected (5.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | uk_t1 | 1 | col3 | A | 1000099 | NULL | NULL | | BTREE | | |
| t1 | 0 | uk_t1 | 2 | col4 | A | 1000099 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
再加上主键索引：
mysql> alter table t1 add primary key (col1);
Query OK, 0 rows affected (7.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | col1 | A | 1000099 | NULL | NULL | | BTREE | | |
| t1 | 0 | uk_t1 | 1 | col3 | A | 1000099 | NULL | NULL | | BTREE | | |
| t1 | 0 | uk_t1 | 2 | col4 | A | 1000099 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
再删除unique 索引
mysql> alter table t1 drop index uk_t1;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
这一步，又很快了……
为何？对于聚集索引的重建涉及到数据的问题，必须新建表并COPY数据，并且更新Second index数据。
而上面看到的两个unique key add/drop 的操作时间不一样，主要是因为unique字段如果not null ，并且原来没有聚集索引，Mysql将会按照unique去建立聚集索引，第一中情况col3、col为null ，所以为普通索引；第二种情况col3、col4 为not null，建立聚集索引，所以重建数据。
上面出现的 manage keys 的状态表示The server is enabling or disabling a table index
当second index被create和drop的时候，该表会被加上SHARE MODE锁，只能读，不能写；如果cluster index被create和drop的时候，会被加上exclusive mode锁，任何操作都会被lock
如在 alter table t1 add primary key (col1) 会阻塞 select * from t1 limit 1000,10 ：
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+
| 2 | root | localhost | test | Query | 34 | manage keys | alter table t1 add primary key (col1) |
| 5 | root | localhost | test | Query | 11 | Waiting for table metadata lock | select * from t1 limit 1000,10 |
| 7 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+
3 rows in set (0.00 sec)
