1. 首页
  2. 问题
  3. SQL：当涉及到NOT IN和NOT EQUAL TO时，哪一个效率更高？为什么？
一尘不染

SQL：当涉及到NOT IN和NOT EQUAL TO时，哪一个效率更高？为什么？

sql

假设我有一组项目：

  • 项目1
  • 第2项
  • 项目3
  • 第4项
  • 项目5

查询可以通过两种方式构造。首先：

SELECT * 
FROM TABLE 
WHERE ITEM NOT IN ('item1', 'item2', 'item3', 'item4','item5')

或者，它可以写为：

SELECT * 
FROM TABLE 
WHERE ITEM != 'item1' 
  AND ITEM != 'item2' 
  AND ITEM != 'item3' 
  AND ITEM != 'item4' 
  AND ITEM != 'item5'
  1. 哪个更有效？为什么？
  2. 在哪一点上变得比另一点更有效率？换句话说，如果有500个物品怎么办？

我的问题专门与PostgreSQL有关。


阅读 190

收藏
2021-03-17

共1个答案

一尘不染

在PostgreSQL中，在合理的列表长度上通常存在很小的差异，尽管IN从概念上讲更加清晰。非常长的AND ... <> ...列表和非常长的NOT IN列表的效果都非常AND差，比差很多NOT IN。

在这两种情况下，如果它们足够长，甚至可以问一个问题，您应该对值列表进行反联接或子查询排除测试。

WITH excluded(item) AS (
    VALUES('item1'), ('item2'), ('item3'), ('item4'),('item5')
)
SELECT * 
FROM thetable t
WHERE NOT EXISTS(SELECT 1 FROM excluded e WHERE t.item = e.item);

或者：

WITH excluded(item) AS (
    VALUES('item1'), ('item2'), ('item3'), ('item4'),('item5')
)
SELECT * 
FROM thetable t
LEFT OUTER JOIN excluded e ON (t.item = e.item)
WHERE e.item IS NULL;

（在现代Pg版本中，无论如何两者都会产生相同的查询计划）。

如果值列表足够长（数以万计的项目），则查询解析可能会开始耗费大量资金。此时，您应该考虑创建一个TEMPORARY表，COPY对要排除在其中的数据进行处理，可能会在其上创建一个索引，然后在临时表上使用上述方法之一而不是CTE。

演示：

CREATE UNLOGGED TABLE exclude_test(id integer primary key);
INSERT INTO exclude_test(id) SELECT generate_series(1,50000);
CREATE TABLE exclude AS SELECT x AS item FROM generate_series(1,40000,4) x;

exclude要忽略的值列表在哪里。

然后，我将对相同数据的以下方法与所有结果（以毫秒为单位）进行比较：

  • NOT IN清单： 3424.596
  • AND ...清单： 80173.823
  • VALUES根据JOIN排除率： 20.727
  • VALUES基于子查询的排除： 20.495
  • 基于表格JOIN，未在索引清单中列出： 25.183
  • 基于子查询表，在ex-list上没有索引： 23.985

…使基于CTE的方法比AND列表快三千倍，比列表快130倍NOT IN。

此处的代码为：https :
//gist.github.com/ringerc/5755247（关注此链接的人，请遮住眼睛）。

对于此数据集大小，在排除列表上添加索引没有区别。

笔记：

  • IN 产生的清单 SELECT 'IN (' || string_agg(item::text, ',' ORDER BY item) || ')' from exclude;
  • AND与生成的列表SELECT string_agg(item::text, ' AND item <> ') from exclude;）
  • 子查询和基于联接的表排除在重复运行中几乎相同。
  • 对该计划的检查表明Pg转换NOT IN为<> ALL

所以…你可以看到，有一个真正 巨大的
在两者之间的差距IN，并ANDVS做一个适当的加入列表。令我惊讶的是，使用VALUES列表使用CTE的速度如此之快…解析VALUES列表几乎无需花费时间，在大多数测试中，其执行
速度 与表方法相同或 稍快 。

如果PostgreSQL能自动识别出IN类似AND条件的荒谬的长子句或链，并切换到更智能的方法（如执行散列连接或将其隐式地转换为CTE节点），那将是很好的选择。现在它不知道该怎么做。

也可以看看：

2021-03-17