MySQL LIMIT 分頁查詢完整語法與實戰技巧

LIMIT 是 MySQL 分頁查詢的核心子句，支援偏移量與行數控制。本文匯總標準語法、常見陷阱、大偏移量優化與分頁最佳實踐。

標準語法

SELECT select_list
FROM table_name
[WHERE condition]
[ORDER BY column]
LIMIT [offset,] row_count

三種等效寫法

實戰示例

-- 第 1 頁，每頁 20 條
SELECT name FROM usertb WHERE age > 20 ORDER BY id LIMIT 0, 20;

-- 第 n 頁（n 從 1 開始）
SELECT name FROM usertb WHERE age > 20 ORDER BY id LIMIT (n-1)*20, 20;

核心陷阱：大偏移量效能崩塌

LIMIT 1000000, 20 會掃描 1,000,020 行再丟棄前 100 萬行，隨偏移量線性增長耗時。

優化方案

1. 延遲關聯（覆蓋索引掃描 + 回表）：

SELECT t.* FROM usertb t
INNER JOIN (
  SELECT id FROM usertb WHERE age > 20 ORDER BY id LIMIT 1000000, 20
) tmp USING (id);

內層僅掃描索引列 id，外層按主鍵回表 20 行。

2. 游標分頁（Seek Method / Keyset Pagination）：

-- 首頁
SELECT * FROM usertb WHERE age > 20 ORDER BY id LIMIT 20;
-- 記錄最後一行 id = 999999
-- 下一頁
SELECT * FROM usertb WHERE age > 20 AND id > 999999 ORDER BY id LIMIT 20;

無偏移量掃描，效能極其穩定，**但不支援跳頁**，適合無限滾動/下一頁場景。

3. 覆蓋索引：確保 WHERE、ORDER BY、SELECT 列均在同一複合索引中，實現 Using index 避免回表。

分頁總數計算

SELECT COUNT(*) FROM usertb WHERE age > 20;
-- 大表可用近似值：EXPLAIN 或 information_schema.TABLES

版本差異提示

• MySQL 8.0+ 支援 LIMIT ... OFFSET ... 標準語法
• MariaDB 10.3+ 支援 FETCH FIRST n ROWS ONLY
• LIMIT -1 表示「取剩餘所有行」（配合 OFFSET 使用）