数据库视图优化概念，索引视图，物化视图

参考：http://blog.csdn.net/eriato/article/details/41039985

http://blog.sina.com.cn/s/blog_71247658010183j6.html

（大概摘一些，，，，重点是了解这概念，用法可以慢慢找）

索引视图

索引视图有助于提高 T-SQL 的性能

Itzik Ben-Gan

T-SQL 是一种允许您以逻辑方式设计请求的语言。这里所说的“逻辑”的意思是，当编写查询时，您指定的是希望获得什么结果，而不是希望以何种方式 获得结果。对如何处理查询进行设计是查询优化器的工作。您所遇到的需要 T-SQL 解决方案的每个问题通常都有许多不同的解决方案，它们最终会返回同样的结果。在理想情况下，假设有两个执行同一任务的不同查询，优化器会为二者生成相同的执行计划－最优化的计划。SQL Server 开发组似乎正在朝这个方向努力。利用 SQL Server 的每个发行版本，优化器会设计出更高级、更有效的计划，执行同一任务的不同查询使用这个相同计划的可能性也在提高。

不过，在许多情况下，您编写查询的方式仍然会动态地影响查询的性能。为了使优化器在不同的计划中作出选择并找到一个高效的计划，您需要完成一些准备工作－也就是，创建索引。大多数人是将普通索引作为其数据库优化的一部分创建的，但我还没有看到索引视图的广泛实现。索引视图可以极大地提高查询性能，尤其在聚合数据的时候。在某些情况下，对视图进行创建并索引后，您甚至不需要更改引用基表的原始查询，这是因为优化器的智能程度足以使用那些索引。

下面我们将讨论使用索引视图来获得性能提高的两个问题。我假设您已经很熟悉索引视图的基本概念。（有关索引视图的详细信息，请参阅 Kalen Delaney 的 "Introducing Indexed Views" （2000 年 5 月发表，InstantDoc ID 8410）一文。）

关于新客户的问题

第一个问题涉及一个典型的市场营销请求－在每个期间（例如，月份），根据定单活动而定的新客户、现有客户和全部客户的数量分别是多少？某个月的新客户就是第一次下达定单的客户。现有客户是那些在以前的月份下达过定单的客户。您可以将 Northwind 数据库中的 Orders 表作为最初的基本数据。表 1（表 1）显示了预期输出的简化版本。创建解决方案并验证您的代码在逻辑上正确无误之后，可针对一个更大的 Orders 表测试其性能，您可以通过运行第 18 页清单 1 显示的代码，在 tempdb 中创建该表。

第 18 页清单 2 显示了我针对此问题的第一个解决方案。定义派生表 M 的最内部查询从 Orders 返回了不同的月份和客户组合。月份表示为指向当月第一天的一个 datetime 值。定义派生表 N 的查询使用 CASE 表达式，对于每个月份和客户组合，如果该客户在当月之前还没有下达过定单，则该表达式返回 1，否则，返回 0。也就是说，如果客户是新客户，CASE 表达式返回 1。最外部查询按月份对 N 中的行进行分组，并通过对 N 表中名为 new 的列中的所有 1 进行汇总，来计算新客户的总数。客户的总数就是该组中的行数 (COUNT(*))，因为该组中的每一行都代表一个不同的客户。现有客户的数量是客户总数减去新客户的数量。

当我编写此解决方案时，我并没有考虑性能，我只想到要用逻辑方式来编写该查询。在我的笔记本电脑上，对我在 tempdb 中创建的较大的 Orders 表运行该查询时，该查询发生了超过 137,000 次的逻辑读取，运行的时间长达 17 秒。普通索引并没有提供多少帮助－当我在 customerid 列和 orderdate 列上创建了组合索引后，该查询运行了 15 秒。看来，这个问题需要采用另一种方法，这次，我决定先考虑性能。

这个较大的 Orders 表会包括每个月份的每个客户的许多定单。如果您有一个只包含月份和客户的不同组合的汇总表，就可以编写一个更易于执行的查询。索引视图的实质就是对表进行汇总。请运行清单 3 中的代码，创建一个名为 Vymcusts 的索引视图，该视图只包含月份和客户的不同组合。注意，如果您希望对一个使用聚合的视图进行索引，则必须在该视图内包括 COUNT_BIG(*) 函数。如果 SQL Server 知道每组中的行数，就可以有效地维护索引视图。

接下来，我测试了几个不同的解决方案，所有方案执行起来都比第一个要好得多。我们来看一下我编写出的执行效果最好的解决方案。我创建了另一个名为 Vnewcusts 的视图，该视图包含每个客户的最小年/月份 (ym) 值：

CREATE VIEW Vnewcusts 
AS 
SELECT customerid, MIN(ym) AS min_ym 
FROM Vymcusts (NOEXPAND) 
GROUP BY customerid

客户的 ym 最小值就是该客户的第一份定单的月份。NOEXPAND 提示要求优化器将索引视图视为表，不要尝试使用来自基表 Orders 的索引。最后，我将 Vymcusts (VY) 左联接到了 Vnewcusts (VN)。如果 VY 和 VN 同时拥有同一个客户，并且 VY 中的当前月份是该客户的最小月份 (VY.ym = VN.min_ym)，则 VY 中的某一行会在 VN 中寻找匹配的行。如果该月份不是该客户的最小月份，则外部联接在 VN.customerid 中以及在 VN 的所有列中均返回 NULL。查询按 VY.ym 列对外部联接的结果进行分组。SQL Server 通过对 VN.customerid 中的非 Null 值进行计数来计算新客户的总数。清单 4 显示了这个最终的查询，该查询在我的笔记本电脑上只运行了 1 秒，仅发生了 280 次逻辑读取。这样的性能令人非常满意。

－（）

视图在数据库开发过程中是非常重要的，对提高查询速度有很大的提高。因此我们的学会创建视图，并且有效的使用视图。
（1）表准的SQL视图
标准视图比较简单，大家也都在使用，在此就不垒述了。
（2）使用动态视图
标准视图有一个严重的局限性，那就是标准视图不支持参数。俗话说“法网恢恢，疏而不漏”，呵呵，我们也可以变通的使用带参数的视图。实现方法是我们把一个用户定义的表值函数当作支持参数的动态视图使用：
CREATE FUNCTION fnTestView (@m_id int)
RETURNS TABLE
AS
RETURN (select * from 视图名称 where 条件=@m_id)
这样可以在Select语句的From子句中引用他们，用法：
Select * from fnTestView(2)

（3）使用索引视图
我们在使用视图的时候有时想怎么能给视图加索引呢，其实，视图和普通的表一样可以添加索引，当SQL Server必须联合很多表时，这项技术可以大大提高Select语句的性能。
当在视图上创建一个唯一聚集索引（unique clusterd index）时，SQL Server将物化这个视图。看下面的例子：
CREATE VIEW dbo.vtUsers
WITH SCHEMABINDING
AS
SELECT dbo.tUsers.userid, dbo.tUsers.username, dbo.tUsers.password,
dbo.tUsers.question, dbo.tUsers.answer, dbo.tUsers.email, dbo.tUsers.realname,
dbo.tUsers.sex, dbo.tUsers.birthday, dbo.tUsers.country, dbo.tUsers.city,
dbo.tUsers.address, dbo.tUsers.zip, dbo.tUsers.tele, dbo.tUsers.exdate,
dbo.tUsers.totalfund, dbo.tUsers.ordertotal, dbo.tUsers.jifen, dbo.tUsers.pid,
dbo.tUsers.agentid, dbo.tUsers.agentid2, dbo.tUsers.agentid3, dbo.tUsers.status,
dbo.tUsers.checkmod, dbo.tUsers.account, dbo.tUsers.bank, dbo.tUsers.logip,
dbo.tUsers.sitename, dbo.tUserInfo.siteurl
FROM dbo.tUsers INNER JOIN
dbo.tUserInfo ON dbo.tUsers.userid = dbo.tUserInfo.userid
WHERE dbo.tUserInfo.UserID >20

CREATE UNIQUE CLUSTERED INDEX idxvtUser
ON vtUsers(userid)

尽管这个索引只引用了列的一个子集，但是这个索引包含叶级别节点中的所有列（每个聚集索引也都是这样）。
和标准视图一样索引视图的创建和使用也都是有限制的。一个标准视图转换为一个索引视图必须遵守以下规则：
A．视图必须使用With Schemabinding选项来创建；
B．在这个视图中不能使用其他视图、导出表、行集函数或自查询，也就是说只能使用表；
C．视图所用到的基本表必须和视图属于同一个所有者；
D．视图只能链接同一个数据库中的表；
E．视图不能包含一个外部链接或自链接，也就是说在链接表时只能使用INNER JOIN并且INNER JOIN前后不能使同一个表，不能使用LEFT（RIGHT） JOIN 或者 LEFT （RIGHT） OUTER JOIN ；看下面的例子：
比如说创建了下面的视图（自链接）：
CREATE VIEW dbo.vtUsers
WITH SCHEMABINDING
AS
SELECT dbo.tUsers.userid, dbo.tUsers.username, dbo.tUsers.password,
dbo.tUsers.question, dbo.tUsers.answer, dbo.tUsers.email, dbo.tUsers.realname
FROM dbo.tUsers Inner join
dbo.tUsers as t ON dbo.tUsers.userid = t.userid
这个视图是可以创建的，但是在创建索引时CREATE UNIQUE CLUSTERED INDEX idxvtUser ON vtUsers(userid) 就会出错了；
再看下面的视图（外部连接）：
CREATE VIEW dbo.vtUsers
WITH SCHEMABINDING
AS
SELECT dbo.tUsers.userid, dbo.tUsers.username, dbo.tUsers.password,
dbo.tUsers.question, dbo.tUsers.answer, dbo.tUsers.email, dbo.tUsers.realname
FROM dbo.tUsers LEFT OUTER JOIN
dbo.tUserInfo ON dbo.tUsers.userid = dbo.tUserInfo.userid
在创建索引时也会出错的。
F．视图不能包含UNION子句、TOP子句、ORDER BY子句、Having子句、Rollup子句、Cube子句、compute子句、Compute By子句或Distinct关键字；
G．视图不允许使用某些集合函数，如：Count（*）可以使用count_big（*）代替、avg()、max()、min()、stdev()、stdevp()、var()或varp()等；
H．视图不能使用Select * 这样的语句，也就是说视图的所有字段都必须显示指定；
I．视图不能包含Text、ntext、image类型的列；
J．如果视图包含一个Group By子句，那么他必须在Select列中包含count_big(*)；
K．视图中的所有标和用户自定义的函数都必须使用两段式名来引用，即所有者.表或函数名称；
L．所有的基本表和视图都必须使用 Set Ansi_Nulls On 创建；
M．在创建索引时或创建索引后执行IUD时，必须显示或隐式地执行：
Set ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
各个选项的有关信息或意义，可以查阅SQL Server的联机丛书，这里就不再介绍了；
N．索引视图只有在SQL Server2000的企业版或开发版或者更高的版本中才能创建。
如果一个视图可以添加唯一聚集索引，那么在添加了唯一聚集索引之后，该视图也可以像数据库表一样添加非聚集索引，CREATE INDEX idxvtUsers ON vtUsers(username,realname)。

转载自http://www.cnblogs.com/fineboy/archive/2005/09/14/236731.html

posted @ 2017-09-25 17:00 以函阅读(329) 评论(0) 编辑收藏举报

刷新页面返回顶部

数据库视图优化概念，索引视图，物化视图

公告