关于java:InnoDB-聚集索引和非聚集索引覆盖索引回表索引下推简述

对于 InnoDB 存储引擎的有 汇集索引和非汇集索引，笼罩索引，回表，索引下推 等概念，这些知识点比拟多，也比拟系统，然而概念都是基于索引建设的，本文从索引查找数据讲述上述概念。

在 MySQL 数据库中 InnoDB 存储引擎，B+ 树可分为汇集索引和非汇集索引。汇集索引也叫聚簇索引，非汇集索引也叫辅助索引或者二级索引。
建表的时候都会创立一个汇集索引，每张表都有惟一的汇集索引：

如果主键被定义了，那么这个主键就是作为汇集索引
如果没有主键被定义，那么该表的第一个惟一非空索引作为汇集索引
如果没有主键也没有惟一索引，InnoDB 外部会生成一个暗藏的主键作为汇集索引，这个暗藏的主键是一个 6 个字节的列，该类的值会随着数据的插入自增。

在创立表增加的索引都是非汇集索引，非汇集索引就是一个为了找到汇集索引的二级索引，通过二级索引索引找到主键，再查找数据。
创立一个表 T, 表中有个一个主键 id。表中有字段 k, 并创立在 k 字段上创立索引。

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k)
)engine=InnoDB;

在表中插入数据别离为 (100,1)、（200,2）、（300,3）、（500,5）、（600,6），别离用 R1~R5 示意。当创立表和插入数据后会生成两棵树：

其中右边的是汇集索引，左边的是非汇集索引。非汇集索引叶子节点存储的是主键的值，汇集索引存储的是整行的数据。
执行 select * from T where k between 3 and 5, 有以下的执行流程：

1、在 k 索引树上找到 k = 3, 获得 ID = 300
2、再到 ID 索引树查到 ID=300 对应的 R3
3、在 k 索引树取下一个值 k=5，获得 ID=500
4、再回到 ID 索引树查到 ID=500 对应的 R4
5、在 k 索引树取下一个值 k=6，不满足条件，查问完结

在这个过程中，从二级索引答复主键索引树查找的过程，称为回表。下面流程回表了两次，别离是步骤 2 和步骤 4。

如果执行的语句是 select ID from T where k between 3 and 5, 这个时候只须要查 ID 的值，而 ID 的值曾经在 k 索引树上了，这个时候就 不须要回表 ，也就是说在这个查问中，索引 k“笼罩了”查问，这个称为 笼罩索引。

因为笼罩索引能够缩小树的搜寻次数，进步查问性能，所以应用笼罩索引是一个罕用的索引优化伎俩。

应用笼罩索引最常见的办法是创立联结索引，将须要查问的字段都放在联结索引上。

最左前缀准则，指的是在一个复合索引中（a,b,c），b+ 树会依照从左往右的程序建设搜寻树，b+ 树会优先比拟 a，如果 a 雷同在顺次比拟 b 和 c，最初失去检索数据，然而像查问（b，c）这样的数据没有 a 字段，b+ 树就不晓得从哪个结点查起了。因为搜寻树的第一个比拟因子就是 a。

索引下推是 mysql 5.6 新个性

创立一个表 use, 其中次要有几个字段：id、name、age、address。建设联结索引（name,age）。

mysql> create table use(
id int primary key, 
name varchar(16), 
age int ,
index(name,age)
)engine=InnoDB;

在表中别离插入数据，（ID3，张六，30），（ID4，张三，10），（ID5，张三，10），（106，张三,20）
要执行以下查问：

select * from user where name like '张 %' and age=10

下面说到 InnoDB 索引满足最左匹配准则，当不合乎最左前缀，会怎么样呢？在这个搜寻树中，只能用“张”，找到一个满足条件 103, 而后再判断其余条件是否满足。

这条语句在 Mysql 5.6 之前和 Mysql 5.6 以及 Mysql 5.6 当前版本执行是不统一的。

在 5.6 之前是没有索引下推的，只能从 ID3 开始一个个回表，虚线示意回表。到主键索引上找数据行，再比照字段值，如下图：
5.6 引入了索引下推，能够在索引遍历过程中，对索引蕴含的字段先做判断，间接过滤到不满足条件的记录，缩小回表次数。如下图：

有了索引下推后，InnoDB 在（name，age）索引内就判断了 age 是否等于 10，不等于 10 的间接跳过，所以下面只须要回表 2 次。

本文从索引查问数据流程上介绍了数据库索引的概念，包含汇集索引、非汇集索引、笼罩索引、回表、最左匹配、索引下推，对于根底的把握能够更快的做数据库的优化，比方缩小回表的次数，最好应用汇集索引，或者笼罩索引。放慢数据查问速度。

极客工夫 | 深入浅出索引（下）

如果感觉文章对你有帮忙的话，请点个赞吧!

关于java:InnoDB-聚集索引和非聚集索引覆盖索引回表索引下推简述

汇集索引和非汇集索引

笼罩索引

最左前缀准则

索引下推（icp）

Mysql 5.6 之前

总结

参考