史上最详细的mysql底层和explan type和type中index和all的区别

背景：
前几天我朋友问我一个问题,mysql的explan(执行计划)中type的index和all是不是压根没有区别，而且还推了几个博主的链接给我，我看了之后感觉有些无奈，有些博主自己都没有太搞清楚就出来科普让很多人都走歪道了，于是就有了这篇文章，花费了我一整天的时间(本文讨论的是innodb存储引擎)

mysql的explan：
什么是explan呢，相信看这篇文章的各位已经至少对mysql有了初步的了解，甚至在开发过程中也会用explan进行sql的优化，explan是mysql执行计划的关键词，在select前面加上这个关键字出现这条查询语句的执行过程，

会有以上关键词，
其中
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

本文不对其他属性做太多解释，这个只是带过一下，其中本文中我们要注意的主要是type中的属性效率
all<index<range<ref<ref_eq<const<system

我们做开发的时候一般都要求最低都要达到range的级别，range级别是范围索引。

mysql的索引底层：
在正文开始之前我们需要聊一下mysql索引底层和其他的一些原理，我们都知道mysql的innodb的底层是b+树（你都看到这里了，我默认你已经初步了解mysql底层了），b+树的特点是叶子节点存储数据，而非叶子节点存储的则是我们mysql的主键，所以每一次查到数据必定会查到最下面的叶子节点，所以mysql表的主树（主键所在的那棵树，也称聚簇索引）是一颗"矮胖的树

如上图，最下面一行就是数据库中主键对应的那列数据，上面的数字则是对应的主键，这是我们表的主树。mysql的主键也叫做聚簇索引(拿起笔这句话要记住)

磁盘页的概念：
我们都知道，mysql的瓶颈主要是磁盘io，为什么redis这种内存性数据库会流行呢，就是因为内存的IO比磁盘的IO高多了
，计算机存储系统中的数据库最小存储单位为512B,而我们磁盘每一次读取，用我们可以理解的"页"这个词来说，每一次磁盘io最小都为4kb，也就是说，你可能只需要读取1kb的数据，但是实际上操作系统确实帮你读取了至少4kb，就比如你在书本上翻页时，翻了一页之后那一页上只写了两行字，可这两行字的确是占了这满满的一页。（好了，操作系统的东西不扯远了。）

因为操作系统磁盘页的这一特性，所以我们b+树的节点大小也是4的倍数，这样也不会造成资源的浪费，b+树每一个节点大小为16kb，每读取一次节点为一次IO,而我们知道mysql的瓶颈就是IO，所以IO次数越少，效率越高。

回表：
我现在建一张表

CREATE TABLE user  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) NULL,
  `name` varchar(255) NULL,
  PRIMARY KEY (`id`),
  INDEX `index_age`(`age`)
);
1
2
3
4
5
6
7
表中建了一个age的普通索引,现在我用执行计划以下这条sql查询，

EXPLAIN select * from user where age =8
1

每个表中每一个索引都有一课独立的树，但是表也不止一个索引啊，除了主键索引还有普通索引组合索引等等，不可能每颗树的叶子节点都是存放表数据吧，那也太浪费我们的磁盘资源了，所以非聚餐索引树最每个索引所对应的是该表的聚餐索引。
如果你是新手，不太看得懂，那么接下来我们再看看这条sql实际上在mysql的执行过程的图形化展示，

如上图所示，在用了这条sql的因为用到了索引的缘故，mysql回到age普通索引树中查到age为8的聚簇索引，然后再到聚簇索引所在的树中查询那条记录，这个过程叫做回表。

覆盖索引：
看到这里的时候有人就发现了:“回表多经历了一课树，多浪费了几次IO的过程，所以用聚餐索引树查的效率就一定要更高，因为IO次数要少”，那可不一定，
接下来我们再用一条sql

EXPLAIN select * from user where age =8
1

结果依然是使用了我们的“index_age”普通索引，为什么会这样呢，这就要归功我们mysql的优化器，优化器会根据算法帮你选择最优的索引，我们的index_age索引树中已经包含了id主键，我们的查询已经拿到了需要拿到的数据，那么就不需要再去浪费IO回表，这个过程叫做覆盖索引，
如在Extra列看到Using index，说明正在使用覆盖索引。

原因分析：
有了以上的基础，我们再来谈谈type中index和all的区别就好很多了，这是我在网上看到的其他博主的解答

而且大多数博主都认为index和all没有实际意义的区别认为只是跟着索引全表访问了一下而已。效率一样，这一点我不敢苟同，虽然all和index都是很慢的一种type，我们应该对其优化，但是因为此所以导致很多人都忽视了他们两者的区别。

论证：
CREATE TABLE `user` (
  `id` int(255) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `duankou` int(255) DEFAULT NULL COMMENT '端口号',
  `name` varchar(255) NOT NULL COMMENT '用户名字',
  `vx` varchar(255) DEFAULT NULL COMMENT '微信',
  `endTime` date DEFAULT NULL COMMENT '用户到期时间',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `canshu` varchar(255) DEFAULT NULL COMMENT '其他参数，不填则默认',
  `beizhu` varchar(255) DEFAULT NULL COMMENT '备注，可以没有',
  `youxiang` varchar(255) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `nameIndex` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
我建立了一张表,并且在表中增加了一条普通索引，且在其中添加了三百万条数据

select count(*) from user
1

用count(*) 的查询方式查询时间是0.6秒左右
再用explan来看看，他用的是哪个索引

可以看到其实我们经常使用的count(*)他其实也是被mysql的优化器优化，找了一个非聚簇索引树去查找，而并不是去找主键所在的那颗局促索引树.这样的好处是啥呢，因为前面我们讲过，数据库磁盘最大的瓶颈是IO，聚簇索引树因为叶子节点存储了数据的缘故，所以所占的磁盘也会更大，而非聚簇索引树的叶子节点仅仅只是存储了主键，而每一个节点同样是16kb的空间，聚簇索引树的叶子可能只能存储一千条记录，而非聚簇索引树却可以存上万条，所以我们的优化器会选择去非聚簇索引树查找，节省IO资源

接下来我们看看聚簇索引树的效率

我count了一个没有建立索引的字段，时间是0.97秒，慢了二分之一左右，
再用explain看看

type 是all，并没有使用到索引，而他们都是查询了count()所有的记录数量，而“youxiang”这个字段却是直接读取了聚簇索引树，因为IO的问题，所以"youxiang"字段显得慢很多，小伙伴们，现在懂了吗
————————————————
版权声明：本文为CSDN博主「依然这么溜」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_45473171/article/details/121442929