分頁方式

MySQL目前常用的分頁方式有兩種：

• 利用limit實(shí)現(xiàn)分頁，語法為“SELECT*FROM 表名 limit 開始記錄數(shù),每頁條數(shù)”；
• 利用主鍵索引實(shí)現(xiàn)分頁，語法為“SELECT*FROM 表名 WHERE 字段名 > (頁數(shù)*10) LIMIT 條數(shù)”

一般使用第一種方式居多，適用于數(shù)據(jù)量不大的場景：

-- 0是開始的記錄數(shù)，10是條數(shù)SELECT * FROM user LIMIT 0,10;

如果換成第二種寫法：

SELECT * FROM user WHERE id > (0*10) LIMIT 10

id是主鍵。如果是第X頁共Y條：(X從0開始計(jì)算)

SELECT * FROM user WHERE id > (X*10) LIMIT Y

當(dāng)然，這種寫法存在一定問題，如果第0頁的id=5的數(shù)據(jù)被刪除了，就會(huì)導(dǎo)致查詢第0頁的數(shù)據(jù)和第1頁的數(shù)據(jù)有重合，第0頁是1-4，6-11(默認(rèn)一頁10條數(shù)據(jù)，因?yàn)閘imit 10，所以會(huì)查詢到id=11)，第二頁就是11-20，可見id=11重合了。

當(dāng)然關(guān)于id不連續(xù)的問題，你可以“邏輯刪除”，增加一個(gè) isdel 的屬性，當(dāng)isdel=1時(shí)表明該數(shù)據(jù)“已經(jīng)刪除”。

但是對于大量數(shù)據(jù)來說，這種問題是可以忽視的。

主要的是，這種大量數(shù)據(jù)的表，是機(jī)會(huì)不會(huì)去對其進(jìn)行刪除甚至修改操作的。

那么為什么大量數(shù)據(jù)使用第二種更合理呢？我們使用MySQL的關(guān)鍵字explain來看一下大概。

EXPLAIN SELECT * FROM test LIMIT 0,10

其他參數(shù)不做說明，就看type:掃描類型

效率從最好到最壞依次是：system > const > eqref > ref > fulltext > refornull > indexmerge > uniquesubquery > indexsubquery > range > index > ALL

可見limit的分頁效果最差。

當(dāng)然，實(shí)際當(dāng)中你可以增加一些查詢參數(shù)，讓查詢不再走全表掃描。

做對比的話就不增加查詢條件了。

EXPLAIN SELECT * FROM test WHERE id > (0*10) LIMIT 10

這個(gè)走的是range  范圍查詢  明顯好多了。

但這實(shí)際上還是通過主鍵的查詢，取巧了而已。并且主鍵有主鍵索引，查詢更快。

一般情況下，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref。

第一種方式和第二種方式當(dāng)數(shù)據(jù)量不多的時(shí)候，是第一種方式占優(yōu)勢，畢竟沒有數(shù)據(jù)重合的問題，并且查詢的速度也沒有明顯差別。但是當(dāng)數(shù)據(jù)量上去了，差別就很明顯了，

SELECT * FROM test LIMIT 0,10

和

SELECT * FROM test LIMIT 100,10

查詢速度是不一樣的 ，查詢了第100頁時(shí)，實(shí)際上前99頁都已經(jīng)被掃描過。

所以第二種的方式更適用于大量數(shù)據(jù)的場景。

那么關(guān)于第一種，再多說一些。你可以通過增加一些查詢參數(shù)去限制type，但是參數(shù)加的不好甚至還不如不加！牽涉到回表問題。

關(guān)于回表：

先了解一些B+樹：

 在這個(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ù)，這叫 回表 。(實(shí)際上查詢了兩次表)

比如執(zhí)行：

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

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

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

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

所以說，如果你加的查詢參數(shù)是無索引，是無序的，那么就是“不如不加”。

那么第一種如何的去優(yōu)化呢？ 

上面select后面帶的是 星號 *，也就是要求獲得行

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

數(shù)據(jù)的 所有字段信息。

我們結(jié)合第二種可以得到一種優(yōu)化，比如執(zhí)行的是：

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

由于這次的offset=6000000，會(huì)在innodb里的主鍵索引中獲取到第0到(6000000 + 10)條 完整行數(shù)據(jù)， 從引擎層獲取到 很多無用的數(shù)據(jù) ，而獲取的這些無用數(shù)據(jù)都是要耗時(shí)的。

當(dāng)select后面是*號時(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í)是提升了，親測能快一倍左右，屬于那種耗時(shí)從3s變成1.5s的操作。

這······

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

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

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

比如下面的sql語句

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

server層會(huì)調(diào)用innodb的接口，在innodb里的非主鍵索引中獲取到第0條數(shù)據(jù)對應(yīng)的主鍵id后， 回表 到主鍵索引中找到對應(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í)，比如到了百萬千萬的量級，問題就突然變得嚴(yán)肅了。

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

總結(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)深度分頁的場景的，必要時(shí)多去影響產(chǎn)品經(jīng)理。

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

最新評論