Optimization

對視圖執行的查詢會發生什麼情況？

  • April 28, 2017

對視圖執行的查詢會發生哪些優化和/或更改？（如果能知道每個主要的*數據庫——Oracle、SQL Server、Postgres、MySQL/MariaDB——*是否以不同的方式處理這個問題，那就太好了）。

例如，假設我有以下視圖：

CREATE VIEW my_view AS
 SELECT
     id,
     val0,
     val1,
     val2
   FROM my_table
   WHERE val0 = 42;

…我想對該視圖執行以下查詢：

SELECT
   id,
   val1
 FROM my_view
 WHERE val2 = 'Fish fingers and custard';

…數據庫是優化查詢還是本質上執行了兩次選擇？IE 與執行下面的潛在查詢 1 或 2 基本相同還是兩者都不相同？

潛在問題 1：

SELECT
   id,
   val1
 FROM (
   SELECT
       id,
       val0,
       val1,
       val2
     FROM my_table
     WHERE val0 = 42
 )
 WHERE val2 = 'Fish fingers and custard';

潛在問題 2：

SELECT
   id,
   val1
 FROM my_table
 WHERE val0 = 42
   AND val2 = 'Fish fingers and custard';

我的答案將幾乎完全集中在 SQL Server 上，因為我將給出一個相當詳細的答案，而且我在其他平台上沒有相同水平的專業知識。

首先，重要的是要認識到查詢優化器並不直接針對您編寫的 SQL 工作。它在優化之前被轉換為內部格式。您為潛在的查詢 1 和查詢 2 列出的內容幾乎相同，除了視圖的細微差別。此處詢問並回答了有關查詢 1 和查詢 2 之間區別的類似問題。如果您想了解有關 SQL Server 使用的內部格式的更多資訊，可以閱讀Paul White 的一系列優秀部落格文章。但是，大多數情況下，只需比較您懷疑可能以相同方式優化的兩個查詢的查詢計劃就足夠了。

使用視圖可以提高性能的幾種方法：

  1. 可以定義在數據庫上實現為物理結構的視圖。在 SQL Server 中，這些稱為索引視圖。在 Oracle 中，這些稱為物化視圖。在 Oracle 中，未針對物化視圖編寫的查詢可能仍使用物化視圖。進一步的討論超出了這個答案的範圍。
  2. 有時，同一個 SQL 查詢需要在多個 RDBMS 平台上執行。通過視圖，我們可以使用每個平台獨有的語法，但將相同的查詢發送到數據庫。如果沒有視圖，我們可能不得不使用可能對性能不利的使用者定義函式。
  3. 有時人們會將非常聰明的程式碼放在視圖中。如果它比你寫的更好，你可以通過使用視圖來提高性能。

通常，針對視圖編寫的查詢與直接針對基表編寫的查詢一樣有效或效率較低。這是因為視圖定義通常包含額外的列和連接，對於針對視圖的查詢所詢問的特定問題可能不需要這些列和連接。為了在復雜的視圖中獲得良好的性能，我們希望發生三件事：

  1. 列消除。如果某個列出現在視圖中，但在針對該視圖的查詢中未提及，則不應計算該值。
  2. 加入淘汰。如果在視圖中使用的表可以在不更改結果的情況下安全地消除，那麼它應該被消除。如果優化器有更多資訊，有時可能會發生這種情況。例如，可能未在數據庫中聲明外鍵。其他時候，實現聯接消除的規則可能不涵蓋特定場景。例如，在 Oracle 中，多列連接不會發生連接消除，但在 SQL Server 中可以。
  3. 謂詞下推。如果我向視圖添加一個過濾器並且基礎列上有一個索引，那麼我應該能夠使用該索引。我相信這就是您的範例所暗示的。即使沒有索引，我仍然希望將過濾器盡可能下推到計劃中，以避免不必要的工作。

根據我的經驗，查詢優化器很好地實現了這些規則，這當然是一件好事，但對於 SE 展示來說可能是壞事。但是，如果我們編寫鬼鬼祟祟的程式碼，我們最終會得到顯示上述所有優化失敗的範例。這是因為實現優化的規則並非旨在涵蓋所有可能的場景。

首先，我將創建一些簡單的範例數據。數據本身並不重要，但表定義很重要。

DROP TABLE IF EXISTS dbo.BASE_TABLE;
CREATE TABLE dbo.BASE_TABLE (
ID INT NOT NULL,
ID2 INT NOT NULL,
FILLER VARCHAR(50), 
CONSTRAINT BASE_TABLE_ID CHECK (ID > 0),
PRIMARY KEY (ID)
);

INSERT INTO dbo.BASE_TABLE WITH (TABLOCK)
SELECT TOP (1000000) 
 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.EXTRA_TABLE;
CREATE TABLE dbo.EXTRA_TABLE (
ID INT NOT NULL,
ID2 INT NOT NULL,
FILLER VARCHAR(50),
PRIMARY KEY (ID, ID2)
);

INSERT INTO dbo.EXTRA_TABLE WITH (TABLOCK)
SELECT TOP (1000000) 
 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.EMPTY_TABLE;
CREATE TABLE dbo.EMPTY_TABLE(
ID INT NOT NULL,
PRIMARY KEY (ID)
);

DROP TABLE IF EXISTS dbo.EMPTY_CCI;
CREATE TABLE dbo.EMPTY_CCI (
ID INT NOT NULL
, INDEX CCI_EMPTY_CCI CLUSTERED COLUMNSTORE
);

GO

CREATE FUNCTION dbo.THE_BEST_FUNCTION () RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN NULL;
END;

GO

這是我偷偷摸摸的視圖定義：

CREATE VIEW dbo.SNEAKY_VIEW
AS
SELECT 
 ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
, dbo.THE_BEST_FUNCTION() FUNCTION_VALUE
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
LEFT OUTER JOIN dbo.EMPTY_CCI ON 1 = 0
LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID;

GO

不幸的是，景色一團糟。作為人類優化器，可以相當程度地簡化該查詢。可以消除連接EXTRA_TABLE，因為我們連接的是完整的主鍵，所以行數不會改變。任何行也不可能匹配，但它是一個外連接，因此不會消除任何內容。EMPTY_CCI可以消除連接反對，因為連接條件永遠不會匹配。可以消除加入反對EMPTY_TABLE，因為我們加入反對完整的主鍵。該函式也總是返回NULL，所以不需要包含它。所以我們可以簡化為：

SELECT 
 ID
, 1 CNT
, NULL
FROM dbo.BASE_TABLE t;

但是，我們可以做得更好。ID由於約束，ID它始終為正，並且始終是唯一的，因為它是主鍵。因此COUNT視窗函式將始終為 1。查詢可以這樣重寫：

SELECT 
 ID
, 1 CNT
, NULL
FROM dbo.BASE_TABLE t;

查詢優化器是否能夠簡化針對視圖的查詢？讓我們通過比較計劃來找出答案。這是簡單查詢的計劃：

簡單查詢

這是SELECT *反對觀點的計劃：

查詢視圖

他們有很大的不同。讓我們通過更多範例查詢來查看上述優化的範例，這些範例可能無法完全按預期工作。

首先，標量使用者定義函式不利於 SQL Server 的性能。在其他問題中，他們強制整個查詢有一個串列計劃。此查詢符合併行計劃的條件，我在我的機器上得到了一個：

SELECT *
FROM (
   SELECT 
     ABS(t.ID) ID
   , COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
   FROM dbo.BASE_TABLE t
   LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
   LEFT OUTER JOIN dbo.EMPTY_CCI ON 1 = 0
   LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID
) derived_table;

計劃：

平行計劃

但是，即使我沒有根據視圖中的使用者定義函式選擇列，我仍然會得到一個強制串列計劃：

SELECT ID, CNT
FROM dbo.SNEAKY_VIEW;

連續計劃

所以使用視圖和派生表之間有一個區別。有時列消除不會以相同的方式工作。

對於第二個範例，該EMPTY_CCI表不會影響查詢結果，因此讓我們將其從派生表中刪除：

SELECT *
FROM (
   SELECT 
     ABS(t.ID) ID
   , COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
   FROM dbo.BASE_TABLE t
   LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
   LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID
) derived_table;

這是查詢計劃：

沒有批處理模式

但是，查詢計劃與視圖有所不同：

SELECT ID, CNT, FUNCTION_VALUE
FROM dbo.SNEAKY_VIEW;

批處理模式

該視圖能夠使用批處理模式，這是在查詢中涉及聚集列儲存索引時實現查詢執行的一種特殊方式。即使該EMPTY_CCI表未顯示在計劃中，查詢仍然符合批處理模式的條件。請注意，這EXTRA_TABLE在兩個查詢中都是不必要的。這是因為連接條件過於復雜，SQL Server 無法確定連接是否可以安全消除。另請注意，該EMPTY_TABLE表未顯示在任一查詢計劃中。查詢優化器能夠在兩個查詢中消除它。

對於第三個範例，讓我們看一下謂詞下推。假設我想過濾以僅包含 where 的行ID = 500000。如果我在視圖之外直接這樣做：

SELECT 
 ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
WHERE ABS(t.ID) = 500000
OPTION (MAXDOP 1);

BASE_TABLE.ID由於該ABS()功能，我無法使用索引。但是，過濾器被下推到掃描中，預計只會返回一行。這可以提高性能：

謂詞

使用此查詢：

SELECT ID, CNT
FROM dbo.SNEAKY_VIEW
WHERE ID = 500000;

我們得到了一個效率較低的計劃。過濾器在ID = 500000計劃的最後實現，因此視窗函式將針對幾乎一百萬個不必要的行進行評估：

沒有下推

這可能比你要找的更深入一些。回到您最初的問題，我會說針對視圖的查詢與潛在查詢 1 最相似。我聽說過有關某些情況下不正確的謠言。例如，假設如果您有許多嵌套視圖，那麼查詢優化器可能難以解包它們，並且您最終可能會因為這個原因而得到一個優化不佳的查詢。我不知道這在目前版本的 SQL Server 中是否正確，但值得避免這樣做，以便其他嘗試理解您的程式碼的人可以更輕鬆地使用它。

對於 SQL Server（我不知道對於其他數據庫系統）。

  1. 創建視圖不會給您帶來任何性能提升，但可以幫助您抽象底層對象並為您的使用者管理對象級安全性。
  2. 使用索引視圖絕對可以幫助您提前準備數據（聚合、計算等），但您需要小心，如此處詳細說明的那樣。

如果我使用視圖和基礎表執行 select 語句，您可以看到您獲得相同的執行計劃並且成本相同（每個 50%）。

--Using View
SELECT [CustomerID]
     ,[CustomerName]
     ,[CustomerCategoryName]
     ,[PrimaryContact]
     ,[AlternateContact]
     ,[PhoneNumber]
     ,[FaxNumber]
     ,[BuyingGroupName]
     ,[WebsiteURL]
     ,[DeliveryMethod]
     ,[CityName]
     ,[DeliveryLocation]
     ,[DeliveryRun]
     ,[RunPosition]
 FROM [WideWorldImporters].[Website].[Customers]
 WHERE CustomerID=2
 GO
 --Using underlying table (view definition)
 SELECT s.CustomerID,
      s.CustomerName,
      sc.CustomerCategoryName,
      pp.FullName AS PrimaryContact,
      ap.FullName AS AlternateContact,
      s.PhoneNumber,
      s.FaxNumber,
      bg.BuyingGroupName,
      s.WebsiteURL,
      dm.DeliveryMethodName AS DeliveryMethod,
      c.CityName AS CityName,
      s.DeliveryLocation AS DeliveryLocation,
      s.DeliveryRun,
      s.RunPosition
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
LEFT OUTER JOIN Sales.BuyingGroups AS bg
ON s.BuyingGroupID = bg.BuyingGroupID
LEFT OUTER JOIN [Application].DeliveryMethods AS dm
ON s.DeliveryMethodID = dm.DeliveryMethodID
LEFT OUTER JOIN [Application].Cities AS c
ON s.DeliveryCityID = c.CityID
WHERE s.CustomerID=2

在此處輸入圖像描述

在此處輸入圖像描述

引用自：https://dba.stackexchange.com/questions/172208

相關問答

Sql-Server

如何針對索引改進受 DateAdd() 約束的視圖中 1 行的估計

  • February 8, 2021
檢視問答
Sql-Server-2016

根據執行計劃創建索引

  • August 22, 2020
檢視問答
Postgresql

視圖對 PostgreSQL 的性能有害嗎？

  • August 2, 2020
檢視問答
Mysql

什麼時候在 MySQL 中使用視圖？

  • November 7, 2018
檢視問答
Sql-Server

OLTP 索引視圖或 SQL 計算

  • November 12, 2017
檢視問答
Sql-Server

我的觀點是否會減慢更新速度？

  • September 29, 2016
檢視問答