MS Access 2010两类性能疑问：NOT EXISTS慢、删少量行效率极低原因

(1) 为什么NOT EXISTS的执行效率远低于EXISTS？ #

这主要是Access查询优化器对两种逻辑的处理方式差异，再加上你的查询结构放大了这个差距：

• 先看EXISTS的逻辑：它属于半匹配查询——只要子查询中找到任意一条匹配的记录，就会立刻停止对当前行的判断，直接返回True。对应你的场景，就是当Access判断出当前行的ID等于分组后的最大ID时，马上认定这行是需要保留的，不用再继续查询。如果你的分组字段（ID_TIME、CURRENCY等）有索引，Access还能快速定位到每个分组的最大ID，进一步提升效率。
• 而NOT EXISTS是反匹配查询，它必须确认子查询中完全没有匹配的记录才能返回True。这意味着对于每一条待删除的重复行，Access都要遍历整个分组结果，确认当前行的ID不是该组的最大ID——这个过程没办法提前终止，必须完成全量的匹配检查。更关键的是，你的子查询是嵌套结构（先分组取MAXID，再匹配ID），Access的优化器可能无法为NOT EXISTS生成最优的执行计划，导致每一行都要重复执行一次昂贵的分组查询，最终累积出近8小时的耗时。
• 还有个数据量的细节：EXISTS只需要处理58000条要保留的记录，而NOT EXISTS要处理10000条重复记录，但这10000条的单条判断成本远高于EXISTS的记录，因为反匹配的校验逻辑本身就更重。

(2) 为什么删除10000行的耗时几乎是“复制116000行+删除126000行”的1000倍？ #

这个差距的核心是逐行操作和批量操作的底层效率差异，Access对这两类操作的处理机制完全不同：

• 直接删除重复行的操作：DELETE * FROM [DATA] WHERE NOT EXISTS(...)是逐行删除。每删除一行，Access都要做三件事：
  1. 执行一次已经很慢的NOT EXISTS判断；
  2. 更新表上的所有索引（如果ID、分组字段等有索引的话，每删一行都要修改索引条目，这个开销很大）；
  3. 把这次删除操作完整记录到事务日志中，确保可以回滚——逐行删除的日志量是指数级的。
     这三个步骤叠加，导致每一行的删除成本都极高，10000行的总耗时自然被拉到了8小时。
• 临时表方案的操作：
  1. INSERT INTO DATA_TEMPORARY_CLEANUP_TABLE SELECT ...是批量插入，Access会一次性处理58000条符合条件的记录，日志是批量写入的，索引也是批量构建的，效率远超逐行操作；
  2. DELETE * FROM [DATA]（不带WHERE条件）在Access里属于快速清空，它不会逐行记录日志，而是直接标记整个表的数据为可复用空间，这个操作几乎是瞬间完成的；
  3. 把临时表的数据插回原表，同样是批量插入，索引也是批量维护；
  4. 最后清空临时表也是快速操作。
     整个过程都是批量处理，日志量小，索引维护成本低，所以总耗时最多只有30秒。
• 另外还有锁的因素：逐行删除时，Access可能会对每一行加锁，即使是单用户操作，细粒度的锁也会带来额外的开销；而批量操作通常会使用表级锁，减少了锁的竞争和维护成本。

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