MySQL索引实验-主键索引一定比辅助索引快吗？【转载】

一、在一个表执行了分别执行了两条语句：
a.SELECT ID FROM MNG_ROLE ORDER BY ID; -- 耗时37秒
b.SELECT ID FROM MNG_ROLE ORDER BY ID, NAME; -- 耗时0.01秒
c.SELECT ID FROM MNG_ROLE; -- 耗时0.22秒
二、表结构如下，插入3万条数据，而且REMARK和RESERVER字段都是填满数据：
====================================================
CREATE TABLE `MNG_ROLE` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) NOT NULL,
`CREATE_DATE` char(8) NOT NULL,
`CREATE_TIME` char(6) DEFAULT NULL,
`UUID` char(32) NOT NULL,
`REMARK` varchar(3000) NOT NULL DEFAULT ‘‘,
`RESERVER` varchar(3000) NOT NULL DEFAULT ‘RESERVER‘,
PRIMARY KEY (`ID`),
UNIQUE KEY `UK_UUID` (`UUID`) USING BTREE,
KEY `INDEX_NAME_CREATE_DATE_TIME_REMARK` (`NAME`,`CREATE_DATE`,`CREATE_TIME`,`REMARK`(255)) USING BTREE,
KEY `ID_NAME` (`ID`,`NAME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8
====================================================
这个表建立了一个主键索引（ID），唯一键约束（UUID），普通索引（INDEX_NAME_CREATE_DATE_TIME_REMARK、ID_NAME）而且需要注意的是，这里有两个字符数为3000的字段，而且在表中的数据都是填满的。
三、首先我们分析两个语句EXPLAIN的结果：
MariaDB [yjtmng]> EXPLAIN SELECT ID FROM MNG_ROLE ORDER BY ID;
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | MNG_ROLE | index | NULL | PRIMARY | 4 | NULL | 145134 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
MariaDB [yjtmng]> EXPLAIN SELECT ID FROM MNG_ROLE ORDER BY ID, NAME;
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | MNG_ROLE | index | NULL | ID_NAME | 156 | NULL | 145134 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
MariaDB [yjtmng]> explain SELECT ID FROM MNG_ROLE;
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | MNG_ROLE | index | NULL | UK_UUID | 96 | NULL | 145134 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
上面两个查询，前者是用到了主键索引，后两者用到了辅助索引，但是为什么用到主键索引会更慢呢？上述三个查询的type=index，这个方式跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。
四、分析：
1.由于表的存储引擎采用的InnoDB，InnoDB的索引属于聚集索引，就是说表数据文件和索引文件都是同一个，表数据的分布按照主键排序，以BTREE数据格式存储，而辅助索引的叶子节点指向的是对应的主键。而上述表的REMARK和RESERVER的数据很多，导致硬盘存储的数据块很多，而主键索引的查找就会因为数据块的增多，导致更多的IO操作，降低查询效率。
而MyISAM引擎的索引属于非聚集索引，索引文件跟数据文件是分开的。而索引文件的所指向的是对应数据的物理地址。
2.EXPLAIN 三个查询的Extra都是“Using index”，表示这三个查询的数据都是直接从索引获取的，没有通过主键，再获取对应的一行数据，这就是索引覆盖。
所以第1、3个查询都是直接从辅助索引中查询，并且获取索引值返回，大大加快了效率，但是第三个查询，MySQL会自动采用唯一键"UUID"作为索引，这是为什么呢。而第二个遍历的是主键索引，而且数据量大，IO操作频繁。