MySQL 之SQL優化詳解（三）
1. 索引優化
一旦建立索引，select 查詢語句的where條件要儘量符合最佳左字首的原則，如若能做到全值匹配最好。
索引優化的第一個前提就是建好索引，第二個就是避免索引失效
索引失效的場景
- 如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列
- 不在索引列上做任何操作（計算、函式、(自動or手動)型別轉換），會導致索引失效而轉向全表掃描
- 儲存引擎不能使用索引中範圍條件右邊的列
- mysql 在使用不等於(!= 或者<>)的時候無法使用索引會導致全表掃描
- is null ,is not null 也無法使用索引
- like以萬用字元開頭('%abc...') mysql索引失效會變成全表掃描的操作
- 少用or,用它來連線時會索引失效
小總結：
假設index(a,b,c)
| Where語句 | 索引是否被使用 |
|---|---|
| where a = 3 | Y,使用到a |
| where a = 3 and b = 5 | Y,使用到a，b |
| where a = 3 and b = 5 and c = 4 | Y,使用到a,b,c |
| where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
| where a = 3 and c = 5 | 使用到a， 但是c不可以，b中間斷了 |
| where a = 3 and b > 4 and c = 5 | 使用到a和b， c不能用在範圍之後，b斷了 |
| where a = 3 and b like 'kk%' and c = 4 | Y,使用到a,b,c |
| where a = 3 and b like '%kk' and c = 4 | Y,只用到a |
| where a = 3 and b like '%kk%' and c = 4 | Y,只用到a |
| where a = 3 and b like 'k%kk%' and c = 4 | Y,使用到a,b,c |
2. 剖析報告:Show Profile
是什麼：是mysql提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於SQL的調優的測量
官網介紹：show profile
預設情況下，引數處於關閉狀態，開啟後預設儲存最近15次的執行結果
1.是否支援，看看當前的mysql版本是否支援
Show variables like 'profiling';
2.開啟功能，預設是關閉，使用前需要開啟
set profiling=on;
3.執行SQL
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5
4.檢視結果 show profile;
5.診斷SQL，show profile cpu,block io for query 上一步前面的問題SQL數字號碼;
| Status | 建議 |
|---|---|
| System lock | 確認是由於哪個鎖引起的，通常是因為MySQL或InnoDB核心級的鎖引起的建議：如果耗時較大再關注即可，一般情況下都還好 |
| Sending data | 從server端傳送資料到客戶端，也有可能是接收儲存引擎層返回的資料，再傳送給客戶端，資料量很大時尤其經常能看見備註：Sending Data不是網路傳送，是從硬碟讀取，傳送到網路是Writing to net建議：通過索引或加上LIMIT，減少需要掃描並且傳送給客戶端的資料量 |
| Sorting result | 正在對結果進行排序，類似Creating sort index，不過是正常表，而不是在記憶體表中進行排序建議：建立適當的索引 |
| Table lock | 表級鎖，沒什麼好說的，要麼是因為MyISAM引擎表級鎖，要麼是其他情況顯式鎖表 |
| create sort index | 當前的SELECT中需要用到臨時表在進行ORDER BY排序建議：建立適當的索引 |
| checking query cache for querychecking privileges on cachedsending cached result to clienstoring result in query cache | 和query cache相關的狀態，已經多次強烈建議關閉 |
6. 日常開發需要注意的結論
- Creating tmp table 建立臨時表
- Copying to tmp table on disk 把記憶體中臨時表複製到磁碟，危險！！！
- locked 被鎖住
示例：剖析 select * from emp group by id%20 order by 5
由剖析報告看出，其中 Copying to tmp table 步驟花費了大量的時間，所以這條SQL應該優化了。
注：該文的SQL只是為了便利的梳理知識點使用，不需要關心這條SQL為什麼這樣寫，瞭解以上知識的使用方法就可以啦