【探討】索引視圖如何提高性能

2019-11-17 03:51:27

字體：大中小

供稿：網(wǎng)友

　最近的一個(gè)項(xiàng)目中，由于數(shù)據(jù)量特別大，導(dǎo)致生成一個(gè)報(bào)表需要等待10多分鐘，所以必須對(duì)系統(tǒng)進(jìn)行優(yōu)化，跟蹤程序后發(fā)現(xiàn)數(shù)據(jù)是從由六個(gè)表聯(lián)接而成的視圖中過濾出來的，并且六個(gè)表中每個(gè)表中的數(shù)據(jù)都是百萬級(jí)的，這樣聯(lián)接后效率可想而知了？所以得先對(duì)視圖進(jìn)行優(yōu)化，檢查這六個(gè)表后發(fā)現(xiàn)其中的兩個(gè)表未建索引，于是馬上對(duì)其設(shè)置索引，再把select的沒用字段去除后，再重新執(zhí)行一下查詢語句后，發(fā)現(xiàn)效率提高了差不多20%，但是總的來說時(shí)間還是比較長(zhǎng)，看來還得繼續(xù)改進(jìn)。聽朋友說索引視圖可以大大提高效率，于是從網(wǎng)上查找了一些關(guān)于索引視圖的資料，并對(duì)其進(jìn)行了簡(jiǎn)單學(xué)習(xí)，現(xiàn)總結(jié)如下：

一、實(shí)例

1、  優(yōu)化前的視圖（執(zhí)行select * from Query_NoEmployRegist用時(shí)127s）

代碼
1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 ALTER VIEW [dbo].[Query_NoEmployRegist]
6 AS
7 SELECT dbo.Person_BasicInfo.*, dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
8       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,
9       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,
10       dbo.Graduater_Business.ComeFrom AS ComeFrom,
11       dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status,
12       dbo.Graduater_Business.ApPRoveResult AS ApproveResult,
13       dbo.Graduater_Business.NewCorp AS NewCorp,
14       dbo.Graduater_Business.CommendNumber AS CommendNumber,
15       dbo.Graduater_Business.EmployStatus AS EmployStatus,
16       dbo.Graduater_Business.NewCommendTime AS NewCommendTime,
17       dbo.Graduater_Business.GetSource AS GetSource,
18       dbo.Graduater_Business.EmployTime AS EmployTime,
19       dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan,
20       dbo.Graduater_Business.FillTime AS FillTime,
21       dbo.Graduater_Business.IsCommendOK AS IsCommendOK,
22       dbo.Graduater_Business.ApproveUser AS ApproveUser,
23       dbo.Graduater_Business.ApproveTime AS ApproveTime,
24       dbo.Graduater_Business.RegistTime AS RegistTime,
25       dbo.Graduater_Business.EmployCorp AS EmployCorp,
26       dbo.Graduater_Business.JobRemark AS JobRemark,
31       dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip,
32       dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile,
33       dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM,
34       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,
35       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,
36       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,
37       dbo.Person_Skill.MandarinLevel AS MandarinLevel,
38       dbo.Person_Skill.Language AS Language,
39       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,
40       dbo.Person_Skill.ComputerLevel AS ComputerLevel,
41       dbo.Person_EmployPurpose.JobType AS JobType,
42       dbo.Person_EmployPurpose.Vocation AS Vocation,
43       dbo.Person_EmployPurpose.JobPlace AS JobPlace,
44       dbo.Person_EmployPurpose.Salary AS Salary,
45       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,
46       dbo.Person_EmployPurpose.CorpType AS CorpType,
49       dbo.Graduater_Business.EmployType AS EmployType,
50       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,
51       dbo.Graduater_Business.EmployCorpType AS EmployCorpType
56 FROM dbo.Person_BasicInfo INNER JOIN
57       dbo.Graduater_Business ON
58       dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID LEFT OUTER JOIN
59       dbo.Graduater_GraduaterRegist ON
60       dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
61        INNER JOIN
62       dbo.Person_Contact ON
63       dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN
64       dbo.Person_Skill ON
65       dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN
66       dbo.Person_EmployPurpose ON
67       dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
68 GO
69 SET ANSI_NULLS OFF
70 GO
71 SET QUOTED_IDENTIFIER OFF
72 GO
　　2、  優(yōu)化后的視圖（執(zhí)行select * from Query_NoEmployRegist用時(shí)98s）　　

代碼
1 SET ANSI_NULLS on
2 GO
3 SET QUOTED_IDENTIFIER on
4 GO
5 ALTER VIEW [dbo].[Query_NoEmployRegist]
6 AS
7 SELECT
8       dbo.Person_BasicInfo.PersonID,
9       dbo.Person_BasicInfo.IdentityID,
10       dbo.Person_BasicInfo.Name,
11       dbo.Person_BasicInfo.Sex,
12       dbo.Person_BasicInfo.Folk,
13       dbo.Person_BasicInfo.Politics,
14       dbo.Person_BasicInfo.Birthday,
15       dbo.Person_BasicInfo.StudentSource,
16       dbo.Person_BasicInfo.StudentSourceCode,
17       dbo.Person_BasicInfo.EduLevel,
18       dbo.Person_BasicInfo.EduLevelCode,
19       dbo.Person_BasicInfo.EduNumber,
20       dbo.Person_BasicInfo.Stature,
21       dbo.Person_BasicInfo.Avoirdupois,
22       dbo.Person_BasicInfo.MarriageStatus,
23       dbo.Person_BasicInfo.College,
24       dbo.Person_BasicInfo.GraduatedDate,
25       dbo.Person_BasicInfo.Train,
26       dbo.Person_BasicInfo.Major,
27       dbo.Person_BasicInfo.Degree,
28       dbo.Person_BasicInfo.DegreeCertificate,
29       dbo.Person_BasicInfo.StudyMode,
30       dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
31       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,
32       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,
33       dbo.Graduater_Business.ComeFrom AS ComeFrom,
34       dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status,
35       dbo.Graduater_Business.ApproveResult AS ApproveResult,
36       dbo.Graduater_Business.NewCorp AS NewCorp,
37       dbo.Graduater_Business.CommendNumber AS CommendNumber,
38       dbo.Graduater_Business.EmployStatus AS EmployStatus,
39       dbo.Graduater_Business.NewCommendTime AS NewCommendTime,
40       dbo.Graduater_Business.GetSource AS GetSource,
41       dbo.Graduater_Business.EmployTime AS EmployTime,
42       dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan,
43       dbo.Graduater_Business.FillTime AS FillTime,
44       dbo.Graduater_Business.IsCommendOK AS IsCommendOK,
45       dbo.Graduater_Business.ApproveUser AS ApproveUser,
46       dbo.Graduater_Business.ApproveTime AS ApproveTime,
47       dbo.Graduater_Business.RegistTime AS RegistTime,
48       dbo.Graduater_Business.EmployCorp AS EmployCorp,
49       dbo.Graduater_Business.JobRemark AS JobRemark,
54       dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip,
55       dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile,
56       dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM,
57       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,
58       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,
59       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,
60       dbo.Person_Skill.MandarinLevel AS MandarinLevel,
61       dbo.Person_Skill.Language AS Language,
62       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,
63       dbo.Person_Skill.ComputerLevel AS ComputerLevel,
64       dbo.Person_EmployPurpose.JobType AS JobType,
65       dbo.Person_EmployPurpose.Vocation AS Vocation,
66       dbo.Person_EmployPurpose.JobPlace AS JobPlace,
67       dbo.Person_EmployPurpose.Salary AS Salary,
68       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,
69       dbo.Person_EmployPurpose.CorpType AS CorpType,
72       dbo.Graduater_Business.EmployType AS EmployType,
73       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,
74       dbo.Graduater_Business.EmployCorpType AS EmployCorpType,
79 FROM dbo.Person_BasicInfo INNER JOIN
80       dbo.Graduater_Business ON
81       dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID LEFT OUTER JOIN
82       dbo.Graduater_GraduaterRegist ON
83       dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
84        INNER JOIN
85       dbo.Person_Contact ON
86       dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN
87       dbo.Person_Skill ON
88       dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN
89       dbo.Person_EmployPurpose ON
90       dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
91 GO
92 SET ANSI_NULLS OFF
93 GO
94 SET QUOTED_IDENTIFIER OFF
95 GO
　　3、  創(chuàng)建索引的視圖（執(zhí)行select * from Query_NoEmployRegist用時(shí)51s）

代碼
1 SET ANSI_NULLS on
2 GO
3 SET QUOTED_IDENTIFIER on
4 GO
5
6 ALTER VIEW [dbo].[Query_NoEmployRegist]
7 WITH SCHEMABINDING AS
8 SELECT
9       dbo.Person_BasicInfo.PersonID,
10       dbo.Person_BasicInfo.IdentityID,
11       dbo.Person_BasicInfo.Name,
12       dbo.Person_BasicInfo.Sex,
13       dbo.Person_BasicInfo.Folk,
14       dbo.Person_BasicInfo.Politics,
15       dbo.Person_BasicInfo.Birthday,
16       dbo.Person_BasicInfo.StudentSource,
17       dbo.Person_BasicInfo.StudentSourceCode,
18       dbo.Person_BasicInfo.EduLevel,
19       dbo.Person_BasicInfo.EduLevelCode,
20       dbo.Person_BasicInfo.EduNumber,
21       dbo.Person_BasicInfo.Stature,
22       dbo.Person_BasicInfo.Avoirdupois,
23       dbo.Person_BasicInfo.MarriageStatus,
24       dbo.Person_BasicInfo.College,
25       dbo.Person_BasicInfo.GraduatedDate,
26       dbo.Person_BasicInfo.Train,
27       dbo.Person_BasicInfo.Major,
28       dbo.Person_BasicInfo.Degree,
29       dbo.Person_BasicInfo.DegreeCertificate,
30       dbo.Person_BasicInfo.StudyMode,
31       dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
32       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,
33       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,
34       dbo.Graduater_Business.ComeFrom AS ComeFrom,
35       dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status,
36       dbo.Graduater_Business.ApproveResult AS ApproveResult,
37       dbo.Graduater_Business.NewCorp AS NewCorp,
38       dbo.Graduater_Business.CommendNumber AS CommendNumber,
39       dbo.Graduater_Business.EmployStatus AS EmployStatus,
40       dbo.Graduater_Business.NewCommendTime AS NewCommendTime,
41       dbo.Graduater_Business.GetSource AS GetSource,
42       dbo.Graduater_Business.EmployTime AS EmployTime,
43       dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan,
44       dbo.Graduater_Business.FillTime AS FillTime,
45       dbo.Graduater_Business.IsCommendOK AS IsCommendOK,
46       dbo.Graduater_Business.ApproveUser AS ApproveUser,
47       dbo.Graduater_Business.ApproveTime AS ApproveTime,
48       dbo.Graduater_Business.RegistTime AS RegistTime,
49       dbo.Graduater_Business.EmployCorp AS EmployCorp,
50       dbo.Graduater_Business.JobRemark AS JobRemark,
51       dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip,
52       dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile,
53       dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM,
54       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,
55       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,
56       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,
57       dbo.Person_Skill.MandarinLevel AS MandarinLevel,
58       dbo.Person_Skill.Language AS Language,
59       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,
60       dbo.Person_Skill.ComputerLevel AS ComputerLevel,
61       dbo.Person_EmployPurpose.JobType AS JobType,
62       dbo.Person_EmployPurpose.Vocation AS Vocation,
63       dbo.Person_EmployPurpose.JobPlace AS JobPlace,
64       dbo.Person_EmployPurpose.Salary AS Salary,
65       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,
66       dbo.Person_EmployPurpose.CorpType AS CorpType,
67       dbo.Person_EmployPurpose.Job AS RequireJob,
68       dbo.Graduater_Business.EmployType AS EmployType,
69       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,
70       dbo.Graduater_Business.EmployCorpType AS EmployCorpType,
71 FROM dbo.Person_BasicInfo INNER JOIN
72       dbo.Graduater_Business ON
73       dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID INNER JOIN
74       dbo.Graduater_GraduaterRegist ON
75       dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
76        INNER JOIN
77       dbo.Person_Contact ON
78       dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN
79       dbo.Person_Skill ON
80       dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN
81       dbo.Person_EmployPurpose ON
82       dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
83 GO
84 CREATE UNIQUE CLUSTERED INDEX Query_NoEmployRegist_Ind
85      ON Query_NoEmployRegist(GraduatedDate, StudentSourceCode,RegistTime,ApproveTime,PrintTime,ComeFrom)
86 SET ANSI_NULLS ON
87 GO
88 SET QUOTED_IDENTIFIER ON
89 GO

看來還得優(yōu)化，希望各位博友指點(diǎn)一下！

二、索引視圖的學(xué)習(xí)總結(jié)

       1、什么是索引視圖？

在視圖上創(chuàng)建唯一的聚集索引及非聚集索引，來提高最復(fù)雜的查詢的數(shù)據(jù)訪問性能。具有唯一的聚集索引的視圖即為索引視圖。從數(shù)據(jù)庫管理系統(tǒng) (DBMS) 的角度看來，視圖是對(duì)數(shù)據(jù)（一種元數(shù)據(jù)類型）的一種描述。當(dāng)創(chuàng)建了一個(gè)典型視圖時(shí)，通過封裝一個(gè) SELECT 語句（定義一個(gè)結(jié)果集來表示為虛擬表）來定義元數(shù)據(jù)。當(dāng)在另一個(gè)查詢的 FROM 子句中引用視圖時(shí)，將從系統(tǒng)目錄檢索該元數(shù)據(jù)，并替代該視圖的引用擴(kuò)展元數(shù)據(jù)。視圖擴(kuò)展之后，SQL Server 查詢優(yōu)化器會(huì)為執(zhí)行查詢編譯一個(gè)執(zhí)行計(jì)劃。查詢優(yōu)化器會(huì)搜索針對(duì)某個(gè)查詢的一組可能的執(zhí)行計(jì)劃，并根據(jù)對(duì)執(zhí)行每個(gè)查詢計(jì)劃所需的實(shí)際時(shí)間的估計(jì)，選擇所能找到的成本最低的計(jì)劃。

對(duì)于非索引視圖，解析查詢所必需的視圖部分會(huì)在運(yùn)行時(shí)被具體化。任何計(jì)算（比如：聯(lián)接或聚合）都在每個(gè)引用視圖的查詢執(zhí)行時(shí)完成1。在視圖上創(chuàng)建了唯一的聚集索引后，該視圖的結(jié)果集隨即被具體化，并保存在數(shù)據(jù)庫的物理存儲(chǔ)中，從而在執(zhí)行時(shí)節(jié)省了執(zhí)行這一高成本操作的開銷。

在查詢執(zhí)行中，可通過兩種方式使用索引視圖。查詢可直接引用索引視圖，或者更重要的是，如果查詢優(yōu)化器確定該視圖可替換成本最低的查詢計(jì)劃中的部分或全部查詢，那么就可以選定它。在第二種情況中，使用索引視圖替代基礎(chǔ)表及其一般索引。不必在查詢中引用視圖以使查詢優(yōu)化器在查詢執(zhí)行時(shí)使用該視圖。這使得現(xiàn)有的應(yīng)用程序可以從新創(chuàng)建的索引視圖中受益，而不必進(jìn)行更改。

索引視圖可通過以下方式提高查詢性能：

　　（1）可預(yù)先計(jì)算聚合并將其保存在索引中，從而在查詢執(zhí)行時(shí)，最小化高成本的計(jì)算。

　　（2）可預(yù)先聯(lián)接各個(gè)表并保存最終獲得的數(shù)據(jù)集。

　　（3）可保存聯(lián)接或聚合的組合。

　　2、應(yīng)用索引視圖的優(yōu)點(diǎn)

　　在實(shí)施索引視圖前，分析數(shù)據(jù)庫工作負(fù)荷。運(yùn)用查詢及各種相關(guān)工具（比如：SQL Profiler）方面的知識(shí)來確定可從索引視圖獲益的查詢。頻繁發(fā)生聚合和聯(lián)接的情況最適合使用索引視圖。無論是否頻繁發(fā)生，只要某個(gè)查詢需要很長(zhǎng)的響應(yīng)時(shí)間，同時(shí)快速獲得響應(yīng)的開銷很高，那么就適合使用索引視圖。

不是所有的查詢都能從索引視圖中獲益。與一般索引類似，如果未使用索引視圖，就無法從中受益。在這種情況下，不僅無法實(shí)現(xiàn)性能改善，而且會(huì)在磁盤空間、維護(hù)和優(yōu)化方面產(chǎn)生額外的成本。然而，當(dāng)使用索引視圖時(shí)，可大大改善（在數(shù)量級(jí)上）數(shù)據(jù)訪問。這是因?yàn)椴樵儍?yōu)化器使用存儲(chǔ)在索引視圖（大幅降低了查詢執(zhí)行的成本）中預(yù)先計(jì)算的結(jié)果。

　　從查詢類型和模式方面來看，獲益的應(yīng)用程序一般包含：

　　• 大型表的聯(lián)接和聚合

　　• 查詢的重復(fù)模式

　　• 幾組相同或重疊的列上的重復(fù)聚合

　　• 相同鍵上相同表的重復(fù)聯(lián)接

　　• 以上各項(xiàng)的組合

　　查詢優(yōu)化器如何使用索引視圖

　　SQL Server 查詢優(yōu)化器自動(dòng)決定何時(shí)對(duì)給定的查詢執(zhí)行使用索引視圖。不必在查詢中直接引用視圖以供優(yōu)化器在查詢執(zhí)行計(jì)劃中使用。所以，現(xiàn)有的應(yīng)用程序可運(yùn)用索引視圖，而不用更改應(yīng)用程序本身；只是必須創(chuàng)建索引視圖。

　　優(yōu)化器考慮事項(xiàng)

　　查詢優(yōu)化器通過考慮幾個(gè)條件來決定索引視圖能否涵蓋整個(gè)或部分查詢。這些條件對(duì)應(yīng)查詢中的一個(gè) FROM 子句并由下列這幾個(gè)部分組成：

　　• 查詢 FROM 子句中的表必須是索引視圖 FROM 子句中的表的超集。

　　• 查詢中的聯(lián)接條件必須是視圖中的聯(lián)接條件的超集。

　　• 查詢中的聚合列必須可從視圖中的聚合列的子集派生。

　　• 查詢選擇列表中的所有表達(dá)式必須可從視圖選擇列表或未包含在視圖定義中的表派生。

　　• 如果與其他謂詞所匹配的行的超集相匹配，那么該謂詞將歸入另一個(gè)謂詞。例如，“T.a=10”歸入“T.a=10 and T.b=20”。任何謂詞都可歸入其自身。視圖中限　　　　　　　　制表值的那部分謂詞必須歸入查詢中限制相同表的那部分謂詞。此外，必須以 SQL Server 可驗(yàn)證的方式實(shí)現(xiàn)這一點(diǎn)。

　　• 屬于視圖定義中的表的查詢搜索條件謂詞的所有列必須出現(xiàn)在下列視圖定義的一項(xiàng)或多項(xiàng)中：

　　(1) 一個(gè) GROUP BY 列表。

　　(2) 視圖選擇列表（如不存在 GROUP BY）。

　　(3) 視圖定義中相同或等價(jià)的謂詞。

　　情況 (1) 和 (2) 允許 SQL Server 對(duì)視圖的列應(yīng)用查詢謂詞，以便進(jìn)一步限制視圖的列。情況 (3) 比較特殊。在這種情況下，不需要對(duì)列進(jìn)行篩選，因此該列不必出現(xiàn)在視圖中。如果查詢不止包含一個(gè) FROM 子句（子查詢、派生表、UNION），優(yōu)化器可能選擇幾個(gè)索引視圖來處理查詢，并將它們應(yīng)用到不同 FROM 子句。

　　3、創(chuàng)建索引視圖

　　創(chuàng)建索引視圖所需的步驟對(duì)于視圖的成功執(zhí)行至關(guān)重要。

　　（1）針對(duì)將在視圖中引用的所有現(xiàn)有表，確認(rèn) ANSI_NULLS 的設(shè)置正確無誤。

　　（2）創(chuàng)建任何新表之前，確認(rèn)對(duì)下表所示的當(dāng)前會(huì)話正確設(shè)置了 ANSI_NULLS。

　　（3）創(chuàng)建任何新表之前，確認(rèn)對(duì)下表所示的當(dāng)前會(huì)話正確設(shè)置了 ANSI_NULLS 和 QUOTED_IDENTIFIER。

　　（4）確認(rèn)視圖定義具有確定性。

　　（5）使用 WITH SCHEMABINDING 選項(xiàng)創(chuàng)建視圖。

　　（6）在視圖上創(chuàng)建唯一的聚集索引之前，確認(rèn)會(huì)話的 SET 選項(xiàng)的設(shè)置正確無誤。

　　（7）在視圖上創(chuàng)建唯一的聚集索引。

　　（8）可用 OBJECTPROPERTY 函數(shù)檢查現(xiàn)有表或視圖上 ANSI_NULLS 和 QUOTED_IDENTIFIER 的值。

　　4、索引視圖的常見問題

　　（1）為何對(duì)可創(chuàng)建索引的視圖類型存在限制？

為了確保在邏輯上可對(duì)視圖進(jìn)行增量維護(hù)，限制創(chuàng)建維護(hù)成本較高的視圖，并限制 SQL Server 實(shí)施的復(fù)雜性。較大的視圖集不具有確定性并與內(nèi)容相關(guān)；其內(nèi)容的“更改”獨(dú)立于 DML 操作。無法對(duì)這些內(nèi)容進(jìn)行索引。在其定義中調(diào)用 GETDATE 或 SUSER_SNAME 的任何視圖就屬于這類視圖。

　　（2）視圖上的第一個(gè)索引為何必須為 CLUSTERED 和 UNIQUE？

必須為 UNIQUE 以便在維護(hù)索引視圖期間，輕松地按鍵值查找視圖中的記錄，并阻止創(chuàng)建帶有重復(fù)項(xiàng)目的視圖（要求維護(hù)特殊的邏輯）。必須為 CLUSTERED，因?yàn)橹挥芯奂饕拍茉趶?qiáng)制唯一性的同時(shí)存儲(chǔ)行。

　　（3）為何查詢優(yōu)化器不選取我的索引視圖用于查詢計(jì)劃？

優(yōu)化器不選取索引視圖主要有三種原因：

　　• 使用 SQL Server Enterprise 或 Developer 版本之外的其他版本。只有 Enterprise 和 Developer 版本才支持自動(dòng)的查詢對(duì)索引視圖匹配。按名稱引用索引視圖并包含 NOEXPAND 提示，讓查詢處理器使用所有其他版本中的索引視圖。

　　• 使用索引視圖的成本可能超出從基表獲取數(shù)據(jù)的成本，或者查詢過于簡(jiǎn)單，使得針對(duì)基表的查詢的速度既快又容易查找。當(dāng)在較小的表上定義索引視圖時(shí)，經(jīng)常會(huì)發(fā)生這種情況。如要強(qiáng)制查詢處理器使用索引視圖，那么可使用 NOEXPAND 提示。如果最初不通過顯式的方式引用視圖，這樣做就可能要求重新編寫查詢。您可獲得帶有 NOEXPAND 的查詢的實(shí)際成本，并將之與不引用該視圖的查詢計(jì)劃的實(shí)際成本相比較。如果兩者的成本相近，那么您就可以認(rèn)定用不用索引視圖都不重要。

　　• 查詢優(yōu)化器不將查詢與索引視圖相匹配。重新檢查視圖和查詢的定義，確保兩者在結(jié)構(gòu)上可相匹配。CASTS、converts 以及其他在邏輯上不會(huì)更改查詢結(jié)果的表達(dá)式可能會(huì)阻止匹配。另外，表達(dá)式規(guī)范化和等價(jià)以及 SQL Server 執(zhí)行的歸入測(cè)試方面存在一些限制。可能無法顯示某些等價(jià)表達(dá)式是相同的，或者邏輯上被其他表達(dá)式歸入的表達(dá)式被真正歸入，因此可能會(huì)錯(cuò)失匹配。

　　（4）每周更新一次數(shù)據(jù)倉庫。索引視圖使查詢速度大大提升，卻降低了每周更新的速度？該怎么辦呢？

　　　　可以考慮在每周更新前丟棄索引視圖，更新完后再重新創(chuàng)建。

　　（5）視圖存在重復(fù)項(xiàng)目，而想對(duì)其進(jìn)行維護(hù)。該怎么辦呢？

　　可以考慮創(chuàng)建一個(gè)視圖，按您所要的視圖中的所有列和表達(dá)式進(jìn)行分組，并添加一個(gè) COUNT_BIG(*) 列，然后在組合的列上創(chuàng)建一個(gè)唯一的聚集索引。

　　分組過程可確保唯一性。雖然不是完全相同的視圖，但可以滿足您的需要。

　　（6）在一個(gè)視圖上定義了另一個(gè)視圖。SQL Server 不讓索引頂級(jí)視圖。該怎么辦呢？

　　可以考慮手動(dòng)將嵌套視圖的定義擴(kuò)展到頂級(jí)視圖，然后對(duì)其進(jìn)行索引（索引最低層的視圖，或者不索引該視圖）。

　　（7）為何一定要對(duì)索引視圖定義 WITH SCHEMABINDING？

　　　　• 使用 schemaname.objectname 明確識(shí)別視圖所引用的所有對(duì)象，而不管是哪個(gè)用戶訪問該視圖，同時(shí)

　　　　• 不會(huì)以導(dǎo)致視圖定義非法或強(qiáng)制 SQL Server 在該視圖上重新創(chuàng)建索引的方式，更改視圖定義中所引用的對(duì)象。

　　（8）為何不能在索引視圖中使用 OUTER JOIN？

　　  當(dāng)將數(shù)據(jù)插入基表時(shí)，行會(huì)在邏輯上從基于 OUTER JOIN 的索引視圖上消失。這會(huì)使執(zhí)行 OUTER JOIN 視圖的增量更新變得相對(duì)復(fù)雜，而執(zhí)行性能將比基于標(biāo)準(zhǔn) (INNER) JOIN 的視圖慢一些。

　　更多資料：http://www.microsoft.com/china/technet/prodtechnol/sql/2005/ipsql05iv.mspx

上一篇：CuteEditor6 破解注冊(cè)文件生成

下一篇：jQuery 1.4中文API文檔發(fā)布，chm格式