深入探究Mysql联合索引的原理——B树|B+树|回表|联合索引|索引覆盖|索引失效

写在前面:最近复习以前学的Mysql索引,又发现迷迷糊糊的了。看了以前的笔记以及好多博客边思考边实践终于打破这城墙了,呜呜呜真感动。不过我发现好多博客不完整,或者有好多错误的,那就当记一下笔记吧,以后来温习温习。本文是在参考博文的理解上做的笔记~

1. 索引是什么?

索引是一种利于快速查询的数据结构,在Mysql中常用的存储引擎是innoDB,innoDB是一种以聚集索引为存储方式。底层的存储方式都是以B+树。

(1)B树简略示意图:
在这里插入图片描述

(2)B+树简略示意图:
在这里插入图片描述

B树和B+树特点: B树每一个节点都可以存入多个key值,每个节点都存储数据,叶子节点没有指针相连;B+树非叶子节点存储key值,叶子节点存储key和数据,叶子节点包括所有的key,并且按照顺序有指针相连。

为什么选择B+树作为存储结构 : (1)由于从磁盘读取数据到内存是以页为基本单位读取,1页4KB大小。假如我们读取相同大小的数据进入内存,B+树可以读入更多的key进入内存,这样B+树对于B树来说相同索引所建树的高度更矮,对于磁盘IO操作的次数就少。(2)B+树的叶子节点是按照一定顺序的指针指向 所以范围查找效率高。

2. InnoDB聚集索引和普通索引有什么差异?

innoDB的实现中有两大类索引:聚集索引普通索引(单列索引,联合索引)

InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须要有,且只有一个聚集索引:

  1. 如果表定义了PK,则PK就是聚集索引(通常是主键为索引)

  2. 如果表没有定义PK,则第一个not NULL unique列是聚集索引;

  3. 否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

所以,聚集索引一定并有且只有一个!!,而普通索引则为自己建立,普通索引的叶子节点存储的不是整行数据而是所有的索引值+主键id,也就是为什么有些情况要用到回表去查找索引之外的值。

3. 回表

回表:如果我们建立了普通索引,我们会进行普通索引的查找;如果查找的字段不在索引字段中(不是索引覆或者主键id),那么我们将会根据普通索引中得到的id去聚集索引中查找所需数据,这就是回表。但索引失效问题例外,索引失效问题下面详细讨论。

4. 索引和联合索引的存储结构

(1)聚簇索引的存储结构(左)和普通索引的存储结构(右)
在这里插入图片描述
理解:当我们自己建立了普通索引,会从普通索引进行查找,如果所查找字段等于所建索引字段 或者是主键id,则不会进行回表,因为可以直接从这个普通索引表中获取到所有值。(这里没有谈到索引失效的情况)

不需要回表的情况:
1.所查找字段刚好等于所建立索引的字段(索引覆盖)
2.所查找字段为主键id
3.通过主键id进行直接查找

(2)联合索引结构
在这里插入图片描述
解释:很多博客关于联合索引存储结构都画错啦,这个图是对的。非叶子节点存储的联合索引值(key1,key2.....),叶子节点存储的是覆盖索引值和主键id。如果查找的字段超过了索引则是会产生回表操作的,进行所有id扫描。

4. 索引和索引失效

4.1 单值索引问题

理解了上面的知识点,下面的问题就迎刃而解啦。跟着例子来吧!!创建索引单列索引 name

create table user (
	id int primary key,
	name varchar(20),
	sex varchar(5),
	index(name)
)engine=innodb;

(1)执行下面的sql语句01
在这里插入图片描述
分析:这里我们自己建立了普通索引(非聚集索引),根据条件 where name=”shenjian" 在普通索引中能够找到id和name字段,当然走索引了且不会失效。

(2)执行下面的sql语句02
在这里插入图片描述
分析:命中name索引,索引叶子节点存储了主键id,但sex字段必须回表查询才能获取到,不符合索引覆盖,需要再次根据id值通过聚集索引获取sex字段,效率会降低。

4.2 联合索引问题

将上述name单列索引升级为联合索引**(name, sex)**来看看。

create table user (
	id int primary key,
	name varchar(20),
	sex varchar(5),
	index(name, sex)
)engine=innodb;

(1)执行下面的sql01和sql02
在这里插入图片描述
分析:联合索引符合最左前缀匹配原则,where name='shenjian'会走索引 name。sql01可以在普通索引中获取到id和name所以直接查询到所需数据;sql02由于查询字段含有sex,所以会首先根据索引 name 查询到id,然后回表操作去聚集索引中找。

(2)一些难以理解场景

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_user` (`name`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO user(`id`, `name`, `age`, `address`) VALUES (1, 'zs', 12, 'beijing');

在这里插入图片描述
分析:根据最左前缀原则,这个是失效的。但是为什么走索引了呢 ? 这里不能用最左前缀来进行解释,要用索引覆盖原理进行解释!!。因为这里 select * 包括id、name、address、age所有字段,而它刚刚好都在普通索引的叶子节点。当然可以走索引啦。

这里这两个语句是等价的
EXPLAIN select * from user where address='beijing';
EXPLAIN select id,name,age,address from user where address='beijing';

4.3 索引失效

(1)SELECT * FROM student WHERE name ='张三' AND addr = '北京市'语句

说明:单列索引,name有索引。这里的" * "含有索引中没有的字段,例如sex。索引会失效,Mysql自动优化直接全表扫描。

分析:首先innodb中存在两种索引 聚集索引和普通索引(单值索引和联合索引)。聚集索引的叶子节点才存储所有数据,而普通索引的叶子节点存储着索引值和主键id。 SELECT * FROM student WHERE name ='张三' AND addr = '北京市'语句,如果name有索引而addr没索引。这里是因为如果走创建的普通索引只能得到id,会回表操作,再根据id得到整条数据,所以效率低。Mysql自身优化就不会去走索引,直接全表根据id查找,也就不会有回表操作。

(2)SELECT * FROM student WHERE addr = '北京市'语句
说明:联合索引,(name,age,address)索引,* 是索引覆盖字段则会走索引。见上面例子难以理解的场景

(3)SELECT * FROM student WHERE addr = '北京市'语句
说明:联合索引,(name,age,address)索引,* 包括索引覆盖字段之外的字段,不符合最左前缀匹配原则也不符合索引覆盖,当然不会走索引。

啊啊终于写完了,欢迎老家批评指正。


版权声明:本文为kexiaoleqq原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
THE END
< <上一篇
下一篇>>