主持人 > 博客 > 管理 > 搜尋結果輸出和效能問題

搜尋結果輸出和效能問題

我們熟悉的所有應用程式中的典型場景之一是根據一定的條件搜尋資料並以易於閱讀的形式顯示它。 還可能有用於排序、分組和分頁的附加選項。 從理論上講，這個任務是微不足道的，但在解決它時，許多開發人員會犯一些錯誤，導致生產力下降。 讓我們嘗試考慮解決此問題的各種選項，並提出選擇最有效的實施方案的建議。

搜尋結果輸出和效能問題

分頁選項#1

我想到的最簡單的選項是以最經典的形式逐頁顯示搜尋結果。

搜尋結果輸出和效能問題
假設您的應用程式使用關聯式資料庫。 在這種情況下，要以此形式顯示訊息，您將需要執行兩個 SQL 查詢：

  • 取得目前頁面的行。
  • 計算與搜尋條件相對應的總行數 - 這是顯示頁面所必需的。

讓我們以測試 MS SQL 資料庫為例來看看第一個查詢 冒險工廠 2016年伺服器。 為此，我們將使用 Sales.SalesOrderHeader 表：

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

上面的查詢將返回清單中的前 50 個訂單，按新增日期降序排序，換句話說，即最近的 50 個訂單。

它在測試庫上運行得很快，但我們看一下執行計劃和 I/O 統計數據：

搜尋結果輸出和效能問題

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

您可以透過在查詢執行時間執行 SET STATISTICS IO ON 指令來取得每個查詢的 I/O 統計資料。

從執行計劃中可以看出，最消耗資源的選項是按新增日期對來源表的所有行進行排序。 問題是表中出現的行越多，排序就越「困難」。 實際上應該避免這樣的情況，所以我們為添加日期添加一個索引，看看資源消耗是否發生了變化：

搜尋結果輸出和效能問題

Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

顯然情況已經好很多了。 但所有問題都解決了嗎？ 讓我們更改查詢以搜尋商品總成本超過 100 美元的訂單：

SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

搜尋結果輸出和效能問題

Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

我們遇到了一個有趣的情況：查詢計劃並不比前一個差多少，但實際的邏輯讀取次數幾乎是全表掃描的兩倍。 有一個出路 - 如果我們從一個已經存在的索引創建一個複合索引，並將商品總價添加為第二個字段，我們將再次獲得 165 次邏輯讀取：

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

這一系列的例子可以持續很長一段時間，但是我在這裡想表達的兩個主要想法是：

  • 在搜尋查詢中新增任何新標準或排序順序都會對搜尋查詢的速度產生重大影響。
  • 但是，如果我們只需要減去部分數據，而不是所有與搜尋字詞相符的結果，則有很多方法可以優化此類查詢。

現在讓我們繼續討論一開始提到的第二個查詢 - 計算滿足搜尋條件的記錄數。 讓我們舉同樣的例子 - 搜尋超過 100 美元的訂單：

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

鑑於上述綜合指數，我們得到：

搜尋結果輸出和效能問題

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

查詢遍歷整個索引這一事實並不奇怪，因為 SubTotal 欄位不在第一個位置，因此查詢無法使用它。 透過在 SubTotal 欄位上新增另一個索引解決了該問題，結果只提供了 48 個邏輯讀取。

您可以再舉幾個計數數量請求的範例，但本質仍然是相同的： 接收一條數據和統計總數是兩個根本不同的請求，並且每個都需要自己的最佳化措施。 一般來說，您無法找到兩個查詢相同有效的索引組合。

因此，在開發此類搜尋解決方案時應明確的重要要求之一是查看找到的物件總數對於企業來說是否真的很重要。 常發生這樣的情況：不。 在我看來，透過特定頁碼進行導覽是一種範圍非常狹窄的解決方案，因為大多數分頁場景看起來都像是「轉到下一頁」。

分頁選項#2

我們假設使用者不關心知道找到的物件總數。 讓我們嘗試簡化搜尋頁面：

搜尋結果輸出和效能問題
事實上，唯一改變的是沒有辦法導航到特定的頁碼，而且現在這個表不需要知道有多少頁碼就可以顯示它。 但問題出現了——表格如何知道是否有下一頁的資料（以便正確顯示「下一頁」連結）？

答案很簡單：您可以從資料庫中讀取比顯示所需的多一筆記錄，這條「附加」記錄的存在將顯示是否還有下一部分。 這樣，您只需執行一個請求即可獲取一頁數據，這顯著提高了效能，並且更容易支援此類功能。 在我的實踐中，有一個案例，拒絕統計記錄總數使結果交付速度加快了4-5倍。

這種方法有幾個用戶介面選項：“後退”和“前進”命令，如上例所示，“加載更多”按鈕，它只是將新部分添加到顯示的結果中，“無限滾動”，它可以工作遵循“加載更多”的原則，但獲取下一部分的信號是讓用戶將所有顯示的結果滾動到最後。 無論採用哪種視覺解決方案，資料採樣的原理都是相同的。

分頁實現的細微差別

上面給出的所有查詢範例都使用「偏移+計數」方法，查詢本身指定結果行的順序以及需要傳回的行數。 首先，讓我們看看在這種情況下如何最好地組織參數傳遞。 在實踐中，我遇到了以下幾種方法：

  • 請求的頁面序號（pageIndex）、頁面大小（pageSize）。
  • 傳回的第一筆記錄的序號（startIndex），結果中的最大記錄數（count）。
  • 傳回的第一筆記錄的序號（startIndex），傳回的最後一筆記錄的序號（endIndex）。

乍一看，這似乎很簡單，沒有什麼區別。 但事實並非如此 - 最方便和通用的選項是第二個（startIndex，count）。 有幾個原因：

  • 對於上面給出的+1條目校對方法，第一個帶有pageIndex和pageSize的選項非常不方便。 例如，我們想要每頁顯示 50 個貼文。 根據上述演算法，您需要多讀取一筆記錄。 如果伺服器上沒有實現這個“+1”，那麼對於第一頁，我們必須請求從 1 到 51 的記錄，對於第二頁，我們必須請求從 51 到 101 的記錄，依此類推。 如果指定頁面大小為 51 並增加 pageIndex，則第二頁將從 52 返回 102，依此類推。 因此，在第一個選項中，正確實現轉到下一頁的按鈕的唯一方法是讓伺服器校對「額外」行，這將是一個非常隱含的細微差別。
  • 第三個選項根本沒有意義，因為要在大多數資料庫中執行查詢，您仍然需要傳遞計數而不是最後一筆記錄的索引。 從 endIndex 中減去 startIndex 可能是一個簡單的算術運算，但這裡是多餘的。

現在我們來描述一下透過「偏移+數量」來實現分頁的缺點：

  • 檢索每個後續頁面將比前一頁更昂貴且更慢，因為資料庫仍需要根據搜尋和排序標準「從頭開始」遍歷所有記錄，然後停在所需的片段處。
  • 並非所有 DBMS 都支援這種方法。

有一些替代方案，但它們也不完美。 第一種方法稱為“鍵集分頁”或“seek方法”，如下：接收到一部分後，可以記住頁面上最後一筆記錄中的欄位值，然後使用它們來取得下一部分。 例如，我們執行以下查詢：

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

在最後一筆記錄中，我們得到了訂單日期值「2014-06-29」。 然後要取得下一頁，您可以嘗試執行以下操作：

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

問題是 OrderDate 是一個非唯一字段，上面指定的條件可能會丟失很多必需的行。 為了使該查詢更加明確，您需要在條件下新增一個唯一欄位（假設 75074 是第一部分中主鍵的最後一個值）：

SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
   OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

此選項可以正常工作，但通常很難最佳化，因為條件包含 OR 運算子。 如果主鍵的值隨著 OrderDate 的增加而增加，則可以透過僅保留按 SalesOrderID 進行篩選來簡化條件。 但如果主鍵的值和結果排序的欄位之間沒有嚴格的相關性，那麼在大多數 DBMS 中都無法避免這種 OR。 我知道的例外是PostgreSQL，它完全支援元組比較，上面的條件可以寫成「WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)」。 給定具有這兩個欄位的複合鍵，這樣的查詢應該相當容易。

例如，可以找到第二種替代方法 ElasticSearch 滾動式 API 或 宇宙數據庫 — 當請求除了資料之外還傳回一個特殊識別碼時，您可以使用該識別碼取得下一部分資料。 如果此標識符具有無限的生命週期（如在 Comsos DB 中），那麼這是透過頁面之間的順序轉換來實現分頁的好方法（上面提到的選項#2）。 它可能的缺點：並非所有 DBMS 都支援它； 產生的下一個區塊標識符可能具有有限的生命週期，這通常不適合實現使用者互動（例如ElasticSearch滾動API）。

複雜過濾

讓我們把任務進一步複雜化。 假設需要實現所謂的分面搜索，這對於在線商店的每個人來說都非常熟悉。 上面基於訂單表的範例在這種情況下不是很說明問題，所以讓我們從 AdventureWorks 資料庫切換到 Product 表：

搜尋結果輸出和效能問題
分面搜尋背後的想法是什麼？ 事實上，對於每個過濾器元素，都會顯示滿足此條件的記錄數 考慮到在所有其他類別中選擇的篩選器.

例如，如果我們在此範例中選擇自行車類別和黑色，則表格將僅顯示黑色自行車，但：

  • 對於「類別」組中的每個條件，該類別的產品數量將顯示為黑色。
  • 對於「顏色」組的每個標準，將顯示該顏色的自行車數量。

以下是此類條件的結果輸出範例：

搜尋結果輸出和效能問題
如果您也檢查「服裝」類別，表格還將顯示有庫存的黑色衣服。 「顏色」部分中的黑色產品數量也將根據新條件重新計算，只有「類別」部分不會發生任何變化......我希望這些例子足以理解通常的分面搜尋演算法。

現在讓我們想像一下如何在關係基礎上實現這一點。 每組條件（例如類別和顏色）都需要單獨的查詢：

SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
  INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC

搜尋結果輸出和效能問題

SELECT Color, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC

搜尋結果輸出和效能問題
這個解決方案有什麼問題？ 它非常簡單——它的擴展性不好。 每個過濾器部分都需要一個單獨的查詢來計算數量，而這些查詢並不是最簡單的。 在線上商店中，某些類別可能有幾十個過濾器部分，這可能會對效能造成嚴重問題。

通常在這些陳述之後我會得到一些解決方案，即：

  • 將所有數量計數合併到一個查詢中。 從技術上講，使用 UNION 關鍵字可以實現這一點，但它不會對效能有太大幫助 - 資料庫仍然必須從頭開始執行每個片段。
  • 緩存數量。 幾乎每次我描述問題時都會向我建議這一點。 需要注意的是，這通常是不可能的。 假設我們有 10 個“方面”，每個方面都有 5 個值。 與在同一在線商店中看到的情況相比，這是一個非常“溫和”的情況。 一個方面元素的選擇會影響其他 9 個方面的數量，換句話說，對於每個標準組合，數量可能不同。 在我們的範例中，使用者總共可以選擇 50 個條件；因此，將有 250 種可能的組合。沒有足夠的記憶體或時間來填充這樣的資料數組。 在這裡你可以反對並說並非所有組合都是真實的，而且用戶很少選擇超過 5-10 個標準。 是的，可以進行延遲加載並僅緩存已選擇的數量，但是選擇越多，此類快取的效率就越低，並且響應時間問題就越明顯（特別是如果資料集定期更改）。

幸運的是，此類問題長期以來已有相當有效的解決方案，可以在大量數據上進行可預測的工作。 對於這些選項中的任何一個，將構面的重新計算和接收結果頁面劃分為對伺服器的兩個並行呼叫並組織使用者介面，使得按構面載入資料「不會幹擾」顯示搜尋結果。

  • 盡可能少地調用“方面”的完全重新計算。 例如，不要在每次搜尋條件發生變化時重新計算所有內容，而是尋找符合當前條件的結果總數並提示用戶顯示它們 - “找到 1425 條記錄，顯示嗎？” 使用者可以繼續更改搜尋詞或點擊「顯示」按鈕。 只有在第二種情況下，所有獲取結果和重新計算所有「方面」數量的請求才會被執行。 在這種情況下，正如您可以輕鬆看到的，您將必須處理一個請求以獲得結果總數及其最佳化。 這種方法在許多小型網路商店都可以找到。 顯然，這不是解決這個問題的靈丹妙藥，但在簡單的情況下它可以是一個很好的折衷方案。
  • 使用搜尋引擎尋找結果並計算方面，例如 Solr、ElasticSearch、Sphinx 等。 所有這些都旨在建立“facet”，並且由於倒排索引，可以非常有效地完成此操作。 搜尋引擎如何運作，為什麼在這種情況下它們比通用資料庫更有效，有哪些實踐和陷阱 - 這是另一篇文章的主題。 這裡我想提請大家注意的是，搜尋引擎不能取代主資料儲存；它只是作為補充：主資料庫中與搜尋相關的任何變更都會同步到搜尋索引中； 搜尋引擎通常只與搜尋引擎交互，不會訪問主資料庫。 這裡最重要的一點是如何可靠地組織這種同步。 這一切都取決於「反應時間」的要求。 如果主資料庫中的變更與其在搜尋中「表現」之間的時間並不重要，您可以建立一個服務，每隔幾分鐘搜尋最近更改的記錄並為其建立索引。 如果您想要最短的回應時間，您可以實施類似的方法 交易寄件箱 將更新傳送到搜尋服務。

發現

  1. 實現伺服器端分頁是一個非常複雜的問題，並且僅對於快速成長或簡單的大型資料集才有意義。 對於如何評估“大”或“快速成長”，沒有絕對準確的方法，但我將遵循這種方法：
    • 如果接收到完整的資料集合，考慮到伺服器時間和網路傳輸，通常符合效能要求，則在伺服器端實現分頁是沒有意義的。
    • 可能存在一種情況，預計在不久的將來不會出現效能問題，因為數據很少，但數據收集不斷增長。 如果將來的某組資料可能不再滿足前面的一點，最好立即開始分頁。
  2. 如果業務方面沒有嚴格要求顯示結果總數或顯示頁碼，且您的系統沒有搜尋引擎，那麼最好不要實現這些要點並考慮選項#2。
  3. 如果對分面搜尋有明確的要求，那麼在不犧牲效能的情況下，您有兩種選擇：
    • 不要在每次搜尋條件變更時重新計算所有數量。
    • 使用 Solr、ElasticSearch、Sphinx 等搜尋引擎。 但應該要理解的是，它不能成為主資料庫的替代品，而應該作為主儲存的補充來解決搜尋問題。
  4. 此外，在分面搜尋的情況下，將搜尋結果頁面的檢索和計數分成兩個並行請求是有意義的。 計數數量可能比獲得結果花費更長的時間，而結果對使用者來說更重要。
  5. 如果您使用 SQL 資料庫進行搜索，則與此部分相關的任何程式碼變更都應該在適當資料量（超過即時資料庫中的資料量）上進行良好的效能測試。 也建議對資料庫的所有實例（尤其是“即時”實例）上的查詢執行時間進行監視。 即使在開發階段查詢計劃一切正常，但隨著資料量的增長，情況可能會發生顯著變化。

來源： www.habr.com

添加評論