MySQL之索引数据结构分析

目录

1 索引数据结构

1.1 简介

索引是一种数据结构，可以帮助我们快速的进行数据的查找
索引的数据结构和具体存储引擎的实现有关，在 MySQL 中使用较多的索引有 Hash 索引，B+ 树索引等，而我们经常使用的 InnoDB 存储引擎的默认索引实现为：B+ 树索引

那么为什么使用索引：

  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 帮助服务器避免排序和临时表。
  • 将随机IO变为顺序IO。
  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义

查看全部数据库引擎介绍：https://db-engines.com/en/

1.2 页数据结构

在MySQL中每个页只有 16K 的大小，但是如果数据很多，那一页肯定就放不下这些数据，那数据肯定就会被分到其他的页中，所以为了把这些页关联起来，肯定就会有记录前后页地址，方便找到对应页；同时每页都是唯一的，那就会需要有一个唯一标志来标记页，就是页号；页中会记录数据所以会存在读写操作，读写操作会存在中断或者其他异常导致数据不全等，那就会需要有校验机制，所以里面还有会校验码，而读操作最重要的就是效率问题，如果按照记录一个个进行遍历，那肯定是很费劲的，所以这里面还会为数据生成对应的页目录（Page Directory）; 所以实际页的内部结构像是下面这样的。
在这里插入图片描述
从图中可以看出，一个 InnoDB 数据页的存储空间大致被划分成了 7 个部分，有的部分占用的字节数是确定的，有的部分占用的字节数是不确定的。在页的 7 个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到 User Records 部分。
但是在一开始生成页的时候，其实并没有 User Records 这个部分，每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分，当 Free Space 部分的空间全部被 User Records 部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。这个过程的图示如下。
在这里插入图片描述
刚刚上面说到了数据的新增的过程。
那下面就来说说，数据的查找过程，假如我们需要查找一条记录，我们可以把表空间中的每一页都加载到内存中，然后对记录挨个判断是不是我们想要的，在数据量小的时候，没啥问题，内存也可以撑；但是现实就是这么残酷，不会给你这个局面；为了解决这问题，mysql 中就有了索引的概念

1.3 为什么MySQL数据页是16K，系统CPU是4K

MySQL每个页是16k是因为它是为了优化磁盘I/O而设计的。在数据库中，数据通常以页的形式存储在磁盘上，每次读取或写入数据都需要进行磁盘I/O操作。如果每个页的大小较小，那么需要读取或写入的页数就会增加，从而增加磁盘I/O的次数，影响数据库的性能。因此，MySQL选择了较大的页大小来减少磁盘I/O的次数，提高数据库的性能。
而CPU每个页是4k是因为它是操作系统的设计决定的。操作系统将内存分成若干个固定大小的块，称为页。每个页的大小通常是4k或8k，这是为了方便管理内存和提高操作系统的性能。较小的页大小可以更好地利用内存，减少内存碎片，提高内存的利用率。同时，较小的页大小也可以减少缺页中断的次数，提高操作系统的性能。
点击了解 系统CPU模型

2 索引底层实现

2.1 Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针
在这里插入图片描述

2.2 B-Tree索引(MySQL使用B+Tree)

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。
在这里插入图片描述

2.3 B+Tree索引

B+Tree索引是B-Tree的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高

2.3.1 B+Tree性质

n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。
B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。
在这里插入图片描述
点击了解二叉树中B+树数据结构

2.3.2 B+树索引的数据结构

在 MySQL 中索引的数据结构和上面描述的页几乎是一模一样的，而且大小也是 16K，但是在索引页中记录的是页 (数据页，索引页) 的最小主键 id 和页号，以及在索引页中增加了层级的信息，从 0 开始往上算，所以页与页之间就有了上下层级的概念。
在这里插入图片描述
这个就是我们常说的 B+ 树，最下面那一层的 page level =0, 也就是叶子节点，其余都是非叶子节点。
在这里插入图片描述
看上图中，我们是单拿一个节点来看，首先它是一个非叶子节点（索引页），在它的内容区中有 id 和 页号地址两部分，这个 id 是对应页中记录的最小记录 id 值，页号地址是指向对应页的指针；而数据页与此几乎大同小异，区别在于数据页记录的是真实的行数据而不是页地址，而且 id 的也是顺序的。
所以 在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。

2.3.3 一棵B+树可以存多少数据量

2.3.3.1 计算一

大家是否还记得，一个B+树大概可以存放多少数据量呢？
InnoDB存储引擎最小储存单元是页，一页大小就是16k。
B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；
在这里插入图片描述
假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数=根结点指针数*单个叶子节点记录行数。
如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16
非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170
因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

2.3.3.2 计算二

我们以 3 层，2 分叉（实际中是 M 分叉）的图例来说明一下查找一个行数据的过程。
比如说我们需要查找一个 id=6 的行数据，因为在非叶子节点中存放的是页号和该页最小的 id，所以我们从顶层开始对比，首先看页号 10 中的目录，有 [id=1, 页号 = 20],[id=5, 页号 = 30], 说明左侧节点最小 id 为 1，右侧节点最小 id 是 5；6>5, 那按照二分法查找的规则，肯定就往右侧节点继续查找，找到页号 30 的节点后，发现这个节点还有子节点（非叶子节点），那就继续比对，同理，6>5&&6<7, 所以找到了页号 60，找到页号 60 之后，发现此节点为叶子节点（数据节点），于是将此页数据加载至内存进行一一对比，结果找到了 id=6 的数据行。

从上述的过程中发现，我们为了查找 id=6 的数据，总共查询了三个页，如果三个页都在磁盘中（未提前加载至内存），那么最多需要经历三次的磁盘 IO。需要注意的是，图中的页号只是个示例，实际情况下并不是连续的，在磁盘中存储也不一定是顺序的。
在这里插入图片描述

至此，我们大概已经了解了表的数据是怎么个结构了，也大概知道查询数据是个怎么的过程了，这样我们也就能大概估算这样的结构能存放多少数据了。
从上面的图解我们知道 B+ 数的叶子节点才是存在数据的，而非叶子节点是用来存放索引数据的。
所以，同样一个 16K 的页，非叶子节点里的每条数据都指向新的页，而新的页有两种可能

  • 如果是叶子节点，那么里面就是一行行的数据
  • 如果是非叶子节点的话，那么就会继续指向新的页

假设

  • 非叶子节点内指向其他页的数量为 x
  • 叶子节点内能容纳的数据行数为 y
  • B+ 数的层数为 z

如下图中所示 Total =x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。
在这里插入图片描述

  • X =？
    上面已经介绍了页的结构，索引也也不例外，都会有 File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右，我们就当做它就是 1K, 那整个页的大小是 16K, 剩下 15k 用于存数据，在索引页中主要记录的是主键与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte; 所以 x=15*1024/12≈1280 行。
  • Y=？
    叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k；但是叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量；每行数据占用空间越大，页中所放的行数量就会越少；这边我们暂时按一条行数据 1k 来算，那一页就能存下 15 条，Y≈15。

算到这边了，是不是心里已经有谱了啊 根据上述的公式，Total =x^(z-1)*y，已知 x=1280,y=15 假设 B+ 树是两层，那就是 Z =2， Total = （1280 ^1 ）15 = 19200 假设 B+ 树是三层，那就是 Z =3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）

一般 B+ 数的层级最多也就是 3 层，你试想一下，如果是 4 层，除了查询的时候磁盘 IO 次数会增加，而且这个 Total 值会是多少，大概应该是 3 百多亿吧，也不太合理，所以，3 层应该是比较合理的一个值。

注意：不 我们刚刚在说 Y 的值时候假设的是 1K ，那比如我实际当行的数据占用空间不是 1K , 而是 5K, 那么单个数据页最多只能放下 3 条数据 同样，还是按照 Z=3 的值来计算，那 Total = （1280 ^2） *3 = 4915200 （近 500w）
所以，在保持相同的层级（相似查询性能）的情况下，在行数据大小不同的情况下，其实这个最大建议值也是不同的，而且影响查询性能的还有很多其他因素，比如，数据库版本，服务器配置，sql 的编写等等，MySQL 为了提高性能，会将表的索引装载到内存中。在 InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降，所以增加硬件配置（比如把内存当磁盘使），可能会带来立竿见影的性能提升哈。

2.3.4 Innodb数据页是双向链表

MySQL的数据页是通过双向链表来组织的。每个数据页都有一个指向前一个数据页和后一个数据页的指针，这样可以方便地在数据页之间进行遍历和访问。双向链表的设计可以提高数据页的访问效率，使得数据库的查询和修改操作更加高效。

2.3.5 Innodb数据页中的记录是单向链表

在MySQL中，数据页是存储数据的基本单位，每个数据页可以包含多条记录。这些记录通过单向链表的方式连接在一起。每个记录中都包含了指向下一条记录的指针，这样就形成了一个单向链表结构。
这种单向链表的设计可以有效地支持数据的插入和删除操作。当需要插入一条新的记录时，只需要修改前一条记录的指针，将其指向新的记录，然后将新记录的指针指向原来的下一条记录。同样，当需要删除一条记录时，只需要修改前一条记录的指针，将其指向下一条记录，然后释放被删除记录的空间。

需要注意的是，MySQL的数据页中的记录并不是双向链表。双向链表需要额外的指针来指向前一条记录，这样会增加额外的存储空间和维护成本。而单向链表只需要一个指针，可以更加高效地支持数据的插入和删除操作。

2.4 Hash索引和B+树索引区别

首先要知道 Hash 索引和 B+ 树索引的底层实现原理：

  • hash索引底层就是 hash表，进行查找时，调用一次 hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
  • B+ 树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同：

  • hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
    因为在 hash索引中经过 hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而 B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
  • hash索引不支持使用索引进行排序，原理同上。
  • hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为 hash函数的不可预测。
  • hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。
  • hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生 hash碰撞，此时效率可能极差。而 B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择 B+ 树索引可以获得稳定且较好的查询速度。而不需要使用 hash索引

2.5 MyISAM和InnoDB实现B+树索引方式区别

MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为非聚簇索引

InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键。
因此，InnoDB表数据文件本身就是主索引，这被称为聚簇索引或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。

在根据主键索引搜索时，直接找到key所在的节点即可取出数据；根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。
因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

总结：
InnoDB 主键索引使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引

2.6 Mysql用B+树做索引而不用B-树或红黑树、二叉树

主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

  • B+树
    B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
    由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

  • Hash
    虽然可以快速定位，但是没有顺序，IO复杂度高；
    基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；
    适合等值查询，如=、in()、<=>，不支持范围查询 ；
    因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；
    Hash索引在查询等值时非常快 ；
    因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；
    如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

  • 二叉树
    树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高

  • 红黑树
    树的高度随着数据量增加而增加，IO代价高

3 聚簇索引和非聚簇索引

3.1 聚簇索引

一种索引，该索引中键值的逻辑顺序决定了表中相应的物理顺序。
聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此 一个表只能包含一个聚集索引 。但该索引可以包含多个列（组合索引）

聚簇索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据。

在 InnoDB 中，只有 主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则MySQL自动为InnoDB表生成一个隐含字段来建立聚簇索引，这个字段长度为6个字节，类型为长整形。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此 不用再次进行回表查询

聚集索引图示
聚集索引的叶节点就是数据节点
在这里插入图片描述

3.2 非聚簇索引

一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块
在这里插入图片描述

聚簇索引和非聚簇索引的区别：

  • 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引。
  • 非聚簇索引的叶子节点存放的是主键值或数据记录的地址(InnoDB辅助索引的data域存储相应记录主键的值，MyISAM辅助索引的data域保存数据记录的地址)

3.3 非聚簇索引一定会回表查询

非聚簇索引一定会回表查询吗
不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询

4 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称 之为覆盖索引
我们知道在InnoDB存储引 擎中，如果不是主键索引，叶子节点存储的是主键值。最终还是要回表，也就是要通过主键再查找一次,这样就 会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作

转载于：https://mp.weixin.qq.com/s/gsA6lwUrL-1EvXRJdjlyFA

5 联合索引数据结构分析

5.1 联合索引数据结构

我们都知道，MySQL的Innodb引擎中，索引是通过B+树来实现的。不管是普通索引还是联合索引，都需要构造一个B+树的索引结构。

那么，我们都知道普通索引的存储结构中在B+树的每个非节点上记录的索引的值，而这棵B+树的叶子节点上记录的是聚簇索引（主键索引）的值。
如：
在这里插入图片描述
那么，如果是联合索引的话，这棵B+树又是如何存储的呢？
在联合索引中，联合索引(name,age)也是一个B+树，非叶子节点中记录的是name,age两个字段的值，叶子节点中记录的是name，age两个字段以及主键id的值
在这里插入图片描述

在存储的过程中，如上图所示，当age不同时，按照age排序，当age相同时，则按照name排序。

所以，了解了索引的存储结构之后，我们就很容易理解最左前缀匹配了：因为索引底层是一个B+树，如果是联合索引的话，在构造B+树的时候，会先按照左边的key进行排序，左边的key相同时再依次按照右边的key排序

所以，在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。
在了解了最左前缀匹配之后，日常我们在工作中，经常在简历索引以及查询的时候，都会基于这个默认的约定进行索引的设计和SQL的优化。

大家都默认MySQL一定是遵循最左前缀匹配的。会认为当一个age,name的联合索引存在时，如果查询语句中不包含age作为条件，就一定不走索引。

MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了。

5.2 索引跳跃扫描

5.2.1 简介

MySQL 8.0.13版本中，对于range查询，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。

通过一个例子给大家解释一下，首先有下面这样一张表（参考了MySQL官网的例子，但是我做了些改动和优化）：

创建表结构
create table t1(f1 int not null,f2 int not null);
创建联合索引
create index idx_t on t1(f1,f2);
插入数据
insert into t1 values
(1,1),(1,2),(1,3),(1,4),(1,5),
(2,1),(2,2),(2,3),(2,4),(2,5);

insert into t1 select f1,f2+5 from t1;
insert into t1 select f1,f2+10 from t1;
insert into t1 select f1,f2+20 from t1;
insert into t1 select f1,f2+40 from t1;

分别在MySQL 5.7.9和MySQL 8.0.30上执行：

explain select f1,f2 from t1 where f2=40;

执行结果如下：
在这里插入图片描述
可以看到，主要有以下几个区别：

MySQL 5.7中，type = index，rows=160，extra=Using where;Using index
MySQL 8.0中，type = range，rows=16，extra=Using where;Using index for skip scan

这里面的type指的是扫描方式，range表示的是范围扫描，index表示的是索引树扫描，通常情况下，range要比index快得多。

从rows上也能看得出来，使用index的扫描方式共扫描了160行，而使用range的扫描方式只扫描了16行。

接着，重点来了，那就是为啥MySQL 8.0中的扫描方式可以更快呢？主要是因为Using index for skip scan表示他用到了索引跳跃扫描的技术

也就是说，虽然我们的SQL中，没有遵循最左前缀原则，只使用了f2作为查询条件，但是经过MySQL 8.0的优化以后，还是通过索引跳跃扫描的方式用到了索引了。

5.2.2 优化原理

在MySQL 8.0.13及以后的版本中

SELECT f1, f2 FROM t1 WHERE f2 = 40;

SQL执行过程如下：

  • 获取f1字段第一个唯一值，也就是f1=1
  • 构造f1=1 and f2 = 40，进行范围查询
  • 获取f1字段第二个唯一值，也就是f1=2
  • 构造f1=2 and f2 = 40，进行范围查询
  • 一直扫描完f1字段所有的唯一值，最后将结果合并返回

也就是说，最终执行的SQL语句是像下面这样的：

SELECT f1, f2 FROM t1 WHERE f1=1 and f2 = 40
union
SELECT f1, f2 FROM t1 WHERE f1=2 and f2 = 40

即，MySQL的优化器帮我们把联合索引中的f1字段作为查询条件进行查询了。

5.2.3 限制条件

在知道了索引跳跃扫描的执行过程之后，这种查询优化比较适合于f1的取值范围比较少，区分度不高的情况，一旦f1的区分度特别高的话，这种查询可能会更慢。
所以，真正要不要走索引跳跃扫描，还是要经过MySQL的优化器进行成本预估之后做决定的。

所以，这种优化一般用于那种联合索引中第一个字段区分度不高的情况。但是话又说回来了，我们一般不太会把区分度不高的字段放在联合索引的左边，不过事无绝对，既然MySQL给了一个优化的方案，就说明还是有这样的诉求的。
但是，我们不能依赖他这个优化，建立索引的时候，还是 优先把区分度高的，查询频繁的字段放到联合索引的左边

除此之外，在MySQL官网中，还提到索引跳跃扫描还有一些其他的限制条件：

  • 表T至少有一个联合索引，但是对于联合索引(A,B,C,D)来说，A和D可以是空的，但B和C必须是非空的。
  • 查询必须只能依赖一张表，不能多表JOIN
  • 查询中不能使用GROUP BY或DISTINCT语句
  • 查询的字段必须是索引中的列

联合索引最左匹配原则参考链接

5.3 索引下推

5.3.1 简介

索引下推（Index Condition Pushdown，简称 ICP）是一种提高查询效率、减少回表操作的技术。它允许 MySQL 在使用联合索引查找数据时，将部分查询条件下推到存储引擎层进行过滤。这样可以减少从表中读取的数据行，降低 I/O 操作的开销，从而提高查询性能。

需要注意的是，索引下推仅适用于联合索引，且它通过将本应由服务器层处理的操作交给存储引擎来执行，从而实现性能优化。

通俗一点就是通过二级索引査到主键id后回表完再进行where条件过滤，改为二级索引査到数据后直接where过滤一遍再进行回表来减少回表的次数

5.3.2 ICP的开启/关闭

默认情况下启用索引条件下推。可以通过设置系统变量 optimizer_switch控制 index_condition_pushdown

#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off ' ;
#打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on ' ;

5.3.3 使用前后的扫描过程

在不使用ICP索引扫描的过程：

  • storage层： 只将满足index key条件的索引记录对应的整行记录取出，返回给server层
  • server 层： 对返回的数据，使用后面的where条件过滤，直至返回最后一行。

使用ICP扫描的过程：

  • storage层： 首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。
  • server 层： 对返回的数据，使用table filter条件做最后的过滤

5.3.4 ICP的使用条件

ICP的使用条件：

  • 如果表访问的类型为 range、ref、eq_ref和ref_or_null 可以使用ICP
  • ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表
  • 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/o操作。
  • 当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少I/O。
    索引覆盖不能使用，一个原因是，索引覆盖，不需要回表。ICP作用是减小回表，ICP需要回表
  • 相关子查询的条件不能使用ICP

6 索引存储位置和数据量

MySQL的索引是存储在磁盘上的，但是为了提高查询效率，MySQL会把常用的索引数据加载到内存中。当进行查询操作时，MySQL会首先在内存中查找索引，如果找不到，再去磁盘上查找。
至于是否全部加载，这取决于MySQL的配置和服务器的内存大小。如果内存足够大，MySQL可能会把所有索引数据都加载到内存中。如果内存较小，MySQL会根据使用频率和最近最少使用（LRU）算法来决定哪些索引数据被加载到内存中。
需要注意的是，即使索引数据被加载到内存中，MySQL也会定期把内存中的索引数据同步回磁盘，以防止数据丢失。

6.1 索引存储位置

假如文件很大了，对应得索引也会很大，以B+树索引为例解析索引和文件存储位置：
在这里插入图片描述

数据和索引其实都是存储在硬盘当中的，然后这时候真正查的时候是要用到一个东西，就是在内存里边准备一个B+树，内存是速度最快的地方，所以在内存里边准备了一个B+树， B+树所有的叶子就是4k小格子。B+树其实数干是在内存里的，比如说的区间和偏移，然后这个时候如果用户想查，只要查询的时候，注意索引在where条件里，只要命中索引了，那么这个查询在B+树会走树干，最终找到某一个叶子
比如身份证号，刚好在叶子代表这个区间里，那么把它从磁盘读到内存，把它解析完之后，最笨的方法，遍历完了之后，可以知道应该下一次把哪个data page放到内存里边读进来，那么就可以找到我们那笔记录了。
由于需要从磁盘读到内存，如果把这些索引再堆到内存里的话，内存不够，存不下这些索引，所以 索引和数据都放在磁盘，内存里只存一个树干，只存一些区间
这样的话是充分利用了各自的能力，磁盘能存很多东西，然后内存速度快，然后用一种数据结构可以加快遍历的一个查找的速度，然后数据又是分而治之的存储，所以这时候获取数据速度极快，最终的目的就是为了什么？减少I/O的流量

点击了解更多关于CPU内存和磁盘相关问题

6.2 数据量很大对索引影响

假如有一张表本身涨到几百万行，数据量变大的时候变成1t、2t了。那么这时候都应该听过这样的一句描述，就是数据库的表如果很大，检索速度、检索性能一定会变低
首先 增删改，如果表有索引，然后增删改变慢，因为要增删改里面数据的话，这个数据建了多少个索引都会找索引列，所以必须去修改索引或调整它的位置，就是维护索引会让增删改变慢。

但是查询速度会不会变慢呢？

  • 假设我这个表100个t，硬盘就能装下100个t，然后内存也刚刚把所有的树干都能存下，然后哪都没有溢出，哪都没有问题，那么这时候，来了一个人的一条简单查询，且where条件能够命中索引。那么这时候，如果一个或少量查询，依然很快。就是一个查询进来之后，因为where条件走的还是内存B+树，走的还是一个索引块，这个索引块到内存依然走的是一个data page，并没有说，数据量变大，未来会把别的data page也要带到内存里面去。
  • 那么什么时候会查询的时候速度会变慢，也就是 并发条件 下，很多的查询都到达了，或者一个复杂的查询到达了，那么这时候查询的时候不是要获取一个的data page到内存了，因为数量变大，数据量越大，能够被很多查询命中的几率，被不同命中的几率就会很大。所以这个时候会受 硬盘带宽 影响速度。也就是说假设来了1万个查询，每个查询查一个4k，每个人查条件都不一样，刚好是又散在不同4k上。那么这1万个查询进入到这条服务器之后，这1万个的每个4k是挨个的向我们内存去走的，查一个，走一个。那么这时候有一部分人就会等待前面那些个4k，他们走完之后才能轮到自己。
    假设都是B+树不受影响，顶层还可以。因为一定要明白硬盘的慢，除了寻址慢，还有一个带宽慢
posted @ 2022-12-12 10:33  上善若泪  阅读(546)  评论(0)    收藏  举报