精华内容
下载资源
问答
  • 2018年4月20日,移动某平台江苏某业务系统发起SQL调优请求,在本次MySQL调试过程中遇到了exists关联子查询导致的sql性能及其低下,并且相关的SQL语句执行速度及其不稳定忽快忽慢;通过重写相关SQL的exists部分修改成...
        2018年4月20日,移动某平台江苏某业务系统发起SQL调优请求,在本次MySQL调试过程中遇到了exists关联子查询导致的sql
    性能及其低下,并且相关的SQL语句执行速度及其不稳定忽快忽慢;通过重写相关SQL的exists部分修改成等值子查询,相关sql的
    性能得到极大提高,并且执行速度比较稳定。本次SQL优化过程记录如下:
        1、环境信息:
        操作系统版本:CENTOS 7.2.1151
        数据库版本:5.7.17-log
        2、查看MySQL慢日志相关信息
    ysql>show variables like '%slow_query_%';
    -+-------------------------------------------+--------------------------------------------------------------------------------+
    |        Variable_name                        |                                    Value                                      |
    -+-------------------------------------------+--------------------------------------------------------------------------------+
    |slow_query_log                               | ON                                                                            |
    |slow_query_log_file                         |/data/mysql/db/elog/slow.log                                                    |
    -+-------------------------------------------+--------------------------------------------------------------------------------+
        2、使用pt工具分析MySQL的慢日志
    cd  /data/mysql/db/elog
    pt-query-digest slow.log  --since '2018-04-13 00:00:00'  --until '2018-04-19 00:00:00' >>slow_report_log
        3、查看慢日志分析报告slow_report_log

    通过慢日志分析报告发现两条运行缓慢的SQL语句Query_ID分别是0xEA33702BDD78E0BA、0x56B6B418ADAAB135,其中
    0xEA33702BDD78E0BA的响应时间占据了整个数据库时间的97.1%,是重点优化对象。
        4、明确需要优化的对象
    --第一条需要优化的重点对象0xEA33702BDD78E0BA

    --SQL执行计划

    --SQL执行统计信息概要

    --第二条要优化的SQL对象
    0x56B6B418ADAAB135

    --SQL执行计划

    --SQL执行统计信息概要

        5、首先看第一条SQL语句,自己的优化思路,确定SQL语句中慢的具体位置
    --对第一条SQL语句拆分,以union为界分两部分
    --0xEA33702BDD78E0BA第一部分:0xEA33702BDD78E0BA_part1

    0xEA33702BDD78E0BA_part1部分SQL语句查询只有13条记录,执行速度非常快0.00秒完成

    --0xEA33702BDD78E0BA第二部分:0xEA33702BDD78E0BA_part2

    0xEA33702BDD78E0BA_part2部分SQL(该部分查询正常出结果是103条数据)语句执行超过10s,超时退出

        6、接下来着重关注0xEA33702BDD78E0BA第二部分:0xEA33702BDD78E0BA_part2

    观察SQL语句发现有EXISTS关联子查询,我的理解是,原先sql被优化器改写,主查询执行多次,每次主查询sql都不同,
    都会发起物理读盘扫表。于是考虑将其改为等值连接子查询:

    --SQL改写后(查询结果集与修改前一致):两个等值查询表结果集在内存,接下来是最终结果集匹配和过滤,执行速度只有0.01秒

    7、修改后的完整SQL如下

    --修改后的sql执行速度,有原先的超过10s超时中断退出降低到0.00秒以下。

    --SQL改写后的执行计划


    8、第二条SQL语句采用第一条SQL语句优化方法,将exists关联子查询改写为表关联的等值子查询
    --改写前的执行效率

    --改写后的执行效率

    --改写后的SQL执行计划


    9、总结
    通过观察SQL改写前后的执行计划,可以发现改写后的SQL 执行中间结果集均已缓存到内存(Using join buffer(Block Nested Loop)),内存结果集匹配过滤降低了表的物理IO,从而提高了SQL的执行性能。



    展开全文
  • SQL语句的复杂查询语句,包括标量子查询及关联子查询

    子查询:将用来定义视图的SELECT语句直接用于FROM子句中。

    SELECT <列名1>, <列名2>,FROM (<SELECT语句>) AS <视图名称>;
    /*
    内层的SELECT语句会先执行;
    Oracle的FROM子句中不能使用AS,直接删除即可;
    */
    

    上述代码等价于:

    CREATE VIEW 视图名称(<视图列名1>, <视图列名2>,)
    AS
    <SELECT语句>
    

    标量子查询

    返回单一值的子查询是标量子查询,即必须且只能返回1行1列的结果。能够使用常数或列名的地方,无论是SELECT子句、GROUP BY子句、HAVING子句,还是ORDER BY子句,几乎所有的地方都能使用(SELECT标量子查询)。

    例:在SELECT子句中使用标量子查询

    SELECT product_id, 
           product_name, 
           sale_price,
           (SELECT AVG(sale_price)
            FROM Product) AS avg_price
           -- 标量子查询
    FROM Product; 
    

    标量子查询不能返回多行结果:

    SELECT product_id, 
           product_name, 
           sale_price,
           (SELECT AVG(sale_price)
           	FROM Product
           	GROUP BY product_type) AS avg_price
           -- 该子查询返回了多行结果,产生错误
    FROM Product; 
    

    关联子查询

    在细分的组内进行比较时,需要使用关联子查询。

    例:关联子查询

    SELECT <列名1>, <列名2>,FROM <表名> AS <别名1>
    WHERE <列名2> > (SELECT <表达式>
                     FROM <表名> AS <别名2>
                     WHERE <别名1>.<列名1> = <别名2>.<列名1>);
                     -- 关联子查询可对集合根据<列名1>进行切分
    
    SELECT product_id, 
           product_name, 
           sale_price,
           (SELECT AVG(sale_price)
           	FROM Product AS P2
           	WHERE P1.product_type = P2.product_type
           	GROUP BY P1.product_type) AS avg_sale_price
           -- 该关联子查询可当作加了条件的标量子查询
    FROM Product AS P1;
    

    关联子查询中别名的作用范围为子查询内部。

    参考资料

    《SQL基础教程》(第2版)[日] MICK著;孙淼,罗勇译

    展开全文
  • 一文详解SQL关联子查询

    千次阅读 2021-03-30 13:42:20
    简介:本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。 本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。 在背景介绍中我们将讲讲常见的关联子查询的...

    简介: 本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。

    image.png
    本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。

    在背景介绍中我们将讲讲常见的关联子查询的语义,关联子查询语法的好处以及其执行时对数据库系统的挑战。第二章中我们将主要介绍如何将关联子查询改写为普通的查询的形式,也就是解关联。第三章中我们会介绍解关联中的优化方法。

    一 背景介绍

    关联子查询是指和外部查询有关联的子查询,具体来说就是在这个子查询里使用了外部查询包含的列。

    因为这种可以使用关联列的灵活性,将sql查询写成子查询的形式往往可以极大的简化sql以及使得sql查询的语义更加方便理解。下面我们通过使用tpch schema来举几个例子以说明这一点。tpch schema是一个典型的订单系统的database,包含customer表,orders表,lineitem表等,如下图:

    image.png

    假如我们希望查询出“所有从来没有下过单的客户的信息”,那么我们可以将关联子查询作为过滤条件。使用关联子查询写出的sql如下。可以看到这里的not exists子查询使用列外部的列c_custkey。

    -- 所有从来没有下过单的客户的信息
    select c_custkey
    from
      customer
    where
      not exists (
        select
          *
        from
          orders
        where
          o_custkey = c_custkey
      )

    如果不写成上面的形式,我们则需要考虑将customer和orders两个表先进行left join,然后再过滤掉没有join上的行,同时我们还需要markorder的每一行,使得本来就是null的那些。查询sql如下:

    -- 所有从来没有下过单的客户的信息
    select c_custkey
    from
      customer
      left join (
        select
          distinct o_custkey
        from
          orders
      ) on o_custkey = c_custkey
    where
      o_custkey is null

    从这个简单的例子中就可以看到使用关联子查询降低了sql编写的难度,同时提高了可读性。

    除了在exists/in子查询中使用关联列,关联子查询还可以出现在where中作为过滤条件需要的值。比如tpch q17中使用子查询求出一个聚合值作为过滤条件。

    -- tpch q17
    SELECT Sum(l1.extendedprice) / 7.0 AS avg_yearly 
    FROM   lineitem l1, 
           part p
    WHERE  p.partkey = l1.partkey 
           AND p.brand = 'Brand#44' 
           AND p.container = 'WRAP PKG' 
           AND l1.quantity < (SELECT 0.2 * Avg(l2.quantity) 
                             FROM   lineitem l2
                             WHERE  l2.partkey = p.partkey);

    除了出现在where里面,关联子查询可以出现在任何允许出现单行(scalar)的地方,比如select列表里。如果我们需要做报表汇总一些customer的信息,希望对每一个customer查询他们的订单总额,我们可以使用下面包含关联子查询的sql。

    -- 客户以及对应的消费总额
    select
      c_custkey,
      (
        select sum(o_totalprice)
        from
          orders
        where o_custkey = c_custkey 
        )
    from
      customer

    更复杂一些的比如,我们希望查询每一个customer及其对应的在某个日期前已经签收的订单总额。利用关联子查询只需要做一些小的改变如下:

    select
      c_custkey,
      (
        select
          sum(o_totalprice)
        from
          orders
        where
          o_custkey = c_custkey
          and '2020-05-27' > (
            select
              max(l_receiptdate)
            from
              lineitem
            where
              l_orderkey = o_orderkey
          ) 
        )
    from
       customer

    看了这些例子,相信大家都已经感受到使用关联子查询带来的便捷。但是同时关联子查询也带来了执行上的挑战。为了计算关联结果的值(子查询的输出),需要iterative的执行方式。

    以之前讨论过的tpch 17为例子:

    SELECT Sum(l1.extendedprice) / 7.0 AS avg_yearly 
    FROM   lineitem l1, 
           part p
    WHERE  p.partkey = l1.partkey 
           AND p.brand = 'Brand#44' 
           AND p.container = 'WRAP PKG' 
           AND l1.quantity < (SELECT 0.2 * Avg(l2.quantity) 
                             FROM   lineitem l2
                             WHERE  l2.partkey = p.partkey);

    这里的子查询部分使用了外部查询的列 p.partkey。

    SELECT 0.2 * Avg(l2.quantity) 
    FROM   lineitem l2
    WHERE  l2.partkey = p.partkey  -- p.partkey是外部查询的列

    优化器将这个查询表示为如下图的逻辑树:

    image.png

    如果数据库系统不支持查看逻辑树,可以通过explain命令查看物理计划,一般输出如下图:

    +---------------+
    | Plan Details  |
    +---------------+
     1- Output[avg_yearly] avg_yearly := expr
     2    -> Project[] expr := (`sum` / DOUBLE '7.0')
     3        - Aggregate sum := `sum`(`extendedprice`)
     4            -> Filter[p.`partkey` = l1.`partkey` AND `brand` = 'Brand#51' AND `container` = 'WRAP PACK' AND `quantity` < `result`]
     5                - CorrelatedJoin[[p.`partkey`]]
     6                    - CrossJoin
     7                        - TableScan[tpch:lineitem l1]
     8                        - TableScan[tpch:part p]
     9                    - Scalar
    10                        -> Project[] result := (DOUBLE '0.2' * `avg`)
    11                            - Aggregate avg := `avg`(`quantity`)
    12                                -> Filter[(p.`partkey` = l2`partkey`)] 
    13                                    - TableScan[tpch:lineitem l2]

    我们将这个连接外部查询和子查询的算子叫做CorrelatedJoin(也被称之为lateral join, dependent join等等。它的左子树我们称之为外部查询(input),右子树称之为子查询(subquery)。子查询中出现的外部的列叫做关联列。在栗子中关联列为p.partkey。

    例子中对应的逻辑计划和相关定义如下图所示,explain返回结果中第6-8行为外部查询,9-13行为子查询,关联部位在子查询中第12行的filter。

    image.png

    这个算子的输出等价于一种iterative的执行的结果。也就将左子树的每一行关联列的值带入到右子树中进行计算并返回一行结果。有些类似将子查询看成一个user defined function(udf),外部查询的关联列的值作为这个udf的输入参数。需要注意的是,我们需要子查询是确定的,也就是对同样值的关联列,每次运行子查询返回的结果应该是确定的。

    在上图的栗子中,如果外部查询有一行的p.partkey的值为25,那么这一行对应的correlatedjoin的输出就是下面这个查询的结果:

    -- p.partkey = 25 时对应的子查询为
    SELECT 0.2 * Avg(l2.quantity) 
    FROM   lineitem l2
    WHERE  l2.partkey = 25

    image.png
    image.png

    需要注意的是,如果计算结果为空集,则返回一行null。而如果运行中子查询返回了超过一行的结果,应该报运行时错误。在逻辑计划里,用enforcesinglerow这个node来约束。

    从上面的介绍中可以发现,CorrelatedJoin这个算子打破了以往对逻辑树自上而下的执行模式。普通的逻辑树都是从叶子节点往根结点执行的,但是CorreltedJoin的右子树会被带入左子树的行的值反复的执行。

    correlatedjoinnode的输出就是在外部查询的结果上增加了一列,但是可以看到这种iterative的执行方式的复杂度和将外部查询和子查询关联产生之前的那部分树进行cross join的复杂度相同。

    同时,这样iterative的执行方式对分布式数据库系统来说是很大的挑战。因为需要修改执行时调度的逻辑。而且我们可以看到,这样的执行方式如果不进行结果的缓存,会进行很多重复结果的计算。

    传统的优化器的优化规则没有特别的针对Correlatedjoin node进行处理,为了支持关联子查询的这种iterative的形式,在优化器初始阶段就会把Correlatedjoin进行等价转换,转换过后的逻辑树用join,aggregation等普通算子来进行关联子查询结果的计算。这个过程被称为解关联(decorrelation/unnesting)。下面一章我们主要介绍常见的解关联的方式。

    二 常见的解关联方式

    为了方便起见,我们在这一章只讨论scalar关联子查询,就是会输出一列值的关联子查询。

    在讨论如何解关联之前,我们总结一下关联子查询的输出有以下特点:

    • correlated join算子的计算结果为在外部查询上增加一列。
    • 增加的那一列的结果为将外部查询关联列的值带入子查询计算得出的。当计算结果超过一行则报错,计算结果为空则补充null。
    • 不同于join算子,correlated join不改变外部查询的其他列(不少行也不膨胀)。

    解开关联的关键在于使得子查询获得对应的外部查询的行的值。

    表现在计划上,就是将correleted join算子向右下推到产生关联的部位的下面。当correlated join算子的左右子树没有关联列的时候,correlated join算子就可以转换成join算子。这样子查询就通过和外部查询join的方式获得了关联列的值,从而可以自上而下计算,回到原本的计算方式。如下图,下图中rest subquery为在关联产生部位之前的子查询部分。当correlated join 推到产生关联的部位之下,就可以转换为普通的join了。

    image.png

    correlated join推过的那些算子都是需要进行改写,以保持等价性(上图的栗子中subquery变为了subquery’)。

    1 下推规则

    论文Orthogonal Optimization of Subqueries and Aggregation[2]给出了将correlatedjoin算子下推到其他算子(filter,project,aggregation,union 等)下面的的等价转换规则。但是文中的correlatedjoin算子是会过滤外部查询的行数的,类似于inner join(论文中称为 )。我们这里讨论更加general的类似于left join的 correlatedjoin (论文中称为 ),并讨论如果要保证外部查询行数不被过滤需要做哪些改写。

    由于篇幅限制,下面我们只介绍下推到filter,project,aggregation算子下面的等价规则。

    为了简单起见,我们在逻辑树中去掉了enforcesinglerow。

    转换1 无关联时转换为join

    回顾前文所说,correlated join算子的左子树为input,右子树为subquery。当correlated join的左右子树没有关联的时候,这个时候对外部查询的每一行,子查询的结果都是相同的。

    我们就可以把correlated join转换成普通的没有join criteria的leftjoin算子。

    image.png

    注:需要在subquery上添加enforcesinglerow来保证join语义和correlatedjoin相同(不会造成input的膨胀)。

    转换2 简单关联条件时转换为join

    当correlated join右子树中最上面的节点为一个关联filter而他的下面无关联时,可以直接将这个filter放到left join的条件中,也可以理解为filter上提。如下图:

    image.png

    转换3 下推穿过filter

    image.png

    论文中correlatedjoin*可以直接推过filter。如果需要下推的为correlatedjoin,则需要对filter进行改写,改写成带有case when的project。当subquery的行不满足filter的条件时应输出null。

    转换4 下推穿过project

    image.png

    correlated join下推过project,需要在project中添加input的输出列。

    转换5 下推穿过aggregation

    image.png

    correlated join下推到带有group by的aggregation时,需要对aggregation进行改写。

    改写为在aggregation的group by的列中增加外部查询的全部列。这里要求外部查询一定有key,如果没有则需要生成临时的key。生成可以的算子在图中为assignuniqueid算子。

    如果aggregation为全局的,那么还需要进行额外的处理。如下图:

    image.png

    correlated join下推到全局aggregation的时候,需要对aggregation增加input的列(以及key)作为group by的列。这个下推规则还需要一个前提,那就是aggregation函数需要满足满足特性 agg(Ø)=agg(null) 。这个的意思就是aggragtion函数需要对空集和对null的计算结果是相同的。

    注:在mysql和AnalyticDB for MySQL(阿里云自研的云原生数据仓库[1],兼容mysql语法,下文简称ADB)的语法里,sum, avg等都不满足这个特性。空集的平均值为0, 而对包含null值的任意集合取平均值结果为null不为0。所以需要mark子查询里的每一行,对空集进行特别的处理,在这里就不展开解释了。

    论文Orthogonal Optimization of Subqueries and Aggregation[2]反复运用上面这些规则进行correlatedjoin的下推,直到correlatedjoin可以转换为普通的join。

    带入之前的tpch q17的栗子中,我们先使用将correlated join推到子查询中的project下面,查询变为:

    image.png

    然后下推穿过这个agg,并改写这个agg,如下图:

    image.png

    这里我们忽略 avg(Ø)!=avg(null) 。如果考虑这个情况,则需要mark子查询全部的行,在correlated join之后根据子查询的结果结合mark的值对空集进行特别处理(将mark了的行的值从null变为0)。感兴趣的读者可以参考下一张中q17的最终计划。

    接着直接调用之前的规则2,上提这个filter。这样这个查询就变为普通的没有关联的查询了。

    image.png

    2 结果复用

    回顾上一节所说,子查询的查询结果是带入每一行关联列的值之后计算得出的,那么显而易见相同值的关联列带入子查询中计算出的结果是完全相同的。在上面的栗子中,对同样的p.partkey,correlatedjoin输出的子查询的结果是相等的。如下图中外部查询partkey为25的话产生的关联子查询时是完全相同的,那么结果也自然相同。

    image.png

    15年Newmann的论文Unnesting Arbitrary Queries[3]介绍了一种方法就是先对外部查询里关联列取distinct,再将correlated join返回的值和原本的外部查询根据关联列进行left join,如下图所示:

    image.png

    这里的not distinct join的条件对应mysql里面的<=>,null<=>null的结果为true,是可以join到一起的。

    带入到之前的例子中如下图所示,对外部查询的关联列partkey先进行distinct,然后带入子查询计算结果,最后再通过join将对应的结果接到原本的外部查询上。

    image.png

    如果进行了上述转换,那么我们可以认为新的input的关联列永远是distinct的。而现在的correlatedjoin*算子可以允许input的列被过滤。这样做的好处除了对于相同的列不进行重复的子查询的计算之外,主要还有下面两个:

    • 新的外部查询是永远有key的,因为distinct过了。
    • correlatedjoin*算子由于过滤外部查询的列,所以它的下推更为简单(不需要assignuniqueid,不需要保留全部行)。

    进行上述的转换后,紧接着再套用之前的等价下推规则,我们又可以将correlatedjoin*下推到一个左右子树没有关联的地方,从而改写为inner join。

    如果按照Unnesting Arbitrary Queries[3]的方法进行解关联,需要将input的一部分结果进行复用,这个复用需要执行引擎的支持。需要注意的是,当系统不支持复用的时候,我们需要执行两次input的子树(如下图),这个时候就需要input这颗子树的结果是deterministic的,否则无法用这个方法进行解关联。

    image.png

    三 关联子查询的优化

    在ADB的优化器中,逻辑计划会根据每一条转换规则进行匹配和转换,也就意味着在关联解开之后不需要关心解关联产生的计划的效率而将它直接交给后续的优化规则。但是现实并不是那么的美好,因为后续规则不够完备,以及解关联之后丢失了外部查询和子查询之间的关系,我们希望在解关联的时候就将计划尽可能优化。

    1 exists/in/filter关联子查询

    在之前的章节中为了简化,我们只讨论了scalar子查询。因为exists/in这些子查询都可以改写成scalar子查询。比如将exists改写为count(*) > 0

    但是可以看到,如果子查询的返回结果被用来过滤外部查询的行,实际上会简化整个解关联的过程。所以我们对exists/in这样的子查询进行特殊处理,在语法解析时就进行区分。在解关联的过程中,如果可以使用semijoin/antijoin算子进行解关联则直接解开关联,否则后续会转化成scalar子查询也就是correlatedjoin的形式。

    2 关联条件的上提

    看到这里会发现,随着correlatedjoin的下推,这个逻辑树会变得更加复杂,所以我们在下推之前会在子查询内部进行关联算子的上提。当这个逻辑就是产生关联的算子越高,correlatedjoin就可以早点推到关联部位的下面。比如下面这个查询:

    SELECT t1.c2
    FROM
      t1
    WHERE t1.c2 < (
        SELECT 0.2 * max(t2.x)
        FROM
          t2
        WHERE t2.c1 = t2.c1
        GROUP BY t2.c1
      );

    这里由于t2.c1 = t2.c1可以推到agg 上面(因为对于子查询这是一个在group by列上的条件),我们就可以进行下面的转换。先把关联的filter上提(有时需要改写),这样就只要把correlatedjoin推过filter,调用转换2就可以了。

    image.png

    更具体的例子就是前文提到的tpch q17。这里的scalar子查询作用在过滤条件中的情况也可以进行进一步改写。

    下图为按照之前说的理论下推correlated join并改写为left join之后的逻辑计划。

    image.png
    image.png
    image.png

    而由于这个scalar子查询是作为filter条件的,这种情况下子查询没有结果返回为null对应的外部查询是一定会被过滤掉的。所以correlatedjoin可以直接转为 correlatedjoin*,再加上将filter进行上提,我们可以得到下面的计划。这样改写的好处是可以在join前先进行agg(early agg)。坏处就是如果不小心处理,很容易造成语义不等价造成count bug。

    3 代价相关的子查询优化

    利用window算子解关联

    回顾到目前为止我们讲的这些,是不是印象最深刻的在于correlatedjoin算子是在外部查询上增加一列。而他的这个行为和window算子类似。window算子的语义就是不改变输入的行数,只是在每一行上增加一个在window的frame里计算出的值。所以我们可以利用window算子进行解关联,如果感兴趣可以参考这两篇论文Enhanced Subquery Optimizations in Oracle[4]和 WinMagic : Subquery Elimination Using Window Aggregation[5]。

    window解关联的改写就是在外部查询包含子查询中全部的表和条件时,可以直接使用window将子查询的结果拼接到外部查询上。他好处是节约了很多tablescan。比如说tpch q2。可以进行下面的改写:

    image.png

    这里之所能改写成window是因为外部查询包含了内部查询全部的表和条件。而且agg函数min也满足特性agg(Ø)=agg(null) (如果不满足,需要对行进行mark以及用case when 改写输出)。

    可以看到改写后tablescan的数量大大减少。更进一步,优化器后面的优化规则会进行根据primarykey的信息以及agg函数的特性进行join 和 window的顺序交换从而进一步减少window算子输入的数据量(filter-join pushdown)。

    image.png

    这些好处很多文章里都说了。我们在这里讨论一下这样改写的不好的地方:

    • 比如在pk未能显示提供/agg的函数对duplicates敏感的情况下,window算子会阻挡filter-join的下推,从而打断了joingraph造成join的中间结果变大。
    • 如果改写为两棵子树的join,filter-join可以下推到其中一颗子树上。而进行window改写后,filter-join无法下推。
    • 在pipeline的执行模型下/&使用cte的情况下,扫表获得的收益有限。
    • 传统优化器对join&agg的优化处理/优化规则比对window好/丰富很多。

    综上所述,什么时候使用window进行改写关联子查询需要进行收益和代价的估计。

    CorrelatedJoin在外部查询中的下推

    在将correlatedJoin往子查询方向下推之前,我们会将correlatedjoin先在外部查询中进行下推(比如推过cross join等)。

    这样做是因为correlatedJoin永远不会造成数据的膨胀,所以理论上应该早点做。但实际上correlatejoin下推后也可能切割joingraph,从而造成和window改写差不多的问题。

    4 等价列的利用

    如果在子查询中存在和外部等价的列,那么可以先用这个列改写子查询中的关联列减少关联的地方从而简化查询。下面举一个简单的例子。

    Select t1.c2
    From
      t1
    Where
      t1.c3 < (
        Select min(t2.c3)
        From t2
        Where t1.c1 = t2.c1
        group by t1.c1
      )
      
    -- 在子查询中使用t2.c1 代替 t1.ct进行简化
    
    Select t1.c2
    From
      t1
    Where
      t1.c3 < (
        Select min(t2.c3)
        From t2
        Where t1.c1 = t2.c1
        group by t2.c1
      )

    5 子查询相关的优化规则

    一个方面correaltedjoin这个算子的特性给了我们一些进行优化的信息。下面举一些例子:

    1. 经过correaltedjoin算子之后的行数与左子树的行数相同。
    2. enforcesinglerow的输出为1行。
    3. 外部查询的关联列决定(function dependency)correaltedjoin的新增的输出列。
    4. assignuniqueid产生的key具备unique的属性等,可用于之后化简aggregation和group by等。
    5. 子查询里的sort可以被裁剪。

    另一个方面,在子查询的改写中,可以通过属性推导进行子查询的化简。比如:

    1. 如果原本外部查询就是unique的则没有别要增加uniqueid列。
    2. enforcesinglerow的子节点的输出如果永远为1行则可以进行裁剪。
    3. 关联列在project上的子查询,如下图,在一些情况下改写为exists子查询。
    select t1.orderkey,
      (
        select
          min(t1.orderkey)
        from
          orders t2
        where
          t2.orderkey > t1.orderkey
      )
    from
      orders t1
    order by
      1

    6 需要注意的地方

    子查询解关联中最需要注意的地方就是两个地方,一个是确保仅对外部查询进行加一列的操作,一个是对null值的处理。

    计数错误

    文献中常提到的是一个经典的解关联容易出错的地方。比如下面的查询,我们有一个前提条件就是t1.c3全都是小于0的。在这个情况下子查询参与的关联条件应该是没有任何过滤度的。而改写成inner join则会过滤掉一些行。语义上是不等价的。

    Select t1.c2
    From
      t1
    Where
      t1.c3 < (
        Select COUNT (*)
        From t2
        Where t1.c1 = t2.c1
      )

    分布式下的leftmarkjoin

    另一个容易出错的地方是论文Unnesting Arbitrary Queries[3]中的LeftMarkJoin,其输出的结果与in的语义相同。简单来说就是下面这个查询的结果。

    select t1.c1 
        in (
        select
          t2.c1
        from
          t2)
    from
      t1

    这个查询对应的逻辑计划如下:

    image.png

    其输出结果为在左子树结果上加一列in的结果,in的结果有三种可能true,false和null。

    在分布式环境下,对这个算子进行repartition和落盘很容易造成和null值相关的计算出错。

    举一个简单的例子,当leftmarkjoin为repartition的执行方式时,会对左表和右表的数据根据c1的hash值进行重分布reshuffle。那么t1.c1中为null的行会被shuffle到同一台executor上。这个时候假如右表没有数据被shuffle到这台机器上,那么这一台executor并不知道对于null的这些行该输出null还是false。因为null in空集的结果为false,而null in 任何非空集合的结果为null。此时这台executor并不知道右表是否为空。

    解开关联后的效率

    在最开始的时候我们提到了iterative的执行方式,这里我们需要说明对有些关联子查询来说即使关联被解开为join/agg等算子,计算查询结果也需要一个cross join的代价。

    比如下面这个两个查询, 第一个是我们常见的关联子查询的样子,可以转换成inner join + early agg的形式。而第二个解开关联后则会变成一个left join on非等值条件(代价同cross join)。

    -- sql 1
    SELECT t1.c1
      FROM t1
     WHERE t1.c2 > ( 
       SELECT min(t2.c2) 
         FROM t2 
        WHERE t2.c1 = t1.c1
       );
    
    -- sql 2
    SELECT t1.c1
      FROM t1
     WHERE t1.c2 > ( 
       SELECT min(t2.c2) 
         FROM t2 
        WHERE t2.c1 > t1.c1
       );

    sq1解开关联后的计划如下:
    image.png

    sql2解开关联后的计划如下:
    image.png

    对于sql1来说,从语义上理解,外部查询的每一行带入子查询里扫过的行都是没有重叠的,所以代价和innerjoin on等值条件是一样的。再加上同样的外部行对应的子查询中min的结果相同可以应用early agg从而可以进一步优化。

    对于sql2来说,从语义上理解,外部查询的每一行都必须要带入子查询中扫过所有的行才能判断在满足t2.c1 > t1.c1这个条件下的子查询的输出应该是什么。为了计算出结果这个代价是无法通过优化节约的。但是对同样的t1.c1输出始终是相同的,Unnesting Arbitrary Queries[3]中的结果复用仍然可以产生优化。

    参考文献
    [1] https://www.aliyun.com/product/ApsaraDB/ads
    [2] Galindo-Legaria,César和Milind Joshi。“子查询和聚合的正交优化。” ACM SIGMOD记录30.2(2001):571-581。
    [3] Neumann,Thomas和Alfons Kemper。“取消嵌套任意查询。” 商业,技术和网络数据库系统(BTW 2015)(2015年)。
    [4]贝拉姆康达(Bellamkonda),斯里坎特(Srikanth)等。“增强了Oracle中的子查询优化。” VLDB基金会论文集2.2(2009):1366-1377
    [5] Zuzarte,Calisto等人。“ Winmagic:使用窗口聚合消除子查询。” 2003 ACM SIGMOD国际数据管理国际会议论文集。2003。
    [6] Neumann,Thomas,Viktor Leis和Alfons Kemper。“联接的完整故事(inHyPer)。” 商业,技术和网络数据库系统(BTW 2017)(2017)。
    [7]加利福尼亚州加林多-莱加里亚(Galindo-Legaria),参数化查询和嵌套等效项。技术报告,Microsoft,2001年。MSR-TR-2000-31,2000年。

    原文链接

    本文为阿里云原创内容,未经允许不得转载。

    展开全文
  • 关联子查询,根本含义就是对于外部查询返回的每一行数据,内部查询都要执行一次,就像python里边说的遍历一样。遍历后对符合条件的记录进行操作。表格sc题目查询每门课程的成绩第2名到第3名的学生信息及该课程成绩;...

    4419e7dd6132b8c28fbc3ccd683f5480.png

    关联子查询,根本含义就是对于外部查询返回的每一行数据,内部查询都要执行一次,就像python里边说的遍历一样。遍历后对符合条件的记录进行操作。

    e1c2cf2a17e53116d28b2014d95a6a86.png

    表格sc

    0f5a07bd31d58f68d6a35cc822094319.png

    题目

    查询每门课程的成绩第2名到第3名的学生信息及该课程成绩;

    SELECT

    where跟的条件的含义:查询同一个课程sc表的成绩小于sc1表的总人数。

    子查询中"WHERE c_id=sc.c_id"其实是分组的意思。

    用逻辑树分析方法分解为,同一课程比如01号课程,找sc表的成绩小于sc1成绩的总人数

    sc表1号课程的所有信息:

    ed3bcd8477032ec7baa1f25ae58b90f4.png

    sc1表1号课程的所有信息:

    29e337150e7e6b6bf13a1afec07f50a9.png

    之前说过关联子查询就是遍历每一条记录,条件时找sc表的成绩小于sc1成绩的总人数

    8fe92c82c598e2b9dbbfa6987ea345be.png

    sc表第一行score是80分,从sc1表中没有找到比80更大的分数,所以结果为0;

    571fb56ba359fc60ebc4e34a177b7692.png

    sc表第二行score是70分(浅黄色行),从sc1表中找到3个比70大的分数(深黄色行),所以结果为3;

    586c09cf46ae97861220fa91d1ad12f0.png

    sc表第三行score是80分(浅橙色行),从sc1表中有1个与80相等的分数(深橙色行),因为排序在第三行,所以结果为1;

    729b34386ecd8410c1752a071df2e434.png

    sc表第四行score是50分(浅绿色行),从sc1表中找到4个比50大的分数(深绿色行),所以结果为4;

    cdc8f6f3083461b1440e20d8cf86dfde.png

    sc表第五行score是76分(浅蓝色行),从sc1表中找到2个比76大的分数(深蓝色行),所以结果为2;

    99fb8b259e3268f4a92504671fdc8e4e.png

    sc表第六行score是31分(浅灰色行),从sc1表中找到5个比31大的分数(深灰色行),所以结果为5;

    排名结果:

    83903321a138627798a263ad53a00b65.png

    排名结果是从从0开始的,score是80分有两个,第一行的80分为0名,第三行的80分是第1名。要的结果是score排前2,3名,也就是排名结果在1和2之间,1号课程的地2、3名是学号3和5。

    最终结果:

    35373f97045b42469772f6e54a1d0e45.png

    练手题

    按各科成绩进行排序,并显示排名;

    SELECT 

    8f29ea0eaca7321da82739f5502679de.png
    展开全文
  • SQL关联子查询的详解

    2020-12-14 13:40:08
    我们先来看一下SQL关联子查询的基本逻辑的定义 对于外部查询返回的每一行数据,内部查询都要执行一次。在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询,然后子查询为每一行数据执行一次并...
  • SQL关联子查询

    2019-09-01 18:19:07
    关联子查询中是信息流是双向的。外部查询的每条记录传递给子查询,然后子查询按照条件执行并返回它的记录。然后,外部查询根据返回的记录做出决策。 例题精讲 成绩表主要信息如下: 要解决的问题是:查询各科...
  • 先说说 : sql的编写顺序 select .. from .. where .. group by ..having .. order by ..sql的执行顺序 ...关联子查询 : 在关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。另外,在关联子查询...
  • 今天我们一起来看一下MySQL中的索引、关联子查询以及语句的优化技巧。一、MySQL的索引数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询数据库表中数据。1、索引的意义索引用于快速找出在某个字段...
  • 视图子查询标量子查询关联子查询如何使用sql解决业务问题各种函数和SQLZOO练习题总结一、视图1、视图是用于存放的是SQL查询语句的,不存放数据,相当于临时存储。2、视图如何使用练习题:按性别汇总创建视图/*创建...
  • SQL子查询关联查询

    万次阅读 2018-08-25 00:13:45
    SQL子查询关联查询:
  • SQL进阶之关联子查询一、关联子查询简介二、关联子查询实操1.CreateTable2.Sample 一、关联子查询简介 通过关联表内部条件从而达到查询效果 二、关联子查询实操 注:版本使用:mysql5.1.73 以下代码均可直接cv在mysql...
  • 一、创建视图1。视图相当于一张临时表,链接...特别是在进行汇总或复杂当查询条件时,导致这个sql语句非常庞大的时候,可以保存为视图提高效率。2)视图会随着原表更新:因为它存放的不是数据而是sql语言。3)不需要...
  • sql:关联子查询

    千次阅读 2018-08-13 22:10:37
    关联子查询会在细分的组内进行比较的时候使用。 假设要按照商品的种类与平均销售单价进行比较,正常的逻辑是:先取出平均值再与销售单价进行对比 select product_id, product_name, sale_price from Product ...
  • ORACLE SQL 表的内关联子查询

    千次阅读 2019-04-08 09:54:37
    表的内关联子查询:子查询可以用主查询里面定义的表进行关联实现查询,这样的查询我们把它叫为表的内关联子查询 下图是一个表的内关联子查询的例子: 如图所示,我们在Where条件中写入子查询,然后再调用主查询定义...
  • 前面我们学过标量子查询,返回值只能是一行结果,在面对需要返回多行结果时会束手无策,解决办法就是使用关联子查询。 01关联子查询 所谓关联子查询,就是在有效范围内进行的子查询。 注意这里的有效范围含义:...
  • 51.Oracle数据库SQL开发之 子查询——编写关联子查询 欢迎转载,转载请标明出处: 关联子查询会引用外部SQL语句中的一列或多列。这种子查询之所以被称为关联子查询,是因为它们通过相同的列与外部的SQL语句关联。 1...
  • 今天我们一起来看一下MySQL中的索引、关联子查询以及语句的优化技巧。一、MySQL的索引数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询数据库表中数据。1、索引的意义索引用于快速找出在某个字段...
  • SQL进阶之关联子查询行间比较关联子查询行间比较越前须知(雾)具体用法 关联子查询行间比较 越前须知(雾) 本系列参考《SQL进阶教程》1,DBMS选用MySQL。 本系列不涉及数据库安装与基础语句,对初学者存在一定...
  • SQL关联子查询和自连接例题
  • 一道关联子查询SQL题目已知表结构如下:A表year sid cid score2001 001 1 602002 001 1 702001 001 2 80 2002 001 2 902001 002 1 702001 002 2 802001 001 3 

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,532
精华内容 612
关键字:

关联子查询sql