萬盛學電腦網

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

MySQL 分頁查詢深度優化:從 LIMIT 深分頁災難到覆蓋索引/延遲關聯/游標分頁實戰

深分頁性能崩塌根因

`SELECT * FROM big_table LIMIT 800000, 20` 需掃描前 800,020 行,**丟棄 80 萬行**,隨偏移量線性增長:

  • 全表掃描 + 文件排序
  • Buffer Pool 污染(大量冷數據擠入)
  • 主從延遲(binlog 巨大)

優化方案對比實測(100 萬測試表)

方案SQL 示例耗時原理
原生深分頁`SELECT * FROM article LIMIT 800000, 20`38.69 s掃描 80 萬行丟棄
只查主鍵列`SELECT id, category_id FROM article LIMIT 800000, 20`0.94 s覆蓋索引,避免回表
子查詢延遲關聯`SELECT id, category_id FROM article WHERE id >= (SELECT id FROM article LIMIT 800000, 1) LIMIT 20`0.36 s子查詢走索引定位起始 ID,主查詢範圍掃描
BETWEEN 範圍查詢`SELECT id, category_id FROM article WHERE id BETWEEN 800000 AND 800020`0.0001 s直接索引範圍掃描,需 ID 連續無洞

四大優化模式詳解

模式一:覆蓋索引(最通用)

-- 只查索引覆蓋的列
SELECT id, category_id FROM article 
WHERE category_id = 5 
ORDER BY id 
LIMIT 800000, 20;
  • 建立 INDEX idx_cat_id (category_id, id)
  • Extra: Using index,無回表
  • 適用:任意偏移量、不要求 ID 連續

模式二:延遲關聯 / 子查詢定位(推薦通用)

SELECT a.* 
FROM article a
INNER JOIN (
    SELECT id FROM article 
    WHERE category_id = 5 
    ORDER BY id 
    LIMIT 800000, 1
) b ON a.id = b.id
LIMIT 20;
  • 子查詢只掃描索引定位起始主鍵
  • 主查詢按主鍵精確回表 20 行
  • 適用:需查完整列、偏移量大、ID 不連續

模式三:游標分頁 / Keyset Pagination(高併發首選)

-- 首頁
SELECT * FROM article WHERE category_id = 5 ORDER BY id LIMIT 20;

-- 翻頁:記住上頁最大 ID
SELECT * FROM article 
WHERE category_id = 5 AND id > 800020  -- last_seen_id
ORDER BY id LIMIT 20;
  • **無 OFFSET,性能恆定**
  • 需前端配合傳遞 `last_id` / `last_cursor`
  • 不支持隨機跳頁,適合「無限滾動/加載更多」

模式四:ID 連續時 BETWEEN 直達(極速但苛刻)

SELECT * FROM article 
WHERE id BETWEEN 800000 AND 800020 
AND category_id = 5;
  • 要求 ID 連續無刪除間隙
  • 配合覆蓋索引極快

COUNT(*) 總數優化(分頁必伴)

方案適用場景精度成本
精確 COUNT(*)小表/實時需求精確全表掃描/索引掃描
EXPLAIN 估算行數大表/近似值可接受估算極低
Redis 緩存總數高併發/準實時近似需維護緩存一致性
單獨統計表極大表/精確需求精確寫入額外開銷
分表分庫單表超 2000 萬精確架構複雜度高

生產環境分頁服務設計模式

// 伪代碼:游標分頁服務
function fetchPage(categoryId, lastId, pageSize) {
    // 1. 參數校驗
    // 2. 緩存熱點首頁
    // 3. 執行 Keyset 分頁 SQL
    // 4. 返回 {data, nextCursor, hasMore}
    // 5. 異步更新總數緩存
}

ORM 框架陷阱提示

Hibernate/MyBatis Plus `PageHelper` 默認生成 `LIMIT offset, size`。**大表分頁務必手寫 SQL 或自定義攔截器改寫為 Keyset/延遲關聯模式**,否則生產事故必發。

總結

MySQL 分頁優化核心:**拒絕大 OFFSET**。優先 **Keyset 分頁(游標法)**,次選 **延遲關聯/子查詢定位**,輔以 **覆蓋索引**。`COUNT(*)` 改用緩存/估算/統計表。ORM 框架深分頁必須介入改寫。**架構層面:單表控制 2000 萬以內,超限分庫分表**。

copyright © 萬盛學電腦網 all rights reserved