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

一、解決步驟

step1、觀察SQL

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

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

step2、分析問題

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

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

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

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

const：針對(duì)主鍵或唯一索引的等值查詢掃描, 最多只返回一行數(shù)據(jù). const 查詢速度非常快, 因?yàn)樗鼉H僅讀取一次即可

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

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

TIPS

最左前綴原則，指的是索引按照最左優(yōu)先的方式匹配索引。比如創(chuàng)建了一個(gè)組合索引(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會(huì)額外搜索哪些行包含了NULL。這種類型常見于解析子查詢

index_merge：此類型表示使用了索引合并優(yōu)化，表示一個(gè)查詢里面用到了多個(gè)索引

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

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

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

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

如果索引是查詢的覆蓋索引，并且索引查詢的數(shù)據(jù)就可以滿足查詢中所需的所有數(shù)據(jù)，則只掃描索引樹。此時(shí)，explain的Extra 列的結(jié)果是Using index。index通常比ALL快，因?yàn)樗饕拇笮⊥ǔＰ∮诒頂?shù)據(jù)。

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

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

possible_keys

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

key

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

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

step3、指定方案

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

二、案例展示

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

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)查詢中使用了子查詢進(jìn)行拼接，子查詢中條件較少，相當(dāng)于先執(zhí)行了一次全表掃描，將第一次查詢的結(jié)果加載到內(nèi)存中再去執(zhí)行關(guān)聯(lián)，查詢時(shí)長(zhǎng)2.63秒，是比較常見的導(dǎo)致慢SQL的原因，應(yīng)該盡量避免使用，這里選擇子查詢改為關(guān)聯(lián)查詢，最后執(zhí)行時(shí)長(zhǎng)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)致實(shí)際執(zhí)行時(shí)使用的索引跟預(yù)想的不一樣，這種SQL常見于在使用共用的查詢SQL，實(shí)際上很多情況下并不能完全適用，例如排序方式，查詢字段，返回條數(shù)等等，因此還是建議不同的業(yè)務(wù)邏輯使用自己?jiǎn)为?dú)定義的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;

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

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

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

4、SQL中的limit也是經(jīng)常導(dǎo)致慢SQL的原因之一，當(dāng)對(duì)SQL使用了limit進(jìn)行限制時(shí)，如果SQL使用的limit限制大于剩余的總條數(shù)，并且使用的索引條件不能很好的利用上有序的特性，那么MYSQL很可能會(huì)進(jìn)行全表掃描。例如下面這個(gè)SQL，SQL在執(zhí)行過程中使用了create_time索引，但是條件中沒有create_time作為條件，而SQL結(jié)果總條數(shù)為6，小于此時(shí)limit的結(jié)果10，因此MYSQL進(jìn)行了全表掃描，耗時(shí)2.19秒，而當(dāng)將limit改為6時(shí)，SQL執(zhí)行時(shí)長(zhǎng)為0.01秒，因?yàn)楫?dāng)MYSQL在查詢到6條滿足條件的結(jié)果時(shí)就直接返回了，不會(huì)再進(jìn)行全表掃描。因此，當(dāng)分頁(yè)查詢的數(shù)據(jù)已經(jīng)不滿一頁(yè)的情況下，最好手動(dò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ù)庫(kù)加載的數(shù)據(jù)量比較大，可以根據(jù)實(shí)際情況選擇先查出來一張表的數(shù)據(jù)作為基礎(chǔ)數(shù)據(jù)，再根據(jù)連表?xiàng)l件把剩下的字段填充上。數(shù)據(jù)量較大的表不建議關(guān)聯(lián)過多表，可以通過適當(dāng)冗余字段或者加工寬表代替。

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

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

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

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

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

減少不必要訪問的列

使用覆蓋索引

語(yǔ)句改寫

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

選擇合適的列進(jìn)行排序

適當(dāng)?shù)牧腥哂?/p>

SQL拆分

適當(dāng)應(yīng)用ES

編輯：黃飛