?作者簡(jiǎn)介 | xuqi，攜程資深數(shù)據(jù)庫工程師，關(guān)注MySQL、分布式數(shù)據(jù)庫的優(yōu)化、運(yùn)維；

              潘達(dá)鳴，攜程資深數(shù)據(jù)庫工程師，關(guān)注數(shù)據(jù)庫性能優(yōu)化、高可用性領(lǐng)域；

              康男，攜程數(shù)據(jù)庫專家，關(guān)注數(shù)據(jù)庫性能調(diào)優(yōu)領(lǐng)域。

一、背景

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

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

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

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

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

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

2.2 理解查詢語句

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

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

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

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

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

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

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

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

2.3 優(yōu)化慢查詢

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

(1) 索引層面

索引缺失

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

select * from hotel where name=’xc’;

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

索引失效

如圖所示，索引失效的大致原因可以分為八類，這些場(chǎng)景通過查看執(zhí)行計(jì)劃都會(huì)發(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條件不符合“最左匹配原則”，則索引會(huì)失效。

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ù)量本身就很多，通過索引掃描并不能減少多少開銷，此時(shí)選擇全表掃描的權(quán)重會(huì)提高很多。

查詢不帶where條件

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

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

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

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

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

-tarpresqls "delete from XXXXXX"

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

強(qiáng)制使用索引

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

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

性能差索引的Index Merge

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

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

(2) SQL頻率

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

(3) 寫法不規(guī)范

分頁寫法

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

MySQL Limit 語法格式：

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

例如下列分頁查詢：

當(dāng)limit只有0，10時(shí)，執(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ù)分布問題，會(huì)導(dǎo)致max和min的查詢非常慢：

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

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

1、添加聯(lián)合索引

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

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

2、改寫為order by的方式

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

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

排序聚合寫法

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

a.  使用索引來滿足排序聚合

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

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

b.  取消隱形排序?

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

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

(4)資源

鎖資源等待

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

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

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

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

硬件配置

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

總結(jié)

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

但是經(jīng)過長(zhǎ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時(shí)切勿過于隨意，貪圖省事，否則事后再優(yōu)化會(huì)變得相當(dāng)困難。