left join 和子查询效率_子查询是怎么执行的

号外号外,《MySQL是怎样运行的:从根儿上理解MySQL》书籍即将在掘金上架,本文是其中的一个章节的一小丢丢,书中的内容没有经过多少小白测试,有想当早鸟的同学来一起测试的可以联系我,或者加我微信:

b3c3378f90a9fc677b5701e2d7c02587.png

子查询的基本语法

略~

小白们眼中子查询的执行方式

在我还是一个单纯无知的少年时,觉得子查询的执行方式是这样的:

  • 如果该子查询是不相关子查询,比如下边这个查询:

    SELECT * FROM s1 
        WHERE key1 IN (SELECT common_field FROM s2);

    我年少时觉得这个查询是的执行方式是这样的:

    • 先单独执行(SELECT common_field FROM s2)这个子查询。

    • 然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 IN (...)

  • 如果该子查询是相关子查询,比如下边这个查询:

    SELECT * FROM s1 
        WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);

    这个查询中的子查询中出现了s1.key2 = s2.key2这样的条件,意味着该子查询的执行依赖着外层查询的值,所以我年少时觉得这个查询的执行方式是这样的:

    • 先从外层查询中获取一条记录,本例中也就是先从s1表中获取一条记录。

    • 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值,本例中就是从s1表中获取的那条记录中找出s1.key2列的值,然后执行子查询。

    • 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立,如果成立,就把外层查询的那条记录加入到结果集,否则就丢弃。

    • 再次执行第一步,获取第二条外层查询中的记录,依次类推~

告诉我不只是我一个人是这样认为的,这样认为的同学请举起你们的双手~~~ 哇唔,还真不少~

其实设计MySQL的大叔想了一系列的办法来优化子查询的执行,大部分情况下这些优化措施其实挺有效的,但是保不齐有的时候马失前蹄,下边我们详细唠叨各种不同类型的子查询具体是怎么执行的。

小贴士: 我们下边即将唠叨的关于MySQL优化子查询的执行方式的事儿都是基于MySQL5.7这个版本的,以后版本可能有更新的优化策略!

标量子查询、行子查询的执行方式

我们经常在下边两个场景中使用到标量子查询或者行子查询:

  • SELECT子句中,我们前边说过的在查询列表中的子查询必须是标量子查询。

  • 子查询使用=><>=<=<>!=<=>等操作符和某个操作数组成一个布尔表达式,这样的子查询必须是标量子查询或者行子查询。

对于上述两种场景中的不相关标量子查询或者行子查询来说,它们的执行方式是简单的,比方说下边这个查询语句:

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

它的执行方式和年少的我想的一样:

  • 先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询。

  • 然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = ...

也就是说,对于包含不相关的标量子查询或者行子查询的查询语句来说,MySQL会分别独立的执行外层查询和子查询,就当作两个单表查询就好了。

对于相关的标量子查询或者行子查询来说,比如下边这个查询:

SELECT * FROM s1 WHERE 
    key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

事情也和年少的我想的一样,它的执行方式就是这样的:

  • 先从外层查询中获取一条记录,本例中也就是先从s1表中获取一条记录。

  • 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值,本例中就是从s1表中获取的那条记录中找出s1.key3列的值,然后执行子查询。

  • 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立,如果成立,就把外层查询的那条记录加入到结果集,否则就丢弃。

  • 再次执行第一步,获取第二条外层查询中的记录,依次类推~

也就是说对于一开始唠叨的两种使用标量子查询以及行子查询的场景中,MySQL优化器的执行方式并没有什么新鲜的。

IN子查询优化

物化表的提出

对于不相关的IN子查询,比如这样:

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

我们最开始的感觉就是这种不相关的IN子查询和不相关的标量子查询或者行子查询是一样一样的,都是把外层查询和子查询当作两个独立的单表查询来对待,可是很遗憾的是设计MySQL的大叔为了优化IN子查询倾注了太多心血(毕竟IN子查询是我们日常生活中最常用的子查询类型),所以整个执行过程并不像我们想象的那么简单(>_

其实说句老实话,对于不相关的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 ...,这样性能耗费可就多了。

于是乎设计MySQL的大叔想了一个招:不直接将不相关子查询的结果集当作外层查询的参数,而是将该结果集写入一个临时表里。写入临时表的过程是这样的:

  • 该临时表的列就是子查询结果集中的列。

  • 写入临时表的记录会被去重。

    我们说IN语句是判断某个操作数在不在某个集合中,集合中的值重不重复对整个IN语句的结果并没有啥子关系,所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小,更省地方~

    小贴士: 临时表如何对记录进行去重?这不是小意思嘛,临时表也是个表,只要为表中记录的所有列建立主键或者唯一索引就好了嘛~

  • 一般情况下子查询结果集不会大的离谱,所以会为它建立基于内存的使用Memory存储引擎的临时表,而且会为该表建立哈希索引。

    小贴士: IN语句的本质就是判断某个操作数在不在某个集合里,如果集合中的数据建立了哈希索引,那么这个匹配的过程就是超级快的。 有同学不知道哈希索引是什么?我这里就不展开了,自己上网找找吧,不会了再来问我~

    如果子查询的结果集非常大,超过了系统变量tmp_table_size或者max_heap_table_size,临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录,索引类型也对应转变为B+树索引。

设计MySQL的大叔把这个将子查询结果集中的记录保存到临时表的过程称之为物化(英文名:Materialize)。为了方便起见,我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引(基于内存的物化表有哈希索引,基于磁盘的有B+树索引),通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快,从而提升了子查询语句的性能。

物化表转连接
将子查询转换为semi-join

semi-join的适用条件

不适用于semi-join的情况

[NOT] EXISTS子查询的执行

吧啦吧啦 ...

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页