优化MySQL 5.5版本普通索引的增删性能

发布时间:2020-09-19编辑:脚本学堂
本文介绍下,在mysql5.5数据库中,对普通索引的增删性能进行优化的方法,有需要的朋友参考下吧。

本节主要内容:
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,建立聚集索引,所以重建数据。

提示:
其中的manage keys 的状态表示The server is enabling or disabling a table index
当second index被create和drop时,该表会被加上SHARE MODE锁,只能读,不能写;
如果cluster index被create和drop时,会被加上exclusive mode锁,任何操作都会被block。