SQL 深入"了解" sqlserver 表连接 join 及性能调优化

时间:2022-09-18 08:37:31

  问题 :

     1.什么是内连接(inner)和外联结(outer)

     2. SQL server 表连接 (FROM--AND 法, JOIN -- ON 法)的区别.

     3.表连接及多表连接的SQL语句执行顺序,和性能调优.


1.第一个问题,首先要明白如何使用JOIN 和 ON 关键字作表连接。

    申明:下文中所用的等价,可能指的是逻辑上的等价(即产生相同的结果集),也可能是执行顺序上的等价,甚至是所产生的执行计划或者执行效率等价。因为很多时候用户只要写普通的sql ,而sql server 会跟据自己的优化 配置和执行计划,产生执行步骤,这些步骤也许和你写的sql很符合,也许更优,当然也可能不符合你的需求。这需要很多的积累,我也只是浅尝辄止,所以没有能力做过多论述。具体问题具体分析,这里只能提供大体思路。

  1)join 的5种方式 :

    inner join ; left join; right join; full join; cross join;

    其中inner  join可以省去inner 关键字。 left/right join 与left/right out join 等价。

    full join 与 同时 left join 和 right join 等价。

    cross join 为将两张表笛卡尔集   

 


 

  2) JOIN -- ON 语句的执行顺序:

    例句:

SELECT * FROM A LEFT JOIN B ON A.ID = B.ID AND A<>0 WHERE A.name = 'x'  

    注意在作on  连接后 的and 子句 和where 子句 。  他们有什么不同!。。。。。。

    逻辑上解释:(不考虑执行计划中执行步骤和作嵌套连接等具体方式,这里只讨论如何思考逻辑上的步骤) 

        执行顺序是: FROM --> JOIN --> ON -->AND--> LEFT--> WHERE -->SELECT

        A步骤. 先将两张表根据ON 条件 作连接(逻辑上,相等于将两张表笛卡尔集后根据ID相等条件筛选数据,实际情况后面分析) 

        B步骤. 根据ON 后面,WHERE 之前 的 AND 条件筛选数据

        C步骤. 跟据LEFT 无论如何,要保证A表的数据完整性。所以在上一步骤产生的结果集中补齐A表因无法比与B表匹配而被AND 条件筛选的掉的数据;

        D步骤. 再根据WHERE筛选结果集。 

    示例:(为了能更好的这一过程,通过实例先思考)

        

CREATE TABLE EMPLOY  (NAME    VARCHAR(10),     DEPTNO    INTEGER );     

INSERT INTO EMPLOY (NAME, DEPTNO) VALUES  
 ('张三',10),  
 ('李四',20),  
 ('王五',10),  
 ('赵红',20);    

 
CREATE TABLE DEPARTMENT  (DEPTNO    INTEGER,      DEPTNAME    VARCHAR(10) ); 

INSERT INTO DEPARTMENT (DEPTNO, DEPTNAME) VALUES 
(10, '市场部'),
(20, '技术部');


--查询一下所有的员工的姓名和部门名为市场部的部门 

    

也许你的SQL 会写成这样:

SELECT E.NAME,D.DEPTNAME 
FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO   
WHERE D.DEPTNAME='市场部' 

仔细读题目,是要查询“所有”员工的姓名,所以肯定要保证员工表的数据完整性。如果使用where,当然不能保证员工表的完整拉。

还记得 在 ON 关键字后 ,WHERE 关键字前的条件筛选方式么???

SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO AND D.DEPTNAME='市场部'  

这样就对了!!

产生的结果很奇怪

张三 市场部
李四 NULL
王五 市场部
赵红 NULL

为什么结果是这样呢? 深入理解下前面所说的SQL 语句执行顺序 

    举个例子:分别执行看看结果,结合上个例子想想(以下ABC步骤意思是前面说的ABCD四个步骤)

--执行A步骤等价的逻辑SQL
SELECT * FROM EMPLOY  E   JOIN DEPARTMENT D   ON E.DEPTNO=D.DEPTNO
--执行B步骤等价的逻辑SQL
SELECT * FROM EMPLOY  E   JOIN DEPARTMENT D   ON E.DEPTNO=D.DEPTNO and  D.DEPTNO=40
--执行C步骤等价的逻辑SQL
SELECT * FROM EMPLOY  E  left JOIN DEPARTMENT D   ON E.DEPTNO=D.DEPTNO and  D.DEPTNO=40

  3)表连接在执行计划,或者是真正的执行方式:

  首先要理解下执行计划,看看SQL语句如何在 SQL SERVER   内部中真正实现这些复杂操作;其中SQL SERVER JOIN 的三种方式(Nested Loops join,Merge Join,Hash Join)要有所了解。如果不懂,可以去http://www.cnblogs.com/fish-li/archive/2011/06/06/2073626.html 看看学习。

  我的私人理解:

    A. Nested Loops join :外表县进行逐条扫描,而内表,根据ON的连接条件,快速SEEK内表看是否有符合的数据(SEEK不是SCAN)。这样产生两张表JOIN后集合。

     B. Merge Join : 用于两张表差不多大,而且在连接字段上有索引。

    C.  Hash Join : 两张表都是数据量很大。

  虽然不是太明白具体如何判断,但是SQL SERVER 会自动判断使用哪种方式,所以不需要太关心,除非是做DBA的。重点了解下Nested Loops join。

 


  4)FROM , JOIN , ON , AND , WHERE  总结  

  铺垫了这么多,终于回到关键问题: 平时看到很多SQL 写法 有的用WHERE  and 进行表连接,有的用JOIN ON 作表连接。这里面不能随便,写不好即影响结果,又阻碍执行效率。可以查看更多详细资料:http://blog.csdn.net/shangboerds/article/details/5213264

    

SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO   
WHERE D.DEPTNAME='市场部' 

SELECT E.NAME,D.DEPTNAME FROM EMPLOY  E LEFT JOIN DEPARTMENT D ON E.DEPTNO=D.DEPTNO 
AND D.DEPTNAME='市场部'  
-- 不论逻辑上还是结果上都不等价

 

  现实SQL查询中,一般都不止两个表连接,一般是多表连接查询! 几个常见错误:

  1。胡乱使用LEFT join :由于分析过执行步骤, LEFT 关键字是要在“两张”表连接完成后(思考下多表连接),再对表相当于进行扫描部全的过程,所以会耗费很多时间。

  2。分不清表连接 (FROM--AND 法, JOIN -- ON 法)的区别;如下两个SQL:

SELECT *
FROM A 
INNER JOIN B ON A.ID = B.ID AND B<>0
INNER JOIN C ON A.ID = B.ID AND C<>0


SELECT *
FROM A 
INNER JOIN B ON A.ID = B.ID 
INNER JOIN C ON A.ID = B.ID 
WHERE 
    B<>0
AND C<>0
--此写法效率比上面两种都差,尤其表越多,效果越明显

 

   思考表连接的的SQL执行顺序。。。前者两张表JOIN 后 马上筛选部分结果在与另一张表JOIN 。后者先将三张表JOIN后再筛选。所以很明显前者效率比后者高.

  3。再添加一条SQL :

  

SELECT * 
FROM A , B , C
WHERE A.ID = B.ID 
AND A.ID = C.ID
AND B<>0 
AND C<>0

  此结果和第一条SQL一样效率不错! 从逻辑上看,似乎SQL 会先将表JOIN 后再筛选,但实战结果。是先筛选再JOIN !因为SQL SERVER 会内部分析,产生一个最优的执行计划,所以不用你操心,自动帮你处理了!而使用JOIN ON 的话,就好像是使用强制命令,告诉数据库,就是要按你的方式处理结果,数据库只好服从!! 所以思考SQL写法不能只说要效率,同时还要注重结果对了,这才是关键!

  4。本人一次看别人SQL,就是不明白作表连接查询,为什么WHERE后面要进行大量的WHERE条件筛选,而且都是无关业务逻辑的。在我的传统观念看来,执行WHERE 语句是需要对全表进行扫描的,这样因该会增加查询时间。现在结合前面所讲的,因为不论是(FROM--AND 还是 JOIN--ON)方式,再与第三张表JOIN之前都应该尽量先筛选一部分结果(可能是大部分结果)。这样速度会大大提升!

 

  

 

  表连接人人都会,可真要说的清清楚楚,也许还需要花点功夫和时间去测试和总结吧。说起来因该有很大的文章,本人知识积累还不够,还希望有人能提点提点,斧正补充一下!!