分析sqlserver查询计划

最近使用到sqlserver数据库并要对查询语句进行调优，之前接触的不多，搜索网上一些帖子和查阅微软msdn资料对sqlserver的查询计划才大致了解了一些，用这篇文章做个总结。本文主要侧重对查询计划的理解，尤其是对微软复杂的运算函数的理解，如理解有误请指出

最近使用到sqlserver数据库并要对查询语句进行调优，之前接触的不多，搜索网上一些帖子和查阅微软msdn资料对sqlserver的查询计划才大致了解了一些，用这篇文章做个总结。本文主要侧重对查询计划的理解，尤其是对微软复杂的运算函数的理解，如理解有误请指出。

如何查看查询计划

先介绍一下如何查看查询计划。使用【SQL Server Management Studio】进行查询，大致有如下几个方式看查询计划

1、set showplan_all on 在查询前显示计划，显示形式为每个查询步骤一行

2、set statistics profile on 在查询后显示计划，并包括每个查询步骤的扫描行数和执行次数

3、选中sql语句按Ctrl + L(同工具栏“显示预估的查询计划”按钮) 以图形方式显示查询计划。这个还可以使用setshowplan_xml on命令，后者生成的xml被【SQL Server Management Studio】打开就是图形查询计划

 

关于查询计划的说明

在msdn上找到set showplan_all on返回结果各列的说明

 

 

 

具体查询计划分析

初步分析计划

开启set showplan_all on

执行如下sql

SELECT dbll.N_SZJY GBM, sum(data.N_SL) NNum

FROM DB_SHARE.dbo.DA_JGXT_VW_QBF_FFJL data

left join DB_SHARE.dbo.T_DBLL dbll

ON dbll.C_ZFBH = data.ZFBH and dbll.D_KSRQ  data.D_RQ and (dbll.D_JSRQ >= data.D_RQ ORdbll.D_JSRQ IS NULL)

where data.D_RQ >= '2012-07-02 00:00:00.0' and data.D_RQ  '2012-08-01 23:59:59.0'

GROUP BY dbll.N_SZJY

 

执行计划左半部分

 

 

 接上图计划的右半部分如下：

 

 

 

Sqlserver的执行计划一出来给人一种特别复杂的感觉有木有，其中命令特别多，还可以看到我们没有写出来的语句比如Expr1006，Expr1012，Bmk1003这都是什么啊？

一步一步来先看看执行计划中每个列的说明

（msdn有对执行计划所有运算符的说明

http://msdn.microsoft.com/zh-cn/library/ms191158(v=sql.105)

）

根据msdn说明，对上面的执行计划分析如下，对于整个计划树的执行是从下到上，从叶子到根的，最上边是整个查询语句：

12、【RID Lookup(OBJECT:([DB_SHARE].[dbo].[T_DBLL] AS [dbll]), SEEK:([Bmk1003]=[Bmk1003]), WHERE:([DB_SHARE].[dbo].[T_DBLL].[D_JSRQ] as [dbll].[D_JSRQ]>=[DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[D_RQ] as [data].[D_RQ] OR [DB_SHARE].[dbo].[T_DBLL].[D_JSRQ] as [dbll].[D_JSRQ] IS NULL) LOOKUP ORDERED FORWARD)】

这是一个书签查找步骤，，RID（record id） Lookup是使用行标示符在堆上进行书签查找，SEEK后面有一个[Bmk1003]=[Bmk1003]，看命名是一个书签，在11行的DefinedValues列有对此的定义。

 

11、【Index Seek(OBJECT:([DB_SHARE].[dbo].[T_DBLL].[I_DBLL_ZFBH_KSRQ] AS [dbll]), SEEK:([dbll].[C_ZFBH]=[DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[ZFBH] as [data].[ZFBH] AND [dbll].[D_KSRQ] 】

这是使用I_DBLL_ZFBH_KSRQ索引在T_DBLL表检索数据，SEEK部分是检索的具体条件，此步骤定义了Bmk1003，11步应该先于12执行，看来在同一层级下顺序是从上到下的。

11步骤和12步骤的意思是先用I_DBLL_ZFBH_KSRQ索引检索数据，再使用书签查找的方式获取每一行的其他数据。因为I_DBLL_ZFBH_KSRQ是一个非聚集索引，它只包含索引列的数据，实际上对T_DBLL检索出的数据列还包括N_SZJY和D_JSRQ，前者要进行Group，后者是进行与DA_JGXT_VW_QBF_FFJL的D_RQ的比对，查询语句涉及到非聚集索引不包含的列时就要通过书签查找或聚集索引查找来提取非索引列（T_DBLL此时没有聚集索引，只能用书签查找的方式）。

 

10、【Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003]))】

一次嵌套循环连接，将11和12步骤数据连接起来，11步骤的数据作为Outer，在进行书签查找的父步骤一般都是Nested Loops Join，这基本符合内表较大且有索引的条件。

 

9、【Table Scan(OBJECT:([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL] AS [data]), WHERE:([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[D_RQ] as [data].[D_RQ]>='2012-07-02 00:00:00.000' AND [DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[D_RQ] as [data].[D_RQ]】

在DA_JGXT_VW_QBF_FFJL表全表扫描数据，条件是D_RQ在一个时间段内。显然这是需要增加索引的

 

8、【Parallelism(Repartition Streams, RoundRobin Partitioning)】

这是什么呢？看看微软古板的说明“Parallelism 运算符执行分发流、收集流和对流重新分区逻辑操作。Argument 列可以包含一个 PARTITION COLUMNS:() 谓词和一个以逗号分隔的分区列的列表。Argument 列还可以包含一个 ORDER BY:() 谓词，以列出分区过程中要保留排序顺序的列。Repartition Streams 运算符处理多个流并生成多个记录流。记录的内容和格式不会改变”。大约是这样，Parallelism表示查询会被并行执行（如果服务器负荷太高可能最终不会并行），这算是sqlserver的一个优化处理，如果服务器处理多任务能力强这就会比串行更有效率，这个并行应该是表示和其他任务的关系，由于10步骤会依赖8步骤的数据，8步骤应该先于10步骤执行

 

7、【Nested Loops(Left Outer Join, OUTER REFERENCES:([data].[ZFBH], [data].[D_RQ], [Expr1012]) WITH UNORDERED PREFETCH)】

一次嵌套循环连接，它将8和10两个步骤得到的数据连接，Outer表是DA_JGXT_VW_QBF_FFJL，因OUTER REFERENCES中的字段都是属于该表。

这里出现了Expr1012，这是个神秘的列，联系上下语句，这个列应该是T_DBLL的N_SZJY，因为后面要用此列数据进行分组。

 

6、【Sort(ORDER BY:([dbll].[N_SZJY] ASC))】

使用N_SZJY列排序，后面要进行Group，这里必须先进行一次排序

 

5、【Stream Aggregate(GROUP BY:([dbll].[N_SZJY]) DEFINE:([partialagg1007]=COUNT_BIG([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL]), [partialagg1009]=SUM([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL])))】

名词解释，“Stream Aggregate 运算符按一列或多列对行分组，然后计算查询返回的一个或多个聚合表达式。此运算符的输出可供查询中的后续运算符引用和/或返回到客户端。Stream Aggregate 运算符要求输入在组中按列进行排序。如果由于前面的 Sort 运算符或已排序的索引查找或扫描导致数据尚未排序，优化器将在此运算符前面使用一个 Sort 运算符”

继续根据微软生硬的解释进行分析，这行的处理就是进行一个分组，因为GROUP BY dbll.N_SZJY一句而产生此行处理。其中还有一个COUNT_BIG，这是一个类似COUNT的函数，区别是前者返回bigint后者返回int，不过语句中只有个sum没有count，为什么还要计算呢？跳过先。

 

4、【Parallelism(Gather Streams, ORDER BY:([dbll].[N_SZJY] ASC))】

又一个并行查询。“Gather Streams 运算符仅用在并行查询计划中。Gather Streams 运算符处理几个输入流并通过组合这几个输入流生成单个记录输出流。不更改记录的内容和格式。如果此运算符保留顺序，则所有的输入流都必须有序。如果输出已排序，则参数列包含一个 ORDER BY:() 谓词和正在排序的列名称。” Gather Streams函数把输入流组合，不过4行只有一个子节点，我理解到4之前仍存在两个输入流，分别来自DA_JGXT_VW_QBF_FFJL表和T_DBLL表，上面的步骤对两个输入流分别有索引条件过滤和分组，但仍然没有整合，在Gather Streams步骤将两个输入流数据整合起来，那么之后的数据看到的就是一个输入流了

3、【Stream Aggregate(GROUP BY:([dbll].[N_SZJY]) DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009])))】

再次进行流聚合。这里和第5步骤的不同在哪里

第5步骤DefinedValues中有如下定义

[partialagg1007]=COUNT_BIG([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL]), [partialagg1009]=SUM([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL])

而此步骤执行了

[globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009])

这就是它们的不同，此步骤再次用N_SZJY分组对之前的计算结果求和，这应该是个避免整合流后分组数据出现重复的操作。

2、【Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010] END))】

Compute Scalar的意思是计算标量。标量，相对于向量而言，无方向数据，就是计算一个数值。这个步骤sqlserver优化器执行了一个case when，globalagg1008是针对每个N_SZJY的count（N_SL），看来之前自动执行count是为了此步，globalagg1010是针对每个N_SZJY的sum（N_SL），对照我们的语句是SELECT dbll.N_SZJY GBM, sum(data.N_SL) NNum，此步骤的意图看来sqlserver会对sum特殊情况的检测，对于整个语句而言就是如果T_DBLL表中N_SZJY列有数据而DA_JGXT_VW_QBF_FFJL中N_SL都为null，那么sum就返回null

 

1、【整条语句】。作为查询计划树的根节点，在计划列表中大多数列都是Null，这个计划的估算行是12行，仅供参考，实际查询的结果是1行

 

关于表扫描

Sql Server 会有以下方法来查找您需要的数据记录：
1. 【Table Scan】：遍历整个表，查找所匹配的记录行。这个操作将会一行一行的检查，当然，效率也是最差的。
2. 【Index Scan】：根据非聚集索引，扫描索引的全部记录，查找所匹配的记录行，匹配的条件可从查询计划的Argument 列中看到。比第一种方式的查找范围要小（B+索引叶子之间有指针类似链表），因此比【Table Scan】要快。
3. 【Index Seek】：根据非聚集索引，定位（获取）记录的存放位置，然后取得记录（这会使用B+索引查找树的定位算法，基本一条记录2-4次IO，取决于表数据量产生的索引树高度），这个方式比起前二种方式会更快。
4. 【Clustered Index Scan】：根据聚集索引扫描全部记录。这个的效率要根据实际情况分析。出现这个步骤可能是效率很差的表现，因为如果条件中的列没有索引，数据库引擎在提取数据的时会考虑进行优化，基于磁盘顺序读比随机读快的原理，数据按照聚集索引的顺序存放，那么用聚集索引来提取数据是一种对更差方式的优化。

比如DA_JGXT_VW_QBF_FFJL表有715455条记录，

如下记录返回8条记录，优化器使用Clustered Index Scan

select * from DB_SHARE.dbo.DA_JGXT_VW_QBF_FFJL where N_ID = 14000

执行时间10s，计划如下，IO消耗是7.21多，此时使用聚集索引扫描来顺序提取数据，这个步骤在这里就是避免更差的随机磁盘读取

 

  

如下语句返回24条记录, 优化器使用Clustered Index Seek

select * from DB_SHARE.dbo.DA_JGXT_VW_QBF_FFJL where D_RQ = '2012-07-25 00:00:00.0'

执行时间0s毫秒级，计划如下，IO消耗是0.000232

 

  

 而直接使用select top 10 * from DB_SHARE.dbo.DA_JGXT_VW_QBF_FFJL查询计划也会使用Clustered Index Scan，

select top 10 * from DB_SHARE.dbo.DA_JGXT_VW_QBF_FFJL

|--Top(TOP EXPRESSION:((10)))

|--Clustered Index Scan (OBJECT:([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[I_DA_JGXT_VW_QBF_FFJL_RQ]))

对于这个无条件的语句这个计划已经是最优的了
5. 【Clustered Index Seek】：根据聚集索引获取记录，不解释，最快！

 

优化Table Scan

使用日期过滤数据，对D_RQ建立聚集索引 

CREATE CLUSTERED INDEX I_DA_JGXT_VW_QBF_FFJL_RQ ON dbo.DA_JGXT_VW_QBF_FFJL(D_RQ)

 

再次查看执行计划

左半部分：

 

 

 

右半部分

 

  

Table Scan变为Clustered Index Seek，看右半部分计划中的EstimateRows、EstimateIO、EstimateCPU、TocalSubTreeCost等都有很大提升

优化RID Lookup

前面提到RID Lookup是使用非聚集索引时提取了索引外的列产生的一种操作，中文解释为书签查找。

微软有一篇专门的文章http://blogs.msdn.com/b/craigfr/archive/2006/06/30/652639.aspx对此作出了解释。每次书签查找会产生一次随机IO，随机IO对于磁盘来说是比较耗费资源的，虽然sqlserver优化器认为这个比不用索引的消耗小些因而选择了这个方式，但可能的情况下我们还是要考虑优化。

优化书签查找的方式大致两种，一种是给目前已经使用的索引加入要查询的列，使得查询的列都在索引中；另一种是使索引成为聚集索引。那么可以考虑创建(C_ZFBH, D_KSRQ, D_JSRQ, N_SZJY)4键联合索引或将(C_ZFBH,D_KSRQ)的索引改为聚集索引。

我先采用了聚集索引优化，查询计划的IO、CPU、Cost都有所提升，主要是TotalSubTreeCost一项提升较多。按照微软对此项的说明为查询开销，这是一个综合的数值，一般这个开销较小的更好，不过也不绝对。

  

 

那么如果用4键联合索引呢？

我发现对比两者的查询计划相差很小，于是我用了set statistics profile on来查看实际的执行情况，这个开关比前面的计划多两列，会返回每个步骤的实际扫描行数和执行次数

列名	说明
Rows	各运算符生成的实际行数
Executes	运算符执行的次数

先看聚集索引，本次执行耗费8s

  

 

 

再看4键联合索引，本次执行耗费12s，值得一提的是此时两个索引都存在，是sqlserver优化器选择了4键联合索引

 

 

 

相比之下，后者的TotalSubTreeCost较小因IO少，但是前者实际扫描的行数较少，且执行时间更短。我在执行前已使用了dbcc dropcleanbuffers和dbcc freeproccache清除缓存，不过我使用的数据库是虚拟机，在执行效率上经常有波动，后者的执行时间长可能因为索引还没有全部加载到内存中，实际测试时有时后者的时间更短。不过鉴于D_JSRQ是存在空值并且检索的时候都要使用(D_JSRQ>日期 or D_JSRQ is null)这样的条件，前者可能更好。

 

看懂Sqlserver查询计划

http://www.cnblogs.com/fish-li/archive/2011/06/06/2073626.html

msdn逻辑运算符和物理运算符引用

http://msdn.microsoft.com/zh-cn/library/ms191158(v=sql.105)