得物面試：MySQL 深度分頁如何最佳化？

深度分頁介紹

查詢偏移量過大的場景我們稱為深度分頁，這會導致查詢效能較低，例如：

# MySQL 在無法利用索引的情況下跳過1000000條記錄後，再獲取10條記錄
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

深度分頁最佳化建議

這裡以 MySQL 資料庫為例介紹一下如何最佳化深度分頁。

範圍查詢

當可以保證 ID 的連續性時，根據 ID 範圍進行分頁是比較好的解決方案：

# 查詢指定 ID 範圍的資料
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以透過記錄上次查詢結果的最後一條記錄的ID進行下一頁的查詢：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10

這種最佳化方式限制比較大，且一般專案的 ID 也沒辦法保證完全連續。

子查詢

我們先查詢出 limit 第一個引數對應的主鍵值，再根據這個主鍵值再去過濾並 limit，這樣效率會更快一些。

阿里巴巴《Java 開發手冊》中也有對應的描述：

利用延遲關聯或者子查詢最佳化超多分頁場景。

# 透過子查詢來獲取 id 的起始值，把 limit 1000000 的條件轉移到子查詢
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;

不過，子查詢的結果會產生一張新表，會影響效能，應該儘量避免大量使用子查詢。並且，這種方法只適用於 ID 是正序的。在複雜分頁場景，往往需要透過過濾條件，篩選到符合條件的 ID，此時的 ID 是離散且不連續的。

當然，我們也可以利用子查詢先去獲取目標分頁的 ID 集合，然後再根據 ID 集合獲取內容，但這種寫法非常繁瑣，不如使用 INNER JOIN 延遲關聯。

INNER JOIN 延遲關聯

延遲關聯的最佳化思路，跟子查詢的最佳化思路其實是一樣的：都是把條件轉移到主鍵索引樹，然後減少回表。不同點是，延遲關聯使用了 INNER JOIN 代替子查詢。

SELECT t1.* FROM t_order t1
INNER JOIN (SELECT id FROM t_order limit 1000000, 1) t2
ON t1.id >= t2.id
LIMIT 10;

覆蓋索引

索引中已經包含了所有需要獲取的欄位的查詢方式稱為覆蓋索引。

覆蓋索引的好處：

• 避免 InnoDB 表進行索引的二次查詢，也就是回表操作: InnoDB 是以聚集索引的順序來儲存的，對於 InnoDB 來說，二級索引在葉子節點中所儲存的是行的主鍵資訊，如果是用二級索引查詢資料的話，在查詢到相應的鍵值後，還要透過主鍵進行二次查詢才能獲取我們真實所需要的資料。而在覆蓋索引中，二級索引的鍵值中可以獲取所有的資料，避免了對主鍵的二次查詢 ，減少了 IO 操作，提升了查詢效率。
• 可以把隨機 IO 變成順序 IO 加快查詢效率: 由於覆蓋索引是按鍵值的順序儲存的，對於 IO 密集型的範圍查詢來說，對比隨機從磁碟讀取每一行的資料 IO 要少的多，因此利用覆蓋索引在訪問時也可以把磁碟的隨機讀取的 IO 轉變成索引查詢的順序 IO。

# 如果只需要查詢 id, code, type 這三列，可建立 code 和 type 的覆蓋索引
SELECT id, code, type FROM t_order
ORDER BY code
LIMIT 1000000, 10;

不過，當查詢的結果集佔表的總行數的很大一部分時，可能就不會走索引了，自動轉換為全表掃描。當然了，也可以透過 FORCE INDEX 來強制查詢最佳化器走索引，但這種提升效果一般不明顯。

參考

• 聊聊如何解決 MySQL 深分頁問題 - 撿田螺的小男孩：https://juejin.cn/post/7012016858379321358
• 資料庫深分頁介紹及最佳化方案 - 京東零售技術：https://mp.weixin.qq.com/s/ZEwGKvRCyvAgGlmeseAS7g
• MySQL 深分頁最佳化 - 得物技術：