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

た入场券 2024-02-05 12:41 23阅读 0赞

## 1. 什么是索引？ ##

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

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

## 2. 索引的优劣势 ##

**优势：**

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

**劣势：**

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

## 3. MySQL 索引有哪些类型 ##

**①：数据结构维度**

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

**②：物理存储维度**

1.  **聚集索引**：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（InnoDB 使用的是聚簇索引，将主键组织到一棵 B+ 树中，而行数据就储存在叶子节点上）
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支不支持哈希索引？][InnoDB]

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

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

![在这里插入图片描述][446b44d4a12c4619873d75067d825730.png]

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

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

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

![在这里插入图片描述][b736257433d44ee98dc0f6d75c7f712c.png]

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

![在这里插入图片描述][338cb55501724a3d96a1499da174b8fa.png]

当遇到 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，这个页我们可以称为 数据页

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

![在这里插入图片描述][8aad668fd23349f583bfee4ec44ec52f.png]

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

![在这里插入图片描述][f304aef7dbaa44b9a201ff438b4857a1.png]

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

#### 3.3.2 单个数据页的数据查找 ####

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

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

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

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

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

![在这里插入图片描述][151a568d03f24971bcec08fff0245712.png]

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

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

![在这里插入图片描述][2fae08b5b03544a398a0fb2fda6cb1cc.png]

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

![在这里插入图片描述][9be4b83acf0a48ed9a90a7af3d251c0c.png]

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

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

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

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

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

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

![在这里插入图片描述][ed863118b4a146e1a4f74261d3bb4fca.png]

此时数据页 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 也会分裂出多个数据页的情况，如下图：

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

![在这里插入图片描述][1b12223d63f84cbb927a3742a0d67c5b.png]

数据页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 索引的叶子节点存的数据就如下图所示：

![在这里插入图片描述][220e4c5e4d7a4dbe93dd2303da31cac9.png]

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

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

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

![在这里插入图片描述][6113c7d062bc46afbc2fcc4f278e1840.png]

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

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

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

### 3.6 复合索引 ###

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

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

![在这里插入图片描述][1060499c1a0842228fbc210d08650865.png]

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

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

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

![在这里插入图片描述][9914571f64e248f194978102207423b5.png]

## 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` 普通索引的索引结构图，大概如下：

![在这里插入图片描述][538a1e6c1b2540f6b2b5be8e16216c54.png]

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

![在这里插入图片描述][a1f6b84de32e4292b1ea218bd1a2f741.png]

这条 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，然后**回表**找出数据行，再去对比年龄和性别等其他字段。如图：

![在这里插入图片描述][07b27ba24f0b4312b36ba117ca574e6a.png]

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

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

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

![在这里插入图片描述][dad5406957e54221bf5d4747774a45db.png]

## 12. 索引的最左前缀原则 ##

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

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

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

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

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

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

![在这里插入图片描述][e209b6e50f1b4cefbb3af299b0e20e14.png]  
一般来说，我们需要重点关注 `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;

查看执行计划：

![在这里插入图片描述][b7556e2d1a2c465e89808b04562b9e7f.png]

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;

![在这里插入图片描述][c8286dc661964042a0c53bbf7b4d071b.png]

只能用 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;

[InnoDB]: https://blog.csdn.net/weixin_41924879/article/details/102755755
[e6a9ca97b04a4452bca83437e8b489ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/b0b21c84eb20476688f42d896eeb9b8b.png
[446b44d4a12c4619873d75067d825730.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/f819a6d858dc4826ae68259c44df0cb1.png
[b736257433d44ee98dc0f6d75c7f712c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/52d9366ab21f4b428cc7e0e432a7dbb4.png
[338cb55501724a3d96a1499da174b8fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/41b3220b796043f4b74ed0920c090fd5.png
[8aad668fd23349f583bfee4ec44ec52f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/13952cad06cb433cb73de4c1579727d7.png
[f304aef7dbaa44b9a201ff438b4857a1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/a66463a035774d5299028b03002bb325.png
[151a568d03f24971bcec08fff0245712.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/af0c1162b9f9444b8294b9b96c71805f.png
[2fae08b5b03544a398a0fb2fda6cb1cc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/70faaccd635c4205a7d67cc717438e85.png
[9be4b83acf0a48ed9a90a7af3d251c0c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/127bba4ab280444aba761e25f391397e.png
[ed863118b4a146e1a4f74261d3bb4fca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/36f936177e584735836af3f89d178306.png
[a9f66b749ae24ee294f5bc395a2f2a25.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/8b822ba243a04454b0026de8450a6689.png
[1b12223d63f84cbb927a3742a0d67c5b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/3ea8ebc070164ce29b112179c11d0044.png
[220e4c5e4d7a4dbe93dd2303da31cac9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/ac158d268be44d72a243ce81828dd473.png
[6113c7d062bc46afbc2fcc4f278e1840.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/89632a000fbf4ed5bc020e1a493f1145.png
[5810ab114ebf491d84f78887669a4935.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/ff65b951dd1e4279b5104e11b9341dbf.png
[1060499c1a0842228fbc210d08650865.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/200583a0a0fb4e07bb84e048ba5603fe.png
[9914571f64e248f194978102207423b5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/ba11f488b0214f64abf635d5caf32127.png
[538a1e6c1b2540f6b2b5be8e16216c54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/b0c8b173f08b47c2b5a6a50320fb3c8e.png
[a1f6b84de32e4292b1ea218bd1a2f741.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/ebd93a6d5aa74e06a3bfdf2aa8aeadb9.png
[07b27ba24f0b4312b36ba117ca574e6a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/bbc47f1afa6a4ae38043a8245bfb3bf8.png
[dad5406957e54221bf5d4747774a45db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/720206205cc147e8a8039628c5d94d12.png
[Mysql]: https://blog.csdn.net/weixin_47872288/article/details/122213482
[e209b6e50f1b4cefbb3af299b0e20e14.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/33f72e5fb4ca45c9b700489de8bb2572.png
[b7556e2d1a2c465e89808b04562b9e7f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/a02acf0e89b84e58999e5a35e8f479ae.png
[c8286dc661964042a0c53bbf7b4d071b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/73216f87d1ea4134a43fcce6af9e3f28.png