InnoDB存储引擎下快速创建索引
在MySQL5.0版本前，新增或删除索引会很缓慢，特别是比较大的表。它需要创建一个新的表，然后将旧表中的数据插入到新的表中，更新索引，待这一切完成后再将旧表删掉并把新表的表名改成原来的表名。
从MySQL5.1版本开始，MySQL允许创建或删除索引不必拷贝整个表内容。在MySQL内置版本的InnoDB存储引擎并没有这种性能；InnoDB Plugin版本可以实现。
在InnoDB中，表中行存储在聚集索引或主键索引，称之为索引组织表。不管是内置版本的InnoDB存储引擎还是InnoDB Plugin存储引擎，更改聚集索引或主键都需要复制数据。但是添加或删除InnoDB Plugin辅助索引的速度要快得多，因为它不涉及复制数据。
Built-in InnoDB版本下：
mysql> CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;
mysql> set profiling = 1;
mysql> ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);
mysql> show profile cpu for query 8;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000044 | 0.000000 | 0.000000 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 |
| init | 0.000035 | 0.000000 | 0.000000 |
| Opening tables | 0.000056 | 0.000000 | 0.000000 |
| System lock | 0.000003 | 0.000000 | 0.000000 |
| Table lock | 0.000003 | 0.000000 | 0.000000 |
| setup | 0.000018 | 0.000000 | 0.000000 |
| creating table | 0.003217 | 0.000999 | 0.000000 |
| After create | 0.000047 | 0.000000 | 0.000000 |
| copy to tmp table | 0.000034 | 0.000000 | 0.000000 |
| rename result table | 0.001110 | 0.000000 | 0.000000 |
| end | 0.000038 | 0.000000 | 0.000000 |
| query end | 0.000004 | 0.000000 | 0.000000 |
| freeing items | 0.000127 | 0.000000 | 0.000000 |
| cleaning up | 0.000004 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
删除索引：
mysql> alter table T1 drop PRIMARY KEY;
mysql> show profile cpu for query 11;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000038 | 0.000000 | 0.000000 |
| checking permissions | 0.000002 | 0.000000 | 0.000000 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 |
| init | 0.000034 | 0.000000 | 0.000000 |
| Opening tables | 0.000010 | 0.000000 | 0.000000 |
| System lock | 0.000003 | 0.000000 | 0.000000 |
| Table lock | 0.000004 | 0.000000 | 0.000000 |
| setup | 0.000015 | 0.000000 | 0.000000 |
| creating table | 0.003219 | 0.000000 | 0.001000 |
| After create | 0.000039 | 0.000000 | 0.000000 |
| copy to tmp table | 0.000029 | 0.000000 | 0.000000 |
| rename result table | 0.001346 | 0.001000 | 0.000000 |
| end | 0.000038 | 0.000000 | 0.000000 |
| query end | 0.000003 | 0.000000 | 0.000000 |
| freeing items | 0.000134 | 0.000000 | 0.000000 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
InnoDB Plugin版本下：
mysql> show variables like "innodb_version";
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| innodb_version | 1.1.4 |
+----------------+-------+
mysql> CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;
mysql> set profiling = 1;
mysql> ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);
mysql> show profile cpu for query 16;
+------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+------------------------------+----------+----------+------------+
| starting | 0.000048 | 0.000000 | 0.000000 |
| checking permissions | 0.000004 | 0.000000 | 0.000000 |
| checking permissions | 0.000003 | 0.000000 | 0.000000 |
| init | 0.000007 | 0.000000 | 0.000000 |
| Opening tables | 0.000095 | 0.000000 | 0.000000 |
| System lock | 0.000006 | 0.000000 | 0.000000 |
| setup | 0.000021 | 0.000000 | 0.000000 |
| creating table | 0.001577 | 0.000000 | 0.000000 |
| After create | 0.000026 | 0.000000 | 0.000000 |
| manage keys | 0.084208 | 0.003000 | 0.010998 |
| rename result table | 0.000158 | 0.000000 | 0.000000 |
| end | 0.000017 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 |
| end | 0.000005 | 0.000000 | 0.000000 |
| query end | 0.000003 | 0.000000 | 0.000000 |
| closing tables | 0.000007 | 0.000000 | 0.000000 |
| freeing items | 0.000013 | 0.000000 | 0.000000 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 |
+------------------------------+----------+----------+------------+
重构聚集索引或主键索引时，InnoDB需要拷贝数据。如下：
mysql> CREATE TABLE T2 (A INT, B INT) ENGINE=InnoDB;
mysql> set profiling = 1;
mysql> ALTER TABLE T2 ADD PRIMARY KEY (B);
mysql> show profile cpu for query 1;
+------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+------------------------------+----------+----------+------------+
| starting | 0.000046 | 0.000000 | 0.000000 |
| checking permissions | 0.000004 | 0.000000 | 0.000000 |
| checking permissions | 0.000004 | 0.000000 | 0.000000 |
| init | 0.000006 | 0.000000 | 0.000000 |
| Opening tables | 0.000109 | 0.001000 | 0.000000 |
| System lock | 0.000006 | 0.000000 | 0.000000 |
| setup | 0.000016 | 0.000000 | 0.000000 |
| creating table | 0.016539 | 0.000000 | 0.001000 |
| After create | 0.000059 | 0.000000 | 0.000000 |
| copy to tmp table | 0.000040 | 0.000000 | 0.000000 |
| rename result table | 0.036043 | 0.000999 | 0.001000 |
| end | 0.000042 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 |
| end | 0.000008 | 0.000000 | 0.000000 |
| query end | 0.000003 | 0.000000 | 0.000000 |
| closing tables | 0.000008 | 0.000000 | 0.000000 |
| freeing items | 0.000022 | 0.000000 | 0.000000 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 |
+------------------------------+----------+----------+------------+
在创建UNIQUE或PRIMARY KEY索引时，Innodb还需要做一些额外的工作。对于UNIQUE索引，InnoDB需要检查表不包含重复的键值。对于PRIMARY KEY索引，InnoDB需要检查主键列有没包含NULL。因此最好在创建表的同时，创建主键。
注意：如何添加或删除REFERENCES外键约束，子表还是需要复制数据的。
©著作权归作者所有：来自51CTO博客作者xhh_0168的原创作品，如需转载，请注明出处，否则将追究法律责任
随时随地看视频
