SQL中为什么参数多了notin比in慢多了，怎么优化

开发工作中，我发现一个现象，比喻下面的两个语句：

select * from shangpin where spdm in ('1','2','3',...); select * from shangpin where spdm not in ('1','2','3',...);

当参数比较少的时候还看不出来什么，但是遇到参数上了几百几千，那么 in 受的影响不大，not in 查询的结果就超级慢了。

那么什么原因导致NOT IN 可能比 IN 慢很多呢？

1. 逻辑复杂度不同

IN 子句：用于检查一个值是否存在于一组特定的值中。对于数据库引擎来说，这是一个相对直接的操作，因为它只需要找到匹配项即可。NOT IN 子句：则需要确保目标值不在给定的集合中。这意味着对于每一个值，数据库必须确认它不等于列表中的任何一项，这通常涉及更多的比较操作。

2. NULL值的影响

如果NOT IN子句中的任意一个值是NULL，整个查询结果将返回空（因为任何与NULL的比较都是未知）。为了处理这种情况，数据库系统可能需要额外的逻辑来确保正确性，从而增加了计算成本。相反，IN子句可以更高效地处理NULL值，因为它只需查找是否存在匹配项。

3. 索引使用效率

在某些情况下，数据库优化器可能无法有效地利用索引来加速NOT IN查询，尤其是当子查询或列表非常大时。相比之下，IN子句通常更容易被优化器识别为可以使用索引的情况，从而提高执行速度。

4. 查询转换

数据库系统有时会将NOT IN重写为其他形式的查询（例如使用LEFT JOIN加上WHERE条件过滤掉匹配行），这种转换可能会导致更低的执行效率，尤其是在大数据集上。而IN子句通常不需要这样的重写，可以直接应用于索引或其他优化策略。

5. 数据量的影响

当IN子句包含大量值时，虽然性能也会受到影响，但大多数现代数据库管理系统都针对这种情况进行了优化。然而，随着NOT IN列表的增长，由于其内在的逻辑复杂性和潜在的全表扫描需求，性能下降的速度往往更快。

那么怎么去优化？

我的方法是这样：

StringBuffer notexistsSqlBuffer=new StringBuffer(); if (spdmListLw.size() > 0) { notexistsSqlBuffer.append("SELECT 1 \n"); notexistsSqlBuffer.append("FROM (\n"); notexistsSqlBuffer.append("SELECT spdm FROM (\n"); notexistsSqlBuffer.append("VALUES\n"); for (int i = 0; i < spdmListLw.size(); i++) { if(i!=0) notexistsSqlBuffer.append(","); notexistsSqlBuffer.append("('"+spdmListLw.get(i)+"')"); } notexistsSqlBuffer.append(") AS spdm_list(spdm)\n"); notexistsSqlBuffer.append(") AS excluded_spdm\n"); notexistsSqlBuffer.append("WHERE excluded_spdm.spdm =shangpin.spdm"); } wrapper.notExists( spdmListLw.size() > 0,notexistsSqlBuffer.toString())

拼成的结果大致是这样：

select * from shangpin where not exists ( select 1 from ( SELECT spdm FROM ( VALUES ('1'),('2'),('1'),...) AS spdm_list(spdm) ) AS excluded_spdm WHERE excluded_spdm.spdm =shangpin.spdm )

MySQL 8.0及以上版本才支持支持VALUES行构造器），可以使用uion all 替代

StringBuffer notexistsSqlBuffer=new StringBuffer(); if (spdmListLw.size() > 0) { notexistsSqlBuffer.append("SELECT 1 FROM (\n"); for (int i = 0; i < spdmListLw.size(); i++) { if(i!=0) notexistsSqlBuffer.append(" UNION ALL "); notexistsSqlBuffer.append(" SELECT '"+spdmListLw.get(i)+"' "); if(i==0) notexistsSqlBuffer.append(" AS SPDM "); } notexistsSqlBuffer.append(" ) as excluded_spdm \n"); notexistsSqlBuffer.append("WHERE excluded_spdm.spdm =com_base_shangpin.spdm"); }