共计 1915 个字符,预计需要花费 5 分钟才能阅读完成。
对于 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。
索引下推(icp)
索引下推是 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 当前版本执行是不统一的。
Mysql 5.6 之前
- 在 5.6 之前是没有索引下推的,只能从 ID3 开始一个个回表,虚线示意回表。到主键索引上找数据行,再比照字段值,如下图:
- 5.6 引入了索引下推,能够在索引遍历过程中,对索引蕴含的字段先做判断,间接过滤到不满足条件的记录,缩小回表次数。如下图:
有了索引下推后,InnoDB 在(name,age)索引内就判断了 age 是否等于 10,不等于 10 的间接跳过,所以下面只须要回表 2 次。
总结
本文从索引查问数据流程上介绍了数据库索引的概念,包含汇集索引、非汇集索引、笼罩索引、回表、最左匹配、索引下推,对于根底的把握能够更快的做数据库的优化,比方缩小回表的次数,最好应用汇集索引,或者笼罩索引。放慢数据查问速度。
参考
极客工夫 | 深入浅出索引(下)
如果感觉文章对你有帮忙的话,请点个赞吧!