一、背景
慢查詢指的是資料庫中查詢時間超過了指定的閾值的SQL，這類SQL通常伴隨著執行時間長、服務器資源占用高、業務回應慢等負面影響，隨著攜程酒店業務的不斷擴張，再加上大量的SQLServer轉MySQL專案的推進，慢查詢的數量正在飛速增長，每日的報警量也居高不下，因此慢查詢的治理優化已經是刻不容緩，此文主要針對MySQL，
二、慢查詢治理實踐
之前的流程發布比較快捷，但是隨著質量差的SQL發布\遷移得越來越多，告警和回退數量也隨之變多，綜合下來，資料庫風險方面不容樂觀，該流程需要優化，
和舊流程相比，新增了一個SQLReview的環節，將潛在的慢查詢提前篩選出來優化，確保上線的SQL質量，在此流程保障下，所有上線到生產的SQL性能都能在DBA評估后的可控范圍內，在研發提交審核后，會收到審批的事件單，
攜程目前是存在自動化review審核的平臺，但是由于酒店業務場景比較復雜，研發對于SQL的理解水平層次不齊，平臺給出的建議并不能做到面面俱到，因此還沒有被廣泛使用于流程中，僅作為一個參考，
要優化慢查詢，首先要知道慢查詢是如何產生的，執行計劃是怎么樣的，最后考慮如何去優化查詢，
1）SQL流程及查詢優化器
一條sql的執行主要分成如圖幾個步驟：
-
SQL語法的快取查詢（QC）
-
語法決議（SQL的撰寫、關鍵字的語法之類）
-
生成執行計劃
-
執行查詢
-
輸出結果
通常慢查詢都發生在“執行查詢”這步，讀懂查詢計劃，可以有效地幫助我們分析SQL性能差的原因，
2）執行計劃
在SQL前面加上EXPLAIN，就可以查看執行計劃，計劃以“表”的形式展示：
具體欄位含義可以參考MySQL官方的解釋，這里不多贅述，
通過執行計劃就可以定位到問題點，通常可以分為這幾種常見的原因，
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的資料分布比較多，可以看到掃描數很高：
-
添加聯合索引
alter table hotel add index idx_hotelid_status(hotelid,status);
在索引覆寫下，extra提示Select tables optimized away，這意味著在查詢執行期間不需要讀取表，可以通過索引直接回傳結果，
-
改寫為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都會產生額外的巨大開銷，
-
使用索引來滿足排序聚合
alter table hotel add index idx_name_hotelid(name,hotelid);
此時MYSQL可以通過訪問索引來避免執行filesort 及temporary操作
-
取消隱形排序
在某些情況下，Group by會默認實作隱形排序，通過添加ORDER BY NULL可以取消這種隱形排序，
*注意從MySQL 8.0開始，不會再有這種情況了，因此不需要ORDER BY NULL寫法了
4）資源
①鎖資源等待
在讀寫很熱的表上，通常會發生鎖資源爭奪，從而導致慢查詢的情況，
-
謹慎使用for update查詢
-
增刪改盡量保證使用到索引
-
降低并發，避免對同一條資料進行反復的修改
②網路波動
往客戶端發送資料時發生網路波動導致的慢查詢
③硬體配置
CPU利用率高，磁盤IO經常滿載，導致慢查詢
三、總結
慢查詢治理是一個長期且漫長的程序，不應等SQL超時報錯后才開始考慮優化，從一開始就要建立完善的日常化流程體系，才能有效的控制慢查詢的增長，
但是經過長期優化后發現，僅僅從資料庫層面優化，并不能實作慢查詢完全“清零”，還有很多的痛點來自于業務邏輯和應用層面本身，這也需要研發工程師著重優化業務邏輯、應用策略，并加強資料庫培訓，在撰寫SQL時切勿過于隨意，貪圖省事，否則事后再優化會變得相當困難，
作者丨xuqi 潘達鳴 康男
本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/Ctrip-SQL-online-process-optimization_how-to-kill-slow-queries-from-the-source.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/542824.html
標籤：MySQL