mysql 聚合索引

原来认为在MySQL中是没有聚集索引的，可是今天在看《High Performance MySQL 2nd》时发现自己原来的观点是不正确的，在MySQL中如果使用Innodb做为存储引擎的话其主键就是聚集索引，也就是说数据是按照主键的顺序存放的。我们可以通过下面的测试来看到这一点：mysql> create table t (ID int(10) unsigned NOT NULL,title varchar(100),primary key(id)) engine=innodb;Query OK, 0 rows affected (0.01 sec)mysql> insert into t values(10,'10');Query OK, 1 row affected (0.00 sec)mysql> insert into t values(8,'8');Query OK, 1 row affected (0.00 sec)mysql> select * from t;+----+-------+| ID | title |+----+-------+| 8 | 8 || 10 | 10 |+----+-------+2 rows in set (0.00 sec)mysql> insert into t values(9,'9');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+----+-------+| ID | title |+----+-------+| 8 | 8 || 9 | 9 || 10 | 10 |+----+-------+可以看出来在innodb中指定了主键后数据的存储顺序就是主键的顺序，如果不指定主键则如下：mysql> create table t2(id int,title varchar(10)) engine=innodb;Query OK, 0 rows affected (0.03 sec)mysql> insert into t2 values(8,'8');Query OK, 1 row affected (0.01 sec)mysql> insert into t2 values(10,'10');Query OK, 1 row affected (0.01 sec)mysql> select * from t2;+------+-------+| id | title |+------+-------+| 8 | 8 || 10 | 10 |+------+-------+2 rows in set (0.01 sec)mysql> insert into t2 values(7,'7');Query OK, 1 row affected (0.00 sec)mysql> select * from t2;+------+-------+| id | title |+------+-------+| 8 | 8 || 10 | 10 || 7 | 7 |+------+-------+3 rows in set (0.01 sec)在没有主键的情况下，数据的存储顺序是插入数据的顺序。同时也可以用同样的方法检验出在MyISAM表中的主键并不是聚集索引mysql> truncate table t;Query OK, 0 rows affected (0.01 sec)mysql> truncate table t2;Query OK, 0 rows affected (0.00 sec)mysql> alter table t engine=myisam;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t2 engine=myisam;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+| t | CREATE TABLE `t` ( `ID` int(10) unsigned NOT NULL, `title` varchar(100) DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create table t2;+-------+--------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------------------------------+| t2 | CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `title` varchar(10) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+--------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert into t values(7,'7');Query OK, 1 row affected (0.00 sec)mysql> insert into t values(10,'10');Query OK, 1 row affected (0.00 sec)mysql> select * from t;+----+-------+| ID | title |+----+-------+| 7 | 7 || 10 | 10 |+----+-------+2 rows in set (0.00 sec)mysql> insert into t values(8,'8');Query OK, 1 row affected (0.01 sec)mysql> select * from t;+----+-------+| ID | title |+----+-------+| 7 | 7 || 10 | 10 || 8 | 8 |+----+-------+3 rows in set (0.00 sec)mysql> insert into t2 values(10,'10');Query OK, 1 row affected (0.01 sec)mysql> insert into t2 values(7,'7');Query OK, 1 row affected (0.01 sec)mysql> select * from t2;+------+-------+| id | title |+------+-------+| 10 | 10 || 7 | 7 |+------+-------+2 rows in set (0.01 sec)由此可见在Innodb中使用主键一定要特别的注意，如果使用随机数据做为主键的话每可能会造成大量的数据移动从而减低服务器的性能