基礎知識
慢SQL官方定義
If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file; Unit:Second.
阿裡雲控制台慢SQL參數
SQL優化原因與影響
•SQL優化原因：查詢效率低的SQL(慢SQL)會占用機器大量的CPU、記憶體和IO資源，影響正常業務。
•慢SQL現象/影響：SQL查詢緩慢、業務逾時、資料庫機器CPU/IO飙升、業務連接配接不上資料庫、現網故障等。
SQL語句執行過程
Optimizer查詢優化器方式
邏輯優化
優化器的邏輯優化，即根據關系代數規則，對SQL語句進行等價變化:
(1) 對投影、選擇等操作進行句式優化;
(2) 對條件表達式進行謂詞優化、條件化簡;
(3) 對連接配接語義進行外連接配接、嵌套連接配接的優化;
(4) 對集合、GROUP BY、ORDER BY等優化
(5) 子查詢優化、視圖重寫、語義優化
邏輯優化例子
條件化簡 :
select id,sfzh,address from t1 where 1=1 and name ='宸謙'; 子查詢優化 :
select * from t1 where a in (select a from t2); 優化器邏輯優化結果：
select t1.* from t1 join t2 on t1.a= t2.a; 實體優化
實體優化的總代價模型 Cost = CPU Cost + IO Cost
(1) CPU Cost：MySQL Server層，處理傳回記錄所花開銷。
CPU Cost = records / TIME_FOR_COMPARE =
records / 5，即每5條記錄的處理時間，作為 1 CPU Cost。
(2) IO Cost：存儲引擎層面，讀取頁面的IO開銷。
執行計劃
檢視
在SQL語句前加explain關鍵字可檢視SQL語句的執行計劃，
常用文法:
（1）explain extended（輸出更多擴充資訊）
（2）explain format=json （json格式輸出，可看到cost等資訊）
結果字段注解
案例分析
優化原則
優化總原則：業務側優化 > SQL 優化 > 資料庫+作業系統參數優化
SQL優化原則：高頻SQL收益 > 低頻SQL收益
業務側優化例子：
（1）某業務每隔1分鐘會并發幾十路去資料庫查詢A表(全表掃描)，造成其他業務響應逾時
（2）統計分析類SQL拆分，熱點資料緩存
慢SQL例子
SELECT
count(*) count
FROM
app_mst
LEFT JOIN app_profile ON app_mst.app_id = app_profile.app_id
LEFT JOIN app_apply_step ON app_mst.app_id = app_apply_step.app_id
WHERE
app_apply_step.is_exp = '0'; 執行計劃：
優化步驟
（1）檢視執行計劃:
在SQL前加上 explain extended,主要關注key(用到什麼索引)、rows(查詢執行掃描的元組個數)、extra(是否利用到排序、臨時表等)。
（2）檢視SQL涉及的表結構，此處主要檢視app_apply_step 表:
show create table app_apply_step, 主要關注表的索引和where 條件中的字段類型。可見 app_apply_step 表 is_exp 字段上無索引,且為tinyint類型。
（3）思考可能優化的點:
先檢視表 app_apply_step
字段is_exp的索引區分度 ，執行語句：
select count(distinct col1),
count(distinct col2)/count(*) from tb_name;
（4）is_exp 字段區分度約等于0，表明通過索引篩選非常高效。
alter table
app_apply_step
add index
idx_is_exp
(
is_exp
);
優化步驟總結
- 檢視執行計劃 explain extended
- 如果有告警資訊，檢視告警資訊 show warnings;
- 檢視SQL涉及的表結構和索引資訊
- 根據執行計劃，思考可能的優化點
- 按照可能的優化點執行表結構變更、增加索引、SQL改寫等操作
- 檢視優化後的執行時間和執行計劃
- 如果優化效果不明顯，重複第四步操作
慢SQL常見問題彙總