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

一、解決步驟

step1、觀察SQL

出于一些歷史原因有的SQL查詢可能非常復(fù)雜，需要同時關(guān)聯(lián)非常多的表，使用一些復(fù)雜的函數(shù)、子查詢，這樣的SQL在項目初期由于數(shù)據(jù)量比較少，不會對數(shù)據(jù)庫造成較大的壓力，但是隨著時間的積累以及業(yè)務(wù)的發(fā)展，這些SQL慢慢就會轉(zhuǎn)變?yōu)槁齋QL，對數(shù)據(jù)庫的性能產(chǎn)生一定的影響。

對于這樣的SQL，建議先了解業(yè)務(wù)場景，梳理關(guān)聯(lián)關(guān)系，嘗試將SQL拆解為幾個簡單的小SQL，在內(nèi)存中關(guān)聯(lián)組合。

step2、分析問題

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

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

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

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

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

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

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

TIPS

最左前綴原則，指的是索引按照最左優(yōu)先的方式匹配索引。比如創(chuàng)建了一個組合索引(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：此類型表示使用了索引合并優(yōu)化，表示一個查詢里面用到了多個索引

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

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

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

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

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

按索引的順序來查找數(shù)據(jù)行，執(zhí)行了全表掃描。此時，explain的Extra列的結(jié)果不會出現(xiàn)Uses index。

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

possible_keys

展示當前查詢可以使用哪些索引，這一列的數(shù)據(jù)是在優(yōu)化過程的早期創(chuàng)建的，因此有些索引可能對于后續(xù)優(yōu)化過程是沒用的。

key

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

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

step3、指定方案

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

二、案例展示

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

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主要的問題在于在關(guān)聯(lián)查詢中使用了子查詢進行拼接，子查詢中條件較少，相當于先執(zhí)行了一次全表掃描，將第一次查詢的結(jié)果加載到內(nèi)存中再去執(zhí)行關(guān)聯(lián)，查詢時長2.63秒，是比較常見的導(dǎo)致慢SQL的原因，應(yīng)該盡量避免使用，這里選擇子查詢改為關(guān)聯(lián)查詢，最后執(zhí)行時長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比較典型，是非常容易被忽視但又經(jīng)常出現(xiàn)的慢SQL。SQL中carrier_code和trader_code都有索引，但是最后使用了update_time索引，這是由于MYSQL優(yōu)化器優(yōu)化后的結(jié)果，可能導(dǎo)致實際執(zhí)行時使用的索引跟預(yù)想的不一樣，這種SQL常見于在使用共用的查詢SQL，實際上很多情況下并不能完全適用，例如排序方式，查詢字段，返回條數(shù)等等，因此還是建議不同的業(yè)務(wù)邏輯使用自己單獨定義的SQL。解決方式可以使用force_index根據(jù)情況指定索引或者修改排序方式

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 優(yōu)化器會盡可能選擇利用現(xiàn)有索引的有序性，減少排序--這看起來是 SQL 的執(zhí)行計劃的最優(yōu)解，但是實際上效果可能會南轅北轍，相信大家都遇到過很多案例中 SQL 執(zhí)行計劃選擇 order by id 的索引進而導(dǎo)致全表掃描，而不是利用 where 條件中的索引查找過濾數(shù)據(jù)，這樣就可能導(dǎo)致查詢很低效(當然查詢也可能很高效，這個跟表中數(shù)據(jù)的具體分布有關(guān))

order by limit 優(yōu)化能起到正面作用的前提是，首先假設(shè)有序索引和無序索引是不相關(guān)的，其次假設(shè)數(shù)據(jù)是均勻分布的。

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

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

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表關(guān)聯(lián)過多，導(dǎo)致數(shù)據(jù)庫加載的數(shù)據(jù)量比較大，可以根據(jù)實際情況選擇先查出來一張表的數(shù)據(jù)作為基礎(chǔ)數(shù)據(jù)，再根據(jù)連表條件把剩下的字段填充上。數(shù)據(jù)量較大的表不建議關(guān)聯(lián)過多表，可以通過適當冗余字段或者加工寬表代替。

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作為時間范圍索引，需要注意是否存在熱數(shù)據(jù)過于集中的問題，導(dǎo)致查詢數(shù)據(jù)量非常大，排序條件比較復(fù)雜，無法直接通過SQL優(yōu)化解決。一方面需要先解決熱數(shù)據(jù)過于集中的問題，一方面需要根據(jù)業(yè)務(wù)場景優(yōu)化，比如增加一些默認條件以縮減數(shù)據(jù)量。

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;

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

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

三、總體優(yōu)化原則

創(chuàng)建合適的索引

減少不必要訪問的列

使用覆蓋索引

語句改寫

數(shù)據(jù)結(jié)轉(zhuǎn)

選擇合適的列進行排序

適當?shù)牧腥哂?/p>

SQL拆分

適當應(yīng)用ES

編輯：黃飛