【MYSQL】MYSQL 的学习教程（三）之索引核心知识点

2023-12-20 06:54:02

1. 什么是索引？

索引是一种能提高数据库查询效率的数据结构，一般存储在磁盘的文件中，它是占用物理空间的

适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。

2. 索引的优劣势

优势：

  • 提高数据的检索效率，降低数据库的 IO 成本
  • 降低了数据的排序效率，降低了 CPU 的消耗

劣势：

  • 索引也是一张表，该表保存着主键与索引字段，并指向实体的记录，所以，索引也占存储
  • 索引虽然提高了查找效率，但会降低表的更新速度。因为更新表时，不仅要保存数据，还要保存索引信息
  • 创建索引和维护索引要耗费时间

3. MySQL 索引有哪些类型

①：数据结构维度

  1. B+ 树索引：所有数据存储在叶子节点，复杂度为 O(logn)，适合范围查询
  2. 哈希索引: 适合等值查询，检索效率高，一次到位
  3. 全文索引：MyISAM 和 InnoDB (MYSQL 5.6 版本之后) 中都支持使用全文索引，一般在文本类型 char、text、varchar 类型上创建

②：物理存储维度

  1. 聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb 存储引擎）
  2. 非聚集索引（二级索引）：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb 存储引擎）

③：逻辑维度

  1. 主键索引：一种特殊的唯一索引，不允许有空值
  2. 唯一索引：索引列中的值必须是唯一的，但是允许为空值
  3. 普通索引：MySQL 中基本索引类型，允许空值和重复值
  4. 联合索引：多个字段创建的索引，使用时遵循最左前缀原则

④：字段个数

  1. 单列索引
  2. 复合索引（联合索引）

3.1 哈希索引

准备：

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (1, '李四', 20, '杭州');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (2, '张三', 18, '北京');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (3, '张三', 23, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (4, '赵六', 22, '杭州');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (5, '王五', 19, '北京');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (6, '赵六', 24, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (7, '刘七', 20, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (8, '刘七', 22, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (9, '王九', 9, '杭州');

Hash 索引其实用的不多，最主要是因为最常见的存储引擎 InnoDB 不支持显示地创建 Hash 索引，只支持自适应Hash索引

InnoDB支不支持哈希索引？

虽然可以使用 sql 语句在 InnoDB 显示声明 Hash 索引，但是其实是不生效的：

在这里插入图片描述
对 name 字段建立 Hash 索引，但是通过 show index from 表名 就会发现实际还是 B+ 树

在这里插入图片描述

在存储引擎中，Memory 引擎支持 Hash 索引

Hash 索引其实有点像 Java 中的 HashMap 底层的数据结构，他也有很多的槽，存的也是键值对，键值为索引列，值为数据的这条数据的行指针，通过行指针就可以找到数据

假设现在 user 表用 Memory 存储引擎，对 name 字段建立 Hash 索引，表中插入三条数据：

在这里插入图片描述

Hash 索引会对索引列 name 的值进行 Hash 计算，然后找到对应的槽下面，如下图所示：

在这里插入图片描述

当遇到 name 字段的 Hash 值相同时，也就是 Hash 冲突，就会形成一个链表，比如有 name = 张三 有两条数据，就会形成一个链表。

之后如果要查 name=李四 的数据，只需要对李四进行 Hash 计算，找到对应的槽，遍历链表，取出 name = 李四 对应的行指针，然后根据行指针去查找对应的数据

Hash索引优缺点

  • hash 索引只能用于等值比较，所以查询效率非常高
  • 不支持范围查询，也不支持排序，因为索引列的分布是无序的

3.2 B+ 树索引

B+ 树是 MYSQL 索引中用的最多的数据结构，这里先不介绍，下面会着重介绍

Hash 索引和 B+ 树区别是什么？

  • B+ 树可以进行范围查询，Hash 索引不能
  • B+ 树支持联合索引的最左侧原则，Hash 索引不支持
  • B+ 树支持 order by 排序，Hash 索引不支持
  • Hash 索引在等值查询上比 B+树效率更高。（但是索引列的重复值很多的话，Hash 冲突，效率降低）
  • B+ 树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询

3.3 聚簇索引

3.3.1 数据页数据存储

我们插入表的数据其实最终都要持久化到磁盘上，InnoDB 为了方便管理这些数据，提出了页的概念，它会将数据划分到多个页中，每个页大小默认是16KB，这个页我们可以称为 数据页

当我们插入一条数据的时候，数据都会存在数据页中，如下图所示：

在这里插入图片描述

当数据不断地插入数据页中，数据会根据主键（没有的话会自动生成）的大小进行排序，形成一个单向链表：

在这里插入图片描述

数据页中除了会存储我们插入的数据之外，还会有一部分空间用来存储额外的信息，额外的信息类型比较多

3.3.2 单个数据页的数据查找

假设现在需要在数据页中定位到 id=2 的这条记录的数据，如何快速定位？

有一种笨办法：从头开始顺着链表遍历就行了，判断 id 是不是等于 2，如果等于 2 就取出数据就行了

虽然这种方法可行，但是如果一个数据页存储的数据多，几十或者是几百条数据，每次都这么遍历，不是太麻烦了

所以，MYSQL 想了一个好办法：给这些数据分组

假设数据页中存了 12 条数据，那么整个分组大致如下图所示：

在这里插入图片描述

3.3.3 多个数据页中的数据查找

当我们不断的往表中插入数据的时候，数据占用空间就会不断变大，但是一个数据页的大小是一定的，当一个数据页存不下数据的时候，就会重新创建一个数据页来存储数据

在这里插入图片描述

MYSQL 为了区分每个页，会为每个数据页分配一个页号，存在额外信息的存储空间中，同时额外信息还会存储当前数据页的前一个和后一个数据页的位置，从而形成数据页之间的双向链表

在这里插入图片描述

并且 MYSQL 规定，前一个数据页的存储数据 id 的最大值要小于后一个数据页的存储数据 id 的最小值，这样就实现了数据在所有数据页中按照 id 的大小排序

现在，如果有多个数据页，当我们需要查找 id = 5 的数据，怎么办呢？

当然还是可以用上面的笨办法，那就是从第一个数据页开始遍历，然后遍历每个数据页中的数据，最终也可以找到 id = 5 的数据

但是你仔细想想，这个笨办法就相当于全表扫描了呀，这肯定是不行的

优化：跟前面单数据页查找数据的优化思路差不多

它会将每个数据页中最小的 id 拿出来，单独放到另一个数据页中，这个数据页不存储我们实际插入的数据，只存储最小的 id 和这个 id 所在数据页的页号，如图所示：

在这里插入图片描述

此时数据页 5 就是抽取出来的，存放了下面三个存放数据的数据页的最小的 id 和对应的数据页号

如果此时查找 id = 5 的数据就很方便了，大致分为以下几个步骤：

  1. 从数据页5直接根据二分查找，发现在4-7之间
  2. 由于4和7是所在数据页最小的id，那么此时id=5的数据必在id=4的数据页上(因为id=7的数据页最小的id就是7)
  3. 接下来就到id=4对应的数据页2的页号找到数据页2
  4. 再根据前面提到的根据数据的主键id从单个数据页查找的流程查找数据

这样就实现了根据主键 id 到在多个数据页之间查找数据

3.3.4 聚簇索引

随着数据量不断增多，存储数据的数据页不断变多，数据页5的数据就会越来越多，但是每个数据页默认就 16k，所以数据页 5 也会分裂出多个数据页的情况，如下图：

在这里插入图片描述
MYSQL 会去抽取数据页 5 和数据页 10 存储的最小的数据的 id 和对应的数据页号，单独拎出来放到一个数据页中，如下图：

在这里插入图片描述

数据页11就是新抽取的数据页，存储了id=1和对应的数据页5的页号以及数id=10和对应的数据页10的页号（而这就是B+树）

而这种叶子节点存储实际插入的数据的 B+ 树就被称为 聚簇索引，非叶子节点存储的就是记录的 id 和对应的数据页号

所以对于InnoDB存储引擎来说，数据本身就存储在一颗B+树中

3.4 非聚簇索引（二级索引）

非聚簇索引也被称为二级索引，本身也就是一颗B+树，一个二级索引对应一颗B+树，但是二级索引B+树存储的数据跟聚簇索引不一样

聚簇索引：叶子节点存的就是我们插入到数据库的数据，非叶子节点存的就是数据的主键 id 和对应的数据页号
非聚簇索引：叶子节点存的是索引列的数据和对应的主键id，非叶子节点除了索引列的数据和id之外，还会存数据页的页号

聚簇索引与非聚簇索引的区别

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引

在 MySQL 的 InnoDB 存储引擎中， 聚簇索引与非聚簇索引最大的区别：叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询

  • 一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。
  • 一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表

而在 MyISM 存储引擎中，它的主键索引，普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据

3.5 单列索引

场景：

我们现在对 name 字段加了一个普通非唯一索引，那么 name 就是索引列，同时 name 这个索引也就是单列索引

此时如果往表中插入三条数据，那么 name 索引的叶子节点存的数据就如下图所示：

在这里插入图片描述

MYSQL 会根据 name 字段的值进行排序，这里我假设张三排在李四前面，当索引列的值相同时，就会根据 id 排序，所以索引实际上已经根据索引列的值排好序了

name 字段存储的中文也可以排序嘛？

答案是可以的，并且 MYSQL 支持很多种排序规则，我们在建数据库或者是建表的时候等都可以指定排序规则。

在这里插入图片描述

对于单个索引列数据查找也是跟前面说的聚簇索引一样，也会对数据分组，之后可以根据二分查找在单个索引列来查找数据

当索引页不断增多是，为了方便在不同索引页中查找数据，也就会抽取一个索引页，除了存页中 id，同时也会存储这个 id 对应的索引列的值：

在这里插入图片描述
当数据越来越多越来越多，还会抽取，也会形成三层的一个 B+ 树

3.6 复合索引

除了单列索引，联合索引其实也是一样的，只不过索引页存的数据就多了一些索引列

比如，在 name 和 age 上建立一个联合索引，此时单个索引页就如图所示：

在这里插入图片描述

先以 name 排序，name 相同时再以 age 排序，如果再有其它列，依次类推，最后再以 id 排序

相比于只有 name 一个字段的索引来说，索引页就多存了一个索引列

最后形成的 B+ 树简化为如下图：

在这里插入图片描述

4. 索引什么时候会失效

  1. 查询条件包含 or，可能导致索引失效
  2. 隐式的类型转换，索引失效
  3. like 通配符 “%” 在关键词前面导致索引失效
  4. 在索引列上使用 MYSQL 的内置函数，索引失效
  5. 对索引列运算（如，+、-、*、/），索引失效
  6. 索引字段上使用（!= 或者 < >，not in）时，可能会导致索引失效
  7. 索引字段上使用is null， is not null，可能导致索引失效
  8. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
  9. 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效
  10. MYSQL 估计使用全表扫描要比使用索引快,则不使用索引

5. 哪些场景下需要建立索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 外键应该建立索引
  4. 排序字段添加适合创建索引
  5. 统计和分组的字段适合创建索引（分组的前提是先排序）
  6. 区分度高的字段适合创建索引索引

6. 哪些场景下不适合建立索引

  1. 数据量少的表，不适合加索引
  2. 更新比较频繁的也不适合加索引
  3. 区分度低的字段不适合加索引（如性别）
  4. where、group by、order by 等后面没有使用到的字段，不需要建立索引
  5. 已经有冗余的索引的情况（比如已经有 a,b 的联合索引，不需要再单独建立 a 索引）

7. 为什么要用 B+ 树，为什么不用二叉树？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少， 以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是 B 树，而偏偏是 B+树呢？

①：为什么不是一般二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找 树来说，查找效率更稳定，总体的查找速度也更快。

②：为什么不是平衡二叉树呢？

如果树这种数据结构作 为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说 的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果 是 B 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数 就降下来啦，查询效率就快啦。

③：那为什么不是 B 树而是 B+树呢？

  • B+树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储 键值，也会存储数据。innodb 中页的默认大小是 16KB，如果不存储数据，那 么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就 会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数有会再次减少，数据查 询的效率也会更快
  • B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链 表连着的。那么 B+树使得范围查找，排序查找，分组查找以及去重查找变得 异常简单

8. 一次 B+ 树索引树查找过程

假设有以下表结构，并且初始化了这几条数据：

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into employee values(100,'小伦',43,'2021-01-20','0');
insert into employee values(200,'俊杰',48,'2021-01-21','0');
insert into employee values(300,'紫琪',36,'2020-01-21','1');
insert into employee values(400,'立红',32,'2020-01-21','0');
insert into employee values(500,'易迅',37,'2020-01-21','1');
insert into employee values(600,'小军',49,'2021-01-21','0');
insert into employee values(700,'小燕',28,'2021-01-21','1');

执行这条查询SQL，需要执行几次的树搜索操作？

select * from Temployee where age=32;

可以先画出 idx_age 普通索引的索引结构图，大概如下：

在这里插入图片描述

再画出 id 主键索引，我们先画出聚族索引结构图，如下：

在这里插入图片描述

这条 SQL 查询语句执行大概流程是这样的：

  1. 搜索 idx_age 索引树，将 磁盘块1 加载到内存，由于 32<43，搜索左路分支，到磁盘寻址 磁盘块2
  2. 将 磁盘块2 加载到内存中，由于 32<36，搜索左路分支，到磁盘寻址 磁盘块4
  3. 将 磁盘块4 加载到内存中，在内存继续遍历，找到 age=32 的记录，取得 id = 400
  4. 拿到 id=400 后，回到 id主键索引树
  5. 搜索 id主键索引树，将 磁盘块1 加载到内存，因为 300<400<500，所以在选择中间分支，到磁盘寻址 磁盘块3
  6. 虽然在 磁盘块3，找到了 id=400，但是它不是叶子节点，所以会继续往下找。到磁盘寻址 磁盘块8
  7. 将 磁盘块8 加载内存，在内存遍历，找到 id=400 的记录，拿到 R4 这一行的数据，好的，大功告成

9. 什么是回表？

回表：当查询的数据在索引树中，找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表

比如：

select * from Temployee where age=32;

需要查询所有列的数据，idx_age 普通索引 不能满足，需要拿到主键 id 的值后，再回到id主键索引查找获取，这个过程就是回表

10. 什么是覆盖索引？

如果我们查询 SQL 的 select * 修改为 select id, age 的话，其实是不需要回表的。因为 id 和 age 的值，都在 idx_age 索引树 的叶子节点上

覆盖索引是 select 的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。

所以，在日常开发中，尽量不要 select *，需要什么查什么，如果出现覆盖索引的情况，查询会快很多

11. 什么是索引下推？

给你这个SQL：

select * from employee where name like '小%' and age=28 and sex='0';

其中，name 和 age 为联合索引 (idx_name_age)

如果是 Mysql5.6 之前，在 idx_name_age 索引树，找出所有名字第一个字是 “小” 的人，拿到它们的主键 id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：

在这里插入图片描述

有些朋友可能觉得奇怪，idx_name_age（name,age) 不是联合索引嘛？为什么选出包含 “小” 字后，不再顺便看下年龄 age 再回表呢，不是更高效嘛？

所以呀，MySQL 5.6 就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

因此，MySQL5.6 版本之后，选出包含 “小” 字后，顺表过滤 age=28

在这里插入图片描述

12. 索引的最左前缀原则

最左前缀匹配原则：在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

比如你建立一个组合索引（a,b,c），其实可以相当于建了（a），（a,b）,(a,b,c) 三个索引，大大提高了索引复用能力

Mysql中索引的最左前缀原则图文剖析（全）

13. 如何知道语句是否走索引查询？

explain 查看 SQL 的执行计划，这样就知道是否命中索引了

当 explain 与 SQL 一起使用时，MySQL 将显示来自优化器的有关语句执行计划的信息：

在这里插入图片描述
一般来说，我们需要重点关注 type、rows、filtered、extra、key

13.1 type

type 表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system：这种类型要求数据库表中只有一条数据，是 const 类型的一个特例，一般情况下是不会出现的
  • const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，速度非常快
  • eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
  • ref : 常用于非主键和唯一索引扫描
  • ref_or_null：这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值的行
  • index_merge：使用了索引合并优化方法，查询使用了两个以上的索引
  • unique_subquery：类似于 eq_ref，条件用了 in 子查询
  • index_subquery：区别于 unique_subquery，用于非唯一索引，可以返回重复值
  • range：常用于范围查询，比如：between … and 或 In 等操作
  • index：全索引扫描
  • all：全表扫描

13.2 rows

该列表示 MySQL 估算要找到我们所需的记录，需要读取的行数。对于 InnoDB 表，此数字是估计值，并非一定是个准确值

13.3 filtered

该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

13.4 extra

该字段包含有关 MySQL 如何解析查询的其他信息，它一般会出现这几个值：

  • Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
  • Using index ：表示是否用了覆盖索引
  • Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于 group by 语句，或者 union 语句
  • Using where : 表示使用了where条件过滤
  • Using index condition：MySQL5.6 之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据

13.5 key

该列表示实际用到的索引。一般配合 possible_keys 列一起看

14. 索引合并

索引合并（index merge）是从 MySQL5.1 开始引入的索引优化机制，在之前的 MySQL 版本中，一条 sql 多个查询条件只能使用一个索引，但是引入了索引合并机制之后，MySQL 在某些特殊的情况下会扫描多个索引，然后将扫描结果进行合并

结果合并会为下面三种情况：

  • 取交集（intersect）
  • 取并集（union）
  • 排序后取并集（sort-union）

删除之前所有的索引，然后为 nam e和 age 各自分别创建一个二级索引 idx_name 和 idx_age

14.1 取交集（intersect）

当执行下面这条 sql 就会出现取交集的情况：

select * from `user` where name = '赵六' and age= 22;

查看执行计划：

在这里插入图片描述

type 是 index_merge，并且 possible_key 和 key 都是 idx_name 和 idx_age，说明使用了索引合并，并且 Extra 有 Using intersect(idx_age,idx_name)

整个过程大致是这样的：分别根据 idx_name 和 idx_age 取出对应的主键 id，之后将主键 id 取交集，那么这部分交集的 id 一定同时满足查询 name = ‘赵六’ and age= 22 的查询条件（仔细想想），之后再根据交集的 id 回表

不过要想使用取交集的联合索引，需要满足各自索引查出来的主键 id 是排好序的，这是为了方便可以快速的取交集

比如下面这条 sql 就无法使用联合索引：

select * from `user` where name = '赵六' and age > 22;

在这里插入图片描述

只能用 name 这个索引，因为 age > 22 查出来的id是无序的，前面在讲索引的时候有说过索引列的排序规则

14.2 取并集（union）

取并集就是将前面例子中的 and 换成 or

select * from `user` where name = '赵六' or age = 22;

前面执行的情况都一样，根据条件到各自的索引上去查，之后对查询的 id 取并集去重，之后再回表

同样地，取并集也要求各自索引查出来的主键id是排好序的，如果查询条件换成 age > 22 时就无法使用取并集的索引合并

select * from `user` where name = '赵六' or age > 22;

14.3 排序后取并集（sort-union）

虽然取并集要求各自索引查出来的主键 id 是排好序的，但是如果遇到没排好序的情况，mysql 会自动对这种情况进行优化，会先对主键 id 排序，然后再取并集，这种情况就叫 排序后取并集（sort-union）。

比如上面提到的无法直接取并集的sql就符合排序后取并集（sort-union）这种情况：

select * from `user` where name = '赵六' or age > 22;

文章来源:https://blog.csdn.net/sco5282/article/details/134834242
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。