Microsoft的优化SQL方法

【 tulaoshi.com - 编程语言 】

　　本文是SQL Server SQL语句优化系列文章的第一篇。该系列文章描述了在Micosoft’s SQLServer2000关系数据库管理系统中优化SELECT语句的基本技巧，我们讨论的技巧可在Microsoft's SQL Enterprise Manager或 Microsoft SQL Query Analyzer（查询分析器）提供的Microsoft图形用户界面使用。

　　除调优方法外，我们给你展示了最佳实践，你可应用到你的SQL语句中以提高性能（所有的例子和语法都已在Microsoft SQL Server 2000中验证）。

　　阅读该系列文章后，你应该对Microsoft 工具包中提供的查询优化工具和技巧有一个基本的了解，我们将提供包含各种各样的以提高性能和加速数据读取操作的查询技巧。

　　Microsoft提供了三种调优查询的主要的方法：　

　　1、使用SET STATISTICS IO 检查查询所产生的读和写

　　2、使用SET STATISTICS TIME检查查询的运行时间

　　3、使用SET SHOWPLAN 分析查询的查询计划

　　SET STATISTICS IO

　　命令SET STATISTICS IO ON 强制SQL Server 报告执行事务时I/O的实际活动。它不能与SET NOEXEC ON 选项配对使用，因为它仅仅对监测实际执行命令的I/O活动有意义。一旦这个选项被打开，每个查询产生包括I/O统计信息的额外输出。为了关闭这个选项，执行SET STATISTICS IO OFF.

　　注：这些命令也能在 Sybase Adaptive Server中运行，虽然结果集可能看起来有点不同。

　　例如，下面是在Northwind 数据库中对于employees表上的一个行统计的简单查询脚本而获得的I/O统计信息。

SET STATISTICS IO ON
GO
SELECT COUNT(*) FROM employees
GO
SET STATISTICS IO OFF
GO
Results:
---------------
2977
Table ‘Employees’ . Scan count 1, logical read 53, physical reads 0, readahead reads 0.

　　这个扫描统计告诉我们扫描执行的数量，逻辑读显示的是从缓存中读出来的页面的数量，物理读显示的是从磁盘中读的页面的数量，Read-ahead 读显示了放置在缓存中用于将来读操作的页面数量。

　　此外，我们执行一个系统存储过程获得表大小的统计信息以供我们分析：

sp_spaceused employees
Results:
name rows reserved data index_size unused
-------------- -------- --------- ------- --------------
Employees 2977 2008KB 1504KB 448KB 56KB

　　通过看这些信息我们能得到些什么呢？

　　◆这个查询没有扫描整个表，在表中的数据量超过1.5M字节，而仅仅执行了53个逻辑I/O操作就得到了结果。这表明该查询发现了一个可用来计算结果的索引，并且扫描索引比扫描所有数据页花费更少的I/O操作。

　　◆索引页几乎全部放在数据缓存中，所以物理读的值是零。这是因为我们之前不久是在employees表上执行了其他查询，此时表和它的索引已经被缓存。你的查询开销可能有不同。

　　◆Microsoft报告没有read-ahead（预读）活动。在这种情况下，数据和索引页已经被缓存起来了。当对一个很大的表作表扫描时，read-ahead可能会半路插入进来，并且在你的查询用到它们之前缓存起所需的页。当SQL Server确定你的事务是顺序读取数据库页并且认为它能预测到你下一步将用到的页面时，Real-ahead会自动打开。实际上一个独立的SQL Server连接在你的进程之前已开始运行并为它缓存数据页。（配置和优化read-ahead 参数已超出这篇文章的讨论范围。

　　在这个例子中，该查询已经尽可能有效率地执行了，不必进一步优化。

　　SET STATISTICS TIME

　　一个事务的实耗时间是一个不稳定的测量，因为这些时间与在服务器上其他用户的活动有关。然而，相比那些对你的用户没有任何意义的数据页数字，它提供了一些实际的测量。他们关心等待查询返回的时间消耗，不关心数据的缓存和有效的read-ahead。SET STATISTICS TIME ON命令报告下面的查询的实际占用时间和CPU使用情况。执行SET STATISTICS TIME OFF禁止这个选项。

SET STATISTICS TIME ON
GO
SELECT COUNT（*） FROM titleauthers
GO
SET STATISTICS TIME OFF
GO
Results:
SQL Server Execution Times;
Cup time=0 ms.　Elapsed time=8672 ms.
SQL Server Parse and Compile Time:
Cpu time=10 ms
----------------
25
(1 row(s) affected)
SQL Servre Execution Times:
Cpu time=0 ms.？ Elapsed time=10 ms.
SQL Server Parse and Compile Time:
Cup time=0 ms

　　第一条信息报告了多少使人困惑的占用（实耗）时间，8672豪秒，这个数据与我们的脚本不相关，这显示的是之前一个命令执行以来逝去的时间。你可以忽略这条信息。SQL Server仅仅花费10毫秒时间去分析和编译该查询。花费0毫秒去执行它（在查询结果可看到）。其真实的意思是这个查询所花费的时间太短以至不能计量。最后的信息报告了这个SET STATISTICS TIME OFF命令相关的分析及编译花费了0毫秒。你可以忽略这个信息。最重要的信息以加重字体突出显示。

　　注意实耗时间和CPU时间是以毫秒显示。这个数字在你的电脑上可能会改变（但是不要尝试与我们的笔记本电脑比较你机器的性能，因为这不是代表性的指标）。而且，每次你执行这个脚本，考虑到你的SQL Server还在处理一些其他事务，你得到的统计信息都可能有一点不同。

　　如果你需要测量一系列的查询或者存储过程的实耗持续时间，更好的办法是采用程序设计的方式（如下所示）。当你运行多个命令时你不得不进行手工合计，这是因为STATISTICS TIME只报告单个查询的持续时间。想象一下，当你对一个在循环里执行成千上万次查询的脚本进行计时的情况下，将面临大量的输出和大量的手工工作。

　　相反，考虑下面这个脚本在事务的前后分别捕获时间并以秒的形式报告总持续时间（你也可以使用毫秒）：

DECLARE @start_time DATETIME
SELECT @start_time=GETDATE()
SELECT　’Elapsed Time,sec’=DATEDIFF(second, @start_time,GETDATE())
GO
　　如果你的脚本被GO分成几步，你不能用本地变量来保存开始时间。变量在GO命令执行后就被销毁。但你可以象这样在临时表里保存开始时间。

CREATE TABLE #save_time (start_time DATETIME NOT NULL)
INSERT #save_time VALUES ( GETDATE())
GO
any script that you want to time (may include GO)
GO
SELECT ‘Elapsed Time, sec’ = DATEDIFF ( second, start_time, GETDATE())
FROM TABLE #save_time
DROP TABLE #save_time
GO
　　请注意，SQL Server’s DATETIME 数据类型存储的时间是以3毫秒为增量。使用DATETIME数据类型不可能获得比这更细的时间粒度。

　　SHOWPLAN 输出和分析

　　这篇文章通过explain plan(解析计划)解释Microsoft SQL Server 2000 使用SET SHOWPLAN_TEXT ON 所输出内容的意义和用处。一个explain plan（也被叫做查询计划，执行计划，或者优化计划）提供了数据库查询引擎执行SQL事务的十分详细的步骤。知道如何阅读explain plan有助于提高高端查询调整和最优化的能力。

　　注：大部分的例子要么是基于PUBS数据库，要么是基于SQL Server系统表的.针对这些实例，我们给很多表增加了好几万条记录以便于在评估查询计划时体现查询优化器的实际作用。

　　SHOWPLAN 输出

　　我们喜欢查询优化器的一个功能就是以查询执行计划的形式提供反馈。现在我们可以更为详细地说明语句的执行，并描述你可能在查询计划中遇到的消息。理解这个输出可以使你的优化水平达到一个新高度。你可以不再把优化器视为一个可以处理你的查询语句的有魔力的“黑盒子”，

　　下面的命令指示SQL Server显示在同一个连接（或进程）中每个查询的执行计划，或将这个选项关闭。

　　SET SHOWPLAN_TEXT { ON | OFF }

　　默认情况下，SHOWPLAN_TEXT ON使得你正在审查的代码不被执行。而是，SQL Server 编译这些代码并且显示这个查询的执行计划。直到你发出SET.SHOWPLAN_TEXT OFF命令后它才停止。

　　其它有用的SET命令

　　有各种各样对调优和调试有用的SET命令。在这篇文档前面我们提到了SET STATISTICS命令。在某些情况下你可以发现其它SET命令的用处：　　　　

　　◆SET NOEXEC{ ON | OFF}: 检查你的Transact-SQL代码的语法，包括编译该代码但不执行。当使用延迟名字解析时，这对检查一个查询语句的语法是很有用的。即，当一个表还没有创建时，你就可以检查基于该表的查询语句的语法。

　　◆SET FMTONLY{ ON | OFF }:仅向客户端返回查询的元数据。对于SELECT语句，通常仅返回列头。

　　◆SET PARSEONLY { ON | OFF }:检查你的Transact-SQL代码的语法，但不编译或执行该代码。

　　一旦设为 ON这些命令将一直有效，直到你手工关闭它们。这些设置不是马上生效，但它们将从下一个步骤开始生效，换言之，你必须在SHOWPLAN or NOEXEC等设置生效前发出GO命令。

　　典型的T-SQL代码如下，获得一个查询的执行计划，而不实际执行。

SET SHOWPLAN_TEXT ON
GO
　 GO
SET SHOWPLAN_TEXT OFF
GO
　　我们将展示几个SHOWPLAN_TEXT 输出的例子。为了避免冗余，我将不重复上面SET命令的展示.在这个部分里所提供的查询都将代替这个脚本中的标签并且都象上面展示的一样“包装”。

　　事实上SHOWPLAN有两个版本：SHOWPLAN_ALL和SHOWPLAN_TEXT。他们提供的信息基本上一样。然而，SHOWPLAN_ALL输出的结果是准备给图形查询工具的而不是给听众的。我们在这整篇文章中将用到SHOWPLAN_TEXT，可提供更可读的格式输出。下面的简单查询选择authors表的所有行。因为我们没有提供where子句所以它除了扫描整个表别无选择：select * form authors

　　在下面的表中SHOWPLAN_TEXT输出的结果没有格式化，我们不得不从SHOWPLAN_ALL的输出中整理出更多的可读信息：

　　这里重要的不同是SHOWPLAN_ALL语句返回了很多有用的调优信息，但这些很难理解和应用。

　　SHOWPLAN 操作

　　SHOWPLAN操作，有时叫做“标签”(tag)，其中一部分操作非常清晰地说明了SQL Server的做法，而其它一些操作将把人难住。这些操作分成物理操作和逻辑操作。物理操作描述被用来处理查询的物理算法，例如，执行一个索引查找。逻辑操作描述语句中使用的关系代数操作，如聚合运算等。SHOWPLAN的结果被细分非具体的步骤分成几步。每个查询的物理操作代表一个独立步骤。步骤通常会伴有一个逻辑操作，但不是所有的步骤都包括逻辑操作。此外，大部分的步骤都有一个操作（要么逻辑操作要么物理操作）和一个参数。参数是操作所影响的查询组件。关于所有执行计划步骤的讨论内容非常繁多。请关注本系列的其它文章。