更多详情内容请访问:MySQL 系列文章导读

1、索引数据结构的选择

1.1 何为索引

MySQL 官方对索引的定义为:索引是帮助 MySQL 高效获取数据的数据结构

索引本质就是数据结构,可以简单理解为“排好序的快速查找数据结构”,满足特定的查找算法。这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法

  • 优点
    1. 提高数据检索效率,降低数据库的 IO 成本
    2. 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性
    3. 对有依赖关系的子表和父表联合查询时可以提高查询速度
    4. 使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间,降低 CPU 消耗
  • 缺点
    1. 创建索引和维护索引需要消耗时间
    2. 索引需要占据磁盘空间,如果有大量的索引,索引文件就可以比数据文件更快达到最大文件尺寸
    3. 当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度

MySQL 常用的索引为:哈希索引、B+ 树索引

如果只选一个数据,那确实是 hash 更快。但是数据库中经常会选择多条,这时候由于 B+ 树索引有序,并且又有链表相连,它的查询效率比 hash 就快很多了。

1.2 二叉查找树

二叉查找树也称为有序二叉查找树,满足二叉查找树的一般性质,是指一棵空树具有如下性质:

  1. 任意节点左子树不为空,则左子树的值均小于根节点的值;
  2. 任意节点右子树不为空,则右子树的值均大于于根节点的值;
  3. 任意节点的左右子树也分别是二叉查找树;
  4. 没有键值相等的节点;

image-20220706204237530

一个二叉查找树是由 n 个节点随机构成,所以,对于某些情况,二叉查找树会退化成一个有 n 个节点的线性链表。如下图:

image-20220706204605895

因此若想最大性能构造一个二叉查找树,需要这个二叉树是平衡的,从而引出新的定义——平衡二叉树(AVL)

1.3 平衡二叉树(AVL)

AVL 树是带有平衡条件的二叉查找树,任意节点的子树高度差都小于等于 1,并且任何一个节点的左子树或者右子树都是平衡二叉树

image-20220706205456666

由于维护这种高度平衡所付出的代价比从中获得的效率收益还大,故而实际的应用不多,更多的地方是用追求局部而不是非常严格整体平衡的红黑树。当然,如果应用场景中对插入删除不频繁,只是对查找要求较高,那么 AVL 还是较优于红黑树。

1.4 红黑树

一种二叉查找树,但在每个节点增加一个存储位表示节点的颜色,可以是 red 或 black。通过对任何一条从根到叶子的路径上各个节点着色的方式的限制,红黑树确保没有一条路径会比其它路径长出两倍。它是一种弱平衡二叉树(由于是弱平衡,可以推出,相同的节点情况下,AVL 树的高度低于红黑树),相对于要求严格的 AVL 树来说,它的旋转次数变少,所以对于搜索、插入、删除操作多的情况下,我们就用红黑树。
image-20220706213127093

MySQL 表数据,一般情况下都是比较庞大、海量的。如果使用红黑树,树的高度会特别高,红黑树虽说查询效率很高。但是在海量数据的情况下,树的高度并不可控。

1.5 B 树

B 树是为了磁盘或其它存储设备而设计的一种平衡多路查找树(相对于二叉,B 树每个内节点有多个分支),与红黑树相比,在相同的的节点的情况下,一颗 B 树的高度远远小于红黑树的高度。B 树上操作的时间通常由存取磁盘的时间和 CPU 计算时间这两部分构成,而 CPU 的速度非常快,所以 B 树的操作效率取决于访问磁盘的次数,关键字总数相同的情况下 B 树的高度越小,磁盘 I/O 所花的时间越少。
image-20220706214039579

这里只是一个简单的 B 树,在实际中 B 树节点中关键字很多的,上面的图中比如 35 节点,35 代表一个 key(索引),而小黑块代表的是这个 key 所指向的内容在内存中实际的存储位置,是一个指针。

1.6 B+ 树

B+ 树是应文件系统所需而产生的一种 B 树的变形树(文件的目录一级一级索引,只有最底层的叶子节点(文件)保存数据)非叶子节点只保存索引,不保存实际的数据,数据都保存在叶子节点中,这正是系统文件的查找。

image-20220706214222626

所有的非叶子节点都可以看成索引部分!

非叶子节点(比如 5,28,65)只是一个key(索引),实际的数据存在叶子节点上(5,8,9)才是真正的数据或指向真实数据的指针。

1.7 为什么说 B+ 树比 B 树更适合数据库索引

  1. B+ 树更适合外部存储,由于内节点不存放真正数据,一个节点节点可以存储更多的关键字,每个节点能索引的范围更大更精确,也意味着 B+ 树单次磁盘 IO 的信息量大于 B 树,I/O 次数相对减少
  2. MySQL 是一种关系型数据库,区间访问是常见的一种情况,B+ 树叶结点增加的链指针,加强了区间访问性,可使用在区间查询的场景;而使用 B 树则无法进行区间查找

1.8 Hash 索引与 B+ 树索引的区别

  1. Hash 索引不能进行范围查询,而 B+ 树可以。

    这是因为 Hash 索引指向的数据是无序的,而 B+ 树的叶子节点是个有序的链表。

  2. Hash 索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用),而 B+ 树可以。

    对于联合索引来说,Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值,所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或多个索引时,联合索引无法被利用。

  3. Hash 索引不支持 Order BY 排序,而 B+ 树支持。

    因为 Hash 索引指向的数据是无序的,因此无法起到排序优化的作用,而 B+ 树索引数据是有序的,可以起到对该字段 Order By 排序优化的作用。

  4. Hash 索引无法进行模糊查询。而 B+ 树使用 LIKE 进行模糊查询的时候,LIKE 后面前模糊查询(比如 % 开头)的话可以起到优化的作用。

  5. Hash 索引在等值查询上比 B+ 树效率更高。

    不过也存在一种情况,就是索引列的重复值如果很多,效率就会降低。这是因为遇到 Hash 冲突时,需要遍历桶中的行指针来进行比较,找到查询的关键字非常耗时。所以 Hash 索引通常不会用到重复值多的列上,比如列为性别,年龄等。

2、索引的推演

新建一个表:

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

这个表使用 Compact 行格式来实际存储记录的。这里简化了 index_demo 表的行格式示意图:

image-20220115190620676

  • record_type:记录头信息的一项属性,表示记录的类型, 0 表示普通记录、 2 表示最小记 录、 3 表示最大记录、 1 表示目录项记录

  • next_record:记录头信息的一项属性,表示下一条地址相对于本条记录的地址偏移量,我们用
    箭头来表明下一条记录是谁

  • 各个列的值:这里只记录在 index_demo 表中的三个列,分别是 C1、C2、C3

  • 其他信息:除了上述3种信息以外的所有信息,包括其他隐藏列的值以及记录的额外信息

将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果:

image-20220115190832740

把一些记录放到页里面的示意图:

image-20220115190856237

2.1 一个简单的索引设计方案

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢?

因为各个页中的记录并没有规律,我们并不知道我们的搜索条件匹配哪些页中的记录,所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办?我们可以为快速定位记录所在的数据页而建立一个目录建这个目录必须完成下边这些事:

  • 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
  • 给所有的页建立一个目录项

image-20220115191149711

比如查找主键值为 20 的记录,具体查找过程分为两步:

  1. 先从目录项中根据二分法快速确定出主键值为 20 的记录在目录项 3 中(因为 12 < 20 < 209),它对应的页是页 9
  2. 再根据前边说的在页中查找记录的方式去页 9 中定位具体的记录

至此,针对数据页做的简易目录就搞定了。这个目录有一个别名,称为索引

2.2 InnoDB 中的索引方案

InnoDB 的数据是按数据页为单位来读写的。也就是说,当需要读取一条记录的时候,并不是将这个记录本身从磁盘读取出来,而是以页为单位,将整个也加载到内存中,一个页中可能有很多记录,然后在内存中对页进行检索。在 innodb 中,每个页的大小默认是 16kb。

2.2.1 目录项记录的页

把前边使用到的目录项放到数据页中的样子:

image-20220115191349068

从图中可以看出来,我们新分配了一个编号为 30 的页来专门存储目录项记录。这里再次强调 目录项记录 和普通的 用户记录不同点

  • 目录项记录record_type 值是 1,而 普通用户记录record_type 值是0。
  • 目录项记录只有 主键值和页的编号 两个列,而普通的用户记录的列是用户自己定义的,可能包含 很多列 ,另外还有 InnoDB 自己添加的隐藏列。
  • 了解:记录头信息里还有一个叫 min_rec_mask 的属性,只有在存储目录项记录的页中的主键值最小的 目录项记录min_rec_mask 值为 1 ,其他别的记录的 min_rec_mask 值都是 0 。

相同点:两者用的是一样的数据页,都会为主键值生成 Page Directory (页目录),从而在按照主键值进行查找时可以使用 二分法 来加快查询速度。

现在以查找主键为 20 的记录为例,根据某个主键值去查找记录的步骤就可以大致拆分成下边两步:

  1. 先到存储 目录项记录 的页,也就是页 30 中通过 二分法 快速定位到对应目录项,因为 12 < 20 < 209 ,所以定位到对应的记录所在的页就是页 9。
  2. 再到存储用户记录的页 9 中根据 二分法 快速定位到主键值为 20 的用户记录。

2.2.2 多个目录项记录的页

image-20220115191806408

从图中可以看出,我们插入了一条主键值为 320 的用户记录之后需要两个新的数据页:

  • 为存储该用户记录而新生成了 页31
  • 因为原先存储目录项记录的 页 30 的容量已满(我们前边假设只能存储4条目录项记录),所以不得不需要一个新的 页 32 来存放 页3 1 对应的目录项。

现在因为存储目录项记录的页不止一个,所以如果我们想根据主键值查找一条用户记录大致需要 3 个步骤,以查找主键值为20的记录为例:

  1. 确定 目录项记录页

    我们现在的存储目录项记录的页有两个,即页30页32 ,又因为页30表示的目录项的主键值的范围是[1, 320),页 32 表示的目录项的主键值不小于 320 ,所以主键值为 20 的记录对应的目 录项记录在页30 中。

  2. 通过目录项记录页确定用户记录真实所在的页
    在一个存储目录项记录的页中通过主键值定位一条目录项记录的方式说过了。

  3. 在真实存储用户记录的页中定位到具体的记录。

2.2.3 目录项记录页的目录页

image-20220115192046295

3、常见索引概念

3.1 索引的分类

  • 功能逻辑:普通索引、唯一索引、主键索引、全文索引
  • 物理实现方式:聚簇索引、非聚簇索引
  • 作用字段个数:单列索引、联合索引

3.2 聚簇索引(主键索引)

  • 特点
    1. 使用记录主键值的大小进行记录和页的排序
      • 页内的记录 是按照主键的大小顺序排成一个 单向链表
      • 各个 存放用户记录的页,也是根据页中用户记录的主键大小顺序排成一个 双向链表
      • 存放 目录项记录的页 分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表
      • 表中行的物理顺序和索引中行的物理顺序是相同的,在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行按照一定的顺序排列,并且自动维护(有序就一定需要维护)这个顺序
    2. B+ 树的叶子节点存储的是完整的用户记录(这条记录中所有列的值),非叶子节点只存储主键的值,不存储记录的数据
    3. 每个表有且一定会有一个聚簇索引,当表中未指定主键时:
      • 用户没有定义主键,那么 InnoDB 会使用第一个非空的唯一索引作为聚簇索引;
      • 用户既没有定义主键,也没有定义唯一索引,那么 InnoDB 会自动生成一个不可见的 ROW_ID 的列名为 GEN_CLUST_INDEX 的聚簇索引,该列是一个 6 字节的自增数值,随着插入而自增
  • 优点
    1. 数据访问速度快,因为索引和数据保存在同一个 B+ 树中
    2. 聚簇索引对于主键的 排序查找范围查找 速度非常快
    3. 按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以 节省了大量的io操作
  • 缺点
    1. 插入速度严重依赖插入时的顺序,否则可能会出现页分裂严重影响性能
    2. 更新主键代价很高,会导致被更新的行移动,一般主键不可更新
    3. 二级索引需要两次索引查找,第一次找到主键,第二次根据主键找到数据行

3.3 二级索引(辅助索引、非聚簇索引)

也是 B+ 树结构,不过有一点和聚簇索引不同,非聚簇索引叶子节点存储字段(索引字段)的值以及对应记录主键的值,其他节点只存储字段的值(索引字段)

image-20220115193713286

3.4 最左匹配原则

当 B+ 树的数据项是复合的数据结构,比如 (name,age,sex) 的时候,B+ 树是按照从左到右的顺序来建立搜索树的,比如当 (张三, 20, F) 这样的数据来检索的时候,B+ 树会优先比较 name 来确定下一步的所搜方向,如果 name 相同再依次比较 agesex,最后得到检索的数据;但当 (20, F) 这样的没有 name 的数据来的时候,B+ 树就不知道下一步该查哪个节点,因为建立搜索树的时候 name 就是第一个比较因子,必须要先根据 name 来搜索才能知道下一步去哪里查询。针对最左匹配原则,查询语句有即可,顺序可以互换,也就是说 name、age、sex 出现顺序不重要,但必须都有

4、索引的设计原则

4.1 适合创建索引

  1. 字段的数值有唯一性的限制

    业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。(来源 Alibaba)

    说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的。

  2. 频繁作为 WHERE 查询条件的字段,UPDATE、DELETE 的 WHERE 条件列

  3. 经常 GROUP BY 和 ORDER BY 的列

  4. DISTINCT 字段需要创建索引

  5. 对用于多表连接的字段创建索引,并且该字段在多张表中的类型必须一致

  6. 使用字符串前缀创建索引

    在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本 区分度决定索引长度。

    说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90% 以上,可以使用 count(distinctleft(列名,索引长度)) / count(*) 的区分度来确定

  7. 区分度高(散列性高)的列

  8. 使用最频繁的列放到联合索引的左侧

  9. 在多个字段都要创建索引的情况下,联合索引优于单值索引

4.2 索引失效的几种常见情况

  1. 不符合最左匹配原则

  2. 计算、函数、类型转换(自动或手动)

    列类型是字符串,一定要在条件中将数据用引号引用,否则失效(隐式转换)

  3. 范围条件右边的索引列失效

  4. 不等于(!= 或者 <>)索引失效

  5. is null 可以使用,is not null 索引失效

  6. like 以通配符 % 开头索引失效

  7. or 语句前后没有同时使用索引

5、优化

5.1 关联查询优化

5.1.1 左(右)外连接查询

LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是关键点,需要建立索引。也就是说被关联的表作为被驱动表,需要创建索引

5.1.2 内连接

对于内连接来说,查询优化器可以自行决定谁作为驱动表,谁作为被驱动表出现。

对于内连接来说:

  • 如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表出现;
  • 如果两个表连接条件都存在索引的情况下,会选择小表驱动大表——小表驱动大表

5.1.3 小结

  1. 保证被驱动表的 JOIN 字段已经创建了索引
  2. 需要 JOIN 的字段,数据类型保持绝对一致
  3. LEFT JOIN 时,选择小表作为驱动表,大表作为被驱动表 。减少外层循环的次数
  4. INNER JOIN 时,MySQL 会自动将小结果集的表选为驱动表`。选择相信 MySQL 优化策略
  5. 能够直接多表关联的尽量直接关联,不用子查询(减少查询的趟数)不建议使用子查询,建议将子查询 SQL 拆开结合程序多次查询,或使用 JOIN 来代替子查询
  6. 衍生表建不了索引

5.2 子查询优化

子查询执行效率不高的原因:

  1. 执行子查询时,MySQL 需要为内层查询语句的查询结果建立一个临时表 ,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源,产生大量的慢查询。
  2. 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引 ,所以查询性能会受到一定的影响。
  3. 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

可以使用连接查询(JOIN)替代子查询

5.3 排序优化

在 MySQL 中,支持两种排序方式,分别是 FileSortIndex 排序。

  • Index 排序中,索引可以保证数据的有序性,就不需要再进行排序,效率更更高
  • FileSort 排序则一般在 内存中 进行排序,占用 CPU 较多。如果待排序的结果较大,会产生临时文件 I/O 到磁盘进行排序的情况,效率低。

优化建议:

  1. SQL 中,可以在 WHERE 子句和 ORDER BY 子句中使用索引,目的是在 WHERE 子句中 避免全表扫描,在 ORDER BY 子句 避免使用 FileSort 排序。当然,某些情况下全表扫描,或者 FileSort 排序不一定比索引慢。但总的来说,我们还是要避免,以提高查询效率。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列;如果不同就使用联合索引。
  3. 无法使用 Index 时,需要对 FileSort 方式进行调优。

INDEX a_b_c(a,b,c)

order by 能使用索引最左前缀 - ORDER BY a

  • ORDER BY a,b
  • ORDER BY a,b,c
  • ORDER BY a DESC,b DESC,c DESC

如果 WHERE 使用索引的最左前缀定义为常量,则 order by 能使用索引

  • WHERE a = const ORDER BY b,c
  • WHERE a = const AND b = const ORDER BY c
  • WHERE a = const ORDER BY b,c
  • WHERE a = const AND b > const ORDER BY b,c

不能使用索引进行排序

  • ORDER BY a ASC, b DESC, c DESC / 排序不一致 /
  • WHERE g = const ORDER BY b,c /丢失a索引/
  • WHERE a = const ORDER BY c /丢失b索引/
  • WHERE a = const ORDER BY a,d /d不是索引的一部分/
  • WHERE a in (...) ORDER BY b,c /对于排序来说,多个相等条件也是范围查询/

5.4 分组优化

  1. group by 使用索引的原则几乎跟 order by 一致 ,group by 即使没有过滤条件用到索引,也可以直接使用索引
  2. group by 先排序再分组,遵照索引建的最佳左前缀法则
  3. 当无法使用索引列,增大 max_length_for_sort_datasort_buffer_size 参数的设置
  4. where 效率高于 having,能写在 where 限定的条件就不要写在 having 中
  5. 减少使用 order by,能不排序就不排序,或将排序放到程序端去做。Order by、group by、distinct 这些语句较为耗费 CPU,数据库的 CPU 资源是极其宝贵的
  6. 包含了 order by、group by、distinct 这些查询的语句,where 条件过滤出来的结果集尽可能保持在 1000 行以内

5.5 分页优化

一般分页查询时,通过创建覆盖索引比较好地提高性能,但是比如 limit 20000000, 10 此时 MySQL 需要排序前 20000000 条记录,仅仅返回 10 条要的记录,其他记录丢弃,此时查询排序代价非常大

  1. 优化思路一:在索引上完成排序分页操作

    SELECT * FROM student stu,(SELECT id FROM student ORDER BY id LIMIT 20000000,10) tmp WHERE stu.id = tmp.id;
  2. 优化思路二:把 LIMIT 查询转换为某一位置的查询

    SELECT * FROM student WHERE id > 20000000 LIMIT 10;

5.6 其他查询优化策略

5.6.1 EXISTS 和 IN 的区分

对于 EXISTS 和 IN 的选择标准:小表驱动大表

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);

SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc);

当 A 小于 B 时,使用 EXISTS;当 B 小于 A 时,使用 IN

5.6.2 LIMIT 1 优化

针对的是会扫描全表的 SQL 语句,如果你可以确定结果集只有一条,那么加上 LIMIT 1 的时候,当找到一条结果的时候就不会继续扫描了,这样会加快查询速度。

如果数据表已经对字段建立了唯一索引,那么可以通过索引进行查询,不会全表扫描的话,就不需要加上 LIMIT 1 了。

6、前缀索引

MySQL 是支持前缀索引的。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串

alter table teacher add index index1(email); 

image-20220118212713552

alter table teacher add index index2(email(6));
image-20220118212729386
  • 如果使用的是 index1 (即 email 整个字符串的索引结构),执行顺序是这样的:

    1. 从 index1 索引树找到满足索引值是“zhangssxyz@xxx.com”的这条记录,取得 ID2 的值;
    2. 到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;
    3. 取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email='zhangssxyz@xxx.com’ 的条件了,循环结束。

    这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行

  • 如果使用的是 index2 (即 email(6) 索引结构),执行顺序是这样的:

    1. 从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
    2. 到主键上查到主键值是 ID1 的行,判断出 emai l的值不是’zhangssxyz@xxx.com’,这行记录丢弃;
    3. 取 index2上刚刚查到的位置的下一条记录,发现仍然是 ’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
    4. 重复上一步,直到在 idxe2 上取到的值不是’zhangs’时,循环结束。

    使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。区分度越高越好。因为区分度越高,意味着重复的键值越少。

注意:使用前缀索引就用不上覆盖索引对查询性能的优化了

解释:如果使用 index1(即 email 整个字符串的索引结构)的话,可以利用覆盖索引,从 index1 查到结果后直接就返回了,不需要回到 ID 索引再去查一次。而如果使用 index2(即 email(6) 索引结构)的话,就不得不回到 ID 索引再去判断 email 字段的值。

即使你将 index2 的定义修改为 email(18) 的前缀索引,这时候虽然 index2 已经包含了所有的信息,但 InnoDB 还是要回到 id 索引再查一下,因为系统并不确定前缀索引的定义是否截断了完整信息。

7、覆盖索引

7.1 何为覆盖索引

  1. 查询中采用的索引树中包含了查询所需要的所有字段的值,不需要再去聚簇索引监数据,这种叫索引覆盖

    举例:SELECT id, name FROM test1 WHERE name = "pendulumye";

    name 对应 idx1 索引,id 为主键,所以 idx1 索引树叶子节点包括了 name、id 的值,这个查询只能走 idx1 这一个索引就可以了,如果 select 后使用 *,还需要一次回表获取 sex、eamil 的值

7.2 覆盖索引的好处

  1. 避免 Innodb 表进行索引的二次查询(回表)

    InnoDB 是以聚簇索引的顺序来存储的,对于 InnoDB 来说,二级索引在叶子节点中所保存的是行的主键信息,如果使用二级索引查询数据,在查找到相应的键值后,还需要通过主键进行二次查询次才能获取真实所需要的数据

    在覆盖索引中,二级索引的键值中可以获取所要的数据,避免了对主键的二次查询,减少了 IO 操作,提升了查询效率

  2. 可以把随机 IO 变成顺序 IO 加快查询效率

    由于覆盖索引是按键值的顺序存储的,对于 I/O 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 I/O 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 I/O 转变成索引查找的顺序 I/O。

    由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

8、Explain 分析

  • type:表示 MySQL 在表中找到所需行的方式,或者叫访问类型
    1. type=ALL:全表扫描
    2. type=index:索引全扫描
    3. type=range:索引范围扫描
    4. type=eq_ref:唯一索引
    5. type=NULL:不用访问表或索引直接就能得到结果
  • possible_keys:表示查询可能使用的索引
  • key:实际使用的索引
  • key_len:使用索引字段的长度
  • rows:扫描行的数量
  • Extra
    1. using index:覆盖索引,不回表
    2. using where:回表查询
    3. using filesort:需要额外排序,不能通过索引得到排序结果

END

本文作者:
文章标题:索引与优化
本文地址:https://www.pendulumye.com/mysql/343.html
版权说明:若无注明,本文皆个人学习记录原创,转载请保留文章出处。
最后修改:2022 年 07 月 13 日
千山万水总是情,给个一毛行不行💋