×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

猜测

本文发表在 rolia.net 枫下论坛根据你所讲的, 猜测T16与T12和T14的foreign key是CLL与CLLCODE. 如果对的话, CLL是T16的一个INDEX. 还有VID和 IID是T2, T12和T14上的INDEX. NUM可能是T16的INDEX或不是. 即使是, 这个INDEX也可能不起太大作用因为34 is a frequent value in T16.NUM.

第一个QUERY的第一层SUBQUERY "EXISTS(SELECT 1 FROM T16 WHERE (T16.NUM = 34)... " 可能造成在ACCESS T1, T2, T3, T4, T12和T14之前ACCESST16.这样T2, T12和T14的INDEX都不会被用上, 这是造成巨慢的原因.
第二个QUERY只有一层SUBQUERY. T16 和T12, T14在同一层. 因为T12和T14相对T16要小很多, 所以T2, T12和T14先被ACCESS.这样一来T12和T14的INDEX VID和 IID就会被用上.但因为T16.CLL IN (T14.CLLCODE, -1),造成T16的INDEX CLL不会被使用.但因为其它TABLE的INDEX被使用了,所以速度快很多.
在第二个QUERY上,当把T16.CLL IN (T14.CLLCODE, -1)该成T16.CLL =T14.CLLCODE,INDEX T16.CLL就会被用到.达到最佳效果.

如果-1是一定需要的话,你可以先做个QUERY SELECT 1 FROM T16 WHERE T16.NUM = 34 AND T16.CLL = -1.如果结果是空,就用优化后的QUERY. 如果不是空,就把T16从QUERY里去掉.更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 我也来一个SQL实例, 如何改写subquery
    本文发表在 rolia.net 枫下论坛select ...
    from T1,T2,T3,T4
    where ...
    EXISTS(SELECT
    1
    FROM T16
    WHERE (T16.NUM = 34) AND
    (EXISTS(SELECT 1
    FROM T14
    WHERE (T14.VID = T2.VID) AND (T14.IID = T2.IID) AND
    T16.CLL IN (T14.CLLCODE, -1))
    OR
    EXISTS(SELECT 1
    FROM T12
    WHERE (T12.VID = T2.VID) AND (T12.IID = T2.IID) AND
    ((T16.CLL = T12.CLLCODE) OR (T16.CLL = -1)))
    )
    )

    Without subquery, it returns 500 rows. T16 has 10 million rows, T12 has 25k rows, T14 has 200k rows.

    There is a foreign key relationshipt between T12 and T16, T12 is the child.
    Same as T14 and T16, T14 is the child.

    Every row in T3 can find a row either in T12 or T14.

    -1 is a rare value in T16.CLL. 34 is a frequent value in T16.NUM.

    Without query rewrite, it requires 15 minutes to finish, after query rewrite, 16 seconds.更多精彩文章及讨论,请光临枫下论坛 rolia.net
    • 太复杂了,看的眼花花。
    • 看起来有点怪……
      T1,T2,T3,T4居然没有任何连接条件。不清楚这几个表的大小,但这样的写法是罕见的。注意到你WHERE后面有省略号,难道是公司机密?
      T12,T14 作为CHILD表,居然比PARENT小很多。
      T3和T12,T14有关,可是居然没在原SQL中体现出来。QUERY REWRITE的原则是要逻辑等价,要不就是原来写法有错。
      没有索引的说明,从表的列名也猜不出值的分布情况(比方说,NAME或ID就是很好的索引对象,而GENDER就不是)
      如果你有什么心得体会,不妨公布出来和大家分享。
      • 不好意思,写错了。
        In “Every row in T3 can find a row either in T12 or T14.” , T3 should be T2. 连接条件写在WHERE里了.

        原来的SQL还有很多条件,但关键是这段subquery写得太烂.一是nested subquery, 二是cartesian join.

        改后的SQL:

        select ...
        from T1,T2,T3,T4
        where ...
        (EXISTS (SELECT 1 FROM T16, T14
        WHERE (T16.NUM = 34) AND (T14.VID = T2.VID) AND (T14.IID = T2.IID) AND T16.CLL IN (T14.CLLCODE, -1))
        OR
        EXISTS (SELECT 1
        FROM T16, T12 WHERE (T16.NUM = 34) AND (T12.VID = T2.VID) AND (T12.IID = T2.IID) AND T16.CLL IN (T12.CLLCODE, -1)))

        实际上, 在上面的SQL里还是有cartesian join. 幸好developer痛定思痛, 把-1也去掉了 (在没违背原设计的情况下). 最后执行时间是0.5秒!
        • 猜测
          本文发表在 rolia.net 枫下论坛根据你所讲的, 猜测T16与T12和T14的foreign key是CLL与CLLCODE. 如果对的话, CLL是T16的一个INDEX. 还有VID和 IID是T2, T12和T14上的INDEX. NUM可能是T16的INDEX或不是. 即使是, 这个INDEX也可能不起太大作用因为34 is a frequent value in T16.NUM.

          第一个QUERY的第一层SUBQUERY "EXISTS(SELECT 1 FROM T16 WHERE (T16.NUM = 34)... " 可能造成在ACCESS T1, T2, T3, T4, T12和T14之前ACCESST16.这样T2, T12和T14的INDEX都不会被用上, 这是造成巨慢的原因.
          第二个QUERY只有一层SUBQUERY. T16 和T12, T14在同一层. 因为T12和T14相对T16要小很多, 所以T2, T12和T14先被ACCESS.这样一来T12和T14的INDEX VID和 IID就会被用上.但因为T16.CLL IN (T14.CLLCODE, -1),造成T16的INDEX CLL不会被使用.但因为其它TABLE的INDEX被使用了,所以速度快很多.
          在第二个QUERY上,当把T16.CLL IN (T14.CLLCODE, -1)该成T16.CLL =T14.CLLCODE,INDEX T16.CLL就会被用到.达到最佳效果.

          如果-1是一定需要的话,你可以先做个QUERY SELECT 1 FROM T16 WHERE T16.NUM = 34 AND T16.CLL = -1.如果结果是空,就用优化后的QUERY. 如果不是空,就把T16从QUERY里去掉.更多精彩文章及讨论,请光临枫下论坛 rolia.net