一、背景

慢查詢指的是數(shù)據(jù)庫(kù)中查詢時(shí)間超過(guò)了指定的閾值的SQL，這類(lèi)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í)踐

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

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

和舊流程相比，新增了一個(gè)SQLReview的環(huán)節(jié)，將潛在的慢查詢提前篩選出來(lái)優(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)給出的建議并不能做到面面俱到，因此還沒(méi)有被廣泛使用于流程中，僅作為一個(gè)參考。

2、理解查詢語(yǔ)句

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

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

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

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

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

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

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

具體字段含義可以參考MySQL官方的解釋?zhuān)@里不多贅述。

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

通過(guò)執(zhí)行計(jì)劃就可以定位到問(wèn)題點(diǎn)，通常可以分為這幾種常見(jiàn)的原因。

1）索引層面

①索引缺失

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

select * from hotel where name=’xc’;

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

②索引失效

如圖所示，索引失效的大致原因可以分為八類(lèi)，這些場(chǎng)景通過(guò)查看執(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ù)類(lèi)型不匹配

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

t1表的col1為varchar類(lèi)型，但是參數(shù)傳入的是數(shù)值類(lèi)型，結(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;

但是以下條件無(wú)法使用到聯(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ù)量過(guò)小的情況下，MYSQL查詢優(yōu)化器可能認(rèn)為返回的數(shù)據(jù)量本身就很多，通過(guò)索引掃描并不能減少多少開(kāi)銷(xiāo)，此時(shí)選擇全表掃描的權(quán)重會(huì)提高很多。

③查詢不帶where條件

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

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

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

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

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

-tarpresqls "delete from XXXXXX"

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

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

MySQL中存在force index()、ignore index（）方式來(lái)強(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)景，例如秒殺、短期促銷(xiāo)活動(dòng)、直播帶貨等
• 通過(guò)定時(shí)JOB循環(huán)拉取全量數(shù)據(jù)，但是循環(huán)的并發(fā)節(jié)奏控制不到位
• 緩存被擊穿、業(yè)務(wù)代碼發(fā)布后緩存失效等原因，導(dǎo)致大量請(qǐng)求直接打到了db

3）寫(xiě)法不規(guī)范

①分頁(yè)寫(xiě)法

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

MySQL Limit 語(yǔ)法格式：

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

例如下列分頁(yè)查詢：

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

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;

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

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

②最大最小值寫(xiě)法

由于where條件的字段數(shù)據(jù)分布問(wèn)題，會(huì)導(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í)行期間不需要讀取表，可以通過(guò)索引直接返回結(jié)果。

• 改寫(xiě)為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ì)全表掃描。

③排序聚合寫(xiě)法

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

• 使用索引來(lái)滿足排序聚合

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

此時(shí)MYSQL可以通過(guò)訪問(wèn)索引來(lái)避免執(zhí)行filesort 及temporary操作

• 取消隱形排序

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

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

4）資源

①鎖資源等待

在讀寫(xiě)很熱的表上，通常會(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利用率高，磁盤(pán)IO經(jīng)常滿載，導(dǎo)致慢查詢

三、總結(jié)

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

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