在SQL2005中使用索引视图来提高查询效率

总所周知,视图是很方便查询数据的,其易维护性,十分被大家看好,可是效率问题一直让人担忧
在SQL2005中,可以使用索引视图,达到两全其美的办法,索引视图就是具有唯一的聚集索引的视图,通过这样的视图查询数据库,效率将是普通视图的10倍以上
在这里 我就向大家介绍一下这个索引视图的建立和使用方法

首先,建立两个表格 一个表格是品牌表 一个是产品表 我相信大家一定也做过类似的案例!

 

1. Create TABLE [dbo].[Brand](   
2.  [ID] [int] IDENTITY(1,1) NOT NULL,   
3.  [BrandName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Brand_BrandName]  DEFAULT (''),   
4.  [Del] [bit] NOT NULL CONSTRAINT [DF_Brand_Del]  DEFAULT ((0)),   
5.  CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED    
6. (   
7.  [ID] ASC  
8. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]   
9. ) ON [PRIMARY]    

Create TABLE [dbo].[Product](   

 [PID] [int] IDENTITY(1,1) NOT NULL,   

 [ProductName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Product_ProductName]  DEFAULT (''),   

 [ProductDes] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Product_ProductDes]  DEFAULT (''),   

 [BrandID] [int] NOT NULL CONSTRAINT [DF_Product_BrandID]  DEFAULT ((0)),   

 [hidden] [bit] NOT NULL CONSTRAINT [DF_Product_hidden]  DEFAULT ((0)),   

 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED    

(   

 [PID] ASC  

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]   

) ON [PRIMARY]  

 

你可以使用语句在表中填充一些测试数据

表建立好以后 很明显是一对多的关系表 品牌对应产品 (其中Brand.ID = Product.BrandName) 按照以前的方法 可以这样获取数据
1. 在Product表中也建立BrandName字段是速度最快的方法 但是缺点是造成数据冗余 不方便维护 例如修改品牌名的时候还要到产品表里做更新
2. 第二个方法是列出产品的时候 依次按照产品表中的 BrandID 得到品牌表里的 BrandName字段 增加查询数据库次数
这个时候就要应用到试图了 由于非索引视图每次都要到到两个表里去查询 速度很慢 所以现在我们开始建立索引视图
这个例子建立索引视图的SQL代码如下

 

SET ANSI_NULLS ON  

GO   

SET QUOTED_IDENTIFIER ON  

GO   

  

Create VIEW [dbo].[ProductList] WITH SCHEMABINDING   

AS  

Select dbo.Brand.BrandName, dbo.Product.ProductName, dbo.Product.PID FROM dbo.Brand INNER JOIN dbo.Product ON dbo.Brand.ID = dbo.Product.BrandID where dbo.Product.Hidden = 0   

GO   

  

SET ANSI_NULLS OFF  

GO   

SET QUOTED_IDENTIFIER OFF  

GO   

 

建立索引视图之前 视图必须要和数据库绑定 所以我们使用使用WITH SCHEMABINDING 参数建立视图(注意当视图更改的时候 需要对该视图重新绑定操作)
下一步我们为这个视图建立一个唯一的聚集索引 取名为 PK_ProductID  添加字段PID为索引键列(注意当视图更改的时候 索引将消失)
如果你还经常使用Where筛选条件或者是排序的话 也可以再建立几个多个字段的非聚集索引
建立索引的代码如下:
 

Create UNIQUE CLUSTERED INDEX [PK_ProductID] ON [dbo].[ProductList]    

(   

    [PID] ASC  

)WITH (SORT_IN_TEMPDB = OFF, Drop_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 

然后我们就来使用这个索引视图

select BrandName, ProductName    

from ProductList WITH (NOEXPAND) order by PID desc  

其中的NOEXPAND参数可以让你的查询速度如同在一个基础表上一般飞快! 当您的查询够复杂 数据量百万级 而你有建立了合理的索引就会体验到索引视图的飞快了

你可以通过如下语句来查看视图中索引的使用情况

Select (select name from sys.sysindexes where id = object_id and indid = index_id) as indexname, * FROM sys.dm_db_index_usage_stats where object_name(object_id)='ProductList'