我們?nèi)粘Ｗ龇猪撔枨髸r(shí)爷光，一般會用limit實(shí)現(xiàn)蛀序，但是當(dāng)偏移量特別大的時(shí)候徐裸，查詢效率就變得低下。本文將分四個(gè)方案骑祟，討論如何優(yōu)化MySQL百萬數(shù)據(jù)的深分頁問題曾我，并附上最近優(yōu)化生產(chǎn)慢SQL的實(shí)戰(zhàn)案例。
limit深分頁為什么會變慢？
先看下表結(jié)構(gòu)哈：
CREATE TABLE account (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵Id',
name varchar(255) DEFAULT NULL COMMENT '賬戶名',
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='賬戶表';
假設(shè)深分頁的執(zhí)行SQL如下：
select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;
這個(gè)SQL的執(zhí)行時(shí)間如下：
執(zhí)行完需要0.742秒稚晚，深分頁為什么會變慢呢客燕？如果換成 limit 0,10也搓，只需要0.006秒哦
我們先來看下這個(gè)SQL的執(zhí)行流程：
通過普通二級索引樹idx_update_time傍妒，過濾update_time條件摸柄，找到滿足條件的記錄ID驱负。
通過ID跃脊，回到主鍵索引樹，找到滿足記錄的行捞稿，然后取出展示的列（回表）
掃描滿足條件的100010行碗殷，然后扔掉前100000行继阻，返回抹缕。
執(zhí)行計(jì)劃如下：
SQL變慢原因有兩個(gè)：
limit語句會先掃描offset+n行睹簇，然后再丟棄掉前offset行磨淌，返回后n行數(shù)據(jù)。也就是說
limit 100000,10搪锣，就會掃描100010行，而limit 0,10，只掃描10行抡谐。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中木缝，索引分主鍵索引（聚簇索引）和二級索引
主鍵索引，葉子節(jié)點(diǎn)存放的是整行數(shù)據(jù)
-
二級索引放案，葉子節(jié)點(diǎn)存放的是主鍵的值吱殉。
把條件轉(zhuǎn)移到主鍵索引樹
如果我們把查詢條件，轉(zhuǎn)移回到主鍵索引樹铅匹，那就可以減少回表次數(shù)啦伊群。轉(zhuǎn)移到主鍵索引樹查詢的話舰始，查詢條件得改為主鍵id了丸卷，之前SQL的update_time這些條件咋辦呢？抽到子查詢那里嘛~
子查詢那里怎么抽的呢萎坷？因?yàn)槎壦饕~子節(jié)點(diǎn)是有主鍵ID的哆档，所以我們直接根據(jù)update_time來查主鍵ID即可瓜浸，同時(shí)我們把 limit 100000的條件插佛，也轉(zhuǎ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;寫漏了蚌铜，可以補(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如下：
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;
查詢效果也是杠桿的，只需要0.034秒
執(zhí)行計(jì)劃如下：
查詢思路就是挤聘，先通過idx_update_time二級索引樹查詢到滿足條件的主鍵ID轰枝，再與原表通過主鍵ID內(nèi)連接，這樣后面直接走了主鍵索引了组去，同時(shí)也減少了回表。
標(biāo)簽記錄法
limit 深分頁問題的本質(zhì)原因就是：偏移量（offset）越大添怔，mysql就會掃描越多的行湾戳，然后再拋棄掉。這樣就導(dǎo)致查詢性能的下降广料。
其實(shí)我們可以采用標(biāo)簽記錄法砾脑，就是標(biāo)記一下上次查詢到哪一條了，下次再來查的時(shí)候艾杏，從該條開始往下掃描韧衣。就好像看書一樣，上次看到哪里了购桑，你就折疊一下或者夾個(gè)書簽畅铭，下次來看的時(shí)候，直接就翻到啦勃蜘。
假設(shè)上一次記錄到100000硕噩，則SQL可以修改為：
select id,name,balance FROM account where id > 100000 order by id limit 10;
這樣的話，后面無論翻多少頁缭贡，性能都會不錯(cuò)的炉擅，因?yàn)槊辛?code>id索引。但是這種方式有局限性：需要一種類似連續(xù)自增的字段阳惹。
使用between...and...
很多時(shí)候谍失，可以將limit查詢轉(zhuǎn)換為已知位置的查詢，這樣MySQL通過范圍掃描between...and莹汤，就能獲得到對應(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)案例
我們一起來看一個(gè)實(shí)戰(zhàn)案例哈抹竹。假設(shè)現(xiàn)在有表結(jié)構(gòu)如下线罕，并且有200萬數(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 '賬號'
amount decimal(20,2) DEFAULT NULL COMMENT '金額'
type varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT '類型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='賬戶表'
業(yè)務(wù)需求是這樣：獲取最2021年的A類型賬戶數(shù)據(jù)柒莉，上報(bào)到大數(shù)據(jù)平臺闻坚。
一般思路的實(shí)現(xiàn)方式
很多伙伴接到這么一個(gè)需求，會直接這么實(shí)現(xiàn)了：
//查詢上報(bào)總數(shù)量
Integer total = accountDAO.countAccount();
//查詢上報(bào)總數(shù)量對應(yīng)的SQL
<select id ='countAccount' resultType="java.lang.Integer">
seelct count(1)
from account
where create_time >='2021-01-01 00:00:00'
and type ='A'
</select>
//計(jì)算頁數(shù)
int pageNo = total % pageSize == 0 ? total / pageSize : (total / pageSize + 1);
//分頁查詢兢孝，上報(bào)
for(int i = 0; i < pageNo; i++){
List<AcctountPO> list = accountDAO.listAccountByPage(startRow,pageSize);
startRow = (pageNo-1)*pageSize;
//上報(bào)大數(shù)據(jù)
postBigData(list);
}
//分頁查詢SQL（可能存在limit深分頁問題，因?yàn)閍ccount表數(shù)據(jù)量幾百萬）
<select id ='listAccountByPage' >
seelct *
from account
where create_time >='2021-01-01 00:00:00'
and type ='A'
limit #{startRow},#{pageSize}
</select>
實(shí)戰(zhàn)優(yōu)化方案
以上的實(shí)現(xiàn)方案仅偎，會存在limit深分頁問題跨蟹，因?yàn)閍ccount表數(shù)據(jù)量幾百萬。那怎么優(yōu)化呢橘沥？
其實(shí)可以使用標(biāo)簽記錄法窗轩，有些伙伴可能會有疑惑，id主鍵不是連續(xù)的呀座咆，真的可以使用標(biāo)簽記錄痢艺？
當(dāng)然可以，id不是連續(xù)介陶，我們可以通過order by讓它連續(xù)嘛堤舒。優(yōu)化方案如下：
//查詢最小ID
String lastId = accountDAO.queryMinId();
//查詢最小ID對應(yīng)的SQL
<select id="queryMinId" returnType=“java.lang.String”>
select MIN(id)
from account
where create_time >='2021-01-01 00:00:00'
and type ='A'
</select>
//一頁的條數(shù)
Integer pageSize = 100;
List<AcctountPO> list ;
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));
<select id ="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}
</select>
