笔记62-徐 统计信息的含义与作用 P363

笔记62-徐  统计信息的含义与作用 P363 

  1 --统计信息的含义与作用 P363 2012-12-22
  2 
  3 --为了以尽可能快的速度完成语句,光有索引是不够的。对于同一句话,SQL
  4 --有很多种方法来完成他。有些方法适合于数据量比较小的时候,有些方法适合于
  5 --数据量比较大的时候。同一种方法,在数据量不同的时候,复杂度会有
  6 --非常大的差别。索引只能帮助SQL找到符合条件的记录。SQL还需要知道
  7 --每一种操作所要处理的数据量有多少,从而估算出复杂度,选取一个
  8 --代价最小的执行计划。说得通俗一点,SQL要能够知道数据是“长得什么样”的
  9 --才能用最快方法完成指令
 10 
 11 --SQL不像人,光看看数据就能够大概心理有数。怎麽能让SQL知道数据的分布信息呢?
 12 --在数据库管理系统里有个常用的技术,就是数据“统计信息(statistics)”
 13 --SQL是通过他了解数据的分布情况的
 14 
 15 --可以先来看刚才的两张范例表在SalesOrderID这个字段上的统计信息,以便对
 16 --这个概念有点直观认识
 17 
 18 --dbo.SalesOrderHeader_test保存的是每张订单的概要信息,一张订单只会有一条记录
 19 --所以SalesOrderID是不会重复的。现在这张表里,应该有31474条记录。
 20 --SalesOrderID是一个int型的字段,所以字段长度是4。运行
 21 DBCC SHOW_STATISTICS(tablename,INDEX OR STATISTICS name)
 22 
 23 DBCC SHOW_STATISTICS([SalesOrderHeader_test],SalesOrderHeader_test_CL)
 24 
 25 --统计信息内容分3部分
 26 --1、统计信息头信息
 27 --列名                          说明
 28 --name                     统计信息的名称,这里就是索引的名字
 29 --updated                  上一次更新统计信息的日期和时间。这里是12 18 2012  1:16AM
 30 --                         这个时间非常重要,根据他能够判断统计信息是什么时候更新的
 31 --                         是不是在数据量发生变化之后,是不是存在统计信息不能反映当前
 32 --                         数据分布特点的问题
 33 
 34 --rows                     表中的行数。这里是31465行,不能完全完全正确地反映了当前表里数据量
 35 --rows sampled             统计信息的抽样行数这里也是31465,说明上次SQL更新统计信息
 36 --                         的时候,对整个表里所有记录的SalesOrderID字段,都扫描了一遍
 37 --                         ,这样做出来的统计信息一般都是很精确的
 38 
 39 --steps                    在统计信息的第三部分,会把数据分成几组,这里是3组
 40 
 41 --density                  第一个列前缀的选择性(不包括EQ_ROWS)
 42 
 43 --average key length       所有列的平均长度,因为SalesOrderHeader_test_CL索引只有一列
 44 --                         数据类型是int,所以长度是4(单位是字节),如果索引有多个列,每个列的数据类型
 45 --                         都不一样,比如再有一个列colc char(10) 那么平均长度是(10+4)/2=7
 46 
 47 --string index             如果为“是”,则统计信息中包含字符串摘要索引,以支持为LIKE条件
 48 --                         估算结果集大小。仅适用于char,varchar,nchar和nvarchar,varchar(max)
 49 --                         nvarchar(max),text,ntext 数据类型的前导列。这里是int,所以这个值
 50 --                         是“NO”
 51 
 52 
 53 
 54 --2、数据字段的选择性
 55 --列名                                说明
 56 --all density              反映索引列的选择性(selectivity)
 57 --                         "选择性"反映数据集里重复的数据量是多少,或者反过来说,值唯一的数据量
 58 --                         有多少。如果一个字段的数据很少有重复,那么他的可选择性就比较高。比如
 59 --                         身份证号,是不可重复的。哪怕对整个中国的身份记录做查询,代入一个身份证号码
 60 --                         最多只会有一条记录返回,在这样的字段上的过滤条件,能够有效地过滤掉大量数据
 61 --                         返回的结果集会比较小
 62 --                         举个相反的例子:性别。所有人只有两种,非男即女。这个字段上的重复性就很高
 63 --                         选择性就很低。一个过滤条件,最多只能过滤掉一半的记录
 64 --                         SQL通过计算“选择性”,使得自己能够预测一个过滤条件做完后,大概能有多少记录
 65 --                         返回
 66 --                         Density的定义是
 67 --                         density = 1/cardinality of index keys
 68 --                         如果这个值小于0.1,一般讲这个索引的选择性比较高,如果大于0.1,他的选择性
 69 --                         就不高了。这里[SalesOrderHeader_test]有31474条没有重复的记录
 70 --                         1/31474 = 3.177e-5 这个字段的选择性是不错的
 71 
 72 --average length           索引列的平均长度,这里还是4
 73 
 74 --columns                  索引列的名称,这里是字段名 SalesOrderID
 75 
 76 
 77 --从这一部分的信息,可以推断出统计信息所关心的字段的长度,以及他有多少条唯一值。但是这些信息
 78 --对SQL预测结果集复杂度还不够。比如我现在要查一个SalesOrderID=60000的订单,还是不知道会有
 79 --多少记录返回。这里需要第三部分的信息
 80 
 81 
 82 --3、直方图(histogram)
 83 --列名                                   说明
 84 --range_hi_key                直方图里每一组(step)数据的最大值
 85 --                            订单号的最小号码在表格里是43659,这里SQL选择他作为第一个step
 86 --                            的最大值,3组数据分别是 ~43659  43660~75131   75132~75132
 87 --range_rows                  直方图里每组数据区间行数,上限值除外 第一组只有一个数:43659
 88 --                            第三组也只有一个数:75132,其他数据都在第二组里,区间里有31471
 89 --                            个数
 90 --EQ_ROWS                     表中值与直方图每组数据上限值相等的行数目 这里都是1
 91 --distinct_range_rows         直方图里每组数据区间非重复值的数目,上限值除外
 92 --                            由于这个字段没有重复值,所以这里 就等于range_rows的值
 93 --avg_range_rows              直方图里每组数据区间内重复值的平均数目,上限值除外。计算公式
 94 --                            (range_rows/distinct_range_rows for distinct_range_rows>0)
 95 --                            这里distinct_range_rows的值就等于range_rows的值,所以avg_range_rows
 96 --                            等于1
 97 
 98 
 99 --有这麽一个直方图,就能够很好地知道表格里的数据分布了。在SalesOrderID这个字段里,最小
100 --值是43659,最大值是75132,在这个区间里有31473个值,而且没有重复值,所以可以推算出表
101 --里的值就是从43659开始到75132结束的每个int值。SQL没有必要存储很多step的信息,只要
102 --这3个step,就能够完全表达数据分布
103 
104 
105 --这样如果查询代入的条件是在43659~75132之间的值。那么SQL知道会返回一行。如果不在这个区间
106 --,就不会有行返回。而且返回的每一行长度,都是4。所以通过统计信息,SQL能够比较好地预测
107 --返回的结果集的行数和长度
108 
109 
110 --这里要说明两点的是:
111 --(1)如果一个统计信息是为一组字段建立的,例如一个复合索引建立在两个以上的字段上,SQL
112 --维护所有字段的选择性信息,但是只会维护第一个字段的直方图。这一点,稍后会举例解释
113 
114 
115 
116 --(2)当表格比较大的时候,SQL在更新统计信息的时候为了降低消耗,只会取表格的一部分数据
117 --做抽样(rows sample),这时候统计信息里面的数据都是根据这些抽样数据估算出来的值
118 --可能和真实值会有些差异
119 
120 
121 --统计信息越细致,当然会越精确,但是维护统计信息要付出的额外开销也就越大。有可能
122 --提高统计信息精确度所带来的执行性能的提升还抵消不了维护统计信息成本的增加。
123 --SQL做这样的设计,不是因为其能力有限,而是为了谋求一个对大多数情况都合适的平衡
124 
125 
126 
127 --刚才看的索引SalesOrderHeader_test_CL数据分布比较简单。现在来看一个稍微复杂一点的索引
128 --SalesOrderDetail_test_NCL
129 
130 --为dbo.SalesOrderDetail_test这张表造数据的时候,做得比较特别,他的前10%的数据,属于
131 --编号43659到75132这3万多条订单,而后90%的数据,平均属于75124到75132这9张订单。这样的
132 --数据分布,SQL的统计信息是如何表示的呢?
133 
134 
135 DBCC SHOW_STATISTICS([SalesOrderDetail_test],SalesOrderDetail_test_NCL)
136 
137 --这个统计信息和SalesOrderHeader_test_CL有很多不同
138 --(1)这里的数据分组(step)有190个,要详细很多
139 
140 --(2)在第2部分density,不但有索引列SalesOrderID的选择性值,还有
141 --SalesOrderID+SalesOrderDetailID合并起来的选择性值。可以看出如果同时
142 --使用两个字段进行过滤,其选择性(3.177226E-05)会比只使用SalesOrderID
143 --(3.18E-05)还要高
144 
145 --(3)直方图只有SalesOrderID的信息,没有SalesOrderDetailID的信息。从直方图
146 --的各项值分布情况,可以清楚地看出[SalesOrderDetail_test]这张表的数据分布特点
147 --SQL能够根据代入的SalesOrderID值,推断出是只有几条,几十条记录返回
148 --(当SalesOrderID在43659~75132之间),还是会12万条数据返回
149 --(当SalesOrderID在75124~75132之间)
150 
151 
152 --所以下面这两段代码虽然结构一模一样,但是因为参数值不同,SQL选择了不同的执行计划
153 --这是因为SQL知道,前一个参数只返回3行(SET STATISTICS PROFILE ON 里的EstimateRows列)
154 --而后面一个会返回121317行。这里SQL“猜”得是完全正确的
155 SET STATISTICS PROFILE ON
156 SELECT
157 b.[SalesOrderID],b.[OrderDate],
158 a.*
159 FROM  [dbo].[SalesOrderDetail_test] a
160 INNER JOIN [dbo].[SalesOrderHeader_test] b
161 ON a.[SalesOrderID]=b.[SalesOrderID]
162 WHERE b.[SalesOrderID]=72642
163 
164 
165 
166 SET STATISTICS PROFILE ON
167 SELECT
168 b.[SalesOrderID],b.[OrderDate],
169 a.*
170 FROM [dbo].[SalesOrderDetail_test] a
171 INNER JOIN [dbo].[SalesOrderHeader_test] b
172 ON a.[SalesOrderID]=b.[SalesOrderID]
173 WHERE b.[SalesOrderID]=75127
174 
175 
176 
177 
178 --------------------统计信息的维护和更新------------------------------
179 --当SQL需要去估算某个操作的复杂度时,他必定要试图去寻找相应的统计信息做支持。
180 --DBA无法预估SQL会运行什么样的操作,所以也无法预估SQL可能需要什么样的统计信息
181 --如果靠人力来建立和维护统计信息,那将是一个非常复杂的工程。好在SQL不是这样设计的
182 --在绝大多数情况下,SQL自己会很好地维护和更新统计信息,用户基本没有感觉,DBA
183 --也没有额外的负担。
184 
185 --这主要是因为在SQL 数据库属性里,有两个默认打开的设置
186 --auto create statistics 自动创建统计信息和auto update statistics自动更新统计信息。
187 --他们能够让SQL在需要的时候自动建立要用到的统计信息,也能在发现统计信息过时的时候,
188 --自动去更新他
189 
190 
191 --SQL会在什么情形下创建统计信息呢?主要有3种情况
192 --(1)在索引创建时,SQL会自动在索引所在的列上创建统计信息
193 --所以从某种角度讲,索引的作用是双重的,他自己能够帮助SQL快速找到数据,而他
194 --上面的统计信息,也能够告诉SQL数据的分布情况
195 
196 
197 
198 --(2)DBA也可以通过之类的语句手动创建他认为需要的统计信息
199 CREATE STATISTICS
200 --如果打开了auto create statistics,一般来讲很少需要手动创建
201 
202 
203 --(3)当SQL想要使用某些列上的统计信息,发现没有的时候,“auto create statistics”
204 --会让SQL自动创建统计信息
205 --例如,当语句要在某个(或者几个)字段上做过滤,或者要拿他们和另外一张表做联接(join)
206 --SQL要估算最后从这张表会返回多少记录。这时候就需要一个统计信息的支持。如果没有,
207 --SQL会自动创建一个
208 
209 
210 
211 --我们可以在dbo.SalesOrderHeader_test上试试
212 EXEC [sys].[sp_helpstats] SalesOrderHeader_test
213 GO
214 
215 --返回表格没有statistics(索引上的除外)
216 USE [AdventureWorks]
217 GO
218 
219 SELECT COUNT(*) FROM [dbo].[SalesOrderHeader_test]
220 WHERE [OrderDate]='2004-06-11 00:00:00.000'
221 GO
222 
223 
224 --运行一句在OrderDate上有过滤条件的查询
225 
226 EXEC [sys].[sp_helpstats] SalesOrderHeader_test
227 GO
228 
229 
230 --返回表格已经有了一个新的统计信息
231 --statistics_name               statistics_keys
232 --_WA_Sys_00000003_405A880E       OrderDate
233 
234 
235 --因此,在打开“auto create statistics”的数据库上,一般不需要担心SQL没有足够的统计
236 --信息来选择执行计划。这一点完全交给SQL管理就可以了
237 
238 --SQL不仅要建立合适的统计信息,还要及时更新他们,使他们能够反映表格里数据的变化
239 --数据的插入、删除、修改都可能会引起统计信息的更新。但是,更新统计信息本身也是
240 --一件消耗资源的事情,尤其是对比较大的表格。如果有一点点小的修改SQL都要去更新
241 --统计信息,可能SQL就得光忙活这个,来不及做其他事情了。SQL还是要在统计信息的
242 --准确度和资源合理消耗之间做一个平衡。在SQL2005/SQL2008,触发统计信息自动更新
243 --的条件是:
244 
245 --(1)如果统计信息是定义在普通表格上,那么当发生下面变化之一后,统计信息就
246 --被认为是过时的了。下次使用到时,会自动触发一个更新动作
247 --分离数据库的时候,也可以手动选择是否更新统计信息
248 --1、表格从没有数据变成有大于等于1条数据
249 --2、对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后
250 --3、对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于
251 --500+(20%*表格数据总量)以后。所以对于比较大的表,只有1/5以上的数据发生变化后
252 --SQL才会去重算统计信息
253 
254 
255 
256 --(2)临时表(temp table)上可以有统计信息。其维护策略基本和普通表一致。
257 --但是表变量(table variable)上不能建立统计信息
258 
259 
260 
261 --这样的维护策略能够保证花费比较小的代价,确保统计信息基本正确。本节后会有一个
262 --案例,反映这个维护策略在数据分布特殊的表上,也有可能造成一些负面影响
263 
264 
265 --在SQL2000的时候,如果SQL在编译一个语句时发现某个表的某个统计信息已经过时,他
266 --会暂停语句的编译,转去更新统计信息,等统计信息更新好以后,用新的信息来做执行
267 --计划。这样的方法当然能够帮助得到一个更准确的执行计划,但是缺点是语句执行要
268 --等统计信息更新完毕。这个过程有点费时。在大部分情况下,语句执行效率对统计信息
269 --没有那么敏感。如果用老的统计信息也能做出比较好的执行计划,这里的等待就白等了
270 
271 
272 --所以在SQL2005以后,数据库属性多了一个“auto update statistics asynchronously”
273 --当SQL发现某个统计信息过时时,他会用老的统计信息继续现在的查询编译,但是会在
274 --后台启动一个任务,更新这个统计信息。这样下一次统计信息被使用到时,就已经是
275 --一个更新过的版本。这样做的缺点是,不能保证当前这句查询的执行计划准确性。
276 --凡事有利有弊,DBA可以根据实际情况做选择
277 
278 
279 --当然,的确有一些例外情况。由于数据的特殊性,会使得SQL这种auto update statistics
280 --的算法不能满足确保执行计划准确性的需求。在实际使用中,有时候数据库的性能突然
281 --之间慢下来。有经验的DBA会安排做一次索引重建任务,常常对性能有所帮助。通常人们
282 --会解释为:因为索引重建消除了数据碎片,因而提高了性能。其实索引重建还做了另外
283 --一件很重要的工作。他使用full scan的方式,重新更新了表上的统计信息,使得统计
284 --信息非常精确。这对性能帮助作用会很大
285 
286 
287 
288 --下面介绍一个非常有意思的案例,问题原因就和auto update statistics的算法有直接关系
289 
290 
291 ----------------------------案例分析------------------------------------------
292 
293 --在一个客户的OLTP系统里,有一句非常重要的查询语句,直接关系到系统整体性能。
294 --在某一天(我们假设是2007-06-05),查询突然变得非常慢。客户的DBA还是比较
295 --有经验的。他确定了系统性能问题是由这一语句导致的以后,备份了一下数据库
296 --然后在用户比较少的时候做了一个全库的索引重建。索引重建完成后,发现查询
297 --语句的性能恢复正常
298 
299 
300 --现在我们就要来解释,为什麽突然语句会突然变慢,为什麽索引重建又能使他恢复正常
301 
302 --由于出现问题时,手里有一份重建索引前的数据库备份,使得我们能够通过恢复数据库
303 --备份的方法来重现问题,分析问题产生原因。恢复数据库完整备份能够把数据库恢复到
304 --和当时一模一样的状态,包括数据页分配、统计信息等。所以笔者在一台测试机上恢复
305 --了备份,运行语句就能重现问题。
306 
307 
308 --通过分析语句执行计划和执行统计信息,将问题定位到了两个表的联接结果上
309 
310 --两个表通过下面的字段联接
311 --st_ord_order.ord_trade_date=st_mkt_market.mkt_curr_trade_date
312 
313 --问题出现时,SQL预测返回行数是1行EstimateRows,而实际返回了9653行Rows。
314 --在重建索引后,SQL变得能够精确预测返回行数,9655.5行,和实际返回值非常接近
315 --由于返回行数预测不准确,SQL在选择执行计划的时候犯了一个一系列错误,导致
316 --选择的执行计划很不合理,最后运行时间慢了非常多
317 
318 
319 
320 --理解为什麽SQL会犯这样的错误,需要在数据库上运行下面两条指令,看看SQL选择用来做
321 --联接的两个索引上的统计信息是什么样子的。这两个索引都建立在要做联接的字段上
322 --(ORD_TRADE_DATE和MKT_CURR_TRADE_DATE)
323 
324 DBCC SHOW_STATISTICS(ORD_TRADE_DATE,st_mkt_market_idx1)
325 
326 DBCC SHOW_STATISTICS(MKT_CURR_TRADE_DATE,ST_ORD_ORDER_IDX3)
327 
328 --从上图可以看出,st_mkt_market_idx1上的统计信息还是2006/11/9那天生成的
329 --他记录了表格里只有一条数据,MKT_CURR_TRADE_DATE的值就是2006/11/9
330 --那就是那天插入的
331 
332 
333 --但是ST_ORD_ORDER_IDX3的统计信息则是2007/6/5生成的。这张表里有83万条记录
334 --日期记录是从2006/11/15开始的,以后的每一天都差不多有数据。但是很明显,
335 --这张表里已经没有2006/11/9的数据了。难怪这两张表按时间相等做联接,SQL认为
336 --不会有记录返回,从而预估了一个最小值:1
337 
338 
339 --那么实际情况有事怎样的呢?重建了索引之后,我们来看新的统计信息值
340 --ST_ORD_ORDER的统计信息没什么大的变化,日期还是从2006/11/14到2007/6/5
341 --但是ST_MKT_MARKE里面唯一的那条值的日期,变成了更新统计信息那天的日期
342 --:2007/6/5。这样SQL能够发现两张表在2007/6/5都有值,所以他可以估计出
343 --两张表做联接,大概能返回9653条记录。这个和实际值就非常接近了
344 
345 
346 --比较两张表统计信息的前后差别,可以发现:
347 --(1)ST_MKT_MARKE表是一张很小的表,一直都只有一条记录,其日期值似乎
348 --就是系统当天的日期。而ST_ORD_ORDER是一张比较大的表,每天有10000条左右
349 --新记录插入。现在表格里保存了半年多的数据
350 
351 
352 
353 --(2)ST_ORD_ORDER上的统计信息在2007/6/5发生了更新。但是ST_MKT_MARKE上的
354 --统计信息从2006/11/9以来一直都没有发生更新。SQL已经知道ST_ORD_ORDER里最早
355 --的数据是从2006/11/15开始的,但是不知道ST_MKT_MARKE里的值也在变化,还是
356 --以为表里的数据是2006/11/9那天的。所以误以为这两张表做联接不会有记录返回
357 
358 --客户的数据库开启了auto update statistics。为什麽一张表(ST_ORD_ORDER)
359 --上的统计信息会自动更新,另一张表(ST_MKT_MARKE)却不能呢?从SQL触发
360 --auto update statistics的算法我们可以找到答案
361 
362 
363 
364 
365 --(1)ST_ORD_ORDER是一张超过500行记录的表,其触发条件是“当统计信息的第一个
366 --字段数据累计变化量大于500+(20%*表格数据总量)超过20%的数据有变化”。而其
367 --数据变化量从2006/11/9以来,已经远远超过1/5(20%)。所以auto update statistics
368 --会时不时地在ST_ORD_ORDER被触发,统计信息能够得到更新
369 
370 
371 --(2)ST_MKT_MARKE却是一张小于500行记录的表,其触发条件是
372 --“当统计信息的第一个字段数据累计变化量大于500+(20%*表格数据总量)超过20%的数据有变化”
373 --算算从2006/11/9到2007/6/5,才过了210天不到
374 SELECT DATEDIFF(dd,'2006-11-9','2007-6-5' )。
375 --每天把昨天的数据删除,加入今天的数据,变化量也就是2。总共的变化量才400多,还没到500。
376 --难怪SQL对他有“歧视”,还没有为他做auto update statistics
377 
378 --(3)如果ST_ORD_ORDER里面一直还有ST_MKT_MARKE的统计信息记录那天的数据,SQL做
379 --联接结果集预估还不会犯错。可是在问题出现之前,客户刚刚把ST_ORD_ORDER里的数据
380 --做了一次归档,把2006/11/14之前的数据从表里删除了。之后数据累积变化量超过了1/5,
381 --触发了ST_ORD_ORDER的auto update statistics,使得问题暴露了出来
382 
383 
384 --花了这麽多时间分析这个问题,结果还是挺有意义的。结论是:如果ST_ORD_ORDER
385 --不能存放超过250天的历史数据,那么再过几个月,等2007/6/5的数据归档后,性能
386 --问题还会再次发生。而解决问题的方法倒是非常简单的,只需要手工(或者做一个任务)
387 --更新ST_MKT_MARKE这张表的统计信息即可
388 
389 --总之,统计信息对SQL正确预估执行计划的成本非常重要
390 
391 
392 
393 -------------------------个人总结------------------------------------
394 --客户不停的每天把昨天的数据删除,加入今天的数据 使得总的数据量不大
395 --导致不会更新统计信息
396 
397 
398 
399 
400 select count(*) 
401 
402 select count(1)  
403 
404 select  count(*) where 某个字段=xxx 
405 
406 都是靠统计信息没有效率问题只有统计信息有没有更新 

 

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