性能分析

要說sql有問題，需要拿出證據(jù)，因此需要性能分析

Mysql查詢優(yōu)化器（Mysql Query Optimizer）

1. Mysql中有專門負責優(yōu)化SELECT語句的優(yōu)化器模塊，主要功能：通過計算分析系統(tǒng)中收集到的統(tǒng)計信息，為客戶端請求的Query提供他認為最優(yōu)的執(zhí)行計劃(它認為最優(yōu)的數(shù)據(jù)檢索方式，不見得是DBA認為是最優(yōu)的，這部分最耗費時間，如果知道m(xù)ysql底層是如何執(zhí)行一條sql，可以幫助我們更好地優(yōu)化sql)
2. 當客戶端向MySQL請求一條Query，命令解析器模塊完成請求分類，區(qū)別出是 SELECT 轉發(fā)給MysQL Query Optimizer，MysQL Query Optimizer首先會對整條Query進行優(yōu)化，處理掉一些常量表達式的預算，直接換算成常量值。并對Query中的查詢條件進行簡化和轉換，如去掉一些無用或顯而易見的條件、進行結構調整。然后分析 Query 中的 Hint 信息(如果有)，看顯示Hint信息是否可以完全確定該Query 的執(zhí)行計劃。如果沒有 Hint 或Hint 信息還不足以完全確定執(zhí)行計劃，則會讀取所涉及對象的統(tǒng)計信息，根據(jù) Query 進行寫相應的計算分析，然后再得出最后的執(zhí)行計劃。

Mysql常見瓶頸

• CPU: CPU在飽和的時候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時候
• IO：磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠大于內(nèi)存容量的時候
• 服務器硬件的性能瓶頸（機器性能本來就弱）: top,free,iostat和vmstat來查看系統(tǒng)的性能狀態(tài)

EXPLAIN簡介

EXPLAIN是什么？

EXPLAIN：SQL的執(zhí)行計劃，使用EXPLAIN關鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MySQL是如何處理SQL語句的，進而分析查詢語句或是表結構的性能瓶頸。

EXPLAIN怎么使用？

語法：explain關鍵字 + 所寫的SQL。

【表格形式查看】

explain select * from tbl_emp;

【列形式查看】

mysql> explain select * from pms_category \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1425
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

EXPLAIN能干嘛？

通過上面的表格可以查看以下信息：

id列 了解表的讀取順序

值有以下三種情況：

• id相同，執(zhí)行順序由上至下。

• id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行。

• id相同和不同 同時存在。永遠是id大的優(yōu)先級最高，id相等的時候從上到下順序執(zhí)行。

總結：id相同上下順序走，不同id大的先走

select_type列 數(shù)據(jù)讀取操作的操作類型。

select_type：數(shù)據(jù)查詢的類型，主要是用于區(qū)別，普通查詢、聯(lián)合查詢、子查詢等復雜查詢。

【常見值】

• SIMPLE：簡單的SELECT查詢，查詢中不包含子查詢或者UNION 。
• PRIMARY：查詢中如果包含任何復雜的子部分，最外層查詢則被標記為PRIMARY。
• SUBQUERY：在SELECT或者WHERE子句中包含了子查詢。
• DERIVED：在FROM子句中包含的子查詢被標記為DERIVED(衍生)，MySQL會遞歸執(zhí)行這些子查詢，把結果放在臨時表中。
• UNION：如果第二個SELECT出現(xiàn)在UNION之后，則被標記為UNION；若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為DERIVED。
• UNION RESULT：從UNION表獲取結果的SELECT。

table列 表的名字

標題已經(jīng)通俗易懂了，哈哈

type列 訪問類型詳解及排列

【type列常見值】

• system：一張表只有一行記錄（如系統(tǒng)參數(shù)表，每個參數(shù)字段只存儲一個值），這是const類型的特例（平時不會出現(xiàn)，這個也可以忽略不計）
• const：表示通過索引一次就找到了，const用于比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)，所以很快。如將主鍵置于where列表中，MySQL就能將該查詢轉化為一個常量。

• eq_ref：唯一性索引掃描，讀取本表中和關聯(lián)表表中的每行組合成的一行，查出來只有一條記錄。除了 system 和const 類型之外, 這是最好的連接類型。

• ref：非唯一性索引掃描，返回本表和關聯(lián)表某個值匹配的所有行，查出來有多條記錄。

【例子1】

【例子2】

• range：只檢索給定范圍的行，一般就是在WHERE語句中出現(xiàn)了BETWEEN、< >、in等的查詢。這種范圍掃描索引比全表掃描要好，因為它只需要開始于索引樹的某一點，而結束于另一點，不用掃描全部索引。

• index：Full Index Scan，全索引掃描，index和ALL的區(qū)別為index類型只遍歷索引樹，不用全表掃描。也就是說雖然ALL和index都是讀全表，但是index是從索引中讀的，ALL是從磁盤中讀取的。**因為索引文件通常比數(shù)據(jù)文件小，所以Index往往比ALL快。

• ALL：Full Table Scan，沒有用到索引，全表掃描。

從最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL沒有用到索引，其他級別都用到索引了，表示全表掃描。

**總結：**多看sql的type，如果是All，爭取優(yōu)化成其他的，一般來說，得保證查詢至少達到range級別，最好達到ref。

possible_keys列 哪些索引可能被使用

possible_keys：顯示可能應用在這張表中的索引，一個或者多個。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用。

key列 哪些索引被實際使用（判斷索引是否失效）

key：實際使用的索引。如果為NULL，則沒有使用索引。

查詢中如果使用了覆蓋索引，則該索引僅僅出現(xiàn)在key列表中，不出現(xiàn)在possible_keys中。

key_len列 索引中使用的字節(jié)數(shù)

key_len：表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度。key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的。

在不損失精度的情況下，長度越短越好。一般是精度要求更高（根據(jù)姓名和城市查詢一個人比只根據(jù)姓名查詢一個人精度更高），key_len越長。

key_len計算規(guī)則：https://blog.csdn.net/qq_34930488/article/details/102931490

ref列 表之間的引用

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)。哪些列或常量被用于查找索引列上的值。

由key_len可知t1表的idx col1 col2被充分使用，cl1匹配t2表的col1，co2匹配了一個常量，即’ac’

rows列 每張表有多少行被優(yōu)化器查詢

rows：根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需的記錄需要讀取的行數(shù)。

【沒建索引之前】

【建索引之后】

Extra列 包含不適合在其他列中顯示但十分重要的額外信息

Extra列也會有一系列的值，下面是這些值的解釋：

• Using filesort：說明MySQL會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取。MySQL中無法利用索引完成的排序操作成為"文件內(nèi)排序"。

總結：出現(xiàn)Using filesort盡快優(yōu)化

• Using temporary：使用了臨時表（查詢結束之后還要刪除）保存中間結果，MySQL在対查詢結果排序時使用了臨時表。常見于排序order by和分組查詢group by。臨時表対系統(tǒng)性能損耗很大。

總結：出現(xiàn)Using temporary盡快優(yōu)化

• Using index：表示相應的SELECT操作中使用了覆蓋索引，避免訪問了表的數(shù)據(jù)行，效率不錯！
• 如果同時出現(xiàn)Using where，表示索引被用來執(zhí)行索引鍵值的查找；
• 如果沒有同時出現(xiàn)Using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作。

覆蓋索引：就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取，換句話說查詢列要被所使用的索引覆蓋。 注意：如果要使用覆蓋索引，不要寫SELECT *，要寫出具體的字段。

• Using where：表明使用了WHERE過濾。
• Using join buffer：使用了連接緩存。如果join特別多，可以調大配置文件的buffer。
• impossible where：WHERE子句的值總是false，不能用來獲取任何元組。

• select tables optimized away：在沒有GROUPBY子句的情況下，基于索引優(yōu)化MIN/MAX操作或者對于MyISAM存儲引擎優(yōu)化COUNT(*)操作，不必等到執(zhí)行階段再進行計算查詢執(zhí)行計劃生成的階段即完成優(yōu)化。
• distinct：找到第一個匹配的元組后停止同樣值的動作

EXPLAIN案例

文章說明

本文章為本人學習尚硅谷的學習筆記，文章中大部分內(nèi)容來源于尚硅谷視頻（點擊學習尚硅谷相關課程），也有部分內(nèi)容來自于自己的思考，發(fā)布文章是想幫助其他學習的人更方便地整理自己的筆記或者直接通過文章學習相關知識，如有侵權請聯(lián)系刪除，最后對尚硅谷的優(yōu)質課程表示感謝。文章來源地址http://www.zghlxwxcb.cn/news/detail-558168.html

到了這里，關于【MYSQL高級】Mysql的SQL性能分析【借助EXPLAIN分析】的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！