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

一、問題場景

假設我們有一張訂單表 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è)務背景：查詢某用戶最近的訂單，且需要支持分頁。
• 數(shù)據量假設：企業(yè)級通常幾十萬到幾百萬條訂單。

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

使用 EXPLAIN查看原始 SQL：

分析：

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

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

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

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

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 → 復合索引長度增加。
• Extra=Using index condition → ICP (Index Condition Pushdown) 優(yōu)化回表行數(shù)。

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

四、游標分頁改寫

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

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，避免掃描前面大量行。
  • 對大數(shù)據量分頁性能穩(wěn)定。
• EXPLAIN 輸出：

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

五、實踐總結

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

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

最新評論