您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 分頁存儲過程優化實戰：從 SQL_CALC_FOUND_ROWS 到高效游標分頁的效能躍升

編輯：MySQL 綜合教學

原始分頁存儲過程與效能瓶頸

CREATE PROCEDURE `Query_Pagination`(
    IN _fields VARCHAR(2000), IN _tables TEXT, IN _where VARCHAR(2000),
    IN _orderby VARCHAR(200), IN _pageindex INT, IN _pagesize INT,
    OUT _totalcount INT, OUT _pagecount INT
)
BEGIN
    SET @startrow = _pagesize * (_pageindex - 1);
    SET @strsql = CONCAT(
        'SELECT SQL_CALC_FOUND_ROWS @rowindex:=@rowindex+1 AS rownumber, ',
        _fields, ' FROM ', _tables,
        CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' WHERE ', _where) END,
        CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' ORDER BY ', _orderby) END,
        ' LIMIT ', @startrow, ',', _pagesize
    );
    PREPARE stmt FROM @strsql; EXECUTE stmt;
    SET _totalcount = FOUND_ROWS();
    -- 計算總頁數邏輯...
END

效能災難現場：百萬級數據 16 秒 vs 0.038 秒

EXPLAIN 分析揭示真相：

SQL_CALC_FOUND_ROWS 強制 MySQL 掃描全表所有符合條件的行，再截取 LIMIT 範圍。百萬行數據全表掃描 → 16 秒
移除 SQL_CALC_FOUND_ROWS 後，MySQL 僅掃描 LIMIT 所需行數 → 0.038 秒，效能提升 400 倍

優化版分頁存儲過程：分離計數與數據查詢

CREATE PROCEDURE `Query_Pagination_Optimized`(
    IN _fields VARCHAR(2000), IN _tables TEXT, IN _where VARCHAR(2000),
    IN _orderby VARCHAR(200), IN _pageindex INT, IN _pagesize INT,
    OUT _totalcount INT, OUT _pagecount INT
)
BEGIN
    -- 1. 先單獨查總數（可利用覆蓋索引極速統計）
    SET @count_sql = CONCAT(
        'SELECT COUNT(*) INTO @total FROM ', _tables,
        CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' WHERE ', _where) END
    );
    PREPARE cnt_stmt FROM @count_sql; EXECUTE cnt_stmt; DEALLOCATE PREPARE cnt_stmt;
    SET _totalcount = @total;

    -- 2. 再查當前頁數據（無 SQL_CALC_FOUND_ROWS，走索引下推）
    SET @startrow = _pagesize * (_pageindex - 1);
    SET @data_sql = CONCAT(
        'SELECT ', _fields, ' FROM ', _tables,
        CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' WHERE ', _where) END,
        CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' ORDER BY ', _orderby) END,
        ' LIMIT ', @startrow, ',', _pagesize
    );
    PREPARE data_stmt FROM @data_sql; EXECUTE data_stmt; DEALLOCATE PREPARE data_stmt;

    -- 3. 計算總頁數
    IF (_totalcount <= _pagesize) THEN SET _pagecount = 1;
    ELSEIF (_totalcount % _pagesize > 0) THEN SET _pagecount = _totalcount / _pagesize + 1;
    ELSE SET _pagecount = _totalcount / _pagesize; END IF;
END

深度優化策略對比

策略	適用場景	優點	缺點
分離 COUNT + LIMIT	通用場景，中小數據量	實現簡單，效能穩定	兩次查詢，COUNT 可能仍慢
覆蓋索引 COUNT(*)	WHERE 條件固定，可建組合索引	COUNT 掃描索引樹，極快	需維護專用索引
延遲關聯	大表分頁，JOIN 複雜	先查主鍵 ID，再回表	SQL 複雜度高
游標/Keyset 分頁	APP 無限滾動、深度分頁	無 OFFSET 掃描，效能恆定	不支援跳頁、需唯一排序鍵
Redis 緩存總數	高併發、總數準確性要求低	COUNT 降級為 O(1)	數據一致性延遲

Keyset 分頁（游標分頁）最佳實踐

-- 首頁
SELECT id, title, create_time FROM articles 
WHERE status = 1 ORDER BY create_time DESC, id DESC LIMIT 20;

-- 下一頁（使用上一頁最後一行的值作為游標）
SELECT id, title, create_time FROM articles 
WHERE status = 1 
  AND (create_time < '2024-01-15 10:30:00' OR (create_time = '2024-01-15 10:30:00' AND id < 10050))
ORDER BY create_time DESC, id DESC LIMIT 20;

優勢： 無論翻到第幾頁，查詢時間恆定；避免深分頁 OFFSET 掃描浪費。適合無限滾動、時間線類產品。