分类目录

链接

2011 年 12 月
一 二 三 四 五 六 日
 1234
567891011
12131415161718
19202122232425
262728293031  

近期文章

热门标签

新人福利，免费薅羊毛

现在位置:    首页 ＞ SQL Server ＞ 正文
精通SQL视图用法
 2011-12-10 19:37 SQL Server 暂无评论 阅读(7,440)

简介

视图可以看作定义在 SQL Server 上的虚拟表 . 视图正如其名字的含义一样，另一种查看数据的入口 . 惯例视图自身并不存储实际的数据，而仅仅存储一个 Select 语句和所涉及表的 metadata.

视图简单的理解如下 : 5 通过视图，客户端不再需要知道底层 tabl 表结构及其之间的关系。视图提供了一个统一访问数据的接口。

为什么要使用视图 ( View

从而我不难发现，使用视图将会得到如下好处 :

  • 视图隐藏了底层的表结构，简化了数据访问操作
  • 因为隐藏了底层的表结构，所以大大加强了平安性，用户只能看到视图提供的数据
  • 使用视图，方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了平安性
  • 视图提供了一个用户访问的接口，当底层表改变后，改变视图的语句来进行适应，使已经建立在这个视图上客户端顺序不受影响

视图 ( View 分类

视图在 SQL 中可以分为三类

  1. 普通视图 ( Regular View
  2. 索引视图 ( Index View
  3. 分割视图 ( Partit View

下面从这几种视图类型来谈视图

普通视图 ( Rugular View

普通视图由一个 Select 语句所定义，视图仅仅包括其定义和被引用表的 metadata. 并不实际存储数据。 MSDN 中创建视图的模版如下 :

  1. CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
  2. [ WITH <view_attribute> [ ,...n ] ] 
  3. AS select_statement 
  4. [ WITH CHECK OPTION ] [ ; ]
  5. <view_attribute> ::= 
  6. {
  7.     [ ENCRYPTION ]
  8.     [ SCHEMABINDING ]
  9.     [ VIEW_METADATA ]     } 

参数还是比较少的现在解释一下上面的参数：

ENCRYPTION 视图是加密的如果选上这个选项，则无法修改 . 创建视图的时候需要将脚本保存，否则再也不能修改了

SCHEMA BINDING 和底层引用到表进行定义绑定。这个选项选上的话，则视图所引用到表不能随便更改构架 ( 比如列的数据类型 ) 如果需要更改底层表构架，则先 drop 或者 alter 底层表之上绑定的视图 .

VIEW_META DA TA 这个是个很有意思的选项 . 正如这个选项的名称所指示，如果不选择，返回给客户端的 metadata View 所引用表的 metadata, 如果选择了这个选项，则返回 View metadata. 再通俗点解释， VIEW_META DA TA 可以让视图看起来貌似表一样。 View 每一个列的定义等直接告诉客户端，而不是所引用底层表列的定义。

WITH Check Option 这个选项用于更新数据做限制，下面会在通过视图更新数据一节解释 .

当然了创建视图除了需要符合上面的语法规则之外，还有一些规则需要遵守：

  • View 中，除非有 TOP 关键字，否则不能用 Order By 子句（如果你一意孤行要用 Order by, 这里有个 hack 使用 Top 100 percent ..
  • View 每个 Schema 中命名必需独一无二
  • View 嵌套不能超过 32 层（其实实际工作中谁嵌套逾越两层就要被打 PP -.-
  • Compute,comput by,INTO 关键字不允许出现在 View 中
  • View 不能建立在临时表上
  • View 不能对全文索引进行查询

建立 View 一个简单的例子 :

  1. CREATE VIEW v_Test_View1
  2. AS
  3. SELECT TOP 100 * FROM HumanResources.Employee

视图建立完成后，就可以像访问表一样访问视图了 :

 

  1. SELECT * FROM v_Test_View1

 

Manag studio 中，创建视图的时候更喜欢用这样一种方法 , 将会便捷很多 :

67索引视图（ Index View

谈到索引视图之前，突然想起以前看过的一个漫画 . 话说咱们高端产品买不起，但是省吃俭用攒点钱买个 IPhone 装装高端总还是可以的吧 : 1

其实索引视图也很类似，普通的视图的基础上，为视图建立唯一聚集索引，这时这个视图就变成了索引视图 . 套用上面漫画的公式 : 视图 + 聚集索引 = 索引视图

索引视图可以看作是一个和表 ( Tabl 等效的对象！

SQL Server 中的索引视图和 Oracl 中的 Materi View 一个概念 . 想要理解索引视图，必需先理解聚集索引。聚集索引简单来说理解成主键，数据库中中的数据依照主键的顺序物理存储在表中，就像新华字典，默认是依照 ABCD . 这样的方式进行内容设置。 ABCD . 就相当于主键 . 这样就避免了整表扫描从而提高了性能 . 因此一个表中只能有一个聚集索引。

对于索引视图也是为一个视图加上了聚集索引后。视图就不仅仅再是 select 语句和表的 metadata 索引视图会将数据物理存在数据库中，索引视图所存的数据和索引视图中所涉及的底层表保持同步。

理解了索引视图的原理之后，可以看出，索引视图对于 LDA P 这种大量数据分析和查询来说，性能将会得到大幅提升。尤其是索引视图中有聚合函数，涉及大量高成本的 JOIN, 因为聚合函数计算的结果物理存入索引视图，所以当面对大量数据使用到索引视图之后，并不必要每次都进行聚合运算，这无疑会大大提升性能 .

而同时，每次索引视图所涉及的表进行 Update,Insert,Delet 操作之后， SQL Server 都需要标识出改变的行，让索引视图进行数据同步 . 所以 LDTP 这类增删改很多的业务，数据库需要做大量的同步操作，这会降低性能。

谈完了索引视图的基本原理和好处与坏处之后，来看看在 SQL Server 中的实现 :

SQL Server 中实现索引视图是一件非常，简单的事，只需要在现有的视图上加上唯一聚集索引 . 但 SQL Server 对于索引视图的限制却使很多 DBA 对其并不青睐 :

比方 :

  • 索引视图涉及的基本表必须 ANSI_NULLS 设置为 ON
  • 索引视图必须设置 ANSI_NULLS 和 QUOTED_INDETIFIER 为 ON
  • 索引视图只能引用基本表
  • SCHEMA BINDING 必需设置
  • 定义索引视图时必须使用 Schema.ViewNam 这样的全名
  • 索引视图中不能有子查询
  • avg,max,min,stdev,stdevp,var,varp 这些聚合函数不能用

 

还有更多 … 就不一一列举了有兴趣的请自行 Googl 之 .

下面我来通过一个例子来说明索引视图 :

假设在 adventureWork 数据库中，有一个查询 :

 

  1. SELECT p.Name,s.OrderQty
  2. FROM Production.Product p
  3.  inner join Sales.SalesOrderDetail s
  4. ON p.ProductID=s.ProductID

 

这个查询的执行计划 : 8befor 这时，建立视图并在这个视图上建立唯一聚集索引 :

 

--
建立
视图
  1. --建立视图
  2. CREATE VIEW v_Test_IndexedView
  3. WITH SCHEMABINDING
  4. AS
  5. SELECT p.Name,s.OrderQty,s.SalesOrderDetailID
  6. FROM Production.Product p
  7.  inner join Sales.SalesOrderDetail s
  8. ON p.ProductID=s.ProductID
  9. GO
  10. --在视图上建立索引
  11. CREATE UNIQUE CLUSTERED INDEX indexedview_test1
  12. ON v_Test_IndexedView(SalesOrderDetailID) 

 

接下来，套用刘谦的台词：见证奇迹的时刻到再次执行之前的查询 : 8aft 从上面这个例子中 , 可以体会到索引视图的强大威力，即使你查询语句中不包括这个索引视图，查询分析器会自动选择这个视图，从而大大的提高了性能 . 当然，这么强力的性能，只有在 SQL SERVER 企业版和开发版才有哦（虽然我见过很多 SQL Server 开发人员让公司掏着 Enterpris 版的钱，用着 Express 版的功能 …

分割视图（ Partit View

分割视图其实从微观实现方式来说，整个视图所返回的数据由几个平行表（既是几个表有相同的表结构，也就是列和数据类型，但存储的行集合不同 ) 进行 UNION 连接 ( 对于 UNION 连接如果不了解，请看我之前的 博文 所获得的数据集 .

分割视图总体上可以分为两种 :

1. 外地分割视图 ( Local Partit View

2. 分布式分割视图 ( Distribut Partit View

因为外地分割视图仅仅是为了和 SQL Server 2005 之前的版本的一种向后兼容，所以这里仅仅对分布式分割视图进行说明 .

分布式分割视图其实是将由几个由不同数据源或是相同数据源获得的平行数据集进行连接所获得的一个简单的概念图如下 : 2 上面的视图所获得的数据分别来自三个不同数据源的表，每一个表中只包括四行数据，最终组成了这个分割视图 .

使用分布式分割视图最大的好处就是提升性能 . 比如上面的例子中，仅仅想取得 ContactID 为 8 这位员工的信息，如果通过分布式视图获取的话， SQL Server 可以非常智能的仅仅扫描包括 ContactID 为 8 表 2 从而防止了整表扫描。这大大减少了 IO 操作，从而提升了性能 .

这里要注意的分布式分割视图所涉及的表之间的主键不能重复，比如上面的表 A ContactID 1-4, 则表 B ContactID 不能是 2-8 这个样子 .

还有一点要注意的一定要为分布式分割索引的主键加 Check 约束，从而让 SQL Server 查询分析器知道该去扫描哪个表 , 下面来看个例子 .

微软示例数据库 AdventureWork 数据库，通过 ContactID 过去 100 行和 100-200 行的数据分别存入两个表， Employee100 和 Employee200, 代码如下 :

 

  1. --create Employee100
  2. SELECT TOP 100 * INTO Employee100
  3. FROM HumanResources.Employee 
  4. ORDER BY EmployeeID
  5. --create Employee200
  6. SELECT *  INTO Employee200
  7. FROM 
  8. (SELECT TOP 100 *
  9. FROM HumanResources.Employee 
  10. WHERE EmployeeID NOT IN (SELECT TOP 100 EmployeeID FROM HumanResources.Employee ORDER BY EmployeeID)
  11. ORDER BY HumanResources.Employee.EmployeeID)AS e
  12. 　　这时来建立分布式分割视图:
  13. CREATE VIEW v_part_view_test
  14. AS
  15. SELECT * FROM Employee100
  16. UNION 
  17. SELECT * FROM Employee200
  18. 　　这时我们对这个索引进行查询操作:
  19. SELECT * FROM v_part_view_test
  20. WHERE EmployeeID=105

 

下面是执行计划 :

3

通过上图可以看出，通过这种分割的方式，执行计划仅仅是扫描 Employee200, 从而防止了扫描所有数据，这无疑提升了性能 .

所以，当你将不同的数据表之间放到不同的服务器或是使用 RA ID5 磁盘阵列时，分布式分割视图则进一步会提升查询性能 .

使用分布式分割视图能够在所有情况下都提升性能吗？那妥妥的不可能 . 使用这种方式如果面对的查询包括了聚合函数，

尤其是聚合函数中还包含 distinct 或是不加 where 条件进行排序 . 那绝对是性能的杀手。因为聚合函数需要扫描分布式分割视图中所有的表，然后进行 UNION 操作后再进行运算 .

通过视图 ( View 更新数据

通过视图更新数据是所不推荐的 . 因为视图并不能接受参数 . 更推荐使用存储过程来实现 .

使用 View 更新数据和更新 Tabl 中数据的方式完全一样（前面说过， View 可以看作是一个虚拟表 , 如果是索引视图则是具体的一张表 )

通过视图来更新数据需要注意以下几点

1. 视图中 From 子句之后至少有一个用户表

2.View 查询无论涉及多少张表，一次只能更新其中一个表的数据

3. 对于表达式计算出来的列，常量列，聚合函数算出来的列无法更新

4.Group By,Having,Distinct 关键字不能影响到列不能更新

这里说一下创建 View 有一个 WITH Check Option 选项，如果选择这个选项，则通过 View 所更新的数据必需符合 View 中 where 子句所限定的条件 , 比方 :

 

创建一个 View:

4

 

视图 ( View 中的几个小技巧

1. 通过视图名称查到视图的定义

  1. SELECT * FROM sys.sql_modules
  2. WHERE object_id=OBJECT_ID('视图名称')

2. 前面说过，普通视图仅仅存储的 select 语句和所引用表的 metadata 当底层表数据改变时，有时候视图中表的 metadata 并没有及时同步，可以通过如下代码进行手动同步

  1. EXEC sp_refreshview 视图名称
视图名称

 

视图 ( View 最佳实践

这是个人一些经验，欢迎补充

  • 一定要将 View 中的 Select 语句性能调到最优（貌似是废话，不过真理都是废话 …
  • View 最好不要嵌套，如果非要嵌套，最多只嵌套一层
  • 能用存储过程和自定义函数替代 View 尽量不要使用 View 存储过程会缓存执行计划，性能更优，限制更少
  • 分割视图上，不要使用聚合函数，尤其是聚合函数还包含了 Distinct
  • 视图内，如果 Where 子句能加在视图内，不要加在视图外（因为调用视图会返回所有行，然后再筛选，性能杀手，如果你还加上了 order by ..

总结

文中对视图的三种类型进行了详解 . 每种视图都有各自的使用范围，使用得当会将性能提升一个档次，而使用不当反而会拖累性能 .

想起一句名言 : everyth ha price,alwai trade-off ..

 

============ 欢迎各位老板打赏~ ===========

关键字: ,
【上篇】
【下篇】

与本文相关的文章

该日志由 Bruce 于2011年12月10日发表在SQL Server分类下

本文版权归Bruce's Blog所有，转载引用请完整注明以下信息：
本文作者：Bruce
本文地址：精通SQL视图用法 | Bruce's Blog

发表评论

留言无头像?