思路
- 開啟慢查詢日誌，抓取慢sql
- 通過explain檢視執行計劃，對慢sql語句分析
- 建立索引並調整語句，再對比優化結果
抓取慢sql
show variables like '%quer%';
| Variable_name | Value | 含義 |
|---|
| binlog_rows_query_log_events | OFF | ON 標識開啟慢查詢日誌 |
| ft_query_expansion_limit | 20 | |
| have_query_cache | YES | |
| log_queries_not_using_indexes | OFF | |
| log_throttle_queries_not_using_indexes | 0 | |
| long_query_time | 10 | 慢查詢時間閥值，單位秒 |
| query_alloc_block_size | 8192 | |
| query_cache_limit | 1048576 | |
| query_cache_min_res_unit | 4096 | |
| query_cache_size | 0 | |
| query_cache_type | OFF | |
| query_cache_wlock_invalidate | OFF | |
| query_prealloc_size | 8192 | |
| slow_query_log | ON | |
| slow_query_log_file | /data/mysql/log/slow_query.log | 慢查詢日誌儲存位置 |
分析查詢語句
- 先看type：All全表掃描，沒有用到索引
- 再看key：Null沒有使用索引列
- 然後看rows：數值越多耗時越長
- 最後看Extra：避免Using temporary和Using Filesort
- id:選擇識別符號，代表執行順序
- select_type:表示查詢的型別。
- simple:簡單的select查詢，不包含聯合查詢和子查詢
- primary:查詢中包含子查詢
- subquery:select 或者where中包含子查詢
- derived：from中包含子查詢
- union：聯合查詢
- union result：union後的結果集
- table:查詢的表名
- partitions:匹配的分割槽
- type:表示表的連線型別
- All：全表掃描
- Index：只遍歷索引樹，索引樹上就有要查詢的結果，不需要IO
- Range：索引範圍掃描
- Ref：非唯一性索引掃描
- Eq_ref：唯一索引掃描
- Const：通過一次索引就能查詢到，通常是Primary Key或者Unique
- System：Const特例，表資料只有一行
- Null：不用訪問索引就可以直接查詢到結果
- Possible_key：能使用哪個索引找到資料行，單並不一定會被使用到
- Key：Possible_key中決定使用的索引
- Key_len:顯示索引中使用的位元組數
- ref:上述表的連線匹配條件，即哪些列或常量被用於查詢索引上的值
- Rows:找到所需記錄要讀取的行數
- Extra:
- Using Where：僅通過索引就可以過濾所需資料
- Using temporary：需要使用臨時表來儲存結果集
- Using Filesort：order by操作無法利用索引完成導致的“檔案排序”
- Using join buffer：連線使用了快取，可以通過新增索引來解決
- Using Index：索引樹中包含要查詢的所有資訊
- Using Index Condition:根據輔助索引過濾資料，減少Server和磁碟的IO次數
索引建立原則
- 表一定要有業務無關的主鍵
- 適合新增索引的列：經常被查詢、經常用於錶連結，經常排序或者分組
- 索引列儘量都是不重複的資料
- 組合索引一般不超過5列，選擇性高的放在前面
- 合理利用索引覆蓋，禁止select *
- explain 判斷sql是否合理利用索引
- 單表索引控制在5個以內
- 不建議在頻繁更新的欄位上新增索引
- where條件中的索引列不能是表示式的一部分，避免對索引列進行函式計算
- join型別的欄位必須型別一致且都建立索引
索引失效
- 隱式的型別轉換會導致索引失效，導致全表掃描
- 對索引列進行函式或者數學計算，例如日期格式化
- 模糊匹配未使用字首匹配
- 使用了負方向查詢，not，!=，not in等
Sql規範
- 按需查詢避免 select *
- 無法使用覆蓋索引，回表，增加IO
- 多查詢的列，會有多餘的IO和網路開銷
- 避免大事務，將大事務拆成小事務。防止出現鎖阻塞，導致的雪崩效應
- 少用多表join，禁止大表join，小表驅動大表，join列必須字符集一致，且有索引
- 儘量避免多層子查詢巢狀
- 定期對慢sql優化