在日常測試工作中，我們經常會遇到分頁查詢接口，例如：
GET /product/search?keyword=&pageNum=1&pageSize=10
乍看之下，這樣的分頁接口似乎并無性能問題，響應時間也很快。但在一次性能壓測中，我們復現了一個典型的深分頁性能瓶頸，并深入分析了其成因與優化思路，本文記錄該過程與結論。
📌 壓測背景
接口路徑：/product/search
功能描述：根據關鍵字模糊查詢商品列表，支持分頁（pageNum, pageSize）。
? 數據規模
為了模擬真實生產場景，我們使用以下 SQL 批量造數，構造了 100萬+商品數據（pms_product 表）：
SET @max_id := (SELECT IFNULL(MAX(id), 0) FROM pms_product);
SET @row := 0;INSERT INTO pms_product (id, brand_id, product_category_id, name, sub_title, price,publish_status, verify_status, sort, description, delete_status,new_status, recommand_status, sale, stock, low_stock, unit, weight,preview_status, service_ids, keywords, note, product_sn
)
SELECT @max_id + seq AS id,FLOOR(1 + RAND() * 10),FLOOR(1 + RAND() * 10),CONCAT('商品-', @max_id + seq),'',ROUND(RAND() * 1000, 2),1, 1, @max_id + seq, '', 0, 1, 1, 100, 100, 10, '', 1.5, 1,'1,2,3', '', '', CONCAT('SN', LPAD(@max_id + seq, 6, '0'))
FROM (SELECT @row := @row + 1 AS seqFROM (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4,(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t5,(SELECT @row := 0) rLIMIT 100000
) temp;數據字段包含多個維度如：商品分類、品牌、價格、上下架狀態、是否刪除等。
🔍 性能壓測結果對比
我們使用 JMeter 對分頁接口進行了壓測，以下是對比結果：
正常分頁壓測結果：
深分頁壓測結果：
🚩 分頁頁碼：pageNum=1（正常分頁）
| 樣本數 | 平均響應時間 | 最大響應時間 | 吞吐量 (TPS) | 平均返回字節數 |
|---|---|---|---|---|
| 10 | 2263ms | 2419ms | 3.2/sec | 9317 字節 |
🚩 分頁頁碼：pageNum=100000（深分頁）
| 樣本數 | 平均響應時間 | 最大響應時間 | 吞吐量 (TPS) | 平均返回字節數 |
|---|---|---|---|---|
| 10 | 3245ms | 3653ms | 2.4/sec | 4224 字節 |
🧠 為什么深分頁會變慢？
? 1. Offset 越大，代價越高
分頁底層使用 LIMIT offset, size，如：
1）深分頁執行的sql:
SELECT * FROM pms_product
WHERE delete_status = 0 AND publish_status = 1
ORDER BY id
LIMIT 99990, 10;
該查詢需要：
- 遍歷前 100000 行（offset），丟棄
- 返回最后 10 行
即使加了索引，MySQL 也必須掃描 offset + limit 條數據后再丟棄前面。
2）正常分頁執行的sql:
SELECT count(0) FROM pms_product WHERE delete_status = 0 AND publish_status = 1;
SELECT ... FROM pms_product WHERE delete_status = 0 AND publish_status = 1 LIMIT 10;
? 結論：
- LIMIT 10 是在數據前面截取的，性能還行，Rows_examined掃描了28行；
- count(0) 已掃描 100w 行（較慢）；
? 2. explain 顯示沒有使用覆蓋索引
我們對深分頁 SQL 執行了 EXPLAIN 分析：
EXPLAIN SELECT * FROM pms_product
WHERE delete_status = 0 AND publish_status = 1
ORDER BY id LIMIT 99990, 10;
? 問題分析：
| 字段 | 說明 |
|---|---|
| type=index | 說明是走了索引，但是全索引掃描（index scan），相當于掃描整張表的索引部分。 |
| key=PRIMARY | 表示使用的是主鍵索引（id）。 |
| rows=100010 | MySQL 預估會掃描大約 10 萬行來定位 LIMIT 起始位置。 |
| Extra=Using where | 表示 WHERE 條件在過濾過程中才判斷，并沒有用到復合索引來提前過濾。 |
?? 這意味著：
- LIMIT 100000, 10 會導致 MySQL 掃描超過 10 萬條記錄，性能非常差。
- WHERE 條件沒有使用到合適的索引（possible_keys 為 NULL）。
? 性能優化建議
1. 避免深分頁 —— 改用“基于游標”方式
例如前端傳入上一次返回結果的 last_id，實現類似“加載更多”：
SELECT * FROM pms_product
WHERE delete_status = 0 AND publish_status = 1 AND id > 上一次最大 id
ORDER BY id
LIMIT 10;
優點：
- 避免 offset，性能線性增長
- 可以用覆蓋索引，避免回表
- 建立合理的聯合索引
如分頁條件為：
WHERE delete_status = 0 AND publish_status = 1 ORDER BY id
建議加：
CREATE INDEX idx_status_id
ON pms_product(delete_status, publish_status, id);
這樣可以走索引，減少掃描行數。
3. 考慮分頁緩存
如果某些頁經常訪問，可以考慮將分頁結果緩存到 Redis，提升響應速度。
📝 總結
深分頁是一種常見但代價昂貴的分頁方式，特別在數據量大、頁碼大的時候：
- offset 會嚴重拖慢查詢
- 即使不使用 count()，深分頁依然很慢
- 優化建議包括：改游標分頁、加索引、用緩存等
