JDBC 分頁查詢 MySQL 實戰：LIMIT 子句、PreparedStatement 參數化與總記錄數統計

JDBC 分頁查詢 MySQL 實戰：LIMIT 子句、PreparedStatement 參數化與總記錄數統計

大數據量下分頁查詢是剛需。MySQL 通過 LIMIT offset, row_count 原生支持高效分頁。本文演示完整的分頁 DAO 實現：總記錄數統計、總頁數計算、邊界校驗、參數化查詢與結果映射。

MySQL LIMIT 語法

SELECT * FROM emp ORDER BY empno LIMIT 5, 5;  -- 跳過前 5 行，取 5 行 (第 2 頁，每頁 5 條)
-- 參數含義：LIMIT [offset,] row_count
-- offset：起始行號 (0-based)，第 1 頁 offset=0
-- row_count：每頁條數

Oracle 使用 ROWNUM 子查詢較繁瑣，MySQL LIMIT 簡潔高效。

完整分頁 DAO 代碼

public class EmpDAO {
    public void findByPageMySQL(int page, int pageSize) {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionSource.getConnection();

            // 1. 統計總記錄數
            String sqlCount = "SELECT COUNT(*) FROM emp";
            stmt = con.prepareStatement(sqlCount);
            rs = stmt.executeQuery();
            int total = 0;
            if (rs.next()) total = rs.getInt(1);
            System.out.println("總記錄數：" + total);

            // 2. 計算總頁數
            int pages = (total + pageSize - 1) / pageSize; // 向上取整

            // 3. 邊界校驗
            if (page > pages) page = pages;
            if (page < 1) page = 1;

            // 4. 分頁查詢
            String sql = "SELECT * FROM emp ORDER BY empno LIMIT ?, ?";
            stmt = con.prepareStatement(sql);
            int offset = (page - 1) * pageSize;
            stmt.setInt(1, offset);
            stmt.setInt(2, pageSize);
            System.out.println("執行 SQL：" + sql + " 參數：[" + offset + ", " + pageSize + "]");

            rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt("empno") + ", " +
                        rs.getString("ename") + ", " +
                        rs.getDouble("sal") + ", " +
                        rs.getDate("hiredate"));
            }
        } catch (SQLException e) {
            throw new RuntimeException("分頁查詢異常", e);
        } finally {
            closeQuietly(rs);
            closeQuietly(stmt);
            closeQuietly(con);
        }
    }

    private void closeQuietly(AutoCloseable c) {
        if (c != null) try { c.close(); } catch (Exception ignored) {}
    }
}

測試輸出

總記錄數：11
執行 SQL：SELECT * FROM emp ORDER BY empno LIMIT ?, ? 參數：[3, 3]
7566, JONES, 2975.00, 1981-04-02
7654, MARTIN, 1250.00, 1981-09-28
7698, BLAKE, 2850.00, 1981-05-01

第 2 頁 (page=2, pageSize=3)，offset=3，正確返回第 4-6 條記錄。

性能優化關鍵點

1. 索引支持 ORDER BY：ORDER BY empno 需 empno 有索引 (主鍵自動)，否則需 filesort，大表極慢。
2. 避免大偏移量：LIMIT 1000000, 10 仍需掃描前 100 萬行。優化方案：
   • 「游標分頁」：記住上一頁最大 ID，WHERE id > last_id ORDER BY id LIMIT 10。
   • 「延遲關聯」：SELECT * FROM emp INNER JOIN (SELECT id FROM emp ORDER BY empno LIMIT 1000000, 10) t USING (id)，子查詢只掃描索引。
3. 覆蓋索引：若只查部分列，建立 INDEX(empno, ename, sal, hiredate) 實現 Using index，避免回表。
4. 總數統計優化：COUNT(*) 大表較慢。可用 Redis 緩存總數、近似值 (EXPLAIN rows)、或異步更新計數表。

圖示

總結

MySQL LIMIT 分頁簡單高效，核心在於：索引支撐排序、參數化防注入、邊界校驗、大偏移量優化策略。生產環境建議封裝通用 PageHelper 或使用 MyBatis Plus 分頁插件自動攔截處理。