MySQL-快速查询的方法-索引

小琦佩猪

已于 2022-11-20 14:08:46 修改

阅读量1.2k

点赞数

分类专栏： MySql 文章标签： java 后端 mysql 性能优化

于 2022-11-19 21:18:03 首次发布

本文链接：https://blog.csdn.net/weixin_44616096/article/details/127940704

版权

MySql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

前言
索引的分类
InnerDB的索引数据结构B+树

前言

阅读上一章的兄弟应该大概了解了没有索引的mysql是什么样子，查询通过链表和二分法实在耗时，在数据量大的时候查询会非常缓慢，这样用户是无法接受的。于是设计mysql的大叔为了就设计了索引这样的数据结构。

索引的分类

聚簇索引

此索引有两大特点：
.1. 使用记录主键值的大小进行页的记录和排序
1.1 对于页内的数据，按照主键大小顺序形成一个单向链表，并且记录被分为若干个组，每个组的最大值的记录拿出来作为槽形成一个单向列表，这个其实就是在上文提到的页的数据结构。
1.2 每个页中的数据的主键之间形成双向链表。
1.3 不同的页之间按照目录项的主键大小顺序形成双向链表。
.2. B+数的叶子节点存储完整的用户记录
就是每一个叶子节点都要有当前那条记录的所有列的值。

唯一索引

该列或者组合列的值是唯一，如果插入相同的值会报错。同时叶子结点会存储主键列和此索引的列的信息。

普通索引

同时叶子结点会存储主键列和此索引的列的信息。

组合索引

其实组合索引和其他二级索引差不多，只不过其他二级索引可以只有一个列，组合索引一般是多个列。

InnerDB的索引数据结构B+树

我们先建立一张表demo，同时会初始化几条记录

CREATE TABLE `demo` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `c1` varchar(10) NOT NULL DEFAULT '',
  `c2` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

我们设置id为主键，再加入两个普通字段先不给他们建立索引。这时候mysql会给id这个字段建立聚簇索引。
那么此时的B+树会是这个样子：
在这里插入图片描述
从图中看出b+树被分为了3层，整个树是通过数据页进行相连，每个数据页之间又通过主键顺序形成双向链表，非叶子节点只记录页号，主键和一些非用户记录信息，这里的record_type代表的是记录的类型，前文也有提到过为1就代表是非叶子结点目录项记录。
大家重点关注一下叶子节点，叶子节点除了拥有非叶子结点的信息外还有用户记录信息，此时的record_type也等于0，这里叶子节点除了拥有主键信息以外还会有当前记录所有其他列的所有信息，也就是说我只要查到主键id的页，就可以获得这个条记录的完整信息（这也是为什么说索引即数据，数据即索引）。大家别看图中的数据只有这几条，其实3层结构的b+树可以存储的数据量很多，我们简单计算一下。
我们现在id是bigint8个字节，指针在InnoDB中为6字节，那么一个B+树非叶子节点就可以存储16 * 1024 / 14 = 1170个索引指针。
指针在InnoDB中为6字节，设主键的类型是bigint，占8字节。一组就是14字节。计算出一个非叶子结点可以存储16 * 1024 / 14 = 1170个索引指针。按照一条记录为1kb来算，那么3层大概可以存1170x1170x16大约是2000多w条数据，这么多数据mysql只用遍历3层就可以拿到，属实很强大了，当然如果实际业务数据达到1000多w条，那可能就要开始考虑分库分表了。
如果是二级索引的b+树会是什么样子呢，比如我们现在给c1列加一个普通索引，这时候mysql就会再生成一个大小为16kb的B+树。那么和聚簇索引的结构有什么不同呢？其实差别就在于叶子节点，前面在介绍的时候就说过，二级索引的B+树叶子节点只存储当前列的信息和主键，那么问题就来了，我用c1作为条件查询的时候，会查询c1的索引树，那我不是只能拿到主键和c1的值了么，那c2怎么办呢？
小编也不卖关子了，虽然二级索引的索引树没有其他列的信息，但是有主键，我们知道了主键的id和页号，就去查询主键索引呀，主键索引是拥有记录的所有列的值的。所以二级索引查询有两步。
1.通过条件查询二级索引，拿到叶子节点的列信息和主键信息，如果这时候我们的查询语句只需要这两个字段就可以直接返回啦。
2.如果查询的语句还有其他的列的信息，我们就需要将步骤1拿到的主键值去聚簇索引查询从而获取到全部列的信息，我们把步骤2的操作叫做回表。回表就意味着耗时，而且是每查到一个符合条件主键就立马进行回表，而不是查询完再一起回表。

这里就可以提到sql优化了，大家肯定会听说过，用到什么列就查询什么列，还有查询一条记录的详情的时候，条件要用到主键来查。还有大家经常争论的主键到底是用uuid随机生成还是自增，通过B+树的数据结构和查询方式，相信大家也知道的原理了。我们很多的sql优化都是因为B+树的数据结构。下面我们再来说一下查询的详细方式。

B+树的查询方式

谈数据结构的时候就大概将了一下咋查的。其实大家只要稍微了解一下数据结构就看到的出来B+树就是一个多叉树+双向链表的组合。

查询从根节点进行出发，在根节点的的双向链表表使用二分法找到查询的值所在的页的区间，这时候就会去到下一层查询，重复上一层的查询方法，直到进入叶子节点，再用重复的方法拿到主键值所在的记录。
所以查询的本质是扫描范围区间。单点扫描的范围就是【n,n】,如果是>=，<=这种范围查询就是【n,正无穷】。

索引失效的本质

我们不管在面试中或者工作中都会遇到索引失效的情况，从而扫了全表。包括什么查询要遵循最左匹配原则，甚至有时候明明给某个字段建立了索引，查询条件也是它，可是通过执行计划查看的时候发现并没有走索引，还是进行了全表扫描。首先我们写sqlmysql不会直接执行，设计mysql的大叔会认为我们写的sql并不会符合他们的要求（生气！！真是小瞧人），他们会用优化器进行优化，然后再执行。

为什么要遵循最左匹配原则

这个问题大家肯定不会陌生，基本上面试必问，我们在模糊查询的时候不要使用左%，这样索引就失效了。那么究其原因为什么会失效呢？其实无非就是和B+树的数据结构有关。前面提到了B+树的查询会从根节点开始遍历，一层层的找到对应的页的范围，再到叶子节点的页中遍历链表就可以拿到值了。那么如果我们现在使用左百分号进行查询，那么一开始mysql就不知道我该找那个页进行比较，得不到查询的值所在的页的范围。他就只能一个个页找，每找到一个页就需要向下层遍历，直到找到所有符合条件的数据，那么这个时候B+树的查询能力完全没用，设计mysql的大叔就认为你这还不如直接扫全表不走索引了。

明明设了索引，查询条件也有索引还是扫了全表

有时候我们遵循了最左匹配原则，查询的条件也包含了索引，但是还是扫了全表。这里就要提到前面所说的回表了，在二级索引作为条件查询的时候，如果我们查询的字段很多，而且包含其他列的数据，那么这里就必须进行回表才能拿到所有列的信息，而且回表是每查询到一条符合记录的id就会立马进行回表，大家试想一下，我如果查询的记录非常多呢，那么就要进行很多次的回表，回表也是耗费性能的，这时候mysql的大叔就会认为，你还不如不走二级索引，直接扫全表得了。
通过了解索引的分类和数据结构，其实我们大概也知道了什么时候创建什么样的索引。因为索引也是会占用内存的，我们不能为了效率生成很多索引。我们有时候为了保值数据一致性而建立唯一索引，有时候为了节省空间使用组合索引。我们也了解到索引为何失效，以及什么样的查询最适合。比如记录通过主键id进行查询，这样避免回表。查询的的列尽量是少，也是为了命中二级索引，建设回表的次数。
其实mysql的优化查询远不止这，就算命中缓存也有很多方法优化查询效率，这一章节就暂时不讲这个，后续会出一期sql优化的实战的。