笔记57-徐 调整语句设计提高性能

笔记57-徐  调整语句设计提高性能

  1 --调整语句设计提高性能 2013-2-11  P449
  2 
  3 --当一个问题语句,已经排除了系统资源瓶颈、阻塞与死锁、物理I/O、编译与重编译
  4 --参数嗅探这些因素,也发现调整索引或使用计划指南不能达到要求,那怎么办?
  5 --不幸的是,我们已经基本上把SQL上常见的调优方法都介绍了。在有些情况下,一个语句
  6 --的写法决定了他天生是一条复杂的语句,SQL很难使用最优的方法来运行他。这时候
  7 --调整SQLSERVER,可能效果都不会很明显。用户要想想,如果这个问题更多的是由语句
  8 --本身导致的话,那调整语句设计是不是更好的解决方法。有时候,可能是解决问题
  9 --唯一的选择
 10 
 11 --常见的语句优化方法:
 12 
 13 --1、筛选条件与计算字段
 14 --筛选条件的写法是有讲究的。最好能够使用SARG的运算符包括=、>、<、>=、<=、in、between、like
 15 --有时还包括like(在前缀匹配时,如like'john%')SARG可以包括由and联接的多个条件。SARG不但可以是匹配
 16 --特定值的查询,例如
 17 CustomerID=ANTON
 18 DOE=last name
 19 
 20 --还可以是匹配一定范围的值的查询,例如
 21 order date>'1/1/2002'
 22 order date>'1/1/2002' and order date<'1/1/2009'
 23 doe in('anton','about')
 24 
 25 
 26 --对于不使用SARG运算符的表达式,SQL对它们很难使用比较优化的做法,很可能就不使用
 27 --索引。
 28 
 29 --非SARG运算符包括 not、<>、not exists、not in、not like和内部函数,例如
 30 --convert、upper等。下面的查询,就不会使用在Production.Product.Name字段上
 31 --的索引 非聚集索引
 32 
 33 --三个都是使用索引扫描
 34 USE [AdventureWorks]
 35 GO
 36 SET STATISTICS PROFILE ON
 37 SET STATISTICS TIME ON
 38 SELECT [Name] FROM [Production].[Product]
 39 WHERE [Name] LIKE'%Deca%'  --不是前缀匹配,所以使用索引扫描
 40 GO
 41 -----------------------------------------------
 42 USE [AdventureWorks]
 43 GO
 44 SET STATISTICS PROFILE ON
 45 SET STATISTICS TIME ON
 46 SELECT [Name] FROM [Production].[Product]
 47 WHERE [Name] NOT LIKE'%Deca%'
 48 GO
 49 ------------------------------------------------
 50 USE [AdventureWorks]
 51 GO
 52 SET STATISTICS PROFILE ON
 53 SET STATISTICS TIME ON
 54 SELECT [Name] FROM [Production].[Product]
 55 WHERE LEFT([Name],4)='Deca'
 56 GO
 57 
 58 --另外,如果要对字段先进性计算,再比较,也会妨碍索引的使用。例如
 59 USE [AdventureWorks]
 60 GO
 61 SET STATISTICS PROFILE ON
 62 SELECT [Name] FROM [Production].[Product]
 63 WHERE [Name]+'_end'='Decal 1_end'
 64 
 65 ---下面这样才会使用索引查找
 66 USE [AdventureWorks]
 67 GO
 68 SET STATISTICS PROFILE ON
 69 SELECT [Name] FROM [Production].[Product]
 70 WHERE [Name]='Decal 1'
 71 
 72 --在写语句的时候,要尽量避免这些写法。有时候,程序里会有很多过滤是要通过
 73 --先计算字段值再进行的。假如,我们找产品的时候,代入的都是大写的字母,
 74 --做比较的时候需要先将“name”字段转成大写,再比较。这时候很自然的,语句
 75 --就是:
 76 SELECT [Name] FROM [Production].[Product]
 77 WHERE UPPER([Name])='Decal 1'
 78 
 79 --他是不会使用索引seek的。如果程序里的许多语句都是这样,那SQL的性能是不会很好的。
 80 --解决的办法,可以是在表格里再加一个字段,专门存放UPPER([Name])以后的结果。然后
 81 --在这个新字段上建立索引。新插入的字段可以是一个计算字段(computed column)这样
 82 --甚至insert语句都不用太多改动
 83 
 84 --例如:我们可以这样修改Production.Product表
 85 USE [AdventureWorks]
 86 GO
 87 ALTER TABLE [Production].[Product] ADD UpperName AS UPPER([Name]) PERSISTED --带有Persisted值的计算列 会存储实际数据
 88 --http://database.ctocio.com.cn/dbzjdysummary/48/8730048_4.shtml
 89 --如果Persisted属性被关掉了,那么计算列只是虚拟列(书本是没有加PERSISTED关键字的)。该列将没有数据存储到磁盘上,并且这些值每次在一个脚本中参照时都会被计算。如果这个属性被设置成激活的,那么计算列的数据将会存储在磁盘中。
 90 --加入一个计算字段,其值直接从UPPER([Name])算出
 91 GO
 92 
 93 CREATE NONCLUSTERED INDEX AK_Product_UName ON [Production].[Product](uppername)
 94 --在上面创建一个非聚集索引
 95 GO
 96 
 97 USE [AdventureWorks]
 98 GO
 99 SELECT UpperName
100 FROM [Production].[Product]
101 WHERE UpperName='DECAL 1'
102 --改语句查询UpperName字段,会使用他上面的索引
103 GO
104 
105 --删除
106 ALTER TABLE [Production].[Product] DROP COLUMN UpperName
107 DROP INDEX AK_Product_UName ON [Production].[Product]
108 
109 
110 
111 --一些表格里有时间字段,而语句经常按年、月查询的时候,也可以使用这种方法
112 --引入计算字段,事先把计算的值存储在索引数据结构里。这样每次查询的时候
113 --就不用再算一遍了
114 
115 --不过,不是所有的计算字段上都能加索引。有些定义,可能每次运行返回值会
116 --不一样non-deterministic。例如,根据employee的生日算年龄,值就会随
117 --当前的时间而发生不断的变化。
118 
119 USE [AdventureWorks]
120 GO
121 ALTER TABLE [HumanResources].[Employee] ADD age AS DATEDIFF(yy,GETDATE(),[BirthDate])
122 GO
123 
124 CREATE NONCLUSTERED INDEX AK_Employee_Age ON [HumanResources].[Employee](age)
125 GO
126 
127 
128 --对这样的语句,查询的修改可能得换一个思路.比如,我想找年龄大于30岁的员工,
129 --可以写成
130 USE [AdventureWorks]
131 GO
132 SELECT * FROM [HumanResources].[Employee] WHERE DATEDIFF(yy,[BirthDate],GETDATE())>30
133 
134 --但是这样性能会有问题,不能使用索引,如果写成:
135 USE [AdventureWorks]
136 GO
137 SELECT * FROM [HumanResources].[Employee] WHERE [BirthDate] <DATEADD(yy,-30,GETDATE())
138 
139 --SQL就有办法使用索引了。两种写法得到的结果很近似,但是第二种对SQL性能比较有利
140 
141 --2、会在运行前改变值的变量
142 --在谈到参数嗅探的时候,提到过SQL在编译的时候,对存储过程代入的变量,SQL是
143 --知道他的值的,也会根据他的值对语句进行优化。但是如果在语句使用他之前,被
144 --其他语句修改过,那SQL生成的执行计划就不准了。这种情况,有时也会导致性能
145 --问题
146 
147 --例如,下面这个存储过程,@date是他代入的参数。SQL会根据参数的值,生成执行计划
148 USE [AdventureWorks]
149 GO
150 CREATE PROCEDURE GetRecentSales(@date DATETIME)
151 AS
152 BEGIN
153 SELECT SUM(d.[OrderQty])
154 FROM [dbo].[SalesOrderHeader_test] h,[dbo].[SalesOrderDetail_test] d
155 WHERE h.[SalesOrderID]=d.[SalesOrderID]
156 AND h.[OrderDate]>@date
157 END
158 ----------------------------------------------------------------------
159 EXEC [sys].[sp_recompile] @objname = N'GetRecentSales' -- nvarchar(776)
160 GO
161 DBCC freeproccache
162 GO
163 SET STATISTICS PROFILE ON
164 GO
165 EXEC GetRecentSales NULL
166 --预估结果集很小,会使用nested loops
167 GO
168 
169 EXEC [sys].[sp_recompile] @objname = N'GetRecentSales' -- nvarchar(776)
170 GO
171 DECLARE @date DATETIME
172 SET @date=DATEADD(mm,-3,(SELECT MAX([OrderDate]) FROM [dbo].[SalesOrderHeader_test]))
173 SET STATISTICS PROFILE ON
174 EXEC GetRecentSales @date
175 --预估结果集比较大,会使用hash match
176 GO
177 
178 
179 
180 --但是如果我们把存储过程改成下面这个样子:
181 USE [AdventureWorks]
182 GO
183 ALTER PROC GetRecentSales ( @date DATETIME )
184 AS
185     BEGIN
186         IF @date IS NULL
187             SET @date = DATEADD(mm, -3,
188                                 ( SELECT    MAX([OrderDate])
189                                   FROM      [dbo].[SalesOrderHeader_test]
190                                 ))
191      --如果是null值,会代入一个新的日期
192         SELECT  SUM(d.[OrderQty])
193         FROM    [dbo].[SalesOrderHeader_test] h ,
194                 [dbo].[SalesOrderDetail_test] d
195         WHERE   h.[SalesOrderID] = d.[SalesOrderID]
196                 AND h.[OrderDate] > @date
197     END
198 
199 -------------------------------------------------------------
200 EXEC [sys].[sp_recompile] @objname = N'GetRecentSales' -- nvarchar(776)
201 GO
202 DBCC freeproccache
203 GO
204 SET STATISTICS PROFILE ON
205 EXEC GetRecentSales NULL
206 GO
207 
208 --我们再用null值来运行,会发现SQL没办法感知到值发生了变化,还是使用了
209 --nested loop完成了查询。这个执行计划不是最优的
210 
211 --怎麽来解决这个问题呢?当然,你可以在使用变量的语句后面加一个
212 --option(recompile)的query hint。这样当SQL运行到这句话的时候,
213 --会重编译可能出问题的语句。在那个时候,就能根据修改过的值
214 --生成更精确的执行计划了
215 
216 USE [AdventureWorks]
217 GO
218 ALTER PROC GetRecentSales ( @date DATETIME )
219 AS
220     BEGIN
221         IF @date IS NULL
222             SET @date = DATEADD(mm, -3,
223                                 ( SELECT    MAX([OrderDate])
224                                   FROM      [dbo].[SalesOrderHeader_test]
225                                 ))
226      --如果是null值,会代入一个新的日期
227         SELECT  SUM(d.[OrderQty])
228         FROM    [dbo].[SalesOrderHeader_test] h ,
229                 [dbo].[SalesOrderDetail_test] d
230         WHERE   h.[SalesOrderID] = d.[SalesOrderID]
231                 AND h.[OrderDate] > @date
232         OPTION(recompile)
233     END
234    
235 --还有一种方法,是把可能出问题的语句单独做成一个子存储过程,让原来
236 --的存储过程调用子存储过程,而不是语句本身。例如
237 
238 USE [AdventureWorks]
239 GO
240 CREATE PROCEDURE GetRecentSalesHelper(@date DATETIME)
241 AS
242 BEGIN
243         SELECT  SUM(d.[OrderQty])
244         FROM    [dbo].[SalesOrderHeader_test] h ,
245                 [dbo].[SalesOrderDetail_test] d
246         WHERE   h.[SalesOrderID] = d.[SalesOrderID]
247                 AND h.[OrderDate] > @date
248                
249 END
250 
251 
252 ALTER PROC GetRecentSales ( @date DATETIME )
253 AS
254     BEGIN
255         IF @date IS NULL
256             SET @date = DATEADD(mm, -3,
257                                 ( SELECT    MAX([OrderDate])
258                                   FROM      [dbo].[SalesOrderHeader_test]
259                                 ))
260         EXEC GetRecentSalesHelper @date
261     END    
262    
263    
264 --这样做的好处,是可以省下语句重编译的时间.两种方法,各有好处.可以根据
265 --实际情况做选择
266 
267 
268 --3、临时表和表变量
269 --不知道大家有没有注意到,SQL里有两种对象可以暂时存放表结构的数据
270 --一种就是大家很熟悉的临时表(temp table),另一种是名气小一点
271 --,是表变量(table variable)。这两种对象功能类似,差异不太明显
272 --功能上比较大的差别是,表变量可以作为存储过程的返回参数,而
273 --临时表不行
274 
275 --那是不是用表变量就可以了,为什麽SQL还要保留临时表这个功能呢?
276 --其实这两个对象在内部实现上还是有很大区别的。
277 
278 
279 --最显著的区别:
280 
281 --SQL会像对普通表一样,在临时表上维护统计信息,用户也可以在上面建立
282 --索引。而表变量上,既不能建立索引,也不会有统计信息。SQL在做执行
283 --计划的时候,总是认为表变量里的数据量只有很少的几行
284 
285 --现在来做一个测试体会一下
286 
287 --表变量情形
288 USE [AdventureWorks]
289 GO
290 DECLARE @tmp TABLE(ProductID INT,OrderQty INT)
291 INSERT INTO @tmp
292 SELECT [ProductID],[OrderQty]
293 FROM [dbo].[SalesOrderDetail_test]
294 WHERE [SalesOrderID]=75124
295 --语句会插入12万条记录
296 
297 SET STATISTICS PROFILE ON
298 SELECT p.[Name],p.[Color],SUM(t.[OrderQty])
299 FROM @tmp t
300 INNER JOIN [Production].[Product] p
301 ON t.[ProductID]=p.[ProductID]
302 GROUP BY p.[Name],p.[Color]
303 ORDER BY p.[Name]
304 --含有12万条记录的表变量和另一张表做join
305 GO
306 
307 
308 --从执行计划里可以看到,SQL认为表变量只会返回1行,所以选择了nested loops。
309 --在这里是不太合适的
310 
311 
312 --临时表情形
313 USE [AdventureWorks]
314 GO
315 CREATE  TABLE #tmp(ProductID INT,OrderQty INT)
316 INSERT INTO #tmp
317 SELECT [ProductID],[OrderQty]
318 FROM [dbo].[SalesOrderDetail_test]
319 WHERE [SalesOrderID]=75124
320 --语句会插入12万条记录
321 
322 SET STATISTICS PROFILE ON
323 SELECT p.[Name],p.[Color],SUM(t.[OrderQty])
324 FROM #tmp t
325 INNER JOIN [Production].[Product] p
326 ON t.[ProductID]=p.[ProductID]
327 GROUP BY p.[Name],p.[Color]
328 ORDER BY p.[Name]
329 --含有12万条记录的表变量和另一张表做join
330 GO
331 DROP TABLE [#tmp]
332 GO
333 
334 --在 SQL Trace里会看到Auto Stats -Created 事件
335 
336 --和表变量很不相同的是,SQL在insert语句之后,select语句之前,触发了
337 --一个自动创建统计信息auto stats created事件。建立了统计信息以后
338 --SQL就知道临时表里有很多数据了。join的方式,因此改用了merge join
339 --性能比前一种好很多
340 
341 --所以表变量的好处是,他的维护成本很低,大量并发使用时对系统的负担
342 --比临时表要低。但是缺点是没有统计信息,存放大量的数据时性能很难保证。
343 --所以,表变量比较适合存放一些很小(几十行或更小)的结果集
344 
345 --临时表的好处是,他的功能和普通用户表接近,能够为大数据集做优化
346 --但是缺点是维护成本高。大量并发使用临时表,会对系统带来比较重
347 --的负荷。所以临时表比较适合存放一些大的结果集
348 
349 --在设计数据库应用,尤其是OLTP这样性能很敏感的应用时,要根据实际情况
350 --作出合理选择
351 
352 
353 
354 
355 --4、尽可能限定语句的复杂度
356 --这是一个人人皆知的道理。如果语句不太复杂,当然性能会好。如果语句非常复杂
357 --当然开销会大,恨呐调快。可是,为了支持业务逻辑,还是常常在SQL里看到
358 --非常复杂的语句。这为调优带来了很大麻烦。有时候简直是无法可想,除非
359 --是对应用设计做大手术
360 
361 
362 --这里列举出一些容易产生复杂语句的情形。在大家设计应用的时候,要小心使用
363 
364 --(1)动态语句
365 --一些应用为了实现客户端的灵活性,会根据用户的选择,动态拼出TSQL语句,发给SQL
366 --运行。例如,在用户界面上列出各种条件,让用户根据自己的喜好,输入条件,进行
367 --组合查询。这样在功能上来讲比较强大,但是在复杂度控制上就有可能会出问题。
368 --如果用户选择的条件太多,或者根据条件返回的记录太多,就有可能会造成问题。
369 --而有些能够过滤大量数据,或者在索引上的条件如果没有被选上,就有可能造成在
370 --大表上的table scan。最好在程序里有动态语句复杂度的控制机制,限制选择的条件
371 --限制返回记录的数量
372 
373 
374 
375 --(2)表格联接的数量
376 --为了支持复杂的业务逻辑,一个应用往往会有成百上千的表格,一些查询往往会联接
377 --十几张甚至几十张表。应用设计的时候对这样的查询要很慎重。如果表格很大,十几张
378 --表做联接,肯定不会有好的性能。如果应用是支持数据分析系统,那可能还好。如果
379 --应用是一个OLTP系统,这样的设计失败的风险可能会很大。有时候可能需要降低
380 --数据库范式级别,多保存一些冗余数据列,以减少表格联接的数量
381 
382 
383 
384 
385 --(3)视图和存储过程的深度
386 --视图和存储过程能够抽象出一些业务逻辑,简化设计,是很推荐的做法。但是如果
387 --在引用视图和存储过程时不加注意,视图套视图,存储过程嵌存储过程,最后
388 --嵌套上四五层,那复杂度累积起来,可能会超出你想象。对SQL的优化,也是很
389 --严重的考验。所以在引用他们的时候,也要考虑累积的复杂度
390 
391 
392 
393 --(4)不必要的排序和计算
394 --对一个大结果集做排序,或者求唯一值,都是比较昂贵的计算,会占用大量系统资源
395 --如果用户对结果集排序或唯一性的要求不高,可以适当去掉这些计算
396 
397 
398 
399 
400 --(5)超大结果集申请和返回
401 --如果根据用户选择的过滤条件,SQL会返回十几万条记录,那应用层该如何处理?
402 --如果一次性返回给应用层,那应用层要缓存和处理这么多记录,自己的性能
403 --会受到很大的挑战。如果一次只取一部分记录,其他记录由SQL代为缓存
404 --(一般是应用服务器端游标),那不但会给SQL的内存使用带来负担,而且
405 --容易产生阻塞问题。如果应用层处理得不好,甚至会产生内存泄漏的问题。
406 --所以程序设计的时候,要确保应用只会申请合适的、有必要的结果集。
407 --例如一个用户在网页上查询他感兴趣的产品,可能最多只会看前面的
408 --100个。如果你返回一万一个产品记录给他,除了暗示你产品多以外,
409 --对用户没有任何意义。这时候在语句里设置一个top 100,可能是
410 --个合理的选择
411 
412 
413 
414 
415 --(6)用多个简单语句替代一个复杂语句
416 --如果一个复杂的语句有很多张表要联接,要做很多计算,很多时候,要
417 --根据表和表的逻辑关系,知道某一张表和另一张表如果先做联接,
418 --可能会过滤掉更多数据。得到的小的结果集再做其他联接,会更快
419 --类似的,有些计算可以先做,也可以后做,人在了解了表格的逻辑之后
420 --会知道是先做好还是后做好。可惜SQL作为一个计算机程序,在这方面
421 --没有人那么聪明。当语句太复杂的时候,他有可能看不出来了。为了
422 --提高性能,对这种特别复杂的语句,可以把一句话拆成两句,甚至三句
423 --分步做完,中间结果集,可以以临时表的形式存放。这样做对程序员来
424 --讲做了很多事,但是对SQL来讲,大大简化了复杂度。很多时候对性能
425 --也会有帮助

 

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