事故背景

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

事故問題現場

• 16:00 收到同事反饋，融合系統分?查詢可?率降低
• 16:05 查詢接?UMP監控，發現接?TP99異常彪?

打開機器監控，發現?乎所有機器的TP999都異常的?，觀察機器CPU監控，發現CPU使?率并不?

• 16:10 查看數據庫監控，發現數據庫CPU異常彪?，定位到是數據庫問題，同時收到了?量的慢SQL郵件。

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

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

問題原因和解決?法

深分?出現原因

問題SQL：

select*fromtablewhereorg_code=xxxxlimit1000,100

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

深分?的?種解決?法

查詢ID+基于ID查詢

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

selectidfromtablewhereorg_code=xxxxlimit1000,5

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

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

基于ID查詢優化

由于在第?次查詢已經查詢出來了所有符合條件的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;

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

使?滾動查詢

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

select*fromtablewhereorg_code=xxxxandid>0limit10;

這種?式服務端實現起來?較簡單且性能很好。缺點是需要客戶端修改，且需要保證ID是?增有序且結果需要是按照ID排序的。最終定下的是使?滾動查詢的?法。最終優化SQL上線后，表現平穩。第?周和庫存?起重新優化了?多規格SKU的SQL。如下:

SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhere

org_code=xxxxandid>0orderbyidasclimit500

測試了沒問題后上線。觀察線上監控穩定。本以為?枕?憂的時候，?周之后，數據庫再次出現了?量的慢查詢，數據庫CPU報警，觀察接?監控:

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

SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhere

org_code=xxxxandid>0orderbyidasclimit500

可以看出來，這就是我們優化后的SQL。運維同學explain這條sql后發現，這條SQL?了主鍵索引，沒有?我們以為應該要?的org_code的索引。

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

最終解決方案

引用join

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

最終使?了先查詢ID（不查詢葉?節點字段保證使?索引），在通過join，使?查詢出來的ID來查詢對應的數據的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了下，可以發現?了我們既定的索引:

于是上線，解決問題。上線穩定后，分析之前的問題SQL，執?下?兩條語句，同樣的SQL，不同的商家，MYSQL的執?結果也是不?樣的

查詢資料找原因

查閱資料得知

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

SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtableforce

index(idx_upc)whereorg_code=xxxxandid>0orderbyidasclimit

500

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

問題總結

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