在企業(yè)級(jí)項(xiàng)目中，深分頁查詢經(jīng)常會(huì)成為性能瓶頸。本篇文章總結(jié)了我在實(shí)踐中優(yōu)化深分頁 SQL 的經(jīng)驗(yàn)，包括 執(zhí)行計(jì)劃分析、索引優(yōu)化、游標(biāo)分頁改寫 等內(nèi)容。

一、問題場景

假設(shè)我們有一張訂單表 orders，包含字段：

id, user_id, status, total_amount, create_time

原始查詢?yōu)椋?/p>

SELECT id, user_id, status, total_amount, create_time
FROM orders
WHERE user_id = 12345
ORDER BY create_time DESC
LIMIT 990, 10;

• 業(yè)務(wù)背景：查詢某用戶最近的訂單，且需要支持分頁。
• 數(shù)據(jù)量假設(shè)：企業(yè)級(jí)通常幾十萬到幾百萬條訂單。

二、原始 SQL 執(zhí)行計(jì)劃分析

使用 EXPLAIN查看原始 SQL：

分析：

• type=ref → 使用了 user_id 索引進(jìn)行精確匹配。
• key=user_id → 索引選擇正確。
• Extra=Using filesort → ORDER BY create_time DESC 未覆蓋索引，需要額外排序。
• 掃描行數(shù) → MySQL 會(huì)掃描前 990 條行再丟棄（LIMIT 偏移量大），深分頁效率低。

? 結(jié)論：單列索引只能加速 WHERE 條件，排序仍需額外操作。

三、復(fù)合索引優(yōu)化

為了提升查詢效率，我們創(chuàng)建復(fù)合索引：

ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time DESC);

• 作用：
  • 覆蓋 WHERE user_id=… 條件。
  • 覆蓋 ORDER BY create_time DESC 條件。
• 優(yōu)化后 EXPLAIN：

分析：

• type=ref → ref → 使用索引范圍掃描，避免全表掃描。
• key_len=8 → 復(fù)合索引長度增加。
• Extra=Using index condition → ICP (Index Condition Pushdown) 優(yōu)化回表行數(shù)。

? 結(jié)論：復(fù)合索引同時(shí)覆蓋 WHERE + ORDER BY，大幅減少掃描行數(shù)和排序成本。

四、游標(biāo)分頁改寫

深分頁仍有偏移量大的問題，可以改寫為游標(biāo)分頁：

SELECT id, user_id, status, total_amount, create_time
FROM orders
WHERE user_id = 12345 AND create_time < '2024-06-01 12:00:00'
ORDER BY create_time DESC
LIMIT 10;

• 優(yōu)勢：
  • 不用 OFFSET，避免掃描前面大量行。
  • 對(duì)大數(shù)據(jù)量分頁性能穩(wěn)定。
• EXPLAIN 輸出：

? 結(jié)論：掃描行數(shù)恒定，排序在索引中完成，性能最佳。

五、實(shí)踐總結(jié)

• 深分頁性能問題：
  • OFFSET 大時(shí)，MySQL 會(huì)掃描并丟棄大量行。
  • ORDER BY 未覆蓋索引 → filesort。
• 優(yōu)化策略：
• 復(fù)合索引覆蓋 WHERE + ORDER BY。
• 游標(biāo)分頁替代大 OFFSET 分頁。
• EXPLAIN 解析技巧：
• type → 盡量是 ref、range 或 const，避免 ALL。
• key / key_len → 關(guān)注索引是否被正確使用。
• Extra → 理解 Using index condition 和 Using filesort。
• 經(jīng)驗(yàn)總結(jié)：
  • 使用 ICP 能減少回表行數(shù)。
  • 游標(biāo)分頁適合大數(shù)據(jù)量分頁查詢。
  • SQL 優(yōu)化不僅是索引，改寫查詢邏輯同樣重要。

到此這篇關(guān)于MySQL 深分頁查詢優(yōu)化實(shí)踐與經(jīng)驗(yàn)分享的文章就介紹到這了,更多相關(guān)mysql深分頁查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論