索引视图是否物理存储在数据库中以及使用索引视图的一些见解
索引视图是否物理存储在数据库中以及使用索引视图的一些见解
前言
这个话题我本来是写在文章里没有写在随笔里的，不过赶脚不写在随笔里其他人就看不到了，因为小弟对视图的认识不深
希望写在随笔里让大家也讨论一下这个话题
小弟在文章的结尾会把我们公司系统使用索引视图的情况告诉大家，希望大家也把你们在系统中如何应用索引视图的在评论中分享一下
让小弟也学习一下，因为小弟对于索引视图的认识也是比较浅的
视图的种类
先来看一下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下载：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
更新标准视图数据
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
