Mysql查询优化之IN子查询优化方法详解
作者:云深n不知处 发布时间:2024-01-16 11:38:07
物化表
首先提出一个不相关的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 … ,这样性能耗费可就多了。
所以提出一个解决方案:不直接将不相关子查询的结果集当作外层查询的参数,而是将该结果集写入一个临时表里。
临时表的特性:
该临时表的列就是子查询结果集中的列。
写入临时表的记录会被去重。
一般情况下子查询结果集不会大的离谱,所以会为它建立基于内存的使用 Memory 存储引擎的临时表,而且会为该表建立哈希索引。
如果子查询的结果集非常大,超过了系统变量 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 表作为驱动表的话,总查询成本由下边几个部分组成:
物化子查询时需要的成本
扫描 s1 表时的成本
s1表中的记录数量 × 通过 m_val = xxx 对 materialized_table 表进行单表访问的成本(物化表中的记录是不重复的,并且为物化表中的列建立了索引,所以这个步骤显然是非常快的)。
如果使用 materialized_table 表作为驱动表的话,总查询成本由下边几个部分组成:
物化子查询时需要的成本
扫描物化表时的成本
物化表中的记录数量 × 通过 key1 = xxx 对 s1 表进行单表访问的成本
来源:https://blog.csdn.net/qq_50629351/article/details/128926613
猜你喜欢
- 这篇文章主要介绍了如何在mac环境中用python处理protobuf,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学
- 一、zipfile模块的简述zipfile是python里用来做zip格式编码的压缩和解压缩的,由于是很常见的zip格式,所以这个模块使用频
- 使用正则表达式的几个步骤:1、用import re 导入正则表达式模块;2、用re.compile()函数创建一个Regex对象;3、用Re
- PHP计算字符串用strlen()只能得到字符串长度,不是宽高像素,使用到了php函数ImageTTFBBox(),就可以根据字体的大小和所
- Background之前数据库只区分了Android,IOS两个平台,游戏上线后现在PM想要区分国服,海外服,港台服。这几个字段从前端那里的
- Selenium一、简介selenium是一个用于Web应用自动化程序测试的工具,测试直接运行在浏览器中,就像真正的用户在操作一样selen
- 最近写了个python抓取必应搜索首页http://cn.bing.com/的背景图片并将此图片更换为我的电脑桌面的程序,在正则匹配图片ur
- 这几天转了几个内容包含日语的贴,结果发现搜索数据库时出现“内存溢出”错误。上网搜索寻求答案未果。最后才发现这就是传说中的“日文 26 个片假
- 1.regex-coach ——正则表达式工具2. IECookiesView——IE的cookie查看工具3.Flex Trace Pan
- rebase在git中是一个非常有魅力的命令,使用得当会极大提高自己的工作效率;相反,如果乱用,会给团队中其他人带来麻烦。它的作用简要概括为
- 前言最近用Django写项目的时候用到了数据的传递,一窍不通,查了点资料。记录一下。水平不高,瓜不保熟。 从两方面来说:从后端传递
- 本文介绍了python十进制和二进制的转换方法(含浮点数),分享给大家,也给自己留个笔记,具体如下:我终于写完了 , 十进制转二进制的小数部
- 目录最终版本过程借鉴代码思考urllib.request和requestsBeautifulSoup优化处理总结代码复制可直接使用,记得pi
- 某日,路上收到用户咨询,为了清除空间,想删除某200多G大表数据,且已经确认此表不再有业务访问,于是执行了一条命令‘de
- pillowPillow是PIL的一个派生分支,但如今已经发展成为比PIL本身更具活力的图像处理库。pillow可以说已经取代了PIL,将其
- Hypothesis是Python的一个高级测试库。它允许编写测试用例时参数化,然后生成使测试失败的简单易懂的测试数据。可以用更少的工作在代
- 1.字符串定义# coding:utf-8if __name__ == '__main__': &
- 看代码吧~import numpy as npa = np.array([[2, 4, 6, 1], [1, 5, 2, 9]])print
- 最近准备做一个关于scrapy框架的实战,爬取腾讯社招信息并存储,这篇博客记录一下创建项目的步骤pycharm是无法创建一个scrapy项目
- asin()方法返回x的反正弦,以弧度表示。语法以下是asin()方法语法:asin(x)注意:此函数是无法直接访问的,所以我们