登录
首页 >  数据库 >  MySQL

Mysql查询优化之IN子查询优化方法详解

来源:脚本之家

时间:2023-02-25 08:53:45 231浏览 收藏

对于一个数据库开发者来说,牢固扎实的基础是十分重要的,golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《Mysql查询优化之IN子查询优化方法详解》,主要介绍了优化、in子查询,希望对大家的知识积累有所帮助,快点收藏起来吧,否则需要时就找不到了!

物化表

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

SELECT * FROM s1 
 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

对于不相关的 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 ,那么这个查询其实可以从下边两种角度来看待:

SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a');

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

在子查询对应的物化表中,则该记录会被加入最终的结果集。画个图表示一下就是这样:

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

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

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

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

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

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

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

总结

以上就是《Mysql查询优化之IN子查询优化方法详解》的详细内容,更多关于mysql的资料请关注golang学习网公众号!

声明:本文转载于:脚本之家 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>