补全缺失行：GROUP BY分组RIGHT JOIN及大数据量查询性能优化问询

嘿，针对你遇到的大数据量下补全缺失测试项的性能问题，我有几个实用的优化思路和方案，帮你把耗时从几分钟压缩到秒级！

首先复盘下你的场景：表1是固定的4项应执行测试，表2是每个产品的测试执行记录，你需要给每个产品补全所有4项测试，标记出未执行的。之前用CROSS JOIN+LEFT JOIN的方案在数据量上来后（8k-15k行）变慢，核心原因是全量CROSS JOIN生成了太多冗余的中间数据——比如一个产品如果已经执行了3项测试，原来的方案会把这3条记录和4项测试全交叉，生成12行再去重匹配，这完全没必要。

优化核心思路：先取唯一产品SN，再关联测试项 #

我们可以先从表2中提取所有唯一的产品SN（每个SN只保留一次），再和表1的4项测试做CROSS JOIN，这样中间数据量直接变成「唯一SN数 × 4」，比原来的「表2总行数 × 4」小得多。比如表2有15k行但只有5k个唯一SN，中间数据就从60k降到20k，性能提升非常明显。

具体SQL实现（通用SQL语法） #

假设你的表结构是这样的：

• 表1：test_definitions（test_id 测试ID，test_name 测试名称）
• 表2：product_test_results（sn 产品序列号，test_date 测试日期，test_id 已执行测试ID）

优化后的SQL代码如下：

-- 第一步：获取所有唯一的产品SN
WITH unique_products AS (
    SELECT DISTINCT sn
    FROM product_test_results
),
-- 第二步：生成每个产品对应的所有应执行测试项
all_required_tests AS (
    SELECT up.sn, td.test_id, td.test_name
    FROM unique_products up
    CROSS JOIN test_definitions td
)
-- 第三步：关联已执行记录，标记执行状态
SELECT 
    art.sn,
    art.test_id,
    art.test_name,
    ptr.test_date AS executed_date,
    -- 标记是否执行
    CASE 
        WHEN ptr.test_id IS NOT NULL THEN '已执行' 
        ELSE '未执行' 
    END AS execution_status
FROM all_required_tests art
LEFT JOIN product_test_results ptr 
    ON art.sn = ptr.sn 
    AND art.test_id = ptr.test_id
ORDER BY art.sn, art.test_id;

额外性能优化：加索引 #

为了让LEFT JOIN的匹配更快，建议给product_test_results表创建复合索引：

CREATE INDEX idx_ptr_sn_testid ON product_test_results (sn, test_id);

同时确保test_definitions的test_id是主键或者有索引，这样CROSS JOIN的时候也能快速获取测试项。

特殊场景补充 #

如果存在从未执行过任何测试的产品（也就是这些SN不在表2中），那你需要把unique_products换成你的产品主表（比如products表），而不是从表2取DISTINCT，这样就能覆盖所有产品的测试补全需求。

这样调整后，处理8k-15k行数据的耗时应该能降到几秒以内，完全解决之前的性能问题！

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