文章出處
View Code
View Code
View Code
View Code
View Code
View Code
View Code
View Code
View Code
View Code
View Code
View Code
文章列表
索引視圖是否物理存儲在數據庫中以及使用索引視圖的一些見解
前言
這個話題我本來是寫在文章里沒有寫在隨筆里的，不過趕腳不寫在隨筆里其他人就看不到了，因為小弟對視圖的認識不深
希望寫在隨筆里讓大家也討論一下這個話題
小弟在文章的結尾會把我們公司系統使用索引視圖的情況告訴大家，希望大家也把你們在系統中如何應用索引視圖的在評論中分享一下
讓小弟也學習一下，因為小弟對于索引視圖的認識也是比較淺的
視圖的種類
先來看一下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 ---------------------------------------------------------
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 輸出了錯誤信息，請與系統管理員聯系。
我們再向索引視圖插入更多數據使他出現聚集索引頁
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下載：http://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下載：http://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下載：http://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下載：http://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
更新標準視圖數據
1 USE [pratice] 2 GO 3 --更新標準視圖數據 4 UPDATE stand_View_Users SET age=20 WHERE [UserID]=12
小結：
更新記錄
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
文章列表
全站熱搜
留言列表
