一、背景

慢查詢指的是數(shù)據(jù)庫中查詢時間超過了指定的閾值的SQL，這類SQL通常伴隨著執(zhí)行時間長、服務(wù)器資源占用高、業(yè)務(wù)響應(yīng)慢等負(fù)面影響。隨著攜程酒店業(yè)務(wù)的不斷擴(kuò)張，再加上大量的SQLServer轉(zhuǎn)MySQL項(xiàng)目的推進(jìn)，慢查詢的數(shù)量正在飛速增長，每日的報警量也居高不下，因此慢查詢的治理優(yōu)化已經(jīng)是刻不容緩，此文主要針對MySQL。

二、慢查詢治理實(shí)踐

1、SQL上線流程優(yōu)化

之前的流程發(fā)布比較快捷，但是隨著質(zhì)量差的SQL發(fā)布\遷移得越來越多，告警和回退數(shù)量也隨之變多，綜合下來，數(shù)據(jù)庫風(fēng)險方面不容樂觀，該流程需要優(yōu)化。

和舊流程相比，新增了一個SQLReview的環(huán)節(jié)，將潛在的慢查詢提前篩選出來優(yōu)化，確保上線的SQL質(zhì)量，在此流程保障下，所有上線到生產(chǎn)的SQL性能都能在DBA評估后的可控范圍內(nèi)，在研發(fā)提交審核后，會收到審批的事件單。

攜程目前是存在自動化review審核的平臺，但是由于酒店業(yè)務(wù)場景比較復(fù)雜，研發(fā)對于SQL的理解水平層次不齊，平臺給出的建議并不能做到面面俱到，因此還沒有被廣泛使用于流程中，僅作為一個參考。

2、理解查詢語句

要優(yōu)化慢查詢，首先要知道慢查詢是如何產(chǎn)生的，執(zhí)行計(jì)劃是怎么樣的，最后考慮如何去優(yōu)化查詢。

1）SQL流程及查詢優(yōu)化器

一條sql的執(zhí)行主要分成如圖幾個步驟：

• SQL語法的緩存查詢（QC）
• 語法解析（SQL的編寫、關(guān)鍵字的語法之類）
• 生成執(zhí)行計(jì)劃
• 執(zhí)行查詢
• 輸出結(jié)果

通常慢查詢都發(fā)生在“執(zhí)行查詢”這步，讀懂查詢計(jì)劃，可以有效地幫助我們分析SQL性能差的原因。

2）執(zhí)行計(jì)劃

在SQL前面加上EXPLAIN，就可以查看執(zhí)行計(jì)劃，計(jì)劃以“表”的形式展示：

具體字段含義可以參考MySQL官方的解釋，這里不多贅述。

3、優(yōu)化慢查詢

通過執(zhí)行計(jì)劃就可以定位到問題點(diǎn)，通?？梢苑譃檫@幾種常見的原因。

1）索引層面

①索引缺失

這個查詢由于缺少name字段索引，產(chǎn)生了全表掃描：

select * from hotel where name=’xc’;

補(bǔ)上索引之后，提示使用到了索引。

②索引失效

如圖所示，索引失效的大致原因可以分為八類，這些場景通過查看執(zhí)行計(jì)劃都會發(fā)現(xiàn)產(chǎn)生type=ALL或者type=index的全表掃描。

• Like、or、非操作符、函數(shù)

explain select * from hotel where name like '%酒店%';explain select * from hotel where name like '%酒店%'or Bookable='T';explain select * from hotel where name  <>'酒店';explain select * from hotel where substring(name,1,2)='酒店';

• 參數(shù)類型不匹配

create table t1 (col1 varchar(3) primary key)engine=innodb default charset=utf8mb4;

t1表的col1為varchar類型，但是參數(shù)傳入的是數(shù)值類型，結(jié)果產(chǎn)生了隱形轉(zhuǎn)換，索引失效導(dǎo)致type=index的全表掃描。

• 聯(lián)合索引

Where條件不符合“最左匹配原則”，則索引會失效。

alter table hotel add index idx_hotelid_name_isdel(hotelid,name,status);

以下條件均可以命中聯(lián)合索引：

explain select * from hotel where hotelid=10000 and name='ctrip' and status='T';explain select * from hotel where hotelid=10000 and name='ctrip';explain select * from hotel where hotelid=10000;

但是以下條件無法使用到聯(lián)合索引：

explain select * from hotel where name='ctrip' and status='T';explain select * from hotel where name='ctrip';explain select * from hotel where status='T';

• 數(shù)據(jù)分布和數(shù)據(jù)量

索引字段的數(shù)據(jù)分布不均勻，表數(shù)據(jù)量過小的情況下，MYSQL查詢優(yōu)化器可能認(rèn)為返回的數(shù)據(jù)量本身就很多，通過索引掃描并不能減少多少開銷，此時選擇全表掃描的權(quán)重會提高很多。

③查詢不帶where條件

不帶where條件直接查詢\修改全表是很危險的操作，表數(shù)據(jù)量夠大的話，盡量拆分成多批次操作。

優(yōu)化中遇到的案例：

某天發(fā)現(xiàn)有一臺DB服務(wù)器IO異常，服務(wù)器鏈接開始堆積，引發(fā)了大量應(yīng)用報錯

監(jiān)控顯示此時repl延遲已經(jīng)有25分鐘，集群幾乎處于無高可用狀態(tài)，非常的危險。

登陸服務(wù)器排查后發(fā)現(xiàn)有一條全表刪除的SQL在通過JOB系統(tǒng)跑，該表的數(shù)據(jù)量很大：

-tarpresqls "delete from XXXXXX"

最后緊急Kill這條SQL后恢復(fù)正常，直接在生產(chǎn)刪除全表是很危險的操作。

④強(qiáng)制使用索引

MySQL中存在force index()、ignore index（）方式來強(qiáng)制使用/忽略特定的索引。

這種方式可能會導(dǎo)致執(zhí)行計(jì)劃選擇不到最優(yōu)的索引，從而導(dǎo)致計(jì)劃走偏。

⑤性能差索引的Index Merge

Index merge方法可以對同一個表使用多個索引分別進(jìn)行條件掃描，檢索多個范圍掃描并將結(jié)果合并為一個。

但是，當(dāng)遇到如圖2個索引字段分布都很差的情況時（status與bookable的區(qū)分度都很低），2個索引的結(jié)果集存在大量數(shù)據(jù)需要merge，性能就會變得很糟糕。

2）SQL頻率

• 業(yè)務(wù)代碼while、for循環(huán)的結(jié)束條件不正確，導(dǎo)致模塊內(nèi)產(chǎn)生死循環(huán)
• 業(yè)務(wù)邏輯本身存在高并發(fā)場景，例如秒殺、短期促銷活動、直播帶貨等
• 通過定時JOB循環(huán)拉取全量數(shù)據(jù)，但是循環(huán)的并發(fā)節(jié)奏控制不到位
• 緩存被擊穿、業(yè)務(wù)代碼發(fā)布后緩存失效等原因，導(dǎo)致大量請求直接打到了db

3）寫法不規(guī)范

①分頁寫法

最常見的分頁寫法就是使用limit，在分頁查詢時，我們會在 LIMIT 后面?zhèn)鲀蓚€參數(shù)，一個是偏移量(offset)，一個是獲取的條數(shù)(limit)。當(dāng)偏移量很小時，查詢速度很快，但是隨著 offset 變大時，查詢速度會越來越慢。

MySQL Limit 語法格式：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

例如下列分頁查詢：

當(dāng)limit只有0，10時，執(zhí)行還是很快，但是隨著offset增加，可以看到深度分頁的情況下，分頁越深，掃描的行數(shù)就越多，性能也就越來越差了。

explain select * from testlimittable order by id limit 1000, 10;explain select * from testlimittable order by id limit 10000, 10;explain select * from testlimittable order by id limit 20000, 10;explain select * from testlimittable order by id limit 30000, 10;explain select * from testlimittable order by id limit 40000, 10;explain select * from testlimittable order by id limit 50000, 10;explain select * from testlimittable order by id limit 60000, 10;

*：警惕通過分頁寫法來實(shí)現(xiàn)循環(huán)分批的邏輯，limit深分頁實(shí)現(xiàn)不了將大量數(shù)據(jù)拆分成若干小份的效果

分批可以采用分段拉取減少掃描的行數(shù)，如果分段拉取不連續(xù)的話可以傳入上一次拉取最大的值作為下一次的起始值：

②最大最小值寫法

由于where條件的字段數(shù)據(jù)分布問題，會導(dǎo)致max和min的查詢非常慢：

explain select max(id) from hotel where hotelid=10000 and status='T';

由于hotelid=10000的數(shù)據(jù)分布比較多，可以看到掃描數(shù)很高：

• 添加聯(lián)合索引

alter table hotel add index idx_hotelid_status(hotelid,status);

在索引覆蓋下，extra提示Select tables optimized away，這意味著在查詢執(zhí)行期間不需要讀取表，可以通過索引直接返回結(jié)果。

• 改寫為order by的方式

explain select id from hotel where hotelid=10000 and status='T' order by id desc limit 1;

掃描數(shù)很少，雖然是type=index的索引掃描，但是由于MYSQL對limit的優(yōu)化，實(shí)際上并不會全表掃描。

③排序聚合寫法

通常SQL在使用Group by及Order by后，會產(chǎn)生臨時表和文件排序操作。若查詢條件的數(shù)據(jù)量非常大，temporary和filesort都會產(chǎn)生額外的巨大開銷。

• 使用索引來滿足排序聚合

alter table hotel add index idx_name_hotelid(name,hotelid);

此時MYSQL可以通過訪問索引來避免執(zhí)行filesort 及temporary操作

• 取消隱形排序

在某些情況下，Group by會默認(rèn)實(shí)現(xiàn)隱形排序，通過添加ORDER BY NULL可以取消這種隱形排序。

*注意從MySQL 8.0開始，不會再有這種情況了，因此不需要ORDER BY NULL寫法了

4）資源

①鎖資源等待

在讀寫很熱的表上，通常會發(fā)生鎖資源爭奪，從而導(dǎo)致慢查詢的情況。

• 謹(jǐn)慎使用for update查詢
• 增刪改盡量保證使用到索引
• 降低并發(fā)，避免對同一條數(shù)據(jù)進(jìn)行反復(fù)的修改

②網(wǎng)絡(luò)波動

往客戶端發(fā)送數(shù)據(jù)時發(fā)生網(wǎng)絡(luò)波動導(dǎo)致的慢查詢

③硬件配置

CPU利用率高，磁盤IO經(jīng)常滿載，導(dǎo)致慢查詢

三、總結(jié)

慢查詢治理是一個長期且漫長的過程，不應(yīng)等SQL超時報錯后才開始考慮優(yōu)化，從一開始就要建立完善的日?；鞒腆w系，才能有效的控制慢查詢的增長。

但是經(jīng)過長期優(yōu)化后發(fā)現(xiàn)，僅僅從數(shù)據(jù)庫層面優(yōu)化，并不能實(shí)現(xiàn)慢查詢完全“清零”，還有很多的痛點(diǎn)來自于業(yè)務(wù)邏輯和應(yīng)用層面本身。這也需要研發(fā)工程師著重優(yōu)化業(yè)務(wù)邏輯、應(yīng)用策略，并加強(qiáng)數(shù)據(jù)庫培訓(xùn)，在編寫SQL時切勿過于隨意，貪圖省事，否則事后再優(yōu)化會變得相當(dāng)困難。