實(shí)戰(zhàn)！聊聊如何解決MySQL深分頁(yè)問(wèn)題
時(shí)間：2021-09-29 13:47:49
手機(jī)看文章
掃描二維碼
隨時(shí)隨地手機(jī)看文章
[導(dǎo)讀]前言我們?nèi)粘Ｗ龇猪?yè)需求時(shí)，一般會(huì)用limit實(shí)現(xiàn)，但是當(dāng)偏移量特別大的時(shí)候，查詢(xún)效率就變得低下。本文將分四個(gè)方案，討論如何優(yōu)化MySQL百萬(wàn)數(shù)據(jù)的深分頁(yè)問(wèn)題，并附上最近優(yōu)化生產(chǎn)慢SQL的實(shí)戰(zhàn)案例。limit深分頁(yè)為什么會(huì)變慢？先看下表結(jié)構(gòu)哈：CREATE?TABLE?account?(??id?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵Id',??name?varchar(255)?DEFAULT?NULL?COMMENT?'賬戶(hù)名',??balance?int(11)?DEFAULT?NULL?COMMENT?'余額',??create_time?...
前言
我們?nèi)粘Ｗ龇猪?yè)需求時(shí)，一般會(huì)用limit實(shí)現(xiàn)，但是當(dāng)偏移量特別大的時(shí)候，查詢(xún)效率就變得低下。本文將分四個(gè)方案，討論如何優(yōu)化MySQL百萬(wàn)數(shù)據(jù)的深分頁(yè)問(wèn)題，并附上最近優(yōu)化生產(chǎn)慢SQL的實(shí)戰(zhàn)案例。
limit深分頁(yè)為什么會(huì)變慢？
先看下表結(jié)構(gòu)哈：CREATE?TABLE?account?(
??id?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵Id',
??name?varchar(255)?DEFAULT?NULL?COMMENT?'賬戶(hù)名',
??balance?int(11)?DEFAULT?NULL?COMMENT?'余額',
??create_time?datetime?NOT?NULL?COMMENT?'創(chuàng)建時(shí)間',
??update_time?datetime?NOT?NULL?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'更新時(shí)間',
??PRIMARY?KEY?(id),
??KEY?idx_name?(name),
??KEY?idx_update_time?(update_time)?//索引
)?ENGINE=InnoDB?AUTO_INCREMENT=1570068?DEFAULT?CHARSET=utf8?ROW_FORMAT=REDUNDANT?COMMENT='賬戶(hù)表';
假設(shè)深分頁(yè)的執(zhí)行SQL如下：select?id,name,balance?from?account?where?update_time>?'2020-09-19'?limit?100000,10;
這個(gè)SQL的執(zhí)行時(shí)間如下：limit 0,10，只需要0.006秒哦- 通過(guò)普通二級(jí)索引樹(shù)idx_update_time，過(guò)濾update_time條件，找到滿(mǎn)足條件的記錄ID。
- 通過(guò)ID，回到主鍵索引樹(shù)，找到滿(mǎn)足記錄的行，然后取出展示的列（回表）
- 掃描滿(mǎn)足條件的100010行，然后扔掉前100000行，返回。
SQL變慢原因有兩個(gè)：- limit語(yǔ)句會(huì)先掃描offset n行，然后再丟棄掉前offset行，返回后n行數(shù)據(jù)。也就是說(shuō)
limit 100000,10，就會(huì)掃描100010行，而limit 0,10，只掃描10行。 limit 100000,10掃描更多的行數(shù)，也意味著回表更多的次數(shù)。
通過(guò)子查詢(xún)優(yōu)化
因?yàn)橐陨系腟QL，回表了100010次，實(shí)際上，我們只需要10條數(shù)據(jù)，也就是我們只需要10次回表其實(shí)就夠了。因此，我們可以通過(guò)減少回表次數(shù)來(lái)優(yōu)化。回顧B 樹(shù)結(jié)構(gòu)
那么，如何減少回表次數(shù)呢？我們先來(lái)復(fù)習(xí)下B 樹(shù)索引結(jié)構(gòu)哈~InnoDB中，索引分主鍵索引（聚簇索引）和二級(jí)索引- 主鍵索引，葉子節(jié)點(diǎn)存放的是整行數(shù)據(jù)
- 二級(jí)索引，葉子節(jié)點(diǎn)存放的是主鍵的值。
把條件轉(zhuǎn)移到主鍵索引樹(shù)
如果我們把查詢(xún)條件，轉(zhuǎn)移回到主鍵索引樹(shù)，那就可以減少回表次數(shù)啦。轉(zhuǎn)移到主鍵索引樹(shù)查詢(xún)的話(huà)，查詢(xún)條件得改為主鍵id了，之前SQL的update_time這些條件咋辦呢？抽到子查詢(xún)那里嘛~子查詢(xún)那里怎么抽的呢？因?yàn)槎?jí)索引葉子節(jié)點(diǎn)是有主鍵ID的，所以我們直接根據(jù)update_time來(lái)查主鍵ID即可，同時(shí)我們把 limit 100000的條件，也轉(zhuǎn)移到子查詢(xún)，完整SQL如下：select?id,name,balance?FROM?account?where?id?>=?(select?a.id?from?account?a?where?a.update_time?>=?'2020-09-19'?limit?100000,?1)?LIMIT?10;寫(xiě)漏了，可以補(bǔ)下時(shí)間條件在外面
查詢(xún)效果一樣的，執(zhí)行時(shí)間只需要0.038秒！由執(zhí)行計(jì)劃得知，子查詢(xún) table a查詢(xún)是用到了idx_update_time索引。首先在索引上拿到了聚集索引的主鍵ID,省去了回表操作，然后第二查詢(xún)直接根據(jù)第一個(gè)查詢(xún)的 ID往后再去查10個(gè)就可以了!INNER JOIN 延遲關(guān)聯(lián)
延遲關(guān)聯(lián)的優(yōu)化思路，跟子查詢(xún)的優(yōu)化思路其實(shí)是一樣的：都是把條件轉(zhuǎn)移到主鍵索引樹(shù)，然后減少回表。不同點(diǎn)是，延遲關(guān)聯(lián)使用了inner join代替子查詢(xún)。優(yōu)化后的SQL如下：SELECT??acct1.id,acct1.name,acct1.balance?FROM?account?acct1?INNER?JOIN?(SELECT?a.id?FROM?account?a?WHERE?a.update_time?>=?'2020-09-19'?ORDER?BY?a.update_time?LIMIT?100000,?10)?AS??acct2?on?acct1.id=?acct2.id;
查詢(xún)效果也是杠桿的，只需要0.034秒idx_update_time二級(jí)索引樹(shù)查詢(xún)到滿(mǎn)足條件的主鍵ID，再與原表通過(guò)主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。標(biāo)簽記錄法
limit 深分頁(yè)問(wèn)題的本質(zhì)原因就是：偏移量（offset）越大，mysql就會(huì)掃描越多的行，然后再拋棄掉。這樣就導(dǎo)致查詢(xún)性能的下降。其實(shí)我們可以采用標(biāo)簽記錄法，就是標(biāo)記一下上次查詢(xún)到哪一條了，下次再來(lái)查的時(shí)候，從該條開(kāi)始往下掃描。就好像看書(shū)一樣，上次看到哪里了，你就折疊一下或者夾個(gè)書(shū)簽，下次來(lái)看的時(shí)候，直接就翻到啦。假設(shè)上一次記錄到100000，則SQL可以修改為：select??id,name,balance?FROM?account?where?id?>?100000?order?by?id?limit?10;
這樣的話(huà)，后面無(wú)論翻多少頁(yè)，性能都會(huì)不錯(cuò)的，因?yàn)槊辛?code style="font-size: 14px;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">id索引。但是這種方式有局限性：需要一種類(lèi)似連續(xù)自增的字段。使用between...and...
很多時(shí)候，可以將limit查詢(xún)轉(zhuǎn)換為已知位置的查詢(xún)，這樣MySQL通過(guò)范圍掃描between...and，就能獲得到對(duì)應(yīng)的結(jié)果。如果知道邊界值為100000，100010后，就可以這樣優(yōu)化：select??id,name,balance?FROM?account?where?id?between?100000?and?100010?order?by?id;
手把手實(shí)戰(zhàn)案例
我們一起來(lái)看一個(gè)實(shí)戰(zhàn)案例哈。假設(shè)現(xiàn)在有表結(jié)構(gòu)如下，并且有200萬(wàn)數(shù)據(jù).CREATE?TABLE?account?(
?id?varchar(32)?COLLATE?utf8_bin?NOT?NULL?COMMENT?'主鍵',
?account_no?varchar(64)?COLLATE?utf8_bin?NOT?NULL?DEFAULT?''?COMMENT?'賬號(hào)'
?amount?decimal(20,2)?DEFAULT?NULL?COMMENT?'金額'
?type?varchar(10)?COLLATE?utf8_bin?DEFAULT?NULL?COMMENT?'類(lèi)型A，B'
?create_time?datetime?DEFAULT?NULL?COMMENT?'創(chuàng)建時(shí)間',
?update_time?datetime?DEFAULT?NULL?COMMENT?'更新時(shí)間',
?PRIMARY?KEY?(id),
?KEY?`idx_account_no`?(account_no),
?KEY?`idx_create_time`?(create_time)
?)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COLLATE=utf8_bin?COMMENT='賬戶(hù)表'?
業(yè)務(wù)需求是這樣：獲取最2021年的A類(lèi)型賬戶(hù)數(shù)據(jù)，上報(bào)到大數(shù)據(jù)平臺(tái)。一般思路的實(shí)現(xiàn)方式
很多伙伴接到這么一個(gè)需求，會(huì)直接這么實(shí)現(xiàn)了：//查詢(xún)上報(bào)總數(shù)量
Integer?total?=?accountDAO.countAccount();
//查詢(xún)上報(bào)總數(shù)量對(duì)應(yīng)的SQL
'countAccount'?resultType="java.lang.Integer">
??seelct?count(1)?
??from?account
??where?create_time?>='2021-01-01?00:00:00'
??and??type?='A'
//計(jì)算頁(yè)數(shù)
int?pageNo?=?total?%?pageSize?==?0???total?/?pageSize?:?(total?/?pageSize? ?1);
//分頁(yè)查詢(xún)，上報(bào)
for(int?i?=?0;?i??List?list?=?accountDAO.listAccountByPage(startRow,pageSize);
?startRow?=?(pageNo-1)*pageSize;
?//上報(bào)大數(shù)據(jù)
?postBigData(list);
}
?
//分頁(yè)查詢(xún)SQL（可能存在limit深分頁(yè)問(wèn)題，因?yàn)閍ccount表數(shù)據(jù)量幾百萬(wàn)）
'listAccountByPage'?>
??seelct?*?
??from?account
??where?create_time?>='2021-01-01?00:00:00'
??and??type?='A'
??limit?#{startRow},#{pageSize}
實(shí)戰(zhàn)優(yōu)化方案
以上的實(shí)現(xiàn)方案，會(huì)存在limit深分頁(yè)問(wèn)題，因?yàn)閍ccount表數(shù)據(jù)量幾百萬(wàn)。那怎么優(yōu)化呢？其實(shí)可以使用標(biāo)簽記錄法，有些伙伴可能會(huì)有疑惑，id主鍵不是連續(xù)的呀，真的可以使用標(biāo)簽記錄？當(dāng)然可以，id不是連續(xù)，我們可以通過(guò)order by讓它連續(xù)嘛。優(yōu)化方案如下：//查詢(xún)最小ID
String??lastId?=?accountDAO.queryMinId();
//查詢(xún)最小ID對(duì)應(yīng)的SQL
"queryMinId"?returnType=“java.lang.String”>
select?MIN(id)?
from?account
where?create_time?>='2021-01-01?00:00:00'
and?type?='A'
//一頁(yè)的條數(shù)
Integer?pageSize?=?100;
List?list?;
do{
???list?=?listAccountByPage(lastId,pageSize);
???//標(biāo)簽記錄法，記錄上次查詢(xún)過(guò)的Id
???lastId?=?list.get(list,size()-1).getId();
????//上報(bào)大數(shù)據(jù)
????postBigData(list);
}while(CollectionUtils.isNotEmpty(list));
"listAccountByPage">
??select?*?
??from?account?
??where?create_time?>='2021-01-01?00:00:00'
??and?id?>?#{lastId}
??and?type?='A'
??order?by?id?asc??
??limit?#{pageSize}
下載文檔
