大規模なデータベース内の各カテゴリから最新のアイテムを効率的に取得するには、SQL クエリを最適化するにはどうすればよいですか?

大規模なデータセットの SQL クエリ パフォーマンスの向上

効率的な SQL クエリは、特に大規模なデータベースや複雑なデータ取得を扱う場合、データベースのパフォーマンスを維持するために不可欠です。これは、相互接続された複数のテーブルを操作する場合に特に重要です。

チャレンジ: カテゴリごとに最新のアイテムを取得する

ID によって分類されたアイテムを格納するデータベースを想像してください。目標は、各カテゴリに最近追加された 4 つのアイテムを表示することです。 カテゴリごとに複数のクエリを使用する従来の方法では、特に多数のカテゴリの場合、データベースに過度の負荷がかかります。

最適化されたクエリ: グループ化と結合の活用

次の最適化されたクエリは、外部結合とグループ化を使用して、単一のデータベース パスで目的のデータを取得します。

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;

このクエリは、同じカテゴリ内の各アイテム (i1) と新しいアイテム (i2) を比較します。 新しい項目が 4 つ未満しか存在しない場合、i1 が結果に含まれます。

代替アプローチ

各カテゴリ内の行番号付け用の MySQL ユーザー変数などの他の方法では、代替ソリューションが提供されます。

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;

MySQL 8.0.3 以降の場合、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;

パフォーマンス上の利点

これらの最適化されたクエリにより、カテゴリの数が増えるにつれてパフォーマンスが大幅に向上します。データベースの負荷を最小限に抑え、リソースの使用を最適化することにより、アプリケーションは大規模なデータセットであっても効率的なデータ取得を保証します。

以上が大規模なデータベース内の各カテゴリから最新のアイテムを効率的に取得するには、SQL クエリを最適化するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。