攜程酒店慢查詢治理之路

作者：xuqi 潘達鳴等 2022-10-21 10:40:08

隨著攜程酒店業務的不斷擴張，再加上大量的SQLServer轉MySQL項目的推進，慢查詢的數量正在飛速增長，每日的報警量也居高不下，因此慢查詢的治理優化已經是刻不容緩，此文主要針對MySQL。

?作者簡介 | xuqi，攜程資深數據庫工程師，關注MySQL、分布式數據庫的優化、運維；

潘達鳴，攜程資深數據庫工程師，關注數據庫性能優化、高可用性領域；

康男，攜程數據庫專家，關注數據庫性能調優領域。

一、背景

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

二、慢查詢治理實踐

2.1 SQL上線流程優化

之前的流程發布比較快捷，但是隨著質量差的SQL發布\遷移得越來越多，告警和回退數量也隨之變多，綜合下來，數據庫風險方面不容樂觀，該流程需要優化。

和舊流程相比，新增了一個SQLReview的環節，將潛在的慢查詢提前篩選出來優化，確保上線的SQL質量，在此流程保障下，所有上線到生產的SQL性能都能在DBA評估后的可控范圍內，在研發提交審核后，會收到審批的事件單。

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

2.2 理解查詢語句

要優化慢查詢，首先要知道慢查詢是如何產生的，執行計劃是怎么樣的，最后考慮如何去優化查詢。

SQL流程及查詢優化器

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

SQL語法的緩存查詢（QC）
語法解析（SQL的編寫、關鍵字的語法之類）
生成執行計劃
執行查詢
輸出結果

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

執行計劃

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

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

2.3 優化慢查詢

通過執行計劃就可以定位到問題點，通常可以分為這幾種常見的原因。

(1) 索引層面

索引缺失

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


select * from hotel where name=’xc’;

補上索引之后，提示使用到了索引。

索引失效

如圖所示，索引失效的大致原因可以分為八類，這些場景通過查看執行計劃都會發現產生type=ALL或者type=index的全表掃描。

Like、or、非操作符、函數

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)='酒店';

參數類型不匹配

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

t1表的col1為varchar類型，但是參數傳入的是數值類型，結果產生了隱形轉換，索引失效導致type=index的全表掃描。

聯合索引

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

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

以下條件均可以命中聯合索引：

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;

但是以下條件無法使用到聯合索引：

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

數據分布和數據量

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

查詢不帶where條件

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

優化中遇到的案例：

某天發現有一臺DB服務器IO異常，服務器鏈接開始堆積，引發了大量應用報錯

監控顯示此時repl延遲已經有25分鐘，集群幾乎處于無高可用狀態，非常的危險。

登陸服務器排查后發現有一條全表刪除的SQL在通過JOB系統跑，該表的數據量很大:

-tarpresqls "delete from XXXXXX"

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

強制使用索引

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

這種方式可能會導致執行計劃選擇不到最優的索引，從而導致計劃走偏。

性能差索引的Index Merge

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

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

(2) SQL頻率

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

(3) 寫法不規范

分頁寫法

最常見的分頁寫法就是使用limit，在分頁查詢時，我們會在 LIMIT 后面傳兩個參數，一個是偏移量(offset)，一個是獲取的條數(limit)。當偏移量很小時，查詢速度很快，但是隨著 offset 變大時，查詢速度會越來越慢。

MySQL Limit 語法格式：

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

例如下列分頁查詢：

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

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;

*：警惕通過分頁寫法來實現循環分批的邏輯，limit深分頁實現不了將大量數據拆分成若干小份的效果

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

最大最小值寫法

由于where條件的字段數據分布問題，會導致max和min的查詢非常慢：

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

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

1、添加聯合索引

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

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

2、改寫為order by的方式

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

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

排序聚合寫法

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

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

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

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

b. 取消隱形排序?

在某些情況下，Group by會默認實現隱形排序，通過添加ORDER BY NULL可以取消這種隱形排序。

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

(4)資源

鎖資源等待

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

謹慎使用for update查詢
增刪改盡量保證使用到索引
降低并發，避免對同一條數據進行反復的修改

網絡波動

往客戶端發送數據時發生網絡波動導致的慢查詢

硬件配置

CPU利用率高，磁盤IO經常滿載，導致慢查詢

總結

慢查詢治理是一個長期且漫長的過程，不應等SQL超時報錯后才開始考慮優化，從一開始就要建立完善的日常化流程體系，才能有效的控制慢查詢的增長。

但是經過長期優化后發現，僅僅從數據庫層面優化，并不能實現慢查詢完全“清零”，還有很多的痛點來自于業務邏輯和應用層面本身。這也需要研發工程師著重優化業務邏輯、應用策略，并加強數據庫培訓，在編寫SQL時切勿過于隨意，貪圖省事，否則事后再優化會變得相當困難。

責任編輯：未麗燕來源：攜程技術