性能优化：如何不使用NOT IN改写指定SQL查询

首先明确你的核心需求：筛选出所有关联的client_id都属于指定的:CLIENT_IDS集合，同时当前记录的client_id也在该集合中的cert_serial_number。原查询使用NOT IN确实存在性能瓶颈和潜在的NULL值问题，不过你提到的最终语句存在逻辑漏洞，这里给出正确的改写方案：

正确的NOT EXISTS写法（推荐） #

这个写法能准确匹配需求，且性能优于NOT IN：

SELECT d.cert_serial_number AS str 
FROM mdm_device d 
WHERE d.client_id IN (:CLIENT_IDS) 
  AND NOT EXISTS (
    SELECT 1 
    FROM mdm_device e 
    WHERE d.cert_serial_number = e.cert_serial_number 
      AND e.client_id NOT IN (:CLIENT_IDS)
  )

逻辑说明： #

1. 外层查询先过滤出当前client_id在目标集合内的记录
2. NOT EXISTS子查询负责验证：当前cert_serial_number没有关联任何不在目标集合里的client_id
3. 为什么你提供的语句有问题？
   你原来的改写用了d.client_id != e.client_id，这会错误排除那些同一cert_serial_number关联多个目标client_id的情况。比如当cert_serial_number=102的所有client_id都在目标集合里时，子查询会找到其他同属集合的client_id记录，导致NOT EXISTS条件不成立，最终无法返回正确结果。

另一种方案：GROUP BY + HAVING聚合验证 #

如果需要从统计角度实现需求，可以用聚合查询：

SELECT cert_serial_number AS str
FROM mdm_device
GROUP BY cert_serial_number
HAVING COUNT(CASE WHEN client_id NOT IN (:CLIENT_IDS) THEN 1 END) = 0
   AND COUNT(CASE WHEN client_id IN (:CLIENT_IDS) THEN 1 END) > 0

逻辑说明： #

• 按cert_serial_number分组后，通过两个条件判断：
  • 不存在任何关联的client_id不在目标集合中（第一个COUNT为0）
  • 至少存在一个关联的client_id在目标集合中（第二个COUNT>0，避免返回完全和目标集合无关的cert_serial_number）

结合示例数据验证 #

用你提供的数据集测试：

• 当输入client_id为1073741835、1073741836时：
  cert_serial_number=102还关联了1073741837-1073741839这些不在集合里的client_id，NOT EXISTS子查询会找到这些记录，因此最终结果为空，符合预期。
• 当输入client_id包含1073741835至1073741839时：
  cert_serial_number=102的所有关联client_id都在集合里，NOT EXISTS子查询无匹配记录，因此会返回102，符合预期。

为什么不推荐NOT IN？ #

• 性能劣势：NOT IN子查询通常会触发全表扫描，尤其是当子查询结果集较大时，性能下降明显；而NOT EXISTS可以利用(cert_serial_number, client_id)联合索引快速定位匹配记录。
• NULL值陷阱：如果子查询返回的cert_serial_number存在NULL值，NOT IN会直接导致整个查询返回空结果，这是容易忽略的坑。

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