开发工作中，我发现一个现象，比喻下面的两个语句：
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");
}
