mysql查询优化之IN子查询优化方法

阳光穿透心脏的1/2处 2024-03-22 20:05 69阅读 0赞

项目中有需要,使用MySQL的in子查询,查询符合in子查询集合中条件的数据,但是没想到的是,MySQL的in子查询会如此的慢,让人无法接受,下面这篇文章主要给大家介绍了关于Mysql查询优化之IN子查询优化的相关资料,需要的朋友可以参考下

### 物化表 ###

首先提出一个不相关的IN子查询

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>s1 </code></p> <p><code>&nbsp;</code><code>WHERE</code> <code>key1 </code><code>IN</code> <code>(</code><code>SELECT</code> <code>common_field </code><code>FROM</code> <code>s2 </code><code>WHERE</code> <code>key3 = </code><code>'a'</code><code>);</code></p> </td> 
  </tr> 
 </tbody> 
</table>

对于不相关的 IN 子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层

查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这  
些问题：

*  结果集太多，可能内存中都放不下～
 *  对于外层查询来说，如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，这就导致：

> 无法有效的使用索引，只能对外层查询进行全表扫描。  
> 在对外层查询执行全表扫描时，由于 IN 子句中的参数太多，这会导致检测一条记录是否符合和 IN 子句中的参数匹配花费的时间太长。  
> 比如说 IN 子句中的参数只有两个：  
> SELECT \* FROM tbl\_name WHERE column IN (a, b);  
> 这样相当于需要对 tbl\_name 表中的每条记录判断一下它的 column 列是否符合 column = a OR column= b 。在 IN 子句中的参数比较少时这并不是什么问题，如果 IN 子句中的参数比较多时，比如这样：  
> SELECT \* FROM tbl\_name WHERE column IN (a, b, c …, …);  
> 那么这样每条记录需要判断一下它的 column 列是否符合 column = a OR column = b OR column = c  
> OR … ，这样性能耗费可就多了。

所以提出一个解决方案：**不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。**

临时表的特性：

1.  该临时表的列就是子查询结果集中的列。
2.  写入临时表的记录会被去重。
3.  一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。
4.  如果子查询的结果集非常大，超过了系统变量 tmp\_table\_size 或者 max\_heap\_table\_size ，临时表会转而  
    使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。  
    **这个将子查询结果集中的记录保存到临时表的过程称之为 物化。**

### 物化表转连接 ###

当我们把子查询进行物化之后，假设子查询物化表的名称为 materialized\_table ，该物化表存储的子查询结果集的列为 m\_val ，那么这个查询其实可以从下边两种角度来看待：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>s1</code></p> <p><code>WHERE</code> <code>key1 </code><code>IN</code> <code>(</code><code>SELECT</code> <code>common_field </code><code>FROM</code> <code>s2 </code><code>WHERE</code> <code>key3 = ‘a');</code></p> </td> 
  </tr> 
 </tbody> 
</table>

从表 s1 的角度来看待，整个查询的意思其实是：对于 s1 表中的每条记录来说，如果该记录的 key1 列的值

在子查询对应的物化表中，则该记录会被加入最终的结果集。

从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在 s1 表  
中找到对应的 key1 列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。

也就是说其实上边的查询就相当于表 s1 和子查询物化表 materialized\_table 进行内连接：

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>SELECT</code> <code>s1.* </code><code>FROM</code> <code>s1 </code><code>INNER</code> <code>JOIN</code> <code>materialized_table </code><code>ON</code> <code>key1 = m_val;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

如果使用 s1 表作为驱动表的话，总查询成本由下边几个部分组成：

1.  物化子查询时需要的成本
2.  扫描 s1 表时的成本
3.  s1表中的记录数量 × 通过 m\_val = xxx 对 materialized\_table 表进行单表访问的成本（物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。

如果使用 materialized\_table 表作为驱动表的话，总查询成本由下边几个部分组成：

1.  物化子查询时需要的成本
2.  扫描物化表时的成本
3.  物化表中的记录数量 × 通过 key1 = xxx 对 s1 表进行单表访问的成本

###  ###

到此这篇关于Mysql查询优化之IN子查询优化方法的文章就介绍到这了,希望可以帮到你。

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][Link 1]

[Link 1]: https://www.weidianyuedu.com