為什麼 LIMIT 1000000, 20 會慢？

MySQL 需掃描前 1,000,000 行再丟棄，再取 20 行。隨著偏移量增大，耗時線性增長。

八大優化方案（按推薦度排序）

1. 基於索引的「游標式」分頁（最推薦）

-- 假設 id 為主鍵/唯一索引，每頁 20 條
SELECT * FROM `t`
WHERE `id` > 1000000   -- 上一頁最大 id
ORDER BY `id` ASC
LIMIT 20;

• 索引範圍掃描，type=range，無 filesort，毫秒級返回。
• 需前端傳遞「上一頁最大 ID」而非頁碼。
• 不支援隨機跳頁，適合無限滾動、列表翻頁場景。

2. 子查詢/聯接定位 + 回表

-- 子查詢法
SELECT * FROM `t`
WHERE `id` <= (
  SELECT `id` FROM `t` ORDER BY `id` DESC LIMIT 1000000, 1
)
ORDER BY `id` DESC LIMIT 20;

-- 聯接法（通常更快）
SELECT a.* FROM `t` AS a
JOIN (
  SELECT `id` FROM `t` ORDER BY `id` DESC LIMIT 1000000, 1
) AS b ON a.`id` <= b.`id`
ORDER BY a.`id` DESC LIMIT 20;

• 子查詢/聯接先在索引上定位偏移位置，再回表取數據。
• 避免了大偏移下的全索引掃描。

3. 延遲關聯

SELECT a.* FROM `t` AS a
JOIN (
  SELECT `id` FROM `t` ORDER BY `id` LIMIT 1000000, 20
) AS b USING (`id`);

• 內層只掃描索引列（Using index），外層按主鍵精確回表 20 行。
• MySQL 5.6+ 優化器自動優化為「Index Condition Pushdown」等效效果。

4. 覆蓋索引 + LIMIT

-- 只查索引覆蓋的列
SELECT `id`, `title`, `created_at` FROM `articles`
WHERE `status` = 1
ORDER BY `created_at` DESC
LIMIT 1000000, 20;

• 若查詢列全部在二級索引中，避免回表，速度提升數倍。

5. Prepared Statement + 游標分頁

PREPARE stmt FROM
'SELECT * FROM `t` WHERE `id` > ? ORDER BY `id` ASC LIMIT ?';
SET @last_id = 1000000, @page_size = 20;
EXECUTE stmt USING @last_id, @page_size;

• 減少解析優化開銷，適合高併發固定模板查詢。

6. 存儲過程封裝（減少網絡往返）

CREATE PROCEDURE `sp_page`(IN last_id BIGINT, IN page_size INT)
BEGIN
  SELECT * FROM `t` WHERE `id` > last_id ORDER BY `id` LIMIT page_size;
END;

• 將邏輯下沉到 Server 端，減少客戶端交互延遲。

7. 物化視圖/汇總表（極大偏移、低頻變更）

預先計算分頁切片 ID 範圍存入輔助表，查詢時直接 JOIN。

8. 反面教材：SQL_CALC_FOUND_ROWS

SELECT SQL_CALC_FOUND_ROWS * FROM `t` LIMIT 1000000, 20;
SELECT FOUND_ROWS();

• 會掃描全表計算總行數，比無優化的 LIMIT 更慢，生產環境禁用。

方案選型決策樹

是否支援「游標式」（前端可傳 last_id）？
  ├─ 是 → 方案 1（最優）
  └─ 必須支援「跳頁」
       ├─ 偏移量 < 10,000 → 方案 4（覆蓋索引）或原生 LIMIT
       ├─ 偏移量 10,000~1,000,000 → 方案 3（延遲關聯）
       └─ 偏移量 > 1,000,000 → 方案 2（子查詢/聯接定位）或方案 7（物化表）

實戰效能對比（千萬級表，主鍵 id）

方案	LIMIT 10000,20	LIMIT 1000000,20	LIMIT 5000000,20
原生 LIMIT	12 ms	1,200 ms	6,500 ms
游標式 (id > last)	2 ms	2 ms	2 ms
延遲關聯	8 ms	15 ms	30 ms
子查詢定位	10 ms	25 ms	45 ms

總結

• 大偏移分頁的本質是「跳過前 N 行」的代價，解法核心是「不掃描前 N 行」。
• 業務允許時，優先改造為「游標式分頁」，體驗最好、效能最穩。
• 必須跳頁時，延遲關聯/子查詢定位是通用折中方案。