Left Join无索引表仅取单匹配结果的SQL查询性能优化

表结构 #

表A（按date分区，id为索引） #

date       | id | content
--------------------------
2023-12-02 | 1  | a 
....

表B（按date分区，无索引） #

date       | id | size
--------------------------
2023-12-02 | 1  | 10  
2023-12-02 | 1  | 11 
....

需求 #

将表A基于id与表B做Left Join，仅返回任意一条匹配的size值，以下两种结果均符合要求：
结果1：

date       | id | content | size
------------------------------------
2023-12-02 | 1  | a       | 10  

结果2：

date       | id | content | size
------------------------------------
2023-12-02 | 1  | a       | 11

原查询性能问题 #

使用常规Left Join查询时执行速度较慢：

SELECT A.*, B.size 
FROM A
LEFT JOIN B ON A.id = b.id
WHERE A.date = '2023-12-02'
  AND B.date = '2023-12-02'

方案1：预聚合表B，减少关联数据量 #

因为只需要每个id对应的任意一条size，先对表B按id聚合，提前过滤冗余数据，再和表A关联。利用表B的date分区先限定范围，聚合效率更高：

SELECT A.*, B_sub.size
FROM A
LEFT JOIN (
    SELECT id, ANY_VALUE(size) AS size
    FROM B
    WHERE date = '2023-12-02'
    GROUP BY id
) B_sub ON A.id = B_sub.id
WHERE A.date = '2023-12-02'

不同数据库取任意值的函数不同：MySQL用ANY_VALUE，PostgreSQL可以用MIN(size)/MAX(size)（只要取任意一条即可），Hive可用first_value(size)。

方案2：给表B添加复合索引 #

如果表B的这类查询频率高，建议创建(date, id)的复合索引，让数据库能快速定位到分区内对应id的数据，避免全分区扫描：

-- MySQL示例
CREATE INDEX idx_b_date_id ON B(date, id);

索引创建后，原查询或方案1的查询都会大幅提速。

方案3：用子查询取单条数据 #

直接对表A的每一行，查询表B中对应id的任意一条size，避免全量关联：

SELECT 
    A.*,
    (SELECT size FROM B WHERE date = '2023-12-02' AND id = A.id LIMIT 1) AS size
FROM A
WHERE A.date = '2023-12-02'

如果后续给表B加了id索引，子查询的速度会进一步提升。

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