阿飛Javaer壹若,轉(zhuǎn)載請(qǐng)注明原創(chuàng)出處,謝謝皂冰!
分頁性能分析
性能瓶頸
查詢偏移量過大的分頁會(huì)導(dǎo)致數(shù)據(jù)庫獲取數(shù)據(jù)性能低下店展,以MySQL為例:
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10
這句SQL會(huì)使得MySQL在無法利用索引的情況下跳過1000000條記錄后,再獲取10條記錄秃流,其性能可想而知赂蕴。而在分庫分表的情況下(假設(shè)分為2個(gè)庫),為了保證數(shù)據(jù)的正確性舶胀,SQL會(huì)改寫為:
SELECT * FROM t_order ORDER BY id LIMIT 0, 1000010
即將偏移量前的記錄全部取出概说,并僅獲取排序后的最后10條記錄。這會(huì)在數(shù)據(jù)庫本身就執(zhí)行很慢的情況下嚣伐,進(jìn)一步加劇性能瓶頸糖赔。因?yàn)樵璖QL僅需要傳輸10條記錄至客戶端,而改寫之后的SQL則會(huì)傳輸1000010*2的記錄至客戶端纤控。
Sharding-JDBC的優(yōu)化
Sharding-JDBC進(jìn)行了2個(gè)方面的優(yōu)化挂捻。
首先,Sharding-JDBC采用流式處理 + 歸并排序的方式來避免內(nèi)存的過量占用船万。Sharding-JDBC的SQL改寫刻撒,不可避免的占用了額外的帶寬,但并不會(huì)導(dǎo)致內(nèi)存暴漲耿导。
與直覺不同声怔,大多數(shù)人認(rèn)為Sharding-JDBC會(huì)將1000010*2記錄全部加載至內(nèi)存,進(jìn)而占用大量內(nèi)存而導(dǎo)致內(nèi)存溢出舱呻。
但由于每個(gè)結(jié)果集的記錄是有序的醋火,因此Sharding-JDBC每次比較僅獲取各個(gè)分片的當(dāng)前結(jié)果集記錄,駐留在內(nèi)存中的記錄僅為當(dāng)前路由到的分片的結(jié)果集的當(dāng)前游標(biāo)指向而已箱吕。
對(duì)于本身即有序的待排序?qū)ο蠼娌担瑲w并排序的時(shí)間復(fù)雜度僅為O(n),性能損耗很小茬高。
其次兆旬,Sharding-JDBC對(duì)僅落至單分片的查詢進(jìn)行進(jìn)一步優(yōu)化。落至單分片查詢的請(qǐng)求并不需要改寫SQL也可以保證記錄的正確性怎栽,因此在此種情況下丽猬,Sharding-JDBC并未進(jìn)行SQL改寫宿饱,從而達(dá)到節(jié)省帶寬的目的。
更好的分頁解決方案
由于LIMIT并不能通過索引查詢數(shù)據(jù)脚祟,因此如果可以保證ID的連續(xù)性谬以,通過ID進(jìn)行分頁是比較好的解決方案:
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
或通過記錄上次查詢結(jié)果的最后一條記錄的ID進(jìn)行下一頁的查詢:
SELECT * FROM t_order WHERE id > 100000 LIMIT 10
是否需要這種分頁
無論是SELECT * FROM t_order ORDER BY id LIMIT 0, 100010
或者SELECT * FROM t_order WHERE id > 100000 LIMIT 10
,性能都一般般由桌,后者只是稍微好點(diǎn)而已为黎,但是由于LIMIT的存在,mysql都需要排序沥寥;
是否能從產(chǎn)品角度或者用戶習(xí)慣等方面解決或者避免這個(gè)問題碍舍?
- 用戶習(xí)慣結(jié)合產(chǎn)品需求解決方案:
比如我們以前有個(gè)每日TOP榜單需求,分析用戶行為一般不會(huì)無限制往下滑邑雅,即使有這種用戶片橡,也是極少數(shù),可以忽略淮野。這樣的話捧书,可以通過SQL
*** LIMIT 300
只查詢10頁總計(jì)300個(gè)TOP應(yīng)用,然后把這些數(shù)據(jù)以list結(jié)構(gòu)保存到redis中骤星。這樣的話经瓷,用戶查看每日TOP榜單只需通過LRANGE key start stop
從redis緩存中取數(shù)據(jù)即可,且限制查詢的offset不允許超過300洞难;