您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 分頁查詢優化深度解析：從 LIMIT 偏移到 ID 集合快取策略

編輯：MySQL 資料庫相關知識

問題根源

LIMIT m, n 在複雜條件下會掃描前 m+n 列再捨棄前 m 列。百萬級資料翻至後頁（如第 1000 頁），需掃描百萬列，I/O 暴增、回應逾時。

優化方案對比

方案	原理	優點	缺點
延遲關聯	`SELECT * FROM tbl INNER JOIN (SELECT id FROM tbl WHERE cond LIMIT m,n) t USING (id)`	無需應用改動，利用覆蓋索引	仍需掃描前 m 列索引
書籤/游標分頁	記住上一頁最大 ID，`WHERE id > last_id LIMIT n`	極快、無偏移掃描	不支援任意跳頁、需唯一遞增鍵
ID 集合快取	先查符合條件所有 ID 存入快取，分頁時 `WHERE id IN (...)`	支援任意跳頁、查詢極快	記憶體佔用、資料變更需失效

ID 集合快取實作詳解

步驟 1：查詢符合條件的 ID 並存入暫存表

CREATE TEMPORARY TABLE tmp_page_ids (
    page_id INT UNSIGNED NOT NULL,
    record_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (page_id, record_id)
) ENGINE=MEMORY;

INSERT INTO tmp_page_ids (page_id, record_id)
SELECT FLOOR((@rownum:=@rownum+1)/$page_size) AS page_id, id
FROM (SELECT @rownum:=0) r, (SELECT id FROM tbl WHERE cond ORDER BY sort_col) t
LIMIT $max_pages * $page_size;

步驟 2：分頁讀取

SELECT t.* FROM tbl t
INNER JOIN tmp_page_ids tmp ON t.id = tmp.record_id
WHERE tmp.page_id = $requested_page
ORDER BY t.sort_col;

步驟 3：快取失效策略

時間過期：暫存表自動隨 Session 結束銷毀
版本號：資料表維護 version 欄位，查詢前比對
觸發器/應用層：寫入時標記相關快取失效

進階優化：合併查詢

INSERT INTO tmp_page_ids (page_id, record_id)
SELECT FLOOR((@rownum:=@rownum+1)/$page_size), id
FROM (SELECT @rownum:=0) r, (SELECT id FROM tbl WHERE cond ORDER BY sort_col) t
LIMIT $max_pages * $page_size;

單條 SQL 完成 ID 擷取與分頁號分配，減少往返。