如何優(yōu)化MySQL百萬數(shù)據(jù)的深分頁問題

前言

我們?nèi)粘Ｗ龇猪撔枨髸r(shí)，一般會(huì)用limit實(shí)現(xiàn)，但是當(dāng)偏移量特別大的時(shí)候，查詢效率就變得低下。本文將分四個(gè)方案，討論如何優(yōu)化MySQL百萬數(shù)據(jù)的深分頁問題，并附上最近優(yōu)化生產(chǎn)慢SQL的實(shí)戰(zhàn)案例。

limit深分頁為什么會(huì)變慢？

先看下表結(jié)構(gòu)哈：

CREATETABLEaccount(
idint(11)NOTNULLAUTO_INCREMENTCOMMENT'主鍵Id',
namevarchar(255)DEFAULTNULLCOMMENT'賬戶名',
balanceint(11)DEFAULTNULLCOMMENT'余額',
create_timedatetimeNOTNULLCOMMENT'創(chuàng)建時(shí)間',
update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新時(shí)間',
PRIMARYKEY(id),
KEYidx_name(name),
KEYidx_update_time(update_time)//索引
)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='賬戶表';

假設(shè)深分頁的執(zhí)行SQL如下：

selectid,name,balancefromaccountwhereupdate_time>'2020-09-19'limit100000,10;

這個(gè)SQL的執(zhí)行時(shí)間如下：

執(zhí)行完需要0.742秒，深分頁為什么會(huì)變慢呢？如果換成 limit 0,10，只需要0.006秒哦

我們先來看下這個(gè)SQL的執(zhí)行流程：

1. 通過普通二級(jí)索引樹idx_update_time，過濾update_time條件，找到滿足條件的記錄ID。
2. 通過ID，回到主鍵索引樹，找到滿足記錄的行，然后取出展示的列（回表）
3. 掃描滿足條件的100010行，然后扔掉前100000行，返回。

執(zhí)行計(jì)劃如下：

SQL變慢原因有兩個(gè)：

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

通過子查詢優(yōu)化

因?yàn)橐陨系腟QL，回表了100010次，實(shí)際上，我們只需要10條數(shù)據(jù)，也就是我們只需要10次回表其實(shí)就夠了。因此，我們可以通過減少回表次數(shù)來優(yōu)化。

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

那么，如何減少回表次數(shù)呢？我們先來復(fù)習(xí)下B+樹索引結(jié)構(gòu)哈~

InnoDB中，索引分主鍵索引（聚簇索引）和二級(jí)索引

• 主鍵索引，葉子節(jié)點(diǎn)存放的是整行數(shù)據(jù)
• 二級(jí)索引，葉子節(jié)點(diǎn)存放的是主鍵的值。

把條件轉(zhuǎn)移到主鍵索引樹

如果我們把查詢條件，轉(zhuǎn)移回到主鍵索引樹，那就可以減少回表次數(shù)啦。轉(zhuǎn)移到主鍵索引樹查詢的話，查詢條件得改為主鍵id了，之前SQL的update_time這些條件咋辦呢？抽到子查詢那里嘛~

子查詢那里怎么抽的呢？因?yàn)槎?jí)索引葉子節(jié)點(diǎn)是有主鍵ID的，所以我們直接根據(jù)update_time來查主鍵ID即可，同時(shí)我們把 limit 100000的條件，也轉(zhuǎn)移到子查詢，完整SQL如下：

selectid,name,balanceFROMaccountwhereid>=(selecta.idfromaccountawherea.update_time>='2020-09-19'limit100000,1)LIMIT10;寫漏了，可以補(bǔ)下時(shí)間條件在外面

查詢效果一樣的，執(zhí)行時(shí)間只需要0.038秒！

我們來看下執(zhí)行計(jì)劃

由執(zhí)行計(jì)劃得知，子查詢 table a查詢是用到了idx_update_time索引。首先在索引上拿到了聚集索引的主鍵ID,省去了回表操作，然后第二查詢直接根據(jù)第一個(gè)查詢的 ID往后再去查10個(gè)就可以了!

因此，這個(gè)方案是可以的~

INNER JOIN 延遲關(guān)聯(lián)

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

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

SELECTacct1.id,acct1.name,acct1.balanceFROMaccountacct1INNERJOIN(SELECTa.idFROMaccountaWHEREa.update_time>='2020-09-19'ORDERBYa.update_timeLIMIT100000,10)ASacct2onacct1.id=acct2.id;

查詢效果也是杠桿的，只需要0.034秒

執(zhí)行計(jì)劃如下：

查詢思路就是，先通過idx_update_time二級(jí)索引樹查詢到滿足條件的主鍵ID，再與原表通過主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了，同時(shí)也減少了回表。

標(biāo)簽記錄法

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

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

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

selectid,name,balanceFROMaccountwhereid>100000orderbyidlimit10;

這樣的話，后面無論翻多少頁，性能都會(huì)不錯(cuò)的，因?yàn)槊辛?code style="font-size:14px;padding:2px 4px;margin-right:2px;margin-left:2px;background-color:rgba(27,31,35,.05);font-family:'Operator Mono', Consolas, Monaco, Menlo, monospace;color:rgb(239,112,96);">id索引。但是這種方式有局限性：需要一種類似連續(xù)自增的字段。

使用between...and...

很多時(shí)候，可以將limit查詢轉(zhuǎn)換為已知位置的查詢，這樣MySQL通過范圍掃描between...and，就能獲得到對(duì)應(yīng)的結(jié)果。

如果知道邊界值為100000，100010后，就可以這樣優(yōu)化：

selectid,name,balanceFROMaccountwhereidbetween100000and100010orderbyid;

手把手實(shí)戰(zhàn)案例

我們一起來看一個(gè)實(shí)戰(zhàn)案例哈。假設(shè)現(xiàn)在有表結(jié)構(gòu)如下，并且有200萬數(shù)據(jù).

CREATETABLEaccount(
idvarchar(32)COLLATEutf8_binNOTNULLCOMMENT'主鍵',
account_novarchar(64)COLLATEutf8_binNOTNULLDEFAULT''COMMENT'賬號(hào)'
amountdecimal(20,2)DEFAULTNULLCOMMENT'金額'
typevarchar(10)COLLATEutf8_binDEFAULTNULLCOMMENT'類型A，B'
create_timedatetimeDEFAULTNULLCOMMENT'創(chuàng)建時(shí)間',
update_timedatetimeDEFAULTNULLCOMMENT'更新時(shí)間',
PRIMARYKEY(id),
KEY`idx_account_no`(account_no),
KEY`idx_create_time`(create_time)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='賬戶表'

業(yè)務(wù)需求是這樣：獲取最2021年的A類型賬戶數(shù)據(jù)，上報(bào)到大數(shù)據(jù)平臺(tái)。

一般思路的實(shí)現(xiàn)方式

很多伙伴接到這么一個(gè)需求，會(huì)直接這么實(shí)現(xiàn)了：

//查詢上報(bào)總數(shù)量
Integertotal=accountDAO.countAccount();

//查詢上報(bào)總數(shù)量對(duì)應(yīng)的SQL
'countAccount'resultType="java.lang.Integer">
seelctcount(1)
fromaccount
wherecreate_time>='2021-01-010000'
andtype='A'


//計(jì)算頁數(shù)
intpageNo=total%pageSize==0?total/pageSize:(total/pageSize+1);

//分頁查詢，上報(bào)
for(inti=0;ilist=accountDAO.listAccountByPage(startRow,pageSize);
startRow=(pageNo-1)*pageSize;
//上報(bào)大數(shù)據(jù)
postBigData(list);
}

//分頁查詢SQL（可能存在limit深分頁問題，因?yàn)閍ccount表數(shù)據(jù)量幾百萬）
'listAccountByPage'>
seelct*
fromaccount
wherecreate_time>='2021-01-010000'
andtype='A'
limit#{startRow},#{pageSize}

實(shí)戰(zhàn)優(yōu)化方案

以上的實(shí)現(xiàn)方案，會(huì)存在limit深分頁問題，因?yàn)閍ccount表數(shù)據(jù)量幾百萬。那怎么優(yōu)化呢？

其實(shí)可以使用標(biāo)簽記錄法，有些伙伴可能會(huì)有疑惑，id主鍵不是連續(xù)的呀，真的可以使用標(biāo)簽記錄？

當(dāng)然可以，id不是連續(xù)，我們可以通過order by讓它連續(xù)嘛。優(yōu)化方案如下：

//查詢最小ID
StringlastId=accountDAO.queryMinId();

//查詢最小ID對(duì)應(yīng)的SQL
"queryMinId"returnType=“java.lang.String”>
selectMIN(id)
fromaccount
wherecreate_time>='2021-01-010000'
andtype='A'


//一頁的條數(shù)
IntegerpageSize=100;

Listlist;
do{
list=listAccountByPage(lastId,pageSize);
//標(biāo)簽記錄法，記錄上次查詢過的Id
lastId=list.get(list,size()-1).getId();
//上報(bào)大數(shù)據(jù)
postBigData(list);
}while(CollectionUtils.isNotEmpty(list));

"listAccountByPage">
select*
fromaccount
wherecreate_time>='2021-01-010000'
andid>#{lastId}
andtype='A'
orderbyidasc
limit#{pageSize}