评价过滤运算符选择对T-SQL执行性能的影响机制

评价过滤运算符选择对T-SQL执行性能的影响机制

　　

介绍

　　过滤操作对绝大多数复杂查询的编写来说非常重要。虽然在某些情况下你不需要用到过滤器，例如编写一个用来检索我国所有省份列表的简单查询就不需要;不过在大多数情况下，你需要过滤数据以便通过更精确的设定缩小查询范围。例如，当你需要一份以字母G开头的所有省份名单时，就得用到过滤机制。

　　开发人员和数据库管理员有可以用各种运算符来执行这样的过滤操作。在决定使用何种运算符时需要优先考虑的问题是查询性能。可能有那么一些专家声称某些特定运算符要比别的好用，并提供一些经验法则之类的东西来指导您的选择。虽然这些经验法则大体而言还是不错的，不过对于任何的个别案例来说，都存在很多影响查询性能的因素，事实证明这些因素比一般法则更重要。对个别查询的用法和执行计划不加研究，而严格地按照经验规则来选择运算符，可能会导致查询达不到最佳性能。

　　选择最佳运算符

　　当我们将要为查询决定最佳运算符的选择时，首先要考虑将来该查询的使用情况。如果这个查询的使用频率很低，而且属于某个在活动不频繁时间才运行的进程的一部分(如深夜运行的SQL Server集成服务或数据转换服务包)，那么长时间查询可能会比较合适。另一方面，如果可以运行该查询的时间很短，例如主营网上购物的商店，就需要对每项交易都执行一个查询，那么您可能会希望把时间花在获得最佳查询性能上，也就是说不仅要保证客户的体验，也要维护数据库服务器的稳定。

　　我们还需要考虑如何为过滤取值，这些值又是从何处获得的。过滤值是单个值还是多个值?某些运算符能容纳的过滤值比其他运算符多。从另外一个表或子查询获得多个过滤值，而不是传递特定值，也可能会影响到你对运算符的选择。

　　此外，当我们将要选择运算符时，还有一个重要因素需要考虑，那就是索引。如果某个适当的索引可用时，SQL Query Optimizer通常会用该索引去执行索引查找操作。如果没有可用索引，或索引不是最适合的，那数据库必须先执行表扫描或索引扫描，这样尤其会导致性能下降。不过，如果被查询的表包含的行比较少，那表扫描或索引扫描执行起来确实会比索引查找要快。当评价执行计划时，我们必须要考虑这些条件。

　　评价执行计划

　　类似于“EXISTS逻辑运算符比IN逻辑运算符更好用”这类大而化之的评价常常会左右我们的思维。在决定选择哪个运算符的时候，我们不能单纯地把某人的教条当圣经来用。更好的做法是评价执行计划，以便为数据库测试我们在其中编写的查询。执行计划为我们揭示是否了执行表扫描或索引扫描，并阐明该查询各步骤的相对性能情况。

　　我们可以使用Enterprise Manager 中的Query Analyzer工具(SQL Server 2000)或者通过SQL Server Management Studio(SQL Server 2005)来查看执行计划。

　　测试样本查询

　　为了更好地阐明测试过程，本文将以AdventureWorks样本数据库为例，通过不同的变化设置来测试一个用来检索三个特定产品销售细节的查询。我们需要准备以下的测试环境：SQL Server 2005标准版、SQL Server Management Studio、SQL Server Profiler和AdventureWorks样本数据库。假设查询的结果会在一个网页应用程序的GridView对象中显示。当页面应用程序处于回传状态时，该GridView对象就会开始组建;因此，必须快速执行查询，而且执行的频率也要很高(假设这是一个访问率很高的网站)。

　　本例用三个常数硬编码的ProductID值来过滤查询。

　　ProductID = '762'

　　ProductID = '754'

　　ProductID = '770'

　　虽然普遍的做法是最好避免使用编死的硬编码值，本例中使用这类值是为了隔离某些运算符对执行计划的影响，也就是任何来自和运算符本身无关的子查询的影响。

　　本例用了两个表：SalesOrderDetail表和Product表，每个表都有若干指定的索引：

　　Product表有四个索引：

　　PK_Product_ProductID(通过主键建立的聚集索引)

　　AK_Product_rowguid(唯一非聚集索引)

　　AK_Product_ProductNumber(唯一非聚集索引)

　　AK_Product_Name(唯一非聚集索引)

　　SalesOrderDetail表有三个索引：

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID(通过主键建立的聚集索引)

　　IX_SalesOrderDetail_ProductID(非聚集索引)

　　AK_SalesOrderDetail_rowguid(唯一非聚集索引)

　　运算符类型

　　用于过滤操作的运算符分为两类：比较运算符和逻辑运算符。

　　比较运算符通过对两个值进行比较，确定这两个值是否满足条件。T-SQL中的比较运算符包括：

　　等于(=)

　　大于(>)

　　小于(<)

　　大于等于(>=)

　　小于等于(<=)

　　不等于(<>)

　　逻辑运算符对条件进行判断，并返回一个布尔逻辑值，显示是否满足该条件。T-SQL中的逻辑运算符包括：

　　ALL

　　ANY

　　BETWEEN

　　EXISTS

　　IN

　　LIKE

　　NOT

　　SOME

　　我们可以在复杂的过滤操作中同时运用比较运算符和逻辑运算符。当我们要使用多种运算符时，运算的顺序(运算符优先级)可能会影响查询的性能。T-SQL根据运算符的位置和类型来处理运算优先级：当使用的运算符都是相同类型(同是比较运算符)时，按照从左到右的顺序运算;当运算符属于不同类型时，比较运算符的优先级高于逻辑运算符。

　　在接下来的例子当中，我们将评价四种最常用的运算符：等于比较运算符(=)和IN、 ANY、EXISTS三个逻辑运算符。

　　等于比较运算符和IN逻辑运算符

　　正如上面提到的，我们需要用一个查询来检索三个特定产品销售情况的详细资料。我们可以使用带等于比较运算符过滤的WHERE从句或带IN逻辑运算符过滤的WHERE从句来完成这个任务。

　　比较运算符只能用来比较两个值。为了检测三个特定ProductID之间的等价关系，我们还需要用到OR运算符。下面就是使用了等于比较运算符的查询语句：

以下是引用片段：
　　SELECT
　　SalesOrderID,
　　CarrierTrackingNumber,
　　ProductID,
　　UnitPrice
　　FROM
　　AdventureWorks.Sales.SalesOrderDetail
　　WHERE
　　(ProductID='762')or
　　(ProductID='754')or
　　(ProductID='770')

　　相比之下，IN逻辑运算符使我们只需简单地使用一列自变量，也不需要使用OR或者AND：

以下是引用片段：
　　SELECT
　　SalesOrderID,
　　CarrierTrackingNumber,
　　ProductID,
　　UnitPrice
　　FROM
　　AdventureWorks.Sales.SalesOrderDetail
　　WHERE
　　ProductIDIN('762','754','770')

　　虽然本例中使用了确切值作为IN运算符的自变量，我们还可以通过子查询来获取自变量值。

　　尽管两个查询语法区别非常明显，但是当在AdventureWorks数据库和这些特定表下运行时，这两个查询都生成相同的执行计划(见图一)。

　　图一. 等于和IN运算符的执行计划：在本例的数据库环境下，这两个查询都生成了相同的执行计划。

　　ANY和EXISTS逻辑运算符

　　当参数中有任何一项满足了条件，那ANY逻辑运算符会返回一个true布尔值;而对于EXISTS逻辑运算符来说，如果其参数中的子查询返回任意行的话，那EXISTS运算符就会返回true值。测试这些运算符的性能要谨慎对待，因为其中使用的子查询可能会包含其他运算符，将直接影响到测试结果。为了尽量减少这方面的影响，下面的ANY和EXISTS查询实例都包含了一个使用等于比较运算符的子查询。

　　下面是测试ANY逻辑运算符的查询语句：

以下是引用片段：
　　SELECT
　　SalesOrderID,
　　CarrierTrackingNumber,
　　ProductID,
　　UnitPrice
　　FROM
　　AdventureWorks.Sales.SalesOrderDetail
　　WHERE
　　ProductID=ANY(
　　SELECT
　　ProductID
　　FROM
　　AdventureWorks.Production.Product
　　WHERE
　　(ProductID='762')OR
　　(ProductID='754')OR
　　(ProductID='770')
　　)

　　以及测试EXISTS逻辑运算符的查询语句：

以下是引用片段：
　　SELECT
　　a.SalesOrderID,
　　a.CarrierTrackingNumber,
　　a.ProductID,
　　a.UnitPrice
　　FROM
　　AdventureWorks.Sales.SalesOrderDetaila
　　WHERE
　　EXISTS(
　　SELECT
　　b.ProductID
　　FROM
　　AdventureWorks.Production.Productb
　　WHERE
　　a.ProductID=b.ProductID
　　AND(
　　(b.ProductID='762')or
　　(b.ProductID='754')or
　　(b.ProductID='770')
　　)
　　)