一、在一个表执行了分别执行了两条语句:
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操作频繁。