Mysql——》index merge 索引合并

缺乏、安全感 2024-03-17 19:33 65阅读 0赞

> 推荐链接：  
> [总结——》【Java】][Java]  
> [总结——》【Mysql】][Mysql]  
> [总结——》【Redis】][Redis]  
> [总结——》【Kafka】][Kafka]  
> [总结——》【Spring】][Spring]  
> [总结——》【SpringBoot】][SpringBoot]  
> [总结——》【MyBatis、MyBatis-Plus】][MyBatis_MyBatis-Plus]  
> [总结——》【Linux】][Linux]  
> [总结——》【MongoDB】][MongoDB]  
> [总结——》【Elasticsearch】][Elasticsearch]

#### 文章名称 ####

*  示例表结构
 *  一、Intersection合并：查询条件使用and连接
 *   *  1、使用Intersection合并的情况
     *   *  （1）等值匹配
         *  （2）主键列可以是范围匹配
     *  2、分析索引合并过程
     *  3、分析不同的查询执行方式的成本代价
     *   *  （1）只读取一个二级索引的成本
         *  （2）读取多个二级索引之后取交集成本
         *  （3）结论
 *  二、Union合并：不同条件使用or连接
 *   *  1、使用Union合并的情况
     *   *  （1）等值匹配
         *  （2）主键列可以是范围匹配
         *  （3）使用Intersection索引合并的搜索条件
     *  2、分析索引合并过程
 *  三、Sort-Union合并
 *   *  1、分析索引合并过程
 *  四、联合索引替代Intersection索引合并

MySQL在一般情况下执行一个查询时，只会用到单个二级索引，但是在某些特殊情况下，可能会使用到多个二级索引。**使用多个索引**来完成一次查询的执行方法称之为**index merge索引合并**，具体的索引合并算法有下边三种。

## 示例表结构 ##

![33473d5589c3e4fa8501cce849fc2ca9.png_averageHue_f6f5f4_clientId_ua503b46f-fef5-4_id_UqNp1_originHeight_342_originWidth_880_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_u50089b69-3027-48ef-8175-0bd3e0c42f4_title][]![59c237202f7a3e51cfe5dde037691d54.png_averageHue_f3f1f0_clientId_ua503b46f-fef5-4_id_E4ree_originHeight_220_originWidth_851_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_ue648a56f-8d1a-4e38-8522-920d1a43a97_title][]

## 一、Intersection合并：查询条件使用and连接 ##

某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集。

### 1、使用Intersection合并的情况 ###

> **Q：下面2种情况一定会发生索引合并吗？**  
> A：**不一定**。  
> 这两种情况只是发生Intersection索引合并的必要条件，不是充分条件。也就是说即使符合Intersection的条件，也不一定发生Intersection索引合并，这得看优化器的判断。  
>   
>   
> 优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

#### （1）等值匹配 ####

*  **二级索引**：必须是**等值匹配**的情况
 *  **联合索引**：在联合索引中的**每个列都必须等值匹配**，不能出现只匹配部分列的情况。

-- 不用使用索引合并，因为对order_no进行了范围匹配
    SELECT * FROM order_exp WHERE order_no> 'a' AND expire_time = 'a';
    
    -- 不用使用索引合并，因为insert_time使用到的联合索引u_idx_day_status中的order_status和expire_time列并没有出现在搜索条件中
    SELECT * FROM order_exp WHERE order_no = 'a' AND insert_time = 'a';

-- 使用索引合并，因为order_no 和 expire_time 索引都是等值匹配
    SELECT * FROM order_exp WHERE order_no = 'a' AND expire_time = 'b';

> **Q：为什么在二级索引列都是等值匹配的情况下可能使用Intersection索引合并？**  
> A：因为只有在这种情况下，根据二级索引查询出的结果集是按照主键值排序的。Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易。

#### （2）主键列可以是范围匹配 ####

-- 使用索引合并
    SELECT * FROM order_exp WHERE id > 100 AND expire_time = 'a';

![1337ee333aeec7a9d0e9001ed3d37988.png_averageHue_ededed_clientId_ua503b46f-fef5-4_id_yojnr_originHeight_540_originWidth_1238_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_u01390870-37b4-4a76-b588-1ab83b87c41_title][]

![f064220681f97793f59e284213dadb03.png_averageHue_f2f7f2_clientId_ua503b46f-fef5-4_id_NtQ4a_originHeight_448_originWidth_1076_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_u81fafaf8-9373-4e5f-8188-b2788b5ac8e_title][]

> **Q：为什么主键列可以是范围匹配？**  
> A：因为主键的索引是有序的，而二级索引的用户记录是由索引列 + 主键构成的，所以根据范围匹配出来的主键就是乱序的，导致回表开销很大。

### 2、分析索引合并过程 ###

SELECT * FROM order_exp WHERE order_no = 'a' AND expire_time = 'b';

1.  从idx\_order\_no二级索引对应的B+树中取出order\_no='a’的相关记录。
2.  从idx\_expire\_time二级索引对应的B+树中取出expire\_time='b’的相关记录。
3.  二级索引的记录都是由索引列 + 主键构成的，可以计算出这两个结果集中id值的交集。
4.  按照上一步生成的id值列表进行回表操作（从聚簇索引中把指定id值的完整用户记录取出来）

### 3、分析不同的查询执行方式的成本代价 ###

> 为什么不直接使用idx\_order\_no或者idx\_expire\_time只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？

#### （1）只读取一个二级索引的成本 ####

1.  按照某个搜索条件读取一个二级索引
2.  然后根据从该二级索引得到的主键值进行回表操作
3.  最后再过滤其他的搜索条件

#### （2）读取多个二级索引之后取交集成本 ####

1.  按照不同的搜索条件分别读取不同的二级索引
2.  然后从多个二级索引得到的主键值取交集
3.  最后根据主键值进行回表操作

#### （3）结论 ####

虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

## 二、Union合并：不同条件使用or连接 ##

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引。

### 1、使用Union合并的情况 ###

> **Q：下面3种情况一定会发生索引合并吗？**  
> A：**不一定**。  
> 这三种情况只是发生Union索引合并的必要条件，不是充分条件。也就是说即使符合Union的条件，也不一定发生Union索引合并，这得看优化器的心情（判断）。  
>   
>   
> 优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

#### （1）等值匹配 ####

分析同Intersection合并

-- 使用Union合并
    SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b';

-- 不用使用索引合并，因为各个二级索引列必须等值匹配
    SELECT * FROM order_exp WHERE order_no< 'a' OR expire_time> 'z'

#### （2）主键列可以是范围匹配 ####

分析同Intersection合并

#### （3）使用Intersection索引合并的搜索条件 ####

搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集。

-- 使用Union合并
    SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c'
    OR (order_no = 'a' AND expire_time = 'b');

### 2、分析索引合并过程 ###

-- 使用Union合并
    SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c'
    OR (order_no = 'a' AND expire_time = 'b');

1.  首先按照搜索条件order\_no = ‘a’ AND expire\_time = 'b’从索引idx\_order\_no和idx\_expire\_time中使用Intersection索引合并的方式得到一个主键集合。
2.  然后按照搜索条件 insert\_time =‘a’ AND order\_status = ‘b’ AND expire\_time = 'c’从联合索引u\_idx\_day\_status中得到另一个主键集合。
3.  采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

## 三、Sort-Union合并 ##

首先按照二级索引记录的主键值进行排序，然后按照Union索引合并方式执行的方式称之为Sort-Union索引合并。这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

> **Q：下面这种情况一定会发生索引合并吗？**  
> A：**不一定**。  
> 查询条件符合了这些情况也不一定就会采用Sort-Union索引合并，也得看优化器的心情。  
> 优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Sort-Union索引合并后进行访问的代价比全表扫描更小时才会使用Sort-Union索引合并。

### 1、分析索引合并过程 ###

SELECT * FROM order_exp WHERE order_no< 'a' OR expire_time> 'z';

1.  先根据order\_no< 'a’条件从idx\_order\_no二级索引中获取记录，并按照记录的主键值进行排序
2.  再根据expire\_time>'z’条件从idx\_expire\_time二级索引中获取记录，并按照记录的主键值进行排序
3.  因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

> **Q：为什么不能使用Union合并？**  
> A：使用Union索引合并，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到。

## 四、联合索引替代Intersection索引合并 ##

-- 建立联合索引，查询又快又好（既不用多读一棵B+树，也不用合并结果）
    SELECT * FROM order_exp WHERE order_no= 'a' And expire_time= 'z';

ALTER TABLE order_exp drop index idx_order_no;
    ALTER TABLE order_exp drop idx_expire_time;
    ALTER TABLE add index idx_order_no_expire_time(order_no,expire_time);

![1f1cc5f6773b4902e0c2666c2aaace2b.png_averageHue_faf5f3_clientId_ua503b46f-fef5-4_id_uM6xb_originHeight_123_originWidth_892_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_ud2131a3e-bb02-461c-9ea6-f50fccf48a5_title][]

[Java]: https://blog.csdn.net/weixin_43453386/article/details/84788317
[Mysql]: https://blog.csdn.net/weixin_43453386/article/details/88667709
[Redis]: https://blog.csdn.net/weixin_43453386/article/details/127966762
[Kafka]: https://blog.csdn.net/weixin_43453386/article/details/128189472
[Spring]: https://blog.csdn.net/weixin_43453386/article/details/124900806
[SpringBoot]: https://blog.csdn.net/weixin_43453386/article/details/84788714
[MyBatis_MyBatis-Plus]: https://blog.csdn.net/weixin_43453386/article/details/84788053
[Linux]: https://blog.csdn.net/weixin_43453386/article/details/89241912
[MongoDB]: https://blog.csdn.net/weixin_43453386/article/details/84788450
[Elasticsearch]: https://blog.csdn.net/weixin_43453386/article/details/108583782
[33473d5589c3e4fa8501cce849fc2ca9.png_averageHue_f6f5f4_clientId_ua503b46f-fef5-4_id_UqNp1_originHeight_342_originWidth_880_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_u50089b69-3027-48ef-8175-0bd3e0c42f4_title]: https://img-blog.csdnimg.cn/img_convert/33473d5589c3e4fa8501cce849fc2ca9.png#averageHue=#f6f5f4&clientId=ua503b46f-fef5-4&id=UqNp1&originHeight=342&originWidth=880&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&taskId=u50089b69-3027-48ef-8175-0bd3e0c42f4&title=
[59c237202f7a3e51cfe5dde037691d54.png_averageHue_f3f1f0_clientId_ua503b46f-fef5-4_id_E4ree_originHeight_220_originWidth_851_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_ue648a56f-8d1a-4e38-8522-920d1a43a97_title]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/cff0b09b4dcb47c1ace2565ff1ef63f0.png
[1337ee333aeec7a9d0e9001ed3d37988.png_averageHue_ededed_clientId_ua503b46f-fef5-4_id_yojnr_originHeight_540_originWidth_1238_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_u01390870-37b4-4a76-b588-1ab83b87c41_title]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/2be9b6ca97ac49dd8ae73e1713cfc5a3.png
[f064220681f97793f59e284213dadb03.png_averageHue_f2f7f2_clientId_ua503b46f-fef5-4_id_NtQ4a_originHeight_448_originWidth_1076_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_u81fafaf8-9373-4e5f-8188-b2788b5ac8e_title]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/0ca41eaff34c46f3a90819267a02e919.png
[1f1cc5f6773b4902e0c2666c2aaace2b.png_averageHue_faf5f3_clientId_ua503b46f-fef5-4_id_uM6xb_originHeight_123_originWidth_892_originalType_binary_ratio_1_rotation_0_showTitle_false_status_done_style_none_taskId_ud2131a3e-bb02-461c-9ea6-f50fccf48a5_title]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/196975c3ae5740fc9d56a2b3e721f5a3.png