在看下邊的內容的時候一定要保證你已經會用explain分析sql了!
索引單表優化案例：
問題：
比如我們要查詢密碼=666並且age>30的按照username倒敘排序的第一條記錄；經過分析會發現sql內部優化器進行了全表掃描type=ALL extra當中還有using filesort的存在說明利用了外部的索引進行排序而沒有用本表的索引進行排序 所以優化是必須要做的!
解決：
A:我們現在給where後邊涉及到的欄位都建立上索引 比如：
create index idx_pau on admin(password,age,username);
然後再進行分析：
我們發現type=range 避免了type=all這種全表掃描情況的發生 但是 在extra當中還是存在using filesort情況產生 這是因為我們建立的索引沒有起作用 你會問 那不是在key欄位當中有idx_pau索引說明索引起作用了啊!? 我們在查的時候where後邊是password = 常量 age > 常量 然後order by username,password會用到符合索引當中的內容 age也會用到符合索引當中的內容 但是喲一條規則就是範圍之後的索引是失效了的 前提是你建立的是複合索引 目前看來是這樣的額! 因為我們建立索引的順序是password age username 因為age是取的範圍 所以後邊的索引 username就會失效 所以才會造成using filesort情況還是存在的情況發生!
B.第一種正確解決方法：
為where後邊涉及到的每個欄位都建立上索引 比如：
create index idx_p on admin(password);
create index idx_p on admin(age);
create index idx_u on admin(username);
這樣我們單獨為where後邊涉及到的欄位都加上索引 那麼首先type=all的問題一定解決了的 另外order by username的問題 之前我們講過order by後邊的欄位建立索引的規則 最好和order by前邊的欄位名稱相同 如果不同就和前邊的欄位一起建立複合索引 並且保持順序相同 詳見using filesort !但是這裡出現另外一種情況就是單獨給order by後邊的欄位建立上了索引 也沒有出現using filesort情況的發生！
C.第二種正確解決方法：
那就是隻為password 和 username建立複合索引
create index idx_pu on admin(password,username);
那麼age > 0 沒有走索引也就不會影響到order by後邊的索引的使用了!那麼age > 0 沒有走索引也就不會影響到order by後邊的索引的使用了!
索引兩張表關聯優化：
問題：boys當中的id=beauty當中的boyfriend_id 利用left join查詢 我們常規的寫法 然後通過explain進行分析得出下邊的結論 發現type = all進行了全表掃描所以我們得要去優化一下！
如何優化呢？
總結：如果是left join那麼給右表相應的欄位新增索引 如果是right join 那麼給左表相應的欄位新增索引！
所以我們這裡使用left join 那麼給右邊的表當中的欄位新增索引 給beauty當中的boyfriend_id建立索引 然後再分析看看：
我們發現type當中有ref存在 並且rows的行數也減少了很多 完美的完成了優化任務!我們發現type當中有ref存在 並且rows的行數也減少了很多 完美的完成了優化任務!
三張表的關聯優化：
原則就是保證join語句中被驅動表上join條件欄位已經被索引
left join 左邊為主表也就是驅動表 右邊為被驅動表
right join相反
儘可能減少join語句中的迴圈總次數 原則就是永遠用小結果集驅動大的結果集
myslq如何優化left join:https://www.cnblogs.com/zedosu/p/6555981.h...
索引優化正式環節
A.如何避免索引失效?A.如何避免索引失效?
對於上邊總結的解釋：
全值匹配：
比如我們為admin表建立了複合索引
create index idx_upa on admin(username,password,age);//為username password age 三個欄位建立了複合索引
執行sql:
explain select * from admin where username=’john’ and password=8888 and age=29;
我們發現索引被充分的利用上了! 最好的避免索引失效的一個方法就是全值匹配 比如我們建立了idx_upa複合索引 那麼我們在查詢的時候欄位的順序和我們建立複合索引的順序是一樣的 開始末尾中間一個都不少的全寫上 這樣效率是最高也是絕對可以避免索引失效的 這就叫全值匹配!
最佳左字首法：（如果索引了多列也就是建立了複合索引 那麼要遵守最左字首法則 指的是查詢從索引的最左前列開始並且不跳過索引中的列）
還是拿上邊的案例來講：
我們建立了idx_upa複合索引 在查詢的時候不可能都是全值匹配 比如我們現在不查詢username=‘’ password=‘’ age=‘’的了 我們要查詢其中的任意兩個 那麼就會出現索引失效的情況發生！
A.比如：select * from admin where username=’’ and password=’’;
我們查的條件是username=’’ and password=’’ 因為username 和 password和複合索引idx_upa當中都是按照順序來的 u代表username p代表password 順序一致 所以不會出現索引失效
B.如果我們查select * from admin where password=’’ and age=’’ 那麼就會出現索引失效的情況 因為違背了左字首法則 在找索引的時候會先匹配最左側的 然後往後找 第一個索引可以理解成火車頭 火車頭都找不到那麼後邊的也就不能用了!
C.如果我們查詢的是select * from admin where username=’john’ and age=29; 那麼雖然不會出現索引失效的提示 key當中也有索引的值 但是 但是 在之前索引不失效的前提下ref當中的const都是兩個const 但是現在是一個const 說明其中有一個沒有用上索引 這種情況資料量多了的話也是影響效能的 並且key_len的長度和我們單獨去查詢username=’’的時候的長度是一致的 所以可以肯定的是後邊這個沒有用上索引
D.如果我們單獨查詢select * from admin where age = 29;或者單獨查詢password=8888的這種情況也是會出現索引失效的情況的 因為我們是複合索引 越過第一個單獨去找第二個第三個是絕對會失效的索引；
所以有句口訣：
帶頭大哥不能少!
中間兄弟不可斷!
除了大哥其他兄弟單獨行動都得死!
帶頭大哥捨棄中間幾個兄弟 帶著後邊幾個兄弟 結果就是隻有帶頭大哥能活 其他都得死!
不在索引列上做任何操作（計算 函式 型別轉換） 會導致索引實現轉向全表掃描
複合索引idx_upa 我們從火車頭上username上 它也是索引列上使用了函式 那麼直接導致索引失效！
mysql當中不能使用索引中範圍條件右邊的列：
全職匹配的情況下：ref級別 並且ref列有三個const表示常量
索引當中存在範圍：（如果不建立索引那一定是全表掃描 建立完索引有的索引不起作用 ）
最後的結果是range範圍級別 明顯不如ref級別高 另外ref當中為空 說明有的索引列沒起作用！ 解決方法上邊講過！
如果我們不使用複合索引而是為三個欄位單獨建立索引那麼就變成了ref級別
儘量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）減少select *）
以上講的都是複合索引的前提下哈！
mysql當中使用不等於!=或者<>的時候無法使用索引並且會導致全表掃描
is null in not null 也無法使用索引
並且is not null會進行全表掃描
所以 我們可以給欄位約上預設值 這樣就不會產生null的情況 也就避免了使用is null和is not null情況的發生!
like以萬用字元開頭（%adb% 或者 %abc）的情況 mysql索引會失效並且變成全表掃描 但是 like ‘abc%’是不會造成索引失效的情況發生的
但是在開發過程當中我們經常會遇到必須是%abc%的情況 又該如何解決索引失效的問題呢？
那就是使用覆蓋索引！什麼是覆蓋索引之前上邊講過
比如：
create index idx_username on admin(username); 我們給username列建立了索引
然後發現 select username from admin where username like ‘%h%’;
但是如果我們這麼查：select * from admin where username lilke ‘%abc%’;
那麼就會造成索引失效的現象並且會全表掃描 因為沒有用上覆蓋索引！
並且但是如果我們這麼查詢 select password from admin where username like ‘%abc%’;也是會全表掃描並且索引失效
但是如果我們這麼查詢 select id,username from admin where username like ‘%abc%’; 就不會造成全表掃描和索引失效 因為id始終是主鍵也是從index索引表當中去查詢相當於也是覆蓋索引！
注意：如果我們建立的是複合索引 那麼只要是查詢的列在複合索引範圍內的都是可以避免全表掃描並且避免索引失效問題產生的！
varchar型別的列字串如果不加單引號則索引失效
比如varchar型別我們平時都是儲存的字串 但是有時候迫不得已存放個2000 或者其他數字 如果在查詢的時候不注意沒有加上單引號 那麼sql優化器就會首先進行隱式的型別轉換然後再去搜尋並且最主要的是索引失效並且進行了全表掃描
少用or,用它來連結的時候會造成索引失效
如果用or來連線 比如 select * from admin where username=a or username=b;這樣也會造成索引失效並且造成全表掃描；解決的方式不如分開查 然後到php當中再去拼接成一個陣列
簡單小測試 配合理解一下：
面試題精講 有助於理解更好的去使用：
前提是我們建立好了複合索引 idx_test03_c1234 c1 c2 c3 c4這麼一個順序的複合索引哦!
A：索引順序不同 第一個是c1 c2 c3 c4
第二個是c1 c2 c4 c3
第三個是c4 c3 c2 c1
結果都是一樣的 都被用上了索引 也就是說索引有沒有被用到和複合索引的順序無關
B：當中出現了範圍查詢 我們看到結果是range範圍級別 那麼說明 c1 c2 c3都被用上了 但是ref為空 按照道理來說應該ref為4個const 但是ref為null 並且key_len的長度正好比四個都用上少一個的長度 那麼就是說明c4索引失效了! 這也驗證了範圍之後全失效的原則
C.c1=’a1’ and c2=‘a2’ and c4>’a4’ and c3 = ‘a3’
這種情況下雖然我們的sql指令順序變了 但是sql優化器內部會按照c1 c2 c3 c4的索引順序去查詢 雖然c4是範圍查詢但是它是在最後被sql優化器找到的 所以4個索引都是有效果的
D：c1=’a1’ and c2=‘a2’ and c4=‘a4’ order by c3 中間兄弟不能斷 看來這裡明顯是斷開了 所以肯定的是c1 c2是絕對被用到了的 order by c3其實c3也是被用到了的 只不過是被用到了排序上 在explain當中是不體現的！ c4是絕對沒有被用上的！ 如果c3也沒有被用上 那麼extra當中是要有usging filesort的 前邊講過 這種using filesort九死一生效率極差！
E:c1=a1 and c2=a2 order by c3 結果和D是一樣的 c4肯定沒被用上 c3去排序去了不在explain當中顯示 c1 c2是絕對被用上了的！
F:c1=a1 and c2=a1 order by c4; 結果是c1 c2被使用 c3 c4不被使用 並且還產生了using filesort
G：c1=a1 and c5=a5 order by c2，c3 結果是c1被使用 c2和c3被使用但是去排序去了 不會產生using filesort
H:c1=a1 and c5=a5 order by c3，c2 結果是c1被使用 因為索引順序是1234 但是order by 的時候出現了3 然後再2 所以會導致using filesort的發生
I:c1=a1 and c2=a2 order by c2,c3 結果是c1 c2都被使用了 並且排序也是按照c2 c3順序排序 沒毛病 非常完美
c2=a2是一個常量了已經 order by 常量,c3 那麼這個常量排序也就是死的 其實就是按照c3排序 c3在索引當中 所以沒毛病
（前提是我們從前邊查詢出了c2=a2是一個常量）
j: c5=a5只是用來迷惑你的 娃哈哈
K：c1=a1 and c5=a5 order by c3，c2 一定是用到了c1 但是c3 c2 順序和索引不一致也會導致filesort的產生
L:c1=a1 and c4=a4 group by c2，c3 ； c1一定是被用到了索引 c4絕對沒被用到 group by當中c2 c3也被用到了 因為也是符合我們的索引順序的 1234 2 3是符合這麼個順序的 但是在explain當中是不顯示的哦
M::c1=a1 and c4=a4 group by c3,c2 c1一定是被使用了索引的 c4絕對沒有被使用 group by 後邊 c3 c2因為違背了我們的索引順序 所以會產生臨時表以及using filesort的產生 情況很不妙!
小總結：
explain分析先看定值也就是常量 然後看範圍 範圍之後的索引必失效 然後看order by order by的順序要和索引順序保持一致如果不一致會產生filesort情況的發生 啥意思呢 我們規定了索引的順序 但是我們在寫sql指令的時候不是按照找個順序執行的 那麼sql優化器就會在內部進行產生內排序 也就是我們看到的using filesort情況的發生
group by 基本上都需要進行排序 如果使用的欄位索引不正確也是和order by 一樣 在內部先進行排序 然後排序完成之後要麼進行搬家 要麼進行刪除 於是乎就產生了臨時表
所以索引的使用非常的重要 尤其是正確使用索引！
優化口訣：
查詢優化：
永遠小表驅動大表
如圖所示 也許在php程式當中兩個for迴圈的結果是一樣的 但是在mysql當中確是不一樣的 第一個會建立5次mysql的連結 而 第二個則會建立1000次連結 對於mysql來說最傷身的就是建立連線釋放連線這玩意了 所以永遠要小表驅動大表
優化原則：小表驅動大表 即小的資料集驅動大的資料集
in的右邊小結果集 existx的右邊是大的結果集
order by 詳解：
order by 子句 儘量使用index方式排序order by 子句 儘量使用index方式排序 避免使用using filesort方式排序
對於order by的研究 我們主要就是看會不會產生using filesort 出現就不好了哦
接下來看我們自己的案例：
create index idx_p_u on admin(password,username);建立password username 複合索引
特別注意：只要用到order by 那麼前邊就不要使用select * 而是按需查詢 要什麼欄位就寫什麼欄位 因為你一旦寫上select * 下邊的總結就都不準了
第一種情況：
explain select username,password from admin order by username;//有帶頭大哥存在username order by後邊複合最左字首法則所以 沒毛病
第二種情況：
explain select username,password from admin where username=’john’ order by username;//加上where條件也是帶頭大哥存在 order by後邊複合最左字首法則 沒毛病
第三種情況：
explain select username,password from admin where password > 10 order by username;//我們索引的順序是username password 雖然我們在指令當中寫的順序不對 但是sql優化器會自動給我們對索引排序 order by後邊複合最左字首法則
第四種情況：
explain select username,password from admin where username=’john’ order by password;//符合 使用where子句與order by子句條件列組合滿足索引最左字首法則！
我們再來看下邊這個經典案例：
預設我們建立的索引列的排序都是升序排序的asc 雖然這裡我們order by的順序沒有變 age birth 但是排序方式變了 birth是desc方式排序 索引sql內部也是會進行一次外部排序 所以也會產生filesort情況的發生的 總結：要麼同升要麼同降
order by 滿足兩種情況 會使用index方式排序：
第一個的解釋：order by 後邊使用和索引列相同順序的欄位 比如idx_name_age 我們order by name或者order by name,age都不會出現filesort情況的產生
第二個的解釋：使用where 子句與order by 子句條件列組合 比如我們建立了name age的複合索引 select * from admin where name=’a’ order by age; 這就是where子句和order by子句條件列組合滿足索引的最左字首法則；
也就是說盡可能在索引列上完成排序操作 遵照索引建的最佳左字首法則；
最終我們對order by做最後的總結（非常重要）：
group by 詳解：
其他的都和order by一樣
最大的區別就是where 高於 having 能寫在where限定的條件就不要去having限定了！
本作品採用《CC 協議》，轉載必須註明作者和本文連結