从ROWNUM到LIMIT

1.前言:一次看似"数据丢失"的 SQL 排查

同样一条 SQL,换个数据库跑,行数不一样了.这不是玄学,是执行优先级的锅.
上周,一位从 Oracle 迁移到金仓数据库 KES 的开发者在群里抛出一个问题:
“我的查询明明写了 ROWNUM <= 10,为什么返回的结果有时候是 7 行、8 行，就是不到10行?而且同样的SQL 在同事的 PostgreSQL 上跑,偏偏返回的就是10行.”

他跑的 SQL 是这样的：

SELECT DISTINCT user_id FROM access_log WHERE rownum <= 10;

access_log 表  存储 的是用户访问日志,同一个 user_id 可能出现在多行中.他的本意是"取前 10 个不重复的用户".但实际结果却让人困惑.

如果你也遇到过类似的问题,或者你正在从 Oracle 迁移到 KES / PostgreSQL,这篇文章将帮你彻底理清背后的执行优先级差异,避免在后续开发中踩同样的坑.

2.问题复现:同一条SQL,为什么返回行数不一样?

让我们用一个简单的  数据集 来复现这个现象.假设 access_log 表的前 15 行数据如下：

执行 SELECT DISTINCT user_id FROM access_log WHERE rownum <= 10; 时：

2.1 KES/Oracle中:先限制行数,再执行去重

先取 10 行：扫描前 10 行物理记录(rowid 1-10)

后去重：对这 10 行做 DISTINCT,得到 A、B、C、D、E、F、G

结果：7 行(而非 10 行)

2.2 PostgreSQL中:先完成去重,再截取结果

PG 使用 LIMIT 而非 ROWNUM,等价 SQL 为 SELECT DISTINCT user_id FROM access_log LIMIT 10;：

先去重：对全表做 DISTINCT,得到所有不重复的 user_id

后取 10 行：对去重后的结果取前 10 个

结果：10 行(恰好 10 个不重复 user_id)

3.原因解析:ROWNUM 与 LIMIT 的执行顺序差异

3.1KES/Oracle:ROWNUM先于DISTINCT 生效

在 KES 和 Oracle 中,ROWNUM 是一个动态生成的伪列.它的赋值发生在数据读取阶段,早于 DISTINCT、ORDER BY 等操作.

执行顺序可以概括为：

全表扫描 → 逐行赋予 ROWNUM → 过滤 ROWNUM 条件 → DISTINCT 去重 → 返回结果

关键问题在于：ROWNUM <= 10 在去重之前就  截断 了数据.如果前 10 行物理记录中存在大量重复值,去重后的结果自然会少于10行.

用流程图表述：

原始 10 行:  A  A  B  C  A  D  E  B  F  G                  ↓ DISTINCT 去重 结果 7 行:   A  B  C  D  E  F  G

3.2 PostgreSQL:LIMIT作用于最终结果集

PostgreSQL 的 LIMIT 作用于最终结果集.执行顺序为：

全表扫描 → DISTINCT 去重 → LIMIT 截取前 N 行 → 返回结果

这种语义更符合大多数开发者的直觉——“我要 10 个不重复的值”.

3.3 优化器影响:ROWNUM为什么会限制子查询改写？

更深入地说,ROWNUM 的存在还会影响优化器的决策.在KES/Oracle中,当子查询内部引用了 ROWNUM 时,外部查询的过滤条件无法下推到子查询中(这一优化技术称为"子查询提升"或"Pull-up").

这意味着：

SELECT * FROM (    SELECT DISTINCT user_id FROM access_log WHERE rownum <= 10 ) t WHERE t.user_id = 'A';

在这条 SQL 中,WHERE t.user_id = 'A' 这个外部过滤条件无法被下推到子查询内部.优化器被迫先对子查询做全表扫描(取前10行),然后在外层做过滤.如果数据量很大,这可能导致不必要的性能损耗.

相比之下,如果将 ROWNUM 替换为 LIMIT,PostgreSQL 的优化器通常可以将外部条件下推,从而减少扫描范围.

4.改写建议:如何避免 DISTINCT + ROWNUM 的语义偏差?

4.1方案一:用嵌套子查询固定执行顺序(推荐)

如果你确实需要"先取 N 行,再去重"的 Oracle/KES 语义,但希望在 PG 上得到一致结果,使用嵌套子查询：

-- KES / Oracle / PG 均可执行，行为一致 SELECT DISTINCT user_id FROM (    SELECT user_id FROM access_log WHERE rownum <= 10 ) t;

或者在 PG 中：

SELECT DISTINCT user_id FROM (    SELECT user_id FROM access_log LIMIT 10 ) t;

4.2方案二:先明确业务目标:取前N行,还是取N个唯一值?

问自己一个问题：你的业务到底想要什么？

大多数情况下,开发者的真实意图是后者——“我要 N 个不重复的值”.在这种情况下,KES/Oracle 中的 DISTINCT + ROWNUM 组合其实是写错了.

4.3方案三:使用窗口函数实现更精确的取数  控制              

如果你需要对排序、去重、截断的顺序有完全精确的控制,使用窗口函数是最可靠的方式:

-- 先按 user_id 分组，取每个 user_id 的最小 rowid，然后取前 10 个 SELECT user_id FROM (    SELECT user_id,           ROW_NUMBER() OVER (ORDER BY MIN(rowid)) AS rn    FROM access_log    GROUP BY user_id ) t WHERE rn <= 10;

这种写法在所有数据库中行为一致,且语义最为明确.

5.总结:限制行数之前,先弄清它限制的是哪一步

DISTINCT + ROWNUM 的执行优先级陷阱,本质上是不同数据库对行号伪列赋值时机的设计差异.关键要点回顾：

KES / Oracle：ROWNUM 赋值在 DISTINCT 之前——先截取,后去重,结果可能少于N行.

PostgreSQL：LIMIT 作用于最终结果——先去重,后截取,结果恰好N行.

ROWNUM 阻断子查询提升：引用 ROWNUM 的子查询,外部过滤条件无法下推,可能导致全表扫描.

最佳实践：

明确业务意图,选择正确的写法

跨库兼容场景下,使用嵌套子查询或窗口函数

避免将 DISTINCT + ROWNUM 作为"取 N 个不重复值"的手段

记住一条铁律：永远不要用 ROWNUM 去做你真正想做之外的事情.它的行为高度依赖于它在 SQL 中的位置和数据库引擎的实现细节.当你对执行顺序有一丝不确定时,窗口函数永远是最安全的选择.