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

谁践踏了优雅 2021-09-07 06:15 192阅读 0赞

问题 :

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` :外表县进行逐条扫描，而内表，根据`ＯＮ`的连接条件，快速`SEEK`内表看是否有符合的数据（`SEEK`不是`SCAN`）。这样产生两张表`JOIN`后集合。

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

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

虽然不是太明白具体如何判断，但是`ＳＱＬ　ＳＥＲＶＥＲ`会自动判断使用哪种方式，所以不需要太关心，除非是做`ＤＢＡ`的。重点了解下`Nested Loops join`。

**４）`FROM , JOIN , ON , AND , WHERE` 总结**

铺垫了这么多，终于回到关键问题：平时看到很多`ＳＱＬ`写法　有的用`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查询中，一般都不止两个表连接，一般是多表连接查询！ 几个常见错误：

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

２。分不清表连接 (`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 :

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

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

４。本人一次看别人`ＳＱＬ`，就是不明白作表连接查询，为什么`WHERE`后面要进行大量的`WHERE`条件筛选，而且都是无关业务逻辑的。在我的传统观念看来，执行`WHERE`语句是需要对全表进行扫描的，这样因该会增加查询时间。**现在结合前面所讲的，因为不论是（`ＦＲＯＭ－－ＡＮＤ`　还是　`ＪＯＩＮ－－ＯＮ`）方式，再与第三张表`ＪＯＩＮ`之前都应该尽量先筛选一部分结果（可能是大部分结果）。这样速度会大大提升！**