站长资源数据库

select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finished
from   OrdersView as v
where v.OrderDate >= '2010-12-1' and v.OrderDate < '2011-12-1';

SELECT     dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate, 
            dbo.Orders.SumMoney, dbo.Orders.Finished, 
            ISNULL(dbo.Customers.CustomerName, N'') AS CustomerName
FROM         dbo.Orders LEFT OUTER JOIN
                dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

foreach(row r1 in outer table)
    foreach(row r2 in inner table)
        if( r1, r2 符合匹配条件 )
            output(r1, r2);

set statistics profile on 
select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finished
from   OrdersView as v
where v.OrderDate >= '2010-12-1' and v.OrderDate < '2011-12-1';

注意：现在加了一句，【set statistics profile on 】，得到的结果如下：

可以从图片上看到，执行查询后，得到二个表格，上面的表格显示了查询的结果，下面的表格显示了查询的执行过程。相比本文的第一张图片，这张图片可能在直观上不太友好，但是，它能反映更多的信息，而且尤其在比较复杂的查询时，可能看起来更容易，因为对于复杂的查询，【执行计划】的步骤太多，图形方式会造成图形过大，不容易观察。而且这张执行过程表格能反映2个很有价值的数据（前二列）。

还是来看看这个【执行过程表格】吧。我来挑几个重要的说一下。
【Rows】：表示在一个执行步骤中，所产生的记录条数。（真实数据，非预期）
【Executes】：表示某个执行步骤被执行的次数。（真实数据，非预期）
【Stmt Text】：表示要执行的步骤的描述。
【EstimateRows】：表示要预期返回多少行数据。

在这个【执行过程表格】中，对于优化查询来说，我认为前三列是比较重要的。对于前二列，我上面也解释了，意思也很清楚。前二列的数字也大致反映了那些步骤所花的成本，对于比较慢的查询中，应该留意它们。【Stmt Text】会告诉你每个步骤做了什么事情。对于这种表格，它所要表达的其实是一种树型信息（一行就表示在图形方式下的一个节点），所以，我建议从最内层开始去读它们。做为示例，我来解释一下这张表格它所表达的执行过程。

第5行：【Clustered Index Seek(OBJECT:([MyNorthwind].[dbo].[Customers].[PK_Customers]), SEEK:([MyNorthwind].[dbo].[Customers].[CustomerID]=[MyNorthwind].[dbo].[Orders].[CustomerID]) ORDERED FORWARD)】，意思是说，SQL Server在对表Customers做Seek操作，而且是按照【Clustered Index Seek】的方式，对应的索引是【PK_Customers】，seek的值来源于[Orders].[CustomerID]

第4行：【Clustered Index Scan(OBJECT:([MyNorthwind].[dbo].[Orders].[PK_Orders]), WHERE:([MyNorthwind].[dbo].[Orders].[OrderDate]>='2010-12-01 00:00:00.000' AND [MyNorthwind].[dbo].[Orders].[OrderDate]<'2011-12-01 00:00:00.000'))】，意思是说，SQL Server在对表Customers做Scan操作，即：最差的【表扫描】的方式，原因是，OrderDate列上没有索引，所以只能这样了。

第3行：【Nested Loops(Left Outer Join, OUTER REFERENCES:([MyNorthwind].[dbo].[Orders].[CustomerID]))】，意思是说，SQL Server把第5行和第4行产生的数据用【Nested Loops】的方式联接起来，其中Outer表是Orders，要联接的匹配操作也在第5行中指出了。

第2行：【Compute Scalar(DEFINE:([Expr1006]=isnull([MyNorthwind].[dbo].[Customers].[CustomerName],N'')))】，意思是说，要执行一个isnull()函数的调用。具体原因请参考本文前部分中给出视图定义代码。

第1行：【SELECT [v].[OrderID],[v].[CustomerID],[v].[CustomerName],[v].[OrderDate],[v].[SumMoney],[v].[Finished] FROM [OrdersView] [v] WHERE [v].[OrderDate]>=@1 AND [v].[OrderDate]<@2】，通常第1行就是整个查询，表示它的返回值。

索引统计信息：查询计划的选择依据

前面一直说到【执行计划】，既然是计划，就表示要在具体执行前就能确定下来的操作方案。那么SQL Server是如何选择一个执行计划的呢？SQL Server怎么知道什么时候该用索引或者用哪个索引呢？对于SQL Server来说，每当要执行一个查询时，都要首先检查这个查询的执行计划是否存在缓存中，如果没有，就要生成一个执行计划，具体在产生执行计划时，并不是看有哪些索引可用（随机选择），而是会参考一种被称为【索引统计信息】的数据。如果您仔细地看一下前面的执行计划或者执行过程表格，会发现SQL Server能预估每个步骤所产生的数据量，正是因为SQL Server能预估这些数据量，SQL Server才能选择一个它认为最合适的方法去执行查询过程，此时【索引统计信息】就能告诉SQL Server这些信息。说到这里，您是不是有点好奇呢，为了让您对【索引统计信息】有个感性的认识，我们来看看【索引统计信息】是个什么样子的。请在【SQL Server Management Studio】，输入以下语句，然后执行。

dbcc show_statistics (Products, IX_CategoryID)

得到的结果如下图：

首先，还是解释一下命令：【dbcc show_statistics】这个命令可以显示我们想知道的【索引统计信息】，它需要二个参数，1. 表名，2. 索引名

再来看看命令的结果，它有三个表格组成：
1. 第一个表格，它列出了这个索引统计信息的主要信息。

列名 说明Name统计信息的名称。Updated上一次更新统计信息的日期和时间。Rows表中的行数。Rows Sampled统计信息的抽样行数。Steps数据可分成多少个组，与第三个表对应。Density第一个索引列前缀的选择性（不包括 EQ_ROWS）。Average key length所有索引列的平均长度。String Index如果为“是”，则统计信息中包含字符串摘要索引，以支持为 LIKE 条件估算结果集大小。仅适用于 char、varchar、nchar 和 nvarchar、varchar(max)、nvarchar(max)、text 以及 ntext 数据类型的前导列。

2. 第二个表格，它列出各种字段组合的选择性，数据越小表示重复越性越小，当然选择性也就越高。

列名 说明All density索引列前缀集的选择性（包括 EQ_ROWS）。注意：这个值越小就表示选择性越高。
如果这个值小于0.1，这个索引的选择性就比较高，反之，则表示选择性就不高了。Average length索引列前缀集的平均长度。Columns为其显示 All density 和 Average length 的索引列前缀的名称。

3. 第三个表格，数据分布的直方图，SQL Server就是靠它预估一些执行步骤的数据量。

列名 说明RANGE_HI_KEY每个组中的最大值。RANGE_ROWS每组数据组的估算行数，不包含最大值。EQ_ROWS为了能让您更好的理解这些数据，尤其是第三组，请看下图：

当时我在填充测试数据时，故意把CategoryId分为1到8（10是后来临时加的），每组填充了78条数据。所以【索引统计信息】的第三个表格的数据也都是正确的，也正是根据这些统计信息，SQL Server才能对每个执行步骤预估相应的数据量，从而影响Join之类的选择。当然了，在选择Join方式时，也要参考第二个表格中字段的选择性。SQL Server在为查询生成执行计划时，查询优化器将使用这些统计信息并结合相关的索引来评估每种方案的开销来选择最佳的查询计划。

再来个例子说明一下统计信息对于查询计划的重要性。首先多加点数据，请看以下代码：

declare @newCategoryId int;
insert into dbo.Categories (CategoryName) values(N'Test statistics');
set @newCategoryId = scope_identity();
declare @count int;
set @count = 0;
while( @count < 100000 )
begin
    insert into Products (ProductName, CategoryID, Unit, UnitPrice, Quantity, Remark) 
    values( cast(newid() as nvarchar(50)), @newCategoryId, N'个', 100, @count +1, N'');
    set @count = @count + 1;
end
go
update statistics Products;
go

再来看看索引统计信息：

再来看看同一个查询，但因为查询参数值不同时，SQL Server选择的执行计划：

select p.ProductId, t.Quantity 
from Products as p left outer join [Order Details] as t on p.ProductId = t.ProductId 
where p.CategoryId = 26;    -- 26 就是最新产生的CategoryId，因此这个查询会返回10W条记录
select p.ProductId, t.Quantity 
from Products as p left outer join [Order Details] as t on p.ProductId = t.ProductId 
where p.CategoryId = 6;    -- 这个查询会返回95条记录

从上图可以看出，由于CategoryId的参数值不同，SQL Server会选择完全不同的执行计划。统计信息重要性在这里体现的很清楚吧。

创建统计信息后，数据库引擎对列值（根据这些值创建统计信息）进行排序，并根据这些值（最多 200 个，按间隔分隔开）创建一个“直方图”。直方图指定有多少行精确匹配每个间隔值，有多少行在间隔范围内，以及间隔中值的密度大小或重复值的发生率。

SQL Server 2005 引入了对 char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text 和 ntext 列创建的统计信息收集的其他信息。这些信息称为“字符串摘要”，可以帮助查询优化器估计字符串模式中查询谓词的选择性。查询中有 LIKE 条件时，使用字符串摘要可以更准确地估计结果集大小，并不断优化查询计划。这些条件包括诸如 WHERE ProductName LIKE '%Bike' 和 WHERE Name LIKE '[CS]heryl' 之类的条件。

既然【索引统计信息】这么重要，那么它会在什么时候生成或者更新呢？事实上，【索引统计信息】是不用我们手工去维护的，SQL Server会自动去维护它们。而且在SQL Server中也有个参数来控制这个更新方式：

统计信息自动功能工作方式

创建索引时，查询优化器自动存储有关索引列的统计信息。另外，当 AUTO_CREATE_STATISTICS 数据库选项设置为 ON（默认值）时，数据库引擎自动为没有用于谓词的索引的列创建统计信息。

随着列中数据发生变化，索引和列的统计信息可能会过时，从而导致查询优化器选择的查询处理方法不是最佳的。例如，如果创建一个包含一个索引列和 1,000 行数据的表，每一行在索引列中的值都是唯一的，则查询优化器将把该索引列视为收集查询数据的好方法。如果更新列中的数据后存在许多重复值，则该列不再是用于查询的理想候选列。但是，查询优化器仍然根据索引的过时分布统计信息（基于更新前的数据），将其视为好的候选列。

当 AUTO_UPDATE_STATISTICS 数据库选项设置为 ON（默认值）时，查询优化器会在表中的数据发生变化时自动定期更新这些统计信息。每当查询执行计划中使用的统计信息没有通过针对当前统计信息的测试时就会启动统计信息更新。采样是在各个数据页上随机进行的，取自表或统计信息所需列的最小非聚集索引。从磁盘读取一个数据页后，该数据页上的所有行都被用来更新统计信息。常规情况是：在大约有 20% 的数据行发生变化时更新统计信息。但是，查询优化器始终确保采样的行数尽量少。对于小于 8 MB 的表，则始终进行完整扫描来收集统计信息。

采样数据（而不是分析所有数据）可以将统计信息自动更新的开销降至最低。在某些情况下，统计采样无法获得表中数据的精确特征。可以使用 UPDATE STATISTICS 语句的 SAMPLE 子句和 FULLSCAN 子句，控制按逐个表的方式手动更新统计信息时采样的数据量。FULLSCAN 子句指定扫描表中的所有数据来收集统计信息，而 SAMPLE 子句用来指定采样的行数百分比或采样的行数

在 SQL Server 2005 中，数据库选项 AUTO_UPDATE_STATISTICS_ASYNC 提供了统计信息异步更新功能。当此选项设置为 ON 时，查询不等待统计信息更新，即可进行编译。而过期的统计信息置于队列中，由后台进程中的工作线程来更新。查询和任何其他并发查询都通过使用现有的过期统计信息立即编译。由于不存在等待更新后的统计信息的延迟，因此查询响应时间可预测；但是过期的统计信息可能导致查询优化器选择低效的查询计划。在更新后的统计信息就绪后启动的查询将使用那些统计信息。这可能会导致重新编译缓存的计划（取决于较旧的统计信息版本）。如果在同一个显式用户事务中出现某些数据定义语言 (DDL) 语句（例如，CREATE、ALTER 和 DROP 语句），则无法更新异步统计信息。

AUTO_UPDATE_STATISTICS_ASYNC 选项设置于数据库级别，并确定用于数据库中所有统计信息的更新方法。它只适用于统计信息更新，而无法用于以异步方式创建统计信息。只有将 AUTO_UPDATE_STATISTICS 设置为 ON 时，将此选项设置为 ON 才有效。默认情况下，AUTO_UPDATE_STATISTICS_ASYNC 选项设置为 OFF。

从以上说明中，我们可以看出，对于大表，还是有可能存在统计信息更新不及时的时候，这时，就可能会影响查询优化器的判断了。
有些人可能有个经验：对于一些慢的查询，他们会想到重建索引来尝试解决。其实这样做是有道理的。因为，在某些时候一个查询突然变慢了，可能和统计信息更新不及时有关，进而会影响查询优化器的判断。如果此时重建索引，就可以让查询优化器知道最新的数据分布，自然就可以避开这个问题。还记得我前面用【set statistics profile on】显示的执行过程表格吗？注意哦，那个表格就显示每个步骤的实际数据量和预估的数据量。要不要重建索引，其实我们可以用【set statistics profile on】来看一下，如果实际数据量和预估的数据量的差值比较大，那么我们可以考虑手工去更新统计信息，然后再去试试。

优化视图查询

再来说说优化视图查询，虽然视图也是由一个查询语句定义的，本质上也是一个查询，但它和一般的查询语句在优化时，还是有所区别的。这里主要的区别在于，视图虽然是由一个查询语句定义的，但如果只去分析这个查询定义，可能得到的意义不大，因为视图多数时候就不是直接使用，而是在使用前，会加上where语句，或者放在其它语句中供from子句所使用。下面还是举个例子吧，在我的演示数据库中有个视图OrdersView，定义代码前面有。我们来看看，如果直接使用这个视图，会有什么样的执行计划出来：

从这个视图可以看出，SQL Server会对表Orders做全表扫描，应该是很低效的。再来看看下面这个查询：

从这个执行计划可以看出，与上面那个就不一样了。前一个查询中对Orders表的查找是使用【Clustered Index Scan】的方式，而现在在使用【Clustered Index Seek】的方式了，最右边二个步骤的成本的百分比也发生了改变。这样就足以说明，优化视图时，最好能根据实际需求，应用不同的过滤条件，再来决定如何去优化。

再来一个由三个查询组成的情况来看看这个视图的执行计划。

select * from dbo.OrdersView where OrderId = 1;
select * from dbo.OrdersView where CustomerId = 1;
select * from dbo.OrdersView where OrderDate >= '2010-12-1' and OrderDate < '2011-12-1';

很明显，对于同一个视图，在不同的过滤条件下，执行计划的差别很明显。

推荐阅读-MSDN文章

索引统计信息
http://msdn.microsoft.com/zh-cn/library/ms190397(SQL.90).aspx

查询优化建议
http://msdn.microsoft.com/zh-cn/library/ms188722(SQL.90).aspx

用于对运行慢的查询进行分析的清单
http://msdn.microsoft.com/zh-cn/library/ms177500(SQL.90).aspx

逻辑运算符和物理运算符引用
http://msdn.microsoft.com/zh-cn/library/ms191158(SQL.90).aspx