MySql 索引扫描方式总结本文简述EXPLAIN分析的索引扫描的方式。常见的扫描方式 - system：系统表，少量

索引前置内容简述

innodb引擎有聚簇索引，非聚簇索引（二级索引）。
聚簇索引只能有一个，是主键索引。
聚簇索引的叶子节点是整行数据，非聚簇索引的叶子节点是行数据的主键id。
MyISAM引擎的索引都是 非聚簇索引。
回表是指查询非聚簇索引获取到主键id，然后根据主键id去查找数据。
索引覆盖是指该索引的字段满足这次select查询的需求，不需要回表查询。

常见的扫描方式

system：系统表，少量数据，往往不需要进行磁盘IO；
const：常量连接；
eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；
ref：非主键非唯一索引等值扫描；
range：范围扫描；
index：索引树扫描；
ALL：全表扫描(full table scan)；

上面各类扫描方式由快到慢： system > const > eq_ref > ref > range > index > ALL

Extra字段说明

Using index 代表索引覆盖，就是查询的列正好在索引中，不用回表查询数据。
Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤。
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

性能从好到坏: useing index > usinh where > using temporary | using filesort

索引失效的情况

使用NOT IN 、<>、!=操作。
like语句操作。
在where子句的查询条件进行运算。
在where子句的查询条件里使用了函数，比如Day，Sum。

测试用例

说明一下以下内容测试环境是MySQL 5.7+innodb引擎。

DROP TABLE IF EXISTS `student`;
CREATE TABLE IF NOT EXISTS `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `nid` int(11) NOT NULL,
  `bid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cid_nid_bid` (`cid`,`nid`,`bid`),
  KEY `id_cid_name` (`id`,`cid`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- 转存表中的数据 `student`
--

INSERT INTO `student` (`id`, `name`, `cid`, `nid`, `bid`) VALUES
(1, 'aaaa', 123, 1, 0),
(2, 'bbbbbb', 3234, 0, 0),
(3, 'aaaa', 123, 2, 1),
(4, 'asdasdasd', 123, 2, 1);
COMMIT;

测试1，使用主键索引查询，ref = const

EXPLAIN SELECT * FROM `student` WHERE id = 1

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	const	PRIMARY,id_cid_name	PRIMARY	4	const	1	100.00	NULL

测试2，符合最左匹配原则，使用索引，type=ref。

EXPLAIN SELECT * FROM `student` WHERE `cid` = 123 and `nid` = 1

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	ref	cid_nid_bid	cid_nid_bid	9	const,const	1	100.00	NULL

测试3，索引覆盖的情况下，即使不符合最左匹配原则，也是可以使用索引。filtered=25%，mysql也认为这样比回表查询要更高效率。（之前面试遇到这个问题，下意识说不能使用索引，但是说不出原因，实操之后才发现自己全错了。）

EXPLAIN SELECT id, cid, bid FROM `student` WHERE `cid` = 123 and `bid` = 1

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	ref	cid_nid_bid	cid_nid_bid	5	const	3	25.00	Using where; Using inde

索引不覆盖的情况下，如果是数据比较少（只有几行）的情况下，MySQL选择全表扫描，如果在十几行以上的数据，MySQL选择使用索引，type = ref。

EXPLAIN SELECT * FROM `student` WHERE `cid` = 123 and `bid` = 1

测试4，range扫描

EXPLAIN SELECT id, cid, bid FROM `student` WHERE `cid` =123 and `nid` > 0

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	range	cid_nid_bid	cid_nid_bid	9	NULL	3	100.00	Using where; Using index

where in 也是range扫描

EXPLAIN SELECT * FROM `student` WHERE id in (1, 4, 5)

测试5，索引树扫描，单纯遍历索引扫描结果，比全表扫描好一点。

EXPLAIN SELECT id, cid, bid FROM `student` WHERE `cid` > 0 and `bid` > 0

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	student	NULL	index	cid_nid_bid	cid_nid_bid	13	NULL	4	33.33	Using where; Using index

MySql 索引扫描方式总结

索引前置内容简述

常见的扫描方式

Extra字段说明

索引失效的情况

测试用例

参考文献