AI 助理
备案控制台
开发者社区 数据库 文章 正文

SQL Server-聚焦使用视图若干限制/建议、视图查询性能问题，你懵逼了？（二十五）

2016-12-17 1016
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介： 前言 上一节我们简单讲述了表表达式的4种类型，这一系列我们来讲讲使用视图的限制，简短的内容，深入的理解，Always to review the basics。 避免在视图中使用ORDER BY 上一节我们也讲述了使用表表达式必须满足的3个要求，其中就有一个无法保证顺序，也就是说的ORDER BY的问题，我们还是重点看看在视图中的限制。

前言

上一节我们简单讲述了表表达式的4种类型，这一系列我们来讲讲使用视图的限制，简短的内容，深入的理解，Always to review the basics。

避免在视图中使用ORDER BY

上一节我们也讲述了使用表表达式必须满足的3个要求，其中就有一个无法保证顺序，也就是说的ORDER BY的问题，我们还是重点看看在视图中的限制。在常规查询中对于排序我们是这样做的。

USE AdventureWorks2012
GO

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC

 

接下来我们在视图中对数据进行排序，我们创建视图来看看

USE AdventureWorks2012
GO

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[view_limit]'))
DROP VIEW [dbo].[view_limit] GO

CREATE VIEW view_limit
AS
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO

此时当我们执行创建视图时会发现如下错误

此时在视图内部不能使用ORDER BY我们创建视图后在外部视图使用ORDER BY看看

USE AdventureWorks2012
GO

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[view_limit]'))
DROP VIEW [dbo].[view_limit]
GO

CREATE VIEW view_limit
AS
SELECT *
FROM Sales.SalesOrderDetail
GO

SELECT *
FROM view_limit
ORDER BY SalesOrderDetailID DESC

我们再来看看上述在视图内部进行ORDER BY时出现的错误，它说明可以使用TOP、OFFSET等，接下来我们利用TOP来看看实际结果是怎样的。

USE AdventureWorks2012
GO

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[view_limit]'))
DROP VIEW [dbo].[view_limit]
GO

CREATE VIEW view_limit
AS
SELECT TOP 100 PERCENT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO

我们再来查询该视图看看返回的结果集

USE AdventureWorks2012
GO

SELECT * 
FROM dbo.view_limit

当我们在创建视图时内部使用ORDER BY对结果集进行降序，结果返回的数据压根没有进行降序，同时我们看到查询计划根本没有出现Sort排序操作。我们再来看另外一种情况将返回的数据设置为比100%少一点试试看。

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[view_limit]'))
DROP VIEW [dbo].[view_limit]
GO

CREATE VIEW view_limit
AS
SELECT TOP 99.9 PERCENT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
GO

此时则进行了降序排序，说明在视图中利用TOP、OFFSET就好使了呢？上述查询我们没有做任何条件限制，我们查查表中总共有多少数据和利用视图查询时返回有多少数据看看。

USE AdventureWorks2012
GO

SELECT COUNT(*) AS originalCount
FROM Sales.SalesOrderDetail


SELECT COUNT(*) AS viewCount
FROM dbo.view_limit

 

虽然在上述情况下我们限制返回的数据最终也按照降序来进行排序，这是相对于小表而言，如果表中数据量比较大的话，此时通过在视图中进行ORDER BY的话将会缺省很多值，所以建议不要在视图中进行ORDER BY而是在视图外部进行ORDER BY。好了这是我们说的第一种限制，我们给出结论。

（1）避免在视图内部使用ORDER BY，当表数据比较小时虽然通过TOP或OFFSET等能解决问题，但是当数据量比较大时此时在视图内部使用ORDER BY会导致更多的数据行缺失，建议在视图外部进行ORDER BY。

避免在视图中使用SELECT *

首先我们通过创建视图来看问题的出现。

USE AdventureWorks2012
GO

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[view_limit]'))
DROP VIEW [dbo].[view_limit]
GO

CREATE VIEW view_limit
AS
SELECT *
FROM HumanResources.Shift
GO

接下来我们通过查找原表和视图的方式来看看返回的数据

USE AdventureWorks2012
GO

-- 查找原表
SELECT *
FROM HumanResources.[Shift]
GO
-- 查找视图
SELECT *
FROM view_limit
GO

恩，没毛病，接下来我们在表中添加额外列

USE AdventureWorks2012
GO

ALTER TABLE HumanResources.[Shift]
ADD AdditionalCol INT
GO

我们再来进行上述查询，看看返回的结果集

此时我们发现添加额外列后视图并未显示，当然数据也就不会显示了。此时我们在用视图查询之前进行刷新看看

USE AdventureWorks2012
GO

-- 查找原表
SELECT *
FROM HumanResources.[Shift]
GO

EXEC sp_refreshview 'view_limit'


-- 查找视图
SELECT *
FROM view_limit
GO

此时才能返回正确的结果。那么是什么原因导致添加额外列通过视图查询会出现意想不到的结果呢，因为视图在编译方式上对列是枚举的，并且新的表列不会自动添加到视图中，也就是说若我们额外添加了列，此时列根本不会添加到视图中，所以此时我们可以通过sp_refreshview或sp_refreshsqlmodule的方式来刷新视图的元数据。所以我们结论如下

（2）避免在视图中使用SELECT *，当表中添加额外列后会导致视图中不会自动进行添加，虽然我们可以通过sp_refreshview或sp_refreshmodule的方式来刷新视图，但是为了避免混淆，最好是在视图定义中显式列出所需要的列的名称，若添加了额外列，同时在视图中我们需要额外列的话，我们通过ALTER VIEW的方式来修改视图定义即可。

视图查询返回额外列通过JOIN表导致查询性能低效

下面我们直接通过例子进行演示。

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[view_limit]'))
DROP VIEW [dbo].[view_limit] 
GO


CREATE VIEW view_limit
AS
SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
GO

解下来我们进行常规SQL查询和视图查询

USE AdventureWorks2012
GO

SELECT *
FROM dbo.view_limit
WHERE SalesOrderDetailID > 111111
GO

SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO

上述利用常规查询和视图查询开销样，但是现在我们有这样一个场景上述视图是被其他同事所写，但是当我们用时还需要返回额外其他列，所以为了不返回其他多余的数据而和同事撕逼，我们需要再次在视图外部进行JOIN来得到我们额外的列，我们下面来看看。

USE AdventureWorks2012
GO

SELECT v1.*
,th.[Quantity] FROM dbo.view_limit v1
INNER JOIN Production.TransactionHistory th ON v1.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO

SELECT [SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber] ,[OrderQty],sod.[ProductID],[SpecialOfferID],[UnitPrice],[UnitPriceDiscount] ,[LineTotal],[ReferenceOrderID] ,th.[Quantity] FROM Sales.SalesOrderDetail sod
INNER JOIN Production.TransactionHistory th ON sod.SalesOrderID = th.ReferenceOrderID
WHERE SalesOrderDetailID > 111111
GO

此时额外返回了Quantity列对视图再次进行JOIN，我们看看查询计划开销

此时发现利用视图查询开销更多，而常规查询不过是多添加一个列而已没有任何改变。我们继续往下看

默认情况下在视图上创建索引无效

我们在前面一直讨论过关于索引的建立的问题，而且索引都是建立在表上，那么我们将索引建立在视图上情况是怎样的呢，是不是查询效率会得到提升呢？我们首先创建测试表并插入数据

USE AdventureWorks2012
GO

IF EXISTS (SELECT * FROM sys.objects 
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[ViewTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[ViewTable]
GO

CREATE TABLE ViewTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))

INSERT INTO ViewTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO

上述我们创建了测试的视图表ViewTable并插入了10万条测试数据，接下来我们对表建立索引。

USE AdventureWorks2012
GO

CREATE UNIQUE CLUSTERED INDEX [idx_original_table] ON dbo.ViewTable
(
ID1 ASC
)

接下来我们来创建视图并在视图上创建索引

USE AdventureWorks2012
GO

CREATE VIEW ViewLimit
WITH SCHEMABINDING
AS
SELECT ID1,ID2,SomeData
FROM dbo.ViewTable
GO

CREATE UNIQUE CLUSTERED INDEX [idx_view_table] ON [dbo].[ViewLimit]
(
ID2 ASC
)
GO

上述我们需要注意，当在视图上创建索引时必须指定WITH SCHAMABINDING，否则不允许在视图上创建索引。我们最后通过常规查询和视图查询来看看查询计划情况

USE AdventureWorks2012
GO

SELECT ID1,ID2,SomeData
FROM dbo.ViewTable
GO

SELECT ID1,ID2,SomeData
FROM dbo.ViewLimit
GO

此时我们发现视图查询利用的索引不是我们创建的索引idx_view_table，主要原因是因为视图和表是关联的，所以查询计划决定在表上的索引比在视图上创建的索引更加高效。 当我们在WITH中强制指定noexpand此时将会执行在视图上创建的索引，因为此时视图已经和原始表没有关系，它是独立的，如下：

USE AdventureWorks2012
GO

SELECT ID1,ID2,SomeData
FROM dbo.ViewTable
GO

SELECT ID1,ID2,SomeData
FROM dbo.ViewLimit
WITH(NOEXPAND)
GO

在视图上创建索引这个问题比较复杂，我们就不讨论了，一般通过常规查询都能解决的问题何必劳驾视图呢。这个我们需要注意一下就行。

总结

本节我们讲了几个使用视图时的限制以及建议等问题，下节我们还是会讨论使用视图的其他限制，简短的内容，深入的理解，我们下节再会。

文章标签：
云数据库 RDS SQL Server 版
Go
索引
SQL
jeffcky
目录
相关文章
泡沫o0
|
Linux Shell
【Shell 命令集合 基础命令】Linux cat 命令使用教程
【Shell 命令集合 基础命令】Linux cat 命令使用教程
泡沫o0
372 1
Lux_Sun
|
BI
运营必备 - CPA、CPS、CPC、CPM推广是什么意思？（一）
运营必备 - CPA、CPS、CPC、CPM推广是什么意思？（一）
Lux_Sun
21999 0
运营必备 - CPA、CPS、CPC、CPM推广是什么意思？（一）
阿里云云原生
|
Arthas 监控 Java
Arthas 实践——生产环境排查 CPU 飚高问题
13:40 收到我们的生产环境服务器绿版 CUP 超负载告警通知。此时心里只有一个想法，重启大法好，马上登录服务器，执行 top 发现进程 30247 和 28337 占用 CPU 为 200 多和100 多基本占用了 4 核的 3 核，整个过程大概用时 30 秒，维护群依然很平静，运营的电话也没打过来，这时候我断定，这次问题应该影响面很小，用户可能也暂时没有发现，好吧，还有时间做排查。
阿里云云原生
4967 0
Arthas 实践——生产环境排查 CPU 飚高问题
爱专研的技术土狗
|
11月前
|
数据采集 存储 XML
Python爬虫定义入门知识
Python爬虫是用于自动化抓取互联网数据的程序。其基本概念包括爬虫、请求、响应和解析。常用库有Requests、BeautifulSoup、Scrapy和Selenium。工作流程包括发送请求、接收响应、解析数据和存储数据。注意事项包括遵守Robots协议、避免过度请求、处理异常和确保数据合法性。Python爬虫强大而灵活，但使用时需遵守法律法规。
爱专研的技术土狗
436 6
wljslmz
|
安全 容器 iOS开发
三分钟了解一款强大的网络设备模拟器：PNETLab
【7月更文挑战第28天】
wljslmz
530 7
三分钟了解一款强大的网络设备模拟器：PNETLab
数据知道
|
Web App开发 Linux Python
linux上安装selenium环境及测试
该文章提供了在Linux CentOS上安装Selenium环境、Chrome浏览器及Chromedriver的详细步骤，并演示了如何以无头模式进行测试。
数据知道
1019 0
威哥爱编程
|
存储 缓存 NoSQL
GuavaCache、EVCache、Tair、Aerospike 缓存框架比较
**摘要：** Guava Cache、EVCache、Tair 和 Aerospike 是不同的缓存解决方案。Guava Cache 是轻量级的本地缓存，适用于Java应用，提供丰富的配置选项和自动加载功能。EVCache 基于 Memcached，适合分布式场景，高并发访问。Tair，阿里巴巴的分布式缓存，支持多种数据结构，适用于大规模系统。Aerospike 是高性能NoSQL数据库，结合缓存和持久化，适用于低延迟和大数据量的场景。选择时要考虑应用场景、性能需求和数据规模。
威哥爱编程
194 0
GuavaCache、EVCache、Tair、Aerospike 缓存框架比较
游客lkw7toijkbadm
|
分布式计算 DataWorks 关系型数据库
阿里云数加-分析型数据库AnalyticDB数据导入的多样化策略
通过合理利用这些数据导入方法，用户可以充分发挥AnalyticDB的实时计算能力和高并发查询性能，为业务分析和决策提供强有力的数据支持。
游客lkw7toijkbadm
408 0
土木林森
|
JavaScript 前端开发 关系型数据库
旅游规划助手：结合Vue的交云性设计和Python的强大后端功能
【4月更文挑战第11天】本文探讨了如何使用Vue.js和Python（Flask或Django）构建旅游规划助手应用，简化旅行规划。首先，确保安装了Python、Node.js、数据库系统和Git。接着，介绍如何用Python搭建后端API，分别展示了Flask和Django的例子。然后，利用Vue.js初始化前端项目，结合Vuex和Vue Router构建用户界面。最后，通过Axios实现前端与后端的数据通信。这样的架构有利于团队协作和代码维护，便于扩展应用功能。
土木林森
246 2
Siam
|
存储 负载均衡 NoSQL
PHP设置Redis储存Session，自定义session驱动
PHP也可以配置将session保存在redis或者memcache中，在几种用来存储会话数据的方式。显然是Redis在效率上要更快些，而与memcached相比，因为有持久化，也更安全一些。 所以也是常用的负载均衡环境处理session的策略。 但因为是将信息储存在内存中，可能会出现内存不足、利用率不高等问题。 优点：效率高 缺点：信息储存在内存中，会产生大小不一的内存块，内存无法完全利用，并且可能出现内存不足。
Siam
345 0
PHP设置Redis储存Session，自定义session驱动