T-SQL查询处理详解.docx

T-SQL查询处理详解 （续）
首先简单提一下T-SQL。T-SQL的正式名称是Transact-SQL，是ANSI和ISO SQL标准的Microsoft SQL Server扩展，而PL/SQL是ORACLE对SQL标准的扩展。
对于T-SQL编程，用得最广泛的，莫过于查询（Querying）。要想写出高质量、高性能的查询语句，必须深入地了解逻辑查询处理。
一、逻辑查询处理的各个阶段
(5)SELECT DISTINCT TOP(<top_specification>) <select_list>                      
(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate> 
(2)WHERE <where_predicate>
(3)GROUP BY <group_by_specification>
(4)HAVING <having_predicate>
(6)ORDER BY <order_by_list>
上边语句是一个普通格式的查询语句，基本包含了所有的查询条件和关键字。你可能会发现前边的序号并不是按顺序来的，被你说对了，这是SQL与其他编程语言不同的最明显特征，就是它的执行顺序并不是按照编写顺序来的。上边的序号，就是查询语句在执行过程中的逻辑处理顺序。下面简单介绍一下各个阶段都干了啥事。
（1）FROM 阶段
    FROM阶段标识出查询的来源表，并处理表运算符。在涉及到联接运算的查询中（各种join），主要有以下几个步骤：
　　a.求笛卡尔积。不论是什么类型的联接运算，首先都是执行交叉连接（cross join），求笛卡儿积，生成虚拟表VT1-J1。
      b.ON筛选器。这个阶段对上个步骤生成的VT1-J1进行筛选，根据ON子句中出现的谓词进行筛选，让谓词取值为true的行通过了考验，插入到VT1-J2。
      c.添加外部行。如果指定了outer join，还需要将VT1-J2中没有找到匹配的行，作为外部行添加到VT1-J2中，生成VT1-J3。
    经过以上步骤，FROM阶段就完成了。概括地讲，FROM阶段就是进行预处理的，根据提供的运算符对语句中提到的各个表进行处理（除了join，还有apply，pivot，unpivot）
（2）WHERE阶段
     WHERE阶段是根据<where_predicate>中条件对VT1中的行进行筛选，让条件成立的行才会插入到VT2中。
（3）GROUP BY阶段
      GROUP阶段按照指定的列名列表，将VT2中的行进行分组，生成VT3。最后每个分组只有一行。
（4）HAVING阶段
      该阶段根据HAVING子句中出现的谓词对VT3的分组进行筛选，并将符合条件的组插入到VT4中。
（5）SELECT阶段
　　这个阶段是投影的过程，处理SELECT子句提到的元素，产生VT5。这个步骤一般按下列顺序进行
        a.计算SELECT列表中的表达式，生成VT5-1。
        b.若有DISTINCT，则删除VT5-1中的重复行，生成VT5-2
        c.若有TOP，则根据ORDER BY子句定义的逻辑顺序，从VT5-2中选择签名指定数量或者百分比的行，生成VT5-3
（6）ORDER BY阶段
     根据ORDER BY子句中指定的列明列表，对VT5-3中的行，进行排序，生成游标VC6.
当然SQL SERVER在实际的查询过程中，有查询优化器来生成实际的工作计划。以何种顺序来访问表，使用什么方法和索引，应用哪种联接方法，都是由查询优化器来决定的。优化器一般会生成多个工作计划，从中选择开销最小的那个去执行。逻辑查询处理都有非常特定的顺序，但是优化器常常会走捷径。
在上一篇T-SQL查询处理详解，文中提到了在如果不考虑查询优化器做的优化的情况下T-SQL查询的逻辑处理过程，讲得很粗糙，这一篇将补充说明这个逻辑处理过程，并对一些容易误解的地方进行说明。不对之处，还请指出。另外再次声明一点，这种逻辑处理顺序，是理论上的处理过程，实际情况还会根据查询优化器选择最优的执行计划。
还是从FROM阶段讲开。
（1）FROM阶段
  这个阶段是查询逻辑处理的第一步。想到这里，想起了LINQ表达式不就是从FROM开始的嘛，看来还是挺有道理的。FROM阶段负责表示表或要查询的表。如果指定了表运算符，还需  要按从左到右的顺序，对运算符进行逐个处理。表运算符有4类，JOIN,APPLY,PIVOT,UNPIVOT。每个表运算符都有自己的处理规则。这里挑最常见的JOIN来说。
对于联接（join），一般有以下几个个步骤:
a.求笛卡尔积，对两张表进行cross join，得出最大的可能结果集。如果左表有n行，右表有m行，则结果集有nxm行。
b.利用ON筛选条件来筛选，剔除不符合条件的行。
c.对于外联接（left，right，full outer join），还需要添加外部行。在上个步骤中，ON条件剔除掉了所有不匹配两张表的行。但是在外联接中，通过指定外连接的类型，需要将其中的一个或者两个表标记为保留表，并返回该表中所有的行。所以这时候还需要将保留表中被ON筛选条件剔除的行重新加入到结果集中（这些重新加进来的表，书中称为外部行），并将外部行中非保留表的列值标记为NULL.再次提醒一下，这一个步骤，只有外联接才执行，对于内联接（inner join）只需要执行a和b两个步骤的。
（2）WHERE阶段
  对于上一步骤返回的虚拟表，经过where条件的判定，只有让where条件为true的行才会被保留下来。请注意，因为还没有对数据进行分组，所以在where子句中不能聚合。也不能引用select列表中创建的别名，因为SELECT阶段还在后头呢。例如where orderid>max(orderid), select year(thedate) as theyear... where theyear>2010是不能使用的。
另外一个很让人迷惑的问题是，对于包含JOIN的查询，到底ON和WHERE子句有什么区别，应该什么时候使用ON子句，什么时候使用where子句。这里作一下说明。只有对于外联接，ON和where子句才会存在这种逻辑区别，因为在外联接中，通过ON子句的筛选之后，还要对保留表进行外部行添加，而where子句则是在外部行添加过之后才进行筛选的。因此，ON子句对这种外联接的情况的筛选，并不是最终的结果，在FROM阶段的第三个步骤，还会把外部行添加回来的。而对于内联接，where子句和on子句作用是完全一样的，在哪里筛选都是同种效果，没有其他步骤。所以在处理这种含有外联接的查询，一定要注意ON筛选和where筛选的区别，避免使用错了，达不到筛选的效果。另外，对于内联接，一个不错的建议是，对于两个表都存在的字段筛选，用ON子句，对于单个表的字段筛选，用where，例如：select * from a inner join b on a.col = b.col where a.col2 >1。
（3）GROUP BY阶段
在这一阶段将上一步返回的虚拟表中的结果集按分组进行重组，由分组集所有列的每个唯一组合，标识出一个组。再用这些组，跟上一步返回的每一个行进行关系。注意，每个行只能关联一个组。最后，生成的结果集中，每个组只能有一行。关于GROUP BY还有很多有意思的地方，比如cube，rollup，grouping等等，有时间再一一介绍。
(4)HAVING阶段
HAVING筛选器用于对上一步返回的结果集进行筛选。HAVING筛选器是唯一能筛选分组数据的筛选器，ON和where都不行。理由很简单，ON和where都是在分组之前进行处理的，自然不能对分组进行筛选。所以HAVING和WHERE的区别，也是很显而易见了。HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 子句一样。
（5）SELECT阶段
这一个步骤，将构造最终返回给调用者的表。这个步骤涉及到3个子阶段。
a.计算表达式。在这个阶段中，select列表可以返回油上一步得到的虚拟表的基础列，也可以是对这些基础列的操作。有一点需要注意的是，在这个select列表中，所有的表达式是同时计算的。举个例子，在SQL中，可以这么交换两个列值:update tab_test set col1 = col2,col2 =col1;在别的语言看来这的确很神奇。
而且在select列表中创建的别名不能在同一select列表中的其他表达式中使用。所以，基于这个特性，我们就会得出一个结论，select列表的顺序是无关紧要的。
b.处理DINSTINCT，如果查询中指定了DINSTINCT，则从上一步返回的虚拟表中删除重复的行。
c.应用TOP选项。对于指定了TOP选项的查询，则会根据查询的order by子句来选择指定数量的行。TOP选项里有很多特殊的地方，此处先不扯远，以后有机会单独讲。
（6）ORDER BY阶段
这一步按照order by列表中的列明对上一步的表进行排序，返回游标。
这里有必要谈一下集合和游标。SQL的理论基础是集合，集合是无序的，它只是成员的一种逻辑集合。对于带有排序作用的ORDER BY子句的查询，可以返回一个对象，其中的行按照特定的顺序组织在一起。ANSI把这种 对象成为游标（cursor）。
因为在这一步中，最后返回的是游标，所以带有order by的查询，是不能用来定义视图，子查询，公用表等。例如：
SELECT * FROM (SELECT col1,col2 FROM tab_test ORDER BY col1)是无效的，并且将报错。
但是如果同时指定了TOP选项，则是一个例外。SELECT * FROM (SELECT top (10) col1,col2 FROM tab_test ORDER BY col1)，对于这个查询，因为同时指定了top和order by，则子查询中的结果一定是固定而且有序的的，但是外部的查询，则不能保证是有序排列的。
对于T-SQL查询逻辑处理，就整理到这里，有什么问题，还请大家指出，一起探讨。

一.控制多联接的物理计算顺序
在编写查询语句时，经常会多次联接查询多个表。在没有外联接的多联接查询中，连接查询的先后顺序对最终的结果是无影响的，查询优化器知道这一点，所以在最终的执行计划中，优化器访问表的顺序可能跟查询语句中指定的联接顺序不一样。
例如，对于以下这样的一个多联接的查询：
use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c
inner join dbo.Orders o on c.CustomerID=o.CustomerID
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID
检查执行计划，你会发现，并没有按照查询语句中指定的逻辑顺序来物理地访问各张表。

如果您想最终的执行计划按照您在查询中指定的顺序来处理联接的话，有两种方法：
1.用FORCE ORDER提示选项。
use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c
inner join dbo.Orders o on c.CustomerID=o.CustomerID
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID option(force order)
这时再去看执行计划，就会发现跟访问顺序跟查询中的是一致的了。

2.可以执行SET FORCEPLAN。不建议用这种方式，它将影响会话中的所有查询。语法为：SET FORCEPLAN ｛ON|OFF｝
当然这种强制执行顺序的方法，是处理性能问题的最后一招。如果您对最优的联接顺序有很肯定的把握的话，可以使用提示选项的方式来处理，一般的情况下，还是建议让优化器去干这些事。
二.控制多联接的逻辑计算顺序
先看看下边这样的一个查询
use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c
LEFT join dbo.Orders o on c.CustomerID=o.CustomerID
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID
这个查询，目的是要返回所有客户的订单情况，注意这里包括没有订单的客户也要查询出来，理论上结果集行数2157行，其中有两个客户没有订单，其orderid等字段全部是null。可以执行了以上查询之后，却发现结果集中有2155行。为了方便查看，我们先加上where条件(where o.OrderID is null)。再执行这条查询，发现结果集为空。那么这条查询就有问题了，它没达到要求呀！为什么会出现这样的情况呢？下边来分析一下。
首先来看看最终的执行计划：

看见了没有！这里边的4个联接查询居然没有left join，全部是inner join！真凶就在这里！优化器把left join当成inner join处理了，当然就少了那两个没有订单的客户了。原来优化器是这么想的：在第一个查询中，我给你添加了外部行，这些外部行的orderid是NULL，而在后面的inner join中，这些为NULL的orderid是关联不到任何结果的。所以查询优化器就擅作主张了，既然第二个联接会消除第一个联接的外部行，那么我就干脆把第一个联接也当作inner join来处理了。所以，一个外连接后边跟着内联接并且内联接是针对外联接中的非保留表字段的话，这个外联接就无效了，直接当作内联接处理了。
问题找到了，就应该想办法去解决。
方法1:将后面的inner join 全部改成 left join。因为left join 可以将外部行一直保留在查询过程中的虚拟表中，所以最后的结果查询出来了正确的行数。但是如果存在没有相关订单明细的订单，或者没有相关产品的订单明细，或者没有相关供应商的产品，这个查询仍然会得到错误的结果，它会由于不匹配多个筛选条件而添加多个外部行。并且查询优化器不能对外联接进行联接排序优化，所以这种方法不可取。
方法2：修改连接顺序。这个查询的关键是，dbo.Customers表中有外部行，那就最后再关联它。于是有下边的查询：
use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Orders o
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID
right join dbo.Customers c on c.CustomerID=o.CustomerID
看看执行计划，这个外联接就保留下来了，得到了正确的查询结果。

这个例子本身简单，而且业务也不复杂，所以容易修改逻辑，但是碰到比较复杂的查询，就不一定那么好修改了，更好的方法，请接着往下看。
方法3.ANSI SQL和T-SQL都是允许控制联接处理的逻辑顺序的。这里我们可以用加括号的方法，把第2，3，4个查询当作一个整体，让它们先联接查询，得出虚拟表，再和第一张表进行left join。代码如下：
use Northwind;
GO
select c.contactname,o.orderid,p.ProductName,s.ContactName from dbo.Customers c
left join
(
dbo.Orders o
inner join dbo.[Order Details] od on od.OrderID = o.OrderID
inner join dbo.Products p on p.ProductID = od.ProductID
inner join dbo.Suppliers s on s.SupplierID = p.SupplierID
)
on c.CustomerID=o.CustomerID
这样看起来自然多了，符合一般的业务习惯了。另外说明一下，这里的括号不是必须的，但是强烈建议添加括号。理由很简单，提高可读性。
对于联接查询，还有很多地方需要去研究，先说到这里，以后深入了再细谈。小弟才疏学浅，如果有地方有纰漏，还请各位提出，指出问题，同时希望大家一起探讨开发中遇到的问题。