事故背景

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

事故問題現(xiàn)場

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

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

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

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

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

問題原因和解決?法

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

問題SQL：

select*fromtablewhereorg_code=xxxxlimit1000,100

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

深分?的?種解決?法

查詢ID+基于ID查詢

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

selectidfromtablewhereorg_code=xxxxlimit1000,5

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

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

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

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

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交互，也是?種常?的解決深分?的?法。

使?滾動查詢

每次接?都會返回查詢出來的數(shù)據(jù)的最?的id（游標），下?次查詢傳?這個游標，服務端只需要根據(jù)這個游標，取出id?于這個游標的n個數(shù)據(jù)即可。n為每?展示條數(shù)。

select*fromtablewhereorg_code=xxxxandid>0limit10;

這種?式服務端實現(xiàn)起來?較簡單且性能很好。缺點是需要客戶端修改，且需要保證ID是?增有序且結果需要是按照ID排序的。最終定下的是使?滾動查詢的?法。最終優(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

測試了沒問題后上線。觀察線上監(jiān)控穩(wěn)定。本以為?枕?憂的時候，?周之后，數(shù)據(jù)庫再次出現(xiàn)了?量的慢查詢，數(shù)據(jù)庫CPU報警，觀察接?監(jiān)控:

可以看到在調?量并不?的前提下，接?的耗時達到了60S。聯(lián)系運維同學幫忙排查，發(fā)現(xiàn)了?量的慢 SQL：

SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhere

org_code=xxxxandid>0orderbyidasclimit500

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

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

最終解決方案

引用join

因為我們使?了主鍵索引進?排序，且查詢了不在索引樹只在葉?節(jié)點中的字段。因此mysql認為主鍵索引更優(yōu)，因為既可以排序，?不?回表，所以就使?主鍵索引最終導致了全表掃描。

最終使?了先查詢ID（不查詢葉?節(jié)點字段保證使?索引），在通過join，使?查詢出來的ID來查詢對應的數(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í)?結果也是不?樣的

查詢資料找原因

查閱資料得知

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

SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtableforce

index(idx_upc)whereorg_code=xxxxandid>0orderbyidasclimit

500

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

問題總結

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