一、背景
二、發現
三、定位
四、分析
- 4.1 索引層面分析
- 4.2 業務層面分析
五、解決
- 5.1 SQL優化
  - 5.1.1索引優化
  - 5.1.2 子查詢優化
  - 5.1.3 分頁優化
  - 5.1.4 Using filesort檔案排序優化
    - 解決
- 5.2 業務改造
  - 5.2.1 總量顯示優化
  - 5.2.1 關聯表優化
- 5.3 減少資料
總結

一、背景

??從業務的角度來看：慢SQL會導致產品用戶體驗差，會減低用戶對產品的好感度，
??從資料庫的角度來看：慢SQL會影響資料庫的性能，每個SQL執行都需要消耗一定的I/O資源，假設總資源是100，有一條慢SQL占用了30的資源共計1分鐘，那么在這1分鐘時間內，其他SQL能夠分配的資源總量就是70，如此回圈，當資源分配完的時候，所有新的SQL執行將會排隊等待，

二、發現

??在治理慢SQL前我們需要知道哪些SQL是慢SQL，即明確治理的物件，MySQL本身提供了慢查詢日志，當SQL耗時超過指定閾值的時候，會將SQL記錄到慢查詢日志檔案中，用戶能夠從慢查詢日志檔案中提取出慢SQL，
??MySQL是可以動態開啟慢查詢日志，即線上的服務器沒有開啟慢日志，重啟后會失效，為防止線上業務受影響，可以先這樣修改，同時將my.cnf組態檔補上配置項即可，

查看配置
- slow_query_log 是否啟用慢查詢日志
- long_query_time 慢查詢閾值
- slow_query_log_file 慢查詢日志檔案slow.log位置
```
show VARIABLES like '%query%';
```
開啟慢查詢日志

??MySQL資料庫默認不啟動慢查詢日志，需要手動設定，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日志會或多或少帶來一定的性能影響

# 開啟慢查詢日志
set global slow_query_log=ON;

# 慢查詢閾值
set global long_query_time=1;

# 慢查詢日志檔案
set global slow_query_log_file=/tmp/mysql_slow.log

三、定位

??我們通過慢查詢日志提取出慢SQL，將這些慢SQL按不同的應用進行區分并整理一份檔案，再定位到對應應用的代碼，在檔案中記錄慢SQL應用在什么業務中，運行在什么場景中（定時任務、在線實時查詢等），

四、分析

??接下來是根據整理的檔案，對這些慢SQL做一些分析，找出慢SQL產生的原因，

4.1 索引層面分析

??使用explain命令輸出SQL的執行計劃，透過執行計劃我們可以了解慢SQL的執行細節，

Mysql中的執行計劃各列說明，

id: 按照sql語法決議后分層后的編號
type：執行計劃中指定表使用的訪問路徑方式，

??這是個非常重要的欄位，也是我們判斷一個SQL執行效率的主要依據（以下只列舉常見的幾種），
??依次從最優到最差分別為：system > const > eq_ref > ref > range > index > ALL

?? - system：從系統表讀一行，這是const聯接型別的一個特例，
?? - const：表最多有一個匹配行,它將在查詢開始時被讀取，const用于常數值比較PRIMARY KEY或UNIQUE索引的所有部分

??- eq_ref：它用在一個索引的所有部分被聯接使用并且索引是UNIQUE或PRIMARY KEY，

?? - ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行，
- ref是我們日常開發中較為常見的情況，也是原則上期望要達到的級別，查詢命中到索引，

??- range：索引范圍掃描

?? - index：只掃描索引樹，不需要回表查詢，在這種情況下，explain 的 Extra 列的結果是 Using index

?? - all：全表掃描，

possible_keys：查找表中的行時可選擇的索引，
key：顯示MySQL實際決定使用的索引，
key_len：顯示MySQL使用索引鍵的長度，就是此次查詢所選擇的索引長度有多少位元組，
ref：ref欄位標識哪些欄位或者常量被用來和key配合從表中查詢記錄出來，如果為NULL表示沒有
rows：該串列明MySQL估計要讀取并檢查的行數，需要注意的是，這個不是結果集里的行數，
filtered：它指回傳結果的行數（MySQL層where過濾生效的資料量）占需要掃描到的行數（rows列的值）的百分比，一般來說越高越好，越低證明查詢代價越高，
Extra：該列包含MySQL解決查詢的詳細資訊（以下只列舉常見的幾種），
- Using filesort：說明mysql會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，
- Using index：這個值重點強調了只需要使用索引就可以滿足查詢表的要求，不需要直接訪問表資料了，一般表示使用了覆寫索引，
- Using temporary：這個值表示使用了內部臨時表（基于記憶體的表），這種情況通常發生在查詢時包含了group by和order by子句，或者來自不同表的列使用了distinct，
- Using where：where條件查詢，通常using where表示優化器需要通過索引回表查詢資料

4.2 業務層面分析

根據具體的業務場景進行考慮

查詢條件是否都是必須的
查詢時間范圍可否縮短
表里面存在的一些大欄位，根據實際情況縮短欄位長度
- 如果不是每次都必須獲取的話，可以考慮從當前表拆出去，主表都是小欄位，子表維護大欄位，這樣效率會更高

五、解決

5.1 SQL優化

5.1.1索引優化

左前綴原則：索引了多個列時，查詢時必須從最左列開始，不能跳過，否則索引失效
在使用不等于符號時(!=，<>)會索引失效
使用is not null會索引失效，但is null 不會
like模糊查詢中以通配符開頭會索引失效
使用or時，左右兩邊的欄位都需要加上索引，否則索引失效
在索引列上使用函式會索引失效
避免隱式型別轉換-字串型別欄位不加單引號索引失效
有時候MySQL優化器選擇的索引不一定是最優的，可以通過FORCE INDEX(idx_order_id)強制要求走某個索引，當然，必須保證這個索引以后不能被洗掉，不然就是個BUG

5.1.2 子查詢優化

select something from user_table 
where id in (select user_id from order_table where xxx=yyyy);

??MySQL從5.7開始優化器對子查詢進行了優化，會自動轉換為join再執行，而對于5.7以下版本的MySQL 我們建議把子查詢改成join的方式：

 select a.something
 from user_table a, order_table b
 where a.id=b.user_id
 and b.xxx=yyyy;

5.1.3 分頁優化

??Limit中分頁查詢會隨著pageNo增大而變緩慢，通過子查詢+表連接解決
select * from mytbl order by id limit 100000,10 改進后的SQL陳述句如下：

select * from mytbl 
where id >= ( select id from mytbl order by id limit 100000,1 ) 
limit 10
# 或者
select * from mytbl 
inner join (select id from mytbl order by id limit 100000,10) as tmp 
on tmp.id=ori.id;

5.1.4 Using filesort檔案排序優化

??orders建立了idx_ppo_created_at索引，使用EXPLAIN進行分析

EXPLAIN
SELECT id,
       temp_id,
       pos_id,
       `type`,
       member_id,
       temp_status,
       money_amount,
       trans_amount,
       return_trans_amount,
       coupon_id,
       cash_points,
       is_cancel,
       is_auto_cancel,
       is_compensate,
       is_multi_equity,
       company_id,
       store_id,
       store_type,
       source_orders,
       return_order_id,
       created_at,
       updated_at
FROM orders
WHERE 1 = 1
  AND `created_at` >= '2021-08-27 00:00:00'
  AND `created_at` <= '2021-08-27 23:59:59'
  AND `type` = 0
  AND `is_cancel` = 0
  AND `temp_status` = 0
ORDER BY id DESC
LIMIT 0,100

??我們可以看到Extra列出現了Using filesort，說明MySQL會對資料使用一個外部的索引排序, 而不是按照表內索引順序進行讀取，

解決

??因為索引的葉子節點資料是根據 created_at 有序的，我們可以利用這一點來避免排序，
??我們將 ORDER BY id DESC 換成 ORDER BY created_at DESC，查看執行計劃，Using filesort 已消失：

5.2 業務改造

??如果SQL本身的性能已經到達極限了，但是耗時仍然很長，這時候，我們可以業務角度著手，看看在業務上能不能做一些變通、妥協，

5.2.1 總量顯示優化

??如上圖所示，我們在做分頁時，頁底都會展示符合條件的記錄總數，以及分頁頁數，資料量少的時候，不會帶來性能問題，但如果資料量較大，這個計算總量的count() 本身就不會太快，再加上每次打開頁面都要計算一次的話，那這樣會就帶性能問題了，同時也會拖慢頁面打開速度，
??如果業務上允許，當資料量少時，精確顯示，當資料量過大后，用戶對真實資料不敏感時，那我們就可以通過 1000+ 等模糊的方式進行初步顯示，減少不必要的掃描，同時也可以讓用戶首次打開時達到加速效果，
原SQL：select count() from table where xxx=yyy;
調整后：select count(*) from (select id from table where xxx=yyy limit 1000);

5.2.1 關聯表優化

??由于業務很復雜，某條SQL關聯了很多表，導致表關聯時的匹配耗時很長，這時候可以看看能不能將多關聯SQL改成較少表的關聯，使用代碼方式進行關聯，但是會增加請求次數，

5.3 減少資料

??如果單表行數超過500萬行或者單表容量超過2GB，SQL再怎么優化還是會慢，這個時候就要做資料拆分，這屬于架構層面的變動，影響的面很大，除了慢SQL本身之外，其他的相關SQL也可能會被“波及”到，這種慢SQL治理的手段，能夠一定程度上解決慢SQL問題，減少作用資料的方式有：

垂直拆分
水平拆分
綜合拆分（垂直+水平）

總結

本文介紹了實際作業中慢SQL治理的方法論
1、發現：如何發現慢SQL，
2、定位：如何定位到慢SQL寫在哪，
3、分析：遇到慢SQL時的分析思路，
4、解決：慢SQL的解決思路，

??SQL優化本身是一個比較復雜的問題，上面所列舉的，只是部份優化的案例，但所有優化的思路都是盡可能的減少SQL在執行中程序中掃描資料塊的次數，只要遵從這一核心思想，SQL優化并不是什么太難的事情，
??以上就是今天要講的內容，本文是筆者實際治理程序中的一些總結和心得，如有不正之處，還請指正，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/447137.html

標籤：MySQL

上一篇：斑馬mc930的delphiandroid設備序列號

下一篇：[MySQL]MySQL8.0的一些注意事項以及解決方案

慢SQL治理方法論