如何优化查询以有效地找到分组最大值？

针对高效分组最大值的优化查询

原始问题：

查询

select * 
from records 
where id in ( select max(id) from records group by option_id )

对整个记录表执行顺序扫描以确定每个 option_id 的最大 ID。这种方法效率低下，尤其是对于大型表。

解决方案：横向联接

一种解决方案是利用横向联接来获取子查询中每个 option_id 的最大 ID：

select r.*
from records r
cross join lateral (
    select max(id) as max_id
    from records
    where option_id = r.option_id
) m
where r.id = m.max_id

此查询使用横向联接来计算单独子查询中的最大 ID。结果与原始记录表连接，仅过滤具有最大 ID 的行。

创建专用索引

另一个优化是在记录表上创建专用索引，用于存储每个 option_id 的最大 ID：

CREATE INDEX idx_max_id ON records (option_id, max(id))

此索引可以直接查找给定 option_id 的最大 ID，从而无需原始索引子查询：

select * 
from records r
where (option_id, id) in (
    select option_id, max(id) from records group by option_id
)

基于索引的方式显着减少了表的访问次数，使得对于大表的查询更加高效。

以上是如何优化查询以有效地找到分组最大值？的详细内容。更多信息请关注PHP中文网其他相关文章！