腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MySQL的幾種分頁(yè)方式，你知道幾種方式

更新時(shí)間：2023年06月16日 00:11:54 作者：碼農(nóng)桃子

這篇文章主要介紹了MySQL的幾種分頁(yè)方式,需要的朋友可以參考下

分頁(yè)方式

MySQL目前常用的分頁(yè)方式有兩種：

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

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

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

如果換成第二種寫法：

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

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

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

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

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

但是對(duì)于大量數(shù)據(jù)來(lái)說(shuō)，這種問(wèn)題是可以忽視的。

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

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

EXPLAIN SELECT * FROM test LIMIT 0,10

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

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

可見(jiàn)limit的分頁(yè)效果最差。

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

做對(duì)比的話就不增加查詢條件了。

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

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

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

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

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

SELECT * FROM test LIMIT 0,10

和

SELECT * FROM test LIMIT 100,10

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

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

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

關(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ì)通過(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)行排序的。

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

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

上面select后面帶的是 星號(hào) *，也就是要求獲得行

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

當(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+樹快速定位到id=6000000的行數(shù)據(jù)，時(shí)間復(fù)雜度是lg(n)，然后向后取10條數(shù)據(jù)。

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

這······

屬實(shí)有些杯水車薪，有點(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í)，邏輯也類似，區(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;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è)專門的術(shù)語(yǔ)，叫深度分頁(yè) 。