萬盛學電腦網

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

MySQL LIMIT 大數據分頁優化終極指南:從百萬到千萬級秒級查詢

MySQL LIMIT offset, size 分頁在資料量超過百萬時效能會急劇下降(LIMIT 1000000, 10 需掃描 100 萬行再丟棄)。本文深度解析 LIMIT 效能瓶頸、延遲關聯、覆蓋索引、複合索引、IN 子查詢、遞歸 CTE 等多種優化方案,並提供百萬/千萬級實測數據對比,助你實現秒級分頁。

一、LIMIT 效能瓶頸根因

-- 慢查詢:掃描前 1000010 行,丟棄前 1000000 行
SELECT * FROM big_table LIMIT 1000000, 10;

-- 快查詢:僅掃描 10 行
SELECT * FROM big_table LIMIT 0, 10;

根因: MySQL 無法直接「跳躍」到 offset 位置,必須順序掃描前 offset+size 行,再丟棄前 offset 行。offset 越大,掃描行數越多,I/O 與 CPU 開銷線性增長。

二、優化方案演進對比(實測環境:100 萬行,InnoDB,主鍵 id)

方案SQL 範例10萬行耗時90萬行耗時原理
原生 LIMITSELECT * FROM t LIMIT 900000, 10~0.05s~8-9s全掃描+丟棄
子查詢延遲關聯SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 900000, 1) LIMIT 10~0.006s~0.006s子查詢只取 id(覆蓋索引),主查詢主鍵範圍查詢
複合索引覆蓋INDEX idx_vtype_id (vtype, id)
SELECT id FROM t WHERE vtype=1 ORDER BY id LIMIT 900000, 10
~0.04s~0.04s索引覆蓋 WHERE+ORDER BY,僅掃描索引樹
IN 子查詢兩階段SELECT * FROM t WHERE id IN (SELECT id FROM t WHERE vtype=1 ORDER BY id LIMIT 900000, 10)~0.05s~0.05s第一階段只取 id(覆蓋索引),第二階段主鍵 IN 查詢
遞歸 CTE (MySQL 8.0+)WITH RECURSIVE ...~0.01s~0.01s遞歸定位游標,避免掃描

三、核心優化方案詳細實作

方案 1:延遲關聯 —— 最通用、相容性最好

-- 步驟 1:子查詢只掃描主鍵索引,定位目標 offset 的 id
SELECT id FROM big_table ORDER BY id LIMIT 900000, 1;
-- 假設返回 id = 900000

-- 步驟 2:主查詢用主鍵範圍查詢,極快
SELECT * FROM big_table 
WHERE id >= 900000 
ORDER BY id LIMIT 10;

合併為單條 SQL:

SELECT * FROM big_table 
WHERE id >= (SELECT id FROM big_table ORDER BY id LIMIT 900000, 1)
ORDER BY id LIMIT 10;

適用: 有自增/單調主鍵、無複雜 WHERE 條件。

方案 2:複合索引覆蓋 —— 帶 WHERE 條件的終極方案

場景:SELECT * FROM articles WHERE category_id = 5 ORDER BY id LIMIT 900000, 10

-- 1. 建立複合索引:WHERE 欄位在前,ORDER BY/LIMIT 欄位在後
ALTER TABLE articles ADD INDEX idx_cat_id (category_id, id);

-- 2. 第一階段:只查 id(覆蓋索引,極快)
SELECT id FROM articles WHERE category_id = 5 ORDER BY id LIMIT 900000, 10;
-- 0.04s 返回 10 個 id

-- 3. 第二階段:用 IN 查詢完整欄位(主鍵查詢,極快)
SELECT * FROM articles WHERE id IN (900001, 900002, ..., 900010);

關鍵原則:

  • 複合索引順序:(WHERE 欄位, ORDER BY 欄位, LIMIT 欄位)
  • 第一階段 只 SELECT 索引覆蓋的欄位(如 id),不要 SELECT *,否則觸發回表,效能暴跌。
  • 第二階段用 IN 列表,MySQL 會優化為主鍵批次查找。

方案 3:兩階段 IN 查詢 —— 應用層組裝,框架友好

-- PHP/應用層邏輯
$ids = $db->query("SELECT id FROM articles WHERE category_id = 5 ORDER BY id LIMIT 900000, 10")
          ->fetchAll(PDO::FETCH_COLUMN);  // 只取 id 陣列

$idList = implode(',', $ids);  // "900001,900002,...,900010"

$rows = $db->query("SELECT id, title, content FROM articles WHERE id IN ($idList)")
           ->fetchAll();

優勢: 完全不依賴特定 SQL 技巧,任何 ORM/框架均可實現,效能接近原生 JOIN。

方案 4:MySQL 8.0+ 遞歸 CTE 遊標分頁

WITH RECURSIVE paginate AS (
  -- 錨點:第一頁
  SELECT id FROM big_table ORDER BY id LIMIT 10
  
  UNION ALL
  
  -- 遞歸:從上一頁最大 id 繼續
  SELECT bt.id 
  FROM big_table bt
  JOIN paginate p ON bt.id > (SELECT MAX(id) FROM paginate)
  ORDER BY bt.id LIMIT 10
)
SELECT * FROM big_table WHERE id IN (SELECT id FROM paginate);

適用: 無連續主鍵、需精確游標分頁、避免 OFFSET 計算。

四、百萬/千萬級分頁架構建議

  1. 業務層面: 避免深分頁(如「第 1000 頁」),改用游標分頁(WHERE id > last_id LIMIT 10),體驗更好、效能恆定。
  2. 索引層面: 針對高頻分頁查詢建立複合覆蓋索引,嚴格遵循「WHERE → ORDER BY → SELECT」欄位順序。
  3. 架構層面: 千萬級以上考慮分表/分庫、唯讀副本、Elasticsearch/ClickHouse 承擔查詢。
  4. 緩存層面: 熱門首頁/前幾頁緩存到 Redis,減少 DB 壓力。

五、總結

MySQL LIMIT 深分頁優化核心三招:

  1. 延遲關聯:子查詢只取主鍵 → 主查詢主鍵範圍查找。
  2. 複合覆蓋索引:WHERE+ORDER BY+LIMIT 欄位合一索引,第一階段只取覆蓋欄位。
  3. 兩階段 IN 查詢:應用層組裝 ID 列表 → 主鍵批次查找,框架友好、效能穩定。

結合游標分頁業務設計、適當分表架構,MySQL 輕鬆支撐千萬級秒級分頁。**索引設計是核心,SQL 改寫是手段,業務妥協是終極解。**

copyright © 萬盛學電腦網 all rights reserved