1. 首頁 > 生活百科 > >

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

生活百科

大家好，我是melo，一名大三后臺練習生
專欄回顧
  • 索引的原理&&設計原則歡迎關注本專欄：MySQL高級篇
本篇速覽在我們上一篇文章中，講到了索引的原理&&設計原則 ， 知道了索引如何使用 。emm？那具體什么場景需要用到索引，我們要怎么分析SQL語句，并對其進行優化呢，這篇將從以下幾點帶你攻破ta：
  • 詳解explain分析SQL
  • 索引失效的幾個場景
    • ......
  • SQL優化的幾個場景
    • 大批量插入
    • order by
    • group by
    • limit分頁
    • insert操作
    • 嵌套查詢
    • or條件
注意，本文MySQL版本為5.6.43，部分結論在其他版本可能不適用?。。?
  • 本篇篇幅較長 ， 全文近8500字，可以收藏下來慢慢啃，沒事就掏出來翻閱翻閱 。
建議通過側邊欄目錄檢索對您有幫助的部分，其中有emoji表情前綴屬于重點部分，覺得對您有幫助的話 ， melo還會持續更進完善本篇文章和MySQL專欄 。
好 ， 現在我們已經掌握了索引的基本原理和使用方法了，要來大干一場優化SQL了！等等，我們要優化什么SQL來著，褲子都脫了，結果沒對象可以.....
「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
別著急，這篇既然掛著MySQL高級篇，自然MySQL還是很高級的，給我們提供了幾種方法，來為我們找到SQL，并分析SQL 。本篇，我們先著重講解如何分析，具體如何找到SQL，后續的實戰篇，我們再來詳細談一談 。
【一、explain】分析SQL
「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
explain中 ， 包含了如下幾個字段（不同版本可能會有所差異）：
字段含義idselect查詢的序列號 ， 是一組數字，表示的是查詢中執行select子句或者是操作表的順序 。select_type表示 SELECT 的類型，常見的取值有 SIMPLE（簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢 ， 即外層的查詢）、UNION（UNION 中的第二個或者后面的查詢語句）、SUBQUERY（子查詢中的第一個 SELECT）等table輸出結果集的表partitions查詢時匹配到的分區信息，對于非分區表值為NULL，當查詢的是分區表時，partitions顯示分區表命中的分區情況 。type表示表的連接類型 ， 性能由好到差的連接類型為( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )possible_keys表示查詢時，可能使用的索引key表示查詢時，實際使用的索引key_len索引字段的長度 ， 可用來區分長短索引rows掃描行的數量filtered表里符合條件的記錄數所占的百分比extra執行情況的說明和描述
看完是不是很懵，感覺好多要記憶的，別著急，下邊我們通過實際案例，來加深記憶
idid 字段是 select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序 。id 情況有三種 ：?
  1. 此處只是單表查詢，id只有一個

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
  1. id一樣，則從上到下

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
  1. id不同，則id值越大，優先級越高
此處是嵌套子查詢 ， 最內部的子查詢，自然是最先執行的

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
簡而言之：
  • id值越大，優先級越高；
  • id值一樣，則從上到下；
select_typeSELECT_TYPE含義SIMPLE簡單的select查詢 ， 查詢中不包含子查詢或者UNIONPRIMARY查詢中若包含任何復雜的子查詢，最外層查詢標記為該標識SUBQUERY在SELECT 或 WHERE 列表中包含了子查詢DERIVED在FROM 列表中包含的子查詢，被標記為 DERIVED（衍生） MYSQL會遞歸執行這些子查詢，把結果放在臨時表中UNION若第二個SELECT出現在UNION之后，則標記為UNION ； 若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為 ： DERIVEDUNION RESULT從UNION表獲取結果的SELECTPRIMARY,SUBQUERY
「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
DERIVED(需要臨時表 ， 自然比上述效率低)
「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

文章插圖
typeTYPE含義NULLMySQL不訪問任何表，索引，直接返回結果system表只有一行記錄(等于系統表) ， 這是const類型的特例，一般不會出現const表示通過索引一次就找到了，const 常用于primary key 或者 unique 索引(本質上都是唯一索引) 。因為只匹配一行數據 ， 所以很快 。如將主鍵置于where列表中，MySQL 就能將該查詢轉換為一個常量 。const于將 "主鍵" 或 "唯一" 索引的所有部分與常量值進行比較eq_ref類似ref，區別在于使用的是唯一索引，使用主鍵的關聯查詢，關聯查詢出的記錄只有一條 。常見于主鍵或唯一索引掃描ref非唯一性索引掃描 ， 返回匹配某個單獨值的所有行 。本質上也是一種索引訪問，返回所有匹配某個單獨值的所有行（多個）range只檢索給定返回的行，使用一個索引來選擇行 。where 之后出現 between，< , > , in 等操作 。indexindex 與 ALL的區別為 index 類型只是遍歷了索引樹 ，  通常比ALL 快， ALL 是遍歷數據文件 。all將遍歷全表以找到匹配的行結果值從最好到最壞以此是：

推薦閱讀