MySQL 分頁兩大核心方案
| 方案 | 語法 | 優點 | 缺點 | 適用場景 |
| Offset 分頁 | LIMIT offset, pageSize | 語法簡單、隨機跳頁 | 深分頁性能急劇下降 (掃描 offset 行) | 頁碼較少、後台管理、數據量 < 10萬 |
| Keyset 分頁 | WHERE id > lastId ORDER BY id LIMIT pageSize | 性能穩定、利用索引 | 不支援隨機跳頁、需唯一排序鍵 | 無限滾動、大數據量、API 分頁 |
方案一:Offset 分頁完整 JSP 實現
<%@ page import="java.sql.*, java.util.*" contentType="text/html;charset=UTF-8" %>
<%
// 1. 參數接收與校驗
int pageSize = 10;
int pageNum = 1;
try { pageNum = Integer.parseInt(request.getParameter("page")); } catch (Exception ignored) {}
if (pageNum < 1) pageNum = 1;
int offset = (pageNum - 1) * pageSize;
// 2. 連線池獲取連線 (假設已配置 JNDI jdbc/mydb)
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null, cntRs = null;
int totalRecords = 0, totalPages = 0;
try {
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/mydb");
conn = ds.getConnection();
// 3. 查總數
cntRs = conn.prepareStatement("SELECT COUNT(*) FROM articles").executeQuery();
if (cntRs.next()) totalRecords = cntRs.getInt(1);
totalPages = (int) Math.ceil((double) totalRecords / pageSize);
if (pageNum > totalPages) pageNum = totalPages;
offset = (pageNum - 1) * pageSize;
// 4. 分頁查詢
pstmt = conn.prepareStatement(
"SELECT id, title, author, create_time FROM articles ORDER BY id DESC LIMIT ?, ?");
pstmt.setInt(1, offset);
pstmt.setInt(2, pageSize);
rs = pstmt.executeQuery();
%>
<table border="1" cellpadding="5">
<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">
共 <%= totalRecords %> 條 / <%= totalPages %> 頁
<% if (pageNum > 1) { %> <a href="?page=<%= pageNum-1 %>">上一頁</a> <% } %>
<% for (int i = 1; i <= totalPages; i++) { %>
<% if (i == pageNum) { %> <strong><%= i %></strong> <% } else { %> <a href="?page=<%= i %>"><%= i %></a> <% } %>
<% } %>
<% if (pageNum < totalPages) { %> <a href="?page=<%= pageNum+1 %>">下一頁</a> <% } %>
</div>
<%
} catch (Exception e) { e.printStackTrace(); out.println("錯誤:" + e.getMessage()); }
finally { /* 關閉資源 */ }
%>
方案二:Keyset 分頁 (適合無限滾動/APP API)
<%-- list.jsp 片段 --%>
<%
int pageSize = 20;
String lastIdParam = request.getParameter("lastId"); // 客戶端傳遞上一頁最後一條 ID
long lastId = lastIdParam != null ? Long.parseLong(lastIdParam) : Long.MAX_VALUE;
// 查詢:ID 小於 lastId 的前 pageSize 條,倒序
String sql = "SELECT id, title, content FROM articles WHERE id < ? ORDER BY id DESC LIMIT ?";
// ... PreparedStatement 設置參數 lastId, pageSize
%>
<div id="article-list">
<% while (rs.next()) { %>
<article data-id="<%= rs.getLong("id") %>">
<h3><%= rs.getString("title") %></h3>
<p><%= rs.getString("content") %></p>
</article>
<% } %>
</div>
<script>
// 無限滾動載入
let loading = false;
window.addEventListener('scroll', () => {
if (loading) return;
const last = document.querySelector('#article-list article:last-child');
if (!last) return;
const rect = last.getBoundingClientRect();
if (rect.top < window.innerHeight + 200) {
loading = true;
fetch('list.jsp?lastId=' + last.dataset.id)
.then(r => r.text())
.then(html => { document.getElementById('article-list').insertAdjacentHTML('beforeend', html); loading = false; });
}
});
</script>
性能優化關鍵點
- 覆蓋索引:
CREATE INDEX idx_articles_id_desc ON articles(id DESC); 或 PRIMARY KEY (id) 天然有序
- 避免 SELECT *:只查需要的欄位,減少 IO
- 總數統計優化:
- 近似值:
EXPLAIN SELECT COUNT(*) 或 information_schema.TABLES
- 緩存總數 (Redis),定期異步更新
- Keyset 分頁不需要總數
- 連線池:HikariCP,
maximumPoolSize 按 CPU 核心數調整
- 游標/流式讀取:
stmt.setFetchSize(Integer.MIN_VALUE) (MySQL) 避免大結果集 OOM
現代框架整合
- MyBatis:
PageHelper.startPage(pageNum, pageSize) 自動攔截 LIMIT
- Spring Data JPA:
Pageable pageable = PageRequest.of(page-1, size, Sort.by("id").descending());
- MyBatis-Plus:
IPage page = new Page<>(pageNum, pageSize); mapper.selectPage(page, null);