1.视图索引(Indexed View)定义
用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。
2.性能测试
create table tab (ID intidentity(1,1), ST char(1), DESCR varchar(10))
alter table tab add CONSTRAINT pk_tabprimary key (ID)
-- 产生10万笔记录.
-- 无视图索引时, 对基表聚合运算.
select ST,COUNT(*) cs fromtab where DESCR like '%2%' group by ST
CPU time = 1766 ms , elapsed time = 1917 ms. Clustered Index Scan(OBJECT:([ cxcai ].[ dbo ].[tab].[ pk_tab ])
-- 新建视图索引.
create view dbo.vtab WITHSCHEMABINDING as
select ST,COUNT_BIG(*) cs fromdbo.tabwhere DESCR like '%2%' group by ST
create unique clustered index idx_vtab on vtab(ST)
-- 有视图索引时, 对基表聚合运算.
select ST,COUNT(*) cs fromtab where DESCR like '%2%' group by ST
CPU time = 0 ms , elapsed time = 0 ms. Clustered Index Scan(OBJECT:([ cxcai ].[ dbo ].[ vtab ].[ idx_vtab ]))
3. 代价
3.1 增加 DML 执行成本 .
3.2 占用磁盘空间 .
用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。
2.性能测试
create table tab (ID intidentity(1,1), ST char(1), DESCR varchar(10))
alter table tab add CONSTRAINT pk_tabprimary key (ID)
-- 产生10万笔记录.
-- 无视图索引时, 对基表聚合运算.
select ST,COUNT(*) cs fromtab where DESCR like '%2%' group by ST
CPU time = 1766 ms , elapsed time = 1917 ms. Clustered Index Scan(OBJECT:([ cxcai ].[ dbo ].[tab].[ pk_tab ])
-- 新建视图索引.
create view dbo.vtab WITHSCHEMABINDING as
select ST,COUNT_BIG(*) cs fromdbo.tabwhere DESCR like '%2%' group by ST
create unique clustered index idx_vtab on vtab(ST)
-- 有视图索引时, 对基表聚合运算.
select ST,COUNT(*) cs fromtab where DESCR like '%2%' group by ST
CPU time = 0 ms , elapsed time = 0 ms. Clustered Index Scan(OBJECT:([ cxcai ].[ dbo ].[ vtab ].[ idx_vtab ]))
3. 代价
3.1 增加 DML 执行成本 .
3.2 占用磁盘空间 .