上次在MySQL8上導(dǎo)入一個(gè)1000萬數(shù)據(jù)之后,今天就想驗(yàn)證看看mysql對(duì)于單表大數(shù)據(jù)的分頁(yè)的表現(xiàn)情況,并探討一下單表大數(shù)據(jù)分頁(yè)的優(yōu)化思路。
測(cè)試環(huán)境
簡(jiǎn)單說明下測(cè)試環(huán)境。
- mysql版本:8.0.17
- 在test測(cè)試數(shù)據(jù)庫(kù)中新增一個(gè)表t_user,字段為ID和NAME。其中在ID字段設(shè)置索引,且name值不同。
- 導(dǎo)入1千萬條數(shù)據(jù),之前有部分測(cè)試數(shù)據(jù),最后數(shù)據(jù)量為10000000,見如下圖:
傳統(tǒng)的分頁(yè)查詢
一般開發(fā)人員對(duì)于web分頁(yè)的解決方案是使用limit子句來完成,語(yǔ)法為:
limit n,m
對(duì)于小規(guī)模數(shù)據(jù)應(yīng)用來講,可能并不會(huì)有大問題,在一定程度上還是比較有效率的,但是如果在大數(shù)據(jù)應(yīng)用來講,它的效率就可能很低了,因?yàn)樗侨頀呙?,?shù)據(jù)越往后,即ID越大,數(shù)據(jù)越慢,例如下面的例子
select * from t_user LIMIT 90000 ,100 ##耗時(shí) 1秒 select * from t_user LIMIT 900000 ,100 ##耗時(shí) 2秒 select * from t_user LIMIT 9000000 ,100 ##耗時(shí) 6秒
耗時(shí)依次增加。
因?yàn)槲覀冏侄伪容^少,所以可能執(zhí)行起來沒想象中的慢,我們可以用執(zhí)行計(jì)劃來驗(yàn)證一下
explain select * from t_user LIMIT 1000000 ,100
從結(jié)果看出執(zhí)行全表掃描,所以如果只取前面幾條,則還是比較容易,如果是取相對(duì)靠后的數(shù)據(jù),則全表掃描對(duì)數(shù)據(jù)庫(kù)壓力時(shí)非常大,特別web應(yīng)用并發(fā)查詢,則很容易造成數(shù)據(jù)庫(kù)響應(yīng)慢。
優(yōu)化思路1
很明顯,既然做全表掃描引起效率低下,那么我們第一印象肯定想辦法是不是不走全表掃描??梢赃@么做,先在索引ID上完成分頁(yè)操作,然后再根據(jù)ID關(guān)聯(lián)回去。查詢代碼更新如下:
1.索引ID完成分頁(yè)
select id from t_user order by id limit 9000000,100
2.最后完成自關(guān)聯(lián),最終SQL如下:
select * from t_user a inner join (select id from t_user order by id limit 9000000,100) b on a.id = b.id
3.看看執(zhí)行計(jì)劃,可見已經(jīng)沒有再全表掃描,這種方式是減少掃描來提高分頁(yè)效率。
最終執(zhí)行時(shí)間為3秒,效率提高一倍。
優(yōu)化思路2
使用子查詢進(jìn)行優(yōu)化,原理是通過子句先定位查詢起始ID值,然后再通過主查詢語(yǔ)句的ID往后查詢,此方法適用ID排序的情況。舉例如下:
select * from t_user where id>=(select id from t_user limit 9000000,1) limit 100;
通過執(zhí)行計(jì)劃查看,它也屬于非全表掃描,所以效率同第一種優(yōu)化方案
優(yōu)化思路3
把LIMIT 查詢轉(zhuǎn)換成某個(gè)位置的查詢,此思路需要和應(yīng)用開發(fā)進(jìn)行結(jié)合,不是單純的數(shù)據(jù)庫(kù)優(yōu)化,因?yàn)槲覀儗?shí)現(xiàn)的是web的分頁(yè)查詢,所以是可以通過頁(yè)面的現(xiàn)實(shí)記錄數(shù)和當(dāng)前頁(yè),計(jì)算出該頁(yè)的ID范圍,我們拼出以下SQL
select * from t_user where id between 9000000 and 9000100 limit 100;
耗時(shí)只需0.4豪秒,是非??焖俚牟樵?,也是我們?cè)谏a(chǎn)中最常見的一種實(shí)現(xiàn)方法。
總結(jié)
優(yōu)化方案2和優(yōu)化3其實(shí)都是把LIMIT m,n轉(zhuǎn)換成 limit n的查詢,適合排序字段唯一的情況,當(dāng)然大部分場(chǎng)景我們的排序字段都會(huì)選主鍵ID,這樣操作的確能減輕分頁(yè)的壓力。理論上,幾千萬數(shù)據(jù)在mysql8上還是可以支撐的。
如果在數(shù)據(jù)量更大的情況下,這幾種方案都無法滿足了,一般我們得在數(shù)據(jù)庫(kù)層和應(yīng)用層之間設(shè)置緩存數(shù)據(jù)庫(kù),再執(zhí)行分頁(yè)查詢。