大促備戰(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ù)庫造成較大的壓力，但是隨著時(shí)間的積累以及業(yè)務(wù)的發(fā)展，這些SQL慢慢就會(huì)轉(zhuǎn)變?yōu)槁齋QL，對(duì)數(shù)據(jù)庫的性能產(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語句，如下是explain語句的執(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 column1 = 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。有兩種修改選擇，一種是改造為分頁查詢，根據(jù)id升序排序，根據(jù)id偏移避免深分頁的問題，另外就是直接獲取符合條件的全量數(shù)據(jù)，不指定排序方式，然后在內(nèi)存中排序即可。像這樣的場(chǎng)景盡量不要使用數(shù)據(jù)庫進(jìn)行排序，除非可以直接利用索引進(jìn)行排序，不然盡量選擇一次性或者分頁的方式將所有數(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 語句 (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)分頁查詢的數(shù)據(jù)已經(jīng)不滿一頁的情況下，最好手動(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ù)庫加載的數(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 00:00:00'

AND r.update_time <= '2023-04-02 00:00:00'

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

WHEN 20 THEN

WHEN 25 THEN

WHEN 35 THEN

WHEN 1 THEN

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