T-SQL查询处理详解

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 1 FROM JOIN ON 2 WHERE 3 GROUP BY 4 HAVING 6 ORDER BY 上边语句是一个普通格式的查询语句 基本包含了所有的查询条件和关键字 你可能会发现前 边的序号并不是按顺序来的 被你说对了 这是 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 阶段是根据中条件对 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 Cus tomers 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 Cus tomers 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 Cus tomers 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 Ord ers 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