同樣一條 SQL，換個數(shù)據(jù)庫跑，行數(shù)不一樣了。這不是玄學(xué)，是執(zhí)行優(yōu)先級的鍋。

引言：一次詭異的"數(shù)據(jù)丟失"排查

上周，一位從 Oracle 遷移到金倉數(shù)據(jù)庫 KES 的開發(fā)者在群里拋出一個問題：

“我的查詢明明寫了 ROWNUM <= 10，為什么返回的結(jié)果有時候是 7 行、8 行，就是不到 10 行？而且同樣的 SQL 在同事的 PostgreSQL 上跑，偏偏返回的就是 10 行。”

他跑的 SQL 是這樣的：

SELECT DISTINCT user_id FROM access_log WHERE rownum <= 10;

access_log 表存儲的是用戶訪問日志，同一個 user_id 可能出現(xiàn)在多行中。他的本意是"取前 10 個不重復(fù)的用戶"。但實際結(jié)果卻讓人困惑。

如果你也遇到過類似的問題，或者你正在從 Oracle 遷移到 KES / PostgreSQL，這篇文章將幫你徹底理清背后的執(zhí)行優(yōu)先級差異，避免在后續(xù)開發(fā)中踩同樣的坑。

一、現(xiàn)象復(fù)現(xiàn)：同樣的 SQL，不同的結(jié)果

讓我們用一個簡單的數(shù)據(jù)集來復(fù)現(xiàn)這個現(xiàn)象。假設(shè) access_log 表的前 15 行數(shù)據(jù)如下：

執(zhí)行 SELECT DISTINCT user_id FROM access_log WHERE rownum <= 10; 時：

在 KES / Oracle 中的執(zhí)行過程

1. 先取 10 行：掃描前 10 行物理記錄（rowid 1-10）
2. 后去重：對這 10 行做 DISTINCT，得到 A、B、C、D、E、F、G

結(jié)果：7 行（而非 10 行）

在 PostgreSQL 中的執(zhí)行過程

PG 使用 LIMIT 而非 ROWNUM，等價 SQL 為 SELECT DISTINCT user_id FROM access_log LIMIT 10;：

1. 先去重：對全表做 DISTINCT，得到所有不重復(fù)的 user_id
2. 后取 10 行：對去重后的結(jié)果取前 10 個

結(jié)果：10 行（恰好 10 個不重復(fù) user_id）

二、原理剖析：執(zhí)行優(yōu)先級的致命差異

2.1 KES / Oracle：ROWNUM 的"先截后去"

在 KES 和 Oracle 中，ROWNUM 是一個動態(tài)生成的偽列。它的賦值發(fā)生在數(shù)據(jù)讀取階段，早于 DISTINCT、ORDER BY 等操作。

執(zhí)行順序可以概括為：全表掃描 → 逐行賦予 ROWNUM → 過濾 ROWNUM 條件 → DISTINCT 去重 → 返回結(jié)果

關(guān)鍵問題在于：ROWNUM <= 10 在去重之前就截斷了數(shù)據(jù)。如果前 10 行物理記錄中存在大量重復(fù)值，去重后的結(jié)果自然會少于 10 行。

用流程圖表述：

原始 10 行:  A  A  B  C  A  D  E  B  F  G
                  ↓ DISTINCT 去重
結(jié)果 7 行:   A  B  C  D  E  F  G

2.2 PostgreSQL：LIMIT 的"先去后截"

PostgreSQL 的 LIMIT 作用于最終結(jié)果集。執(zhí)行順序為：

全表掃描 → DISTINCT 去重 → LIMIT 截取前 N 行 → 返回結(jié)果

這種語義更符合大多數(shù)開發(fā)者的直覺——“我要 10 個不重復(fù)的值”。

2.3 執(zhí)行計劃差異：ROWNUM 阻斷子查詢提升

更深入地說，ROWNUM 的存在還會影響優(yōu)化器的決策。在 KES / Oracle 中，當(dāng)子查詢內(nèi)部引用了 ROWNUM 時，外部查詢的過濾條件無法下推到子查詢中（這一優(yōu)化技術(shù)稱為"子查詢提升"或"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' 這個外部過濾條件無法被下推到子查詢內(nèi)部。優(yōu)化器被迫先對子查詢做全表掃描（取前 10 行），然后在外層做過濾。如果數(shù)據(jù)量很大，這可能導(dǎo)致不必要的性能損耗。

相比之下，如果將 ROWNUM 替換為 LIMIT，PostgreSQL 的優(yōu)化器通?？梢詫⑼獠織l件下推，從而減少掃描范圍。

三、避坑指南：如何寫出跨庫一致的 SQL

方案一：嵌套子查詢（推薦）

如果你確實需要"先取 N 行，再去重"的 Oracle / KES 語義，但希望在 PG 上得到一致結(jié)果，使用嵌套子查詢：

-- KES / Oracle / PG 均可執(zhí)行，行為一致
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;

方案二：明確你的業(yè)務(wù)意圖

問自己一個問題：你的業(yè)務(wù)到底想要什么？

大多數(shù)情況下，開發(fā)者的真實意圖是后者——“我要 N 個不重復(fù)的值”。在這種情況下，KES / Oracle 中的 DISTINCT + ROWNUM 組合其實是寫錯了。

方案三：使用窗口函數(shù)（最精確的控制）

如果你需要對排序、去重、截斷的順序有完全精確的控制，使用窗口函數(shù)是最可靠的方式：

-- 先按 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;

這種寫法在所有數(shù)據(jù)庫中行為一致，且語義最為明確。

四、總結(jié)

DISTINCT + ROWNUM 的執(zhí)行優(yōu)先級陷阱，本質(zhì)上是不同數(shù)據(jù)庫對行號偽列賦值時機(jī)的設(shè)計差異。關(guān)鍵要點回顧：

1. KES / Oracle：ROWNUM 賦值在 DISTINCT 之前——先截取，后去重，結(jié)果可能少于 N 行。
2. PostgreSQL：LIMIT 作用于最終結(jié)果——先去重，后截取，結(jié)果恰好 N 行。
3. ROWNUM 阻斷子查詢提升：引用 ROWNUM 的子查詢，外部過濾條件無法下推，可能導(dǎo)致全表掃描。
4. 最佳實踐：
   • 明確業(yè)務(wù)意圖，選擇正確的寫法
   • 跨庫兼容場景下，使用嵌套子查詢或窗口函數(shù)
   • 避免將 DISTINCT + ROWNUM 作為"取 N 個不重復(fù)值"的手段

記住一條鐵律：永遠(yuǎn)不要用 ROWNUM 去做你真正想做之外的事情。它的行為高度依賴于它在 SQL 中的位置和數(shù)據(jù)庫引擎的實現(xiàn)細(xì)節(jié)。當(dāng)你對執(zhí)行順序有一絲不確定時，窗口函數(shù)永遠(yuǎn)是最安全的選擇。

到此這篇關(guān)于Oracle遷移PostgreSQL出現(xiàn)SQL結(jié)果不一致的問題排查與解決方法的文章就介紹到這了,更多相關(guān)Oracle遷移PostgreSQL踩坑內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論