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

大大源码 • 2023年4月17日 pm11:21 • 其他

写在前面：最近复习以前学的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必须要有，且只有一个聚集索引：

如果表定义了PK，则PK就是聚集索引（通常是主键为索引）
如果表没有定义PK，则第一个not NULL unique列是聚集索引；
否则，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)索引，* 包括索引覆盖字段之外的字段，不符合最左前缀匹配原则也不符合索引覆盖，当然不会走索引。

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

原文链接：https://blog.csdn.net/kexiaoleqq/article/details/118497584

THE END