limit深分頁為什么會變慢？

例如：一條sql：

select id,name,age, from user where age >10 limit (10000,10);

首先這條sql肯定是比較慢的，因為它經(jīng)過了很多次的回表。

sql執(zhí)行流程：

1：先通過普通索引age，過濾掉age條件，找到符合條件的這10010條記錄的id

2：通過這10010條記錄的id找到id的葉子結(jié)點，取出對應(yīng)的值（回表）

3：丟棄前10000條數(shù)據(jù)，留下最后10條，返回。

sql變慢的原因：

1. limit語句會先掃描offset+n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說limit 100000,10，就會掃描100010行，而limit 0,10，只掃描10行。
2. limit 100000,10 掃描更多的行數(shù)，也意味著回表更多的次數(shù)。

解決方案一：通過子查詢優(yōu)化

因為上面的sql，回表了100010次，但其實回表10次就夠了，所以說只需要減少回表次數(shù)就夠了。

到這個時候有些同學(xué)可能對于回表這個概念并不是很清楚，簡單來說：從二級索引的葉子結(jié)點上只能查詢到當(dāng)前索引字段和id字段，然后根據(jù)查詢到的id字段再去主鍵索引中查詢（因為主鍵索引的葉子結(jié)點中存放的是整行數(shù)據(jù)）

那么看到這可能很多人就明白了，只需要將條件轉(zhuǎn)移到主鍵索引樹上就能夠減少回表次數(shù)了。

所以我們只需要寫一個子查詢將id查詢出來，然后將這個id作為主查詢的where條件就ok了。

注意：子查詢也不能過多的回表。

select id,sales_order_code,lift_code from `or_lift_item` where id > (select id FROM or_lift_item WHERE create_time> '2023-06-03 10:25:51' limit 50000,1) LIMIT 10

這個查詢效果是一樣的，但是回表次數(shù)卻減少到了10條，查詢效率大大提高。

解決方案二： INNER JOIN 延遲關(guān)聯(lián)

延遲關(guān)聯(lián)的優(yōu)化思路，跟子查詢的優(yōu)化思路其實是一樣的：都是把條件轉(zhuǎn)移到主鍵索引樹，然后減少回表。不同點是，延遲關(guān)聯(lián)使用了inner join代替子查詢。

優(yōu)化后的SQL如下：

select abc.id,abc.sales_order_code,abc.lift_code from `or_lift_item` abc JOIN (select a.id FROM or_lift_item a WHERE a.create_time> '2023-06-03 10:25:51' ORDER BY a.create_time limit 40000,10) tbs ON abc.id = tbs.id

解決方案三： 標(biāo)簽記錄法

limit 深分頁問題的本質(zhì)原因就是：偏移量（offset）越大，mysql就會掃描越多的行，然后再拋棄掉。這樣就導(dǎo)致查詢性能的下降。

其實我們可以采用標(biāo)簽記錄法，就是標(biāo)記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪里了，你就折疊一下或者夾個書簽，下次來看的時候，直接就翻到啦。

假設(shè)上一次記錄到100000，則SQL可以修改為：

select id,sales_order_code,lift_code from `or_lift_item` where id > 100000 ORDER BY id LIMIT 10

到此這篇關(guān)于mysql深度分頁的問題解決的文章就介紹到這了,更多相關(guān)mysql 深度分頁內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論