分頁(yè)場(chǎng)景慢？MySQL的鍋！
時(shí)間：2021-08-19 15:56:36
手機(jī)看文章
掃描二維碼
隨時(shí)隨地手機(jī)看文章
[導(dǎo)讀]從一個(gè)問(wèn)題說(shuō)起在剛工作的時(shí)候，發(fā)現(xiàn)分頁(yè)場(chǎng)景下，當(dāng)offset變大，MySQL處理速度非常慢！具體sql如下：select?*?from?t_record?where?age?>?10?offset?10000?limit?10下表所示為表t_record結(jié)構(gòu)，為了簡(jiǎn)單起見(jiàn)，只列了...
從一個(gè)問(wèn)題說(shuō)起
在剛工作的時(shí)候，發(fā)現(xiàn)分頁(yè)場(chǎng)景下，當(dāng)offset變大，MySQL處理速度非常慢！具體sql如下：
select?*?from?t_record?where?age?>?10?offset?10000?limit?10
| 字段名 | 類(lèi)型 | 描述 |
| id | bigint(20) unsigned | 主鍵id |
| age | int | 年齡 |
其中t_record是要查詢的數(shù)據(jù)表，表中一共有50000條記錄，age字段上有索引，且age>10的記錄有20000條。
這條語(yǔ)句非常慢，基本達(dá)到了秒級(jí)延遲，在第二次請(qǐng)求有緩存之后，才變快。
在數(shù)據(jù)量這么少的情況下，走索引還這么慢，這完全不能接受，我就問(wèn)我導(dǎo)師為什么，他反問(wèn)“索引場(chǎng)景，MySQL中獲得第n大的數(shù)，時(shí)間復(fù)雜度是多少？”
答案的追尋
小白直覺(jué)作答
當(dāng)時(shí)只知道MySQL索引使用的是樹(shù)，瞎猜了個(gè)O(logn)，心想二叉樹(shù)找一個(gè)節(jié)點(diǎn)不就是O(logn)么。自然而然，導(dǎo)師白了一眼，讓我自己去研究。
繼續(xù)解答
想來(lái)想去...只能從底層結(jié)構(gòu)分析了，MySQL的索引是B 樹(shù)。仔細(xì)想一下，就會(huì)發(fā)現(xiàn)通過(guò)索引去找很別扭。因?yàn)槟悴恢狼皀個(gè)數(shù)在其他子樹(shù)的分布情況，也沒(méi)有標(biāo)記讓你能快速選擇去哪個(gè)子樹(shù)尋找，我們無(wú)法利用B 樹(shù)分支過(guò)濾的查找特性。
這下我明白導(dǎo)師的用意了——offset n，就是從第n大的數(shù)開(kāi)始找！第n大的數(shù)沒(méi)法使用樹(shù)分支查找，所以offset，也不能！
回到我們一開(kāi)始的問(wèn)題：
select * from t_record where age > 10 offset 10000 limit 10通過(guò)二級(jí)索引age，我們只能找到對(duì)應(yīng)的起始節(jié)點(diǎn)，但無(wú)法通過(guò)樹(shù)結(jié)構(gòu)過(guò)濾掉10000個(gè)節(jié)點(diǎn)，再獲取10個(gè)節(jié)點(diǎn)，因?yàn)槲覀儫o(wú)法知道某個(gè)子樹(shù)下有多少數(shù)據(jù)，就無(wú)法通過(guò)分支進(jìn)行排除。
那該怎么辦呢？
我們來(lái)仔細(xì)看下B 樹(shù)的結(jié)構(gòu)，它不光有常規(guī)樹(shù)的分支結(jié)構(gòu)，底部還有一個(gè)由葉子節(jié)點(diǎn)組成鏈表。
顯而易見(jiàn)，最方便最快的方式，就是用樹(shù)定位到起始位置，然后直接通過(guò)葉子節(jié)點(diǎn)組成的鏈表，以O(shè)(n)的復(fù)雜度找到第n大的數(shù)據(jù)。
回到我們最初的問(wèn)題，總結(jié)一下：?jiǎn)栴}的本質(zhì)其實(shí)就是讓offset找到第n大的數(shù)，再通過(guò)鏈表遍歷，在數(shù)據(jù)量很大的情況下，確實(shí)會(huì)慢。
但是即使是O(n)，也不至于僅有幾萬(wàn)數(shù)據(jù)就慢得令人發(fā)指。
是不是還有其他影響因素？
系統(tǒng)學(xué)習(xí)
牛牛決定深入研究，帶著問(wèn)題去查找了很多資料。
這里推薦兩本書(shū)，一本《MySQL技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎》，通過(guò)它可以對(duì)InnoDB的底層機(jī)制，如acid、mvcc、索引實(shí)現(xiàn)、文件存儲(chǔ)，有更深的理解。
第二本是《高性能MySQL》，這本書(shū)從使用層面著手，講得比較深入，并提到了很多設(shè)計(jì)和優(yōu)化的思路，對(duì)日常工作和學(xué)習(xí)都有很大的幫助。
兩本書(shū)相結(jié)合，反復(fù)領(lǐng)會(huì)，MySQL就差不多能登堂入室了。
針對(duì)我們的問(wèn)題，這里介紹兩個(gè)相關(guān)的概念：
- 聚簇索引：包含主鍵索引和對(duì)應(yīng)的實(shí)際數(shù)據(jù)，索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)；
- 輔助索引：也叫二級(jí)節(jié)點(diǎn)，其葉子節(jié)點(diǎn)還是索引節(jié)點(diǎn)，并沒(méi)有完整的數(shù)據(jù)，僅包含了索引值本身和主鍵id，用主鍵id反查聚蔟索引才能獲取完整數(shù)據(jù)。
如圖所示，offset會(huì)先從二級(jí)索引的鏈表順序找10000個(gè)節(jié)點(diǎn)。
注意，即使這10000個(gè)節(jié)點(diǎn)會(huì)被扔掉，MySQL也會(huì)通過(guò)二級(jí)索引上的主鍵id，去聚簇索引上查一遍數(shù)據(jù)，這可是10000次隨機(jī)IO，自然慢成哈士奇。
大家讀到這里可能會(huì)提出疑問(wèn)，為什么MySQL會(huì)有這種行為？
這和它的優(yōu)化器有關(guān)系，也算是MySQL的一個(gè)大坑，時(shí)至今日，也沒(méi)有優(yōu)化。
問(wèn)題的解決
針對(duì)分頁(yè)性能問(wèn)題，《高性能MySQL》中提到了兩種方案，讓我們一起來(lái)看看：
方案一：產(chǎn)品上繞過(guò)
根據(jù)業(yè)務(wù)實(shí)際需求，看能否替換為上一頁(yè)、下一頁(yè)的功能，這樣子就可以通過(guò)和上次返回?cái)?shù)據(jù)進(jìn)行比較，搭上樹(shù)分支過(guò)濾的便車(chē)。
特別在ios，android端，以前那種完全的分頁(yè)是不常見(jiàn)的。即轉(zhuǎn)換為如下sql，第一次last_id傳0即可。
select * from t_record where id > last_id limit 10優(yōu)點(diǎn)
1.能利用樹(shù)的分支結(jié)構(gòu)，過(guò)濾掉第n個(gè)數(shù)之前的數(shù)據(jù)集；
2.直接通過(guò)主鍵索引查找，省略了二級(jí)索引查找過(guò)程，性能會(huì)更高。
缺點(diǎn)
1.使用場(chǎng)景其實(shí)是受限制的。比如，如果是針對(duì)age字段有條件判斷，再分頁(yè)，那么使用主鍵id查找就不滿足需求；
2.把主鍵id暴露出去了，這個(gè)本身不應(yīng)該是業(yè)務(wù)層面關(guān)心的字段。
可以看到，該方案在我們的場(chǎng)景中，是不適用的。
因?yàn)槲覀冞€有age做過(guò)濾條件，此時(shí)用大于主鍵id的方式，雖然看起來(lái)變成順序IO了，但由于是根據(jù)主鍵id排列來(lái)尋找，而不是根據(jù)需要的age索引，所以會(huì)導(dǎo)致MySQL去查更多的數(shù)據(jù)。雖然不符合我們案例的需求，但還是來(lái)看看優(yōu)缺點(diǎn)：
方案二：正面剛
這里先介紹一個(gè)概念：
索引覆蓋：當(dāng)輔助索引查詢的數(shù)據(jù)只有主鍵id和輔助索引本身，那么就不必再去查聚簇索引。
思路如下：
select * from t_record id in(select id from t_record where age > 10 offset 10000 limit 10）這句話是說(shuō)，先從條件查詢中，查找數(shù)據(jù)對(duì)應(yīng)的數(shù)據(jù)庫(kù)唯一id值，因?yàn)橹麈I在輔助索引上就有，所以不用回歸到聚簇索引的磁盤(pán)上拉取。
如此以來(lái)，offset部分均不需要去反查聚蔟索引，只有l(wèi)imit出來(lái)的10個(gè)主鍵id會(huì)去查詢聚簇索引，這樣只會(huì)十次隨機(jī)IO。
在業(yè)務(wù)確實(shí)需要用分頁(yè)的情況下，使用該方案可以大幅度提高性能。通常能滿足性能要求。
優(yōu)點(diǎn)
1.維持了分頁(yè)需求，適用所有l(wèi)imit offset場(chǎng)景，大大減少隨機(jī)IO，提高了性能；
2.二級(jí)索引上，只查找id，傳輸?shù)臄?shù)據(jù)包也變小。
缺點(diǎn)
二級(jí)索引上還是會(huì)走下面的鏈表來(lái)遍歷，這部分時(shí)間復(fù)雜度還是O(n)。
方案選型
如果產(chǎn)品本身的需求，是分上下頁(yè)，且沒(méi)用其他過(guò)濾條件，可以用方案一。
方案二更具有普適性，同時(shí)由于合理分表的大小，一般也就500w，二級(jí)索引上O(n)的查找損耗，通常也在可接受范圍。
總結(jié)
從一個(gè)小問(wèn)題，往下深究，不僅可以深入理解這個(gè)問(wèn)題，在面試和工作中大放異彩，同時(shí)在探索的過(guò)程中，自身的知識(shí)儲(chǔ)備也能得到拓展，是技術(shù)的一個(gè)提升捷徑。
下載文檔
