索引覆盖与覆盖索引的深入探究

本文中所说的覆盖索引和索引覆盖，特指本文中的概念

【1】索引覆盖

【1.1】索引覆盖的概念

　　在我的理解中，什么是索引覆盖？就是说，你的所有查询条件中，每个条件CBO都愿意去扫描索引来查询数据（无论是单列索引还是复合索引均可），然后根据索引扫描/查找的一个或多个结果集组合出我们想要查询的结果集。

　　然后非聚集索引会根据不同where条件走的索引获取到叶子节点数据（也就是聚集索引键值），这个时候就获取到了聚集索引值+本身索引列的值。

　　最后再拿不同where条件获取到的聚集索引值做等值比较匹配，均相等的就是我们想要的数据。这个也避免了回表去从实际存储数据的数据页去找数据。

【1.2】索引覆盖实践（MSSQL索引交叉）

用的是，mssql2014

use master;
create table test(id int,num int,num1 int);
create clustered index CIX_id on test(id);
create index IX_num on test(num);
create index IX_num1 on test(num1);
--create a test data, 10000 rows
;with temp as (
select 1 as id,5 as num,10 as num1
union all
select id+1,num+5,num1+10 from temp
where id<10000
)
insert into test select * from temp option(maxrecursion 0);

结果：

如上图，我们可以看到，并没有回表，优化器直接分别根据 num 和 num1 去读取这2列上的索引。

然后获取到（1）聚集索引键值+num （2）聚集索引键值+num1

最后，直接对比匹配相等的聚集索引值（因为查出来的聚集索引键值并不一定保证是有序的《也可以理解成这个时候出来的聚集索引键值就是一个堆结果集》，所以这里MSSQL自动用了更优秀的等值匹配算法：HASH MATCH；

【要是不喜欢看图形界面】可以使用 set showplan_all on/off 开关表格执行计划

问题：我们前面索引覆盖概念里说道，【你的所有查询条件中，每个条件CBO都愿意去扫描索引来查询数据】。

　　之所以上面可以形成索引覆盖，是因为2个条件值命中行都很少，所以CBO 都可以根据条件去走索引。

　　不过，一旦某个条件CBO觉得不值得走索引（比如重复太高、索引行命中太多等等成本代价原因），那么就会变成如普通查询一样，能走索引部分走索引，不能走的只能回表查找了。

【1.3】索引覆盖实践（MYSQL）

用的是5.7.24 社区版

create database test default character set utf8;
use test;
create table test1(id int,num int ,num1 int ,num2 int) default character set utf8;
create unique index UX_id on test1(id);
create index IX_num on test1(num);
create index IX_num1 on test1(num1);
create index IX_num2 on test1(num2);

--create test data
delimiter $$
create procedure sp_insert()
begin
declare i int;
set i=1;
while(i<10000) do
insert into test1 values(i,i+1,i+2,i+3);
set i=i+1;
end while;
end $$
delimiter ;
call sp_insert();

由于对mysql不是很熟，我们先看看覆盖索引、正常回表的执行计划，最后再尝试索引覆盖。

由上图我们可以得知

　　第一行语句是正常的覆盖索引。

　　第二行是我们单独拿num1<10000 查看一下，避免MSSQL和MYSQL的不一致误导我。事实证明是一致的，果然不走索引。

　　第三行集合我们的【1.2】中就可以知道，是正常的索引完之后回表查询。

最后我们试试看，mysql索引覆盖到底行不行

【mysql索引合并】

参考：https://www.cnblogs.com/gjb724332682/p/11018678.html

　　这，2个执行计划一摸一样，就是filtered不一样，我觉得MYSQL应该没有本文中指的(索引覆盖)这么一回事吧（不确定，欢迎大佬指正）。

　　实际上，经过查找资料，发现mysql 也有索引合并，其原理和上面【1.2】的 sql server 实验一样。

　　但绝大多数时候是聚集索引和二级索引列作为 where 条件，或者多是 2个二级索引在where中 or 的情况下，比如 select num,num1 from test1 where num<10 or num1 <20;

【2】覆盖索引

【2.1】覆盖索引的概念

　　这就是我们经常说的覆盖索引和索引覆盖，大多数人并没有细细区分它们。

　　那到底什么叫覆盖索引呢？我的理解是，一个索引包含了所有我们要查的值。

【2.2】覆盖索引实践（MSSQL）

use master;
create table test1(id int,num int,num1 int,num2 int); 
create clustered index CIX_id on test1(id);
create  index IX_num on test1(num);
create  index IX_num1_2 on test1(num1,num2);
--create a test data, 10000 rows
;with temp as (
    select 1 as id,5 as num,10 as num1, 11 as num2
    union all
    select id+1,num+5,num1+10,num2+11 from temp
    where id<10000
)
insert into test1 select * from temp option(maxrecursion 0);

这里以复合索引列为例来演示：

　　因为复合索引中已经包含了 num2的值，所以直接就可以查询出来了。

再来一个例子：我们要的结果集加上聚集索引所在列id

　　同理，因为 id 是聚集索引，已经在(num1,num2) 复合索引的叶子节点里了，所以也满足覆盖索引的特性（包含索要查询的所有数据），这里也没有回表，直接在索引查询中就搞定了。

　　同理单列索引、include包含，这些带来的效果都是一样的。

【2.3】覆盖索引实践（MYSQL）

　　　这个看【1.3】即可。，mysql是有覆盖索引的。

【3】总结

　　索引覆盖：一个select语句，所有查询条件中，CBO对于每个条件都愿意去扫描索引来查询数据（无论是单列索引还是复合索引还是其他索引均可），然后根据索引扫描/查找的一个或多个结果集组合出我们想要查询的结果集。

　　覆盖索引：一个select 语句，查询时，一个索引包含了所有我们要查的值。

　　个人感觉，索引覆盖是包含覆盖索引的，且外界大多理解的覆盖索引或索引覆盖等字样的意思都指的是本文中的覆盖索引。

　　本文概念和实验不一定正确、全面，欢迎大佬拍砖

posted @ 2020-01-18 20:08 郭大侠1 阅读(2227) 评论(0) 编辑收藏举报

刷新页面返回顶部