MySQL LIMIT 查詢優化實戰：小偏移直用 LIMIT、大偏移子查詢延遲關聯

核心結論

根據偏移量大小選擇策略：

• 小偏移 (offset < 1000)：直接 LIMIT offset, n 最快
• 大偏移 (offset > 1000)：子查詢延遲關聯 WHERE id >= (SELECT id ... LIMIT offset, 1) 顯著優於直接 LIMIT

實測數據支撐

原理解析

• 直接 LIMIT：全索引掃描至 offset 位置，然後回表取數據。Offset 越大，掃描頁數越多，I/O 線性增長
• 子查詢法：內層 SELECT uid ... LIMIT offset, 1 僅遍歷聚簇索引葉子節點 (極窄)，定位目標主鍵值；外層 WHERE uid >= target 直接範圍查找，僅回表 LIMIT n 行
• 關鍵差異：避免了「掃描 offset 行 + 回表 offset 行」的雙重開銷

生產環境封裝建議

-- 通用分頁存儲過程
CREATE PROCEDURE page_query(
    IN p_table VARCHAR(64),
    IN p_where VARCHAR(500),
    IN p_order VARCHAR(100),
    IN p_offset INT,
    IN p_limit INT,
    IN p_pk VARCHAR(64)  -- 主鍵列名
)
BEGIN
    IF p_offset < 1000 THEN
        SET @sql = CONCAT('SELECT * FROM ', p_table, ' ', p_where, ' ORDER BY ', p_order, ' LIMIT ', p_offset, ', ', p_limit);
    ELSE
        SET @sql = CONCAT(
            'SELECT * FROM ', p_table, ' ',
            'WHERE ', p_pk, ' >= (SELECT ', p_pk, ' FROM ', p_table, ' ', p_where, ' ORDER BY ', p_order, ' LIMIT ', p_offset, ', 1) ',
            p_where, ' ORDER BY ', p_order, ' LIMIT ', p_limit
        );
    END IF;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

使用：CALL page_query('user', 'WHERE status=1', 'uid', 50000, 20, 'uid');

避坑指南

1. ORDER BY 列必須有索引，否則子查詢同樣慢
2. 主鍵/唯一鍵有刪除產生的間隙不影響正確性（>= 而非 >= 配合 LIMIT 1）
3. 複雜 WHERE 子查詢與主查詢必須完全一致，否則結果集偏移
4. 極大偏移 (百萬級) 建議改用游標/應用層記住上次最大 ID 翻頁 (Keyset Pagination)