查詢優化本就不是一蹴而就的，需要學會使用對應的工具、借鑒别人的經驗來對SQL進行優化，并且提升自己。
前言
一、建立索引規範
二、索引失效原因
三、SQL優化殺手锏之`Explain`
四、SQL優化殺手锏之`慢查詢`
五、優化大法
前言
先來鞏固一下索引的優點，檢索資料快、查詢穩定、存儲具有順序性避免伺服器建立臨時表、将随機的I/O變為有序的I/O。
但索引一旦建立的不規範就會造成以下問題，占用額外空間，浪費記憶體，降低資料的增、删、改性能。
是以隻有在了解索引資料結構的基礎上才能建立出高效的索引。
<code>**本文所有操作均在MySQL8.0.12**</code>
一、建立索引規範
在學習索引優化之前，需要對建立索引的規範有一定的了解，此規範來自于阿裡巴巴開發手冊。
主鍵索引：pk_column_column
唯一索引：uk_column_column
普通索引：idx_column_column
二、索引失效原因
建立索引需知道在什麼情況下索引會失效，隻有了解索引失效的原因，在建立索引時才不會出現一些已知錯誤。
1.帶頭大哥不能死
這局經典的語句就是涵蓋建立索引時一定要符合最左側原則。
例如表結構為<code>u_id，u_name，u_age，u_sex，u_phone，u_time</code>
建立索引為<code>idx_user_name_age_sex</code>。
查詢條件必須帶上u_name這一列。
2.不在索引列上做任何操作
不在索引列上做任何計算、函數、自動或者手動的類型轉換，否則會進行全表掃描。簡而言之不要在索引列上做任何操作。
3.倆邊類型不等
例如建立了索引idx_user_name，name字段類型為varchar
在查詢時使用where name = kaka，這樣的查詢方式會直接造成索引失效。
正确的用法為where name = “kaka”。
4.不适當的like查詢會導緻索引失效
建立索引為idx_user_name
執行語句為select * from user where name like “kaka%”;可以命中索引。
執行語句為select name from user where name like “%kaka”;可以使用到索引（僅在8.0以上版本）。
執行語句為select * from user where name like ‘’%kaka";會直接導緻索引失效
5.範圍條件之後的索引會失效
建立索引為idx_user_name_age_sex
執行語句select * from user where name = ‘kaka’ and age > 11 and sex = 1;
上面這條sql語句隻會命中name和age索引，sex索引會失效。
複合索引失效需要檢視key_len的長度即可。
總結：%在後邊會指令索引，當使用了覆寫索引時任何查詢方式都可命中索引。
以上就是咔咔關于索引失效會出現的原因總結，在很多文章中沒有标注MySQL版本，是以你有可能會看到is null 、or索引會失效的結論。
三、SQL優化殺手锏之<code>Explain</code>
在寫完SQL語句之後必須要做的一件事情就是使用Explain進行SQL語句檢測，看是否命中索引。
下圖就是使用explain輸出格式，接下來将會對輸出格式進行簡單的解釋。
1.id
這列就是查詢的編号，如果查詢語句中沒有子查詢或者聯合查詢這個辨別就一直是1。
如存在子查詢或者聯合查詢這個編号會自增。
2.select_type
最常見的類型就是SIMPLE和PRIMARY，此列知道就行了。
3.table
了解為表名即可
4.<code>**type</code>
此列是在優化SQL語句時最需要關注的列之一，此列顯示了查詢使用了何種類型。
以下排序從最優到最差。
system：表内隻有一行資料
const：最多隻會有一條記錄比對，常用于主鍵或者唯一索引為條件查詢
eq_ref：當連接配接使用的索引為主鍵和唯一時會出現
ref：使用普通索引=或<=> 運算符進行比較将會出現
fulltext：使用全文索引
ref_or_null：跟ref類型類似，隻是增加了null值的判斷，實際用的不多。語句為where name = ‘kaka’ and name is null，name為普通索引。
index_merge：查詢語句使用了倆個以上的索引，常見在使用and、or會出現，官方文檔将此類型放在ref_or_null之後，但是在很多的情況下由于讀取索引過多性能有可能還不如range
unique_subquery：用于where中的in查詢，完全替換子查詢，效率更高。語句為<code>value IN (SELECT primary_key FROM single_table WHERE some_expr)</code>
index_subquery：子查詢中的傳回結果字段組合是一個索引（或索引組合），但不是一個主鍵或唯一索引
range：索引範圍查詢，常見于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等運算符的查詢中。
index：索引全表掃描，把索引從頭到尾掃一遍
all：全表掃描,性能最差。
5.possible_keys
此列顯示的可能會使用到的索引
6.<code>**key</code>
優化器從possible_keys中命中的索引
7.key_len
查詢用到的索引長度（位元組數），key_len隻計算where條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到key_len中。
8.ref
如果是使用的常數等值查詢，這裡會顯示const。
如果是連接配接查詢，被驅動表的執行計劃這裡會顯示驅動表的關聯字段。
如果是條件使用了表達式或者函數，或者條件列發生了内部隐式轉換，這裡可能顯示為func。
9.<code>**rows</code>
這是mysql估算的需要掃描的行數（不是精确值）。
這個值非常直覺顯示 SQL 的效率好壞, 原則上 rows 越少越好。
10.filtered
此清單示存儲引擎傳回的資料在server層過濾後，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數
11.<code>**extra</code>
在大多數情況下會出現以下幾種情況。
Using index：使用了覆寫索引，查詢列都為索引字段
Using where：使用了where語句
Using temporary ：查詢結果進行排序的時候使用了一張臨時表
Using filesort ：對資料使用一個外部的索引排序
Using index condition：使用了索引下推，關于索引下推可以檢視咔咔之前文章<code>MySQL索引一文</code>
12.總結
以上就是關于Explain所有列的說明，在平時開發的過程中，一般隻會關注type、key、rows、extra這四列。
type優化目标至少達到range級别，要求是ref級别，如果可以consts最好。
key是查詢使用到的索引，如果此列為空，要麼未建立索引，要麼索引失效。
rows是這條SQL語句掃描的行數，越少越好。
extra：此列為擴充列，如果出現臨時表、檔案排序則需要優化。
四、SQL優化殺手锏之<code>慢查詢</code>
上文說到了可以直接使用explain來分析自己的SQL語句是否合理，接下來再聊一個點那就是慢查詢。
檢視慢查詢是否打開
檢視是否記錄沒有使用索引的SQL語句
開啟慢查詢、開啟記錄沒有使用到索引的SQL語句
set global log_queries_not_using_idnexes=‘on’;
set global log_queries_not_using_indexes=‘on’;
查詢以上倆個配置是否打開
設定慢查詢時間，這個時間由自己把控，一般1s即可<code>set globle long_query_time=1;</code>
如果檢視這個時間沒有變，則關于用戶端在重新連接配接一次即可。
檢視慢查詢存儲位置
然後随便執行一條不執行索引的語句即可在這個日志中檢視到此語句
上圖中一般需要主要觀察的是Query_time、SQL語句内容。
以上就是關于如何使用慢查詢來檢視項目中出現問題的SQL語句。
五、優化大法
此處跟大家聊一些常用的SQL語句優化方案，以上的倆個工具要好好的利用，輔助我們進行打怪。
禁止使用select *，需要什麼字段查詢什麼字段
where字段設定索引
group by、order by字段設定索引
舍棄offset，limit分頁，使用延遲關聯來實作分頁(資料量不大時可不用)
寫分頁時當count為0時，直接傳回避免執行分頁語句
利用覆寫索引進行查詢避免回表
建立複合索引時區分度最高的放在最左側
統計資料行數隻用count(*)，别整的花裡胡哨的
關于in和exist，如果查詢的倆個表大小一緻則性能差别可忽略，如果子查詢表大用exist，否則使用in
查詢一行資料時加上limit 1
選擇合理的資料類型，在滿足條件下資料類型越小越好
聯合查詢join最多三個表，并且需要join的字段資料類型保持一緻
in操作能避免盡量避免，無法避免的情況下in元素控制在1000以内
資料更新頻繁，區分度不高的列不适合建立索引
explain中的type至少要達到range，要求為ref
聯合索引滿足最左側原則
堅持學習、堅持寫博、堅持分享是咔咔從業以來一直所秉持的信念。希望在偌大網際網路中咔咔的文章能帶給你一絲絲幫助。我是咔咔，下期見。