MySQL 單表百萬資料分頁效能優化實測

MySQL 單表百萬資料分頁效能優化實測

測試環境

• 資料表：product (866,633 筆)
• 平均行長：5,133 bytes (~5KB)
• 表總大小：~4.1 GB
• 欄位類型：varchar、datetime、text 等，id 為主鍵

1. 直接 LIMIT 分頁效能實測

SELECT * FROM product LIMIT 10, 20;      -- 0.016 秒
SELECT * FROM product LIMIT 100, 20;     -- 0.016 秒
SELECT * FROM product LIMIT 1000, 20;    -- 0.047 秒
SELECT * FROM product LIMIT 10000, 20;   -- 0.094 秒
SELECT * FROM product LIMIT 400000, 20;  -- 3.229 秒
SELECT * FROM product LIMIT 866613, 20;  -- 37.44 秒 (最後一頁)

結論：查詢時間與起始位置成正比，深分頁效能不可接受。

2. 覆蓋索引優化驗證

僅查詢主鍵 id (覆蓋索引)：

SELECT id FROM product LIMIT 866613, 20;  -- 0.2 秒 (提升 100+ 倍)

3. 兩種完整欄位查詢優化寫法

寫法 A：子查詢定位 ID + 主查詢

SELECT * FROM product
WHERE id >= (SELECT id FROM product LIMIT 866613, 1)
LIMIT 20;  -- 0.2 秒

寫法 B：JOIN 衍生表

SELECT * FROM product a
JOIN (SELECT id FROM product LIMIT 866613, 20) b
ON a.id = b.id;  -- 0.2 秒

4. 原理分析

• 覆蓋索引查詢僅掃描索引樹，極快
• 子查詢/JOIN 先在索引上定位目標 ID 集合，再以主鍵精確回表
• 避免了全表掃描與大量隨機 I/O

實測證明：百萬級資料表深分頁，務必使用覆蓋索引定位主鍵再回表，可將查詢時間從秒級降至毫秒級。