事故背景
這次事故也是我們組里遇到的一次關(guān)于分頁(yè)慢查詢(xún)的典型例子,通過(guò)這篇文章,你可以很清晰的跟隨我們還原事故現(xiàn)場(chǎng),以及每一步遇到問(wèn)題做出的調(diào)整和改動(dòng)。
事故問(wèn)題現(xiàn)場(chǎng)
- 16:00 收到同事反饋,融合系統(tǒng)分?查詢(xún)可?率降低
- 16:05 查詢(xún)接?UMP監(jiān)控,發(fā)現(xiàn)接?TP99異常彪?


打開(kāi)機(jī)器監(jiān)控,發(fā)現(xiàn)?乎所有機(jī)器的TP999都異常的?,觀察機(jī)器CPU監(jiān)控,發(fā)現(xiàn)CPU使?率并不?

- 16:10 查看數(shù)據(jù)庫(kù)監(jiān)控,發(fā)現(xiàn)數(shù)據(jù)庫(kù)CPU異常彪?,定位到是數(shù)據(jù)庫(kù)問(wèn)題,同時(shí)收到了?量的慢SQL郵件。

定位到這里,我們基本確定這個(gè)不是幾分鐘能解決的問(wèn)題,于是我們分成兩步去處理。第一步:打開(kāi)限流,防止更多的慢sql請(qǐng)求進(jìn)行 第二步:分析慢sql,進(jìn)行改造上線 查看慢SQL,?部分都是融合系統(tǒng)分?查詢(xún)接?涉及到的SQL,同時(shí)由于上游系統(tǒng)在15:35左右對(duì)于該接?調(diào)?流量激增,和數(shù)據(jù)庫(kù)CPU暴漲,接?TP999暴漲的時(shí)間吻合,推測(cè)是由于庫(kù)存對(duì)于該接?的調(diào)?對(duì)于數(shù)據(jù)庫(kù)造成了壓?,導(dǎo)致接?耗時(shí)增加。但是該接?的調(diào)?量并不?,再次查看慢SQL,發(fā)現(xiàn)有?量已經(jīng)遍歷到?百?的慢SQL。推測(cè)是深分?的問(wèn)題。
- 16:15 排查?志發(fā)現(xiàn),?部分SQL都指向商家xxxx,查詢(xún)發(fā)現(xiàn)其下有10W條數(shù)據(jù)(占?總數(shù)量的?分之?),MQ發(fā)現(xiàn)有?量重試,分?查詢(xún)接?超時(shí)時(shí)間發(fā)現(xiàn)配置的是2S。推測(cè)是慢查詢(xún)導(dǎo)致的?頻次重試將數(shù)據(jù)庫(kù)的性能拖垮。
- 16:25 觀察代碼后,確定了是深分?問(wèn)題,確定下來(lái)了優(yōu)化?案。為了避免庫(kù)存修改接?,?先我們優(yōu)化SQL將其優(yōu)化為?查詢(xún)的形式。即先通過(guò)pageNo和pageSize查詢(xún)出ID,然后取出當(dāng)中的最?值和最?值,然后使?范圍查詢(xún)?nèi)ゲ樵?xún)出來(lái)全表數(shù)據(jù)。由于線上持續(xù)對(duì)數(shù)據(jù)庫(kù)造成壓?,先讓上游把MQ的消費(fèi)暫停消費(fèi)。
- 17:40 優(yōu)化代碼上線,上游重新打開(kāi)MQ消費(fèi),但是由于消費(fèi)積累的消息?較多,直接打開(kāi)后,還是對(duì)融合數(shù)據(jù)庫(kù)造成了壓?。接?的TP99再次飆升,數(shù)據(jù)庫(kù)CPU再次飆到100%。
- 18:00 復(fù)盤(pán)了下,決定不再優(yōu)化舊接?,?是開(kāi)發(fā)新接?,基于滾動(dòng)ID進(jìn)?分?查詢(xún)。需要推動(dòng)上游?起參與開(kāi)發(fā)和聯(lián)調(diào)。
- 22:20 新接?上線,重新放開(kāi)MQ消費(fèi),上游積壓了?量消息的情況下,新接?表現(xiàn)平穩(wěn),“問(wèn)題解決”

問(wèn)題原因和解決?法
深分?出現(xiàn)原因
問(wèn)題SQL:
select*fromtablewhereorg_code=xxxxlimit1000,100
以上?的SQL為例,MySQL的limit?作原理就是先讀取前?1000條記錄,然后拋棄前1000條,讀后?100條想要的,所以?碼越?,偏移量越?,性能就越差。
深分?的?種解決?法
查詢(xún)ID+基于ID查詢(xún)
即先使?查詢(xún)條件查詢(xún)出來(lái)id,再通過(guò)id進(jìn)?范圍查詢(xún),也就是說(shuō)我第?次優(yōu)化的時(shí)候使?的?法 ?先查詢(xún)出來(lái)ID,以上?的SQL為例
selectidfromtablewhereorg_code=xxxxlimit1000,5
然后查詢(xún)出來(lái)id后,使?id進(jìn)?in查詢(xún),由于是直接基于主鍵的in查詢(xún),所以效率較?
select*fromtablewhereidin(1,2,3,4,5);
基于ID查詢(xún)優(yōu)化
由于在第?次查詢(xún)已經(jīng)查詢(xún)出來(lái)了所有符合條件的ID了,可以使?范圍查詢(xún)來(lái)替代in查詢(xún),效率更?(in 查詢(xún)需要和集合??的元素進(jìn)??對(duì),但是范圍查詢(xún)只需要?較最?和最?即可)
select*fromtablewhereorg_code=xxxxandid>=1andid<=?5;
使??查詢(xún)
selecta.id,a.dj_sku_id,a.jd_sku_idfromtableajoin(selectidfrom
jd_spu_skuwhereorg_code=xxxxlimit1000,5)b
ona.id=b.id;
使??查詢(xún)可以減少和數(shù)據(jù)庫(kù)的IO交互,也是?種常?的解決深分?的?法。
使?滾動(dòng)查詢(xún)
每次接?都會(huì)返回查詢(xún)出來(lái)的數(shù)據(jù)的最?的id(游標(biāo)),下?次查詢(xún)傳?這個(gè)游標(biāo),服務(wù)端只需要根據(jù)這個(gè)游標(biāo),取出id?于這個(gè)游標(biāo)的n個(gè)數(shù)據(jù)即可。n為每?展示條數(shù)。
select*fromtablewhereorg_code=xxxxandid>0limit10;
這種?式服務(wù)端實(shí)現(xiàn)起來(lái)?較簡(jiǎn)單且性能很好。缺點(diǎn)是需要客戶(hù)端修改,且需要保證ID是?增有序且結(jié)果需要是按照ID排序的。最終定下的是使?滾動(dòng)查詢(xún)的?法。最終優(yōu)化SQL上線后,表現(xiàn)平穩(wěn)。第?周和庫(kù)存?起重新優(yōu)化了?多規(guī)格SKU的SQL。如下:
SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhere
org_code=xxxxandid>0orderbyidasclimit500
測(cè)試了沒(méi)問(wèn)題后上線。觀察線上監(jiān)控穩(wěn)定。本以為?枕?憂(yōu)的時(shí)候,?周之后,數(shù)據(jù)庫(kù)再次出現(xiàn)了?量的慢查詢(xún),數(shù)據(jù)庫(kù)CPU報(bào)警,觀察接?監(jiān)控:

可以看到在調(diào)?量并不?的前提下,接?的耗時(shí)達(dá)到了60S。聯(lián)系運(yùn)維同學(xué)幫忙排查,發(fā)現(xiàn)了?量的慢 SQL:
SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhere
org_code=xxxxandid>0orderbyidasclimit500
可以看出來(lái),這就是我們優(yōu)化后的SQL。運(yùn)維同學(xué)explain這條sql后發(fā)現(xiàn),這條SQL?了主鍵索引,沒(méi)有?我們以為應(yīng)該要?的org_code的索引。

和運(yùn)維初步溝通后得出結(jié)論,在某些情況下,主鍵索引的優(yōu)先級(jí)是會(huì)?于普通索引的。
最終解決方案
引用join
因?yàn)槲覀兪?了主鍵索引進(jìn)?排序,且查詢(xún)了不在索引樹(shù)只在葉?節(jié)點(diǎn)中的字段。因此mysql認(rèn)為主鍵索引更優(yōu),因?yàn)榧瓤梢耘判颍?不?回表,所以就使?主鍵索引最終導(dǎo)致了全表掃描。
最終使?了先查詢(xún)ID(不查詢(xún)?nèi)~?節(jié)點(diǎn)字段保證使?索引),在通過(guò)join,使?查詢(xún)出來(lái)的ID來(lái)查詢(xún)對(duì)應(yīng)的數(shù)據(jù)的SQL:
selecta.idASid,a.dj_org_codeASdjOrgCode,a.dj_sku_idAS
djSkuId,a.jd_sku_idASjdSkuId,a.ynASynfrom
tableajoin
(
SELECTidFROMtablewhereorg_code=xxxxandid>0order
byidasclimit500
)tona.id=t.id;
再次explain了下,可以發(fā)現(xiàn)?了我們既定的索引:

于是上線,解決問(wèn)題。上線穩(wěn)定后,分析之前的問(wèn)題SQL,執(zhí)?下?兩條語(yǔ)句,同樣的SQL,不同的商家,MYSQL的執(zhí)?結(jié)果也是不?樣的

查詢(xún)資料找原因
查閱資料得知
- MYSQL會(huì)將limit的數(shù)量和where條件?查詢(xún)出的數(shù)量進(jìn)??對(duì),如果limit數(shù)量占?較? (例如某些商家的sku數(shù)??較多),則會(huì)"優(yōu)化"為主鍵索引,因?yàn)镸YSQL此時(shí)認(rèn)為?主鍵索引會(huì)減少 ?次索引樹(shù)的查詢(xún),且可以在較短時(shí)間??得到結(jié)果。(沒(méi)有LIMIT不會(huì)?主鍵索引)
- 因此在where 索引A order by 主鍵索引 limit N的這種SQL,需要考慮MYSQL優(yōu)化主鍵索引的情況。
- 除了上?最終上線后的優(yōu)化SQL,也可以通過(guò)force index強(qiáng)制使?索引:
SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtableforce
index(idx_upc)whereorg_code=xxxxandid>0orderbyidasclimit
500
但是這種寫(xiě)死了索引名稱(chēng)的?式,如果以后修改了索引名,容易導(dǎo)致安全隱患。
問(wèn)題總結(jié)
- B端系統(tǒng)也需要考慮對(duì)??系統(tǒng)的保護(hù),接?限流等,防?異常流量或者異常調(diào)?把??的系統(tǒng)調(diào)死。這次幸虧上游系統(tǒng)是通過(guò)MQ調(diào)?融合API的,可以暫停消費(fèi),如果是?API調(diào)?,且流量較?,持續(xù)讓數(shù)據(jù)庫(kù)處于?壓狀態(tài),會(huì)影響到融合系統(tǒng)的整體穩(wěn)定性。
- 針對(duì)可能出現(xiàn)的?險(xiǎn)點(diǎn)絕不姑息。這次這個(gè)分?查詢(xún)sku的接?,之前就看到過(guò),但是當(dāng)時(shí)覺(jué)得這個(gè)接?在數(shù)據(jù)量較少的情況下性能也還好,?且也有了商家維度的索引,就放過(guò)了,考慮后續(xù)優(yōu)化。結(jié)果現(xiàn)在就爆出了問(wèn)題。
-
針對(duì)SQL的優(yōu)化,上線前要謹(jǐn)慎,?且需要同?條SQL,需要針對(duì)不同的邊界情況(例如這次的多SKU的商家)進(jìn)?反復(fù)測(cè)試,調(diào)整。
-
cpu
+關(guān)注
關(guān)注
68文章
11079瀏覽量
217036 -
API
+關(guān)注
關(guān)注
2文章
1617瀏覽量
64024 -
SQL
+關(guān)注
關(guān)注
1文章
783瀏覽量
45134
原文標(biāo)題:坑慘了!一次分頁(yè)慢查詢(xún)導(dǎo)致的事故處理過(guò)程
文章出處:【微信號(hào):magedu-Linux,微信公眾號(hào):馬哥Linux運(yùn)維】歡迎添加關(guān)注!文章轉(zhuǎn)載請(qǐng)注明出處。
發(fā)布評(píng)論請(qǐng)先 登錄
一文了解MyBatis的查詢(xún)原理
微處理溫度控制模擬VI 輸出階段的處理過(guò)程
vison assistant中的圖像處理過(guò)程
51單片機(jī)中斷處理過(guò)程有幾個(gè)
污水處理過(guò)程儀表技術(shù)的研究現(xiàn)狀
污水處理過(guò)程儀表技術(shù)的研究現(xiàn)狀
一次波過(guò)程的等值電路

評(píng)論