[TOC] MySQL 之SQL優化詳解（三） 1. 索引優化 一旦建立索引，select 查詢語句的where條件要儘量符合最佳左首碼的原則，如若能做到全值匹配最好。 索引優化的第一個前提就是建好索引，第二個就是避免索引失效 索引失效的場景 1. 如果索引了多列，要遵守最左首碼法則。 ...
目錄
MySQL 之SQL優化詳解（三）
1. 索引優化
一旦建立索引，select 查詢語句的where條件要儘量符合最佳左首碼的原則，如若能做到全值匹配最好。
索引優化的第一個前提就是建好索引，第二個就是避免索引失效
索引失效的場景
1.如果索引了多列，要遵守最左首碼法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列
2.不在索引列上做任何操作（計算、函數、(自動or手動)類型轉換），會導致索引失效而轉向全表掃描
3.存儲引擎不能使用索引中範圍條件右邊的列
4.mysql 在使用不等於(!= 或者<>)的時候無法使用索引會導致全表掃描
5.is null ,is not null 也無法使用索引
6.like以通配符開頭('%abc...') mysql索引失效會變成全表掃描的操作
7.少用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為什麼這樣寫，瞭解以上知識的使用方法就可以啦
