MySQL および PostgreSQL でグループごとの最大クエリを最適化するにはどうすればよいですか?

グループごとの最大クエリの最適化

option_id でグループ化された最大 ID を持つレコードを取得することを目的とした提供されたクエリは、すべてをスキャンするためパフォーマンスの問題に直面しています。行。これに対処するには、option_id ごとに最大 ID 値を格納するプログラム インデックスの利用を検討してください。このアプローチにより、インデックスをスキャンするだけで最大値を効率的に取得できます。

インデックス付け (option_id, id) や (option_id, id DESC) などの従来のアプローチでは、クエリが適切に最適化されません。

MySQL の最適化

MySQL 5.5このクエリには高度な最適化が組み込まれています。 (option_id, id) のインデックスを作成することで、MySQL は group-by 操作のインデックスを活用し、順次スキャンの必要性を回避します。

参照テーブルによる Postgres の最適化

Postgres 内、レコード内の個別の option_id に対応する一意の option_id を使用して、参照テーブル options を作成します。 table.

CREATE TABLE options ( option_id INT PRIMARY KEY, option TEXT UNIQUE NOT NULL );

INSERT INTO options SELECT DISTINCT option_id, 'option' || option_id FROM records;

相関サブクエリを使用して、option_id ごとにレコードから最大 ID を取得します。

SELECT option_id, (SELECT MAX(id) FROM records WHERE option_id = o.option_id) AS max_id
FROM options o
ORDER BY 1;

このアプローチにより、インデックスのみのスキャンが有効になり、レコード内の行アクセスが最小限に抑えられます。このクエリの理想的なインデックス:

CREATE INDEX ON records (option_id, id DESC NULLS LAST);

LATERAL 結合を使用した Postgres の最適化 (Postgres 9.3 )

あるいは、Postgres 9.3 以降では、LATERAL 結合を使用して、同様のことを達成する最適化:

SELECT *
FROM records
LATERAL (
  SELECT MAX(id) OVER (PARTITION BY option_id) AS max_id
  FROM records
) AS m
WHERE records.id = m.max_id;

このアプローチでは、効率的なクエリ実行のためにインデックスのみのスキャンも利用します。

以上がMySQL および PostgreSQL でグループごとの最大クエリを最適化するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。