如何高效检查产品外键ID是否被引用？百万级数据查询优化问询

为什么原查询效率低 #

你原来用的NOT IN在数据量小时没问题，但百万级数据下会有两个核心问题：

1. 子查询会生成全量的product_id临时集合，需要逐行与product表的id比对，开销极大；
2. 如果子查询的product_id存在NULL值，会导致整个NOT IN条件直接返回空结果，逻辑出错。

优化方案 #

1. 用NOT EXISTS替代NOT IN

NOT EXISTS是半连接逻辑，数据库找到匹配的引用记录后会立即停止扫描，比NOT IN的全量比对高效得多，且不受NULL值影响：

SELECT p.id 
FROM product p
WHERE 
  -- 加入你原来的老旧产品筛选规则，比如 create_time < '2020-01-01'
  NOT EXISTS (SELECT 1 FROM sale_line sl WHERE sl.product_id = p.id)
  AND NOT EXISTS (SELECT 1 FROM purchase_line pl WHERE pl.product_id = p.id)

2. 使用LEFT JOIN + IS NULL

左连接后筛选未匹配的行，数据库优化器通常能对这种逻辑做很好的优化：

SELECT p.id
FROM product p
LEFT JOIN sale_line sl ON sl.product_id = p.id
LEFT JOIN purchase_line pl ON pl.product_id = p.id
WHERE 
  -- 加入你原来的老旧产品筛选规则
  sl.product_id IS NULL
  AND pl.product_id IS NULL

3. 必须添加索引（核心优化）

没有索引的话，任何查询都会全表扫描，百万级数据下必然卡顿：

• 给sale_line.product_id和purchase_line.product_id创建普通索引：

  CREATE INDEX idx_sale_line_product_id ON sale_line(product_id);
  CREATE INDEX idx_purchase_line_product_id ON purchase_line(product_id);
  

• 如果product表的筛选规则用到了其他字段（比如时间、状态），也要给这些字段加索引，减少需要扫描的product行数。

4. 分批删除避免性能问题

因为最终要执行删除操作，不要一次性处理百万行，分批操作可以避免锁表、事务过大等问题：

-- 每次删除1000行，可根据数据库性能调整数量
DELETE FROM product
WHERE 
  id IN (
    SELECT p.id 
    FROM product p
    WHERE 
      -- 老旧产品筛选规则
      NOT EXISTS (SELECT 1 FROM sale_line sl WHERE sl.product_id = p.id)
      AND NOT EXISTS (SELECT 1 FROM purchase_line pl WHERE pl.product_id = p.id)
    LIMIT 1000
  );
-- 重复执行直到没有可删除的行

额外注意事项 #

• 先执行SELECT语句验证结果正确后，再执行DELETE；
• 用数据库的执行计划工具（比如MySQL的EXPLAIN、PostgreSQL的EXPLAIN ANALYZE）检查索引是否被正确命中；
• 尽量在业务低峰期执行操作，避免影响线上服务。

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