荣耀彩票代理

IT技术互动交流平台

SQL Server聚焦使用视图若干限制/建议 视图查询性能问题 你懵逼了?(二十五)

作者:JeffckyWang  来源:IT165收集  发布日期:2016-12-19 20:32:25

前言

荣耀彩票代理SHANGYIJIEWOMENJIANDANJIANGSHULEBIAOBIAODASHIDE4ZHONGLEIXING,ZHEIYIXILIEWOMENLAIJIANGJIANGSHIYONGSHITUDEXIANZHI,JIANDUANDENEIRONG,SHENRUDELIJIE,Always to review the basics。

避免在视图中使用ORDER BY

荣耀彩票代理SHANGYIJIEWOMENYEJIANGSHULESHIYONGBIAOBIAODASHIBIXUMANZUDE3GEYAOQIU,QIZHONGJIUYOUYIGEWUFABAOZHENGSHUNXU,YEJIUSHISHUODEORDER BYDEWENTI,WOMENHAISHIZHONGDIANKANKANZAISHITUZHONGDEXIANZHI。ZAICHANGGUICHAXUNZHONGDUIYUPAIXUWOMENSHIZHEIYANGZUODE。

USE AdventureWorks2012
GO

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC

JIEXIALAIWOMENZAISHITUZHONGDUISHUJUJINXINGPAIXU,WOMENCHUANGJIANSHITULAIKANKAN

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

CISHIDANGWOMENZHIXINGCHUANGJIANSHITUSHIHUIFAXIANRUXIACUOWU

CISHIZAISHITUNEIBUBUNENGSHIYONGORDER BYWOMENCHUANGJIANSHITUHOUZAIWAIBUSHITUSHIYONGORDER BYKANKAN

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

荣耀彩票代理WOMENZAILAIKANKANSHANGSHUZAISHITUNEIBUJINXINGORDER BYSHICHUXIANDECUOWU,TASHUOMINGKEYISHIYONGTOP、OFFSETDENG,JIEXIALAIWOMENLIYONGTOPLAIKANKANSHIJIJIEGUOSHIZENYANGDE。

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

WOMENZAILAICHAXUNGAISHITUKANKANFANHUIDEJIEGUOJI

USE AdventureWorks2012
GO

SELECT * 
FROM dbo.view_limit

荣耀彩票代理DANGWOMENZAICHUANGJIANSHITUSHINEIBUSHIYONGORDER BYDUIJIEGUOJIJINXINGJIANGXU,JIEGUOFANHUIDESHUJUYAGENMEIYOUJINXINGJIANGXU,TONGSHIWOMENKANDAOCHAXUNJIHUAGENBENMEIYOUCHUXIANSortPAIXUCAOZUO。WOMENZAILAIKANLINGWAIYIZHONGQINGKUANGJIANGFANHUIDESHUJUSHEZHIWEIBI100%SHAOYIDIANSHISHIKAN。

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

CISHIZEJINXINGLEJIANGXUPAIXU,SHUOMINGZAISHITUZHONGLIYONGTOP、OFFSETJIUHAOSHILENI?SHANGSHUCHAXUNWOMENMEIYOUZUORENHETIAOJIANXIANZHI,WOMENCHACHABIAOZHONGZONGGONGYOUDUOSHAOSHUJUHELIYONGSHITUCHAXUNSHIFANHUIYOUDUOSHAOSHUJUKANKAN。

USE AdventureWorks2012
GO

SELECT COUNT(*) AS originalCount
FROM Sales.SalesOrderDetail


SELECT COUNT(*) AS viewCount
FROM dbo.view_limit

SUIRANZAISHANGSHUQINGKUANGXIAWOMENXIANZHIFANHUIDESHUJUZUIZHONGYEANZHAOJIANGXULAIJINXINGPAIXU,ZHEISHIXIANGDUIYUXIAOBIAOERYAN,RUGUOBIAOZHONGSHUJULIANGBIJIAODADEHUA,CISHITONGGUOZAISHITUZHONGJINXINGORDER BYDEHUAJIANGHUIQUESHENGHENDUOZHI,SUOYIJIANYIBUYAOZAISHITUZHONGJINXINGORDER BYERSHIZAISHITUWAIBUJINXINGORDER BY。HAOLEZHEISHIWOMENSHUODEDIYIZHONGXIANZHI,WOMENJICHUJIELUN。

(1)BIMIANZAISHITUNEIBUSHIYONGORDER BY,DANGBIAOSHUJUBIJIAOXIAOSHISUIRANTONGGUOTOPHUOOFFSETDENGNENGJIEJUEWENTI,DANSHIDANGSHUJULIANGBIJIAODASHICISHIZAISHITUNEIBUSHIYONGORDER BYHUIDAOZHIGENGDUODESHUJUXINGQUESHI,JIANYIZAISHITUWAIBUJINXINGORDER BY。

避免在视图中使用SELECT *

SHOUXIANWOMENTONGGUOCHUANGJIANSHITULAIKANWENTIDECHUXIAN。

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

JIEXIALAIWOMENTONGGUOCHAZHAOYUANBIAOHESHITUDEFANGSHILAIKANKANFANHUIDESHUJU

USE AdventureWorks2012
GO

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

EN,MEIMAOBING,JIEXIALAIWOMENZAIBIAOZHONGTIANJIAEWAILIE

USE AdventureWorks2012
GO

ALTER TABLE HumanResources.[Shift]
ADD AdditionalCol INT
GO

荣耀彩票代理WOMENZAILAIJINXINGSHANGSHUCHAXUN,KANKANFANHUIDEJIEGUOJI

荣耀彩票代理CISHIWOMENFAXIANTIANJIAEWAILIEHOUSHITUBINGWEIXIANSHI,DANGRANSHUJUYEJIUBUHUIXIANSHILE。CISHIWOMENZAIYONGSHITUCHAXUNZHIQIANJINXINGSHUAXINKANKAN

USE AdventureWorks2012
GO

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

EXEC sp_refreshview 'view_limit'


-- 查找视图
SELECT *
FROM view_limit
GO

CISHICAINENGFANHUIZHENGQUEDEJIEGUO。NEIMESHISHENMEYUANYINDAOZHITIANJIAEWAILIETONGGUOSHITUCHAXUNHUICHUXIANYIXIANGBUDAODEJIEGUONI,YINWEISHITUZAIBIANYIFANGSHISHANGDUILIESHIMEIJUDE,BINGQIEXINDEBIAOLIEBUHUIZIDONGTIANJIADAOSHITUZHONG,YEJIUSHISHUORUOWOMENEWAITIANJIALELIE,CISHILIEGENBENBUHUITIANJIADAOSHITUZHONG,SUOYICISHIWOMENKEYITONGGUOsp_refreshviewHUOsp_refreshsqlmoduleDEFANGSHILAISHUAXINSHITUDEYUANSHUJU。SUOYIWOMENJIELUNRUXIA

(2)BIMIANZAISHITUZHONGSHIYONGSELECT *,DANGBIAOZHONGTIANJIAEWAILIEHOUHUIDAOZHISHITUZHONGBUHUIZIDONGJINXINGTIANJIA,SUIRANWOMENKEYITONGGUOsp_refreshviewHUOsp_refreshmoduleDEFANGSHILAISHUAXINSHITU,DANSHIWEILEBIMIANHUNXIAO,ZUIHAOSHIZAISHITUDINGYIZHONGXIANSHILIECHUSUOXUYAODELIEDEMINGCHENG,RUOTIANJIALEEWAILIE,TONGSHIZAISHITUZHONGWOMENXUYAOEWAILIEDEHUA,WOMENTONGGUOALTER VIEWDEFANGSHILAIXIUGAISHITUDINGYIJIKE。

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

XIAMIANWOMENZHIJIETONGGUOLIZIJINXINGYANSHI。

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

JIEXIALAIWOMENJINXINGCHANGGUISQLCHAXUNHESHITUCHAXUN

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

SHANGSHULIYONGCHANGGUICHAXUNHESHITUCHAXUNKAIXIAOYANG,DANSHIXIANZAIWOMENYOUZHEIYANGYIGECHANGJINGSHANGSHUSHITUSHIBEIQITATONGSHISUOXIE,DANSHIDANGWOMENYONGSHIHAIXUYAOFANHUIEWAIQITALIE,SUOYIWEILEBUFANHUIQITADUOYUDESHUJUERHETONGSHISIBI,WOMENXUYAOZAICIZAISHITUWAIBUJINXINGJOINLAIDEDAOWOMENEWAIDELIE,WOMENXIAMIANLAIKANKAN。

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

CISHIEWAIFANHUILEQuantityLIEDUISHITUZAICIJINXINGJOIN,WOMENKANKANCHAXUNJIHUAKAIXIAO

CISHIFAXIANLIYONGSHITUCHAXUNKAIXIAOGENGDUO,ERCHANGGUICHAXUNBUGUOSHIDUOTIANJIAYIGELIEERYIMEIYOURENHEGAIBIAN。WOMENJIXUWANGXIAKAN

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

WOMENZAIQIANMIANYIZHITAOLUNGUOGUANYUSUOYINDEJIANLIDEWENTI,ERQIESUOYINDOUSHIJIANLIZAIBIAOSHANG,NEIMEWOMENJIANGSUOYINJIANLIZAISHITUSHANGQINGKUANGSHIZENYANGDENI,SHIBUSHICHAXUNXIAOLVHUIDEDAOTISHENGNI?WOMENSHOUXIANCHUANGJIANCESHIBIAOBINGCHARUSHUJU

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

荣耀彩票代理SHANGSHUWOMENCHUANGJIANLECESHIDESHITUBIAOViewTableBINGCHARULE10WANTIAOCESHISHUJU,JIEXIALAIWOMENDUIBIAOJIANLISUOYIN。

USE AdventureWorks2012
GO

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

荣耀彩票代理JIEXIALAIWOMENLAICHUANGJIANSHITUBINGZAISHITUSHANGCHUANGJIANSUOYIN

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

SHANGSHUWOMENXUYAOZHUYI,DANGZAISHITUSHANGCHUANGJIANSUOYINSHIBIXUZHIDINGWITH SCHAMABINDING,FOUZEBUYUNXUZAISHITUSHANGCHUANGJIANSUOYIN。WOMENZUIHOUTONGGUOCHANGGUICHAXUNHESHITUCHAXUNLAIKANKANCHAXUNJIHUAQINGKUANG

USE AdventureWorks2012
GO

SELECT ID1,ID2,SomeData
FROM dbo.ViewTable
GO

SELECT ID1,ID2,SomeData
FROM dbo.ViewLimit
GO

荣耀彩票代理CISHIWOMENFAXIANSHITUCHAXUNLIYONGDESUOYINBUSHIWOMENCHUANGJIANDESUOYINidx_view_table,ZHUYAOYUANYINSHIYINWEISHITUHEBIAOSHIGUANLIANDE,SUOYICHAXUNJIHUAJUEDINGZAIBIAOSHANGDESUOYINBIZAISHITUSHANGCHUANGJIANDESUOYINGENGJIAGAOXIAO。 DANGWOMENZAIWITHZHONGQIANGZHIZHIDINGnoexpandCISHIJIANGHUIZHIXINGZAISHITUSHANGCHUANGJIANDESUOYIN,YINWEICISHISHITUYIJINGHEYUANSHIBIAOMEIYOUGUANXI,TASHIDULIDE,RUXIA:

USE AdventureWorks2012
GO

SELECT ID1,ID2,SomeData
FROM dbo.ViewTable
GO

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

ZAISHITUSHANGCHUANGJIANSUOYINZHEIGEWENTIBIJIAOFUZA,WOMENJIUBUTAOLUNLE,YIBANTONGGUOCHANGGUICHAXUNDOUNENGJIEJUEDEWENTIHEBILAOJIASHITUNI。ZHEIGEWOMENXUYAOZHUYIYIXIAJIUXING。

总结

荣耀彩票代理BENJIEWOMENJIANGLEJIGESHIYONGSHITUSHIDEXIANZHIYIJIJIANYIDENGWENTI,XIAJIEWOMENHAISHIHUITAOLUNSHIYONGSHITUDEQITAXIANZHI,JIANDUANDENEIRONG,SHENRUDELIJIE,WOMENXIAJIEZAIHUI。

Tag标签:         
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规