得物面試:MySQL 深度分頁如何優(yōu)化?
JavaGuide 官方網(wǎng)站： javaguide.cn
JavaGuide 官方星球 ： JavaGuide知識星球
今天分享的是一位讀者在去年秋招面試得物被提問的一個(gè)問題。
ps：這個(gè)數(shù)據(jù)庫優(yōu)化問題在面試中還是比較常見的， 阿里、騰訊、用友、京東、小紅書等中大廠的面試都問過這個(gè)問題。
深度分頁介紹
查詢偏移量過大的場景我們稱為深度分頁，這會導(dǎo)致查詢性能較低，例如：
# MySQL 在無法利用索引的情況下跳過1000000條記錄后，再獲取10條記錄
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
深度分頁優(yōu)化建議
這里以 MySQL 數(shù)據(jù)庫為例介紹一下如何優(yōu)化深度分頁。
范圍查詢
當(dāng)可以保證 ID 的連續(xù)性時(shí)，根據(jù) ID 范圍進(jìn)行分頁是比較好的解決方案：
# 查詢指定 ID 范圍的數(shù)據(jù)
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以通過記錄上次查詢結(jié)果的最后一條記錄的ID進(jìn)行下一頁的查詢：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10
這種優(yōu)化方式限制比較大，且一般項(xiàng)目的 ID 也沒辦法保證完全連續(xù)。
子查詢
我們先查詢出 limit 第一個(gè)參數(shù)對應(yīng)的主鍵值，再根據(jù)這個(gè)主鍵值再去過濾并 limit，這樣效率會更快一些。
阿里巴巴《Java 開發(fā)手冊》中也有對應(yīng)的描述：
利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景。
# 通過子查詢來獲取 id 的起始值，把 limit 1000000 的條件轉(zhuǎn)移到子查詢
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order limit 1000000, 1) LIMIT 10;
不過，子查詢的結(jié)果會產(chǎn)生一張新表，會影響性能，應(yīng)該盡量避免大量使用子查詢。并且，這種方法只適用于 ID 是正序的。在復(fù)雜分頁場景，往往需要通過過濾條件，篩選到符合條件的 ID，此時(shí)的 ID 是離散且不連續(xù)的。
當(dāng)然，我們也可以利用子查詢先去獲取目標(biāo)分頁的 ID 集合，然后再根據(jù) ID 集合獲取內(nèi)容，但這種寫法非常繁瑣，不如使用 INNER JOIN 延遲關(guān)聯(lián)。
INNER JOIN 延遲關(guān)聯(lián)
延遲關(guān)聯(lián)的優(yōu)化思路，跟子查詢的優(yōu)化思路其實(shí)是一樣的：都是把條件轉(zhuǎn)移到主鍵索引樹，然后減少回表。不同點(diǎn)是，延遲關(guān)聯(lián)使用了 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;
覆蓋索引
索引中已經(jīng)包含了所有需要獲取的字段的查詢方式稱為覆蓋索引。
覆蓋索引的好處：
- 避免 InnoDB 表進(jìn)行索引的二次查詢，也就是回表操作: InnoDB 是以聚集索引的順序來存儲的，對于 InnoDB 來說，二級索引在葉子節(jié)點(diǎn)中所保存的是行的主鍵信息，如果是用二級索引查詢數(shù)據(jù)的話，在查找到相應(yīng)的鍵值后，還要通過主鍵進(jìn)行二次查詢才能獲取我們真實(shí)所需要的數(shù)據(jù)。而在覆蓋索引中，二級索引的鍵值中可以獲取所有的數(shù)據(jù)，避免了對主鍵的二次查詢 ，減少了 IO 操作，提升了查詢效率。
- 可以把隨機(jī) IO 變成順序 IO 加快查詢效率: 由于覆蓋索引是按鍵值的順序存儲的，對于 IO 密集型的范圍查找來說，對比隨機(jī)從磁盤讀取每一行的數(shù)據(jù) IO 要少的多，因此利用覆蓋索引在訪問時(shí)也可以把磁盤的隨機(jī)讀取的 IO 轉(zhuǎn)變成索引查找的順序 IO。
# 如果只需要查詢 id, code, type 這三列，可建立 code 和 type 的覆蓋索引
SELECT id, code, type FROM t_order
ORDER BY code
LIMIT 1000000, 10;
不過，當(dāng)查詢的結(jié)果集占表的總行數(shù)的很大一部分時(shí)，可能就不會走索引了，自動轉(zhuǎn)換為全表掃描。當(dāng)然了，也可以通過 FORCE INDEX 來強(qiáng)制查詢優(yōu)化器走索引，但這種提升效果一般不明顯。
參考
- 聊聊如何解決 MySQL 深分頁問題 - 撿田螺的小男孩：https://juejin.cn/post/7012016858379321358
- 數(shù)據(jù)庫深分頁介紹及優(yōu)化方案 - 京東零售技術(shù)：https://mp.weixin.qq.com/s/ZEwGKvRCyvAgGlmeseAS7g
- MySQL 深分頁優(yōu)化 - 得物技術(shù)：https://juejin.cn/post/6985478936683610149
?? MySQL 面試題推薦 ：
- 為什么要用索引？MySQL 索引底層數(shù)據(jù)結(jié)構(gòu)是什么？
- 為什么 MySQL 不建議使用 NULL 作為列默認(rèn)值？
- MySQl 索引失效的場景有哪些？
- MySQL 支持哪些存儲引擎？MyISAM 和 InnoDB 的區(qū)別是什么？
- MySQL 中有哪些鎖？表級鎖和行級鎖有什么區(qū)別？
- MySQL 事務(wù)的默認(rèn)隔離級別是什么?可以解決幻讀問題么？
?? 優(yōu)質(zhì)合集推薦 ：
點(diǎn)擊下方卡片進(jìn)入公眾號
回復(fù) 「PDF」 即可領(lǐng)取原創(chuàng)PDF技術(shù)面試手冊
回復(fù) 「學(xué)習(xí)路線 」 即可獲取4w+字最新版Java學(xué)習(xí)路線
回復(fù) 「開源」 即可獲取優(yōu)質(zhì)Java開源項(xiàng)目合集
免費(fèi)分享無套路，有幫助點(diǎn)個(gè)贊就好！