在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'    

转载于:https://www.cnblogs.com/shili_cn/archive/2010/12/15/1906809.html