在非常大的表上過濾結果集（使用 SQL Server 中的 INCLUDE 索引）

考慮一個非常大的表，其中包含數億行定義為

-- Here ObjectIdName and ObjectTypeName together are unique.
-- Due to their size they aren't indexed, but rather they are
-- hashed to ObjectIdName -> ObjectId and ObjectTypeName -> ObjectType
-- which are used in the index instead. 
CREATE TABLE VeryLarge
(
   ObjectId INT NOT NULL,
   ObjectIdName NVARCHAR(512) NOT NULL,
   ObjectType INT NOT NULL,
   ObjectTypeName NVARCHAR(512) NOT NULL,
   PayLoad VARBINARY(MAX) NULL,
   IsDeleted BIT NOT NULL
);

-- This was originally in the question. By mistake includes the Payload column.
CREATE NONCLUSTERED INDEX IX_VeryLarge1 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, PayLoad, ObjectIdName, ObjectTypeName);

-- This was the index I intended to have, without the Payload column.
CREATE NONCLUSTERED INDEX IX_VeryLarge2 ON VeryLarge(ObjectId, ObjectType) INCLUDE(IsDeleted, ObjectIdName, ObjectTypeName);

如果假設ObjectIdandObjectType參數在 99.99% 的情況下定位單行，那麼在我看來，查詢和索引之間需要相互作用才能如此有效，查詢將始終在合理的時間內以可預測的方式返回（考慮硬體和實際行數）。但是，我不確定應該如何構造查詢。基本上我想做的是首先用ObjectIdandObjectTypeObjectIdName查詢並用and過濾這個小結果集ObjectTypeName（如果有不止一行）。

考慮到這一點，通過定義的索引，以下查詢在 SQL Server 上是否合理？

SELECT
   Payload
FROM
   VeryLarge
WHERE
   ObjectId = @objectId
   AND ObjectType = @objectType
   AND IsDeleted = 0
   AND ObjectIdName = @objectIdName
   AND ObjectTypeName = @objectTypeName;

-- Or alternatively, considering the question:
SELECT
   PayLoad
FROM
(
   SELECT
       PayLoad,
       ObjectIdName,
       ObjectTypeName
   FROM
       VeryLarge
   WHERE
       ObjectId = @objectId
       AND ObjectType = @objectType
       AND IsDeleted = 0
) AS x
WHERE x.ObjectIdName = @objectIdName AND x.ObjectTypeName = @objectTypeName;

我不知道如何建構這樣一個查詢，該查詢將修剪第一個查詢的結果，該查詢將保證命中索引並僅返回一行或幾行。看起來 usingINCLUDE具有相同的效果，強制和索引搜尋，但我不知道是否會有與此相關的成本。另外，我知道 MySQL 沒有包含索引的這個概念，但它的工作方式類似，我假設過濾最多幾行的結果集將適用於任何支持索引的數據庫，而不管其他結構如何。

這與我在Hashed and heap indexed object storage table insert and query performance中的另一個問題有關，其中定義了表和查詢。

<編輯：附加問題：將這些重複ObjectType列放在同一個表中看起來很浪費。可能是另一個文章的主題，但我相信它們應該被分開到不同的桌子上。那麼問題是，是否應該INNER JOIN避免使用僅在第一次過濾後行數多於行時才執行連接的查詢。以及如何做到這一點。

從您提供的詳細資訊來看，IX_VeryLarge非聚集索引將支持您在問題中顯示的兩個查詢似乎是合理的。您將Payload列鍵入為VARBINARY(MAX)- 如果您希望將大型對象儲存在該列中，我可能不會將其儲存在INCLUDE索引中，因為這會導致索引比其他情況大得多。如果您的絕大多數查詢產生一兩行，則最好對聚集索引/堆（表）進行簡單的查找。您展示的模式是擁有聚集索引/堆並支持包含列的非聚集索引（所謂的覆蓋索引），這種模式非常常見，並且可能是確保良好性能以及良好統計數據的唯一最佳方法.

話雖如此，我不希望第二個查詢優於第一個查詢，因為返回的結果將完全相同。SQL 不是一種過程語言，它是一種聲明性語言。使用過程語言，您可以告訴電腦要做什麼，而使用聲明性語言，您可以描述所需的結果。由於 SQL 是聲明性的，並且由於兩個查詢都返回相同的結果集，因此可以合理地假設 SQL Server 可能會為兩個查詢提供相同的計劃。由於第二個查詢對人類來說更難理解，我傾向於第一個變體。盡量不要智取 SQL Server 查詢優化器，它做不到是很難做到的。

歸根結底，讓您真正了解您的情況、數據以及表定義和輸出要求的最佳選擇的唯一方法是測試它們。

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