Sql-Server

如何在具有數百萬三元組的表上提高此查詢的性能？

  • April 13, 2020

為了更快地搜尋某些表中的許多字元串欄位，我一直在嘗試使用三元組。

我創建了一個單獨的表來保存它們，並創建了一個查詢來搜尋它們（旨在用於表值函式）。

CREATE TABLE [dbo].[SearchTrigramTwoFieldKey]
(
 [Ordinal]                   BIGINT          NOT NULL,
 [SearchCategoryId]          INTEGER         NOT NULL    CONSTRAINT [FK__SearchTrigramTwoFieldKey_SearchCategoryId_To_dbo.SearchCategory_Id]               FOREIGN KEY([SearchCategoryId])         REFERENCES [dbo].[SearchCategory]([Id]),
 [SearchCategoryColumnId]    INTEGER         NOT NULL    CONSTRAINT [FK__SearchTrigramTwoFieldKey_SearchCategoryColumnId_To_dbo.SearchCategoryColumn_Id]   FOREIGN KEY([SearchCategoryColumnId])   REFERENCES [dbo].[SearchCategoryColumn]([Id]),
 [TableId]                   INTEGER         NOT NULL    CONSTRAINT [FK__SearchTrigramTwoFieldKey_TableId_To_dbo.Table_Id]                                 FOREIGN KEY([TableId])                  REFERENCES [dbo].[Table]([Id]),
 [RecordId1]                 BIGINT          NOT NULL,
 [RecordId2]                 BIGINT          NOT NULL,
 [Trigram]                   NVARCHAR(3)     NOT NULL,
 [IsLastTrigram]             BIT             NOT NULL,
 [RecordColumnTrigramCount]  INTEGER         NOT NULL,

 CONSTRAINT [PK__SearchTrigramTwoFieldKey_SearchCategoryId_SearchCategoryColumnId_TableId_RecordId1_RecordId2_Ordinal]
     PRIMARY KEY
     (
         [SearchCategoryId]          ASC,
         [SearchCategoryColumnId]    ASC,
         [TableId]                   ASC,
         [RecordId1]                 ASC,
         [RecordId2]                 ASC,
         [Ordinal]                   ASC
     ),
)

CREATE UNIQUE NONCLUSTERED INDEX [UNCI__SearchTrigramTwoFieldKey_IsLastTrigram] ON [dbo].[SearchTrigramTwoFieldKey]
(
 [SearchCategoryId] ASC,
 [SearchCategoryColumnId] ASC,
 [TableId] ASC,
 [RecordId1] ASC,
 [RecordId2] ASC,
 [IsLastTrigram] ASC
)
WHERE ([IsLastTrigram]=(1))

最後兩個欄位是為了減少在針對該表的搜尋查詢中需要進行的計算量，以嘗試提高性能，同時使用索引作為對壞數據的預防措施。

插入所有三元組後，該表中大約有 6000 萬條記錄。這個數字幾乎肯定會隨著時間的推移而增加。

為了搜尋它，我編寫了以下查詢：

--Setting up query parameters:
DECLARE @SearchCategoryId INTEGER = 3
DECLARE @SearchCategoryColumnIds AS TABLE([Value] INTEGER NOT NULL)
DECLARE @searchValues AS TABLE([Value] NVARCHAR(4000))


INSERT INTO @searchValues([Value])
VALUES('Land'), ('Ireland')

--The query itself:
SELECT  ROW_NUMBER() OVER (ORDER BY COUNT(CASE WHEN IsExactMatch = 1 THEN 1 END) DESC,
                                 COUNT(*) DESC,
                                 MIN(CASE WHEN IsExactMatch = 0 THEN MinMatchDistanceRowOrder END)) AS [MatchOrder],
     RecordId1,
     RecordId2
FROM
(
SELECT  RecordId1, RecordId2,
     IIF(MIN([T].T2Ordinal) = 1 AND MAX(CAST(T.T2IsLastTrigram AS INTEGER)) = 1, 1, 0) AS IsExactMatch,
     ROW_NUMBER() OVER (ORDER BY MIN(T.T2TrigramCount - T1TrigramCount)) AS MinMatchDistanceRowOrder,
     [SearchValue]
FROM
(SELECT T1.SearchValueNumber,
     T1.SearchValue,
     LAG(T1.Ordinal)         OVER (PARTITION BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T1.SearchValueNumber ORDER BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T1.Ordinal)
                                 AS T1OrdinalLag,
     T1.Ordinal          AS T1Ordinal,
     LEAD(T1.Ordinal)        OVER (PARTITION BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T1.SearchValueNumber ORDER BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T1.Ordinal)
                                 AS T1OrdinalLead,
     T1.NgramCount     AS T1TrigramCount,
     LAG(T2.Ordinal)         OVER (PARTITION BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T1.SearchValueNumber ORDER BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T2.Ordinal, T2.Trigram)
                                 AS T2OrdinalLag,
     T2.Ordinal          AS T2Ordinal,
     LEAD(T2.Ordinal)        OVER (PARTITION BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T1.SearchValueNumber ORDER BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T2.Ordinal, T2.Trigram)
                                 AS T2OrdinalLead,
     T2.IsLastTrigram    AS T2IsLastTrigram,
     MIN(T2.Ordinal)         OVER (PARTITION BY T2.SearchCategoryId, T2.SearchCategoryColumnId, T2.TableId, T2.RecordId1, T2.RecordId2, T1.SearchValueNumber)
                                 AS MinOrdinal,
     T2.RecordColumnTrigramCount  AS T2TrigramCount,
     T2.SearchCategoryId,
     T2.SearchCategoryColumnId,
     T2.TableId,
     T2.RecordId1,
     T2.RecordId2
FROM dbo.SearchTrigramTwoFieldKey AS T2
INNER JOIN
(
 SELECT [Value] FROM @SearchCategoryColumnIds
 UNION ALL
 SELECT NULL) AS scc ON NOT EXISTS(SELECT TOP 1 [Value] FROM @SearchCategoryColumnIds) OR T2.SearchCategoryColumnId = [Value]
INNER JOIN
(
 SELECT SearchValueNumber, SearchValue, ngrams.Ordinal, ngrams.Ngram, ngrams.IsLastNgram, ngrams.NgramCount
 FROM
 (
     SELECT  ROW_NUMBER() OVER (ORDER BY [Value]) AS SearchValueNumber, *
     FROM
     (
         SELECT DISTINCT [Value] AS SearchValue, *
         FROM @searchValues
     ) AS T
 ) AS [sv]
 CROSS APPLY dbo.fnGenerateNgrams([sv].[Value], DEFAULT) AS ngrams
) AS T1 ON T1.Ngram = T2.Trigram
WHERE T2.SearchCategoryId = @SearchCategoryId) AS T
WHERE
(
 (   T1OrdinalLead IS NULL OR T1OrdinalLead = T1Ordinal+1)
OR  (T1OrdinalLag IS NULL OR T1OrdinalLag = T1Ordinal-1)
)
AND
(
 (   T2OrdinalLead IS NULL OR T2OrdinalLead = T2Ordinal+1)
OR  (T2OrdinalLag IS NULL OR T2OrdinalLag = T2Ordinal-1)
)
AND T2TrigramCount >= T1TrigramCount
GROUP BY SearchCategoryId, SearchCategoryColumnId, TableId, RecordId1, RecordId2, [SearchValue]
HAVING COUNT(*) >= (SELECT TOP 1 NGramCount FROM dbo.fnGenerateNgrams([SearchValue], DEFAULT))
) AS T
GROUP BY RecordId1, RecordId2
HAVING COUNT(DISTINCT [SearchValue]) = (SELECT COUNT(DISTINCT [Value]) FROM @searchValues)
ORDER BY MatchOrder ASC
OPTION(RECOMPILE)

關於查詢的一些註釋：

  • 旨在獲取多個搜尋詞，為這些詞生成三元組，並將它們與三元組表中的三元組進行匹配。

    • 對於每個三元組集合所針對的每個記錄，所有指定的搜尋詞必須至少匹配一次。

  • 分解時，應保留三元組順序，以便找到正確的匹配項。

  • 應該返回 MatchOrder 以便我們可以在需要時按最接近的匹配項進行排序。

    • 匹配旨在按完全匹配的數量排序，

此查詢是唯一將查詢此表的查詢。每隔一段時間就會有數據插入和刪除以刷新更新的數據，但目前這些速度並不是特別關注的問題。

執行時間因指定的搜尋值而有很大差異，即使它只是單個值（我見過一些短至 6 秒，而另一些只需要 5 分鐘就兩個字），我懷疑（但不確定）這是由於有多少數據與某些三元組匹配，即使它們最終不是完全匹配。

通過查看 SSMS 和計劃資源管理器中的執行計劃，我相信它看起來像是在消耗時間，但我不確定如何使用索引正確糾正這個問題。

這些是我迄今為止在 trigram 表上創建的索引（除了它的主聚集索引和上面的唯一非聚集索引），以試圖提高執行速度：

CREATE NONCLUSTERED INDEX [NCI__SearchTgramTwoFieldKey_SearchCategoryColumnId_TableId_RecordId1_RecordId2_Ordinal_IsLastTgram_RecordColumnTgramCount_Tgram] ON [dbo].[SearchTrigramTwoFieldKey]
(
   [SearchCategoryColumnId] ASC,
   [TableId] ASC,
   [RecordId1] ASC,
   [RecordId2] ASC,
   [Ordinal] ASC,
   [IsLastTrigram] ASC,
   [RecordColumnTrigramCount] ASC,
   [Trigram] ASC
)

CREATE NONCLUSTERED INDEX [NCI__SearchTrigramTwoFieldKey_SearchCategoryColumnId_TableId_RecordId1_RecordId2] ON [dbo].[SearchTrigramTwoFieldKey]
(
   [SearchCategoryColumnId] ASC,
   [TableId] ASC,
   [RecordId1] ASC,
   [RecordId2] ASC
)    

CREATE NONCLUSTERED INDEX [NCI__SearchTrigramTwoFieldKey_SearchCategoryColumnId_TableId_RecordId1_RecordId2_Ordinal] ON [dbo].[SearchTrigramTwoFieldKey]
(
   [SearchCategoryColumnId] ASC,
   [TableId] ASC,
   [RecordId1] ASC,
   [RecordId2] ASC,
   [Ordinal] ASC
)    

CREATE NONCLUSTERED INDEX [NCI__SearchTrigramTwoFieldKey_SearchCategoryId_Trigram__Include_IsLastTrigram_RecordColumnTrigramCount] ON [dbo].[SearchTrigramTwoFieldKey]
(
   [SearchCategoryId] ASC,
   [Trigram] ASC
)
INCLUDE (   [IsLastTrigram], RecordColumnTrigramCount])

在這四個索引中，只有最後一個是我推薦創建的索引。其他的都是實驗性的，試圖提高性能。

執行計劃：https ://www.brentozar.com/pastetheplan/?id=HyFZDlTDI

儘管我很努力，但性能仍遠未達到我想要的水平。我想嘗試盡可能加快執行時間，最好的情況是一個或多個搜尋詞花費不到一秒的時間，但我不知道這有多可行。

我缺乏足夠的索引知識來理解如何正確解決這個問題（假設索引是解決這個問題的正確方法）。我希望了解我可以做些什麼來提高這裡的性能（以及為什麼它會提高性能），無論是通過適當的索引還是通過改進查詢同時保持其功能，如果可能的話。

我已經包含了查詢和表定義，以防它們揭示了一些我沒有意識到存在的可怕（但可糾正）的低效率。

我不認為索引是您的（主要）問題。

該執行計劃中有一些與時間相關的奇怪而令人不安的事情。首先是持續時間和 CPU 之間的差異：

<QueryTimeStats CpuTime="93275" ElapsedTime="315874" />

查詢執行了 5 分鐘，但只使用了 1.5 分鐘的 CPU 時間（在 DOP 1 處）。這種差異通常意味著 SQL Server 正在等待某些共享資源，並且在執行查詢時沒有取得進展。

在執行計劃中擷取了一些等待統計資訊：

<WaitStats>
 <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="103626" WaitCount="35266" />
 <Wait WaitType="PAGELATCH_EX" WaitTimeMs="77512" WaitCount="2742411" />
 <Wait WaitType="PAGELATCH_SH" WaitTimeMs="66027" WaitCount="2037681" />
 <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="7798" WaitCount="2440" />
 <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="41" WaitCount="38422" />
</WaitStats>

資源管理器

RESOURCE_GOVERNOR_IDLE等待時間超過 103 秒。通常情況下，我建議您檢查伺服器配置，並確保您沒有受到過多的 CPU 分配限制，使用如下查詢：

SELECT 
   rgrp.[name],
   rgrp.min_cpu_percent,
   rgrp.max_cpu_percent, 
   rgrp.cap_cpu_percent
FROM sys.dm_resource_governor_resource_pools rgrp;

由於您使用的是 Azure SQL 數據庫，因此您需要升級到具有更多計算的層。我在計劃 XML 中也注意到了這一點：

NonParallelPlanReason="EstimatedDOPIsOne"

我認為最小的 vCore 選項是 2，因此這意味著您正在使用最小的 DTU 模型產品之一（小於 S3）。

嘗試一次將數據庫擴展一層，直到您看到RESOURCE_GOVERNOR_IDLE等待減少到更可接受的水平。

注意：這也可能導致 7 秒SOS_SCHEDULER_YIELD。

鎖存等待

您還有 143 秒的閂鎖等待時間。通常我會懷疑這是某種 tempdb 爭用，但在此查詢中沒有太多使用 tempdb 的證據（有一個 ~200 MB 的散列溢出，還有一些小的假離線）。

鑑於 CPU 上限問題，我懷疑這種意外高級別的閂鎖等待也與正在使用的 Azure 服務層有關。

隨著等待

減去上面討論的 246 秒等待，查詢執行時間從 315 秒減少到 69 秒。它仍然不是很神奇，但肯定比 5 分鐘要好。在更高的 Azure 服務層上，您還可以從並行執行中受益，從而進一步減少執行時間。

其他方法

不同的三元組實現

如果您不想在這個問題上“扔硬體”，並且對替代方法感興趣，Paul White 編寫了一個非常注重性能的三元組搜尋函式並在這里分享：SQL Server 中的三元組萬用字元字元串搜尋

當然，這從本質上改變了你的整個方法，所以你必須權衡廢棄/重寫你迄今為止所做的事情與增加 Azure 成本的成本與收益。

利用批處理模式

作為一個實驗，Conor Cunningham 建議嘗試利用批處理模式——或者在行儲存上使用新的批處理模式，或者通過在表上創建列儲存索引：

…我們在更新的兼容級別中在行儲存上添加了批處理模式，因此請考慮這一點 - 但是，它將在更高的 DOP 下提供更多的好處。此外，列儲存索引可能是一個需要考慮的實驗……

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

相關問答

Sql-Server

TSQL 慢查詢，未按預期使用索引

  • September 13, 2022
檢視問答
Sql-Server

如何找出目前查詢計劃中的“Key Lookups”？

  • May 4, 2022
檢視問答
Sql-Server

為什麼不使用此日期時間索引？

  • April 20, 2020
檢視問答
Sql-Server

分區掃描對大小數據的影響

  • December 9, 2019
檢視問答
Sql-Server

綜合指數

  • May 6, 2019
檢視問答
Sql-Server

覆蓋指數問題

  • May 6, 2019
檢視問答