SQL表关联中的逻辑与效率

入职后用了SQL也有一年了，对SQL和各个数据库的了解也渐渐多了起来。感觉SQL的关联蛮有意思的，在这里写一下自己对这玩意的认识的总结吧。

本篇主要是通过目前接触到的两个数据库–vertica和impala(Oracle和MySql也有，但是个人用的不多)的执行计划来写的，优化器做到差不多地步的数据库应该大同小异

P.S.本篇所用的表数据量都在数千万到数亿不等。如果用一个只有几十几百行的小表来跑的话，很可能数据库的优化器就不再浪费时间做优化了。
又P.S. 两个数据库执行计划相同的话，就只截了一张图

一、关联中的ON与WHERE
在关联中这两个子句都可以起到对数据过滤的作用，但他们过滤的时机不同。ON主要是关联之前过滤，WHERE则是关联之后过滤。虽然现在大部分数据库优化器都可以自动把WHERE的过滤条件提前了，但是WHERE在某些时候还是会和ON有区别的，同样的过滤条件放在WHERE或ON后面可能会有完全不同的结果。下面就详细的分析一下。

1、内连接
由于表名不重要，所以此处隐去了表名

语句1SELECT    t1.*,    t2.*FROM    xxxx t1INNER JOIN    yyyy t2ON    t1.city_id = '10303'    AND t2.city_id = '10305'    AND t1.oid = t2.oid

这条语句在vertica和impala中生成的执行计划是差不多的，分别是
vertica和IMPALA中的执行计划（由于他们的执行计划一样，lz比较懒就放了一张图上来)：

从执行计划里可以看到，他们都是把on中的过滤条件，提前到了扫描数据表的时候执行，这样可以减少需要关联的数据条目数，提高执行效率

将ON中的条件改到WHERE后面，他们的情况都差不多。总之，对于Inner join来说，筛选条件放在on或者where后面，效果是差不多的。

2、外连接
2-1 过滤条件放在on后面

语句2-1SELECT    t1.*,    t2.*FROM    xxxx t1LEFT JOIN    yyyy t2ON    t1.city_id = '10303'    AND t2.city_id = '10305'    AND t1.oid = t2.oid

vertica和Impala中的执行计划：

从执行计划中可以看到，对于外连接中的on后面的条件，数据库只对从表进行了优化，而主表则是先扫描全表，到关联的时候才把on中的条件作为关联条件执行，这样的话效率会差很多，之后会给出执行时间对比。

语句2-1的执行时间大约为6.885秒(vertica中的，不得不说vertica效率还是很高的。impala要几十秒才能完成，这里偷懒没有做记录)

2-2 过滤条件放在where后面

语句2-2SELECT    t1.*,    t2.*FROM    xxxx t1LEFT JOIN    yyyy t2ON    t1.oid = t2.oidWHERE    t1.city_id = '10303'    AND t2.city_id = '10305'

vertica中的执行计划：

可以看到在vertica中，这条语句被修改为：先分别对主、从表筛选，然后按照inner join来处理
impala中的执行计划：

在impala中，也是先对主、从表筛选，但关联依然按照左连接处理，关联之后按照other predicates来做筛选。优化器方面果然还是没有vertica作的更精细。
语句2-2的执行时间大约为3.814秒，比2-1缩短了2倍以上

表面上看起来，2-1和2-2的SQL语句的逻辑差不多，只是效率不同，但其实他们达到的效果完全不同。从vertica的执行计划就可以看出，2-1的关联依然是按照左连接来处理的，即t1表中关联失败的数据依然会出现在结果中，但2-2则被转成了内联，即t1表中关联失败的数据不会出现在结果中。想象到极端情况：t1按条件筛选出了100条数据，而这100条数据和t2表完全匹配不上，那么结果是：

• 2-1的SQL语句将输出100行，这100行数据仅t1表的相关字段有值，t2表的相关字段为null
• 2-2的SQL语句将输出0行

这就是这两条SQL语句逻辑上的区别。但2-1的语句的效率实在是太低了(虽然也没用多久，但主要还是数据量筛的比较少，而且vertica性能还算不错。如果是几亿的数据相互关联，那么效果就会很明显)。如何用较高的效率来实现2-1的逻辑呢？
有两个方法，一个是用子查询，先对主表用子查询进行筛选，结果再与从表进行关联，即：

语句2-3    SELECT        t1.*,        t2.*    FROM        (SELECT            *         FROM            xxxx        WHERE            city_id='10303') t1    LEFT JOIN        yyyy t2    ON            t2.city_id = '10305'        AND t1.oid = t2.oid

执行时间约为3.916秒，和2-2的执行速度差不多。
执行计划很明显，这里就不贴了

另外一种方法，则是将对主表的过滤条件放到where后面，对从表的过滤条件放到on后面：

语句2-4    SELECT        t1.*,        t2.*    FROM        xxxx t1    LEFT JOIN        yyyy t2    ON        t2.city_id = '10305'        AND t1.oid = t2.oid    WHERE        t1.city_id = '10303'

执行时间大约为4.025秒
vertica和impala中的执行计划：

可以看到，在执行计划中，数据库先分别对主、从表的数据分别作了过滤，然后再执行左连接，用较高的效率实现了语句2-1的效果

总结：
其实写完后想想，上面的逻辑完全是按照on、where的功能来的，即：

• on在关联的时候作为筛选条件，在关联前生效
• where作为最终结果的筛选条件，在关联后生效

区别就是数据库的优化器对SQL语句进行了优化，把where中的语句提到了最前面执行，这样可以起到提高语句执行效率的作用。不过不知道为什么，on中对主表的过滤条件，两个数据库的优化器都没有做优化，不知道oracle中是怎么处理的，这个放到后面再考虑

另外，从上面的分析也可以看出，把过滤条件放在on还是where后面，不光会影响语句的执行效率，更重要的是，会产生不同的逻辑，在外连接中得到完全不同的结果。因此很有必要对关联作比较深刻的了解