問題分析

當對包含50萬條記錄的edu_test表進行分頁查詢時，發現隨著分頁越深入，查詢時間越長：

• limit 0,10：0.05秒
• limit 200000,10：0.14秒
• limit 499000,10：0.21秒

通過EXPLAIN分析發現，limit offset, size會進行全表掃描，掃描后從offset位置開始取size條記錄返回。

優化方案

方案1：通過有序唯一索引縮小掃描范圍

-- 使用主鍵范圍查詢
SELECT * FROM edu_test WHERE id > 499000 ORDER BY id ASC LIMIT 10;
-- 執行時間：0.14秒-- 進一步縮小掃描范圍
SELECT * FROM edu_test WHERE id BETWEEN 499000 AND 499020 ORDER BY id ASC LIMIT 10;
-- 執行時間：0.09秒

優點：利用索引快速定位，減少掃描數據量
前提：必須使用有序且唯一的字段（如自增主鍵）

方案2：子查詢優化

SELECT * FROM edu_test 
WHERE id >= (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 1) 
LIMIT 10;
-- 執行時間：0.16秒

方案3：JOIN查詢優化

SELECT s.* FROM edu_test s
JOIN (SELECT id FROM edu_test ORDER BY id LIMIT 499000, 10) t
ON s.id = t.id;
-- 執行時間：0.16秒

方案2和3的共同優點：

• 先通過索引快速定位到起始ID
• 再通過主鍵精確查詢所需記錄
• 避免了全表掃描

實際業務應用建議

1. 主鍵設計原則：

   • 保持主鍵唯一且有序（推薦自增ID）
   • 避免使用業務邏輯復雜的字符串作為主鍵
   • 考慮熱點業務場景，減少回表操作

2. 復雜場景處理：

   • 對于分布式ID或字符串主鍵，可使用WHERE id LIKE '10289%'先縮小范圍
   • 考慮添加輔助索引優化特定查詢場景

3. 性能權衡：

   • 簡單分頁（前幾頁）：直接使用LIMIT
   • 深度分頁：采用優化方案
   • 考慮使用"上一頁/下一頁"替代具體頁碼跳轉

通過合理的主鍵設計和查詢優化，可顯著提升大數據量下的分頁查詢性能。