J2EE 分頁查詢優化實戰：拒絕 iBatis 內存分頁，擁抱數據庫原生分頁 (Oracle ROWNUM / MySQL LIMIT)

iBatis 內存分頁的性能陷阱

iBatis (現 MyBatis 前身) 提供的 executeQueryForList(statement, params, skipResults, maxResults) 看似方便，實則**在 JDBC 層獲取全量 ResultSet 後，在 Java 內存中跳過前 skipResults 條，再取 maxResults 條**。

危害分析

• 內存爆炸：百萬級數據全載入 JVM Heap，易觸發 OOM。
• 網絡浪費：全量數據從 DB 傳輸到應用服務器。
• DB 壓力：全表掃描/全索引掃描，無法利用 Limit/Top 下推優化。

關閉 iBatis 內部緩存 (緩解但不治本)

<settings>
    <setting name="lazyLoadingEnabled" value="false"/>
    <setting name="cacheModelsEnabled" value="false"/>
    <setting name="enhancementEnabled" value="false"/>
    <setting name="maxRequests" value="512"/>
    <setting name="maxSessions" value="128"/>
    <setting name="maxTransactions" value="32"/>
</settings>

這僅禁用 iBatis 二級緩存，**不改變分頁時全量獲取 ResultSet 的本質**。

正解：將分頁下推至數據庫層

利用數據庫原生分頁語法，僅讓 DB 返回當前頁數據，極大降低內存、網絡、CPU 開銷。

Oracle 分頁寫法演進

1. 經典雙層嵌套 ROWNUM (Oracle 10g/11g 通用)

SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM orders WHERE status = 'PAID' ORDER BY create_time DESC
    ) a WHERE ROWNUM <= :endRow  -- endRow = pageNum * pageSize
) WHERE rn > :startRow;          -- startRow = (pageNum - 1) * pageSize

• 內層排序 + 截取前 N 行 (利用索引順序掃描停止)。
• 外層過濾起始行號。
• 關鍵：ROWNUM 在 ORDER BY **之前**賦值，故必須雙層嵌套保證排序正確。

2. Oracle 12c+ 簡潔語法 (OFFSET ... FETCH)

SELECT * FROM orders 
WHERE status = 'PAID' 
ORDER BY create_time DESC
OFFSET :startRow ROWS FETCH NEXT :pageSize ROWS ONLY;

標準 SQL:2008 語法，優化器原生支持，計劃更優。

MySQL 分頁：LIMIT 子句

SELECT * FROM orders 
WHERE status = 'PAID' 
ORDER BY create_time DESC
LIMIT :startRow, :pageSize;  -- LIMIT offset, row_count

• 語法簡潔，性能極佳 (配合索引)。
• 深分頁性能陷阱：LIMIT 1000000, 20 仍需掃描前 100 萬行。優化：使用延遲關聯或書籤記錄上一頁最大 ID：WHERE id > :lastMaxId ORDER BY id LIMIT 20。

SQL Server 分頁 (2012+)

SELECT * FROM orders 
WHERE status = 'PAID' 
ORDER BY create_time DESC
OFFSET @startRow ROWS FETCH NEXT @pageSize ROWS ONLY;

PostgreSQL 分頁

SELECT * FROM orders 
WHERE status = 'PAID' 
ORDER BY create_time DESC
LIMIT :pageSize OFFSET :startRow;

iBatis/MyBatis 整合數據庫分頁的最佳實踐

1. 動態 SQL 映射文件

<select id="selectOrdersByPage" resultType="Order">
    SELECT * FROM (
        SELECT a.*, ROWNUM rn FROM (
            SELECT * FROM orders 
            WHERE status = #{status} 
            ORDER BY create_time DESC
        ) a WHERE ROWNUM <= #{endRow}
    ) WHERE rn > #{startRow}
</select>

2. Java 代碼調用

Map params = new HashMap<>();
params.put("status", "PAID");
params.put("startRow", (pageNum - 1) * pageSize);
params.put("endRow", pageNum * pageSize);

List list = sqlMap.queryForList("selectOrdersByPage", params);

3. 總記錄數查詢 (分頁組件必需)

<select id="countOrders" resultType="int">
    SELECT COUNT(*) FROM orders WHERE status = #{status}
</select>

通用分頁工具類封裝 (ThreadLocal 避免參數傳遞)

public class PageContext {
    private static final ThreadLocal TL = new ThreadLocal<>();
    
    public static void setPage(int pageNum, int pageSize) {
        TL.set(new Page(pageNum, pageSize));
    }
    public static Page getPage() { return TL.get(); }
    public static void clear() { TL.remove(); }
    
    public static class Page {
        public final int startRow, endRow, pageSize;
        public Page(int pageNum, int pageSize) {
            this.pageSize = pageSize;
            this.startRow = (pageNum - 1) * pageSize;
            this.endRow = pageNum * pageSize;
        }
    }
}

// MyBatis 攔截器自動攔截並注入分頁 SQL (生產環境推薦 PageHelper)
public class PaginationInterceptor implements Interceptor {
    @Override public Object intercept(Invocation invocation) throws Throwable {
        Page page = PageContext.getPage();
        if (page != null) {
            // 修改 BoundSql 注入 LIMIT/OFFSET 或 ROWNUM
        }
        return invocation.proceed();
    }
}

總結

1. 堅決拒絕 skipResults/maxResults 內存分頁。
2. 優先使用 數據庫原生分頁語法 (LIMIT/OFFSET/ROWNUM)。
3. 深分頁優化：避免大 OFFSET，改用 ID 書籤/游標分頁。
4. 框架層解決：MyBatis 使用 PageHelper 攔截器自動改寫 SQL；Hibernate 使用 setFirstResult/setMaxResults (底層同樣依賴方言原生分頁)。
5. 統計總數：單獨 COUNT(*) 查詢，可緩存或異步執行。

將分頁責任交給數據庫，是處理海量數據分頁的**唯一正確架構決策**。