在了解覆盖索引之前我们先大概了解一下什么是聚集索引(主键索引)和辅助索引(二级索引)

       聚集索引（主键索引）：

           聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。

           聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

       辅助索引（二级索引）：

           非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。

        再来看看什么是覆盖索引，有下面三种理解：

• 解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
• 解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
• 解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

　　不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

　　当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息

　　

       从执行结果上看，这个SQL语句只通过索引，就取到了所需要的数据，这个过程就叫做索引覆盖。

几种优化场景：

　　1.无WHERE条件的查询优化：

　　执行计划中，type 为ALL，表示进行了全表扫描

　　如何改进？优化措施很简单，就是对这个查询列建立索引。如下，

ALERT TABLE t1 ADD KEY(staff_id);

•  再看一下执行计划

 

explain select sql_no_cache count(staff_id) from t1
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: staff_id
      key_len: 1
          ref: NULL
         rows: 1023849
　　　　　 Extra: Using index

1 row in set (0.00 sec)

        possible_key: NULL，说明没有WHERE条件时查询优化器无法通过索引检索数据，这里使用了索引的另外一个优点，即从索引中获取数据，减少了读取的数据块的数量。 无where条件的查询，可以通过索引来实现索引覆盖查询，但前提条件是，查询返回的字段数足够少，更不用说select *之类的了。毕竟，建立key length过长的索引，始终不是一件好事情。

• 查询消耗

　　

　　从时间上看，小了0.13 sec

2、二次检索优化

　　如下这个查询：

select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.13 sec)

　　执行计划：

explain select sql_no_cache rental_date from t1 where inventory_id<80000*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id
          key: inventory_id
      key_len: 3
          ref: NULL
         rows: 153734
        Extra: Using index condition
1 row in set (0.00 sec)

　　Extra：Using index condition 表示使用的索引方式为二级检索，即79999个书签值被用来进行回表查询。可想而知，还是会有一定的性能消耗的

　　尝试针对这个SQL建立联合索引，如下：

alter table t1 add key(inventory_id,rental_date);

　　执行计划：

explain select sql_no_cache rental_date from t1 where inventory_id<80000*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id,inventory_id_2
          key: inventory_id_2
      key_len: 3
          ref: NULL
         rows: 162884
        Extra: Using index
1 row in set (0.00 sec)

　　Extra：Using index 表示没有会标查询的过程，实现了索引覆盖

3、分页查询优化

　　如下这个查询场景

select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.75 sec)

　　在未优化之前，我们看到它的执行计划是如此的糟糕

 

explain select tid,return_date from t1 order by inventory_id limit 50000,10*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1023675
        
1 row in set (0.00 sec)

　　看出是全表扫描。加上而外的排序，性能消耗是不低的

　　如何通过覆盖索引优化呢？

　　我们创建一个索引，包含排序列以及返回列，由于tid是主键字段，因此，下面的复合索引就包含了tid的字段值

alter table t1 add index liu(inventory_id,return_date);

　　那么，效果如何呢？

select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.03 sec)

　　可以发现，添加复合索引后，速度提升0.7s！

　　我们看一下改进后的执行计划

explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: liu
      key_len: 9
          ref: NULL
         rows: 50010
　　　　Extra: Using index
1 row in set (0.00 sec)

 　　执行计划也可以看到，使用到了复合索引，并且不需要回表

　　对比一下如下的改写SQL，思想是通过索引消除排序

select a.tid,a.return_date from  t1 a 
inner join 
(select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;

　　并在此基础上，我们为inventory_id列创建索引，并删除之前的覆盖索引

alter table t1 add index idx_inid(inventory_id)；
drop index liu;

　　然后收集统计信息。

select a.tid,a.return_date from t1 a inner join  (select tid from t1 order by inventory_id limit 800000,10) b on a.tid=b.tid;
+--------+---------------------+
| tid    | return_date         |
+--------+---------------------+
| 800001 | 2005-08-24 13:09:34 |
| 800002 | 2005-08-27 11:41:03 |
| 800003 | 2005-08-22 18:10:22 |
| 800004 | 2005-08-22 16:47:23 |
| 800005 | 2005-08-26 20:32:02 |
| 800006 | 2005-08-21 14:55:42 |
| 800007 | 2005-08-28 14:45:55 |
| 800008 | 2005-08-29 12:37:32 |
| 800009 | 2005-08-24 10:38:06 |
| 800010 | 2005-08-23 12:10:57 |
+--------+---------------------+

　　这种优化手段较前者时间多消耗了大约140ms。这种优化手段虽然使用索引消除了排序，但是还是要通过主键值回表查询。因此，在select返回列较少或列宽较小的时候，我们可以通过建立复合索引的方式优化分页查询，效果更佳，因为它不需要回表！

4、建了索引但是查询不走索引

　　表结构：

CREATE TABLE `t_order` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`order_code` char(12) NOT NULL,
`order_amount` decimal(12,2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE )
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

     查询语句：

select order_code,order_amount from t_order order by order_code limit 1000; 

    发现虽然在order_code上建了索引，但是看查询计划却不走索引，为什么呢？因为数据行读取order_amount，所以是随机IO。那怎么办？重新建索引，使用覆盖索引。

ALTER TABLE `t_order` ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);

这样再查看SQL的执行计划，就发现可以走到索引了。

总结：覆盖索引的优化及限制

 覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不需要读取数据，有以下优点：

 1、索引项通常比记录要小，所以MySQL访问更少的数据。

 2、索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。

 3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。

 4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。

 限制：

 1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。

 2、Hash和full-text索引不存储值，因此MySQL只能使用BTree。

 3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。

 4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

参考文献:

【1】 袋鼠云技术团队博客，https://yq.aliyun.com/articles/62419

【2】MySQL覆盖索引优化，https://yq.aliyun.com/articles/709783

【3】MySQL SQL优化之索引覆盖

【4】 Baron Schwartz等 著，宁海元等 译 ；《高性能MySQL》（第3版）； 电子工业出版社 ，2013

来源：mysql-覆盖索引 - 寻找风口的猪 - 博客园 (cnblogs.com)

前置知识：

聚簇索引与非聚簇索引_KKKL的博客-CSDN博客聚簇索引和非聚簇索引并不是某种单独类型的索引，只是索引在数据存储方式上的一种分类。比如主键索引是聚簇索引，其他自行创建的索引都属于非聚簇索引1、聚簇索引聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引优点：1、查找速度快，聚簇索引查找数据只用查找一次，即通过主键找到对应的数据；非聚簇索引需要先通过索引找到主键，再根据主键找到数据2、对于主键的排序查找和范围查找速度非常快缺点：1、插https://blog.csdn.net/qq_45404693/article/details/120681256了解了聚簇索引和非聚簇索引后可以知道，如果使用非聚簇索引，都会先定位到聚簇索引上，如果有需要，再定位到表的数据上，这个由聚簇索引定位到数据表上的查询过程就叫做回表，所以非聚簇索引会走2次索引

那么有没有哪种情况可以让非聚簇索引也只走1次索引呢？有，那就是使用覆盖索引

我们知道，索引上的内容也不过是将表上的某些字段以B+树的结构储存起来，如果我们要查询的字段刚好就是索引包括的字段，那就可以在扫描完索引后直接得到结果，不需要回表，这就是覆盖索引

举个例子

有这么一张表 

---

注意字段是varchar类型！！如果是char会有不同的结果，在此只讨论varchar

---

有一个复合索引(col1, col2)

如果我们进行SELECT *查询

EXPLAIN SELECT * FROM tab1 WHERE col1 = "1";

结果很正常也很普通

但是假设我们只需要col1和col2两个字段上的数据，就可以查询语句改为

EXPLAIN SELECT col1, col2 FROM tab1 WHERE col1 = "1";

然后会发现结果有变化

 在Extra字段出现了一个Using index，说明使用了覆盖索引

正如上面所说，查询的字段与索引中的字段对应，所以不需要回表查询，这样就提高了查询速度

我们甚至可以再添加一个id字段

EXPLAIN SELECT id, col1, col2 FROM tab1 WHERE col1 = "1";

结果与上面相同，也会使用覆盖索引

像下面这样单独查询索引字段也可以用到覆盖索引

EXPLAIN SELECT id FROM tab1 WHERE col1 = "1";

EXPLAIN SELECT col1 FROM tab1 WHERE col1 = "1";

EXPLAIN SELECT col2 FROM tab1 WHERE col1 = "1";

但如果查询col3，结果肯定是用不到覆盖索引的

EXPLAIN SELECT col3 FROM tab1 WHERE col1 = "1";

 如果随便在col3上加一个索引也是无法使用覆盖索引的

---

还有一点，我们知道复合索引需要符合最左匹配原则，否则会导致索引失效。但是假如我们查询的字段仅为id或索引字段，索引也并不会完全失效，比如

EXPLAIN SELECT id FROM tab1 WHERE col2 = "1";

EXPLAIN SELECT col1 FROM tab1 WHERE col2 = "1";

EXPLAIN SELECT col2 FROM tab1 WHERE col2 = "1";

 会出现如下结果

 type是index，而之前的type是ref，说明查找效率有一定下降，但是也用到了索引。在Extra中同时出现了Using where和Using index，可以理解为MySQL在查询时知道这不符合最左匹配，但是因为查询的字段都在索引中，所以可以在相比整个表数据更小的索引文件中查找，查询速度也比全表查询更快

1. MySQL存储引擎

MySQL比较常见的三种存储引擎：InnoDB、MyISAM、Memory。

这三种存储引擎的实现方式各不相同，InnoDB、MyISAM使用的是B+树，Memory使用的是哈希索引。虽然InnoDB、MyISAM都使用的是B+树，但两者还是不完全相同的。InnoDB中，主键索引的B+树叶子节点存储的是整行数据。MyISAM主键索引的B+树叶子节点存储的是整行数据所在内存中的地址。

2. MySQL索引结构

2.1 B树和B+树定义

MySQL索引结构是B-tree与B+tree结构。B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。如人们可能会以为B-树是一种树，而B树又是另一种树。而事实上是，B-tree就是指的B树。

B树就是一种平衡多路查找树，其每个节点可以有两个以上的子节点。B+树在B树的基础上做了改进，在B+树中，出现在分支结点中的元素会被当作它们在该分支结点位置的中序后继者（叶子结点）中再次列出，且每一个叶子结点都会保存一个指向后一叶子结点的指针。

2.2 B树和B+树区别

B树和B+树区别
1.B-Tree 中，所有节点都会带有指向具体记录的指针；B+Tree 中只有叶子结点会带有指向具体记录的指针。
2.B-Tree 中不同的叶子之间没有连在一起；B+Tree 中所有的叶子结点通过指针连接在一起。
3.B-Tree 中可能在非叶子结点就拿到了指向具体记录的指针，搜索效率不稳定；B+Tree 中，一定要到叶子结点中才可以获取到具体记录的指针，搜索效率稳定。

2.3 B+Tree优点

1.B+Tree 中，由于非叶子结点不带有指向具体记录的指针，所以非叶子结点中可以存储更多的索引项，这样就可以有效降低树的高度，进而提高搜索的效率。
2.B+Tree 中，叶子结点通过指针连接在一起，这样如果有范围扫描的需求，那么实现起来将非常容易，而对于 B-Tree，范围扫描则需要不停的在叶子结点和非叶子结点之间移动。

3. MySQL索引回表

MySQL 中的索引有很多中不同的分类方式，可以按照数据结构分，可以按照逻辑角度分，也可以按照物理存储分，其中，按照物理存储方式，可以分为聚簇索引和非聚簇索引。

我们日常所说的主键索引，其实就是聚簇索引（Clustered Index）;主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index），或者叫作辅助索引。

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：
1.主键索引的叶子结点Data域存储的是一行完整的数据。
2.非主键索引的叶子结点Data域存储的则是主键值。

所以，当我们需要查询的时候
1.如果是通过聚簇索引也就是主键索引来查询，此时聚簇索引叶子结点Data域存储的是完整的数据，通过一次B+Tree搜索就可以查询到数据。例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。

2.如果是通过非聚簇索引也就是非主键索引来查询，通过非聚簇索引叶子结点Data域查询的主键的值，然后再通过主键索引叶子结点Data域找到完整数据，也就是通过两次B+Tree搜索才能找到数据。例如 select * from user where username=‘javaboy’，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

对于第二种查询方式而言，一共搜索了两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。

4. MySQL覆盖索引

1.使用非聚簇索引也就是非主键索引一定会回表吗？不一定！
如果查询的列本身就存在于索引中，那么即使使用二级索引，一样也是不需要回表的。

比如有一个部门表dept，id是主键索引，code是非主键索引，如果通过非主键索引查询整行记录，就需要先通过非聚簇索引code查询到聚簇索引也就是主键索引id，在根据id查询到整条记录，这个需要回表，但是，如果通过code查询主键索引id，这个通过非主键索引code直接可以查询到，就不需要回表。

2.MySQL覆盖索引实现验证

-- 创建部门表
CREATE TABLE `dept` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
  `code` varchar(32) NOT NULL COMMENT '部门编码',
  `name` varchar(30) NOT NULL COMMENT '部门名称',
  `address` varchar(60) DEFAULT NULL COMMENT '部门地址',
  `ceo` int(11) DEFAULT NULL COMMENT '随机数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_code` (`code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='部门表';

-- 查看执行计划
EXPLAIN
SELECT * FROM dept where code = 'ubVBVXam';

-- 覆盖索引 Extra 的值为 Using index
EXPLAIN
SELECT id FROM dept where code = 'ubVBVXam';

执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	dept		const	idx_code	idx_code	130	const	1	100.00	Using index

可以看到，此时使用到了 code索引，但是最后的 Extra 的值为 Using index，这就表示用到了索引覆盖扫描（覆盖索引），此时直接从索引中过滤不需要的记录并返回命中的结果，这一步是在 MySQL 服务器层完成的，并且不需要回表。

5. B+Tree 能存多少数据

计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB。InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块。

基于这样的知识储备，我们可以大致算一下一个 B+Tree 能存多少数据。

假设数据库中一条记录是 1KB，那么一个页就可以存 16 条数据（叶子结点）；对于非叶子结点存储的则是主键值+指针，在 InnoDB 中，一个指针的大小是 6 个字节，假设我们的主键是 bigint ，那么主键占 8 个字节，当然还有其他一些头信息也会占用字节我们这里就不考虑了，我们大概算一下，小伙伴们心里有数即可：

16*1024/(8+6)=1170

即一个非叶子结点可以指向 1170 个页，那么一个三层的 B+Tree 可以存储的数据量为：

1170117016=21902400

可以存储 2100万 条数据。

在 InnoDB 存储引擎中，B+Tree 的高度一般为 2-4 层，这就可以满足千万级的数据的存储，查找数据的时候，一次页的查找代表一次 IO，那我们通过主键索引查询的时候，其实最多只需要 2-4 次 IO 操作就可以了。

参考文档
MySQL索引数据结构B-tree和B+tree详解
MySQL数据库索引及失效场景
MySQL架构设计详解