axure9 条件中条件_SQL中join条件和where条件的执行次序

测试目的

1. 测试关联查询的执行过程，过滤条件写在where后和on后的区别

2. 认识“谓词下推”

测试设计

    客户表CUST

    

    订单表 ORDERS

    

现在想查询年龄小于18岁的客户的订单情况。有以下两个sql语句

SELECT * FROM CUST A LEFT JOIN ORDERS B ON A.CUSTID=B.CUSTID WHERE AGE <18SELECT * FROM CUST A LEFT JOIN ORDERS B ON A.CUSTID=B.CUSTID AND AGE <18

测试过程

首先，关联的中间结果如下

SELECT * FROM CUST A LEFT JOIN ORDERS B ON A.CUSTID=B.CUSTID

第一个sql执行结果如下，是先连接两个表，再用age<18对中间结果进行过滤。

第二个sql执行结果，不满足age<18的行不会连接，但左表所有记录都会显示。

谓词在on后面的关联成本更高，除非业务需要，否则把谓词放在where后面更好。

测试结果

        以上测试得出结论，多表关联是先连接再执行where后的过滤条件(谓词)。那么问题来了，把全表的数据从存储层读取至内存，在计算引擎(内存)中关联后在过滤，在表数据量很大的时候性能消耗必定很大。能不能在关联之前，从底层取数据的时候就用where条件把不需要的数据过滤掉呢？这个思想就是谓词下推。

谓词下推

        谓词下推的目的就是通过将一些过滤条件尽可能的在最底层执行可以减少上层交互的数据量，从而提升性能。往高大上的方面说，就是将过滤表达式下推到存储层直接过滤数据，减少传输到计算层的数据量。

        以下面的sql为例

SELECT * FROM CUST A LEFT JOIN ORDERS B ON A.CUSTID=B.CUSTID WHERE AGE <18

        按照sql标准的执行过程，需要先对CUST表和ORDERS表做全表扫描，数据读取至内存中再进行关联和过滤。DB2强大的优化器，会在真正执行sql前对其进行重写，把过滤条件age<10改成子查询移到关联前执行(谓词下推至DMS数据管理层或IDM索引管理层)，可以大大降低Join操作的输入数据，提升SQL的执行效率。改写后的sql如下

SELECT Q4.$C5 AS "CUSTID", Q4.NAME AS "NAME", Q4.AGE AS "AGE", Q4.ORDERID AS "ORDERID", Q4.$C2 AS "CUSTID", Q4.AMOUNT AS "AMOUNT"FROM  (SELECT    Q3.AMOUNT,   Q3.ORDERID,  Q3.CUSTID,    Q2.NAME,  Q2.AGE,  Q2.CUSTID   FROM    (SELECT  Q1.CUSTID,  Q1.NAME,  Q1.AGE     FROM   DB2INST1.CUST AS Q1     WHERE  (Q1.AGE < 18) ) AS Q2     LEFT OUTER JOIN DB2INST1.ORDERS AS Q3    ON (Q2.CUSTID = Q3.CUSTID)  )AS Q4

       通过理解sql关联查询的执行次序和谓词下推思想，在以后的解决sql性能问题时会有更加深入的思路。