事故背景
事故問題現場
問題原因和解決⽅法

深分⻚出現原因

深分⻚的⼏種解決⽅法

查詢ID+基于ID查詢
基于ID查詢優化
使⽤滾動查詢

最終解決方案

引用join
查詢資料找原因

問題總結

事故背景

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

事故問題現場

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 * from table where org_code = xxxx limit 1000,100

以上⾯的SQL為例，MySQL的limit⼯作原理就是先讀取前⾯1000條記錄，然後抛棄前1000條，讀後⾯100條想要的，是以⻚碼越⼤，偏移量越⼤，性能就越差。

深分⻚的⼏種解決⽅法

查詢ID+基于ID查詢

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

select id from table where org_code = xxxx limit 1000,5

然後查詢出來id後，使⽤id進⾏in查詢，由于是直接基于主鍵的in查詢，是以效率較⾼

select * from table where id in (1,2,3,4,5);

基于ID查詢優化

由于在第⼀次查詢已經查詢出來了所有符合條件的ID了，可以使⽤範圍查詢來替代in查詢，效率更⾼(in 查詢需要和集合⾥⾯的元素進⾏⽐對，但是範圍查詢隻需要⽐較最⼤和最⼩即可)

select * from table where org_code = xxxx and id >= 1 and id <= 5;

使⽤⼦查詢

select a.id,a.dj_sku_id,a.jd_sku_id from table a join (select id from

jd_spu_sku where org_code = xxxx limit 1000,5) b

on a.id = b.id;

使⽤⼦查詢可以減少和資料庫的IO互動，也是⼀種常⽤的解決深分⻚的⽅法。

使⽤滾動查詢

每次接⼝都會傳回查詢出來的資料的最⼤的id（遊标），下⼀次查詢傳⼊這個遊标，服務端隻需要根據這個遊标，取出id⼤于這個遊标的n個資料即可。n為每⻚展示條數。

select * from table where org_code = xxxx and id > 0 limit 10;

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

SELECT id,dj_org_code,dj_sku_id,jd_sku_id,yn FROM table where

org_code = xxxx and id > 0 order by id asc limit 500

測試了沒問題後上線。觀察線上監控穩定。本以為⾼枕⽆憂的時候，⼀周之後，資料庫再次出現了⼤量的慢查詢，資料庫CPU報警，觀察接⼝監控:

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

SELECT id,dj_org_code,dj_sku_id,jd_sku_id,yn FROM table where

org_code = xxxx and id > 0 order by id asc limit 500

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

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

最終解決方案

引用join

因為我們使⽤了主鍵索引進⾏排序，且查詢了不在索引樹隻在葉⼦節點中的字段。是以mysql認為主鍵索引更優，因為既可以排序，⼜不⽤回表，是以就使⽤主鍵索引最終導緻了全表掃描。

最終使⽤了先查詢ID（不查詢葉⼦節點字段保證使⽤索引），在通過join，使⽤查詢出來的ID來查詢對應的資料的SQL：

select a.id AS id,a.dj_org_code AS djOrgCode,a.dj_sku_id AS

djSkuId,a.jd_sku_id AS jdSkuId,a.yn AS yn from

table a join

(

SELECT id FROM table where org_code = xxxx and id > 0 order

by id asc limit 500

) t on a.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強制使⽤索引:

SELECT id,dj_org_code,dj_sku_id,jd_sku_id,yn FROM table force

index(idx_upc) where org_code = xxxx and id > 0 order by id asc limit

500

但是這種寫死了索引名稱的⽅式，如果以後修改了索引名，容易導緻安全隐患。

問題總結

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

分頁慢查詢導緻的事故，太坑了！