MySQL 5.5版本对普通索引增删性能的优化
导读：
传说5.5对于非聚集索引添加、删除性能上做了很大改善，在5.5之前版本中，ADD INDEX,DROP INDEX 需要拷贝整个表的，这样在生产环境上修改索引带来的风险很大。即便性能提高了，大家还是要慎重考虑索引的使用。先来实验下
基本表：
CREATE TABLE `task` (
`UID` bigint(20) unsigned NOT NULL DEFAULT ’0′,
`TDID` int(10) unsigned NOT NULL,
`s` tinyint(3) unsigned NOT NULL DEFAULT ’0′,
`date` int(8) unsigned DEFAULT ’0′,
KEY `TDID` (`TDID`),
KEY `UID` (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
root@localhost : test 02:51:10> SELECT COUNT(*) FROM task;
+———-+
| COUNT(*) |
+———-+
| 1773940 |
+———-+
根据不同的索引类型进行测试
Gereral Index
root@localhost : test 05:53:16> ALTER TABLE task ADD KEY UID(UID);
Query OK, 0 rows affected (7.15 sec)
SHOW PROCESSLIST
| 33 | root | localhost | test | Query | 5 | manage keys | ALTER TABLE task ADD KEY UID(UID) |
root@localhost : test 02:52:26> ALTER TABLE task DROP INDEX UID;
Query OK, 0 rows affected (0.08 sec)
UK
root@localhost : test 05:58:00> ALTER TABLE task ADD UNIQUE KEY UK_UT(UID,TDID,date);
Query OK, 0 rows affected (7.99 sec)
SHOW PROCESSLIST
| 33 | root | localhost | test | Query | 4 | manage keys | ALTER TABLE task ADD UNIQUE KEY UK_UT(UID,TDID,date) |
root@localhost : test 05:58:11> ALTER TABLE task DROP INDEX UK_UT;
Query OK, 0 rows affected (0.09 sec)
看到如此的变化，有什么感受呢？以后不必担心生产线上更改一个索引耗时太久。之前ALTER TABLE ADD \DROP INDEX 进行经过这样的过程创建带有索引的新表—>从旧表COPY到新表à删除旧表àrename新表;但是5.5目前流程是：删除INNODB系统表与索引有关的数据，并且删除Mysql数据字典中于索引有关的数据就可以了，空间会被INNODB回收，以便于新建的表和索引直接使用。添加索引必须要扫描所有行，并且按照键值在Memory buffer和tempfile排序
当然这只是普通索引，其实还有PK,UK，效果怎样？继续实验。
CREATE TABLE `task` (
`UID` bigint(20) unsigned NOT NULL DEFAULT ’0′,
`TDID` int(10) unsigned NOT NULL,
`s` tinyint(3) unsigned NOT NULL DEFAULT ’0′,
`date` int(8) unsigned NOT NULL DEFAULT ’0′,
KEY `TDID` (`TDID`),
KEY `UID` (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PK
ALTER TABLE task ADD PRIMARY KEY(UID,TDID,date);
Query OK, 0 rows affected (19.89 sec)
SHOW PROCESSLIST;
|33 | root | localhost | test | Query | 19 | manage keys | ALTER TABLE task ADD PRIMARY KEY(UID,TDID,date)
ALTER TABLE task DROP PRIMARY KEY;
Query OK, 1773940 rows affected (16.07 sec)
SHOW PROCESSLIST;
| 33 | root | localhost | test | Query | 3 | copy to tmp table | ALTER TABLE task DROP PRIMARY KEY |
UK
ALTER TABLE task ADD UNIQUE KEY UK_UT(UID,TDID,date);
Query OK, 0 rows affected (27.08 sec)
show processlist;
| 33 | root | localhost | test | Query | 8 | manage keys | ALTER TABLE task ADD UNIQUE KEY UK_UT(UID,TDID,date) |
ALTER TABLE task DROP INDEX UK_UT;
Query OK, 1773940 rows affected (20.76 sec)
show processlist;
| 33 | root | localhost | test | Query | 9 | copy to tmp table | ALTER TABLE task DROP INDEX UK_UT |
两种情况都耗时很长啊！看看是何原因？对于聚集索引的重建涉及到数据的问题，必须新建表并COPY数据，并且更新Second index数据。
而上面看到的两个UK，主要是因为UK字段如果not null ，Mysql将会按照UK去建立聚集索引，第一中情况date为null ，所以为普通索引；第二种情况date为not null，建立聚集索引，所以重建数据
提示：