MySQL关联查询优化：获取各BID最高优先级数据性能提升方案

这是个典型的关联后取分组最高优先级记录的性能问题，你原来的查询用了相关子查询——每匹配到一条记录就会执行一次MAX(priority)计算，数据量上来后这种逐行计算的方式肯定会拖慢速度，甚至给服务器带来不小负载。下面给你几个更高效的解决方案，以及配套的索引优化建议：

方案一：使用窗口函数（推荐） #

窗口函数是现在处理分组TOP1场景的最优选择之一，只需要对table2做一次分组排序计算，避免了重复的子查询调用：

SELECT t2.id, t2.BID, t2.priority, t2.info
FROM table1
INNER JOIN (
    SELECT 
        *,
        -- 按BID分组，每组内按priority降序排序，标记行号
        ROW_NUMBER() OVER (PARTITION BY BID ORDER BY priority DESC) AS rn
    FROM table2
) t2 ON table1.BID = t2.BID
WHERE table1.relid = 1 AND t2.rn = 1;

说明： #

• ROW_NUMBER()会给每个BID分组内的记录按priority从高到低分配行号，rn=1就是每组的最高优先级记录。
• 如果存在同一个BID有多个相同最高优先级的记录，这个方案只会返回其中一条（若需要稳定结果，可以在ORDER BY后加额外字段，比如id DESC来指定优先级）。

方案二：预聚合最大优先级再关联 #

先一次性计算出每个BID的最高优先级，再通过关联获取对应记录，避免逐行计算：

WITH bid_max_priority AS (
    -- 预计算每个BID的最大优先级
    SELECT BID, MAX(priority) AS max_p
    FROM table2
    GROUP BY BID
)
SELECT t2.id, t2.BID, t2.priority, t2.info
FROM table1
INNER JOIN bid_max_priority bmp ON table1.BID = bmp.BID
INNER JOIN table2 t2 ON bmp.BID = t2.BID AND bmp.max_p = t2.priority
WHERE table1.relid = 1;

说明： #

• 这个方案会返回所有拥有最高优先级的记录（如果同一个BID有多个相同最高优先级的行），适合需要保留全部最高优先级记录的场景。

关键：添加合适的索引 #

不管用哪个方案，索引都是提升性能的核心，建议创建以下索引：

1. 给table1创建复合索引，加速relid过滤和BID关联：

CREATE INDEX idx_table1_relid_bid ON table1(relid, BID);

2. 给table2创建覆盖索引，避免回表查询：

• 对于支持INCLUDE的数据库（如PostgreSQL、SQL Server）：

CREATE INDEX idx_table2_bid_priority ON table2(BID, priority DESC) INCLUDE (id, info);

• 对于MySQL等不支持INCLUDE的数据库，直接把需要的字段加入索引：

CREATE INDEX idx_table2_bid_priority ON table2(BID, priority DESC, id, info);

这些索引能让数据库直接从索引中获取所需数据，不用扫描全表，大幅降低查询耗时。

内容的提问来源于stack exchange，提问作者Programmer