mysql分頁(yè)問(wèn)題和優(yōu)化的思路是什么

最近有同學(xué)面試字節(jié)三面的時(shí)候，被問(wèn)到：“MySQL 分頁(yè)有什么性能問(wèn)題？怎么優(yōu)化？”

小林哥，MySQL分頁(yè)怎么優(yōu)化?，面字節(jié)的時(shí)候被問(wèn)到了，一二面考，算法撐過(guò)去了，但是MySQL優(yōu)化的問(wèn)題沒(méi)怎么準(zhǔn)備過(guò)。

今天就主要圍繞這個(gè)問(wèn)題跟大家說(shuō)一下 MySQL 分頁(yè)問(wèn)題和優(yōu)化的思路。

我們刷網(wǎng)站的時(shí)候，我們經(jīng)常會(huì)遇到需要分頁(yè)查詢的場(chǎng)景。

比如下圖紅框里的翻頁(yè)功能。

我們很容易能聯(lián)想到可以用mysql實(shí)現(xiàn)。

假設(shè)我們的建表sql是這樣的

mysql建表sql

建表sql大家也不用扣細(xì)節(jié)，只需要知道id是主鍵，并且在user_name建了個(gè)非主鍵索引就夠了，其他都不重要。

為了實(shí)現(xiàn)分頁(yè)。

很容易聯(lián)想到下面這樣的sql語(yǔ)句。

select?*?from?page?order?by?id?limit?offset,?size;

比如一頁(yè)有10條數(shù)據(jù)。

user表數(shù)據(jù)庫(kù)原始狀態(tài)

第一頁(yè)就是下面這樣的sql語(yǔ)句。

select?*?from?page?order?by?id?limit?0,?10;

第一百頁(yè)就是

select?*?from?page?order?by?id?limit?990,?10;

那么問(wèn)題來(lái)了。

用這種方式，同樣都是拿10條數(shù)據(jù)，查第一頁(yè)和第一百頁(yè)的查詢速度是一樣的嗎？為什么？

兩種limit的執(zhí)行過(guò)程

上面的兩種查詢方式。對(duì)應(yīng) limit offset, size 和 limit size 兩種方式。

而其實(shí) limit size ，相當(dāng)于 ?limit 0, size。也就是從0開(kāi)始取size條數(shù)據(jù)。

也就是說(shuō)，兩種方式的區(qū)別在于offset是否為0。

我們先來(lái)看下limit sql的內(nèi)部執(zhí)行邏輯。

Mysql架構(gòu)

mysql內(nèi)部分為server層和存儲(chǔ)引擎層。一般情況下存儲(chǔ)引擎都用innodb。

server層有很多模塊，其中需要關(guān)注的是執(zhí)行器是用于跟存儲(chǔ)引擎打交道的組件。

執(zhí)行器可以通過(guò)調(diào)用存儲(chǔ)引擎提供的接口，將一行行數(shù)據(jù)取出，當(dāng)這些數(shù)據(jù)完全符合要求（比如滿足其他where條件），則會(huì)放到結(jié)果集中，最后返回給調(diào)用mysql的客戶端（go、java寫(xiě)的應(yīng)用程序）。

我們可以對(duì)下面的sql先執(zhí)行下 explain。

explain?select?*?from?page?order?by?id?limit?0,?10;

可以看到，explain中提示 key 那里，執(zhí)行的是PRIMARY，也就是走的主鍵索引。

分頁(yè)查詢offset=0

主鍵索引本質(zhì)是一棵B+樹(shù)，它是放在innodb中的一個(gè)數(shù)據(jù)結(jié)構(gòu)。

我們可以回憶下，B+樹(shù)大概長(zhǎng)這樣。

B+樹(shù)結(jié)構(gòu)

在這個(gè)樹(shù)狀結(jié)構(gòu)里，我們需要關(guān)注的是，最下面一層節(jié)點(diǎn)，也就是葉子結(jié)點(diǎn)。而這個(gè)葉子結(jié)點(diǎn)里放的信息會(huì)根據(jù)當(dāng)前的索引是主鍵還是非主鍵有所不同。

如果是主鍵索引，它的葉子節(jié)點(diǎn)會(huì)存放完整的行數(shù)據(jù)信息。

如果是非主鍵索引，那它的葉子節(jié)點(diǎn)則會(huì)存放主鍵，如果想獲得行數(shù)據(jù)信息，則需要再跑到主鍵索引去拿一次數(shù)據(jù)，這叫回表。

比如執(zhí)行

select?*?from?page?where?user_name?=?"小白10";

會(huì)通過(guò)非主鍵索引去查詢user_name為"小白10"的數(shù)據(jù)，然后在葉子結(jié)點(diǎn)里找到"小白10"的數(shù)據(jù)對(duì)應(yīng)的主鍵為10。

此時(shí)回表到主鍵索引中做查詢，最后定位到主鍵為10的行數(shù)據(jù)。

回表

但不管是主鍵還是非主鍵索引，他們的葉子結(jié)點(diǎn)數(shù)據(jù)都是有序的。比如在主鍵索引中，這些數(shù)據(jù)是根據(jù)主鍵id的大小，從小到大，進(jìn)行排序的。

基于主鍵索引的limit執(zhí)行過(guò)程

那么回到文章開(kāi)頭的問(wèn)題里。

當(dāng)我們?nèi)サ鬳xplain，執(zhí)行這條sql。

select?*?from?page?order?by?id?limit?0,?10;

上面select后面帶的是星號(hào)，也就是要求獲得行數(shù)據(jù)的所有字段信息。*

server層會(huì)調(diào)用innodb的接口，在innodb里的主鍵索引中獲取到第0到10條完整行數(shù)據(jù)，依次返回給server層，并放到server層的結(jié)果集中，返回給客戶端。

而當(dāng)我們把offset搞離譜點(diǎn)，比如執(zhí)行的是

select?*?from?page?order?by?id?limit?6000000,?10;

server層會(huì)調(diào)用innodb的接口，由于這次的offset=6000000，會(huì)在innodb里的主鍵索引中獲取到第0到（6000000 + 10）條完整行數(shù)據(jù)，返回給server層之后根據(jù)offset的值挨個(gè)拋棄，最后只留下最后面的size條，也就是10條數(shù)據(jù)，放到server層的結(jié)果集中，返回給客戶端。

可以看出，當(dāng)offset非0時(shí)，server層會(huì)從引擎層獲取到很多無(wú)用的數(shù)據(jù)，而獲取的這些無(wú)用數(shù)據(jù)都是要耗時(shí)的。

因此，我們就知道了文章開(kāi)頭的問(wèn)題的答案，mysql查詢中 limit 1000,10 會(huì)比 limit 10 更慢。原因是 limit 1000,10 會(huì)取出1000+10條數(shù)據(jù)，并拋棄前1000條，這部分耗時(shí)更大

那這種case有辦法優(yōu)化嗎？

可以看出，當(dāng)offset非0時(shí)，server層會(huì)從引擎層獲取到很多無(wú)用的數(shù)據(jù)，而當(dāng)select后面是*號(hào)時(shí)，就需要拷貝完整的行信息，拷貝完整數(shù)據(jù)跟只拷貝行數(shù)據(jù)里的其中一兩個(gè)列字段耗時(shí)是不同的，這就讓原本就耗時(shí)的操作變得更加離譜。

因?yàn)榍懊娴膐ffset條數(shù)據(jù)最后都是不要的，就算將完整字段都拷貝來(lái)了又有什么用呢，所以我們可以將sql語(yǔ)句修改成下面這樣。

select?*?from?page??where?id?>=(select?id?from?page??order?by?id?limit?6000000,?1)?order?by?id?limit?10;

上面這條sql語(yǔ)句，里面先執(zhí)行子查詢 select id from page order by id limit 6000000, 1, 這個(gè)操作，其實(shí)也是將在innodb中的主鍵索引中獲取到6000000+1條數(shù)據(jù)，然后server層會(huì)拋棄前6000000條，只保留最后一條數(shù)據(jù)的id。

但不同的地方在于，在返回server層的過(guò)程中，只會(huì)拷貝數(shù)據(jù)行內(nèi)的id這一列，而不會(huì)拷貝數(shù)據(jù)行的所有列，當(dāng)數(shù)據(jù)量較大時(shí)，這部分的耗時(shí)還是比較明顯的。

在拿到了上面的id之后，假設(shè)這個(gè)id正好等于6000000，那sql就變成了

select?*?from?page??where?id?>=(6000000)?order?by?id?limit?10;

這樣innodb再走一次主鍵索引，通過(guò)B+樹(shù)快速定位到id=6000000的行數(shù)據(jù)，時(shí)間復(fù)雜度是lg(n)，然后向后取10條數(shù)據(jù)。

這樣性能確實(shí)是提升了，親測(cè)能快一倍左右，屬于那種耗時(shí)從3s變成1.5s的操作。

這······

屬實(shí)有些杯水車(chē)薪，有點(diǎn)搓，屬于沒(méi)辦法中的辦法。

基于非主鍵索引的limit執(zhí)行過(guò)程

上面提到的是主鍵索引的執(zhí)行過(guò)程，我們?cè)賮?lái)看下基于非主鍵索引的limit執(zhí)行過(guò)程。

比如下面的sql語(yǔ)句

select?*?from?page?order?by?user_name??limit?0,?10;

server層會(huì)調(diào)用innodb的接口，在innodb里的非主鍵索引中獲取到第0條數(shù)據(jù)對(duì)應(yīng)的主鍵id后，回表到主鍵索引中找到對(duì)應(yīng)的完整行數(shù)據(jù)，然后返回給server層，server層將其放到結(jié)果集中，返回給客戶端。

而當(dāng)offset>0時(shí)，且offset的值較小時(shí)，邏輯也類(lèi)似，區(qū)別在于，offset>0時(shí)會(huì)丟棄前面的offset條數(shù)據(jù)。

也就是說(shuō)非主鍵索引的limit過(guò)程，比主鍵索引的limit過(guò)程，多了個(gè)回表的消耗。

但當(dāng)offset變得非常大時(shí)，比如600萬(wàn)，此時(shí)執(zhí)行explain。

非主鍵索引offset值超大時(shí)走全表掃描

可以看到type那一欄顯示的是ALL，也就是全表掃描。

這是因?yàn)閟erver層的優(yōu)化器，會(huì)在執(zhí)行器執(zhí)行sql語(yǔ)句前，判斷下哪種執(zhí)行計(jì)劃的代價(jià)更小。

很明顯，優(yōu)化器在看到非主鍵索引的600w次回表之后，搖了搖頭，還不如全表一條條記錄去判斷算了，于是選擇了全表掃描。

因此，當(dāng)limit offset過(guò)大時(shí)，非主鍵索引查詢非常容易變成全表掃描。是真·性能殺手。

這種情況也能通過(guò)一些方式去優(yōu)化。比如

select?*?from?page?t1,?(select?id?from?page?order?by?user_name?limit?6000000,?100)?t2??WHERE?t1.id?=?t2.id;

通過(guò)select id from page order by user_name limit 6000000, 100。先走innodb層的user_name非主鍵索引取出id，因?yàn)橹荒弥麈Iid，不需要回表，所以這塊性能會(huì)稍微快點(diǎn)，在返回server層之后，同樣拋棄前600w條數(shù)據(jù)，保留最后的100個(gè)id。然后再用這100個(gè)id去跟t1表做id匹配，此時(shí)走的是主鍵索引，將匹配到的100條行數(shù)據(jù)返回。這樣就繞開(kāi)了之前的600w條數(shù)據(jù)的回表。

當(dāng)然，跟上面的case一樣，還是沒(méi)有解決要白拿600w條數(shù)據(jù)然后拋棄的問(wèn)題，這也是非常挫的優(yōu)化。

像這種，當(dāng)offset變得超大時(shí)，比如到了百萬(wàn)千萬(wàn)的量級(jí)，問(wèn)題就突然變得嚴(yán)肅了。

這里就產(chǎn)生了個(gè)專(zhuān)門(mén)的術(shù)語(yǔ)，叫深度分頁(yè)。

深度分頁(yè)問(wèn)題

深度分頁(yè)問(wèn)題，是個(gè)很惡心的問(wèn)題，惡心就惡心在，這個(gè)問(wèn)題，它其實(shí)無(wú)解。

不管你是用mysql還是es，你都只能通過(guò)一些手段去"減緩"問(wèn)題的嚴(yán)重性。

遇到這個(gè)問(wèn)題，我們就該回過(guò)頭來(lái)想想。

為什么我們的代碼會(huì)產(chǎn)生深度分頁(yè)問(wèn)題？

它背后的原始需求是什么，我們可以根據(jù)這個(gè)做一些規(guī)避。

如果你是想取出全表的數(shù)據(jù)

有些需求是這樣的，我們有一張數(shù)據(jù)庫(kù)表，但我們希望將這個(gè)數(shù)據(jù)庫(kù)表里的所有數(shù)據(jù)取出，異構(gòu)到es，或者h(yuǎn)ive里，這時(shí)候如果直接執(zhí)行

select?*?from?page;

這個(gè)sql一執(zhí)行，狗看了都搖頭。

因?yàn)閿?shù)據(jù)量較大，mysql根本沒(méi)辦法一次性獲取到全部數(shù)據(jù)，妥妥超時(shí)報(bào)錯(cuò)。

于是不少mysql小白會(huì)通過(guò)limit offset size分頁(yè)的形式去分批獲取，剛開(kāi)始都是好的，等慢慢地，哪天數(shù)據(jù)表變得奇大無(wú)比，就有可能出現(xiàn)前面提到的深度分頁(yè)問(wèn)題。

這種場(chǎng)景是最好解決的。

我們可以將所有的數(shù)據(jù)根據(jù)id主鍵進(jìn)行排序，然后分批次取，將當(dāng)前批次的最大id作為下次篩選的條件進(jìn)行查詢。

可以看下偽代碼

batch獲取數(shù)據(jù)

這個(gè)操作，可以通過(guò)主鍵索引，每次定位到id在哪，然后往后遍歷100個(gè)數(shù)據(jù)，這樣不管是多少萬(wàn)的數(shù)據(jù)，查詢性能都很穩(wěn)定。

batch分批獲取user表

如果是給用戶做分頁(yè)展示

如果深度分頁(yè)背后的原始需求只是產(chǎn)品經(jīng)理希望做一個(gè)展示頁(yè)的功能，比如商品展示頁(yè)，那么我們就應(yīng)該好好跟產(chǎn)品經(jīng)理battle一下了。

什么樣的翻頁(yè)，需要翻到10多萬(wàn)以后，這明顯是不合理的需求。

是不是可以改一下需求，讓它更接近用戶的使用行為？

比如，我們?cè)谑褂霉雀杷阉鲿r(shí)看到的翻頁(yè)功能。

一般來(lái)說(shuō)，谷歌搜索基本上都在20頁(yè)以內(nèi)，作為一個(gè)用戶，我就很少會(huì)翻到第10頁(yè)之后。

作為參考。

如果我們要做搜索或篩選類(lèi)的頁(yè)面的話，就別用mysql了，用es，并且也需要控制展示的結(jié)果數(shù)，比如一萬(wàn)以內(nèi)，這樣不至于讓分頁(yè)過(guò)深。

如果因?yàn)楦鞣N原因，必須使用mysql。那同樣，也需要控制下返回結(jié)果數(shù)量，比如數(shù)量1k以內(nèi)。

這樣就能勉強(qiáng)支持各種翻頁(yè)，跳頁(yè)（比如突然跳到第6頁(yè)然后再跳到第106頁(yè)）。

但如果能從產(chǎn)品的形式上就做成不支持跳頁(yè)會(huì)更好，比如只支持上一頁(yè)或下一頁(yè)。

上下頁(yè)的形式

這樣我們就可以使用上面提到的start_id方式，采用分批獲取，每批數(shù)據(jù)以start_id為起始位置。這個(gè)解法最大的好處是不管翻到多少頁(yè)，查詢速度永遠(yuǎn)穩(wěn)定。

總結(jié)

limit offset, size 比 limit size 要慢，且offset的值越大，sql的執(zhí)行速度越慢。

當(dāng)offset過(guò)大，會(huì)引發(fā)深度分頁(yè)問(wèn)題，目前不管是mysql還是es都沒(méi)有很好的方法去解決這個(gè)問(wèn)題。只能通過(guò)限制查詢數(shù)量或分批獲取的方式進(jìn)行規(guī)避。

遇到深度分頁(yè)的問(wèn)題，多思考其原始需求，大部分時(shí)候是不應(yīng)該出現(xiàn)深度分頁(yè)的場(chǎng)景的，必要時(shí)多去影響產(chǎn)品經(jīng)理。

如果數(shù)據(jù)量很少，比如1k的量級(jí)，且長(zhǎng)期不太可能有巨大的增長(zhǎng)，還是用limit offset, size 的方案吧，整挺好，能用就行。

編輯：黃飛