笔记60-徐 读懂执行计划 P383

笔记60-徐  读懂执行计划 P383  

  1 --读懂执行计划 P383  2012-12-29
  2 
  3 --编译结束以后,就可以让SQL打出执行计划。执行完毕后,还可以让SQL打出
  4 --按照这个执行计划运行的统计信息。通过这样的方法,用户可以研究为什麽
  5 --一句指令会运行时间长。
  6 
  7 --得到执行计划的方式有两种:
  8 --1、一种是在指令的前面打开一些开关,让执行计划信息打在结果集里,这种
  9 --方法比较适合在一个测试环境里对单个语句调优。这些开关最常用的有
 10 SET SHOWPLAN_ALL ON
 11 
 12 SET showplan_xml ON  --(是不是reuse了一个执行计划,SQL有没有觉得缺少索引),只能在XML
 13 --的输出里看到
 14 
 15 SET STATISTICS PROFILE ON
 16 
 17 
 18 --2、另一种方法是使用SQL Trace里的事件跟踪来跟踪语句的执行计划。常用的事件有
 19 --SHOWPLAN ALL、showplan STATISTICS profile、showplan xml STATISTICS PROFILE、、
 20 --这种方法的好处是无须打开任何开关,可以用来直接跟踪应用程序的语句执行。缺点是
 21 --他会把所有语句都抓下来。如果SQL比较忙的话,输出会很大,有可能影响性能,所以
 22 --要慎用
 23 
 24 
 25 --这两种方法收集的信息大部分是一样的。XML的方式会将结果以XML的结构返回,在
 26 --SQL Trace里,这种结果可以用图形的方式显示执行计划以及其相关的信息。对执行
 27 --计划比较简单的语句,这样的输出方式可能比较友善。但是对于复杂的执行计划,
 28 --如果用图形的方式显示,一个屏幕很难全放得下,每一步的统计信息要鼠标点到
 29 --才会显示出来,笔者认为反而不如文本输出格式易读性好。所以下面将集中讨论
 30 --SHOWPLAN_ALL的方法和showplan STATISTICS profile方法。当然,有些信息
 31 --(例如是不是reuse了一个执行计划,SQL有没有觉得缺少索引),只能在XML
 32 --的输出里看到
 33 
 34 
 35 --SHOWPLAN_ALL的方法和showplan STATISTICS profile方法都能够完整地输出执行计划
 36 --为什麽要区别对待他们呢?他们有两大区别:
 37 
 38 --1、输出执行计划是在语句执行前还是执行后
 39 --如果打开set showplan_all on,再运行语句,SQL会在语句编译好或者找到可重用的执行计划
 40 --后,就输出他,语句本身不会被执行。在SQL Trace里,showplan all事件发生在语句开始之前
 41 
 42 --而set STATISTICS profile on和showplan STATISTICS profile发生在语句结束之后。也就是说
 43 --,如果语句没做完就被用户停止掉,将不会得到执行计划输出
 44 
 45 
 46 --2、是否包含每一步实际的返回行数
 47 --SQL在生产执行计划的时候,会预估每一步返回行数,以算出预计的成本。但是预估的值
 48 --不一定准确。STATISTICS profile的结果里,不但包含预估值,还会返回实际每一步的返回
 49 --行数。比较这两行,就能看出SQL预估得是否准确。showplan all的方法由于没有真正去运行
 50 --这句查询,只能得到预估返回行数,得不到实际返回行数
 51 
 52 --所以如果用户能够等到要优化的语句做完,那么拿一份STATISTICS profile的结果就可以了,
 53 --从这个结果里能看到调优需要的所有信息。但是STATISTICS profile的缺点是要真正去运行
 54 --一遍语句。如果语句非常昂贵,要跑很久才能跑完(有时候甚至跑不完),那用户就不得不
 55 --等待。有时候语句会对数据进行修改,例如update,insert,delete等,这不是生成数据库
 56 --能让你随便测试的。在这些情况下,可能只能使用showplan all的方法,得到一个预估的
 57 --执行计划。两种方法各有利弊,各有合适的情形
 58 
 59 
 60 USE [AdventureWorks]
 61 GO
 62 SET SHOWPLAN_ALL ON
 63 GO
 64 SELECT
 65 a.[SalesOrderID],a.[OrderDate],a.[CustomerID],
 66 b.[SalesOrderDetailID],b.[ProductID],b.[OrderQty],
 67 b.[UnitPrice]
 68 FROM [dbo].[SalesOrderHeader_test] a
 69 INNER JOIN [dbo].[SalesOrderDetail_test] b
 70 ON a.[SalesOrderID]=b.[SalesOrderID]
 71 WHERE a.[SalesOrderID]=43659
 72 GO
 73 
 74 
 75 --只返回执行计划,没有结果集返回
 76 --执行计划里只有“EsitmateRows”,没有“Rows”(实际行数)
 77 
 78 
 79 SET SHOWPLAN_ALL OFF
 80 GO
 81 
 82 SET STATISTICS PROFILE ON
 83 GO
 84 SELECT
 85 a.[SalesOrderID],a.[OrderDate],a.[CustomerID],
 86 b.[SalesOrderDetailID],b.[ProductID],b.[OrderQty],
 87 b.[UnitPrice]
 88 FROM [dbo].[SalesOrderHeader_test] a
 89 INNER JOIN [dbo].[SalesOrderDetail_test] b
 90 ON a.[SalesOrderID]=b.[SalesOrderID]
 91 WHERE a.[SalesOrderID]=43659
 92 
 93 GO
 94 
 95 --先返回结果集,再返回执行计划
 96 --执行计划里有“EsitmateRows”,也有“Rows”(实际行数)
 97 
 98 SET STATISTICS PROFILE OFF
 99 GO
100 
101 
102 
103 --有了执行计划,就要读懂他的含义。SQL的执行计划是按照树状结构输出的,分成
104 --若干个层次,下一层分支隶属于上一层子句。例如,上面这个查询,他的最底层
105 --子句是执行计划的第五行(在[SalesOrderDetail_test]上的index seek)和第六行
106 --([SalesOrderDetail_test]上的clustered index seek)。在此之上,SQL将这两个
107 --结果集用嵌套循环的方式联接起来(执行计划第四行)。得到的结果,和执行计划
108 --第三行(在[SalesOrderDetail_test]上作clustered index seek)并列为一层。
109 --他们的上一层是嵌套循环(执行计划第二行),说明SQL是使用嵌套循环的方式,
110 --把两个结果集合并起来,最后得到了查询的最终结果
111 
112 --现在来看看原先这句查询,如果是手动做的话,你会怎麽做:
113 SELECT
114 a.[SalesOrderID],a.[OrderDate],a.[CustomerID],
115 b.[SalesOrderDetailID],b.[ProductID],b.[OrderQty],
116 b.[UnitPrice]
117 FROM [dbo].[SalesOrderHeader_test] a
118 INNER JOIN [dbo].[SalesOrderDetail_test] b
119 ON a.[SalesOrderID]=b.[SalesOrderID]
120 WHERE a.[SalesOrderID]=43659
121 GO
122 
123 --查询的含义:是把[SalesOrderDetail_test]和[SalesOrderHeader_test]这两张表通过
124 --[SalesOrderID]这个共同的字段联接起来,选择其中[SalesOrderID]等于43659的记录
125 --把他们的[SalesOrderID],[OrderDate][CustomerID],[SalesOrderDetailID],[ProductID],
126 --[OrderQty],字段值都返回回来
127 
128 --因为[SalesOrderHeader_test]在[SalesOrderID]字段上有一个聚集索引。SQL可以直接找到
129 --[SalesOrderID]=43659的记录,然后把他的[SalesOrderID],OrderDate,CustomerID值找到
130 --所以这里使用Clustered index seek是很自然的事情
131 
132 
133 --但是对于dbo.SalesOrderDetail_test这张表,由于在[SalesOrderID]字段上的索引是
134 --nonclustered的,而查询要返回的字段并不能完全被非聚集索引所包含,所以SQL先通过
135 --SalesOrderDetail_test_NCL这个非聚集索引找到[SalesOrderID]=43659的记录,然后
136 --再根据指针和聚集索引SalesOrderDetail_test_CL做Nested Loops的联接,把所有需要的
137 --字段值取出来
138 
139 
140 --在执行计划里,会出现很多运算符,比如Parallelism并行,Nested Loops循环嵌套连接
141 --,Merge Join合并连接
142 
143 --当多表连接时(书签查找,索引之间的连接都算),SQL Server会采用三类不同的连接方式:
144 --循环嵌套连接(Nested Loops Join),合并连接(Merge Join),散列连接(Hash Join)。
145 --这几种连接并不是哪种会比另一种更好,而是每种连接方式都会适应特定场景。
146 
147 --之类。要读懂执行计划,了解哪些步骤比较花时间和资源,就要理解常见的运算。这些
148 --运算的含义,SQL的联机丛书里都有介绍。在这里只介绍几个最常见的,也是比较容易
149 --出问题的运算
150 
151 
152 
153 
154 --联接 Join
155 --联接是SQL语句里最常见的运算。一条语句会涉及若干个表格,这些表格是通过
156 --某些字段联接在一起的。SQL也要把结果集作联接,这就是平时说的Join
157 
158 --SQL有三种Join方法:nested loops join,merge join ,hash join。这三种方法
159 --没有哪一种是永远最好的,但是都有其最合适的上下文。SQL会根据两个结果集所
160 --基于的表格结构,以及结果集大小,选择最合适的联接方法。当然,用户也可以
161 --在语句里指定join方法。SQL会尽力尊重你的选择。(有些查询按照指定的join方法)
162 --可能做不出执行计划,SQL会报错
163 
164 
165 --例如,下面这同一句查询,因为有不同的join hint,选择的联接也有不同
166 
167 USE [AdventureWorks]
168 GO
169 SET STATISTICS PROFILE ON
170 GO
171 SELECT
172 COUNT(b.[SalesOrderID])
173 FROM [dbo].[SalesOrderHeader_test] a
174 INNER LOOP JOIN [dbo].[SalesOrderDetail_test] b
175 ON a.[SalesOrderID]=b.[SalesOrderID]
176 WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
177 GO
178 
179 SELECT
180 COUNT(b.[SalesOrderID])
181 FROM [dbo].[SalesOrderHeader_test] a
182 INNER MERGE JOIN [dbo].[SalesOrderDetail_test] b
183 ON a.[SalesOrderID]=b.[SalesOrderID]
184 WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
185 GO
186 
187 SELECT
188 COUNT(b.[SalesOrderID])
189 FROM [dbo].[SalesOrderHeader_test] a
190 INNER HASH JOIN [dbo].[SalesOrderDetail_test] b
191 ON a.[SalesOrderID]=b.[SalesOrderID]
192 WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
193 
194 GO
195 
196 
197 
198 --下面就先来介绍这三种方法,然后将他们作一下比较
199 
200 --nested loops join
201 --nested loops join是一种最基本的联接方法,被SQL广泛使用。他的算法是
202 --对于两张要被join在一起的表格,SQL选择一张做Outer table ,另外一张
203 --做inner table
204 
205 for each row r1 IN the OUTER TABLE
206 begin
207 FOR each row r2 IN the INNER TABLE
208   IF r1 joins WITH r2
209   OUTPUT(r1,r2)
210 IF r1 did NOT JOIN
211   OUTPUT(r1,NULL)
212 END
213 
214 --以上面的查询例子为例,SQL选择了[SalesOrderHeader_test]作为outer table
215 --[SalesOrderDetail_test]作为inner table。首先SQL在[SalesOrderHeader_test]
216 --上使用聚集索引做一个seek,找出每一条 a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660
217 --的记录。每找到一条记录,SQL都进入Inner table,找能够和他join返回数据
218 --的记录(a.[SalesOrderID]=b.[SalesOrderID])。由于outer table[SalesOrderHeader_test]
219 --上有10000条a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660的记录,每一条
220 --SQL都要到inner table里去找能join的row,所以inner table [SalesOrderDetail_test]
221 --被扫描了10000次(注意比较“rows”和“executes”列的值)
222 --rows:50577  COUNT(b.[SalesOrderID])=50577
223 --executes:10000
224 
225 
226 
227 --nested loops join是一种基本的联接方式。他不需要SQL为join建立另外的数据结构
228 --所以也比较节省内存空间,也无须用tempdb的空间。他适用join类型是非常广泛的
229 --有些联接是merge join和hash join做不了的,但nested loops join可以做。
230 --所以这种联接方式的优点是很明显的,但是他的缺点也很明显
231 
232 --1、算法的复杂度等于inner table乘以outer table
233 --所以如果是两张表比较大,尤其是outer table比较大的情况,inner table会被扫描很多次。这时候
234 --的算法复杂度增加的非常快,总的资源消耗量也会增加得很快。所以nested loops join比较
235 --适合于两个比较小的结果集做联接,或者至少是outer table的结果集比较小
236 
237 --像前面的例子,由于outer table [SalesOrderHeader_test]的数据集有10000条记录,所以
238 --inner table就会被扫描10000次。这是不太划算的。如果让SQL自己选择而不加join hint,
239 --SQL不会选择nested loops 的联接方式
240 
241 
242 --2、outer table的数据集最好能够事先排序好,以提高检索效率
243 --如果数据集能够事先排序好,做nested loops当然能够更快一些。当然如果没有排序,nested loops
244 --join 也能做得出来,就是cost会大大增加
245 
246 
247 
248 
249 --3、inner table上最好有一个索引,能够支持检索
250 --由于算法是拿着outer table里的一个值,在inner table里找到所有符合条件的记录,所以在inner table
251 --里找得快慢也能很大程度上影响整体的速度。如果进行检索的字段上有一个索引,查找的速度会大大
252 --加快,inner table数据集稍微大一点也没关系。否则就要每次做整个数据集的扫描,是很浪费资源的
253 
254 
255 --总之,nested loops join对数据集比较小的联接,效率是最高的,因此在SQL里使用得很广泛。当SQL
256 --发现能够选择一个很小的数据集作为outer table的时候,他往往会选择nested loops,大的数据集
257 --做outer table,性能会急剧下降。很多语句性能问题,都是由于这个造成的
258 
259 
260 
261 --merge join
262 --刚才反复强调,nested loops join只适用于outer table数据集比较小的情况。如果数据集比较大,SQL
263 --怎麽做联接呢?还好他有其他两种联接方式,merge join和hash join
264 
265 --merge join的前提条件是,两个数据集必须要先排序好。他的算法是:
266 GET FIRST row r1 FROM input1
267 GET FIRST row r2 FROM INPUT2
268 WHILE NOT at the END OF either INPUT
269 begin
270 IF r1 joins WITH r2
271 begin
272 OUTPUT(r1,r2)
273 GET NEXT row r2 FROM INPUT 2
274 END
275 ELSE
276 IF r1>r2 GET NEXT row r1 FROM INPUT 1
277 ELSE GET NEXT row2 FROM INPUT 2
278 END
279 
280 
281 --也就是说,从两边的数据集里各取一个值,比较一下。如果相等,就把这两行联接起来返回。
282 --如果不相等,那就把小的那个值丢掉,按顺序取下一个更大的。两边的数据集有一边遍历
283 --结束,整个join的过程就结束。所以整个算法的复杂度最大就是大的那个数据集里的记录
284 --数量,这个比起nested loops join两个数据集相乘的复杂度,的确是小了很多。所以
285 --在数据集很大的情况下,merge join的优势是非常明显的
286 
287 
288 --rows:50577  COUNT(b.[SalesOrderID])=50577
289 --executes:4
290 
291 --但是merge join的局限性也很强,所以在实际的语句里,使用得并不是那么普遍。
292 --他的局限性主要有:
293 
294 --1、做联接的两个数据集必须要事先按照join的字段排好序(不知道是不是要加order by如果没有索引的话)
295 --这个先决条件是merge join算法的基础,而对大的数据集排序本来就是一件比较复杂的事情。
296 --有些数据集是基于join的那个字段上的索引得到的,所以能够不费额外的资源就排好了顺序,
297 --这时候用merge join可能就比较合适
298 
299 --例如,范围查询,两个数据集都是根据在[SalesOrderID]字段的索引上seek出来的,所以
300 --不需要再做排序
301 
302 
303 --2、merge join只能做以“值相等”为条件的联接,而且如果数据集可能有重复的数据,merge join
304 --要采用many-to-many这种很浪费资源的联接方式
305 
306 --在SQL扫描数据集时,如果数据集1有两个或者多个记录值相等,SQL必须得把数据集2里扫描过的
307 --数据暂时建立一个数据结构存放起来,万一数据集1里下一个记录还是这个值,那还有用。
308 --这个临时数据结构被称为“worktable”,会被放在tempdb或者内存里。这样做很耗资源,所以
309 --在上面的执行计划里,merge join的两个子句的subtree cost分别为0.202和0.109。但是
310 --many-to-many的join子句subtree cost是5.051。也就是说,join自己的cost是4.74
311 --(5.051-0.202-0.109=4.74)。这是一个不小的cost
312 
313 --如果把SalesOrderHeader_test_CL(聚集索引),改成一个唯一unique的聚集索引
314 --SQL就知道数据集1(dbo.SalesOrderHeader_test)的值不会重复,也就不需要
315 --做many-to-many join
316 --执行计划果然发生变化,预估的cost降低了一个数量级
317 
318 --上面这两个限制(排序,唯一(可以不唯一)),影响了merge join的使用范围。
319 --但是merge join的一个独特好处是,返回的数据集也是按照顺序排好的
320 
321 
322 --这里顺便提一下结果集的顺序问题。有些用户会抱怨,同一个查询的结果集为什麽有时候是
323 --按他想要的顺序排列,有时候又不是。或者是在SQL2000里是这个顺序,到了SQL2005/2008
324 --又是那个顺序。讲完了merge join大家就能够理解,同样做join,merge就能够按顺序返回
325 --但是nested loops 就不能。只要语句里没有指定“order by”,SQL选取哪一种join并不需要
326 --考虑结果集是否按顺序返回的。他更多考虑的是哪一种join算法代价最小。如果数据量和
327 --数据分布让SQL觉得nested loops划算,他就转用nested loops。结果集就不按顺序返回了
328 --但是SQL并没有做错什么
329 
330 --一句话,如果你想要结果集按照某个顺序返回,就要明确地用“order by”指定。如果你没有
331 --指定,哪怕一模一样的查询,结果集顺序这一次和上一次不一样是很正常的。因为数据
332 --发生变化,或者参数不同,SQL很可能就会选择不同的执行计划
333 
334 
335 
336 
337 
338 --hash join
339 --顾名思义,hash join就是利用哈希算法做匹配的联接算法。哈希算法分成两步:
340 --“build”和“probe”。在“build”阶段,SQL选择两个要做join的数据集中的一个,
341 --根据记录的值建立在一张在内存中的hash表。然后在“probe”阶段,SQL选择
342 --另外一个数据集,将里面的记录值依次带入,找出符号条件,返回可以做联接
343 --的行。具体算法是:
344 
345 FOR each row r1 IN the build TABLE
346 begin
347 calculate HASH VALUE ON r1 JOIN KEY(s)
348 INSERT r1 INTO the appropriate HASH bucket
349 END
350 FOR each row r2 IN the probe TABLE
351 begin
352 calculate HASH VALUE ON r2 JOIN KEY(s)
353 FOR each row r1 IN the CORRESPONDING HASH bucket
354   IF r1 joins WITH r2
355    OUTPUT (r1,r2)
356 
357 END
358 
359 --和其他两种join算法比,hash join的优点是很明显的
360 
361 --1、这对于数据集比较大的join,其复杂度能够控制在合理的范围以内。虽然比merge join
362 --要高一些,但是比nested loops要简单许多
363 
364 
365 --2、他不需要数据集事先按照什么顺序排序,也不要求上面有索引
366 --因为联接使用的是哈希算法,对输入没有限制,不需要SQL像为merge join一样,事先
367 --准备好一个排过序的输入。由于做hash join总是要把两边的数据集都要扫描一遍
368 --所以有没有索引其实帮助也不大。没有索引,对性能也不会有太大影响
369 
370 
371 
372 
373 --3、可以比较容易地升级成使用多处理器的并行执行计划
374 --因为算法没有要求代入的数据有任何次序,所以用多个CPU并行完成是比较容易的
375 --总之,hash join是一种适合于要join的数据集比较大,上面没有合适索引的情况。
376 --像刚才的那个例子,是一个10000条记录的数据集和一个50577条记录的数据集之间
377 --的联接。使用nested loops要循环10000次,代价比较高。SQL预估出来的cost是2.233
378 --使用merge join时,虽然两个数据集都是排序好的,但是由于可能有重复的值,
379 --SQL只好使用many-to-many的join方式,cost也很高,预估是5.882。使用hash join
380 --预估的cost是0.727,比前两个都小。所以如果不代入join hint的话,SQL默认
381 --会对这句话使用hash join
382 
383 --但是,hash join并不是一种最优的join算法,只是SQL在输入不优化(join的数据集
384 --比较大,或上面没有合适索引)的时候的一种不得已的选择。这是因为hash join
385 --是一种最耗资源的join算法。他在做join之前,要先在内存里建立一张hash表
386 --建立的过程需要CPU资源,hash表需要用内存或tempdb存放。而join的过程也要
387 --使用CPU资源来计算(“probe”)。如果同时有很多用户在用hash算法做join,对
388 --SQL的整体负担是比较重的。从降低SQL整体负荷的角度考虑,还是要尽量降低
389 --join输入的数据集的大小,配合合适的索引,引导SQL尽量使用nested loops join
390 --或者merge join
391 
392 
393 --三种join方式比较
394 --                                        nested loops join       
395 --最适合于                           相对较小的两个数据集,inner table在做join的字段上有一个索引
396 --并发性                              能够支持大量的并发用户同时运行                     
397 --join时是否两个字段相等              不要
398 --是否使用内存资源                     不使用
399 --是否使用tempdb                       不使用
400 --输入数据集是否排序                   不要
401 --希望输入数据集排序否                 希望outer input是排序的
402 
403 --                                        merge join       
404 --最适合于                           输入数据集大小中等或较大,并且在join字段上索引帮助排序,或语句要求返回一个排好序的结果集
405 --并发性                             有索引支持的many-to-many的join并发性较好,many-to-many的就差了                   
406 --join时是否两个字段相等              要(除非是full outer join)
407 --是否使用内存资源                     不使用(如果要为merge join做排序,可能要使用)
408 --是否使用tempdb                       many-to-many join要使用
409 --输入数据集是否排序                   要
410 --希望输入数据集排序否                 是的
411 
412 --                                       hash join       
413 --最适合于                           输入数据集较大。尤其适合于data warehouse数据仓库环境下的那些复杂的查询语句
414 --并发性                              最好同时只有少数用户在同时运行                   
415 --join时是否两个字段相等              要
416 --是否使用内存资源                     使用
417 --是否使用tempdb                       使用
418 --输入数据集是否排序                   不要
419 --希望输入数据集排序否                 不要
420 
421 
422 
423 ---在SQL做联接的时候,会按照输入数据集所基于的表格的结构,衡量可能利用的索引,
424 --也根据统计信息,预估两个输入数据集的大小,选择使用三种join方式其中的一种。
425 --如果选得不对,可能会造成join的速度非常慢
426 
427 
428 
429 
430 
431 -------------------其他常见的运算操作-------------------
432 --除了读取数据的运算操作(table scan/seek、clustered index scan/seek、index scan/seek、bookmark lookup等)
433 --以及join运算,SQL常见的其他运算有:
434 
435 --Aggregation 聚合 ETL Extraction-Transformation-Loading的缩写,中文名称为数据提取、转换和加载
436 --主要用来计算sum()、count()、max()、min()、avg()、distinct()等运算函数。aggregation也分两种
437 --Stream Aggregation(将数据集排成一个队列以后做运算)  和Hash Aggregation(类似于hash join,
438 --需要SQL先建立hash表,然后才能做运算)。例如:
439 
440 SET STATISTICS PROFILE ON
441 GO
442 
443 SELECT  MAX([SalesOrderDetailID]) FROM [dbo].[SalesOrderDetail_test]
444 --stream aggregation
445 GO
446 
447 SELECT [SalesOrderID],COUNT([SalesOrderDetailID]) --[SalesOrderDetailID] group by的字段要做聚合
448 from [dbo].[SalesOrderDetail_test]
449 GROUP BY [SalesOrderID]
450 --stream aggregation
451 GO
452 
453 
454 SET STATISTICS PROFILE ON
455 GO
456 SELECT [CustomerID],COUNT(*)
457 FROM [dbo].[SalesOrderHeader_test]
458 GROUP BY [CustomerID]
459 --hash aggregation
460 GO
461 
462 
463 --Union
464 --Union就是把两个数据集合并起来。但是要注意的是,Union有两种:Union ALL和Union
465 --这两种的复杂度是不一样的
466 
467 --Union ALL很简单,就是合并两个集合,不管里面是否有重复的数据。例如:
468 SET STATISTICS PROFILE ON
469 GO
470 
471 SELECT DISTINCT [ProductID],[UnitPrice]
472 FROM [dbo].[SalesOrderDetail_test]
473 WHERE [ProductID]=776
474 UNION ALL
475 SELECT DISTINCT [ProductID],[UnitPrice]
476 FROM [dbo].[SalesOrderDetail_test]
477 WHERE [ProductID]=776
478 
479 
480 --他的DISTINCT() 函数是用hash match做出的aggregation。两个结果集用一个简单的“concatenation”
481 --相连,无须做其他工作
482 
483 --但是Union就不同了。他不但要合并两个数据集,还要把其中重合的数据删除。
484 --所以SQL要把合并以后的结果集进行排序。这是比较昂贵的
485 --例如:
486 SET STATISTICS PROFILE ON
487 GO
488 
489 SELECT DISTINCT([ProductID]),[UnitPrice]
490 FROM [dbo].[SalesOrderDetail_test]
491 WHERE [ProductID]=776
492 UNION
493 SELECT DISTINCT([ProductID]),[UnitPrice]
494 FROM [dbo].[SalesOrderDetail_test]
495 WHERE [ProductID]=776
496 
497 
498 --经过Concatenation后的结果集有8条,其中4条是重复的数据。SQL又做了一个
499 --DISTINCT Order By,把这4条重复的数据去掉
500 
501 --SQL有时候也用hash或者merge join的方式,达到同样合并数据的目的
502 
503 
504 
505 --Parallelism
506 --当SQL发现某个要处理的数据集比较大,cost比较高时,如果SQL服务器有多颗
507 --CPU,SQL就会尝试使用并行的方法,把一个数据集分成若干个,由若干个线程
508 --同时处理,以提高总体效率。这就是通常说的“并行执行计划”
509 
510 --例如图12-42 ,SQL就按照某个字段的值,奇数的交给线程1做,偶数的交给线程2做
511 
512 
513 --还是看这条语句
514 SET STATISTICS PROFILE ON
515 GO
516 SELECT DISTINCT [ProductID],[UnitPrice]
517 FROM [dbo].[SalesOrderDetail_test]
518 WHERE [ProductID]=776
519 GO
520 
521 --SQL用两个线程运行了这个查询。在Parallelism之前(第五行和第四行),
522 --SQL已经为distinct函数作了一次使用sort的aggregation。并行运行的
523 --每个线程各返回了4条记录,总共8条。当并发运行得到的结果合并时,
524 --SQL还要再做一次Distinct Order By,以从这8条记录中再找出distinct
525 --的结果。所以在第二行,结果数从8变成了4
526 
527 
528 
529 --SQL里还有其他操作。如果有兴趣,可以参见《inside microsoft sqlserver2005
530 --query tunning and optimization》一书的第三章

 

posted @ 2013-07-29 08:59 桦仔 阅读(...) 评论(...)  编辑 收藏