萬盛學電腦網

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

大數據量分頁查詢優化:MySQL 與 SQL Server 方案對比

問題背景

傳統 LIMIT offset, rows(MySQL)或 TOP(SQL Server)在 Offset 極大時效能急劇下降,因資料庫需掃描並捨棄前 N 筆記錄。

MySQL 優化方案:延遲關聯 / 子查詢定位

-- 假設主鍵為 id,需查第 10000 頁、每頁 20 筆
SELECT t.*
FROM `large_table` t
INNER JOIN (
    SELECT id FROM `large_table` ORDER BY id LIMIT 200000, 20
) tmp USING (id);

原理:子查詢僅掃描索引(覆蓋索引)定位主鍵,再回表取完整欄位,避免大量回表開銷。

SQL Server 方案:ROW_NUMBER() 窗口函數(原文範例)

-- 建立測試環境
CREATE DATABASE TestForPaging;
GO
USE TestForPaging;
GO
CREATE TABLE SomeData (
    id INT PRIMARY KEY,
    name VARCHAR(30) NULL,
    description TEXT
);
GO
-- 插入測試資料...
-- 核心分頁存儲過程
CREATE PROCEDURE GetDataPaged
    @startRowIndex INT,
    @maximumRows INT,
    @sort VARCHAR(50)
AS
BEGIN
    IF LEN(@sort) = 0 SET @sort = 'id';
    SELECT dataLevel, name, description FROM (
        SELECT name, description,
               ROW_NUMBER() OVER (ORDER BY 
                   CASE WHEN @sort = 'id' THEN id END DESC,
                   CASE WHEN @sort = 'name' THEN name END
               ) AS dataLevel
        FROM SomeData
    ) AS datawithlevel
    WHERE dataLevel > (@startRowIndex * @maximumRows) 
      AND dataLevel <= ((@startRowIndex + 1) * @maximumRows);
END
GO

注意:原文範例為 SQL Server T-SQL 語法(ROW_NUMBER() OVER、GO 批次分隔符),MySQL 8.0+ 才支援窗口函數,舊版需用變數模擬或採用上述延遲關聯法。

通用最佳實務

  • 確保排序欄位有索引(最好是覆蓋索引)。
  • 避免 SELECT *,僅取必要欄位。
  • 業務允許時,改用「游標式分頁」(WHERE id > last_seen_id ORDER BY id LIMIT 20),效能穩定不隨頁碼遞減。
copyright © 萬盛學電腦網 all rights reserved