作者： TechRepublic.com
Friday, March 5 2004 9:51 AM
|
特別說明：
在微軟的SQL服務(wù)器系統(tǒng)中，對數(shù)據(jù)庫查詢功能進(jìn)行適當(dāng)?shù)膬?yōu)化需要懂得一些基本的查詢索引和性能統(tǒng)計方面的知識。熟悉該系統(tǒng)的優(yōu)化工作是如何實(shí)現(xiàn)的將有助于提高決策的正確性。
隨著你對微軟的SQL服務(wù)器數(shù)據(jù)庫實(shí)現(xiàn)的逐漸熟悉，性能優(yōu)化的需求也將進(jìn)一步增加。建立一個真正實(shí)現(xiàn)最優(yōu)查詢功能的數(shù)據(jù)庫環(huán)境的第一步是要懂得SQL服務(wù)器系統(tǒng)的優(yōu)化器是如何工作的。
索引
雖然對于特定的查詢來說，進(jìn)行查詢規(guī)劃和性能優(yōu)化可能只需要少量的成本估算與比較，也可以沒有成本估算與比較，但是大多數(shù)的查詢將從實(shí)現(xiàn)完全優(yōu)化的工作中受益。提高查詢性能的最有效的方法之一就是創(chuàng)建一個高效率的索引。一個構(gòu)架良好的索引在執(zhí)行查詢工作的時候可以避免出現(xiàn)掃描整個數(shù)據(jù)表的情況。
在創(chuàng)建索引的時候，SQL服務(wù)器系統(tǒng)將自動度量和存儲那些與索引列相關(guān)的分布狀態(tài)值相對應(yīng)的統(tǒng)計信息。這些統(tǒng)計信息常常被優(yōu)化器用來評估查詢的優(yōu)化策略是否合理。
有兩種類型的索引：clustered索引和non-clustered索引，根據(jù)數(shù)據(jù)集合的不同，每種類型的索引都有各自獨(dú)特的優(yōu)點(diǎn)。
clustered索引要求數(shù)據(jù)表中數(shù)據(jù)按照順序存儲。因?yàn)閿?shù)據(jù)已經(jīng)排序，所以對于查找一定范圍的索引值時clustered 索引是非常有效的。對于查找具有唯一索引值的行信息來說，這種類型的索引性能也優(yōu)于其他類型的索引。
non-clustered索引和教科書中的索引非常相似，索引在一個位置而其數(shù)據(jù)值卻在另外一個位置。對于一個數(shù)據(jù)值的查詢搜索來說，首先搜索non-clustered的索引，找到數(shù)據(jù)值在數(shù)據(jù)表中的位置，然后直接從這個位置得到數(shù)據(jù)。non-clustered 索引對于精確匹配查詢是非常有用的。
統(tǒng)計學(xué)
作為一種常用的規(guī)則，和大多數(shù)商業(yè)使用需求一樣，索引的數(shù)量應(yīng)該盡可能少，以減少與每個查詢相關(guān)的處理過程。如果要分析和優(yōu)化查詢的性能，首先應(yīng)該度量和收集數(shù)據(jù)的統(tǒng)計特性。
SQL服務(wù)器系統(tǒng)能夠維護(hù)索引值的數(shù)據(jù)統(tǒng)計特性。如果對其進(jìn)行適當(dāng)?shù)呐渲?，對于非索引值也能夠進(jìn)行統(tǒng)計度量。
對于性能優(yōu)化，數(shù)據(jù)庫管理員應(yīng)該懂得幾個基本的統(tǒng)計概念，這些概念的定義如下：
|
基數(shù)：度量在數(shù)據(jù)集中可以存在多少個唯一值。
密度：度量在數(shù)據(jù)集中唯一值的個數(shù)。密度通過如下方法得到：給定鍵值的行數(shù)除以數(shù)據(jù)表的總行數(shù)。優(yōu)化器將忽略高密度的索引。
選擇率：度量對于一個特定的查詢將返回查詢結(jié)果的行數(shù)。選擇率通過如下方法得到：查詢關(guān)鍵字的個數(shù)除以查詢得到的行數(shù)。要計算查詢規(guī)劃的相對成本，優(yōu)化器需要一個有效的選擇率來度量。
隨著列中數(shù)據(jù)的變化，索引和列統(tǒng)計信息就變得沒有用處了，這樣將導(dǎo)致優(yōu)化器在決定如何處理查詢時達(dá)不到最優(yōu)性能。因此，根據(jù)數(shù)據(jù)表中數(shù)據(jù)的變化，SQL服務(wù)器系統(tǒng)周期的自動更新這些統(tǒng)計信息。通過對這些數(shù)據(jù)的采樣，這種統(tǒng)計信息的自動更新將使得成本降到最低，而且不需要對全部數(shù)據(jù)進(jìn)行分析。
最佳性能
在一個復(fù)雜的數(shù)據(jù)庫表中設(shè)計并指定索引是一件非常棘手的任務(wù)。幸運(yùn)的是，SQL服務(wù)器系統(tǒng)有一個內(nèi)置的調(diào)節(jié)向?qū)韼椭憬⒆顑?yōu)的統(tǒng)計和索引集合。要提高數(shù)據(jù)庫的查詢性能，可以通過運(yùn)行向?qū)硖峁┮粋€基于腳本的建議列表。
對于SQL服務(wù)器查詢優(yōu)化器如何工作這一部分懂得越多，你就會知道對于特定的情形為什么只能用向?qū)У慕ㄗh來實(shí)現(xiàn)。但是，對于動態(tài)系統(tǒng)來說，最佳的數(shù)據(jù)庫性能分析部分將需要進(jìn)行周期性地更新。理解查詢索引性能中的每個統(tǒng)計度量的真正含義將有助于你在管理決策方面有一個良好的知識基礎(chǔ)。
更新索引統(tǒng)計
分布頁面并不是每次一個記錄更新時都要進(jìn)行更新.在大型數(shù)據(jù)庫中,這會導(dǎo)致巨大的性能損失.因此,當(dāng)用戶初始創(chuàng)建一個空表時,分布頁面仍是空的.它僅在發(fā)生如下情況時才被更新:
1.用戶在一個已存在數(shù)據(jù)表上創(chuàng)建一個索引.
2.用戶進(jìn)行了update?satatic語句
從系統(tǒng)管理員角度來看,用戶應(yīng)該創(chuàng)建一個工具來自動地更新分布頁面.自動更新應(yīng)該至少每周一次,如果數(shù)據(jù)量每天增加10%以上則應(yīng)每天一次.
因?yàn)椴豢赡苊刻於继砑铀饕?用戶需要使用update?statistics語句更新分布頁面,用以優(yōu)化SQLserver.
UPDATE?STATISTICS
在指定的表或索引視圖中，對一個或多個統(tǒng)計組（集合）有關(guān)鍵值分發(fā)的信息進(jìn)行更新。若要基于列生成統(tǒng)計，請參見?CREATE?STATISTICS。?
語法
UPDATE?STATISTICS?table?|?view
????[?
????????index
????????|?(?statistics_name?[?,...n?]?)
????]?
????[????WITH
????????[?
????????????[?FULLSCAN?]
????????????|?SAMPLE?number?{?PERCENT?|?ROWS?}?]
????????????|?RESAMPLE
????????]?
????????[?[?,?]?[?ALL?|?COLUMNS?|?INDEX?]
????????[?[?,?]?NORECOMPUTE?]?
????]?
參數(shù)
table?|?view
要更新統(tǒng)計的表或索引視圖的名稱。表名和視圖名必須符合標(biāo)識符的規(guī)則。有關(guān)更多信息，請參見使用標(biāo)識符。由于索引名在每個數(shù)據(jù)庫中不唯一，所以必須指定?table?或?view。可選擇指定數(shù)據(jù)庫、表或視圖所有者。只有在?Microsoft??SQL?Server??2000?企業(yè)版中才支持索引視圖。
index
要更新統(tǒng)計的索引。索引名必須符合標(biāo)識符的規(guī)則。如果未指定?index，則更新指定表或索引視圖中的所有索引的分發(fā)統(tǒng)計。若要查看索引名和描述的列表，請帶表名或視圖名執(zhí)行?sp_helpindex。
statistics_name
要更新的統(tǒng)計組（集合）的名稱。統(tǒng)計名稱必須符合標(biāo)識符規(guī)則。有關(guān)生成統(tǒng)計組的更多信息，請參見?CREATE?STATISTICS。
n
是表示可以指定多個?statistic_name?組的占位符。
FULLSCAN
指定應(yīng)讀取?table?或?view?中的所有行以收集統(tǒng)計。FULLSCAN?提供與?SAMPLE?100?PERCENT?相同的行為。FULLSCAN?不能與?SAMPLE?選項(xiàng)一起使用。
SAMPLE?number?{?PERCENT?|?ROWS?}
當(dāng)為較大的表或視圖收集統(tǒng)計時，指定要采樣的表或索引視圖的百分比或行數(shù)。number?只允許使用整數(shù)，無論它是?PERCENT?還是?ROWS。若要對較大的表或視圖使用默認(rèn)采樣行為，請將?SAMPLE?number?和?PERCENT?或?ROWS?一起使用。Microsoft?SQL?Server?將確保值的采樣數(shù)不低于某一數(shù)目，以保證統(tǒng)計有用。如果?PERCENT、ROWS?或?number?選項(xiàng)導(dǎo)致要采樣的行數(shù)過小，SQL?Server?則自動根據(jù)表或視圖中的現(xiàn)有行數(shù)改正采樣。
說明??默認(rèn)行為是在目標(biāo)表或索引視圖上進(jìn)行采樣掃描。SQL?Server?自動計算所需的樣本大小。
RESAMPLE
指定使用從所有現(xiàn)有統(tǒng)計（包括索引）繼承的采樣速率來收集統(tǒng)計。如果采樣速率導(dǎo)致要采樣的行過少，SQL?Server?則自動根據(jù)表或視圖中的現(xiàn)有行數(shù)改正采樣。
ALL?|?COLUMNS?|?INDEX
指定?UPDATE?STATISTICS?語句是否影響列統(tǒng)計、索引統(tǒng)計或所有現(xiàn)有統(tǒng)計。如果未指定選項(xiàng)，則?UPDATE?STATISTICS?語句影響所有的統(tǒng)計。每個?UPDATE?STATISTICS?語句只能指定一種類型（ALL、COLUMNS?或?INDEX）。?
NORECOMPUTE
指定過期統(tǒng)計不自動重新計算。統(tǒng)計過期與否取決于在索引列上進(jìn)行的?INSERT、UPDATE?和?DELETE?操作的數(shù)量。指定該選項(xiàng)時，將導(dǎo)致?SQL?Server?禁用自動統(tǒng)計重建功能。若要還原自動統(tǒng)計重新計算，請重新執(zhí)行?UPDATE?STATISTICS（不要?NORECOMPUTE?選項(xiàng)），或者執(zhí)行?sp_autostats。
重要??禁用自動統(tǒng)計重新計算會導(dǎo)致?SQL?Server?查詢優(yōu)化器對于涉及指定表的查詢選擇非最佳的策略。
注釋
SQL?Server?保留每個索引中關(guān)于鍵值分發(fā)的統(tǒng)計，并且使用這些統(tǒng)計來決定查詢處理中使用哪個（或哪些）索引。用戶可以通過使用?CREATE?STATISTICS?語句生成基于非索引列的統(tǒng)計。查詢優(yōu)化依賴于分發(fā)步驟的準(zhǔn)確性：?
如果索引中的鍵值有顯著變化，請對此索引重新運(yùn)行?UPDATE?STATISTICS。
如果索引列中添加、更改或刪除大量數(shù)據(jù)（即如果鍵值分發(fā)更改），或者用?TRUNCATE?TABLE?語句將表截斷然后重新填充，請使用?UPDATE?STATISTICS。?
若要查看統(tǒng)計最近一次更新的時間，請使用?STATS_DATE?函數(shù)。
只有當(dāng)能夠在計算列上創(chuàng)建索引時，才可以在包含這些計算列的表上創(chuàng)建或更新統(tǒng)計。有關(guān)在計算列上創(chuàng)建索引的要求和限制的更多信息，請參見?CREATE?INDEX。
權(quán)限
UPDATE?STATISTICS?權(quán)限默認(rèn)授予表或視圖的所有者，并且該權(quán)限不可轉(zhuǎn)讓。
示例
A.?更新單個表的所有統(tǒng)計
本示例更新表?authors?上的所有索引分發(fā)統(tǒng)計。
UPDATE?STATISTICS?authors
B.?僅更新單一索引的統(tǒng)計
本示例僅更新表?authors?的索引?au_id_ind?的分發(fā)信息。?
UPDATE?STATISTICS?authors?au_id_ind
C.?使用?50%?采樣更新特定統(tǒng)計組（集合）的統(tǒng)計
本示例首先創(chuàng)建表?authors?中?au_lname?列和?au_fname?列的統(tǒng)計組，然后對其進(jìn)行更新。
CREATE?STATISTICS?anames?
???ON?authors?(au_lname,?au_fname)
???WITH?SAMPLE?50?PERCENT
GO
--?Time?passes.?The?UPDATE?STATISTICS?statement?is?then?executed.
UPDATE?STATISTICS?authors(anames)?
???WITH?SAMPLE?50?PERCENT
GO
D.?使用?FULLSCAN?和?NORECOMPUTE?更新特定統(tǒng)計組（集合）的統(tǒng)計
本示例更新表?authors?中的?anames?統(tǒng)計組（集合），強(qiáng)制對表?authors?中的所有行進(jìn)行完全掃描，并且關(guān)閉該統(tǒng)計組（集合）的自動統(tǒng)計更新。
UPDATE?STATISTICS?authors(anames)
???WITH?FULLSCAN,?NORECOMPUTE
sp_updatestats對當(dāng)前數(shù)據(jù)庫中所有用戶定義的表運(yùn)行?UPDATE?STATISTICS。
語法
sp_updatestats?[[@resample?=]?''resample'']
返回代碼值
0（成功）或?1（失?。?/p>
參數(shù)
[@resample?=]?''resample''
指定?sp_updatestats?將使用?UPDATE?STATISTICS?命令的?RESAMPLE?選項(xiàng)。新統(tǒng)計表將繼承舊統(tǒng)計表的采樣比率。如果未指定?''resample''，則?sp_updatestats?使用默認(rèn)采樣更新統(tǒng)計表。該參數(shù)的數(shù)據(jù)類型為?varchar(8)，默認(rèn)值為?''NO''。
注釋
sp_updatestats?會顯示表示其進(jìn)度的消息。完成更新之后，該存儲過程將報告已為所有的表更新了統(tǒng)計信息。?
權(quán)限
只有?DBO?和?sysadmin?固定服務(wù)器角色的成員才能執(zhí)行該過程。
示例
下例為數(shù)據(jù)庫?pubs?中的表更新統(tǒng)計信息。
USE?pubs
EXEC?sp_updatestats?
Sqlserver7?編程技術(shù)內(nèi)幕提供的方法.
drop?proc?pr_updateindex
create?proc?pr_updateindex
as
?set?nocount?on?
?declare?get_index_curs?cursor?
??for?select?name--tablename
??from?sysobjects?--systemtable
?where?type=''u''?-usertable
?declare?@holdtable?varchar(30)
?declare?@message?varchar(40)
?declare?@dynamic?varchar(51)
?open?getindex_curs
?fetch?next?from?getindex_curs?into?@holdtable
?while?@@fetch_status=0
?begin
???select?@dynamic=''update?statistics?''+@holdtable
???select?@message=''updating''+@holdtable
???exec(@dynamic)
??print?@message
??fetch?next?from?getindex_curs?into?@holdtable
end
??close?getindex_curs
Copyright?(C)?2003?Cameron?Michelis?copying?and?redistribution?of?this?file?is?permitted?provided?
this?notice?and?the?above?comments?are?preserved.
*/
Set?quoted_identifier?off
use?master
DECLARE?@fillfactor?varchar(2)
DECLARE?@tablename?varchar(30)
DECLARE?@tablename_header?varchar(75)
DECLARE?@dataname?varchar(30)
DECLARE?@dataname_header?varchar(75)
DECLARE?datanames_cursor?CURSOR?FOR?SELECT?name?FROM?sysdatabases
????????WHERE?name?not?in?('master',?'pubs',?'tempdb',?'model',?'northwind')
/*?Variable?Initialization?*/
?select?@fillfactor?=?"0"?--?Set?Fill?factor?here
?????--?Note?"0"?will?use?original?fillfactor.
/*?End?Variable?Initialization?*/
OPEN?datanames_cursor
??FETCH?NEXT?FROM?datanames_cursor?INTO?@dataname
??WHILE?(@@fetch_status?<>?-1)
????BEGIN
??????IF?(@@fetch_status?=?-2)
????????BEGIN
??FETCH?NEXT?FROM?datanames_cursor?INTO?@dataname
???????????CONTINUE
????????END
?SELECT?@dataname_header?=?"Database?"?+?RTRIM(UPPER(@dataname))
???????PRINT?"?"
?PRINT?@dataname_header
???????PRINT?"?"
?EXEC?("USE?"?+?@dataname?+?"?DECLARE?tnames_cursor?CURSOR?FOR?SELECT?name?from?sysobjects?where?type?=?'U'")
?Select?@dataname_header?=?RTRIM(UPPER(@dataname))
?Exec?("Use?"?+?@dataname)?
?OPEN?tnames_cursor
??FETCH?NEXT?FROM?tnames_cursor?INTO?@tablename
??WHILE?(@@fetch_status?<>?-1)
??????????BEGIN
?????????????IF?(@@fetch_status?=?-2)????????????
????BEGIN
??????????????????FETCH?NEXT?FROM?tnames_cursor?INTO?@tablename
??????????????????CONTINUE
???????????????END
????????SELECT?@tablename_header?=?"??Updating?"?+?RTRIM(UPPER(@tablename))
????PRINT?""
?????????????PRINT?@tablename_header
????EXEC?("USE?"?+?@dataname?+?"?DBCC?DBREINDEX?("?+?@tablename?+?","?+?"''"?+?","?+?@fillfactor?+?")")
????EXEC?("USE?"?+?@dataname?+?"?UPDATE?STATISTICS?"?+?@tablename)
????FETCH?NEXT?FROM?tnames_cursor?INTO?@tablename
??????????END
?DEALLOCATE?tnames_cursor
???????FETCH?NEXT?FROM?datanames_cursor?INTO?@dataname
??????END
DEALLOCATE?datanames_cursor
PRINT?""
PRINT?"?"
PRINT?"Indexing?complete?for?All?User?Databases"
SET QUOTED_IDENTIFIER OFF
/* Start with master DB */
USE master
/* Create Variables */
DECLARE @DBName CHAR(64)
DECLARE @TableName CHAR(64)
DECLARE @FQTableName CHAR(64)
DECLARE @TempVar CHAR(256)
/* Create DB List */
DECLARE DBCursor CURSOR FOR
SELECT name
FROM master..sysdatabases
OPEN DBCursor
FETCH NEXT
FROM DBCursor
INTO @DBName
/* Create Database Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Retrieve Table List */
PRINT 'Retrieving Table List for DB ' + @DBName
EXEC ('SELECT name AS TableName INTO ##TableNames FROM [' + @DBName + ']..sysobjects WHERE type = ''U''')
/* Open Table List */
DECLARE TableCursor CURSOR FOR
SELECT TableName
FROM ##TableNames
OPEN TableCursor
FETCH NEXT
FROM TableCursor
INTO @TableName
/* Create Table Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Add DB Name to Table Name */
SELECT @FQTableName = QUOTENAME(RTRIM(@DBName)) + '..' + QUOTENAME(RTRIM(@TableName))
SELECT @TableName = RTRIM(@DBName) + '..' + RTRIM(@TableName)
/* ReIndex Table */
PRINT 'ReIndexing Table ' + @TableName
DBCC DBREINDEX(@TableName)
/* Update Statics on Table */
PRINT 'Updating Statistics on Table ' + @TableName
EXEC ('UPDATE STATISTICS ' + @FQTableName)
/* Get Next Table Name */
FETCH NEXT
FROM TableCursor
INTO @TableName
END
/* Close Table Cursor */
CLOSE TableCursor
DEALLOCATE TableCursor
/* Remove Tempory Table */
DROP TABLE ##TableNames
/* Preform DB Checks */
PRINT 'Preforming DB Checks on ' + @DBName
DBCC CHECKDB (@DBName)
/* Get Next Table Name */
FETCH NEXT
FROM DBCursor
INTO @DBName
END
/* Close DB Curosor */
CLOSE DBCursor
DEALLOCATE DBCursor
/* Finished */