使用SQL Server 2000索引視圖提高性能
本文介紹 SQL Server 2000 企業(yè)版的新功能,即索引視圖。講解索引視圖并討論一些提高性能的具體方案。
什么是索引視圖?
許多年來,Microsoft? SQL Server? 一直都提供創(chuàng)建虛擬表(稱為視圖)的功能。在過去,這些視圖主要有兩種用途:
提供安全機(jī)制,將用戶限制在一個或多個基表中的數(shù)據(jù)的某個子集。
提供一種機(jī)制,允許開發(fā)人員定制用戶如何才能以邏輯方式查看存儲在基表中的數(shù)據(jù)。
SQL Server 2000 已經(jīng)擴(kuò)展了 SQL Server 視圖的功能,以提高系統(tǒng)性能。它可以在一個視圖上創(chuàng)建唯一的群集索引和非群集索引,可以改進(jìn)最復(fù)雜查詢的數(shù)據(jù)訪問性能。在 SQL Server 2000 中,擁有唯一群集索引的視圖被稱為索引視圖。
注意:索引視圖只是SQL Server 2000 企業(yè)版和SQL Server 2000 開發(fā)人員版的一個功能。
從數(shù)據(jù)庫管理系統(tǒng) (DBMS) 的觀點來看,視圖是數(shù)據(jù)(元數(shù)據(jù))的說明。創(chuàng)建典型視圖時,通過 SELECT 語句(定義一個顯示為虛擬表的結(jié)果集)來定義元數(shù)據(jù)。當(dāng)其它查詢的 FROM 子句中引用了某個視圖時,將從系統(tǒng)目錄中檢索該元數(shù)據(jù),并對其進(jìn)行擴(kuò)展以代替該視圖的引用。在視圖擴(kuò)展之后,查詢優(yōu)化器會為正在執(zhí)行的查詢編譯單個執(zhí)行計劃。
如果是非索引視圖,視圖在運行時將被實體化。任何計算(如聯(lián)接或聚合)都在為每個引用該視圖的查詢執(zhí)行查詢期間進(jìn)行。(視圖并不總需要被完全實體化。查詢可以包含其它一些謂詞、聯(lián)接或聚合,以應(yīng)用于該視圖所引用的表和視圖。)在視圖上創(chuàng)建了唯一的群集索引之后,視圖的結(jié)果集會立即被實體化并持續(xù)保存在數(shù)據(jù)庫的物理存儲空間中,以便節(jié)省這種操作所占用的大量資源。
在執(zhí)行查詢時,有兩種方法可以使用索引視圖。查詢可直接引用索引視圖,更重要的是,如果查詢優(yōu)化器確定視圖能夠替換為查詢的部分或全部,而且這是低成本的查詢計劃,則可以選擇索引視圖。第二種情況是使用索引視圖代替基礎(chǔ)表及其普通索引。此時,不需要在查詢中引用視圖,查詢優(yōu)化器即可在執(zhí)行查詢期間使用該視圖。這樣,現(xiàn)有的應(yīng)用程序無需更改即可從新建的索引視圖中獲益。
通過索引視圖提高的性能
使用索引來提高查詢性能并不是什么新觀念,不過,索引視圖還具有使用標(biāo)準(zhǔn)索引不能獲得的其它性能優(yōu)點。索引視圖能夠在以下方面提高查詢性能:
能夠預(yù)先計算聚合并將其存儲在索引中,從而***限度地減少在執(zhí)行查詢期間進(jìn)行成本很高的計算。
能夠預(yù)先聯(lián)接表并存儲生成的數(shù)據(jù)集。
能夠存儲聯(lián)接或聚合的組合。
下圖說明了查詢優(yōu)化器使用索引視圖時一般能夠提高多少性能。提供的查詢復(fù)雜程度各不相同(例如,聚合計算的數(shù)量、所用表的數(shù)量或謂詞數(shù)),并包括來自實際生產(chǎn)環(huán)境的數(shù)百萬行的大表。
圖 1. 當(dāng)查詢優(yōu)化器使用索引視圖時一般能夠提高多少性能
使用視圖的輔助索引
視圖的輔助性非群集索引可以提高其它查詢性能。與表的輔助索引類似,視圖的輔助索引也可以提供更多選項,以便查詢優(yōu)化器在編譯過程中從中進(jìn)行選擇。例如,如果查詢包括群集索引未涉及的列,優(yōu)化器可以在計劃中選擇一個或多個輔助索引,從而避免對索引視圖或基表進(jìn)行費時的全局掃描。
由于索引需要不斷維護(hù),所以為架構(gòu)添加索引會增加數(shù)據(jù)庫的額外開銷。因此應(yīng)該認(rèn)真考慮,找到索引和維護(hù)額外開銷之間的平衡點。
使用索引視圖的好處
實現(xiàn)索引視圖之前,請先分析數(shù)據(jù)庫的工作量。運用自己對查詢以及各種工具(例如 SQL 分析器)的知識來鑒別使用索引視圖可以獲益的查詢。如果經(jīng)常進(jìn)行聚合和聯(lián)接,***使用索引視圖。
并非所有查詢都會從索引視圖中獲益。與普通索引類似,如果未使用索引視圖,就沒有好處可言。在此情況下,不但不能提高性能,還會加大磁盤空間的占用、增加維護(hù)和優(yōu)化的成本。但是,如果使用了索引視圖,它們可以(成數(shù)量級地)明顯地提高數(shù)據(jù)訪問的性能。這是因為查詢優(yōu)化器使用存儲在索引視圖中的預(yù)先計算的結(jié)果,從而大大降低了執(zhí)行查詢的成本。
查詢優(yōu)化器只在查詢的成本比較大時才考慮使用索引視圖。這樣可以避免在查詢優(yōu)化成本超出因使用索引視圖而節(jié)省的成本時,試圖使用各種索引視圖。當(dāng)查詢成本低于 1 時,幾乎不使用索引視圖。
使用索引視圖可以受益的應(yīng)用包括:
- 決定支持工作量
- 數(shù)據(jù)集市
- 聯(lián)機(jī)分析處理 (OLAP) 庫和源
- 數(shù)據(jù)挖掘工作量
從查詢的類型和模式的角度來看,受益的應(yīng)用可被歸納為包含以下內(nèi)容的應(yīng)用:
- 大表的聯(lián)接和聚合
- 查詢的重復(fù)模式
- 重復(fù)聚合相同或重疊的列集
- 針對相同關(guān)鍵字重復(fù)聯(lián)接相同的表
- 上述的組合
相反,包含許多寫入的聯(lián)機(jī)事務(wù)處理 (OLTP) 系統(tǒng)或更新頻繁的數(shù)據(jù)庫,可能會因為要同時更新視圖和根本基表而使維護(hù)成本增加,所以不能利用索引視圖。
查詢優(yōu)化器如何使用索引視圖
SQL Server 查詢優(yōu)化器可自動確定何時可以將索引視圖用于給定的查詢執(zhí)行中。查詢中無需直接引用視圖,優(yōu)化器就可以將該視圖用于查詢執(zhí)行計劃。因此,無需對現(xiàn)有的應(yīng)用程序本身進(jìn)行任何更改,這些應(yīng)用程序即可利用索引視圖。唯一需要做的就是創(chuàng)建索引視圖。
優(yōu)化器的考慮因素
查詢優(yōu)化器會考慮幾個條件來確定索引視圖能涵蓋部分查詢還是整個查詢。這些條件符合查詢中的單個 FROM 子句并包含以下內(nèi)容:
- 查詢 FROM 子句中的表必須是索引視圖 FROM 子句中的表的超集。
- 查詢中的聯(lián)接條件必須是視圖中聯(lián)接條件的超集。
- 查詢中的聚合列必須是視圖中的聚合列的子集。
- 查詢選擇列表中的所有表達(dá)式都必須源自于視圖選擇列表或源自于不包括在視圖定義中的表。
- 查詢搜索條件謂詞必須是視圖定義中搜索條件謂詞的超集。視圖搜索謂詞中的每個合取項都必須以同樣的形式出現(xiàn)在查詢搜索謂詞中。
- 查詢搜索條件謂詞中的所有列(屬于視圖定義中的表)都必須出現(xiàn)在下列一項或多項中:
視圖定義中的同一個謂詞。
GROUP BY 列表。
視圖選擇列表(若沒有 GROUP BY 列表)。
如果查詢包含多個 FROM 子句(子查詢、派生表、UNION),優(yōu)化器可以選擇多個索引視圖來管理含有多個 FROM 子句的查詢。
注意:存在例外情形,即優(yōu)化器可能將兩個 FROM 子句折疊成一個(將子查詢折疊成聯(lián)接或?qū)⑴缮碚郫B成聯(lián)接變體)。如果出現(xiàn)此類情況,索引視圖替換可能會涵蓋原查詢中的多個 FROM 子句。
本文檔結(jié)尾介紹了演示這些條件的查詢示例。而建議的***方法就是:讓查詢優(yōu)化器來確定在查詢執(zhí)行計劃中使用哪些索引(如果有的話)。
使用 NOEXPAND 選項
NOEXPAND 選項強(qiáng)制查詢優(yōu)化器象對待包含群集索引的普通表一樣對待視圖。在此情況下,必須在 FROM 子句中直接引用索引視圖。例如:
SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND)WHERE ...
使用 EXPAND VIEWS 選項
另外,用戶可以在查詢結(jié)束時通過使用 EXPAND VIEWS 選項,明確地將索引視圖排除在考慮之外。例如:
SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...OPTION (EXPAND VIEWS)
如果使用該選項,查詢優(yōu)化器在評估低成本的方法(該方法涉及查詢中引用的列)時將忽略所有視圖索引。
設(shè)計的考慮因素
為數(shù)據(jù)庫系統(tǒng)找到適當(dāng)?shù)乃饕窍喈?dāng)復(fù)雜的。盡管在設(shè)計普通索引時要考慮許多可能性,但將索引視圖添加到架構(gòu)會極大地增加設(shè)計和潛在結(jié)果的復(fù)雜性。
例如:
索引視圖可用于查詢中所引用表的任何子集。 查詢中條件的任何子集(屬于表的上述子集)分組列。 聚合函數(shù),如 SUM。
應(yīng)同時設(shè)計表的索引和索引視圖,以便從各個結(jié)構(gòu)中獲得***結(jié)果。由于索引和索引視圖都可能對給定的查詢有用,所以單獨設(shè)計它們會導(dǎo)致多余的建議方案,以致存儲和維護(hù)開銷較高。在調(diào)整數(shù)據(jù)庫的物理設(shè)計時,必須均衡考慮各種查詢集的性能要求與數(shù)據(jù)庫系統(tǒng)必須支持的更新操作。因此,為索引視圖找到一種合理的物理設(shè)計是一項很具挑戰(zhàn)性的任務(wù),因而應(yīng)該盡可能地使用“索引微調(diào)向?qū)?rdquo;。
如果存在許多索引視圖可供查詢優(yōu)化器考慮用于特定查詢,查詢優(yōu)化成本會顯著增加。查詢優(yōu)化器可能考慮為查詢中表的任意子集定義的所有索引視圖。拒絕每一個視圖之前,必須對它進(jìn)行語法分析,然后研究其是否可能成為潛在的替換體。這可能需要一些時間,尤其是在有數(shù)百個此類的視圖用于給定的查詢時。
視圖必須符合幾項要求,您才能為其創(chuàng)建唯一的群集索引。在設(shè)計階段,請考慮以下要求:
視圖以及視圖中引用的所有表都必須在同一數(shù)據(jù)庫中,并具有同一個所有者。
索引視圖無需包含要供優(yōu)化器使用的查詢中引用的所有表。
必須先為視圖創(chuàng)建唯一群集索引,然后才可以創(chuàng)建其它索引。士正確設(shè)置某些 SET 選項(在本文檔的后文中討論)。另外,如果這些 SET 選項正確,查詢優(yōu)化器將不考慮索引視圖。
視圖必須使用架構(gòu)綁定創(chuàng)建,視圖中引用的任何用戶定義的函數(shù)必須使用 SCHEMABINDING 選項創(chuàng)建。
另外,還要求有一定的磁盤空間來存放由索引視圖定義的數(shù)據(jù)。
上文中就使用SQL Server 2000索引視圖提高性能的問題為大家作出了詳細(xì)的解析,希望對大家能夠有所幫助。
【編輯推薦】
- SQL Server 2000數(shù)據(jù)庫作數(shù)據(jù)庫服務(wù)器的優(yōu)點
- SQL Server 2000中生成XML的小技巧
- 在SQL Server 2000中恢復(fù)Master數(shù)據(jù)庫