萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> JSP 程式設計

JSP + MySQL 實現高性能分頁:LIMIT 偏移與 Keyset 分頁對比實戰

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>

性能優化關鍵點

  1. 覆蓋索引:CREATE INDEX idx_articles_id_desc ON articles(id DESC); 或 PRIMARY KEY (id) 天然有序
  2. 避免 SELECT *:只查需要的欄位,減少 IO
  3. 總數統計優化:
    • 近似值:EXPLAIN SELECT COUNT(*) 或 information_schema.TABLES
    • 緩存總數 (Redis),定期異步更新
    • Keyset 分頁不需要總數
  4. 連線池:HikariCP,maximumPoolSize 按 CPU 核心數調整
  5. 游標/流式讀取: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);
copyright © 萬盛學電腦網 all rights reserved