您好,登錄后才能下訂單哦!
今天就跟大家聊聊有關(guān)MySQL深分頁問題解決的操作過程是怎樣的,可能很多人都不太了解,為了讓大家更加了解,小編給大家總結(jié)了以下內(nèi)容,希望大家根據(jù)這篇文章可以有所收獲。
我們?nèi)粘W龇猪撔枨髸r,一般會用limit實現(xiàn),但是當(dāng)偏移量特別大的時候,查詢效率就變得低下。本文將分4個方案,討論如何優(yōu)化MySQL百萬數(shù)據(jù)的深分頁問題,并附上最近優(yōu)化生產(chǎn)慢SQL的實戰(zhàn)案例。
先看下表結(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)建時間', update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', 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;
這個SQL的執(zhí)行時間如下:
執(zhí)行完需要0.742秒,深分頁為什么會變慢呢?如果換成 limit 0,10,只需要0.006秒哦
我們先來看下這個SQL的執(zhí)行流程:
通過普通二級索引樹idx_update_time,過濾update_time條件,找到滿足條件的記錄ID。
通過ID,回到主鍵索引樹,找到滿足記錄的行,然后取出展示的列(回表)
掃描滿足條件的100010行,然后扔掉前100000行,返回。
SQL的執(zhí)行流程
執(zhí)行計劃如下:
SQL變慢原因有兩個:
limit語句會先掃描offset+n行,然后再丟棄掉前offset行,返回后n行數(shù)據(jù)。也就是說limit 100000,10,就會掃描100010行,而limit 0,10,只掃描10行。
limit 100000,10 掃描更多的行數(shù),也意味著回表更多的次數(shù)。
因為以上的SQL,回表了100010次,實際上,我們只需要10條數(shù)據(jù),也就是我們只需要10次回表其實就夠了。因此,我們可以通過減少回表次數(shù)來優(yōu)化。
那么,如何減少回表次數(shù)呢?我們先來復(fù)習(xí)下B+樹索引結(jié)構(gòu)哈~
InnoDB中,索引分主鍵索引(聚簇索引)和二級索引
主鍵索引,葉子節(jié)點(diǎn)存放的是整行數(shù)據(jù)
二級索引,葉子節(jié)點(diǎn)存放的是主鍵的值。
如果我們把查詢條件,轉(zhuǎn)移回到主鍵索引樹,那就不就可以減少回表次數(shù)啦。轉(zhuǎn)移到主鍵索引樹查詢的話,查詢條件得改為主鍵id了,之前SQL的update_time這些條件咋辦呢?抽到子查詢那里嘛~
子查詢那里怎么抽的呢?因為二級索引葉子節(jié)點(diǎn)是有主鍵ID的,所以我們直接根據(jù)update_time來查主鍵ID即可,同時我們把 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;
查詢效果一樣的,執(zhí)行時間只需要0.038秒!
我們來看下執(zhí)行計劃
由執(zhí)行計劃得知,子查詢 table a查詢是用到了idx_update_time索引。首先在索引上拿到了聚集索引的主鍵ID,省去了回表操作,然后第二查詢直接根據(jù)第一個查詢的 ID往后再去查10個就可以了!
因此,這個方案是可以的~
延遲關(guān)聯(lián)的優(yōu)化思路,跟子查詢的優(yōu)化思路其實是一樣的:都是把條件轉(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í)行計劃如下:
查詢思路就是,先通過idx_update_time二級索引樹查詢到滿足條件的主鍵ID,再與原表通過主鍵ID內(nèi)連接,這樣后面直接走了主鍵索引了,同時也減少了回表。
limit 深分頁問題的本質(zhì)原因就是:偏移量(offset)越大,mysql就會掃描越多的行,然后再拋棄掉。這樣就導(dǎo)致查詢性能的下降。
其實我們可以采用標(biāo)簽記錄法,就是標(biāo)記一下上次查詢到哪一條了,下次再來查的時候,從該條開始往下掃描。就好像看書一樣,上次看到哪里了,你就折疊一下或者夾個書簽,下次來看的時候,直接就翻到啦。
假設(shè)上一次記錄到100000,則SQL可以修改為:
select id,name,balance FROM account where id > 100000 order by id limit 10;
這樣的話,后面無論翻多少頁,性能都會不錯的,因為命中了id索引。但是你,這種方式有局限性:需要一種類似連續(xù)自增的字段。
很多時候,可以將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 desc;
我們一起來看一個實戰(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)建時間', update_time datetime DEFAULT NULL COMMENT '更新時間', 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ù),上報到大數(shù)據(jù)平臺。
很多伙伴接到這么一個需求,會直接這么實現(xiàn)了:
//查詢上報總數(shù)量 Integer total = accountDAO.countAccount(); //查詢上報總數(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> //計算頁數(shù) int pageNo = total % pageSize == 0 ? total / pageSize : (total / pageSize + 1); //分頁查詢,上報 for(int i = 0; i < pageNo; i++){ List<AcctountPO> list = accountDAO.listAccountByPage(startRow,pageSize); startRow = (pageNo-1)*pageSize; //上報大數(shù)據(jù) postBigData(list); } //分頁查詢SQL(可能存在limit深分頁問題,因為account表數(shù)據(jù)量幾百萬) <select id ='listAccountByPage' > seelct * from account where create_time >='2021-01-01 00:00:00' and type ='A' limit #{startRow},#{pageSize} </select>
以上的實現(xiàn)方案,會存在limit深分頁問題,因為account表數(shù)據(jù)量幾百萬。那怎么優(yōu)化呢?
其實可以使用標(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(); //上報大數(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>
看完上述內(nèi)容,你們對MySQL深分頁問題解決的操作過程是怎樣的有進(jìn)一步的了解嗎?如果還想了解更多知識或者相關(guān)內(nèi)容,請關(guān)注億速云行業(yè)資訊頻道,感謝大家的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。