sql - 为什么在连接使用 select max 的列时此 SQL(连接)查询很慢？

Question

如果有所不同，我将使用 Apache Derby 10.8。

我有一个非常简单的数据库，其中包含一个包含项目的表格和一个包含这些项目的投标的表格。我想选择每个加入该项目的出价最高的项目。以下是我第一次尝试它，性能很糟糕:

select
    item.id as item_id,
    item.name as item_name,
    item.retail_value as item_retail_value,
    item.vendor as item_vendor,
    bid.bid_amount as bid_amount,
    bid.bidder_name as bid_bidder_name,
    bid.bidder_phone as bid_bidder_phone,
    bid.operator_name as bid_operator_name
from item
    left outer join bid on bid.item_id = item.id and
    bid.bid_amount = (select max(bid.bid_amount) from bid where bid.item_id = item.id and bid.status = 'OK')

我创建了一组使用 282 个项目的测试数据，每个项目有 200 个出价(总共 56400 个出价)。上面的查询大约需要 30-40 秒才能运行。如果我选择每个项目并手动循环选择每个项目的高出价，则只需不到一秒钟。

我试过索引 bid.bid_amount 和 bid.status 列，但它没有做任何值得注意的事情。 SQL 不是我最擅长的领域，所以如果有人愿意解释 为什么 查询这么慢，我真的很感激。

Answer 1

查询很慢，因为您正在执行所谓的相关子查询——它为每一行运行 max。

尝试这样的事情:

select
    item.id as item_id,
    item.name as item_name,
    item.retail_value as item_retail_value,
    item.vendor as item_vendor,
    bid.bid_amount as bid_amount,
    bid.bidder_name as bid_bidder_name,
    bid.bidder_phone as bid_bidder_phone,
    bid.operator_name as bid_operator_name
from 
    item
    left outer join (
        select 
            item_id, 
            MAX(bid_amount) maxamount 
        from 
            bid 
        where 
            status = 'OK' 
        group by 
            item_id
    ) b1 on
        item.id = b1.item_id
    left outer join bid on
        bid.item_id = item.id
        and bid.bid_amount = b1.maxamount

这个子查询只运行一次，它会运行得更快。