萬盛學電腦網

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

MySQL 分頁優化實戰:百萬級資料從 0.15 秒降至 0.003 秒

當單表資料突破百萬筆,傳統 LIMIT offset, size 分頁會陷入效能泥沼。本文以實測數據示範如何利用主鍵索引將深分頁查詢加速 50 倍,並解析優化原理與適用場景。

痛點:傳統分頁為何會慢?

典型分頁語句:

SELECT * FROM user LIMIT 100000, 20;

MySQL 必須掃描前 100,000 筆再丟棄,隨著 offset 增大,掃描行數線性增長。實測百萬筆資料下:

  • 耗時:0.151 秒
  • 掃描行數:16 萬+
  • 未使用任何索引

EXPLAIN 分析傳統分頁執行計畫

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

核心思路:先在索引上快速定位目標主鍵,再回表查試完整欄位。

SELECT * 
FROM user 
WHERE id > (SELECT id FROM user WHERE id >= 100000 LIMIT 1) 
LIMIT 20;

實測效果對比

  • 耗時:0.003 秒(提升 50 倍)
  • 掃描行數:8 萬+(僅掃描索引)
  • 使用到主鍵索引

優化後執行計畫使用主鍵索引

關鍵前提與限制

條件說明
主鍵連續遞增適用於自增 ID 或有序 UUID;若主鍵離散需改用覆蓋索引
無頻繁刪除導致間隙大量刪除會造成「定位 ID 不存在」,需改用 id >= (SELECT id ...)
排序欄位即主鍵若按非主鍵欄位排序,需建立對應的覆蓋索引

進階變體:覆蓋索引優化

若查詢欄位較少,可建立覆蓋索引避免回表:

ALTER TABLE user ADD INDEX idx_name_age (name, age);
SELECT id, name, age 
FROM user 
WHERE id > (SELECT id FROM user WHERE name = '張三' ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

總結:分頁優化決策樹

  1. 資料量 < 10 萬 → 直接用 LIMIT 即可
  2. 深分頁 + 主鍵有序 → 延遲關聯 / 子查詢定位
  3. 非主鍵排序 → 建立覆蓋索引 + 延遲關聯
  4. 極大資料量 + 複雜條件 → 考慮 Elasticsearch 或游標分頁

善用 EXPLAIN 分析執行計畫,才能精準定位瓶頸,對症下藥。

copyright © 萬盛學電腦網 all rights reserved