MySQL SQL優化
- 開啟慢查詢日誌 , 觀察一天或重現事故
- Explain + 慢查詢SQL
- Show Profile分析SQL生命週期
- DB參數優化
- 慢查詢日誌
- 使用慢查詢日誌 , 紀錄查詢效能差的SQL
- 當查詢時間超過long_query_time時寫入日誌,默認時間為10秒
- 開啟慢查詢日誌 , 設置後須重新連線DB
- Explain + 慢查詢SQL
- 使用Explain查看執行計畫 , 優化SQL
- SQL優化策略 (Index索引為其重點)
- 查詢執行計畫 : explain select ............最重要之4個欄位
- Id : 執行順序 , id越大優先執行 , id值相等則上方列優先
- type : 查詢等級System>const>eq_ref>ref>range>index>all
- rows : 預估要查詢的列數 , 越少越好
- extra : 其他index>filesort>temporary
- SQL優化策略
- Index優化策略
- select和where欄位皆可使用
- MySQL CRUD皆會做查詢的動作, 因此Index索引決定效能
- MyIsam引擎將資料與索引拆分成兩份檔案InnoDB為同一份檔案查詢效能較好
- 建議PK使用數值或自動產生流水號查詢效能較文字高
- 建議一定要加PK , 否則默認由DB自己判斷生成RowId
- MySQL 預設使用 B+Tree資料結構儲存索引
- 根據官方分析過,預設高度為3
- 完成資料data存在於最後一層索引上 , 與其他資料結構僅儲存磁碟位置不同
- Mysql另外有一種Hash資料結構, 較適合等值where條件
- 組合索引必須依序讀取/設置
- range索引(>= , > , < , <=)導致後續的索引失效 , 故可將此欄位放置在最後面
- 欄位不使用函數、計算
- 當使用left join時 , index加在右表 , 反之亦然
- 少使用select * , 使用覆蓋索引(欄位順序個數跟Index完全相同)效能佳
- 少用 OR
- Varchar必用單引號 , 以免索引失效
- 不使用 >< , != , 以免索引失效
- 不使用 is null , is not null , 以免索引失效, 使用 ''代替null欄位
- Like必須使用 'X%'以免索引失效 , 否則可用覆蓋索引彌補
- 查詢優化策略
- join buffer大小可以視情況調整
- 小表(A)驅動大表(B)查詢 , B in (A) 或 A exist (B)
- Order By優化策略
- 可搭配Index左前戳開頭規則 , 建立組合索引
- 不可使用 select *
- 所有排序欄位必須一致遞升或遞減
- sort_buffer_size不足會導致多次I/O、臨時表多路合併 (這是每個進程都有一份)
- max_length_for_sort_data不足會導致臨時表與多路合併
- 當max_length_for_sort_data設置過大會導致sort_buffer_size不足
- 當查詢筆數<max_length_for_sort_data且排序欄位並非text|blob , 使用單路排序
- Group By優化策略
- 實質上是先排序後分組
- 與Order By索引規則相同
- 當無法使用索引 , 則增加sort_buffer_size和max_length_for_sort_data
- Show Profile分析SQL生命週期
- 分析SQL執行細節和生命週期 , 查出問題點
- 開啟profile功能
- 診斷方式
- Status欄位不得出現之訊息 , 將嚴重影響效能
沒有留言:
張貼留言