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

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

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

mysql建表sql

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

為了實(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í)行過程

上面的兩種查詢方式。對(duì)應(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層 和 存儲(chǔ)引擎層 。一般情況下存儲(chǔ)引擎都用innodb。

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

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

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

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

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

分頁查詢offset=0

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

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

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

在這個(gè)樹狀結(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ì)通過非主鍵索引去查詢 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í)行過程

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

當(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ì)從引擎層獲取到 很多無用的數(shù)據(jù) ，而獲取的這些無用數(shù)據(jù)都是要耗時(shí)的。

因此，我們就知道了文章開頭的問題的答案， 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ì)從引擎層獲取到很多無用的數(shù)據(jù)，而當(dāng)select后面是*號(hào)時(shí)，就需要拷貝完整的行信息， 拷貝完整數(shù)據(jù) 跟 只拷貝行數(shù)據(jù)里的其中一兩個(gè)列字段 耗時(shí)是不同的，這就讓原本就耗時(shí)的操作變得更加離譜。

因?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? , 這個(gè)操作，其實(shí)也是將在innodb中的主鍵索引中獲取到 6000000+1 條數(shù)據(jù)，然后server層會(huì)拋棄前6000000條，只保留最后一條數(shù)據(jù)的id。

但不同的地方在于，在返回server層的過程中，只會(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再走一次 主鍵索引 ，通過B+樹快速定位到id=6000000的行數(shù)據(jù)，時(shí)間復(fù)雜度是lg(n)，然后向后取10條數(shù)據(jù)。

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

這······

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

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

上面提到的是主鍵索引的執(zhí)行過程，我們?cè)賮砜聪禄?nbsp;非主鍵索引 的limit執(zhí)行過程。

比如下面的sql語句

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í)，邏輯也類似，區(qū)別在于，offset>0時(shí)會(huì)丟棄前面的offset條數(shù)據(jù)。

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

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

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

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

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

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

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

這種情況也能通過一些方式去優(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， 不需要回表 ，所以這塊性能會(huì)稍微快點(diǎn)，在返回server層之后，同樣拋棄前600w條數(shù)據(jù)，保留最后的100個(gè)id。然后再用這100個(gè)id去跟t1表做id匹配，此時(shí)走的是主鍵索引，將匹配到的100條行數(shù)據(jù)返回。這樣就繞開了之前的600w條數(shù)據(jù)的回表。

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

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

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

深度分頁問題

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

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

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

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

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

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

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

select * from page;

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

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

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

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

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

可以看下偽代碼

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

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

batch分批獲取user表

如果是給用戶做分頁展示

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

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

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

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

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

作為參考。

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

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

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

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

上下頁的形式

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

聽起來很挫？

怎么會(huì)呢，把這個(gè)功能包裝一下。

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

是不是就不挫了？

總結(jié)

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