【Mysql】索引下推、索引合并详解

总是学不会.

已于 2024-12-15 00:21:41 修改

阅读量1k

点赞数 30

分类专栏： Mysql 文章标签： mysql 数据库后端 java

于 2024-12-14 23:54:56 首次发布

本文链接：https://blog.youkuaiyun.com/m0_70871140/article/details/144478990

版权

10 篇文章

订阅专栏

这篇文章就简单的给大家介绍下索引下推、索引合并

索引下推是 MySQL 5.6 引入的一项优化，用于减少回表次数。它将部分查询条件的过滤工作推到存储引擎层，而不是在 MySQL 服务层处理，从而提升查询效率。

在范围查询或复合索引场景下，MySQL 会利用索引列中的更多信息进行过滤，仅回表获取满足条件的数据。这减少了不必要的回表操作。

未使用索引下推
- 查询语句：
```
SELECT * FROM user1 WHERE name LIKE 'A%' AND age = 40;
```
- 执行过程：
  1. 存储引擎通过索引 name 定位到匹配 name LIKE 'A%' 的数据范围。
  2. 每条记录都回表获取完整行数据。
  3. 回表后，在服务层进一步过滤 age = 40 的条件。
使用索引下推
- 查询语句：
```
SELECT * FROM user1 WHERE name LIKE 'A%' AND age = 40;
```
- 执行过程：
  1. 存储引擎在二级索引中先判断 name LIKE 'A%' 和 age = 40 的条件。
  2. 仅当二级索引满足所有条件时才回表获取完整行数据。
- 优化效果：通过在存储引擎层过滤不符合条件的数据，回表次数大幅减少。

在范围查询中，通过复合索引的多个字段过滤条件，尤其是 SELECT * 查询。

索引合并是 MySQL 从 5.1 开始支持的一种优化技术，允许 SQL 查询同时使用多个单列索引，通过合并结果提高查询性能。

交集（INTERSECT）
- 使用场景：查询条件是 AND。
- 示例：
```
SELECT * FROM user WHERE name = '赵六' AND age = 22;
```
- 执行过程：
  - 分别通过 idx_name 和 idx_age 获取符合条件的主键 ID 列表。
  - 对主键 ID 列表取交集。
  - 根据交集中的 ID 回表查询。
并集（UNION）
- 使用场景：查询条件是 OR。
- 示例：
```
SELECT * FROM user WHERE name = '赵六' OR age = 22;
```
- 执行过程：
  - 分别通过 idx_name 和 idx_age 获取符合条件的主键 ID 列表。
  - 对主键 ID 列表取并集，并去重。
  - 根据去重后的 ID 回表查询。
排序后取并集（SORT-UNION）
- 使用场景：当主键 ID 无序时（上面2种id是有序的）。
- 示例：
```
SELECT * FROM user WHERE name = '赵六' OR age > 22;
```
- 执行过程：
  - 对主键 ID 进行排序。
  - 然后取并集。
  - 根据并集中的 ID 回表查询。