MySQL 通用分頁查詢存儲過程：高效實現與優化指南

前言

分頁查詢是 Web 應用的高頻需求。MySQL 的 LIMIT offset, rows 在大偏移量時性能急劇下降（需掃描並丟棄前 offset 行）。本文提供一個基於主鍵游標（Keyset Pagination）的通用存儲過程，實現深分頁高性能查詢，並解析其原理與適用場景。

存儲過程完整代碼

DELIMITER $$

DROP PROCEDURE IF EXISTS `prc_page_result` $$

CREATE PROCEDURE `prc_page_result` (
    IN  `p_currpage`      INT,          -- 當前頁碼 (1-based)
    IN  `p_columns`       VARCHAR(500), -- 查詢欄位，如 'id, name, created_at'
    IN  `p_tablename`     VARCHAR(500), -- 表名
    IN  `p_condition`     VARCHAR(500), -- WHERE 條件（不含 WHERE 關鍵字），如 'status=1 AND type=2'
    IN  `p_order_field`   VARCHAR(100), -- 排序欄位（必須有索引），如 'id'
    IN  `p_asc`           TINYINT,      -- 1=DESC, 0=ASC
    IN  `p_primary_field` VARCHAR(100), -- 主鍵欄位，如 'id'
    IN  `p_pagesize`      INT           -- 每頁大小
)
BEGIN
    DECLARE v_sql       VARCHAR(4000);
    DECLARE v_order     VARCHAR(200);
    DECLARE v_where     VARCHAR(600);
    DECLARE v_cursor_op VARCHAR(10);
    DECLARE v_sub_select VARCHAR(1000);

    -- 1. 構建 ORDER BY
    IF p_asc = 1 THEN
        SET v_order = CONCAT(' ORDER BY `', p_order_field, '` DESC ');
        SET v_cursor_op = '<';
    ELSE
        SET v_order = CONCAT(' ORDER BY `', p_order_field, '` ASC ');
        SET v_cursor_op = '>';
    END IF;

    -- 2. 構建 WHERE 子句
    IF TRIM(p_condition) = '' THEN
        SET v_where = '';
    ELSE
        SET v_where = CONCAT(' WHERE ', p_condition);
    END IF;

    -- 3. 核心邏輯：首頁用 LIMIT，非首頁用主鍵游標
    IF p_currpage = 1 THEN
        -- 第一頁：直接 LIMIT
        SET v_sql = CONCAT('SELECT ', p_columns, ' FROM `', p_tablename, '`', v_where, v_order, ' LIMIT ?');
    ELSE
        -- 非首頁：子查詢取上一頁最後一條的主鍵值作為游標
        SET v_sub_select = CONCAT(
            'SELECT `', p_primary_field, '` FROM `', p_tablename, '`', v_where, v_order,
            ' LIMIT ', (p_currpage - 1) * p_pagesize, ', 1'
        );
        -- 主查詢：WHERE pk > (或 <) 游標值
        SET v_sql = CONCAT(
            'SELECT ', p_columns, ' FROM `', p_tablename, '`',
            v_where,
            IF(v_where = '', ' WHERE ', ' AND '),
            '`', p_primary_field, '` ', v_cursor_op, ' (', v_sub_select, ')',
            v_order, ' LIMIT ?'
        );
    END IF;

    -- 4. 準備並執行
    SET @page_size = p_pagesize;
    SET @stmt_sql = v_sql;
    PREPARE stmt FROM @stmt_sql;
    EXECUTE stmt USING @page_size;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

調用示例

-- 第 1 頁，每頁 25 條，按 id 降序
CALL prc_page_result(1, 'id, title, create_time', 'articles', 'status=1', 'id', 1, 'id', 25);

-- 第 100 頁（深分頁），同樣高效
CALL prc_page_result(100, 'id, title, create_time', 'articles', 'status=1', 'id', 1, 'id', 25);

核心原理：Keyset Pagination (Seek Method)

關鍵點：

• 利用覆蓋索引（(status, id) 或 (status, create_time, id)）直接定位，避免回表。
• 游標值取自上一頁最後一條記錄的主鍵，而非計算偏移量。
• 要求排序欄位唯一且不變（通常用主鍵或唯一索引），否則會出現數據跳過/重複。

進階優化與注意事項

1. 索引設計建議

-- 針對 WHERE status=1 ORDER BY id DESC 的最優索引
ALTER TABLE articles ADD INDEX idx_status_id (status, id DESC);  -- MySQL 8.0 支援降序索引
-- 5.7 用 (status, id) 配合 ORDER BY id DESC 仍可用到索引（反向掃描）

2. 動態 SQL 注入防範

存儲過程拼接 SQL 存在注入風險（雖然參數化了 LIMIT 值）。生產環境建議：

• 在應用層驗證 p_tablename、p_columns、p_order_field、p_primary_field 為白名單合法標識符。
• p_condition 建議改為傳遞參數化條件（如 JSON），在存儲過程內構建參數化 WHERE，或改用應用層構建 SQL。

3. 總頁數/總記錄數獲取

分頁組件通常需總數。建議單獨快速查詢：

SELECT COUNT(*) FROM articles WHERE status=1;  -- 有覆蓋索引極快
-- 或使用近似值：EXPLAIN SELECT ... 的 rows 欄位（不準但無鎖）

4. 非唯一排序欄位的處理

若按 create_time 排序（可能重複），游標需改為 (create_time, id) 複合值：

WHERE (create_time, id) > (last_create_time, last_id) ORDER BY create_time, id LIMIT ?

5. 事務隔離與一致性

長事務中分頁查詢可能見到幻讀。建議：

• 只讀事務設 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ（MySQL 預設）。
• 或接受最終一致性，應用層處理邊界情況。

適用場景與替代方案對比

結語

本存儲過程實現了基於主鍵游標的高效分頁核心邏輯，解決了 LIMIT offset 深分頁性能崩塌問題。實際落地時，務必配合覆蓋索引設計、應用層參數白名單驗證、總數快速查詢三大支撐。對於極高併發、極大數據量場景，建議引入專業 OLAP 引擎（ClickHouse、Apache Druid）或搜尋引擎（Elasticsearch）卸載分頁查詢壓力，讓 MySQL 專注事務處理。