Java 程序員寫 SQL 的 10 大陷阱與最佳實踐指南

前言

Java 開發者習慣命令式與面向對象思維，而 SQL 是聲明式語言。這種認知差異導致許多 Java 程序員在編寫 SQL 時陷入效能低下、邏輯錯誤、可維護性差的泥潭。本文梳理 10 大高頻錯誤，並給出針對性優化方案，助你寫出高性能、正確、優雅的 SQL。

1. 忽視 NULL 的三值邏輯陷阱

錯誤現象：將 NULL 視為普通值，導致 `NULL = NULL` 為假、`NOT IN` 子查詢含 NULL 返回空集、約束條件失效。

核心認知：SQL 中 NULL = UNKNOWN，任何與 NULL 比較結果為 UNKNOWN。JDBC 將 SQL NULL 映射為 Java null，易產生誤解。

解決方案：

• 寫查詢時時刻考慮：WHERE col IS NULL / IS NOT NULL、COALESCE、NULLIF。
• 避免 `NOT IN (subquery)`，改用 `NOT EXISTS` 或 `LEFT JOIN ... IS NULL`。
• 檢查約束定義：`CHECK (col > 0)` 會拒絕 NULL，需顯式 `OR col IS NULL`。

2. 在 Java 內存中處理海量數據

錯誤現象：將百萬行數據 `SELECT *` 拉到 JVM，用 `Stream`/`for` 循環聚合、分組、去重。

最佳實踐：

• 下推計算到數據庫：窗口函數（`ROW_NUMBER`, `SUM() OVER`）、`GROUP BY`、`DISTINCT`、集合運算（`UNION/INTERSECT/EXCEPT`）。
• 利用數據庫優化器、索引、並行查詢能力，僅傳輸結果集。
• Oracle `MODEL` 子句、PostgreSQL `LATERAL JOIN` 等高級特性可解決極複雜邏輯。

3. 濫用 UNION 而非 UNION ALL

錯誤現象：預設寫 `UNION` 去重，觸發排序與比較，大數據集極慢。

原則：業務允許重複或已知無重複時，**永遠優先 `UNION ALL`**。需去重時才用 `UNION`（或 `UNION DISTINCT`）。

4. 應用層分頁代替數據庫原生分頁

錯誤現象：JDBC 拿全量結果，內存 `skip/limit`。

標準語法：

• MySQL/PostgreSQL/SQLite: `LIMIT n OFFSET m`
• Oracle 12c+: `OFFSET m ROWS FETCH NEXT n ROWS ONLY`
• SQL Server: `OFFSET m ROWS FETCH NEXT n ROWS ONLY`
• 舊版 Oracle: `ROWNUM` 子查詢嵌套

ORM 框架（jOOQ, MyBatis, Hibernate）均支援方言自適應分頁，務必開啟。

5. 應用層手工 JOIN 替代數據庫 JOIN

錯誤現象：多次查詢拉取多表，Java 中 `Map` 關聯。

代價：網絡往返增加、內存壓力大、喪失數據庫 Hash Join / Merge Join / Nested Loop 等優化策略。

原則：單條 SQL 完成關聯，僅在極特殊場景（跨數據庫實例、異構數據源）考慮應用層組裝。

6. 用 DISTINCT/UNION 掩蓋笛卡爾積缺陷

錯誤現象：多表 JOIN 缺少關聯條件產生笛卡爾積，用 `DISTINCT` 強行去重。

危害：掩蓋邏輯 Bug、觸發全排序極慢、內存溢出風險。

排查：見到 `DISTINCT` 即檢查 JOIN 條件是否缺失外鍵列、是否存在 1:N:N 鏈路導致膨脹。

7. 忽視 MERGE (UPSERT) 語句

錯誤現象：`SELECT` 判斷存在 → `INSERT` 或 `UPDATE`，並發下競態條件頻發。

標準方案：

• 標準 SQL: `MERGE INTO target USING source ON (...) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...`
• MySQL: `INSERT ... ON DUPLICATE KEY UPDATE ...`
• PostgreSQL: `INSERT ... ON CONFLICT (...) DO UPDATE ...`
• SQL Server: `MERGE` 或 `UPSERT` 模式

原子性、無鎖競爭、代碼簡潔。

8. 用聚合子查詢代替窗口函數

錯誤現象：需保留明細行同時顯示聚合指標（如各部門薪資平均值），寫法：

SELECT *, (SELECT AVG(sal) FROM emp e2 WHERE e2.dept = e1.dept) avg_sal FROM emp e1;

優化：窗口函數 `AVG(sal) OVER (PARTITION BY dept)` —— 可讀性強、優化器易下推、避免重複掃描。

適用場景：排名、累計、移動平均、同比環比、分頁優化（`ROW_NUMBER() OVER`）。

9. 在 Java 內存排序而非 ORDER BY

誤區：以為 `ORDER BY` 慢，實則數據庫利用索引可避免排序，且支援複雜表達式（`CASE WHEN`、函數）。

原則：數據庫擅長排序，且分頁必須配合 `ORDER BY` 保證確定性。僅在結果集極小（<1000 行）且無索引支持時，應用層排序可接受。

10. 逐條 INSERT 批量數據

錯誤現象：循環執行 `PreparedStatement.executeUpdate()`，每條獨立事務或頻繁提交。

標準批處理：

ps = conn.prepareStatement("INSERT INTO t(a,b) VALUES(?,?)");
for (Data d : list) {
  ps.setInt(1, d.a);
  ps.setString(2, d.b);
  ps.addBatch();
  if (++count % 1000 == 0) ps.executeBatch(); // 分批提交控制 Undo Log
}
ps.executeBatch();

關鍵：`addBatch()` + 定期 `executeBatch()`，關閉自動提交，大幅降低網絡往返與日誌開銷。

總結

SQL 是集合論與關係代數的實踐，**思維轉型**是關鍵：

1. 集合操作替代迭代
2. 聲明「要什麼」而非「怎麼做」
3. 信任優化器，輔以索引、統計信息、執行計劃分析

將這 10 點內化為代碼審查清單，你的 SQL 質量將躍升新台階。