Oracle数据库中Delete查询快但Select查询极慢，原因何在？

问题背景 #

有一张包含160,000行数据的表，执行查询语句：

SELECT ID FROM mytable WHERE id NOT IN ( SELECT max(id) FROM mytable GROUP BY user_id);

耗时超1小时仍未完成，但执行删除语句：

delete FROM mytable WHERE id NOT IN (SELECT max(id) FROM mytable GROUP BY user_id);

仅需0.5秒。表结构及数据示例如下：

---------------------------------------------------------------------------------------------------
| id  | MyTimestamp  |    Name        | user_id   ...    
----------------------------------------------------------------------------------------------------
|   0 |  1657640396  |    John        | 123581         ...    
|   1 |  1657638832  |    Tom         | 168525         ...    
|   2 |  1657640265  |    Tom         | 168525         ...    
|   3 |  1657640292  |    John        | 123581         ...    
|   4 |  1657640005  |    Jack        | 896545         ...    
-----------------------------------------------------------------------------------------

核心原因分析 #

1. 执行计划与优化器策略差异 #

数据库优化器对SELECT和DELETE的优化逻辑存在本质区别：

• DELETE以完成数据删除为目标，优化器会优先选择高效执行路径：先通过子查询快速获取每个user_id对应的max(id)集合，再直接利用主键id的索引（假设id是主键）定位要删除的行，避免低效的逐行比对。
• SELECT语句若未触发最优执行计划，可能会采用嵌套循环方式，逐行检查当前行的id是否不在子查询结果集中。当子查询结果集较大时，这种重复比对会产生极高的CPU和IO消耗，直接导致耗时剧增。

2. 数据处理与资源消耗差异 #

• DELETE操作无需向客户端返回大量结果，仅需完成磁盘IO写入和事务日志记录，资源集中在数据修改环节，操作完成后直接结束。
• SELECT操作需要将所有符合条件的id查询出来并传输到客户端。如果符合条件的行数较多（比如占总数据量的大部分），不仅查询阶段要处理海量数据，结果传输阶段还会消耗额外的内存、CPU和网络资源，大幅拉长整体耗时。

3. 索引利用效率差异 #

• 若id是主键（聚簇索引），DELETE可直接通过主键索引快速定位目标行。而SELECT语句即便只查询id，如果优化器判断失误（比如未缓存子查询结果、重复执行子查询），会导致索引无法高效利用，进而拖慢整体速度。另外，若user_id无索引，子查询的GROUP BY会变慢，但这里DELETE执行快，说明子查询本身耗时短，问题出在主查询的匹配阶段。

优化SELECT语句的建议 #

• 改用NOT EXISTS替代NOT IN，避免NOT IN可能带来的空值问题和低效匹配：

  SELECT t1.id 
  FROM mytable t1 
  WHERE NOT EXISTS (
      SELECT 1 
      FROM mytable t2 
      WHERE t2.user_id = t1.user_id 
        AND t2.id = (SELECT max(id) FROM mytable t3 WHERE t3.user_id = t1.user_id)
  );
  

• 给user_id添加索引，加速子查询的GROUP BY操作：

  CREATE INDEX idx_mytable_userid ON mytable(user_id);
  

• 将子查询结果存入临时表，再做关联查询，减少重复计算：

  CREATE TEMPORARY TABLE temp_max_ids AS 
      SELECT max(id) as max_id FROM mytable GROUP BY user_id;
  
  SELECT t.id 
  FROM mytable t 
  LEFT JOIN temp_max_ids tm ON t.id = tm.max_id 
  WHERE tm.max_id IS NULL;
  

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