优化MySQL 5.5版本普通索引的增删性能
本节主要内容：
MySQL 5.5版本对普通索引的增删性能的优化。
说明：
传说5.5对于非聚集索引添加、删除性能上做了很大改善，在5.5之前版本中，ADD INDEX,DROP INDEX 需要拷贝整个表的，这样在生产环境上修改索引带来的风险很大。
即便性能提高了，大家还是要慎重考虑索引的使用。
下面我们针对这些内容做下测试。
基本表：
`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 |
+———-+
根据不同的索引类型进行测试：
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，效果怎样？继续实验。
`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，建立聚集索引，所以重建数据。
提示：
其中的manage keys 的状态表示The server is enabling or disabling a table index
当second index被create和drop时，该表会被加上SHARE MODE锁，只能读，不能写；
如果cluster index被create和drop时，会被加上exclusive mode锁，任何操作都会被block。
您可能感兴趣的文章：
优化MySQL 5.5版本普通索引的增删性能
mysql索引创建与删除命令
mysql创建与删除索引实例
mysql优化器可选开关用法详解
mysql索引管理 mysql创建索引
mysql索引创建、删除与查看
mysql索引的建立规则
mysql索引的建立原则
mysql索引类型区别分析
mysql 索引优化之btree、hash与rtree