-- 典型深翻頁災難
SELECT * FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20;
-- MySQL 必須掃描 1,000,020 行,丟棄前 1,000,000 行,極慢
-- 1. 只在索引上定位主鍵 (覆蓋索引掃描,極快)
SELECT id FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20;
-- 2. 主鍵回表查詳細列
SELECT t.*
FROM huge_table t
JOIN (
SELECT id FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20
) tmp USING (id);
原理: 子查詢僅掃描索引葉子節點 (窄行),回表僅 20 行。
前提: WHERE/Order By 列有覆蓋索引。
-- 首頁
SELECT * FROM huge_table WHERE status=1 ORDER BY id LIMIT 20;
-- 下一頁: 記住上一頁最大 id
SELECT * FROM huge_table WHERE status=1 AND id > 1000020 ORDER BY id LIMIT 20;
-- 上一頁: 記住上一頁最小 id
SELECT * FROM huge_table WHERE status=1 AND id < 1000001 ORDER BY id DESC LIMIT 20;
優勢: 無 OFFSET 掃描,恆定毫秒級。
限制: 僅支持「下一頁/上一頁」,不支持跳頁;排序列必須唯一 (或 (sort_col, pk) 組合唯一)。
-- 建立覆蓋索引
CREATE INDEX idx_status_id_name ON huge_table (status, id, name, ...);
-- 查詢僅需索引列
SELECT id, name FROM huge_table WHERE status=1 ORDER BY id LIMIT 1000000, 20;
-- 無需回表,但仍需掃描 100 萬索引條目
-- 標準 SQL:2008 語法 (MySQL 8.0.2+ 支持)
SELECT * FROM huge_table WHERE status=1 ORDER BY id
OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY;
-- 等價於 LIMIT 1000000, 20
-- 可讀性更好,遷移其他數據庫無需改動
LIMIT offset, size (簡單優先)ORDER BY RAND() LIMIT 10 → 全表掃描+文件排序,禁用LIMIT 1000000, 20 無覆蓋索引 → 全表掃描+回表,災難ORDER BY sort_col, pk**Handler_read_next、Handler_read_rnd_next 觀察掃描行數EXPLAIN 確認 type=ref/range、Extra=Using index
Pageable firstPage = PageRequest.of(0, 20, Sort.by("id"));
Slice slice = userRepository.findByStatus(1, firstPage);
// 下一頁
Pageable nextPage = PageRequest.of(slice.getNumber()+1, 20, Sort.by("id"));
// 或手動構建 Keyset: userRepository.findByStatusAndIdGreaterThan(1, lastId, PageRequest.of(0,20,Sort.by("id")));