视图

View（视图）

视图的示例如下：

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

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

普通视图(Rugular View)

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

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

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

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

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

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

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

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

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

建立 View 一个简单的例子:

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

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

SELECT * FROM v_Test_View1

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

索引视图(Indexed View)

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

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

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

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

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

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

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

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

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

比如:

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

分割视图(Partitioned View)

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

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

1.本地分割视图(Local Partitioned View)

2.分布式分割视图(Distributed Partitioned View)

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

分布式分割视图其实是将由几个由不同数据源或是相同数据源获得的平行数据集进行连接所获得的，一个简单的概念图如下:

上面的视图所获得的数据分别来自三个不同数据源的表，每一个表中只包含四行数据，最终组成了这个分割视图.

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

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

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

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

--create Employee100
SELECT TOP 100 * INTO Employee100
FROM HumanResources.Employee
ORDER BY EmployeeID
--create Employee200
SELECT *  INTO Employee200
FROM
(SELECT TOP 100 *
FROM HumanResources.Employee
WHERE EmployeeID NOT IN (SELECT TOP 100 EmployeeID FROM HumanResources.Employee ORDER BY EmployeeID)
ORDER BY HumanResources.Employee.EmployeeID)AS e

这时来建立分布式分割视图:

CREATE VIEW v_part_view_test
AS
SELECT * FROM Employee100
UNION
SELECT * FROM Employee200

这时我们对这个索引进行查询操作:

SELECT * FROM v_part_view_test
WHERE EmployeeID=105

下面是执行计划:

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

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

使用分布式分割视图能够在所有情况下都提升性能吗？那妥妥的不可能.使用这种方式如果面对的查询包含了聚合函数，尤其是聚合函数中还包含 distinct。或是不加 where 条件进行排序.那绝对是性能的杀手。因为聚合函数需要扫描分布式分割视图中所有的表，然后进行 UNION 操作后再进行运算.

通过视图(View)更新数据

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

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

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

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

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

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

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

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

我创建一个 View:

视图(View)中的几个小技巧

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

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

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

EXEC sp_refreshview 视图名称

视图(View)的最佳实践

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

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

案例

SQL CREATE VIEW 实例

可以从某个查询内部、某个存储过程内部，或者从另一个视图内部来使用视图。通过向视图添加函数、join 等等，我们可以向用户精确地提交我们希望提交的数据。

样本数据库 Northwind 拥有一些被默认安装的视图。视图 “Current Product List” 会从 Products 表列出所有正在使用的产品。这个视图使用下列 SQL 创建：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

我们可以查询上面这个视图：

SELECT * FROM [Current Product List]

Northwind 样本数据库的另一个视图会选取 Products 表中所有单位价格高于平均单位价格的产品：

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

我们可以像这样查询上面这个视图：

SELECT * FROM [Products Above Average Price]

另一个来自 Northwind 数据库的视图实例会计算在 1997 年每个种类的销售总数。请注意，这个视图会从另一个名为 “Product Sales for 1997” 的视图那里选取数据：

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

我们可以像这样查询上面这个视图：

SELECT * FROM [Category Sales For 1997]

我们也可以向查询添加条件。现在，我们仅仅需要查看 “Beverages” 类的全部销量：

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL 更新视图

您可以使用下面的语法来更新视图：

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

现在，我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

SQL 撤销视图

您可以通过 DROP VIEW 命令来删除视图。

SQL DROP VIEW Syntax
DROP VIEW view_name
上一页
下一页

最近更新于 0001-01-01

comments powered by Disqus

© 2017-2022 NGTE all rights reserved