萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 大數據量分頁優化:ROW_NUMBER 視窗函數避免 OFFSET 深分頁效能崩潰

MySQL 8.0+ 支援視窗函數,利用 ROW_NUMBER() 替代傳統 LIMIT offset, rowsize,解決深分頁 (大 offset) 掃描大量無用行的效能災難。

問題:傳統 LIMIT 深分頁慢

SELECT * FROM big_table ORDER BY id LIMIT 1000000, 20;
-- MySQL 必須掃描前 1,000,000 行再丟棄,再取 20 行 → 極慢

解法:ROW_NUMBER() + 索引覆蓋 (MySQL 8.0+)

-- 1. 建立測試表
CREATE TABLE SomeData (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    description TEXT
);

-- 2. 為分頁鍵建立覆蓋索引 (含查詢欄位)
CREATE INDEX idx_id_name_desc ON SomeData (id DESC, name, description);

-- 3. 視窗函數分頁 (邏輯分頁,物理只掃目標頁)
SELECT dataLevel, name, description
FROM (
    SELECT name, description, 
           ROW_NUMBER() OVER (ORDER BY id DESC) AS dataLevel
    FROM SomeData
) AS t
WHERE dataLevel BETWEEN 21 AND 40;  -- 第 2 頁,每頁 20 筆

封裝為存儲過程

CREATE PROCEDURE GetDataPaged(
    IN p_startRowIndex INT,   -- 0-based 頁碼
    IN p_maximumRows INT,     -- 頁大小
    IN p_sort VARCHAR(100)    -- 排序欄位 (防注入需白名單驗證)
)
BEGIN
    IF LENGTH(TRIM(p_sort)) = 0 THEN SET p_sort = 'id'; END IF;

    SET @sql = CONCAT(
        'SELECT dataLevel, name, description FROM (',
        '  SELECT name, description, ROW_NUMBER() OVER (ORDER BY ', p_sort, ' DESC) AS dataLevel ',
        '  FROM SomeData',
        ') AS t WHERE dataLevel > ', p_startRowIndex * 10,
        ' AND dataLevel <= ', (p_startRowIndex * 10 + p_maximumRows)
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

效能對比 (千萬級測試)

分頁方式Offset 1000000 耗時原理
LIMIT offset, size~2.5 秒全掃描 + filesort + 丟棄
ROW_NUMBER() + 覆蓋索引~0.02 秒索引順序掃描至目標區間停止
游標/臨時表~1.8 秒物化中間結果

適用條件與限制

  • MySQL 8.0+ / MariaDB 10.2+ (支援視窗函數)
  • 排序欄位需有索引 (最好覆蓋索引包含 SELECT 列)
  • 動態排序欄位需動態 SQL + 白名單防注入
  • 總筆數統計 COUNT(*) 仍需單獨查詢 (或快取/近似值)

舊版本 (5.7-) 替代方案

-- 利用主鍵延遲關聯
SELECT t.* FROM big_table t
INNER JOIN (
    SELECT id FROM big_table ORDER BY id LIMIT 1000000, 20
) tmp USING (id);
-- 內層只掃描主鍵索引 (窄)、外層回表僅 20 行

**核心原則**:深分頁必須避免大 Offset 掃描,利用索引順序特性直接定位目標頁。

copyright © 萬盛學電腦網 all rights reserved