一條sql的執行返回，時間過長，怎麼辦？如何優化，這就要從mysql的執行計劃說起

mysql的執行計劃：通過EXPLAIN SELECT * from A where X=? and Y=?

執行計劃一般包括如下：

1。id

id相同,執行順序由上而下，id不同,值越大越先被執行

2。selectType

  • SIMPLE： 表示此查詢不包含 UNION 查詢或子查詢
  • PRIMARY： 表示此查詢是最外層的查詢
  • SUBQUERY： 子查詢中的第一個 SELECT
  • UNION： 表示此查詢是 UNION 的第二或隨後的查詢
  • DEPENDENT UNION： UNION 中的第二個或後面的查詢語句, 取決於外面的查詢
  • UNION RESULT, UNION 的結果
  • DEPENDENT SUBQUERY: 子查詢中的第一個 SELECT, 取決於外面的查詢. 即子查詢依賴於外層查詢的結果.
  • DERIVED：衍生，表示導出表的SELECT（FROM子句的子查詢）

3.table

table表示查詢涉及的表或衍生的表

4.type

這個欄位是優化sql的重要欄位，也是我們判斷sql性能和優化程度重要指標。他的取值類型範圍：

  • const:當查詢最多匹配一行時，常出現於where條件是＝的情況，一般常用於等值掃描或者唯一性索引掃描，比如select * from A where A.id=2，這種非常快
  • system: 是const的特殊情況，此時說明表中只有一行數據，直接返回。
  • eq_ref:這種相當於多表之間關聯查詢，select A.* from A,B where A.id=B.userId,其中A中id對於B.userId是一一對應的，此時查詢效率較高
  • ref: 此類型通常出現在多表的聯合 查詢，使用了非唯一或非主鍵索引，或者匹配最左前綴規則索引的語句。比如select A.* from A where A.name=a and A.sex=m，name和sex構成一個組合索引
  • range: 表示使用索引範圍查詢，通過索引欄位範圍獲取表中部分數據記錄。這個類型通常出現在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。比如select A.* from A where A.time between a and c
  • index: 表示全索引掃描(full index scan)， index 類型掃描所有的索引， 而不掃描數據。index 類型通常出現在：所要查詢的數據直接在索引樹中就可以獲取到, 而不需要掃描數據，但是索引本身數量很多，內容很大，那麼執行效率很低。
  • ALL: 表示全表掃描，這個類型的查詢是性能最差的查詢之一。 那麼基本就是隨著表的數量增多，執行效率越慢。

執行效率：

ALL < index < range< ref < eq_ref < const < system。對於我們平時查詢執行計劃，優化sql來說，最好是避免ALL和index

5.possible_keys

它表示Mysql在執行該sql語句的時候，可能用到的索引信息，僅僅是可能，實際不一定會用到。

6.key

此欄位是 mysql 在當前查詢時所真正使用到的索引。 他是possible_keys的子集

7.key_len

表示查詢優化器使用了索引的位元組數，這個欄位可以評估組合索引是否完全被使用，這也是我們優化sql時，評估索引的重要指標

9.rows

rows 也是一個重要的欄位，mysql 查詢優化器根據統計信息，估算該sql返回結果集需要掃描讀取的行數，這個值相關重要，索引優化之後，掃描讀取的行數越多，說明要麼是索引設置不對，要麼是欄位傳入的類型之類的問題，說明要優化空間越大

10.filtered

filtered參數，它指返回結果的行占需要讀到的行(rows列的值)的百分比，就是百分比越高，說明需要查詢到數據越準確， 百分比越小，說明查詢到的數據量大，而結果集很少

11.extra

  • using filesort ：表示 mysql 對結果集進行外部排序，不能通過索引順序達到排序效果。一般有 using filesort都建議優化去掉，因為這樣的查詢 cpu 資源消耗大，延時大。
  • using index：覆蓋索引掃描，表示查詢在索引樹中就可查找所需數據，不用掃描表數據文件，往往說明性能不錯。
  • using temporary：查詢有使用臨時表, 一般出現於排序， 分組和多表 join 的情況， 查詢效率不高，建議優化。
  • using where ：sql使用了where過濾,效率較高。

實際案例優化

這是建表的sql信息：通過 show create table A;

有一個查詢語句，這是歷史遺留的sql，

select id from trade_iapreceipt where selectUserId=0 and selectOrderNo=2 and (status= 1 or status = 1) limit 1;

表面一看，沒啥問題，應該會命中索引userStatus, 在實際生產上，執行時間較長，看看它的執行計劃，

彷彿一看，也沒啥問題，索引也用到了，key欄位顯示，Extra顯示使用index排序了，但是根據類型type為index，說明這是個全索引掃描，效率很低的，最後命中的索引userStatus，這是在測試環境上數據比較少，到了生產環境數據量相當大，rt就升高了，這是看著沒有問題，然後仔細觀察，發現原來在表中，selectUserId是varchar類型，結果查詢的時候，傳入的是整數，mysql做了類型轉換，導致了全索引掃描。

之後sql進行了修改，發現明顯提升，用了索引，而且掃描的行數明顯減少，線上執行效率很高


推薦閱讀：
相关文章