大促備戰，最大的隱患項之一就是慢SQL，對于服務平穩運行帶來的破壞性最大，也是日常工作中經常帶來整個應用抖動的最大隱患，在日常開發中如何避免出現慢SQL，出現了慢SQL應該按照什么思路去解決是我們必須要知道的。本文主要介紹對于慢SQL的排查、解決思路，通過一個個實際的例子深入分析總結，以便更快更準確的定位并解決問題。

一、解決步驟

step1、觀察SQL

出于一些歷史原因有的SQL查詢可能非常復雜，需要同時關聯非常多的表，使用一些復雜的函數、子查詢，這樣的SQL在項目初期由于數據量比較少，不會對數據庫造成較大的壓力，但是隨著時間的積累以及業務的發展，這些SQL慢慢就會轉變為慢SQL，對數據庫的性能產生一定的影響。

對于這樣的SQL，建議先了解業務場景，梳理關聯關系，嘗試將SQL拆解為幾個簡單的小SQL，在內存中關聯組合。

step2、分析問題

大家在分析慢SQL時最常用的工具肯定是explain語句，如下是explain語句的執行輸出。

一般情況下我們最需要關注的指標有type、possible_keys、key、rows、extra幾項。

type為連接類型，有如下幾種取值，性能從好到壞排序如下：

system：該表只有一行（相當于系統表），system是const類型的特例

const：針對主鍵或唯一索引的等值查詢掃描, 最多只返回一行數據. const 查詢速度非常快, 因為它僅僅讀取一次即可

eq_ref：當使用了索引的全部組成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才會使用該類型，性能僅次于system及const。

ref：當滿足索引的最左前綴規則，或者索引不是主鍵也不是唯一索引時才會發生。如果使用的索引只會匹配到少量的行，性能也是不錯的。

TIPS

最左前綴原則，指的是索引按照最左優先的方式匹配索引。比如創建了一個組合索引(column1, column2, column3)，那么，如果查詢條件是：

WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用該索引；

WHERE column2 = 2、WHERE column1 = 1 AND column3 = 3就無法匹配該索引。

fulltext：全文索引

ref_or_null：該類型類似于ref，但是MySQL會額外搜索哪些行包含了NULL。這種類型常見于解析子查詢

index_merge：此類型表示使用了索引合并優化，表示一個查詢里面用到了多個索引

unique_subquery：該類型和eq_ref類似，但是使用了IN查詢，且子查詢是主鍵或者唯一索引。例如：

index_subquery：和unique_subquery類似，只是子查詢使用的是非唯一索引。

range：范圍掃描，表示檢索了指定范圍的行，主要用于有限制的索引掃描。比較常見的范圍掃描是帶有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。

index：全索引掃描，和ALL類似，只不過index是全盤掃描了索引的數據。當查詢僅使用索引中的一部分列時，可使用此類型。有兩種場景會觸發：

如果索引是查詢的覆蓋索引，并且索引查詢的數據就可以滿足查詢中所需的所有數據，則只掃描索引樹。此時，explain的Extra 列的結果是Using index。index通常比ALL快，因為索引的大小通常小于表數據。

按索引的順序來查找數據行，執行了全表掃描。此時，explain的Extra列的結果不會出現Uses index。

ALL：全表掃描，性能最差。

possible_keys

展示當前查詢可以使用哪些索引，這一列的數據是在優化過程的早期創建的，因此有些索引可能對于后續優化過程是沒用的。

key

表示MySQL實際選擇的索引，重點需要注意Using filesort和Using temporary，前者代表無法利用索引完成排序操作，數據較少時從內存排序，否則從磁盤排序，后者MySQL需要創建一個臨時表來保存結果。

通過EXPLAIN可以初步定位出SQL是否使用索引，使用的索引是否正確，排序是否合理、索引列區分度等情況，通過這些基本就可以定位出絕大部分問題。

step3、指定方案

若無法從SQL本身解決可以根據業務場景和數據分布情況等因素合理制定修改方案。

二、案例展示

1、本SQL主要存在兩個問題，一個是查詢結果數據量較大，大約2W條數據，其次就是根據非索引字段oil_gun_price排序，造成filesort。有兩種修改選擇，一種是改造為分頁查詢，根據id升序排序，根據id偏移避免深分頁的問題，另外就是直接獲取符合條件的全量數據，不指定排序方式，然后在內存中排序即可。像這樣的場景盡量不要使用數據庫進行排序，除非可以直接利用索引進行排序，不然盡量選擇一次性或者分頁的方式將所有數據加載到內存后再進行排序。

SELECT gs.id,
       gs.gas_code,
       gs.tpl_gas_code,
       gs.gas_name,
       gs.province_id,
       gs.province_name,
       gs.city_id,
       gs.city_name,
       gs.county_id,
       gs.county_name,
       gs.town_id,
       gs.town_name,
       gs.detail_address,
       gs.banner_image,
       gs.logo_image,
       gs.longitude,
       gs.latitude,
       gs.oil_gun_serials,
       gs.gas_labels,
       gs.status,
       gs.source,
       gp.oil_number,
       gp.oil_gun_price
FROM fi_club_oil_gas gs
LEFT JOIN fi_club_oil_gas_price gp ON gs.gas_code = gp.gas_code
WHERE oil_number = 95
  AND status = 1
  AND gs.yn = 1
  AND gp.yn=1
ORDER BY gp.oil_gun_price ASC;

2、本SQL主要的問題在于在關聯查詢中使用了子查詢進行拼接，子查詢中條件較少，相當于先執行了一次全表掃描，將第一次查詢的結果加載到內存中再去執行關聯，查詢時長2.63秒，是比較常見的導致慢SQL的原因，應該盡量避免使用，這里選擇子查詢改為關聯查詢，最后執行時長0.71秒

SELECT count(0)
FROM trans_scheduler_base tsb
INNER JOIN
  (SELECT scheduler_code,
          vehicle_number,
          vehicle_type_code
   FROM trans_scheduler_calendar
   WHERE yn = 1
   GROUP BY scheduler_code) tsc ON tsb.scheduler_code = tsc.scheduler_code
WHERE tsb.type = 3
  AND tsb.yn = 1;


----------修改后--------------
SELECT count(distinct(tsc.scheduler_code))
FROM trans_scheduler_base tsb
LEFT JOIN trans_scheduler_calendar tsc ON tsb.scheduler_code = tsc.scheduler_code
WHERE tsb.type = 3
  AND tsb.yn = 1
  AND tsc.yn=1

3、本SQL比較典型，是非常容易被忽視但又經常出現的慢SQL。SQL中carrier_code和trader_code都有索引，但是最后使用了update_time索引，這是由于MYSQL優化器優化后的結果，可能導致實際執行時使用的索引跟預想的不一樣，這種SQL常見于在使用共用的查詢SQL，實際上很多情況下并不能完全適用，例如排序方式，查詢字段，返回條數等等，因此還是建議不同的業務邏輯使用自己單獨定義的SQL。解決方式可以使用force_index根據情況指定索引或者修改排序方式

SELECT id,
       carrier_name,
       carrier_code,
       trader_name,
       trader_code,
       route_type_name,
       begin_province_name,
       begin_city_name,
       begin_county_name,
       end_province_name,
       end_city_name,
       end_county_name
FROM carrier_route_config
WHERE yn = 1
  AND carrier_code ='C211206007386'
  AND trader_code ='010K1769496'
ORDER BY update_time DESC
LIMIT 10;

對于 limit N 帶有 group by ，order by 的 SQL 語句 (order by 和 group by 的字段有索引可以使用)，MySQL 優化器會盡可能選擇利用現有索引的有序性，減少排序--這看起來是 SQL 的執行計劃的最優解，但是實際上效果可能會南轅北轍，相信大家都遇到過很多案例中 SQL 執行計劃選擇 order by id 的索引進而導致全表掃描，而不是利用 where 條件中的索引查找過濾數據，這樣就可能導致查詢很低效(當然查詢也可能很高效，這個跟表中數據的具體分布有關)

order by limit 優化能起到正面作用的前提是，首先假設有序索引和無序索引是不相關的，其次假設數據是均勻分布的。

這兩個假設是估算通過排序索引來訪問cost 的前提(但是現實生產環境中這兩個假設在絕大多數場景中都是不成立的，所以就造成多數場景下索引選擇錯誤)，有可能會遇到通過條件索引過濾執行時間為幾十毫秒，但是通過索引排序掃描耗時1小時的情況，可以認為是MySQL的一個bug。

4、SQL中的limit也是經常導致慢SQL的原因之一，當對SQL使用了limit進行限制時，如果SQL使用的limit限制大于剩余的總條數，并且使用的索引條件不能很好的利用上有序的特性，那么MYSQL很可能會進行全表掃描。例如下面這個SQL，SQL在執行過程中使用了create_time索引，但是條件中沒有create_time作為條件，而SQL結果總條數為6，小于此時limit的結果10，因此MYSQL進行了全表掃描，耗時2.19秒，而當將limit改為6時，SQL執行時長為0.01秒，因為當MYSQL在查詢到6條滿足條件的結果時就直接返回了，不會再進行全表掃描。因此，當分頁查詢的數據已經不滿一頁的情況下，最好手動設置limit參數。

SELECT cva.id,
       cva.carrier_vehicle_approval_code,
       dsi.driver_erp,
       d.driver_name,
       cva.vehicle_number,
       cva.vehicle_type,
       cva.vehicle_kind,
       cva.fuel_type,
       cva.audit_user_code,
       dsi.driver_id,
       cva.operate_type,
       dsi.org_code,
       dsi.org_name,
       dsi.prov_code,
       dsi.prov_name,
       dsi.area_code,
       dsi.area_name,
       dsi.node_code,
       dsi.node_name,
       dsi.position_name,
       cva.create_user_code,
       cva.audit_status,
       cva.create_time,
       cva.audit_time,
       cva.audit_reason,
       d.jd_pin,
       d.call_source,
       cv.valid_status
FROM driver_staff_info dsi
INNER JOIN carrier_vehicle_approval cva ON cva.driver_id = dsi.driver_id
INNER JOIN driver d ON dsi.driver_id = d.driver_id
INNER JOIN carrier_vehicle_info cv ON cv.vehicle_number = cva.vehicle_number
WHERE dsi.yn = 1
  AND d.yn = 1
  AND cva.yn = 1
  AND cv.yn = 1
  AND dsi.org_code = '3'
  AND dsi.prov_code = '021S002'
  AND cva.carrier_code = 'C230425013337'
  AND cva.yn = 1
  AND cva.audit_status = 0
  AND d.call_source IN ('kuaidi',
                        'kuaiyun')
ORDER BY cva.create_time DESC
LIMIT 10

5、如下SQL表關聯過多，導致數據庫加載的數據量比較大，可以根據實際情況選擇先查出來一張表的數據作為基礎數據，再根據連表條件把剩下的字段填充上。數據量較大的表不建議關聯過多表，可以通過適當冗余字段或者加工寬表代替。

SELECT blsw.bid_line_code,
         blsw.bid_bill_code,
         blsw.bid_line_name,
         blsw.step_code,
         blsw.step_type,
         blsw.step_type_name,
         blsw.step_weight,
         blsw.step_weight_scale,
         blsw.block_price,
         blsw.max_weight_flag,
         blsw.id,
         blsw.need_quote_price,
         bbs.step_item_code,
         bbs.step_item_name,
         bbs.step_seq,
         bl.bid_line_seq
FROM bid_line_step_weight blsw
LEFT JOIN bid_bill_step bbs
    ON blsw.bid_bill_code = bbs.bid_bill_code
        AND blsw.step_code = bbs.step_code
        AND blsw.step_type = bbs.step_type
LEFT JOIN bid_line bl
    ON blsw.bid_line_code = bl.bid_line_code
        AND blsw.bid_bill_code = bl.bid_bill_code
WHERE blsw.yn = 1
        AND bbs.yn = 1
        AND bl.yn=1
        AND blsw.bid_bill_code = 'BL230423051192'; 

6、本SQL使用update_time作為時間范圍索引，需要注意是否存在熱數據過于集中的問題，導致查詢數據量非常大，排序條件比較復雜，無法直接通過SQL優化解決。一方面需要先解決熱數據過于集中的問題，一方面需要根據業務場景優化，比如增加一些默認條件以縮減數據量。

SELECT r.id,
         r.carrier_code,
         r.carrier_name,
         r.personal_name,
         r.status,
         r.register_org_name,
         r.register_org_code,
         r.register_city_name,
         r.verify_status,
         r.cancel_time,
         r.reenter_time,
         r.verify_user_code,
         r.data_source,
         r.sign_contract_flag,
         r.register_time,
         r.update_time,
         r.promotion_erp,
         r.promotion_name,
         r.promotion_pin,
         r.board_time,
         r.sync_basic_status,
         r.personal_verify_result,
        r.cert_verify_result,
        r.qualify_verify_result,
        r.photo_verify_result,
         d.jd_pin,
         d.driver_id,
         v.vehicle_number,
         v.vehicle_type,
         v.vehicle_length,
         r.cancellation_code ,
         r.cancellation_remarks
FROM carrier_resource r
LEFT JOIN carrier_driver d
    ON r.carrier_code = d.carrier_code
LEFT JOIN carrier_vehicle v
    ON r.carrier_code = v.carrier_code
WHERE r.update_time >= '2023-03-26 0000'
        AND r.update_time <= '2023-04-02 0000'
        AND r.yn = 1
        AND v.yn = 1
        AND d.yn = 1
        AND d.status != -1
        AND IFNULL(r.carrier_individual_type,'') != '2'
ORDER BY  (case r.verify_status
    WHEN 30 THEN
    1
    WHEN 20 THEN
    2
    WHEN 25 THEN
    3
    WHEN 35 THEN
    4
    WHEN 1 THEN
    5
    ELSE 6 end), r.update_time desc, if((v.driving_license_time IS null
        AND d.driver_license_time IS null), 0, 1) desc, if(((v.driving_license_time IS NOT null
        AND v.driving_license_time < NOW())
        OR (d.driver_license_time IS NOT null
        AND d.driver_license_time < NOW())), 2, 0) DESC LIMIT 10;

實際開發過程中還有許多從SQL本身不好優化的場景，比如查詢數據加載過多、表數據量過大、數據傾斜嚴重等等，盡量根據業務場景進行一些必要的保護措施限制，在不影響業務的情況下尋找替代方案，例如使用ES進行查詢，不過還是需要根據實際的場景選擇不同的方式解決。

7、對于一些較大數據量的表，在進行分頁查詢的時候其實很快就能返回結果，但是在進行分頁count總條數時往往很慢，這是因為在分頁查詢時會有pageSize的限制，當MYSQL查詢到滿足條數的數據后就會直接返回，而在進行count時則會根據條件全表查詢，當條件包含的數據量過大時就會限制SQL的性能。這種情況下建議一方面將分頁邏輯重寫，分離count和selectList，可以考慮應用ES作為count數據來源，或在某些條件下如果已存在總條數則不再count，減少分頁count的次數；另一方面限制分頁深度避免出現深分頁。

三、總體優化原則

創建合適的索引

減少不必要訪問的列

使用覆蓋索引

語句改寫

數據結轉

選擇合適的列進行排序

適當的列冗余

SQL拆分

適當應用ES

編輯：黃飛