動態查詢的索引解決方案

我有一個貸記/借記表（500 萬條記錄）。應用程序必須提供一個 UI 視圖來搜尋提供 N 個條件的數據：

• 日期類型（必填），例如。創建日期或截止日期
• 日期範圍（必填）
• 狀態（已付費、未付費或兩者兼有）
• 文件號碼
• 客戶ID
• 付款方式（信用卡、現金等）

使用者必須提供前兩個標準，但可以提供或不提供另一個。所以我有很多組合。

我想我將不得不創建許多索引，例如：

• ix_search_customerID_dueDate (customerID, dueDate)
• ix_search_customerID_creationDate (customerID, creationDate)

有無數種組合。我使用 C# 和實體框架根據提供的值生成查詢，這很容易做到，但我不知道如何創建索引以涵蓋所有可能性。是否有可能或者我應該更改 UI 邏輯？

我閱讀了這個答案，因此基於此我相信如果我創建一個索引（creationDate、status、document、customerID、typeOfPayment）並且使用者僅提供例如客戶 ID，則該索引將不起作用。

您不能創建支持所有這些可選參數的單個索引。您可以創建多個索引來盡力支持最常見的參數組合，但要支持所有可能的組合，成本可能不值得（並且您必須衡量對工作負載寫入部分的影響 -索引不是免費的）。

我對“客戶可以隨心所欲地搜尋”問題的解決方案是創建廚房水槽程序，該程序使用動態 SQL 根據提供的參數構造 where 子句。這樣做是允許 SQL Server 為每個參數組合編譯一個單獨的計劃，這樣您就沒有一個大規模的查詢來嘗試針對所有場景進行優化，但實際上只能針對極少數情況進行優化。基本方法是：

CREATE PROCEDURE dbo.KitchenSink
 @param1 datatype = NULL,
 @param2 datatype = NULL
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.table 
   WHERE 1 = 1'

   + CASE WHEN @param1 IS NULL THEN N'' ELSE
       N' AND column1 = @param1' END

   + CASE WHEN @param2 IS NULL THEN N'' ELSE
       N' AND column2 = @param2' END

   ...
 ;

 EXEC sys.sp_executesql @sql, N'@param1 datatype, @param2 datatype, ...',
        @param1, @param2, ...;
END
GO

這將使您有機會充分利用具有與提供的參數組合對齊的鍵列的任何索引。您將不得不承認某些查詢將無法很好地執行，但這應該有助於最大限度地減少這些情況。

額外注意事項：

• 您可以OPTION (RECOMPILE)在動態 SQL 內部使用來阻止參數嗅探（例如，即使只提供了姓氏，單獨的動態 SQL 也無法幫助您每次都生成最佳計劃，因為搜尋LIKE '%x%'將需要與 不同的計劃LIKE '[a-m]%'）。
• 您應該使用優化即席工作負載設置（請參閱此處和此處），以便 SQL Server 不會將寶貴的計劃記憶體空間浪費在僅使用一次的潛在復雜和大型計劃上。

如果您的 UI 強制您對數字欄位執行 LIKE %xx% 操作，那麼這將允許您的查詢通過跳轉到折扣不需要的記錄的點對索引執行 SEEK 操作。

（Aaron 的程式碼是正確的，但我們在生產環境中有類似的東西，LIKE %% 導致了 SCANS，通過將 SCANS 轉換為 SEEKS，進行以下修改大大提高了性能）

--Variable Recieved by stored procedure
DECLARE @customerid INT= 1000

--Startpoint enabling SEEK operations against Index
DECLARE @customer_start_point INT 

SET @customer_start_point = ISNULL(@customerid,0)


DECLARE @STR = 
'
SELECT
   *
FROM
   dbo.table
WHERE
   --The query will perform a SEEK and skip the first 1000 records
   CustomerID >= ' + @customer_start_point


+ CASE WHEN @customerid IS NULL THEN ''
       ELSE ' AND CAST(CustomerID AS VARCHAR(20)) LIKE ''%' + CAST(@customerid AS VARCHAR(20)) + '%'''  
       END

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