查询大表无销售记录产品SQL执行超时优化方案咨询

原始语句性能问题原因 #

原始LEFT JOIN写法会先完成两张表的全量关联生成临时结果集，再过滤关联失败的记录，当表数据量较大且没有对应索引时，临时表占用资源极高，极易出现超时。

前置优化（必做） #

• 给order_items表的product_id字段创建普通索引，若该字段已设为主键/外键可跳过。索引可以将product_id匹配效率提升数十倍以上。

高效实现方案 #

方案1：NOT EXISTS实现（最推荐，性能最优） #

数据库执行遇到NOT EXISTS子句时，匹配到第一条对应product_id的订单记录就会终止当前产品的扫描，不需要完成全量关联，性能远高于原始LEFT JOIN写法。

SELECT p.product_id
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

方案2：NOT IN实现 #

适用于order_items表中product_id重复率较高的场景，先提取所有产生过销售的唯一产品ID列表，再做匹配：

SELECT product_id
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE product_id IS NOT NULL
);

注意：必须在子查询中添加product_id IS NOT NULL过滤条件，否则如果order_items存在product_id为NULL的记录，会导致NOT IN返回空结果。

额外优化提示 #

如果查询结果量级较大依然有超时问题，可根据使用的数据库类型添加对应的查询优化提示：

• MySQL环境可添加SQL_BIG_RESULT提示，提前分配足够的缓存存储结果集：

SELECT SQL_BIG_RESULT p.product_id
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

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