SQL Server 2000優(yōu)化SELECT 語句的方案介紹
以下的文章主要向大家講述的是SQL Server 2000優(yōu)化SELECT 語句的實(shí)際操作方案，此文講述的是SQL Server SQL語句優(yōu)化系列文章中的***篇。該系列文章描述了在Micosoft’s SQLServer2000關(guān)系數(shù)據(jù)庫管理系統(tǒng)中優(yōu)化SELECT語句的基本技巧。
我們討論的技巧可在Microsoft's SQL Enterprise Manager或 Microsoft SQL Query Analyzer（查詢分析器）提供的Microsoft圖形用戶界面使用。
除調(diào)優(yōu)方法外，我們給你展示了***實(shí)踐，你可應(yīng)用到你的SQL語句中以提高性能（所有的例子和語法都已在Microsoft SQL Server 2000中驗(yàn)證）。
閱讀該系列文章后，你應(yīng)該對Microsoft 工具包中提供的查詢SQL Server 2000優(yōu)化工具和技巧有一個(gè)基本的了解，我們將提供包含各種各樣的以提高性能和加速數(shù)據(jù)讀取操作的查詢技巧。
Microsoft提供了三種調(diào)優(yōu)查詢的主要的方法：
使用SET STATISTICS IO 檢查查詢所產(chǎn)生的讀和寫；
使用SET STATISTICS TIME檢查查詢的運(yùn)行時(shí)間；
使用SET SHOWPLAN 分析查詢的查詢計(jì)劃 。
SET STATISTICS IO
命令SET STATISTICS IO>
注：這些命令也能在 Sybase Adaptive Server中運(yùn)行，雖然結(jié)果集可能看起來有點(diǎn)不同。
例如，下面是在Northwind 數(shù)據(jù)庫中對于employees表上的一個(gè)行統(tǒng)計(jì)的簡單查詢腳本而獲得的I/O統(tǒng)計(jì)信息:
SET STATISTICS IO>這個(gè)掃描統(tǒng)計(jì)告訴我們掃描執(zhí)行的數(shù)量，邏輯讀顯示的是從緩存中讀出來的頁面的數(shù)量，物理讀顯示的是從磁盤中讀的頁面的數(shù)量，Read-ahead 讀顯示了放置在緩存中用于將來讀操作的頁面數(shù)量。
此外，我們執(zhí)行一個(gè)系統(tǒng)存儲過程獲得表大小的統(tǒng)計(jì)信息以供我們分析：
- sp_spaceused employees Results: name rows reserved data index_size unused -------------- -------- --------- ------- Employees 2977 2008KB 1504KB 448KB 56KB
通過看這些信息我們能得到些什么呢？
這個(gè)查詢沒有掃描整個(gè)表，在表中的數(shù)據(jù)量超過1.5M字節(jié)，而僅僅執(zhí)行了53個(gè)邏輯I/O操作就得到了結(jié)果。這表明該查詢發(fā)現(xiàn)了一個(gè)可用來計(jì)算結(jié)果的索引，并且掃描索引比掃描所有數(shù)據(jù)頁花費(fèi)更少的I/O操作。
索引頁幾乎全部放在數(shù)據(jù)緩存中，所以物理讀的值是零。這是因?yàn)槲覀冎安痪檬窃趀mployees表上執(zhí)行了其他查詢，此時(shí)表和它的索引已經(jīng)被緩存。你的查詢開銷可能有不同。
Microsoft報(bào)告沒有read-ahead（預(yù)讀）活動(dòng)。在這種情況下，數(shù)據(jù)和索引頁已經(jīng)被緩存起來了。當(dāng)對一個(gè)很大的表作表掃描時(shí)，read-ahead可能會(huì)半路插入進(jìn)來，并且在你的查詢用到它們之前緩存起所需的頁。
當(dāng)SQL Server確定你的事務(wù)是順序讀取數(shù)據(jù)庫頁并且認(rèn)為它能預(yù)測到你下一步將用到的頁面時(shí)，Real-ahead會(huì)自動(dòng)打開。實(shí)際上一個(gè)獨(dú)立的SQL Server連接在你的進(jìn)程之前已開始運(yùn)行并為它緩存數(shù)據(jù)頁。（配置和優(yōu)化read-ahead 參數(shù)已超出這篇文章的討論范圍。
在這個(gè)例子中，該查詢已經(jīng)盡可能有效率地執(zhí)行了，不必進(jìn)一步SQL Server 2000優(yōu)化。
SET STATISTICS TIME
一個(gè)事務(wù)的實(shí)耗時(shí)間是一個(gè)不穩(wěn)定的測量，因?yàn)檫@些時(shí)間與在服務(wù)器上其他用戶的活動(dòng)有關(guān)。然而，相比那些對你的用戶沒有任何意義的數(shù)據(jù)頁數(shù)字，它提供了一些實(shí)際的測量。他們關(guān)心等待查詢返回的時(shí)間消耗，不關(guān)心數(shù)據(jù)的緩存和有效的read-ahead。
SET STATISTICS TIME> SET STATISTICS TIME>***條信息報(bào)告了多少使人困惑的占用（實(shí)耗）時(shí)間，8672豪秒，這個(gè)數(shù)據(jù)與我們的腳本不相關(guān)，這顯示的是之前一個(gè)命令執(zhí)行以來逝去的時(shí)間。你可以忽略這條信息。SQL Server僅僅花費(fèi)10毫秒時(shí)間去分析和編譯該查詢。
花費(fèi)0毫秒去執(zhí)行它（在查詢結(jié)果可看到）。其真實(shí)的意思是這個(gè)查詢所花費(fèi)的時(shí)間太短以至不能計(jì)量。***的信息報(bào)告了這個(gè)SET STATISTICS TIME OFF命令相關(guān)的分析及編譯花費(fèi)了0毫秒。你可以忽略這個(gè)信息。最重要的信息以加重字體突出顯示。
注意實(shí)耗時(shí)間和CPU時(shí)間是以毫秒顯示。這個(gè)數(shù)字在你的電腦上可能會(huì)改變（但是不要嘗試與我們的筆記本電腦比較你機(jī)器的性能，因?yàn)檫@不是代表性的指標(biāo)）。而且，每次你執(zhí)行這個(gè)腳本，考慮到你的SQL Server還在處理一些其他事務(wù)，你得到的統(tǒng)計(jì)信息都可能有一點(diǎn)不同。
如果你需要測量一系列的查詢或者存儲過程的實(shí)耗持續(xù)時(shí)間，更好的辦法是采用程序設(shè)計(jì)的方式（如下所示）。當(dāng)你運(yùn)行多個(gè)命令時(shí)你不得不進(jìn)行手工合計(jì)，這是因?yàn)镾TATISTICS TIME只報(bào)告單個(gè)查詢的持續(xù)時(shí)間。想象一下，當(dāng)你對一個(gè)在循環(huán)里執(zhí)行成千上萬次查詢的腳本進(jìn)行計(jì)時(shí)的情況下，將面臨大量的輸出和大量的手工工作。
相反，考慮下面這個(gè)腳本在事務(wù)的前后分別捕獲時(shí)間并以秒的形式報(bào)告總持續(xù)時(shí)間（你也可以使用毫秒）：
- DECLARE @start_time DATETIME SELECT @start_time=GETDATE()
<any query or a script that you want to time, without a GO> SELECT ’Elapsed Time,sec’ =DATEDIFF(second, @start_time,GETDATE()) GO
如果你的腳本被GO分成幾步,你不能用本地變量來保存開始時(shí)間。變量在GO命令執(zhí)行后就被銷毀。但你可以象這樣在臨時(shí)表里保存開始時(shí)間。
- CREATE TABLE #save_time (start_time DATETIME NOT NULL) INSERT
- #save_time VALUES ( GETDATE()) GO < any script that you want to time (may include GO) > GO
- SELECT ‘Elapsed Time, sec’ = DATEDIFF ( second, start_time, GETDATE()) FROM TABLE #save_time DROP TABLE #save_time GO
請注意，SQL Server’s DATETIME 數(shù)據(jù)類型存儲的時(shí)間是以3毫秒為增量。使用DATETIME數(shù)據(jù)類型不可能獲得比這更細(xì)的時(shí)間粒度。
SHOWPLAN 輸出和分析
這篇文章通過explain plan(解析計(jì)劃)解釋Microsoft SQL Server 2000 使用SET SHOWPLAN_TEXT>
注：大部分的例子要么是基于PUBS數(shù)據(jù)庫，要么是基于SQL Server系統(tǒng)表的.針對這些實(shí)例，我們給很多表增加了好幾萬條記錄以便于在評估查詢計(jì)劃時(shí)體現(xiàn)查詢優(yōu)化器的實(shí)際作用。
SHOWPLAN 輸出:
我們喜歡查詢優(yōu)化器的一個(gè)功能就是以查詢執(zhí)行計(jì)劃的形式提供反饋。現(xiàn)在我們可以更為詳細(xì)地說明語句的執(zhí)行，并描述你可能在查詢計(jì)劃中遇到的消息。理解這個(gè)輸出可以使你的優(yōu)化水平達(dá)到一個(gè)新高度。你可以不再把SQL Server 2000優(yōu)化器視為一個(gè)可以處理你的查詢語句的有魔力的“黑盒子”，
下面的命令指示SQL Server顯示在同一個(gè)連接（或進(jìn)程）中每個(gè)查詢的執(zhí)行計(jì)劃，或?qū)⑦@個(gè)選項(xiàng)關(guān)閉。
SET SHOWPLAN_TEXT {>
默認(rèn)情況下，SHOWPLAN_TEXT>
其它有用的SET命令
有各種各樣對調(diào)優(yōu)和調(diào)試有用的SET命令。在這篇文檔前面我們提到了SET STATISTICS命令。在某些情況下你可以發(fā)現(xiàn)其它SET命令的用處:
- SET NOEXEC{>SET FMTONLY{>SET PARSEONLY {>
一旦設(shè)為>
典型的T-SQL代碼如下，獲得一個(gè)查詢的執(zhí)行計(jì)劃，而不實(shí)際執(zhí)行。
SET SHOWPLAN_TEXT>我們將展示幾個(gè)SHOWPLAN_TEXT 輸出的例子。為了避免冗余，我將不重復(fù)上面SET命令的展示.在這個(gè)部分里所提供的查詢都將代替這個(gè)腳本中的標(biāo)簽并且都象上面展示的一樣“包裝”。
事實(shí)上SHOWPLAN有兩個(gè)版本：SHOWPLAN_ALL和SHOWPLAN_TEXT。他們提供的信息基本上一樣。然而，SHOWPLAN_ALL輸出的結(jié)果是準(zhǔn)備給圖形查詢工具的而不是給聽眾的。我們在這整篇文章中將用到SHOWPLAN_TEXT，可提供更可讀的格式輸出。下面的簡單查詢選擇authors表的所有行。因?yàn)槲覀儧]有提供where子句所以它除了掃描整個(gè)表別無選擇：select * form authors
在下面的表中SHOWPLAN_TEXT輸出的結(jié)果沒有格式化，我們不得不從SHOWPLAN_ALL的輸出中整理出更多的可讀信息：
- SHOWPLAN_TEXT SHOWPLAN_ALL StmtTextStmtText ---------------------------------
- |--Clustered Index Scan |--Clustered Index Scan (OBJECT:([pubs].[dbo].
- (OBJECT:([pubs].[dbo]. [authors].[UPKCL_auidind])) [authors].[UPKCL_auidind])) StmtID NodeID
- Parent --------- -------- ------- 2 2 1 PhysicalOp LogicalOp ------------ ----------------
- NULL NULL Clustered Index scan Clustered Index scan Argument ---------------------------------------------
- 1 OBJECT:([pubs].[dbo]. ].[UPKCL_auidind]) DefindedValues ---------------------------------------
- 23 _ <all columns in table>_ EstimatedRows EstimateIO EstimatedCPU ------------------ ------------- --------
- 23 NULL NULL 23 0.01878925 5.1899999E-5 AvgRowSizeotalSubtreeCost ------------------------------------
- NULL 3.7682299E-2 1113.7682299E-2 OutputList -----------------------------------------
- NULL _ <all columns in table>_ Warnings TypeParallel EstimateExecutions -------- -------------------------
- NULL SELECT 0NULL NULPLAN_ROW01.0
這里重要的不同是SHOWPLAN_ALL語句返回了很多有用的調(diào)優(yōu)信息，但這些很難理解和應(yīng)用。
SHOWPLAN 操作
SHOWPLAN操作，有時(shí)叫做“標(biāo)簽”(tag)，其中一部分操作非常清晰地說明了SQL Server的做法，而其它一些操作將把人難住。這些操作分成物理操作和邏輯操作。物理操作描述被用來處理查詢的物理算法，例如，執(zhí)行一個(gè)索引查找。
邏輯操作描述語句中使用的關(guān)系代數(shù)操作，如聚合運(yùn)算等。SHOWPLAN的結(jié)果被細(xì)分非具體的步驟分成幾步。每個(gè)查詢的物理操作代表一個(gè)獨(dú)立步驟。步驟通常會(huì)伴有一個(gè)邏輯操作，但不是所有的步驟都包括邏輯操作。此外，大部分的步驟都有一個(gè)操作（要么邏輯操作要么物理操作）和一個(gè)參數(shù)。參數(shù)是操作所影響的查詢組件。關(guān)于所有執(zhí)行計(jì)劃步驟的討論內(nèi)容非常繁多。
以上的相關(guān)內(nèi)容就是對SQL Server 2000優(yōu)化SELECT語句方法的介紹，望你能有所收獲。
