分頁是 Web 應用中最常見的需求，但隨著數據量增長，傳統 LIMIT offset, size (MySQL) 或 ROWNUM (Oracle) 深分頁性能會急劇下降。本文針對 JSP + JDBC 場景，分析高效分頁 SQL 寫法原理，並提供完整實現代碼。
假設表 articles 有 100 萬條記錄，查詢第 50,000 頁 (每頁 20 條)：
-- 寫法 1：MySQL LIMIT
SELECT * FROM articles ORDER BY id DESC LIMIT 1000000, 20;
-- 寫法 2：Oracle ROWNUM 子查詢
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT * FROM articles ORDER BY id DESC
) a WHERE ROWNUM <= 1000020
) WHERE rn > 1000000;問題：資料庫需掃描並排序前 1,000,020 條記錄再丟棄前 1,000,000 條，I/O 與 CPU 消耗巨大，響應時間隨頁碼線性增長。
原理：利用索引覆蓋，僅掃描索引葉子節點獲取主鍵 ID，再回表查詢目標頁的完整列數據。
-- 步驟 1：僅掃描主鍵索引獲取目標頁 ID (極快)
SELECT id FROM articles ORDER BY id DESC LIMIT 1000000, 20;
-- 步驟 2：根據 ID 列表回表查詢完整數據 (僅 20 條)
SELECT * FROM articles WHERE id IN (上述ID列表) ORDER BY id DESC;或合併為單條 SQL (MySQL 5.6+ 優化器可自動優化)：
SELECT a.* FROM articles a
INNER JOIN (
SELECT id FROM articles ORDER BY id DESC LIMIT 1000000, 20
) b ON a.id = b.id
ORDER BY a.id DESC;SELECT TOP 20 * FROM articles
WHERE id NOT IN (
SELECT TOP 1000000 id FROM articles ORDER BY id DESC
)
ORDER BY id DESC;注意：NOT IN 子查詢在某些資料庫中性能較差，優先考慮 NOT EXISTS 或 LEFT JOIN ... IS NULL 寫法，或使用窗口函數 ROW_NUMBER() (SQL Server 2005+, Oracle, PostgreSQL)。
<%@ page language="java" import="java.util.*,java.sql.*" contentType="text/html;charset=gb2312" %>
<jsp:useBean id="db" scope="page" class="com.myapp.db.ConnectionPool" />
<%
// 1. 參數獲取與校驗
int pageSize = 20;
int currentPage = 1;
String pageParam = request.getParameter("page");
if (pageParam != null) {
try { currentPage = Integer.parseInt(pageParam); }
catch (NumberFormatException ignored) {}
}
if (currentPage < 1) currentPage = 1;
// 2. 獲取連接 (假設 ConnectionPool 提供 getConnection())
Connection conn = db.getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 3. 查詢總記錄數 (可緩存、異步更新、或使用近似值)
String countSql = "SELECT COUNT(*) FROM articles";
pstmt = conn.prepareStatement(countSql);
rs = pstmt.executeQuery();
int totalRows = 0;
if (rs.next()) totalRows = rs.getInt(1);
rs.close(); pstmt.close();
// 4. 計算總頁數
int totalPages = (totalRows + pageSize - 1) / pageSize;
if (currentPage > totalPages) currentPage = totalPages;
// 5. 高效分頁查詢 (以 MySQL 為例，使用延遲關聯)
int offset = (currentPage - 1) * pageSize;
String dataSql = "SELECT a.id, a.title, a.create_time, a.author " +
"FROM articles a " +
"INNER JOIN (SELECT id FROM articles ORDER BY id DESC LIMIT ?, ?) b ON a.id = b.id " +
"ORDER BY a.id DESC";
pstmt = conn.prepareStatement(dataSql);
pstmt.setInt(1, offset);
pstmt.setInt(2, pageSize);
rs = pstmt.executeQuery();
%>
<table border="1" cellpadding="5" cellspacing="0">
<tr><th>ID</th><th>標題</th><th>作者</th><th>發布時間</th></tr>
<% while (rs.next()) { %>
<tr>
<td><%= rs.getInt("id") %></td>
<td><%= rs.getString("title") %></td>
<td><%= rs.getString("author") %></td>
<td><%= rs.getTimestamp("create_time") %></td>
</tr>
<% } %>
</table>
<div class="pagination">
<% if (currentPage > 1) { %>
<a href="?page=<%= currentPage - 1 %>">上一頁</a>
<% } %>
<span>第 <%= currentPage %> 頁 / 共 <%= totalPages %> 頁</span>
<% if (currentPage < totalPages) { %>
<a href="?page=<%= currentPage + 1 %>">下一頁</a>
<% } %>
</div>
<%
} catch (SQLException e) {
out.println("查詢異常: " + e.getMessage());
e.printStackTrace();
} finally {
// 6. 資源釋放 (必須在 finally 中)
try { if (rs != null) rs.close(); } catch (SQLException ignored) {}
try { if (pstmt != null) pstmt.close(); } catch (SQLException ignored) {}
try { if (conn != null) db.releaseConnection(conn); } catch (SQLException ignored) {}
}
%>WHERE id > lastId ORDER BY id LIMIT 20，無 OFFSET 開銷，適合無限滾動/瀑布流場景。COUNT(*) 在大表極慢，可定時任務緩存總數、或使用資料庫統計信息近似值、或僅顯示「上一頁/下一頁」不顯示總頁數。ORDER BY 列與 WHERE 列建立聯合索引，實現 Using index 避免回表。高效分頁的核心在於 減少資料庫掃描行數。JSP 層面應：使用 PreparedStatement 防注入、嚴格釋放資源、參數校驛；SQL 層面根據資料庫特性選擇 延遲關聯、游標分頁、窗口函數 等優化手段；架構層面考慮 緩存總數、異步加載、搜索引擎分流。組合拳打出，千萬級數據分頁也能毫秒級響應。
