事故背景

這次事故也是我們組里遇到的一次關(guān)于分頁慢查詢的典型例子，通過這篇文章，你可以很清晰的跟隨我們還原事故現(xiàn)場(chǎng)，以及每一步遇到問題做出的調(diào)整和改動(dòng)。

事故問題現(xiàn)場(chǎng)

• 16:00 收到同事反饋，融合系統(tǒng)分?查詢可?率降低
• 16:05 查詢接?UMP監(jiān)控，發(fā)現(xiàn)接?TP99異常彪?

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

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

定位到這里，我們基本確定這個(gè)不是幾分鐘能解決的問題，于是我們分成兩步去處理。第一步：打開限流，防止更多的慢sql請(qǐng)求進(jìn)行 第二步：分析慢sql，進(jìn)行改造上線 查看慢SQL，?部分都是融合系統(tǒng)分?查詢接?涉及到的SQL，同時(shí)由于上游系統(tǒng)在15:35左右對(duì)于該接?調(diào)?流量激增，和數(shù)據(jù)庫CPU暴漲，接?TP999暴漲的時(shí)間吻合，推測(cè)是由于庫存對(duì)于該接?的調(diào)?對(duì)于數(shù)據(jù)庫造成了壓?，導(dǎo)致接?耗時(shí)增加。但是該接?的調(diào)?量并不?，再次查看慢SQL，發(fā)現(xiàn)有?量已經(jīng)遍歷到?百?的慢SQL。推測(cè)是深分?的問題。

• 16:15 排查?志發(fā)現(xiàn)，?部分SQL都指向商家xxxx，查詢發(fā)現(xiàn)其下有10W條數(shù)據(jù)（占?總數(shù)量的?分之?），MQ發(fā)現(xiàn)有?量重試，分?查詢接?超時(shí)時(shí)間發(fā)現(xiàn)配置的是2S。推測(cè)是慢查詢導(dǎo)致的?頻次重試將數(shù)據(jù)庫的性能拖垮。
• 16:25 觀察代碼后，確定了是深分?問題，確定下來了優(yōu)化?案。為了避免庫存修改接?，?先我們優(yōu)化SQL將其優(yōu)化為?查詢的形式。即先通過pageNo和pageSize查詢出ID，然后取出當(dāng)中的最?值和最?值，然后使?范圍查詢?nèi)ゲ樵兂鰜砣頂?shù)據(jù)。由于線上持續(xù)對(duì)數(shù)據(jù)庫造成壓?，先讓上游把MQ的消費(fèi)暫停消費(fèi)。
• 17:40 優(yōu)化代碼上線，上游重新打開MQ消費(fèi)，但是由于消費(fèi)積累的消息?較多，直接打開后，還是對(duì)融合數(shù)據(jù)庫造成了壓?。接?的TP99再次飆升，數(shù)據(jù)庫CPU再次飆到100%。
• 18:00 復(fù)盤了下，決定不再優(yōu)化舊接?，?是開發(fā)新接?，基于滾動(dòng)ID進(jìn)?分?查詢。需要推動(dòng)上游?起參與開發(fā)和聯(lián)調(diào)。
• 22:20 新接?上線，重新放開MQ消費(fèi)，上游積壓了?量消息的情況下，新接?表現(xiàn)平穩(wěn)，“問題解決”

問題原因和解決?法

深分?出現(xiàn)原因

問題SQL：

select*fromtablewhereorg_code=xxxxlimit1000,100

以上?的SQL為例，MySQL的limit?作原理就是先讀取前?1000條記錄，然后拋棄前1000條，讀后?100條想要的，所以?碼越?，偏移量越?，性能就越差。

深分?的?種解決?法

查詢ID+基于ID查詢

即先使?查詢條件查詢出來id,再通過id進(jìn)?范圍查詢，也就是說我第?次優(yōu)化的時(shí)候使?的?法 ?先查詢出來ID，以上?的SQL為例

selectidfromtablewhereorg_code=xxxxlimit1000,5

然后查詢出來id后，使?id進(jìn)?in查詢，由于是直接基于主鍵的in查詢，所以效率較?

select*fromtablewhereidin(1,2,3,4,5);

基于ID查詢優(yōu)化

由于在第?次查詢已經(jīng)查詢出來了所有符合條件的ID了，可以使?范圍查詢來替代in查詢，效率更?(in 查詢需要和集合??的元素進(jìn)??對(duì)，但是范圍查詢只需要?較最?和最?即可)

select*fromtablewhereorg_code=xxxxandid>=1andid<=?5;

使??查詢

selecta.id,a.dj_sku_id,a.jd_sku_idfromtableajoin(selectidfrom

jd_spu_skuwhereorg_code=xxxxlimit1000,5)b

ona.id=b.id;

使??查詢可以減少和數(shù)據(jù)庫的IO交互，也是?種常?的解決深分?的?法。

使?滾動(dòng)查詢

每次接?都會(huì)返回查詢出來的數(shù)據(jù)的最?的id（游標(biāo)），下?次查詢傳?這個(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)起來?較簡(jiǎn)單且性能很好。缺點(diǎn)是需要客戶端修改，且需要保證ID是?增有序且結(jié)果需要是按照ID排序的。最終定下的是使?滾動(dòng)查詢的?法。最終優(yōu)化SQL上線后，表現(xiàn)平穩(wěn)。第?周和庫存?起重新優(yōu)化了?多規(guī)格SKU的SQL。如下:

SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhere

org_code=xxxxandid>0orderbyidasclimit500

測(cè)試了沒問題后上線。觀察線上監(jiān)控穩(wěn)定。本以為?枕?憂的時(shí)候，?周之后，數(shù)據(jù)庫再次出現(xiàn)了?量的慢查詢，數(shù)據(jù)庫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

可以看出來，這就是我們優(yōu)化后的SQL。運(yùn)維同學(xué)explain這條sql后發(fā)現(xiàn)，這條SQL?了主鍵索引，沒有?我們以為應(yīng)該要?的org_code的索引。

和運(yùn)維初步溝通后得出結(jié)論，在某些情況下，主鍵索引的優(yōu)先級(jí)是會(huì)?于普通索引的。

最終解決方案

引用join

因?yàn)槲覀兪?了主鍵索引進(jìn)?排序，且查詢了不在索引樹只在葉?節(jié)點(diǎn)中的字段。因此mysql認(rèn)為主鍵索引更優(yōu)，因?yàn)榧瓤梢耘判颍?不?回表，所以就使?主鍵索引最終導(dǎo)致了全表掃描。

最終使?了先查詢ID（不查詢?nèi)~?節(jié)點(diǎn)字段保證使?索引），在通過join，使?查詢出來的ID來查詢對(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)定后，分析之前的問題SQL，執(zhí)?下?兩條語句，同樣的SQL，不同的商家，MYSQL的執(zhí)?結(jié)果也是不?樣的

查詢資料找原因

查閱資料得知

• MYSQL會(huì)將limit的數(shù)量和where條件?查詢出的數(shù)量進(jìn)??對(duì)，如果limit數(shù)量占?較? （例如某些商家的sku數(shù)??較多），則會(huì)"優(yōu)化"為主鍵索引，因?yàn)镸YSQL此時(shí)認(rèn)為?主鍵索引會(huì)減少 ?次索引樹的查詢，且可以在較短時(shí)間??得到結(jié)果。（沒有LIMIT不會(huì)?主鍵索引）
• 因此在where 索引A order by 主鍵索引 limit N的這種SQL，需要考慮MYSQL優(yōu)化主鍵索引的情況。
• 除了上?最終上線后的優(yōu)化SQL，也可以通過force index強(qiáng)制使?索引:

SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtableforce

index(idx_upc)whereorg_code=xxxxandid>0orderbyidasclimit

500

但是這種寫死了索引名稱的?式，如果以后修改了索引名，容易導(dǎo)致安全隱患。

問題總結(jié)

• B端系統(tǒng)也需要考慮對(duì)??系統(tǒng)的保護(hù)，接?限流等，防?異常流量或者異常調(diào)?把??的系統(tǒng)調(diào)死。這次幸虧上游系統(tǒng)是通過MQ調(diào)?融合API的，可以暫停消費(fèi)，如果是?API調(diào)?，且流量較?，持續(xù)讓數(shù)據(jù)庫處于?壓狀態(tài)，會(huì)影響到融合系統(tǒng)的整體穩(wěn)定性。
• 針對(duì)可能出現(xiàn)的?險(xiǎn)點(diǎn)絕不姑息。這次這個(gè)分?查詢sku的接?，之前就看到過，但是當(dāng)時(shí)覺得這個(gè)接?在數(shù)據(jù)量較少的情況下性能也還好，?且也有了商家維度的索引，就放過了，考慮后續(xù)優(yōu)化。結(jié)果現(xiàn)在就爆出了問題。
• 針對(duì)SQL的優(yōu)化，上線前要謹(jǐn)慎，?且需要同?條SQL，需要針對(duì)不同的邊界情況（例如這次的多SKU的商家）進(jìn)?反復(fù)測(cè)試，調(diào)整。