一文帶你掌握MySQL查詢優化技能

2021 年 5 月 6 日
筆記

查詢優化本就不是一蹴而就的，需要學會使用對應的工具、借鑒別人的經驗來對SQL進行優化，並且提升自己。

分享一套部落客覺得講的很詳細很實用的MySQL教程給大家，可直接點擊觀看！

//www.bilibili.com/video/BV1fx411X7BD

先來鞏固一下索引的優點，檢索數據快、查詢穩定、存儲具有順序性避免伺服器建立臨時表、將隨機的I/O變為有序的I/O。

但索引一旦創建的不規範就會造成以下問題，佔用額外空間，浪費記憶體，降低數據的增、刪、改性能。

所以只有在理解索引數據結構的基礎上才能創建出高效的索引。

**本文所有操作均在MySQL8.0.12**

一、創建索引規範

在學習索引優化之前，需要對創建索引的規範有一定的了解，此規範來自於阿里巴巴開發手冊。

主鍵索引：pk_column_column
唯一索引：uk_column_column
普通索引：idx_column_column

二、索引失效原因

創建索引需知道在什麼情況下索引會失效，只有了解索引失效的原因，在創建索引時才不會出現一些已知錯誤。

1.帶頭大哥不能死

這局經典的語句就是涵蓋創建索引時一定要符合最左側原則。

例如表結構為u_id，u_name，u_age，u_sex，u_phone，u_time

創建索引為idx_user_name_age_sex。

查詢條件必須帶上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優化殺手鐧之Explain

在寫完SQL語句之後必須要做的一件事情就是使用Explain進行SQL語句檢測，看是否命中索引。

下圖就是使用explain輸出格式，接下來將會對輸出格式進行簡單的解釋。

1.id

這列就是查詢的編號，如果查詢語句中沒有子查詢或者聯合查詢這個標識就一直是1。

如存在子查詢或者聯合查詢這個編號會自增。

2.select_type

最常見的類型就是SIMPLE和PRIMARY，此列知道就行了。

3.table

理解為表名即可

4.**type

此列是在優化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查詢，完全替換子查詢，效率更高。語句為value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery：子查詢中的返回結果欄位組合是一個索引（或索引組合），但不是一個主鍵或唯一索引
range：索引範圍查詢，常見於使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等運算符的查詢中。
index：索引全表掃描，把索引從頭到尾掃一遍
all：全表掃描,性能最差。