> 原文網址 : https://blog.csdn.net/zbw18297786698/article/details/54349046 在項目中驗證 SQL 語句執行效率的時候最直觀的方式就是查看其執行時間，但是在線上環境中如果不慎運行一個效率十分低下的 SQL 導致數據庫當掉了，那就悲劇了。並且只看執行時間，並無法有效的定位影響效率的原因。因此通過 `EXPLAIN` 命令查看SQL語句的執行計劃，根據執行計劃可以對SQL進行相應的優化。理解SQL執行計劃各個字段的含義這時候顯得十分重要。 如下圖 ```sql EXPLAIN SELECT COUNT(*) FROM blog ```  這是一個簡單的 SQL 的執行計劃，可以看到其包含十個字段來描述這個執行計劃。 其中比較重要的字段有 `select_type`、`type`、`ref`、`extra` 。 下面為更好的理解執行計劃，這裡對每個字段進行相應的解釋。 ### id 一個複雜的sql會生成多執行計劃如下圖： ```sql EXPLAIN SELECT COUNT(*) FROM (SELECT id from blog where id = 1) a ```  可以看到含有子查詢的sql產生了兩條記錄，分別表示該條sql的執行順序。 ### select_type 查詢類型，有如下幾種值 1. simple 表示簡單查詢，沒有子查詢和union 如圖1所示 2. primary 最外邊的select，在有子查詢的情況下最外邊的select查詢就是這種類型如圖2所示 3. union union語句的後一個語句執行的時候為該類型如圖2.1所示 ```sql EXPLAIN SELECT COUNT(*) FROM blog UNION SELECT id from blog where id = 1 ```  union result union語句的結果如圖所示。 ### table 使用的表名 ### type 連接類型，十分重要的字段 按照代表的效果由最優到最差情況進行介紹。 #### system 表僅有一行 const的特例。 #### const 最多匹配一行並且使用 `PRIMARY KEY` 或 `UNIQUE` 索引，才會是 `const`。 ```sql EXPLAIN SELECT * FROM blog where id =1 ```  下面這種情況搜索到一條數據但是沒有用到主鍵或索引 所以 type 不是 `const` 關於all的含義將在下文介紹 ```sql EXPLAIN SELECT * FROM blog LIMIT 1 ```  #### eq_ref 根據 MYSQL 官方手冊的解釋： "對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是除了 `const` 類型最好的聯接類型。它用在一個索引的所有部分被聯接使用並且索引是 `UNIQUE` 或`PRIMARY KEY`。 `eq_ref` 可以用於使用=比較帶索引的列。看下面的語句 ```sql EXPLAIN SELECT * FROM blog, author where blog.blog_author_id = author.id ```  ```sql EXPLAIN SELECT * FROM author, blog where blog_author_id = author.id ```  #### ref 對於所有取自前表的行組合，所有的匹配項都是通過索引讀出的。也可以理解為連接不能基於關鍵字選擇單個行,可能查找到多個符合條件的行。叫做 `ref` 是因為索引要跟某個參考值相比較。這個參考值或者是一 個常數,或者是來自一個表裡的多表查詢的 結果值。 如下圖 ```sql EXPLAIN SELECT * FROM blog where blog_author_id = 2 // 其中blog_author_id有索引 ```  寫到這裡 相信大家還是對以上各種類型的解釋有點迷迷糊糊。下面看一個等值連接的例子，會加深對索引和以上解釋的理解。 ```sql SELECT * FROM author, blog where author.id=blog.blog_author_id and author.id = 2 ``` 這條語句查出作者2發表的所有博客。 id為author表主鍵，mysql會自動為主鍵創建唯一索引。而blog_author_id是blog一個普通字段，如果對其加個索引看一下運行的效果。  先觀察下一下這個執行計劃，可以看出 MYSQL 對 SQL 語句的執行已經做了很好的優化.這裡可以看到其中一條優化規則，先做選擇操作縮小連接操作的集合維度，再做連接操作，詳細可查看 MYSQL 生成執行計劃的優化策略。 解釋一下：第一行代表 MYSQL 生成的第一個執行計劃。即 `select * from author where id= 2` 由於 `id` 是 `author` 表的主鍵，且表包含多條數據但僅命中一行，所以其類型為 `const`。 第二行:對於 `blog` 表中 `auhorid` 為 2 的記錄有多個，且是通過索引讀出的。滿足 `ref` 的條件。 自然而然 如果把 `blog` 表中的 `author_id` 所以去除掉，則其類型應該不會再是 `ref`。讓我們來驗證這個想法。 ```sql drop index author_id on blog ``` 再來執行以下查詢語句  可以看到 `type` 類型變為 `ALL` 了，這種類型的效率非常慢，同時你可以看到 rows 這一行數據也發生了變化。由於沒有索引，所以需要掃描全表。詳細關於 `ALL` 類型和 rows 列的含義將在下文中介紹。 下面接著看下一個類型。 #### ref_or_null 如同 ref，但是添加了 MySQL 可以專門搜索包含 NULL 值的行。在解決子查詢中經常使用該聯接類型的優化。或解釋為MySQL 必須在初次查找的結果 裡找出 null 條目,然後進行二次查找。 這種類型沒搞明白 做實驗都沒出現這種類型 希望各位朋友給個例子。 但是上面說的這五種類型是屬於總體來說效果很不錯的了。如果能滿足以上類型的查詢 基本上不需要太大的優化、下面介紹效率較低幾種類型 當出現以下幾種類型的查詢 就要好好考慮做做優化了。 #### index_merge 該聯接類型表示使用了索引合併優化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。查看下面這條sql ```sql EXPLAIN SELECT * FROM blog where blog_title = "first" and blog_author_id = 1 ```  大致解釋一下索引和並優化的概念，這時mysql針對sql使用多個索引進行查詢時的優化方案。通俗的說就是mysql會把同一個表的多個索引掃描的結果進行合併。詳細的去看看相關博客。 解釋一下上述的例子，分別對blog_title和authorid創建索引，這時用and查詢滿足以上兩種條件的結果，如果查到一條的話它就是ref 但是如果匹配多條的話他就會進行索引合併。 #### unique_subquery 顧名思義 subquery可以看出這種類型跟子查詢有關係，同時大家知道子查詢在mysql中是十分不建議使用的一種查詢方式，當遇到子查詢時多思考如果通過連接查詢來優化。盡可能少的使用IN語句。 在某些 IN 查詢中使用此種類型,而不是常規的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) ```sql EXPLAIN SELECT * FROM blog where blog_author_id in (SELECT id from author where author_name = 'test1') ```  即使對 authorname 創建索引也是相同的執行計劃 對於這種情況你可以將其改寫成一個 left join 語句 ```sql SELECT blog.* FROM blog LEFT JOIN author ON blog_author_id = author.id WHERE author_name = 'test1' ``` 一樣的執行結果 但是執行計劃就是不同的如下圖  可見這種查詢就是用到了索引。效率可想而知。 #### index_subquery 在某些 IN 查詢中使用此種類型, 與 unique_subquery 類似,但是查詢的是非唯一性索引: ```sql value IN (SELECT key_column FROM single_table WHERE some_expr) ``` ```sql EXPLAIN SELECT * FROM author where id in ( SELECT blog_author_id from blog where blog_title = 'secend') ```  同樣的要盡量避免使用這種方式的查詢。 ### range 顧名思義，range意思就是范圍。因此可以解釋為：只檢索給定範圍的行,使用一個索引來選擇 行。 key 列顯示使用了哪個索引。當使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比較關鍵字列時,可 以使用 range。 這種類型解釋的很清楚了 稍微舉個栗子大家看看吧。 ```sql EXPLAIN SELECT * FROM blog where id > 2 ```  #### index 這種類型的意思也十分明顯，查詢過程中使用到了索引。解釋為： 全表掃描,只是掃描表的時候按照索引次序 進行而不是行。主要優點就是避免了排序, 但是開銷仍然非常大。舉個栗子 ```sql EXPLAIN SELECT * FROM blog ORDER BY id ```  #### all 最壞的情況,從頭到尾全表掃描 。性能最差的一種類型 遇到這種類型 你得想想 為什麼不建索引！為什麼 不改造 sql！改造sql也是為了讓mysql運行的時候盡可能的使用到索引， 這裡又牽扯出一個問題 如何建索引 數據庫維護索引也是一件十分費時費力的事情。詳細內容自行查詢 本人還未總結~~~ 這個就不舉例子了 大家看看上邊的例子 有很多連接查詢計劃中都存在all類型，順便想想如何優化。 解釋到這里大家對執行計劃所代表的效率含義基本上有個認識了，現在對後面的字段進行介紹。 ### possible_keys 很明顯了 它的意思就是有可能使用到的索引。 ### key MySQL 實際從 possible_key 選擇使用的索引。如果為 NULL,則沒有使用索引。很少的情況 下,MYSQL 會選擇優化不足的索引。這種情 況下,可以在 SELECT 語句中使用 USE INDEX (indexname)來強制使用一個索引或者用 IGNORE INDEX(indexname)來強制 MYSQL 忽略索引。 ### key_length 使用索引的長度。當然在不失精度的情況下 長度越小越好！ ### ref 顯示索引的那一列被引用到了。 ### rows MYSQL 認為必須檢查的用來返回請求數據的行數，越大越不好。說明沒有很好的使用到索引。 ### Extra 表示mysql解決查詢的詳細信息。 #### Using Index 表示使用到索引 #### using filesort 表示 MySQL 會對結果使用一個外部索引排序,而不是從表裡按索引次序讀到相關內容。可能在內存或者磁盤上進行排序。 MySQL 中無法利用索引完成的排序操作稱為“文件排序” 常見於 order by 和group by語句中。注意如果你對排序列創建索引mysql仍然會提示你使用的是filesort，所以對於這個字段應該有自己的判斷。 ```sql EXPLAIN SELECT * FROM blog order by blog_title ```  #### Using temporary 表示進行查詢時使用到臨時表。當使用到臨時表時，表示sql的效率需要進行相應的優化了。這種類型可能會在連接排序查詢中出現。 為了便於理解先舉一個例子。 ```sql EXPLAIN SELECT * FROM author,blog where author.id=blog.blog_author_id and blog.blog_title='first' order by author.id desc ```  這條語句是要查出寫first這篇博客的博主信息，並按用戶id排序。 先來看看mysql連接查詢算法 Nested Loop Join 通過驅動表的結果集，一條一條的按照連接條件查詢下個表中的記錄。 這裡出現了一個名詞 驅動表 驅動表定義： 1. 當連接條件確定時，查詢條件篩選後記錄少的為驅動表。 2. 當連接條件不確定時，行數少的表為驅動表。 按照上述定義，由於blog_tiltle經過篩選條件後查詢得到的記錄數為2，而未對author表進行條件過濾，因此該sql的驅動表為blog。 將過濾後的blog表的記錄一條條的對author表查詢，而後合併，這時需要按照author表的id字段進行排序，因此需要對合併結果(臨時表)進行排序。 如果按照驅動表排序，則可以直接排序而無需臨時表。 ```sql EXPLAIN SELECT * FROM author,blog where author.id=blog.blog_author_id and blog.blog_title='first' order by blog.id desc ``` 