mysql 5.5 对于非聚集索引增删操作方式的变更和优化

来源：互联网发布：纸箱厂软件编辑：程序博客网时间：2024/05/16 17:32

聚集索引和非聚集索引的区别理解

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;

+----+------+-----------+------+---------+------+-------------------+-------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+-------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+-------------------------------------------+

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;

+----+------+-----------+------+---------+------+-------------+-------------------------------------------+

+----+------+-----------+------+---------+------+-------------+-------------------------------------------+

+----+------+-----------+------+---------+------+-------------+-------------------------------------------+

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;

+----+------+-----------+------+---------+------+-------------+--------------------------------------+

+----+------+-----------+------+---------+------+-------------+--------------------------------------+

+----+------+-----------+------+---------+------+-------------+--------------------------------------+

相比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;

+----+------+-----------+------+---------+------+-------------+---------------------------------------+

+----+------+-----------+------+---------+------+-------------+---------------------------------------+

+----+------+-----------+------+---------+------+-------------+---------------------------------------+

2 rows in set (0.00 sec)

mysql> 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;

+----+------+-----------+------+---------+------+-------------------+---------------------------------+

+----+------+-----------+------+---------+------+-------------------+---------------------------------+

+----+------+-----------+------+---------+------+-------------------+---------------------------------+

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;

+----+------+-----------+------+---------+------+-------------+------------------------------------------------+

+----+------+-----------+------+---------+------+-------------+------------------------------------------------+

+----+------+-----------+------+---------+------+-------------+------------------------------------------------+

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;

+----+------+-----------+------+---------+------+-------------------+---------------------------------+

+----+------+-----------+------+---------+------+-------------------+---------------------------------+

+----+------+-----------+------+---------+------+-------------------+---------------------------------+

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;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| 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;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| 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;

+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+

+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+

+----+------+-----------+------+---------+------+---------------------------------+---------------------------------------+

3 rows in set (0.00 sec)

0 0