SQL优化 第七章 查询转换 4 Filter转换

参考资料：
本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。

 

4 Filter转换

4.1 环境准备

create table tests1 as select * from dba_objects;

create table tests2 as select * from tests1;

CREATE INDEX IX_tests1_ID ON tests1(OBJECT_ID);

CREATE INDEX IX_tests2_ID ON tests2(OBJECT_ID);

4.2 Filter说明及初步改善

下面请大家执行一下这条SQL

EXPLAIN PLAN FOR SELECT COUNT(*) FROM tests1 T1

WHERE EXISTS (SELECT 1

              FROM tests2 T2

              WHERE T1.OBJECT_TYPE=T2.OBJECT_TYPE

                AND T2.OWNER='SCOTT'

              UNION ALL

              SELECT 1 FROM tests2 T3

              WHERE T1.OBJECT_ID=T3.OBJECT_ID

                AND T3.OBJECT_TYPE='DIRECTORY');

首先解释什么是Filter，Filter的计算方法类似嵌套循环，即外表走一条，子查询执行一次，子查询依赖外表，但是于嵌套循环不同的是，嵌套循环是结果集和结果集（当然这个被驱动的最好是表或者物化视图，因为可以走索引）的连接方式，可以理解为连接的两端都是固定的结果集，而Filter是结果集和查询的嵌套循环，被驱动部分无法构成一个独立的结果集。SQL优化器本身一般也是不太喜欢Filter，在产生执行计划时尽量展开Filter，上面的SQL之所以产生Filter，就是因为传入的条件不一样，优化器没法展开。

我们加上hint unnest试一下，强制让Filter展开，

EXPLAIN PLAN FOR SELECT COUNT(*) FROM tests1 T1

WHERE EXISTS (SELECT /*+ UNNEST*/ 1

              FROM tests2 T2

              WHERE T1.OBJECT_TYPE=T2.OBJECT_TYPE

                AND T2.OWNER='SCOTT'

              UNION ALL

              SELECT /*+ UNNEST*/ 1 FROM tests2 T3

              WHERE T1.OBJECT_ID=T3.OBJECT_ID

                AND T3.OBJECT_TYPE='DIRECTORY');

加hint后，并没有什么用，因为优化器已经很努力的干掉这种性能很差的Filter了，之所以还在，因为优化器已经无能为力了。

从上面的执行计划来看，大家说Filter是性能杀手，还是有依据的，上面的执行计划，我们不用看任何成本，大表驱动子查询一条，就知道这个计划废了。另外，很重要一点就是，这种执行计划是优化器很难改变的，执行计划固定，本身也是一个雷，有可能没有立即爆，但随着数据的变化，在某一天爆，因为优化器已经无法改变Filter执行计划了。

当然，还是要说明，hint unnest有时候还是有用的，不能一棍子打死，如果没用，oracle会把这个hint直接删掉的，存在即是合理嘛。

那怎么办，人为改写SQL。

SELECT COUNT(*) FROM

(SELECT *  FROM tests1 T1

WHERE EXISTS (SELECT 1

              FROM tests2 T2

              WHERE T1.OBJECT_TYPE=T2.OBJECT_TYPE

                AND T2.OWNER='SCOTT')

UNION

SELECT * FROM tests1 T1

WHERE EXISTS (SELECT 1 FROM tests2 T3

              WHERE T1.OBJECT_ID=T3.OBJECT_ID

                AND T3.OBJECT_TYPE='DIRECTORY'));

改写之后就好多了，该Hash时Hash，该嵌套时嵌套。

4.3 Filter与其他连接比较

之前，一直在讨论Filter的不好，那Filter有没有好的一面呢？优化器也是很努力的，或者时优化器的开发者很努力，即使百般不好，还是极力的去优化Filter连接。那我么看看Filter好的一面及优化器是怎么优化Filter的。

4.3.1 环境准备

create table tests3 as select rownum as id ,d.* from dba_objects d ;

create table tests4 as select d.* from tests3 d ;

update tests3 t3 set t3.id=mod(t3.id,100) where t3.id <=3000;

commit;

CREATE INDEX IX_tests3_OBJID ON TESTS3(OBJECT_ID);

CREATE INDEX IX_tests4_OBJID ON TESTS4(OBJECT_ID);

4.3.2 Filter与其他连接比较

（1）Filter连接

SELECT COUNT(*)

FROM TESTS3 T3

WHERE EXISTS

   (SELECT /*+ NO_UNNEST*/ T4.ID

    FROM  TESTS4 T4

    WHERE T3.ID=T4.ID)

AND T3.OBJECT_ID<=3000;

（2）嵌套半连接

SELECT /*+ LEADING(T3) USE_NL([email protected])*/ COUNT(*)

FROM TESTS3 T3

WHERE EXISTS

   (SELECT /*+ QB_NAME(Q)*/ T4.ID

    FROM  TESTS4 T4

    WHERE T3.ID=T4.ID)

  AND T3.OBJECT_ID<=3000;

（3）Hash半连接

SELECT COUNT(*)

FROM TESTS3 T3

WHERE EXISTS

   (SELECT T4.ID

    FROM  TESTS4 T4

    WHERE T3.ID=T4.ID)

AND T3.OBJECT_ID<=3000;

（4）Hash连接

SELECT  COUNT(*)

FROM TESTS3 T3 ,TESTS4 T4

  WHERE T3.ID=T4.ID

AND T3.OBJECT_ID<=3000;

（5）嵌套循环

SELECT /*+ LEADING(T3) USE_NL(T4)*/ COUNT(*)

FROM TESTS3 T3 ,TESTS4 T4

  WHERE T3.ID=T4.ID

AND T3.OBJECT_ID<=3000;

比较一：Filter和嵌套循环

Filter连接算法类似嵌套循环，那么不同点在哪里呢？关键字，Hash Table。

Filter只返回主表的数据，也就是和半连接一样，数据不会因为被驱动表的连接列值重复而产生数据翻倍，那么，可以建立一个Hash表，在连接列匹配成功，存入Hash表，比如本例的T3.ID，T4.ID都等于1时，把（1（T3.ID），1（T4.ID）放入Hash表，下一次T3.ID再取到1时，先从Hash表找结果，如果找到，不再执行子查询，那么，此时，如果走Filter连接，在外表连接列重复值很多时，就会大大减少子查询的执行次数，比如本例构造的数据，tests3表OBJECT_ID<=3000时，id列有29或30个重复值，Filter性能高于嵌套循环。

比较二：Filter和嵌套半连接

这两个执行计划性能一样，因为被驱动表走了相同的连接方式，同时我猜测，嵌套半连接内部也维护了Hash表。

比较三：Filter和Hash，Hash Semi

因为本案例被驱动表连接列没有索引，被驱动表只能全表扫，所以，走Hash或Hash Semi性能更好。因为被驱动表tests4的id列无重复值，所以hash和hash semi也是一样的。

我们继续进行下一轮比较，

首先，我们创建索引，

CREATE INDEX IX_tests3_ID ON TESTS3(ID);

CREATE INDEX IX_tests4_ID ON TESTS4(ID);

(1)

SELECT COUNT(*)

FROM TESTS3 T3

WHERE EXISTS

   (SELECT /*+ NO_UNNEST*/ T4.ID

    FROM  TESTS4 T4

    WHERE T3.ID=T4.ID

      AND T4.OBJECT_TYPE='TABLE')

  AND T3.OBJECT_ID<=1000;

(2)

SELECT COUNT(*)

FROM TESTS3 T3

WHERE EXISTS

   (SELECT T4.ID

    FROM  TESTS4 T4

    WHERE T3.ID=T4.ID

      AND T4.OBJECT_TYPE='TABLE')

AND T3.OBJECT_ID<=1000;

(3)

SELECT /*+ LEADING(T3) USE_NL([email protected])*/ COUNT(*)

FROM TESTS3 T3

WHERE EXISTS

   (SELECT /*+ QB_NAME(Q)*/ T4.ID

    FROM  TESTS4 T4

    WHERE T3.ID=T4.ID

      AND T4.OBJECT_TYPE='TABLE')

AND T3.OBJECT_ID<=1000;

本轮比较：

嵌套循环半连接>Filter>Hash，因为本案例中最终返回数据量小，被驱动表还可以走索引，符合嵌套循环优化的前提。

通过本章节，我们得出一个结论：

4.4 Filter转换小结

我们先中途小结以下，目的是为了下一章节的优化案例做好理论基础。

（1）如果符合嵌套循环优化的前提（小表驱大表，大表走索引），并且主表连接列重复值较多，可以将Filter展开成为半连接；

（2）如果符合嵌套循环优化的前提（小表驱大表，大表走索引），并且主表连接列重复不多，可以将Filter展开成为内连接，当然，要考虑被驱动表去重成本；

（3）如果符合Hash连接优化的前提（返回值较多），可以展开为Hash或Hash Semi连接；

（4）Filter优化有时候要改写SQL。