用SQLServer2000索引視圖提高性能(下)

1、用sqlserver2000索引視圖提高性能（下）使用“索引微調(diào)向?qū)?quot;“索引微調(diào)向?qū)?#39;'除建議使用基表的索引之外，還建議使用索引視 圖。使用該向?qū)Э商岣吖芾韱T確定索引和索引視圖相結(jié)合的能力，從而優(yōu)化針對數(shù)據(jù)庫執(zhí)行 的典型混合查詢的性能。由于“索引微調(diào)向?qū)?quot;強制使用所有必需的set選項（以確保結(jié)果 集的正確性），其索引視圖將會成功創(chuàng)建。不過，如果您的應(yīng)用程序的選項沒有按照耍求設(shè) 置，可能無法利用這些視圖。對那些參與索引視圖定義的表執(zhí)行的插入、更新或刪除操作可 能會失敗。維護索引視圖sql server自動維護索引視圖，這與維護任何其它索引的情況類 似。對于普通

2、索引而言，每個索引都直接連接到單個表。通過對基礎(chǔ)表執(zhí)行每個insert、 update或delete操作，索引相應(yīng)地進行了更新，以便使存儲在該索引中的值始終與 表一致。索引視圖的維護與此類似。不過，如果視圖引用了多個表，則對這些表中的任何一 個進行更新都需要更新索引視圖。與普通索引不同的是，對任何一個參與的表執(zhí)行一次行插入操作都可能導(dǎo)致在索引視圖 中進行多次行插入操作。更新和刪除操作的情況也是如此。因此，較之于維護表的索引，維 護索引視圖的代價更為高昂。在sql server 2000中，某些視圖可以更新。如果某個視圖可 以更新，則使用insert. update和delete語句可通過該視圖

3、直接修改根本基表。為 某個視圖創(chuàng)建索引并不會妨礙該視圖的更新。有關(guān)可更新視圖的詳細信息，請參閱關(guān)于sql server 2000的4<sql server聯(lián)機圖書”中的“通過視圖修改數(shù)據(jù)（英文）”。null。維護成本的考慮因素設(shè)計索引視圖時應(yīng)該考慮以下幾點：數(shù)據(jù)庫屮需要有一個額外的存 儲空間用于索引視圖。索引視圖的結(jié)果集以類似于典型表存儲空間的方式物理保存在數(shù)據(jù)庫 中。sql server自動維護視圖。因此，對定義視圖所據(jù)的基表的任何更改都可能引起視圖 索引的一處或多處更改，從而導(dǎo)致維護開銷的增加。一個視圖獲得的凈性能提高就是視圖 提供的查詢執(zhí)行節(jié)約總計與存儲和維護該視圖耗費的成本之間的

4、差。估計視圖將占用的所需 存儲空問要相對簡單一些。用sql查詢分析器的“顯示估計的執(zhí)行計劃”工具求視圖定義屮 select語句的值。該工具將得出查詢返冋的行數(shù)和行大小的近似值。將這兩個值相乘， 即可估計出視圖的可能大小。不過這只是一個近似值。視圖索引的實際大小只能通過創(chuàng)建視 圖索引來精確得岀。從sql server執(zhí)行的自動維護考慮因素的觀點出發(fā)，“顯示估計的執(zhí) 行計劃”的功能可能會對此開銷的影響有所了解。如果用sql查詢分析器評估修改視圖的 語句（針對視圖的update語句、針對基表的insert語句），showplan將包括該語 句的維護操作。同時考慮此成本和此操作將在生產(chǎn)壞境中發(fā)生的次數(shù)

5、，可以指示視圖維護的 可能成本。通常建議對視圖或基表進行的任何修改和更新都應(yīng)該盡可能地成批執(zhí)行，而不要 單獨進行。這樣可以減少視圖維護的某些開銷。創(chuàng)建索引視圖創(chuàng)建索引視圖所需的步驟與 視圖的成功實現(xiàn)密不可分。確保將在視圖中引用的所有現(xiàn)有表的set選項都正確。創(chuàng)建 任何新表和視圖之前，確保會話的set選項已正確設(shè)置。確保視圖定義是確定的。使用 with schemabinding選項創(chuàng)建視圖。創(chuàng)建視圖的唯一群集索引。使用set選項以 獲得一致的結(jié)果如果在執(zhí)行查詢時啟用不同的set選項，則在sql server中對同一個表 達式求值會產(chǎn)生不同的結(jié)果。例如，將set選項concat_null_yie

6、lds_null設(shè)置 為 on 之后，表達式 abc' + null 返回的值是 null。而將 concat_null_yieds_null設(shè)置為off z后，該表達式得出的結(jié)果卻是abc爲索引 視圖要求多個set選項的值都固定，以確保這些視圖能夠得到正確維護并返冋一致的結(jié) 果。只要出現(xiàn)以下情況，就必須將下表中的set選項設(shè)置為耍求的值列中所示的值：創(chuàng)建 了索引視圖。對索引視圖中引用的任何表執(zhí)行了任何insert、update或delete操 作。查詢優(yōu)化器使用索引視圖來生成查詢計劃。set選項 要求的值 默認服務(wù)器的值ole db 和 odbc 的值 db lib 的值 ansi_

7、nulls on off on off ansi_padding on on on off ansi warning on off on off arithabort on off off offconcat_null_yields_null on off on off numeric_roundabort off off off off quoteddentifier on off on off 如果使用的是 ole db 或 odbc 服 務(wù)器連接，唯一必須修改的值是arithabort的設(shè)置。所有db lib值都必須使用 sp_configure在服務(wù)器級上正確設(shè)置或使用set命令從應(yīng)用

8、程序正確設(shè)置。有關(guān)set選 項的詳細信息，請參閱關(guān)于sql server 2000的“sql server聯(lián)機圖書”中的“使用sql server屮的選項（英文）使用確定性函數(shù)索引視圖的定義必須是確定性的。如果選擇列表中的所有表達式以及 where和group by子句都是確定性的，則視圖就是確定性的。只要用特定的一組輸入 值對確定性表達式進行求值，一定會返回同一個結(jié)果。只有確定性函數(shù)可以加入確定性表達 式。例如，dateadd是確定性函數(shù)，因為將任何給定的一組變量值賦了它的三個參數(shù)進 行求值，返回的總是同一個結(jié)果。而getdate則不是確定性函數(shù)，因為始終用同一個變 量調(diào)用它，而它每次執(zhí)行后

9、返回的值都不相同。有關(guān)詳細信息，請參閱關(guān)于sql server 2000 的“sqlserver聯(lián)機圖書呻的“確定性和非確定性函數(shù)二即便某個表達式是確定性的，但如果其中包含浮動表達式，確切的結(jié)果就可能収決于處 理器的體系結(jié)構(gòu)或微代碼的版本。要確保sql server 2000中數(shù)據(jù)的完整性，此類表達式只 能加入索引視圖的非關(guān)鍵列。不包含浮動表達式的確定性表達式被稱為精確的表達式。只有 精確的確定性表達式可以加入索引視圖的關(guān)鍵列和where或group by子句。使用 columnproperty函數(shù)和isdeterministic屬性來確定視圖列是否是確定性的。使用 columnpropert

10、y函數(shù)和isprecise屬性來確定包含架構(gòu)綁定的視圖中的確定性列是否 是精確的。如果為true,則columnproperty會返回1,如果為false,則返回0, 如果是無效的輸入（列不是確定性的），則返回 null o例如,select columnproperty（object_id（'vdiscountr）；sumdiscountprice7isprecise,）返冋的是 0,因 為sumdiscountprice列引用了表order details中的浮動列discount0而同一視圖中的列 sumprice既是確定性的又是精確的。注意：該select語句所基于的視圖能夠在

11、示例部 分找到（視圖1）。其它要求除“設(shè)計準則"、“使用set選項以獲得一致的結(jié)果”和“使用 確定性函數(shù)”部分中列出的要求z外，還必須符合以下要求。基表要求基表在創(chuàng)建時必須正 確設(shè)置set選項，否則就不能被包含架構(gòu)綁定的視圖引用。表必須通過視圖定義中的兩 部分名稱（所有者.表名）引用。函數(shù)要求 用戶定義的函數(shù)必須使用 with schemabinding選項創(chuàng)建。用戶定義的函數(shù)必須通過兩部分名稱（所有者.函數(shù)）引用。 視圖要求 視圖必須使用with schemabinding選項創(chuàng)建。視圖必須只引用同一數(shù)據(jù) 庫中的基表，而不能引用其它視圖。語法限制對視圖定義的語法有兒個限制。視圖定義

12、不 能包含以下內(nèi)容：count® rowset函數(shù) 派生表 自聯(lián)接 distinct stdev > variance、avg float*列、文本列、ntext列、圖像列 子查詢 全文謂詞（contain、 freetext）可空表達式的sum min、max top outer聯(lián)接union注意： 索引視 圖可以包含浮動列，不過，此類列不能包含在群集索引關(guān)鍵字中。group by限制如果未 使用group by,表達式不能在選擇列表中使用。如果使用了 group by,則view定 義：必須包含count_big（*）。不得包含having> cube或rollup。

13、這些限制只 適用于索引視圖定義。查詢可以在其執(zhí)行計劃中使用索引視圖，即便該索引視圖并不符合這 些group by限制。索引要求執(zhí)行create index語句的用戶必須是視圖所有者。如 果視圖定義中包含group by子句，唯一群集索引的關(guān)鍵字只能引用group by子句中 指定的列。示例 本部分的示例闡述索引視圖在兩種主要查詢（聚合和聯(lián)接）中的使用問題。 同時還說明查詢優(yōu)化器在確定某個索引視圖是否可用時使用的條件。有關(guān)這些條件的完整列 表，請參閱查詢優(yōu)化器如何使用索引視圖。查詢基于northwind （sql server 2000中提供的數(shù)據(jù)庫樣本）中的表，并可以寫入的方式執(zhí)行。創(chuàng)建視圖的

14、前后，最好使用sql查詢優(yōu)化器中的“顯示執(zhí)行計劃"工具來查看查 詢優(yōu)化器選定的計劃。盡管示例中闡述了優(yōu)化器是如何選擇成本最低的執(zhí)行計劃的，但因為 northwind數(shù)據(jù)庫樣本太小，因此無法體現(xiàn)性能的提高。以下查詢顯示如何從onler details 表中返回具有最大總折扣的五種產(chǎn)品的兩個方法。查詢1 select top 5 productid, sum(unitprice*quantity) sum(unitprice*quantity*( 1.00-discount)as rebatefrom order detailsgroup by productidorder by reb

15、ate desc 查詢 2select top 5 productid, sum(unitprice*quantity*discount)as rebatefrom order detailsgroup by productidorder by rebate desc查詢優(yōu)化器選定的執(zhí)行計劃包含：對order details表的 群集索引掃描，估計有2,155行。哈希匹配/聚合運算符，該運算符基于group by列將 選定的行放入哈希表，然后計算每行的sum聚合?；趏rder by子句的top 5排 序運算符。視圖1添加包括rebate列所需聚合的索引視圖將更改查詢1的查詢執(zhí)行計 劃。在數(shù)百

16、萬行的大表上，查詢的性能也將明顯提高。create view vdiscountl with schemabindingasselectsum(unitprice*quantity)assumprice,sumcunitpricequantityc 1.00-discount)as sumdiscountprice, count_big(*) as count, productid from dbo.order detailsgroup by productid gocreate unique clustered index vdiscountlnd on vdiscountl (produc

17、tid)第一個查詢的執(zhí)行計劃顯示 vdiscountl視圖由查詢優(yōu)化器使用。不過，由于該視圖不包含sum(unitprice*quantity*discount)聚合，因此不會被笫二個 查詢使用?？梢詣?chuàng)建另一個可以同時滿足上述兩個查詢的索引視圖。視圖2create view vdiscount2 with schemabindingas select sum(unitprice*quantity)as sumprice, sum(unitprice*quantity*( 1.00-discount)assumdiscountprice,sum(unitprice*quantitydiscoun

18、t)as sumdiscountprice2, count_big(*) as count, productidfrom dbo. order detailsgroup by productidgocreate unique clustered index vdiscountlnd on vdiscount2 (productid)有了該索引視圖，現(xiàn)在兩個查 詢的查詢執(zhí)行計劃包含：對vdiscouno視圖的群集索引掃描，估計有77行 基于order by子句的top5排序函數(shù)查詢優(yōu)化器選擇該視圖是因為它提供了最低的執(zhí)行成本，盡管 在查詢中并未引用該視圖。查詢3查詢3類似于前兒個查詢，只是pro

19、ductid已被 orderld所取代，視圖定義中沒有包括該列。這違背了以下條件：查詢選擇列表中的所有表 達式都必須能從未包括在視圖定義內(nèi)的表的視圖選擇列表中派生。select top 3 orderld, sum(unitprice*quantity*discount) orderrebatefrom dbo.order detailsgroup by orderldorder by orderrebate desc要求單獨的索引視圖來滿足該查詢?？梢詫discounl2 進行修改，使它包括oixlehd,但是所生成視圖的行數(shù)將與原表的行數(shù)相同，因此，提供的 性能也不會高于使用基表所提供的

20、性能。查詢4該查詢可生成每個產(chǎn)品的平均價格。 select productname, od.productid,avg(od.unitprice*( 1.00-discount) as avgprice, sum(od.quantity) as units from order details od, products p where od.productid=p.productid group by productname, od.produclid 索引視圖的定義屮不能包 括復(fù)雜的聚合(例如，stdev、variance、avg),不過，如果索引視圖中包括幾個聯(lián)合 起來執(zhí)行復(fù)雜聚合的簡單聚

21、合函數(shù)，即可用于執(zhí)行包含avg的查詢。視圖3該索引視圖 包含執(zhí)行avg函數(shù)所需的簡單聚合函數(shù)。在創(chuàng)建了視圖3后執(zhí)行查詢4時，執(zhí)行計劃會 顯示正被使用的視圖。優(yōu)化器可以從視圖的簡單聚合列price和count中導(dǎo)出avg表達 式。create view view3 with schemabinding as select productid, sum(unitprice*( 1.00-discount)as price, count_big(*)as count, sum(quantity)as units from dbo.order details group by productid go

22、create unique clustered index iv3 on view3 (productid)查詢5該查詢與查詢4相同，只不過包括一個附加搜索條件。即 使該附加搜索條件只引用未包括在視圖定義內(nèi)的表中的列，視圖3也將用于該查詢。select productname, od.productid, av g(od.unitprice*( 1.00-discount) as avgprice, sum(od.quantity)as units from order details as od, products as p where od.productid=p.productid an

23、d p.productname like *%tofu%' group by productname, od.productid查詢6査詢優(yōu)化器不能將視圖3用于該查詢。附加搜索條件od.unitprice>10 包含視圖定義內(nèi)的表屮的列，而該列卻不出現(xiàn)在group by列表屮，搜索謂詞也不出現(xiàn)在 視圖定義中。select productname, od.productid, avg(od.unitprice*( 1.00-discount)as avgprice, sum(od.quantity) as units from order details od, products

24、p where od.productid = p.productid and od.unitprice > 10 group by productname, od.productid 查 詢7相反，查詢優(yōu)化器可以將視圖3用于查詢7,原因是新搜索條件od.productid in (1,2,13,41)中定義的列包括在視圖定義內(nèi)的group by子句中。select productname, od.productid, av g(od.unitprice*( 1.00-discount) as avgprice, sum(od.quantity) as unitsfrom order de

25、tails as od, products as pwhere od.productid = p.productidand od.productid in (1,2,13,41)group by productname, od.productid 視圖 4 該視圖在視圖定義 中包括了列od.discount,可以滿足查詢6的條件。create view view4 with schemabindingasselectproductname,od.productid,sum(od.unitprice*( 1.00-discount) as avgprice, sum(od.quantity) as units, count_big(*) as count from dbo.order details as od, dbo.products as p where od.productid = p.productid and od.unitprice > 10group by productname, od.productidgocreate unique clustered index vdiscountlnd on view4 (produ