如何最佳化 SQL 查詢以有效率地從大型資料庫中的每個類別中檢索最新項目？

提升海量資料集的 SQL 查詢效能

高效的 SQL 查詢對於維護資料庫效能至關重要，尤其是在處理大型資料庫和複雜的資料檢索時。當使用多個互連表時，這一點尤其重要。

挑戰：檢索每個類別的最新項目

想像一個資料庫儲存按 ID 分類的項目。目標是顯示每個類別及其最近新增的四個項目。 每個類別使用多個查詢的傳統方法會導致資料庫負載過重，尤其是在類別數量較多的情況下。

最佳化查詢：利用分組與連結

以下最佳化查詢使用外部連接和分組在單一資料庫傳遞中檢索所需的資料：

<code class="language-sql">SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4;</code>

此查詢將同一類別中的每個項目 (i1) 與較新的項目 (i2) 進行比較。 如果存在的新項目少於四個，則 i1 包含在結果中。

替代方法

其他方法，例如用於每個類別內行編號的 MySQL 使用者變量，提供替代解決方案：

<code class="language-sql">SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 4;</code>

對於 MySQL 8.0.3 及更高版本，SQL 視窗函數提供了標準化方法：

<code class="language-sql">WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id DESC) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;</code>

效能優勢

隨著類別數量的增加，這些最佳化的查詢顯著提高了效能。透過最大限度地減少資料庫負載和優化資源使用，應用程式即使在處理大量資料集的情況下也能確保高效的資料檢索。

以上是如何最佳化 SQL 查詢以有效率地從大型資料庫中的每個類別中檢索最新項目？的詳細內容。更多資訊請關注PHP中文網其他相關文章！