MySQL分頁(yè)優(yōu)化的測(cè)試案例
發(fā)表時(shí)間:2023-09-02 來(lái)源:明輝站整理相關(guān)軟件相關(guān)文章人氣:
[摘要]最近無(wú)意間看到一個(gè)MySQL分頁(yè)優(yōu)化的測(cè)試案例,并沒(méi)有非常具體地說(shuō)明測(cè)試場(chǎng)景的情況下,給出了一種經(jīng)典的方案,因?yàn)楝F(xiàn)實(shí)中很多情況都不是固定不變的,能總結(jié)出來(lái)通用性的做法或者說(shuō)是規(guī)律,是要考慮非常多的場(chǎng)景的,同時(shí),面對(duì)能夠達(dá)到優(yōu)化的方式要追究其原因,同樣的做法,換了個(gè)場(chǎng)景,達(dá)不到優(yōu)化效果的,還要追究其...
最近無(wú)意間看到一個(gè)MySQL分頁(yè)優(yōu)化的測(cè)試案例,并沒(méi)有非常具體地說(shuō)明測(cè)試場(chǎng)景的情況下,給出了一種經(jīng)典的方案,
因?yàn)楝F(xiàn)實(shí)中很多情況都不是固定不變的,能總結(jié)出來(lái)通用性的做法或者說(shuō)是規(guī)律,是要考慮非常多的場(chǎng)景的,
同時(shí),面對(duì)能夠達(dá)到優(yōu)化的方式要追究其原因,同樣的做法,換了個(gè)場(chǎng)景,達(dá)不到優(yōu)化效果的,還要追究其原因。
個(gè)人對(duì)此場(chǎng)景在不用情況表示懷疑,然后自己測(cè)試了一把,果然發(fā)現(xiàn)一些問(wèn)題,同時(shí)也證實(shí)了一些預(yù)期的想法。
本文就MySQL分頁(yè)優(yōu)化,從最最簡(jiǎn)單的情況出發(fā),來(lái)做一個(gè)簡(jiǎn)單的分析。
另:本文測(cè)試環(huán)境是最最低配置的云服務(wù)器,相對(duì)來(lái)說(shuō)服務(wù)器硬件環(huán)境有限,不過(guò)對(duì)于不同的語(yǔ)句(寫法)應(yīng)該是“平等的”
MySQL經(jīng)典的分頁(yè)“優(yōu)化”做法
MySQL分頁(yè)優(yōu)化中,有一種經(jīng)典的問(wèn)題,在查詢?cè)健翱亢蟆钡臄?shù)據(jù)越慢(取決于表上的索引類型,對(duì)于B樹(shù)結(jié)構(gòu)的索引,SQL Server中也一樣)
select * from t order by id limit m,n。
也即隨著M的增大,查詢同樣多的數(shù)據(jù),會(huì)越來(lái)越慢
面對(duì)這一問(wèn)題,于是就產(chǎn)生了一種經(jīng)典的做法,類似于(或者變種)如下的寫法
就是先把分頁(yè)范圍內(nèi)的id單獨(dú)找出來(lái),然后再跟基表做關(guān)聯(lián),最后查詢出來(lái)所需要的數(shù)據(jù)
select * from t
inner join (select id from t order by id limit m,n)t1 on t1.id = t.id
這種做法是不是總是生效的,或者說(shuō)是在什么情況下后者才能到達(dá)到優(yōu)化的目的?有沒(méi)有做了改寫之后無(wú)效甚至變慢的情況?
與此同時(shí),絕大多數(shù)查詢都是有篩選條件的,
如果有篩選條件的情況,
sql語(yǔ)句就變成了select * from t where *** order by id limit m,n
如果如法炮制,改寫成類似
select * from t
inner join (select id from t where *** order by id limit m,n )t1 on t1.id = t.id
在這種情況下,改寫后的sql語(yǔ)句還能達(dá)到優(yōu)化的目的嗎?
測(cè)試環(huán)境搭建
測(cè)試數(shù)據(jù)比較簡(jiǎn)單,通過(guò)存儲(chǔ)過(guò)程循環(huán)寫入測(cè)試數(shù)據(jù),測(cè)試表的InnoDB引擎表。
這里要注意的是日志寫入模式一定要修改成innodb_flush_log_at_trx_commit = 2,否則默認(rèn)情況下,500w數(shù)據(jù),估計(jì)一天都寫不完,這個(gè)與日志寫入模式有關(guān),就不多說(shuō)了,
分頁(yè)查詢優(yōu)化的緣由
首先還是先看一下這個(gè)經(jīng)典的問(wèn)題,分頁(yè)的時(shí)候,越“靠后”查詢相應(yīng)越慢的情況
測(cè)試一:查詢第1-20行的數(shù)據(jù),0.01秒
同樣是查詢20行數(shù)據(jù),查詢相對(duì)“靠后”的數(shù)據(jù),比如這里的從4900001-4900020行數(shù)據(jù)的情況,用時(shí)1.97秒。
從中可以看到,查詢條件不變的情況下,越往后查詢,查詢效率越低,可以簡(jiǎn)單理解成:同樣搜索20行數(shù)據(jù),越是靠后的數(shù)據(jù),查詢代價(jià)越大。
至于為什么后一種效率較低,后面會(huì)慢慢分析。
測(cè)試環(huán)境是centos 7 ,mysql 5.7,測(cè)試表的數(shù)據(jù)是500W
重現(xiàn)經(jīng)典分頁(yè)“優(yōu)化”,當(dāng)沒(méi)有篩選條件,排序列為聚集索引的時(shí)候,并不會(huì)有所改善
這里來(lái)對(duì)比以下兩種寫法在聚集索引列作為排序條件時(shí)候的性能
select * from t order by id limit m,n。
select * from t
inner join (select id from t order by id limit m,n)t1 on t1.id = t.id
第一種寫法:
select * from test_table1 order by id asc limit 4900000,20;測(cè)試結(jié)果見(jiàn)截圖,執(zhí)行時(shí)間為8.31秒
第二種改寫后的寫法:
select t1.* from test_table1 t1
inner join (select id from test_table1 order by id limit 4900000,20)t2 on t1.id = t2.id;執(zhí)行時(shí)間為8.43秒
這里很清楚,通過(guò)經(jīng)典的改寫方法改寫之后,性能能毫無(wú)提升,甚至還有一點(diǎn)點(diǎn)變慢了,
實(shí)際測(cè)試上表現(xiàn)為兩者在性能上并沒(méi)有明顯的線性差異,這兩者樓主是做了多次測(cè)試的。
我個(gè)人看到類似結(jié)論非要測(cè)一下不可的,這個(gè)東西不能靠蒙,或者靠運(yùn)氣什么的,能提高效率是為什么,不能提高又是為什么。
那么為什么改寫之后的寫法沒(méi)有像傳說(shuō)中的那種提升性能?
是什么導(dǎo)致當(dāng)前這個(gè)改寫沒(méi)有到達(dá)提升性能的目的?
后者能夠提升性能的原理是什么?
首先看一下測(cè)試表的表結(jié)構(gòu),排序列上是有索引,這一點(diǎn)是沒(méi)有問(wèn)題的,關(guān)鍵是這個(gè)排序列上的索引是主鍵(聚集索引)。
為什么排序列上是聚集索引的時(shí)候,相對(duì)“優(yōu)化”改寫之后的sql并不能達(dá)到“優(yōu)化”的目的?
在排序列為聚集索引列的情況下,兩者都是順序掃描表來(lái)實(shí)現(xiàn)查詢符合條件的數(shù)據(jù)的
后者雖然是先驅(qū)動(dòng)一個(gè)子查詢,然后再用子查詢的結(jié)果驅(qū)動(dòng)主表,
但是子查詢并沒(méi)有改變“順序掃描表來(lái)實(shí)現(xiàn)查詢符合條件的數(shù)據(jù)的”做法,當(dāng)前情況下,甚至改寫后的做法顯得畫蛇添足
參考如下兩者執(zhí)行計(jì)劃,第一個(gè)截圖的執(zhí)行計(jì)劃的一行,與改寫后的sql的執(zhí)行計(jì)劃的第三行(id =2 的那一行),基本上一樣。
當(dāng)沒(méi)有篩選條件,排序列為聚集索引時(shí)候的分頁(yè)查詢,所謂的分頁(yè)查詢優(yōu)化只不過(guò)是畫蛇添足
目前來(lái)看,查詢上述數(shù)據(jù),兩種方式都非常慢,那如果要查詢上述的數(shù)據(jù),該如何做?
還是要看為什么慢,首先要理解B數(shù)的平衡性結(jié)構(gòu),在我自己粗略的理解來(lái)看,如下圖,
當(dāng)查詢的數(shù)據(jù)“靠后”的時(shí)候,實(shí)際上是偏離在B樹(shù)索引的一個(gè)方向,如下兩個(gè)截圖所示的目標(biāo)數(shù)據(jù)
其實(shí)平衡樹(shù)上的數(shù)據(jù),沒(méi)有所謂的“靠前”與“靠后”,“靠前”與“靠后”都是相對(duì)于對(duì)方來(lái)說(shuō)的,或者說(shuō)是從掃描的方向上來(lái)看的
從一個(gè)方向上看“靠后的”數(shù)據(jù),從一個(gè)方向看就是“靠前的”,前后不是絕對(duì)的。
如下兩個(gè)截圖是B樹(shù)索引結(jié)構(gòu)的粗略表現(xiàn)形式,假如目標(biāo)數(shù)據(jù)的位置固定的情況下,所謂的“靠后”是相對(duì)與從左向右來(lái)說(shuō)的;
如果從右向左看,之前所謂靠后的數(shù)據(jù)實(shí)際上是“靠前”的。
只要數(shù)據(jù)是靠前的,要高效低找到這部分?jǐn)?shù)據(jù),還是可以的。mysql中應(yīng)該也有類似于sqlserver中的正向(forwarded)和反向掃描(backward)的做法。
如果對(duì)于靠后的數(shù)據(jù),采用反向掃描,應(yīng)該就可以很快找到這個(gè)部分?jǐn)?shù)據(jù),然后對(duì)找到的數(shù)據(jù)在再次排序(asc),結(jié)果應(yīng)該是一樣的,
首先來(lái)看效果:結(jié)果跟上面的查詢一模一樣,這里僅耗時(shí)0.07秒,之前的兩種寫法均超過(guò)了8秒,效率有上百倍的差距。
至于這個(gè)是為什么,我想根據(jù)上面的闡述,自己應(yīng)該能夠體會(huì)的到,這里附上這個(gè)sql。
如果經(jīng)常查詢所謂的靠后的數(shù)據(jù),比如說(shuō)Id較大的數(shù)據(jù),或者說(shuō)是時(shí)間維度上較新的數(shù)據(jù),可以采用倒敘掃描索引的方式來(lái)實(shí)現(xiàn)高效分頁(yè)查詢
(這里請(qǐng)計(jì)算好數(shù)據(jù)所在的分頁(yè),同樣的數(shù)據(jù),正序和倒序其起始“頁(yè)碼”是不同的)
select* from(select * from test_table1 order by id desc limit 99980,20) t order by id;
當(dāng)沒(méi)有篩選條件,排序列為非聚集索引的時(shí)候,會(huì)有所改善
這里對(duì)測(cè)試表test_table1做出如下改變
1,增加一個(gè)id_2列,
2,該字段上創(chuàng)建一個(gè)唯一索引,
3,該字段用對(duì)應(yīng)的主鍵Id填充
上面的測(cè)試是按照主鍵索引(聚集索引)來(lái)排序的,現(xiàn)在來(lái)按照非聚集索引排序,也即新增的這個(gè)列id_2來(lái)排序,測(cè)試一開(kāi)始提到的兩種分頁(yè)方法。
首先來(lái)看第一種寫法
select * from test_table1 order by id_2 asc limit 4900000,20;執(zhí)行時(shí)間為1分鐘多一點(diǎn),暫且認(rèn)其為60秒
第二種寫法
select t1.* from test_table1 t1
inner join (select id from test_table1 order by id_2 limit 4900000,20)t2 on t1.id = t2.id;執(zhí)行時(shí)間1.67秒
從這種情況來(lái)看,也就是說(shuō)排序列為非聚集索引列的時(shí)候,后一種寫法確實(shí)能大幅度地提升效率。差不多有40倍的提升。
那么原因在何呢?
首先來(lái)看第一種寫法的執(zhí)行計(jì)劃,可以簡(jiǎn)單理解為這個(gè)sql的執(zhí)行時(shí)做全表掃描之后,然后重新按照id_2排序,最后取最前20條數(shù)據(jù)。
首先全表掃描就是一個(gè)非常耗時(shí)的過(guò)程,排序也是一個(gè)非常大的代價(jià),因此表現(xiàn)為性能非常的低下。
再來(lái)看后者的執(zhí)行計(jì)劃,他是首先子子查詢中,按照id_2上的索引順序掃描,然后用符合條件的主鍵Id去表中查詢數(shù)據(jù)
這樣的話,避免了查詢出來(lái)大量的數(shù)據(jù)然后重新排序(Using filesort)
如果了解sqlserver執(zhí)行計(jì)劃的情況下,后者與前者相比,應(yīng)該還有避免了頻繁的回表(sqlserver中叫做key lookup或者書簽查找的過(guò)程
可以認(rèn)為是子查詢驅(qū)動(dòng)外層表查詢符合條件的20條的數(shù)據(jù)的過(guò)程是一個(gè)批量的,一次性的。
其實(shí),只有在當(dāng)前情況下,也就是說(shuō)排序列為非聚集索引列的時(shí)候,改寫后的sql才能提升分頁(yè)查詢的效率。
即便如此,此方式“優(yōu)化”過(guò)的分頁(yè)語(yǔ)句,還是與如下寫法的分頁(yè)效率有比較大的差別的
上面也看到了,返回同樣的數(shù)據(jù),如下的查詢是0.07秒,比這里的1.67秒還是高2個(gè)數(shù)量級(jí)的
select* from(select * from test_table1 order by id desc limit 99980,20) t order by id;
另外一個(gè),想提到的問(wèn)題就是,如果經(jīng)常性分頁(yè)查詢,還要按照某種順序,那么為什么不在這個(gè)列上建立一個(gè)聚集索引。
比如語(yǔ)句自增Id的,或者時(shí)間+其他字段確保唯一性的,mysql會(huì)在主鍵上自動(dòng)創(chuàng)建聚集索引。
然后有了聚集索引,“靠前”與“靠后”僅僅是一個(gè)相對(duì)的邏輯上的概念了,如果多數(shù)時(shí)候是想得到“靠后”或者較新的數(shù)據(jù),就可以采用上述寫法,
當(dāng)存在篩選條件的情況下,分頁(yè)查詢的優(yōu)化
這一部分想了想,情況太復(fù)雜了,很難概括出來(lái)一種非常具有代表性的案例,因此就不過(guò)多地做測(cè)試了。
select * from t where *** order by id limit m,n
1,比如刷選條件本身就很高效,一過(guò)濾出來(lái)僅剩下很少一部分?jǐn)?shù)據(jù),那么改不改寫sql意義也不大,因?yàn)楹Y選條件本身就可以做到很高效的篩選
2,比如刷選條件本身作用不大(過(guò)濾后數(shù)據(jù)量依然巨大),這種情況其實(shí)又回到了不存在篩選條件的情況,還有取決于如何排序,正序還是倒序等等
3,比如篩選條件本身作用不大(過(guò)濾后數(shù)據(jù)量依然巨大),要考慮的一個(gè)很實(shí)際的問(wèn)題是數(shù)據(jù)分布,
數(shù)據(jù)的分布也會(huì)影響的sql的執(zhí)行效率(sqlserver中的經(jīng)歷,mysql應(yīng)該差別不大)
4,本身查詢比較復(fù)雜的情況下,很難說(shuō)用某種方式就可以達(dá)到高效的目的
情況越復(fù)雜,越是難以總結(jié)出來(lái)一種通用性的規(guī)律或者說(shuō)是方法,一切都要以具體情況來(lái)看待,很難下一個(gè)定論。
這里對(duì)于查詢加上篩選條件的情況,就不做一一分析了,不過(guò)可以肯定的是,脫離了實(shí)際場(chǎng)景,肯定沒(méi)有一個(gè)固化的方案。
另外,對(duì)于查詢當(dāng)前頁(yè)數(shù)據(jù)時(shí)候,利用上一頁(yè)查詢的最大值做篩選條件,也可以很快滴找到當(dāng)前頁(yè)的數(shù)據(jù),這樣當(dāng)然沒(méi)有問(wèn)題,但這屬于另外一個(gè)做法,不在本文討論之列。
總結(jié)
分頁(yè)查詢,越靠后越慢的情況,實(shí)則對(duì)于B樹(shù)索引來(lái)說(shuō),靠前與靠后是一個(gè)邏輯上相對(duì)的概念,性能上的差異,是基于B樹(shù)索引結(jié)構(gòu)以及掃描方式有關(guān)的.
如果加上篩選條件,情況將變得更加復(fù)雜,這個(gè)問(wèn)題在SQL Server中的原理也是一樣的,本來(lái)也在SQL Server中做了測(cè)試的,這里就不重復(fù)了。
當(dāng)前這種情況,排序列不一定,查詢條件不一定,數(shù)據(jù)分布不一定,就很難用一種特定的方法來(lái)實(shí)現(xiàn)“優(yōu)化”,弄不好還起到畫蛇添足的副作用。
因此在做分頁(yè)優(yōu)化的時(shí)候,一定要根據(jù)具體的場(chǎng)景來(lái)做分析,方法也不一定只有一種,脫離實(shí)際場(chǎng)景的結(jié)論,都是扯犢子。
唯有弄清楚這個(gè)問(wèn)題的來(lái)龍去脈,才能游刃有余。
因此個(gè)人對(duì)于數(shù)據(jù)“優(yōu)化”的結(jié)論,一定是具體問(wèn)題具體分析,是很忌諱總結(jié)出來(lái)一套規(guī)則(規(guī)則1,2,3,4,5)給人“套用”,鑒于本人也很菜,就更不敢總結(jié)出來(lái)一些教條了。
以上就是MySQL分頁(yè)優(yōu)化的測(cè)試案例的詳細(xì)內(nèi)容,更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章!
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識(shí)。