索引覆盖，就是单独依靠某个索引本身就能完成查询，无需回表

背景：
一个host表
campus字段和is_deleted字段各自建有索引，is_deleted是二值索引，标记机器是否已删除
目的：
查所有的园区（campus），以及园区内的机器数量。

无任何where条件

explain
SELECT
  `campus`, count(1) as count
FROM
  `host`
-- WHERE
 -- (host.is_deleted + 0 = 0)
GROUP BY
  campus
ORDER BY
  campus

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	host		range	IDX_host_campus	IDX_host_campus	303		236 (索引的范围扫描只需要扫236条记录)	100	Using index

直接进行IDX_host_campus索引的范围扫描然后Using index(索引覆盖)，无需回表。查询速度较快

加入is_deleted条件，但主动禁止走is_deleted索引

explain
SELECT
  `campus`, count(1) as count
FROM
  `host`
WHERE
 (host.is_deleted + 0 = 0) -- 主动禁止走is_deleted索引
GROUP BY
  campus
ORDER BY
  campus

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	host		index	IDX_host_campus	IDX_host_campus	303		984754	100	Using where

按IDX_host_campus索引的顺序扫描了全表，Using where表示回表逐条扫描了，扫描984754条导致慢查询

为什么要去回表？因为host.is_deleted需要回表才能拿到，单独依靠IDX_host_campus这个索引无法完成查询

不禁止走is_deleted

explain
SELECT
  `campus`
FROM
  `host`
WHERE
 (host.is_deleted = 0)
GROUP BY
  campus
ORDER BY
  campus

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	host		ref	IDX_host_is_deleted,IDX_host_campus	IDX_host_is_deleted	1	const	492493	100	Using temporary; Using filesort

mysql选择了is_deleted索引，然后要回表去拿host.campus字段的信息，也没有索引覆盖

因为都大量回表产生大量随机io，速度和主动禁止走is_deleted索引差不多

加联合索引IDX_host_campus_isdeleted

alter table host add index IDX_host_campus_isdeleted (campus, is_deleted)

explain
SELECT device_class, count(host.id) count FROM `host` 
WHERE (host.is_deleted +0 = 0)  
GROUP BY device_class ORDER BY device_class

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	host		index	IDX_host_campus,IDX_host_campus_isdeleted	IDX_host_campus_isdeleted	304		680869	100	Using where; Using index

扫描索引IDX_IDX_host_campus_isdeleted，逐个索引结点判断is_deleted，返回campus，索引覆盖无需回表

IDX_IDX_host_campus_isdeleted 已经cover了全部查询条件，实现索引覆盖，无需回表

---

并非like "%xxx"的前缀模糊匹配就不使用索引

• 前缀模糊匹配的确是无法进行index seek，但mysql存储引擎仍然可能利用index进行scan，比如索引覆盖的查询场景：

查询所有包含 青 字的campus

explain
SELECT campus FROM `host` WHERE (campus like "%青%") 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	host		index		IDX_host_campus	303		680869	11.11	Using where; Using index

查询类型是index索引扫描，Using index提示使用到了索引覆盖。很容易理解，因为返回结果只需要campus字段，过滤条件也是campus字段本身，因此直接依赖IDX_host_campus就可以完成查询

• 而不能索引覆盖的前缀模糊查询，索引毫无用武之地，自然possible_keys和key都不会出现IDX_host_campus

explain
SELECT * FROM `host` WHERE (campus like "%青%") 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	host		ALL					680869	11.11	Using where