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

比如下圖紅框里的翻頁功能。

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

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

mysql建表sql

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

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

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

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

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

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

第一頁就是下面這樣的sql語句。

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

第一百頁就是

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

那么問題來了。

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

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

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

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

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

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

Mysql架構(gòu)

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

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

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

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

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

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

分頁查詢offset=0

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

我們可以回憶下，B+樹大概長這樣。

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

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

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

比如執(zhí)行：

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

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

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

回表

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

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

那么回到文章開頭的問題里。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這······

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

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

上面提到的是主鍵索引的執(zhí)行過程，我們再來看下基于非主鍵索引的limit執(zhí)行過程。

比如下面的sql語句:

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

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

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

也就是說非主鍵索引的limit過程，比主鍵索引的limit過程，多了個回表的消耗。

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

非主鍵索引offset值超大時走全表掃描

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

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

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

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

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

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

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

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

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

這里就產(chǎn)生了個專門的術(shù)語，叫深度分頁。

深度分頁問題深度

分頁問題，是個很惡心的問題，惡心就惡心在，這個問題，它其實(shí)無解。

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

遇到這個問題，我們就該回過頭來想想。

為什么我們的代碼會產(chǎn)生深度分頁問題?

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

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

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

select * from page;

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

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

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

這種場景是最好解決的。

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

可以看下偽代碼：

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

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

batch分批獲取user表

如果是給用戶做分頁展示

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

什么樣的翻頁，需要翻到10多萬以后，這明顯是不合理的需求。

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

比如，我們在使用谷歌搜索時看到的翻頁功能。

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

作為參考。

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

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

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

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

上下頁的形式

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

聽起來很挫?

怎么會呢，把這個功能包裝一下。

變成像抖音那樣只能上劃或下劃，專業(yè)點(diǎn)，叫瀑布流。

是不是就不挫了?

總結(jié)

• limit offset, size 比 limit size 要慢，且offset的值越大，sql的執(zhí)行速度越慢。
• 當(dāng)offset過大，會引發(fā)深度分頁問題，目前不管是mysql還是es都沒有很好的方法去解決這個問題。只能通過限制查詢數(shù)量或分批獲取的方式進(jìn)行規(guī)避。
• 遇到深度分頁的問題，多思考其原始需求，大部分時候是不應(yīng)該出現(xiàn)深度分頁的場景的，必要時多去影響產(chǎn)品經(jīng)理。
• 如果數(shù)據(jù)量很少，比如1k的量級，且長期不太可能有巨大的增長，還是用limit offset, size 的方案吧，整挺好，能用就行。

參考資料

《MySQL的Limit子句底層原理你不可不知》https://blog.csdn.net/qq_34115899/article/details/120727513