如何优化 MySQL 和 PostgreSQL 中的分组最大查询？

优化分组最大查询

提供的查询旨在检索按 option_id 分组的最大 ID 记录，由于扫描所有记录而面临性能问题行。为了解决这个问题，请考虑使用存储每个 option_id 的最大 ID 值的编程索引。这种方法只需扫描索引即可高效检索最大值。

传统方法，例如索引 (option_id, id) 或 (option_id, id DESC)，不能充分优化查询。

MySQL 的优化

MySQL 5.5 为此引入了高级优化 询问。通过索引 (option_id, id)，MySQL 利用索引进行分组操作，避免了顺序扫描的需要。

Postgres 使用参考表优化

在 Postgres 中，创建一个参考表 options，其具有与记录中不同 option_ids 相对应的唯一 option_ids

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);

Postgres Optimization with LATERAL Join (Postgres 9.3 )

或者，在 Postgres 9.3 及更高版本中，使用 LATERAL join 来实现类似的优化：

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中文网其他相关文章！