SQLServer統(tǒng)計信息理解（總結(jié)）2015-01-1717:21

1275人閱讀

評論(0)

\o"收藏"收藏

\o"舉報"舉報

分類：SQLServer（188）

SQLServer調(diào)優(yōu)優(yōu)化（16）

前言Sqlserver查詢是基于開銷查詢的，在首次生成執(zhí)行計劃時，是基于多階段的分析優(yōu)化才確定出較好的執(zhí)行計劃。而這些開銷的基數(shù)估計，是根據(jù)統(tǒng)計信息來確定的。統(tǒng)計信息其實就是對表的各個字段的總體數(shù)據(jù)進行分段分布，\o"MySQL知識庫"數(shù)據(jù)庫默認(rèn)都會自動維護。

表和視圖都有統(tǒng)計信息，統(tǒng)計信息對象是根據(jù)索引或表列的列表創(chuàng)建的。當(dāng)某列第一次最為條件查詢時，將創(chuàng)建單列的統(tǒng)計信息。當(dāng)創(chuàng)建索引時，將創(chuàng)建同名的統(tǒng)計信息。索引中，統(tǒng)計信息只統(tǒng)計首列，因此索引除了按首列排序存儲數(shù)據(jù)外，其統(tǒng)計信息也是按首列計算統(tǒng)計的，所以索引設(shè)置時定義的第一列非常重要。每個統(tǒng)計信息對象都在包含一個或多個表列的列表上創(chuàng)建，并且包括顯示值在第一列中的分布的直方圖。

接下來了解統(tǒng)計信息吧~~^^統(tǒng)計信息的查看：可以看到，統(tǒng)計信息也是表的一種對象。[sql]

\o"viewplain"viewplain

\o"copy"copy

--列出表中的所有統(tǒng)計信息

select

*

from

sys.stats

where

object_id=OBJECT_ID(N'[Sales].[SalesOrderDetail]')

[sql]

\o"viewplain"viewplain

\o"copy"copy

--查看統(tǒng)計信息及其列

SELECT

AS

statistics_name

,

AS

column_name

,sc.stats_column_id

FROM

sys.stats

AS

s

INNER

JOIN

sys.stats_columns

AS

sc

ON

s.object_id

=

sc.object_id

AND

s.stats_id

=

sc.stats_id

INNER

JOIN

sys.columns

AS

c

ON

sc.object_id

=

c.object_id

AND

c.column_id

=

sc.column_id

WHERE

s.object_id

=

OBJECT_ID(N'[Sales].[SalesOrderDetail]');

--查看所有統(tǒng)計信息更新時間

exec

sp_helpstats

N'[Sales].[SalesOrderDetail]',

'ALL'

統(tǒng)計信息的屬性：右鍵統(tǒng)計信息，選擇“屬性”，可看到統(tǒng)計信息的設(shè)置和分布。[sql]

\o"viewplain"viewplain

\o"copy"copy

還可以使用命令DBCC

SHOW_STATISTICS查看，以下為列。

DBCC

SHOW_STATISTICS('[Sales].[SalesOrderDetail]','IX_SalesOrderDetail_ProductID')

主要分為三部分，分別為“統(tǒng)計信息頭部”，“密度向量”，“直方圖”1

統(tǒng)計信息頭信息列名說明Name統(tǒng)計信息對象的名稱（IX_SalesOrderDetail_ProductID）Updated上一次更新統(tǒng)計信息的日期和時間（Mar142012

1:14PM）Rows上次更新統(tǒng)計信息時表或索引視圖中的總行數(shù)（121317）。如果篩選統(tǒng)計信息或者統(tǒng)計信息與篩選索引對應(yīng)，該行數(shù)可能小于表中的行數(shù)RowsSampled用于統(tǒng)計信息計算的抽樣總行數(shù)（121317）。如果RowsSampled<Rows，顯示的直方圖和密度結(jié)果則是根據(jù)抽樣行估計的。Steps直方圖中的梯級數(shù)(200)。

每個梯級都跨越一個列值范圍，后跟上限列值。

直方圖梯級是根據(jù)統(tǒng)計信息中的第一個鍵列定義的。最大梯級數(shù)為200。Density計算公式為：1/統(tǒng)計信息對象第一個鍵列中的所有值（不包括直方圖邊界值）的

distinctvalues。(0.0078125)

查詢優(yōu)化器不使用此Density值，顯示此值的目的是為了與SQLServer2008之前的版本實現(xiàn)向后兼容。AverageKeyLength統(tǒng)計信息對象中所有鍵列的每個值的平均字節(jié)數(shù)(12:3個int類型。ProductID,SalesOrderID,SalesOrderDetailID)StringIndex（NO）Yes指示統(tǒng)計信息對象包含字符串摘要統(tǒng)計信息，以改進對使用LIKE運算符的查詢謂詞的基數(shù)估計；例如

WHEREProductNameLIKE'%Bike'。

字符串摘要統(tǒng)計信息與直方圖分開存儲，如果統(tǒng)計信息對象為char、varchar、nchar、nvarchar、varchar(max)、nvarchar(max)、text

或

ntext.

類型，則基于其第一個鍵列創(chuàng)建字符串摘要統(tǒng)計信息。FilterExpression包含在統(tǒng)計信息對象中的表行子集的謂詞。

NULL=未篩選的統(tǒng)計信息。UnfilteredRows應(yīng)用篩選表達式前表中的總行數(shù)（121317）。

如果FilterExpression為NULL，則UnfilteredRows等于Rows。2

密度信息列名說明AllDensityDensity為1/distinctvalues。

結(jié)果顯示統(tǒng)計信息對象中各列的每個前綴的密度，每個密度顯示一行。

非重復(fù)值是每個行前綴和列前綴的列值的非重復(fù)列表。反映索引列的選擇性（selectivity）

"選擇性"反映數(shù)據(jù)集里重復(fù)的數(shù)據(jù)量是多少，或者反過來說，值唯一的數(shù)據(jù)量有多少。如果一個字段的數(shù)據(jù)很少有重復(fù)，那么他的可選擇性就比較高。比如身份證號，是不可重復(fù)的。哪怕對整個中國的身份記錄做查詢，代入一個身份證號碼最多只會有一條記錄返回，在這樣的字段上的過濾條件，能夠有效地過濾掉大量數(shù)據(jù)返回的結(jié)果集會比較小舉個相反的例子：性別。所有人只有兩種，非男即女。這個字段上的重復(fù)性就很高選擇性就很低。一個過濾條件，最多只能過濾掉一半的記錄SQL通過計算“選擇性”，使得自己能夠預(yù)測一個過濾條件做完后，大概能有多少記錄返回

Density的定義是：

density=1/cardinalityofindexkeys如果這個值小于0.1，一般講這個索引的選擇性比較高，如果大于0.1，他的選擇性就不高了。（參考《Microsoftsqlserver企業(yè)級平臺管理實踐》）AverageLength存儲列前綴的列值列表的平均長度（以字節(jié)為單位）。Columns為其顯示Alldensity和Averagelength的前綴中的列的名稱這里至于為什么會有3行，是因為【ProductID】為非聚集索引，【SalesOrderID,SalesOrderDetailID】為聚集索引，而每個非聚集索引中都包含有聚集索引的鍵值，所以這里的統(tǒng)計信息也出現(xiàn)了3個可選項。當(dāng)前統(tǒng)計信息

[AllDensity]

計算方法：[sql]

\o"viewplain"viewplain

\o"copy"copy

select

count(*)

from

(select

count(*)

a

from

[Sales].[SalesOrderDetail]group

by

ProductID

)

as

T

select

count(*)

from

(select

count(*)

a

from

[Sales].[SalesOrderDetail]

group

by

ProductID,SalesOrderID)

as

T

select

count(*)

from

(select

count(*)

a

from

[Sales].[SalesOrderDetail]

group

by

ProductID,SalesOrderID,SalesOrderDetailID)

as

T

--按不同組統(tǒng)計如下：

group

by

ProductID

--266行

group

by

ProductID,

SalesOrderID

--121317行

group

by

ProductID,

SalesOrderID,

SalesOrderDetailID

--121317行

select

1.0/266

as

[all

density]

union

all

select

1.0/121317

as

[all

density]

union

all

select

1.0/121317

as

[all

density]

2

直方圖列名說明RANGE_HI_KEY直方圖梯級的上限列值。

列值也稱為鍵值。（按ProductID

的范圍分布）RANGE_ROWS其列值位于直方圖梯級內(nèi)（不包括上限）的行的估算數(shù)目。（2個ProductID值之間有多少行）EQ_ROWS其列值等于直方圖梯級的上限的行的估算數(shù)目。（等于當(dāng)前行ProductID值的有多少行）DISTINCT_RANGE_ROWS非重復(fù)列值位于直方圖梯級內(nèi)（不包括上限）的行的估算數(shù)目。（2個ProductID值之間有多少不重復(fù)的鍵值ProductID）AVG_RANGE_ROWS重復(fù)列值位于直方圖梯級內(nèi)（不包括上限）的平均行數(shù)（如果DISTINCT_RANGE_ROWS>0，則為

RANGE_ROWS/DISTINCT_RANGE_ROWS）。統(tǒng)計信息的重要性：SQLServer中，在執(zhí)行一個批處理語句時，關(guān)系引擎中的查詢優(yōu)化器會先估計生成較優(yōu)的執(zhí)行計劃，執(zhí)行執(zhí)行器才安照此執(zhí)行計劃請求數(shù)據(jù)。即在生成執(zhí)行計劃期間，sqlserver是根據(jù)表中的統(tǒng)計信息進行行數(shù)估計，按照腳本語義來確定物理操作步驟生成執(zhí)行計劃，再按照該執(zhí)行計劃訪問數(shù)據(jù)。而對于數(shù)據(jù)較大的表，按照統(tǒng)計信息估計的行數(shù)也常常不準(zhǔn)確，這就是使查詢使用了不準(zhǔn)確的執(zhí)行計劃而比較慢。類似如：“參數(shù)嗅探”因傳遞參數(shù)值無法確定而估算錯誤；使用表變量不會有統(tǒng)計信息也不會估算行數(shù)。[sql]

\o"viewplain"viewplain

\o"copy"copy

--現(xiàn)在以這個表的列統(tǒng)計為例[Sales].[SalesOrderDetail](SpecialOfferID)

DBCC

SHOW_STATISTICS('[Sales].[SalesOrderDetail]','_WA_Sys_0000000B_44CA3770')

如果查詢?nèi)掌诜秶?2005-07-01'<ModifiedDate<='2005-08-01',看上圖，查詢返回的估計行數(shù)應(yīng)該為896.7728(190.2021+706.5707)[sql]

\o"viewplain"viewplain

\o"copy"copy

SELECT

COUNT(

ModifiedDate

)FROM

[Sales].[SalesOrderDetail]

WHERE

ModifiedDate

>'2005-07-01

00:00:00'AND

ModifiedDate<='2005-08-01

00:00:00'

估計行數(shù)為896.773，與統(tǒng)計信息的直方圖的信息一致。其實就是根據(jù)直方圖統(tǒng)計出來的，如果估計行數(shù)不準(zhǔn)確，一定是統(tǒng)計信息沒有正確的直方圖信息，因此需要更新統(tǒng)計信息。在看帶參數(shù)的測試：[sql]

\o"viewplain"viewplain

\o"copy"copy

SELECT

COUNT(*)

FROM

[Sales].[SalesOrderDetail]

WHERE

ProductID=800

估計行數(shù)是495，是直方圖里顯示ProductID=800的估計?，F(xiàn)在使用參數(shù)替換。[sql]

\o"viewplain"viewplain

\o"copy"copy

DECLARE

@ProductID

INT

SET

@ProductID

=

800

SELECT

COUNT(*)

FROM

[Sales].[SalesOrderDetail]

WHERE

ProductID=@ProductID

看到估計行數(shù)是456.079，這個估計與實際的相差不大，不影響執(zhí)行計劃改變。但是為什么又變了呢？這個怎么來的？下面解釋[sql]

\o"viewplain"viewplain

\o"copy"copy

DBCC

SHOW_STATISTICS('[Sales].[SalesOrderDetail]','IX_SalesOrderDetail_ProductID')

ProductID=800的估計行數(shù)是495，而使用參數(shù)的是456.079，統(tǒng)計信息中并沒有記錄，但是SqlServer卻能根據(jù)密度計算。看紅框中的數(shù)值，因為我是以ProductID為謂詞，因此選擇了密度

Alldensity=

0.003759399，估計行數(shù)為：[EstimateRows]=121317*

0.003759399

=

456.079008483參數(shù)估計公式：[EstimateRows]

=Rows*[Alldensity]有時候即使更新了統(tǒng)計信息，結(jié)果還是一樣，因為數(shù)據(jù)量太大，估計數(shù)據(jù)不完全，看RowsSampled可知道，因此也可以在更新統(tǒng)計信息時采用全表行數(shù)統(tǒng)計，但是這樣掃描表數(shù)據(jù)也耗性能。即便如此，還是有些可能不一樣，因為直方圖的步長最多200，數(shù)據(jù)列中相同的和不同的差距太大，200段分布也有參差不齊的數(shù)據(jù)，不能使用更多步更詳細(xì)的數(shù)據(jù)直方圖。統(tǒng)計信息的更新設(shè)置：Sqlserver默認(rèn)自動維護統(tǒng)計信息，在數(shù)據(jù)庫級別可以設(shè)置自動創(chuàng)建和更新統(tǒng)計信息的選項。[sql]

\o"viewplain"viewplain

\o"copy"copy

用腳本設(shè)置如下：

ALTER

DATABASE

[databaseName]

SET

AUTO_CREATE_STATISTICS

ON

WITH

NO_WAIT

ALTER

DATABASE

[databaseName]

SET

AUTO_UPDATE_STATISTICS

ON

WITH

NO_WAIT

ALTER

DATABASE

[databaseName]

SET

AUTO_UPDATE_STATISTICS_ASYNC

ON

WITH

NO_WAIT

AUTO_CREATE_STATISTICS：AUTO_CREATE_STATISTICS=ON時，當(dāng)將某列作為條件查詢時，系統(tǒng)自動為每個條件列創(chuàng)建單列的統(tǒng)計信息。創(chuàng)建索引時也會自動創(chuàng)建相應(yīng)的統(tǒng)計信息.查詢優(yōu)化器通過使用AUTO_CREATE_STATISTICS選項創(chuàng)建統(tǒng)計信息時，統(tǒng)計信息名稱以_WA開頭。

AUTO_UPDATE_STATISTICS：AUTO_UPDATE_STATISTICS=ON時，查詢優(yōu)化器將確定統(tǒng)計信息何時可能過期，然后在查詢使用這些統(tǒng)計信息時更新它們。

統(tǒng)計信息將在插入、更新、刪除或合并操作更改表或索引視圖中的數(shù)據(jù)分布后過期。

查詢優(yōu)化器通過計算自最后統(tǒng)計信息更新后數(shù)據(jù)修改的次數(shù)并且將這一修改次數(shù)與某一閾值進行比較，確定統(tǒng)計信息何時可能過期。

該閾值基于表中或索引視圖中的行數(shù)。查詢優(yōu)化器在編譯查詢和執(zhí)行緩存查詢計劃前，檢查是否存在過期的統(tǒng)計信息。

在編譯某一查詢前，查詢優(yōu)化器使用查詢謂詞中的列、表和索引視圖確定哪些統(tǒng)計信息可能過期。

在執(zhí)行緩存查詢計劃前，數(shù)據(jù)庫引擎確認(rèn)該查詢計劃引用最新的統(tǒng)計信息。

AUTO_UPDATE_STATISTICS_ASYNC：異步統(tǒng)計信息更新選項AUTO_UPDATE_STATISTICS_ASYNC將確定查詢優(yōu)化器是使用同步統(tǒng)計信息更新還是異步統(tǒng)計信息更新。

默認(rèn)情況下，異步統(tǒng)計信息更新選項被關(guān)閉，并且查詢優(yōu)化器以同步方式更新統(tǒng)計信息。

AUTO_UPDATE_STATISTICS_ASYNC選項適用于為索引創(chuàng)建的統(tǒng)計信息對象、查詢謂詞中的單列以及使用

CREATESTATISTICS

語句創(chuàng)建的統(tǒng)計信息。統(tǒng)計信息更新可以是同步（默認(rèn)設(shè)置）或異步的。

對于同步統(tǒng)計信息更新，查詢將始終用最新的統(tǒng)計信息編譯和執(zhí)行；在統(tǒng)計信息過期時，查詢優(yōu)化器將在編譯和執(zhí)行查詢前等待更新的統(tǒng)計信息。

對于異步統(tǒng)計信息更新，查詢將用現(xiàn)有的統(tǒng)計信息編譯，即使現(xiàn)有統(tǒng)計信息已過期。如果在查詢編譯時統(tǒng)計信息過期，查詢優(yōu)化器可以選擇非最優(yōu)查詢計劃。

在異步更新完成后編譯的查詢將從使用更新的統(tǒng)計信息中受益。統(tǒng)計信息自動維護更新：Sqlserver之所以自動維護統(tǒng)計信息，首先設(shè)置AUTO_UPDATE_STATISTICS=ON，sqlserver會在符合某條件時自動更新表中的統(tǒng)計信息。其中我們可以看到的，系統(tǒng)表sysindexes的列rowmodctr，它記錄自上次更新統(tǒng)計信息后插入、刪除、更新行的累計總次數(shù)。對于滿足統(tǒng)計信息更新的條件，系統(tǒng)會自動更新。SELECT

name,rows,rowmodctrFROMsys.sysindexes

自動更新統(tǒng)計規(guī)則：?表中行范圍rows=0行增長rows>0行；?表中行范圍

0<rows<=500行，只要變化的次數(shù)rowmodctr>500；?表中行范圍rows>500行，只要變化的次數(shù)rowmodctr>500+20%rows；?臨時表行數(shù)rows<6，只要變化的次數(shù)rowmodctr>6；

需要手動更新統(tǒng)計信息：查詢執(zhí)行時間很長。在升序或降序鍵列上發(fā)生插入操作。在維護操作后。[sql]

\o"viewplain"viewplain

\o"copy"copy

--創(chuàng)建測試表

create

table

test(id

int

identity(1,1),name

char(20),value

numeric(18,4),meno

varchar(50))

create

clustered

index

IX_test

on

test(name)

alter

table

test

add

constraint

PK_test

primary

key

nonclustered(id)

--以[dbo].[test]表為例,先查看

select

,rows,rowmodctr,stats_date(s.object_id,s.stats_id)

AS

update_date

from

sys.sysindexes

i

inner

join

sys.stats

s

on

=

where

s.object_id

=

OBJECT_ID('[dbo].[test]')

--此時觀看兩個索引的直方圖,什么都沒有

DBCC

SHOW_STATISTICS('[dbo].[test]','IX_test')

DBCC

SHOW_STATISTICS('[dbo].[test]','PK_test')

--插入1行數(shù)據(jù),統(tǒng)計信息沒有更新?

insert

into

test(name,value,meno)

select

'name',0,'meno'

[sql]

\o"viewplain"viewplain

\o"copy"copy

--最終增刪了506*2行,統(tǒng)計信息都沒有生成

insert

into

test(name,value,meno)

select

'name',0,'meno'

go

500

delete

from

test

[sql]

\o"viewplain"viewplain

\o"copy"copy

--重新測試：重新刪除創(chuàng)建表。在插入數(shù)據(jù)前,每個字段搜索一次，非索引字段會自動生成統(tǒng)計信息.

select

*

from

test

where

id=1

select

*

from

test

where

name=''

select

*

from

test

where

value=0

select

*

from

test

where

meno=''

[sql]

\o"viewplain"viewplain

\o"copy"copy

--插入一行數(shù)據(jù),否則操作任何列統(tǒng)計信息都沒有更新

insert

into

test(name,value,meno)

select

'name',0,'meno'

go

--上面的查詢并沒有自動更新統(tǒng)計信息,只有作為where條件的更改或刪除了才更新統(tǒng)計信息

update

test

set

name='name'

where

name='name'

update

test

set

value=0

where

value=0

update

test

set

meno='meno'

where

id=1

delete

from

test

where

meno='meno'

[sql]

\o"viewplain"viewplain

\o"copy"copy

--再重新插入數(shù)據(jù),準(zhǔn)備測試用

insert

into

test(name,value,meno)

select

'name',0,'meno'

go

--查看統(tǒng)計情況

select

,rows,rowmodctr,stats_date(s.object_id,s.stats_id)

AS

update_date

from

sys.sysindexes

i

inner

join

sys.stats

s

on

=

where

s.object_id

=

OBJECT_ID('[dbo].[test]')

[sql]

\o"viewplain"viewplain

\o"copy"copy

--當(dāng)我更新索引的統(tǒng)計信息到

rowmodctr

=

500

行的時候，統(tǒng)計信息并沒有更新

update

test

set

name='name'

where

name='name'

go

497

[sql]

\o"viewplain"viewplain

\o"copy"copy

--z再更新一次，使索引更新累計rowmodctr

=

501行

update

test

set

name='name'

where

name='name'

go

[sql]

\o"viewplain"viewplain

\o"copy"copy

結(jié)果發(fā)現(xiàn)：索引的統(tǒng)計信息更新了，rowmodctr重新設(shè)置為1行。按相同的方法更新value為501次，非鍵列是沒有更新的！

update

test

set

value=0

where

value=0

[sql]

\o"viewplain"viewplain

\o"copy"copy

也就是這個條件是符合的：表中行范圍

0<rows<=500

行，只要變化的次數(shù)

rowmodctr>500

；

--插入數(shù)據(jù)到501行

insert

into

test(name,value,meno)

select

'name',0,'meno'

go

500

[sql]

\o"viewplain"viewplain

\o"copy"copy

當(dāng)數(shù)據(jù)大于500行達到501行時，rowmodctr此時大于500行并沒有更新索引的統(tǒng)計信息。

--現(xiàn)在更新501行數(shù)據(jù)的20%，統(tǒng)計信息并沒有更新。

with

tab

as(select

top

20

percent

*

from

test)

update

tab

set

name='name'

現(xiàn)在行數(shù)602行，理論上超過601.2(501+501*0.2)行會更新，現(xiàn)在在更新一次，如果統(tǒng)計信息自動更新就對了上圖看到，真的更新了！所以這個條件是符合的：表中行范圍rows>500行，