索引視圖是否實體存儲在資料庫中以及使用索引視圖的一些見解
前言
這個話題我本來是寫在文章裡沒有寫在随筆裡的，不過趕腳不寫在随筆裡其他人就看不到了，因為小弟對視圖的認識不深
希望寫在随筆裡讓大家也讨論一下這個話題
小弟在文章的結尾會把我們公司系統使用索引視圖的情況告訴大家，希望大家也把你們在系統中如何應用索引視圖的在評論中分享一下
讓小弟也學習一下，因為小弟對于索引視圖的認識也是比較淺的
視圖的種類
先來看一下SQLSERVER中視圖的種類
1、标準視圖
2、索引視圖：加了索引，在SQL2000之後提供的新功能。在視圖上建立索引後，該視圖的結果集随機被具體化，并儲存在資料庫的實體存儲中。
對索引視圖建立的第一個索引必須是唯一聚集索引。建立了唯一聚集索引後，才可以建立其他非聚集索引。如果删除視圖，該視圖的索引也被删除，
若删除聚集索引，視圖的所有非聚集索引和自動建立的統計資訊也被删除。删除視圖的聚集索引将删除存儲的結果集，并且索引視圖會變為标準視圖
3、分區視圖：在一台或多台伺服器間水準連接配接一組成員表中的分區資料，使資料看起來像來自一個表。依據連接配接資料伺服器的不同，
他又分為本地分區視圖和分布式分區視圖。分布式分區視圖用于實作資料庫伺服器聯合
驗證
為了區分标準視圖和索引視圖，我們分别建立一張基本表、一個索引視圖、一個标準視圖，兩個視圖都是基于那個基本表
使用下面SQL語句建立測試環境
1 USE [pratice]
2 GO
3
4 --建表
5 CREATE TABLE [dbo].[Users](
6 [UserID] [int] IDENTITY(1,1) NOT NULL,
7 [UserName] [nvarchar](50) NULL,
8 [Age] [int] NULL,
9 [Gender] [bit] NULL,
10 [CreateTime] [datetime] NULL
11 ) ON [PRIMARY]
12 GO
13
14 --插入資料
15 INSERT INTO [dbo].[Users] ( [UserName], [Age], [Gender], [CreateTime] )
16 SELECT '啊文',20,1,'2012-05-01' UNION ALL
17 SELECT '寶文',23,0,'2012-05-05' UNION ALL
18 SELECT '張武',38,1,'2012-05-04' UNION ALL
19 SELECT '劉星',32,0,'2012-05-08' UNION ALL
20 SELECT '小青',27,1,'2012-06-01' UNION ALL
21 SELECT '王八',16,1,'2012-07-08' UNION ALL
22 SELECT '臭屁',42,1,'2012-09-02'
23 GO
24
25 SELECT * FROM [dbo].[Users]
26 GO
27
28 --------------------------------------------------------
29 --建立索引視圖
30 CREATE VIEW index_View_Users
31 WITH SCHEMABINDING
32 AS
33 SELECT UserID,UserName,Age,Gender,CreateTime FROM dbo.Users
34 GO
35
36 CREATE UNIQUE CLUSTERED INDEX CIX_View_Users ON index_View_Users(UserID)
37
38 SELECT * FROM index_View_Users
39
40
41 --建立标準視圖
42 CREATE VIEW stand_View_Users
43 AS
44 SELECT UserID,UserName,Age,Gender,CreateTime FROM dbo.Users
45 GO
46
47 SELECT * FROM stand_View_Users
48
49
50 --------------------------------------------------------- View Code
1 CREATE TABLE [dbo].[DBCCResult](
2 [PageFID] [nvarchar](200) NULL,
3 [PagePID] [nvarchar](200) NULL,
4 [IAMFID] [nvarchar](200) NULL,
5 [IAMPID] [nvarchar](200) NULL,
6 [ObjectID] [nvarchar](200) NULL,
7 [IndexID] [nvarchar](200) NULL,
8 [PartitionNumber] [nvarchar](200) NULL,
9 [PartitionID] [nvarchar](200) NULL,
10 [iam_chain_type] [nvarchar](200) NULL,
11 [PageType] [nvarchar](200) NULL,
12 [IndexLevel] [nvarchar](200) NULL,
13 [NextPageFID] [nvarchar](200) NULL,
14 [NextPagePID] [nvarchar](200) NULL,
15 [PrevPageFID] [nvarchar](200) NULL,
16 [PrevPagePID] [nvarchar](200) NULL
17 ) ON [PRIMARY]
18 --TRUNCATE TABLE [dbo].[DBCCResult]
19 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,index_View_Users,-1) ')
20
21 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 而标準視圖是沒有資料頁存儲視圖資料的
1 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,stand_View_Users,-1) ')
2
3 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 1 消息 5239，級别 16，狀态 1，第 1 行
2 無法處理對象 ID 487672785 (對象 'stand_View_Users')，因為此 DBCC 指令不支援此類型的對象。
3
4 (0 行受影響) 我們看一下資料頁37398裡的内容，資料頁裡存儲的内容跟基本表是一樣的
1 DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理者聯系。
2
3 PAGE: (1:37398)
4
5
6 BUFFER:
7
8
9 BUF @0x03D91AFC
10
11 bpage = 0x1B706000 bhash = 0x00000000 bpageno = (1:37398)
12 bdbid = 5 breferences = 0 bUse1 = 16885
13 bstat = 0x1c0000b blog = 0x1212121b bnext = 0x00000000
14
15 PAGE HEADER:
16
17
18 Page @0x1B706000
19
20 m_pageId = (1:37398) m_headerVersion = 1 m_type = 1
21 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
22 m_objId (AllocUnitId.idObj) = 477 m_indexId (AllocUnitId.idInd) = 256
23 Metadata: AllocUnitId = 72057594069188608
24 Metadata: PartitionId = 72057594058309632 Metadata: IndexId = 1
25 Metadata: ObjectId = 471672728 m_prevPage = (0:0) m_nextPage = (0:0)
26 pminlen = 21 m_slotCnt = 7 m_freeCnt = 7858
27 m_freeData = 320 m_reservedCnt = 0 m_lsn = (3043:16246:39)
28 m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
29 m_tornBits = 0
30
31 Allocation Status
32
33 GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
34 PFS (1:32352) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
35 ML (1:7) = NOT MIN_LOGGED
36
37 Slot 0 Offset 0x60 Length 32
38
39 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
40
41 Memory Dump @0x0823C060
42
43 00000000: 30001500 01000000 14000000 01000000 †0...............
44 00000010: 0044a000 000500e0 01002000 4a558765 †.D........ .JU.e
45
46 Slot 0 Column 0 Offset 0x4 Length 4
47
48 UserID = 1
49
50 Slot 0 Column 1 Offset 0x1c Length 4
51
52 UserName = 啊文
53
54 Slot 0 Column 2 Offset 0x8 Length 4
55
56 Age = 20
57
58 Slot 0 Column 3 Offset 0xc Length 1 (Bit position 0)
59
60 Gender = 1
61
62 Slot 0 Column 4 Offset 0xd Length 8
63
64 CreateTime = 05 1 2012 12:00AM
65
66 Slot 1 Offset 0x80 Length 32
67
68 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
69
70 Memory Dump @0x0823C080
71
72 00000000: 30001500 02000000 17000000 00000000 †0...............
73 00000010: 0048a000 000500e0 01002000 9d5b8765 †.H........ ..[.e
74
75 Slot 1 Column 0 Offset 0x4 Length 4
76
77 UserID = 2
78
79 Slot 1 Column 1 Offset 0x1c Length 4
80
81 UserName = 寶文
82
83 Slot 1 Column 2 Offset 0x8 Length 4
84
85 Age = 23
86
87 Slot 1 Column 3 Offset 0xc Length 1 (Bit position 0)
88
89 Gender = 0
90
91 Slot 1 Column 4 Offset 0xd Length 8
92
93 CreateTime = 05 5 2012 12:00AM
94
95 Slot 2 Offset 0xa0 Length 32
96
97 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
98
99 Memory Dump @0x0823C0A0
100
101 00000000: 30001500 03000000 26000000 01000000 †0.......&.......
102 00000010: 0047a000 000500e0 01002000 205f666b †.G........ . _fk
103
104 Slot 2 Column 0 Offset 0x4 Length 4
105
106 UserID = 3
107
108 Slot 2 Column 1 Offset 0x1c Length 4
109
110 UserName = 張武
111
112 Slot 2 Column 2 Offset 0x8 Length 4
113
114 Age = 38
115
116 Slot 2 Column 3 Offset 0xc Length 1 (Bit position 0)
117
118 Gender = 1
119
120 Slot 2 Column 4 Offset 0xd Length 8
121
122 CreateTime = 05 4 2012 12:00AM
123
124 Slot 3 Offset 0xc0 Length 32
125
126 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
127
128 Memory Dump @0x0823C0C0
129
130 00000000: 30001500 04000000 20000000 00000000 †0....... .......
131 00000010: 004ba000 000500e0 01002000 18521f66 †.K........ ..R.f
132
133 Slot 3 Column 0 Offset 0x4 Length 4
134
135 UserID = 4
136
137 Slot 3 Column 1 Offset 0x1c Length 4
138
139 UserName = 劉星
140
141 Slot 3 Column 2 Offset 0x8 Length 4
142
143 Age = 32
144
145 Slot 3 Column 3 Offset 0xc Length 1 (Bit position 0)
146
147 Gender = 0
148
149 Slot 3 Column 4 Offset 0xd Length 8
150
151 CreateTime = 05 8 2012 12:00AM
152
153 Slot 4 Offset 0xe0 Length 32
154
155 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
156
157 Memory Dump @0x0823C0E0
158
159 00000000: 30001500 05000000 1b000000 01000000 †0...............
160 00000010: 0063a000 000500e0 01002000 0f5c5297 †.c........ ..\R.
161
162 Slot 4 Column 0 Offset 0x4 Length 4
163
164 UserID = 5
165
166 Slot 4 Column 1 Offset 0x1c Length 4
167
168 UserName = 小青
169
170 Slot 4 Column 2 Offset 0x8 Length 4
171
172 Age = 27
173
174 Slot 4 Column 3 Offset 0xc Length 1 (Bit position 0)
175
176 Gender = 1
177
178 Slot 4 Column 4 Offset 0xd Length 8
179
180 CreateTime = 06 1 2012 12:00AM
181
182 Slot 5 Offset 0x100 Length 32
183
184 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
185
186 Memory Dump @0x0823C100
187
188 00000000: 30001500 06000000 10000000 01000000 †0...............
189 00000010: 0088a000 000500e0 01002000 8b736b51 †.......... ..skQ
190
191 Slot 5 Column 0 Offset 0x4 Length 4
192
193 UserID = 6
194
195 Slot 5 Column 1 Offset 0x1c Length 4
196
197 UserName = 王八
198
199 Slot 5 Column 2 Offset 0x8 Length 4
200
201 Age = 16
202
203 Slot 5 Column 3 Offset 0xc Length 1 (Bit position 0)
204
205 Gender = 1
206
207 Slot 5 Column 4 Offset 0xd Length 8
208
209 CreateTime = 07 8 2012 12:00AM
210
211 Slot 6 Offset 0x120 Length 32
212
213 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
214
215 Memory Dump @0x0823C120
216
217 00000000: 30001500 07000000 2a000000 01000000 †0.......*.......
218 00000010: 00c0a000 000500e0 01002000 ed81415c †.......... ...A\
219
220 Slot 6 Column 0 Offset 0x4 Length 4
221
222 UserID = 7
223
224 Slot 6 Column 1 Offset 0x1c Length 4
225
226 UserName = 臭屁
227
228 Slot 6 Column 2 Offset 0x8 Length 4
229
230 Age = 42
231
232 Slot 6 Column 3 Offset 0xc Length 1 (Bit position 0)
233
234 Gender = 1
235
236 Slot 6 Column 4 Offset 0xd Length 8
237
238 CreateTime = 09 2 2012 12:00AM
239
240
241 DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理者聯系。 View Code
我們再向索引視圖插入更多資料使他出現聚集索引頁
1 --插入資料
2 INSERT INTO index_View_Users ( [UserName], [Age], [Gender], [CreateTime] )
3 SELECT '啊文',20,1,'2012-05-01' UNION ALL
4 SELECT '寶文',23,0,'2012-05-05' UNION ALL
5 SELECT '張武',38,1,'2012-05-04' UNION ALL
6 SELECT '劉星',32,0,'2012-05-08' UNION ALL
7 SELECT '小青',27,1,'2012-06-01' UNION ALL
8 SELECT '王八',16,1,'2012-07-08' UNION ALL
9 SELECT '臭屁',42,1,'2012-09-02'
10 GO 100 再看一下表中頁面情況
1 --TRUNCATE TABLE [dbo].[DBCCResult]
2 INSERT INTO DBCCResult EXEC ('DBCC IND(pratice,index_View_Users,-1) ')
3
4 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 我們看一下13571聚集索引頁的内容
1 DBCC SHOWCONTIG(index_View_Users)
2 GO
3
4 DBCC SHOWCONTIG(stand_View_Users)
5 GO 1 SELECT * FROM sys.[dm_db_partition_stats] WHERE [object_id]=OBJECT_ID('index_View_Users') 小結
從上面的測試結果可以看出，索引視圖已經跟基本表一樣存儲到硬碟中而且占用資料庫空間
并且聚集索引頁、資料頁這些跟基本表的沒有什麼差別
插入資料到索引視圖究竟做了什麼操作？
我這裡主要對基本表、标準視圖、索引視圖的資料插入和資料更新進行簡單分析（主要看執行計劃），看一下索引視圖的更新究竟做了什麼？
我在下面的測試裡将執行計劃導出來，大家可以把sqlplan檔案拖到SSMS裡就可以看到執行計劃了
插入資料到基本表
1 USE [pratice]
2 GO
3
4 --插入資料到基本表
5 INSERT INTO Users ( [UserName], [Age], [Gender], [CreateTime] )
6 VALUES ( N'nihao', -- UserName - nvarchar(50)
7 30, -- Age - int
8 1, -- Gender - bit
9 '2013-08-29 03:40:36' -- CreateTime - datetime
10 ) sqlplan下載下傳：https://files.cnblogs.com/lyhabc/%E6%8F%92%E5%85%A5%E5%88%B0%E5%9F%BA%E6%9C%AC%E8%A1%A8.rar
插入資料到索引視圖
1 USE [pratice]
2 GO
3
4 --插入資料到索引視圖
5 INSERT INTO index_View_Users ( [UserName], [Age], [Gender], [CreateTime] )
6 VALUES ( N'nihao', -- UserName - nvarchar(50)
7 30, -- Age - int
8 1, -- Gender - bit
9 '2013-08-29 03:40:36' -- CreateTime - datetime
10 ) sqlplan下載下傳：https://files.cnblogs.com/lyhabc/%E6%8F%92%E5%85%A5%E5%88%B0%E7%B4%A2%E5%BC%95%E8%A7%86%E5%9B%BE.rar
插入資料到标準視圖
1 USE [pratice]
2 GO
3
4 --插入資料到标準視圖
5 INSERT INTO stand_View_Users ( [UserName], [Age], [Gender], [CreateTime] )
6 VALUES ( N'nihao', -- UserName - nvarchar(50)
7 30, -- Age - int
8 1, -- Gender - bit
9 '2013-08-29 03:40:36' -- CreateTime - datetime
10 ) sqlplan下載下傳：https://files.cnblogs.com/lyhabc/%E6%8F%92%E5%85%A5%E5%88%B0%E6%A0%87%E5%87%86%E8%A7%86%E5%9B%BE.rar
小結：
插入記錄
1、插入記錄到基本表，會同時插入記錄到索引視圖
2、插入記錄到索引視圖，會同時插入記錄到基本表
3、插入記錄到标準視圖，會同時插入記錄到基本表和索引視圖
因為标準視圖沒有實體存儲到硬碟中，是以隻插入資料到索引視圖和基本表中
更新索引視圖的資料究竟做了什麼操作？
更新基本表資料
1 USE [pratice]
2 GO
3 --更新基本表資料
4 UPDATE Users SET age=20 WHERE [UserID]=12 sqlplan下載下傳：https://files.cnblogs.com/lyhabc/%E6%9B%B4%E6%96%B0%E5%9F%BA%E6%9C%AC%E8%A1%A8%E6%95%B0%E6%8D%AE.rar
更新索引視圖資料
1 USE [pratice]
2 GO
3 --更新索引視圖資料
4 UPDATE index_View_Users SET age=20 WHERE [UserID]=12 sqlplan下載下傳：https://files.cnblogs.com/lyhabc/%E6%9B%B4%E6%96%B0%E7%B4%A2%E5%BC%95%E8%A7%86%E5%9B%BE%E6%95%B0%E6%8D%AE.rar
更新标準視圖資料
1 USE [pratice]
2 GO
3 --更新标準視圖資料
4 UPDATE stand_View_Users SET age=20 WHERE [UserID]=12 sqlplan下載下傳：https://files.cnblogs.com/lyhabc/%E6%9B%B4%E6%96%B0%E6%A0%87%E5%87%86%E8%A7%86%E5%9B%BE%E6%95%B0%E6%8D%AE.rar
小結：
更新記錄
1、更新基本表記錄，會同時更新索引視圖記錄
2、更新索引視圖記錄，會同時更新基本表記錄
3、更新标準視圖記錄，會同時更新基本表和索引視圖記錄
總結
從上面的實驗可以看出無論是插入記錄還是更新記錄，基本表和索引視圖都需要進行同步插入和更新記錄，如果索引視圖引用多個基本表
那麼這個開銷也是不小的
我們公司的系統中的資料庫表是沒有一個外鍵跟視圖的！！
我的猜想：
1、增加備援字段，盡量不使用外鍵，其實增加備援字段也可以解決視圖問題，因為視圖可以引用多張表
2、視圖在插入記錄和更新記錄的時候會增加額外開銷
3、如果需要級聯更新，就使用事務更新兩個表，插入記錄也是一樣
4、報表也會引用多張表，那麼使用多表連接配接的SQL語句來解決一張報表引用多張基本表的問題，不用視圖
補充：
删除了聚集索引之後，索引視圖是否會變成标準視圖呢？
1 DROP INDEX CIX_View_Users ON index_View_Users 1 --插入資料到基本表
2 INSERT INTO Users ( [UserName], [Age], [Gender], [CreateTime] )
3 VALUES ( N'nihao', -- UserName - nvarchar(50)
4 30, -- Age - int
5 1, -- Gender - bit
6 '2013-08-29 03:40:36' -- CreateTime - datetime
7 ) 可以看到索引視圖已經沒有了
查詢視圖資料的時候還可以查詢，證明索引視圖已經退化為标準視圖了
如有不對的地方，歡迎大家拍磚o(∩_∩)o