LIMIT 是 MySQL 分頁的核心，但隨著資料量增長，LIMIT offset, rows 的深分頁效能會急劇下降。本文從基礎語法出發，深入分析深分頁慢的原因，並提供多種優化方案與 PHP 實戰整合範例。

LIMIT 基礎語法

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

• 單參數：LIMIT 5 ≡ LIMIT 0,5（前 5 筆）
• 雙參數：LIMIT 5,10 取第 6-15 筆（offset 從 0 開始）
• 負數技巧：LIMIT 95,-1 取第 96 筆到最後
• PostgreSQL 相容：LIMIT 10 OFFSET 5

深分頁效能問題根因

LIMIT 100000, 10 需要掃描前 100,000 行再丟棄，只取 10 行。隨著 offset 增大，掃描成本線性增長，IO 與 CPU 消耗劇增。

優化方案對比實測（100 萬測試數據）

方案一：標準 LIMIT（基準）

SELECT * FROM yanxue8_visit LIMIT 10000, 10;
-- 耗時：~0.0187 秒

方案二：子查詢 + 覆蓋索引定位（推薦）

SELECT * FROM yanxue8_visit 
WHERE vid >= (
    SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 1
) LIMIT 10;
-- 耗時：~0.0061 秒（約 1/3）

原理：內層查詢僅掃描主鍵 vid（覆蓋索引），快速定位 offset 位置的主鍵值；外層直接按主鍵範圍查詢，避免大量回表與丟棄。

方案三：延遲關聯

SELECT t.* FROM yanxue8_visit t
INNER JOIN (
    SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 10
) tmp USING (vid);

同樣利用覆蓋索引快速定位主鍵，再回表取完整行。

方案四：記住上一頁最大 ID（游標分頁，最優）

-- 首頁
SELECT * FROM yanxue8_visit ORDER BY vid LIMIT 10;

-- 後續頁（假設上一頁最大 vid = 10010）
SELECT * FROM yanxue8_visit WHERE vid > 10010 ORDER BY vid LIMIT 10;

優勢：無 offset 掃描，性能極其穩定，適合無限滾動、API 分頁。限制：不支援跳轉任意頁、需唯一遞增排序鍵。

PHP 分頁整合範例（傳統模式）

<?php
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$pagesize = 20;
$offset = ($page - 1) * $pagesize;

// 總數查詢（可用 SQL_CALC_FOUND_ROWS 優化）
$total = $pdo->query("SELECT COUNT(*) FROM info")->fetchColumn();
$pages = ceil($total / $pagesize);

// 數據查詢
$stmt = $pdo->prepare("SELECT * FROM info ORDER BY id LIMIT ?, ?");
$stmt->execute([$offset, $pagesize]);
$rows = $stmt->fetchAll();

// 輸出表格與分頁鏈接...
?>

大數據量分頁最佳實踐總結

場景	推薦方案	優缺點
後台管理、需跳頁	子查詢定位 / 延遲關聯	支援任意跳頁，中等效能
前端無限滾動、API 分頁	游標分頁	效能最佳、穩定，不支援跳頁
靜態報表、全量匯出	游標分頁 + 批次處理	避免長事務、記憶體溢出
搜尋結果分頁	Elasticsearch 等搜尋引擎	MySQL 全文檢索分頁效能差

關鍵索引設計原則

• 排序欄位 + 過濾欄位建立複合索引：INDEX (status, created_at, id)
• 覆蓋索引：子查詢只選主鍵，外層回表
• 避免 ORDER BY RAND()、ORDER BY 非索引欄位 觸發 filesort

選對分頁策略，配合合適索引，百萬級甚至億級資料也能實現毫秒級分頁響應。