文章已收錄Github精選，歡迎Star： https://github.com/yehongzhi

前言

在MySQL中，我們知道加索引能提高查詢效率，這基本上算是常識了。但是有時候，我們加了索引還是覺得SQL查詢效率低下，我想看看有沒有使用到索引，掃描了多少行，表的加載順序等等，怎麼檢視呢？其實MySQL自帶的SQL分析神器Explain執行計劃就能完成以上的事情！

Explain有哪些資訊

先确認一下試驗的MySQL版本，這裡使用的是

5.7.31

版本。

要精通SQL優化？那就學一學explain吧！前言Explain有哪些資訊Explain執行計劃詳解總結

隻需要在SQL語句前加上explain關鍵字就可以檢視執行計劃，執行計劃包括以下資訊：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra，總共12個字段資訊。

然後建立三個表：

CREATE TABLE `tb_student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='學生表';

CREATE TABLE `tb_class` (
  `id` INT(10) primary key not null auto_increment,
  `name` VARCHAR(36) NOT NULL,
    `stu_id` INT(10) NOT NULL,
    `tea_id` INT(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班級表';

CREATE TABLE `tb_teacher` (
  `id` INT(10) primary key not null auto_increment,
  `name` VARCHAR(36) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教師表';

Explain執行計劃詳解

explain的使用很簡單，隻需要在SQL語句前加上關鍵字

explain

即可，關鍵是怎麼看explain執行後傳回的字段資訊，這才是重點。

一、id

SELECT識别符。這是SELECT的查詢序列号。SQL執行的順序的辨別，SQL從大到小的執行。id列有以下幾個注意點：

id相同時，執行順序由上至下。
id不同時，如果是子查詢，id的序号會遞增，id值越大優先級越高，越先被執行。

EXPLAIN SELECT * FROM `tb_student` WHERE id IN (SELECT stu_id FROM tb_class WHERE tea_id IN(SELECT id FROM tb_teacher WHERE `name` = '馬老師'));

根據原則，當id不同時，SQL從大到小執行，id相同則從上到下執行。

二、select_type

表示select查詢的類型，用于區分各種複雜的查詢，例如普通查詢，聯合查詢，子查詢等等。

SIMPLE

表示最簡單的查詢操作，也就是查詢SQL語句中沒有子查詢、union等操作。

PRIMARY

當查詢語句中包含複雜查詢的子部分，表示複雜查詢中最外層的 select。

SUBQUERY

當

select

或

where

中包含有子查詢，該子查詢被标記為SUBQUERY。

DERIVED

在SQL語句中包含在

from

子句中的子查詢。

UNION

表示在union中的第二個和随後的select語句。

UNION RESULT

代表從

union

的臨時表中讀取資料。

EXPLAIN SELECT u.`name` FROM ((SELECT s.id,s.`name` FROM `tb_student` s) UNION (SELECT t.id,t.`name` FROM tb_teacher t)) AS u;

<union2,3>

代表是id為2和3的select查詢的結果進行union操作。

MATERIALIZED

MATERIALIZED

表示物化子查詢，子查詢來自視圖。

三、table

表示輸出結果集的表的表名，并不一定是真實存在的表，也有可能是别名，臨時表等等。

四、partitions

表示SQL語句查詢時比對到的分區資訊，對于非分區表值為NULL，當查詢的是分區表則會顯示分區表命中的分區情況。

五、type

需要重點關注的一個字段資訊，表示查詢使用了哪種類型，在

SQL

優化中是一個非常重要的名額，依次從優到差分别是：system > const > eq_ref > ref > range > index > ALL。

system和const

單表中最多有一條比對行，查詢效率最高，是以這個比對行的其他列的值可以被優化器在目前查詢中當作常量來處理。通常出現在根據主鍵或者唯一索引進行的查詢，system是const的特例，表裡隻有一條元組比對時（系統表）為system。

eq_ref

primary key 或 unique key 索引的所有部分被連接配接使用，最多隻會傳回一條符合條件的記錄，是以這種類型常出現在多表的join查詢。

ref

相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分字首，可能會找到多個符合條件的行。

range

使用索引選擇行，僅檢索給定範圍内的行。一般來說是針對一個有索引的字段，給定範圍檢索資料，通常出現在where語句中使用

bettween...and

、

<=

in

等條件查詢。

index

掃描全表索引，通常比ALL要快一些。

ALL

全表掃描，MySQL周遊全表來找到比對行，性能最差。

六、possible_keys

表示在查詢中可能使用到的索引來查找，别列出的索引并不一定是最終查詢資料所用到的索引。

七、key

跟possible_keys有所差別，key表示查詢中實際使用到的索引，若沒有使用到索引則顯示為NULL。

八、key_len

表示查詢用到的索引key的長度(位元組數)。如果單列索引，那麼就會把整個索引長度計算進去，如果是聯合索引，不是所有的列都用到，那麼就隻計算實際用到的列，是以可以根據key_len來判斷聯合索引是否生效。

九、ref

顯示了哪些列或常量被用于查找索引列上的值。常見的值有：

const

，

func

null

，字段名。

十、rows

mysql估算要找到我們所需的記錄，需要讀取的行數。可以通過這個資料很直覺的顯示

SQL

性能的好壞，一般情況下

rows

值越小越好。

十一、filtered

指傳回結果的行占需要讀到的行(rows列的值)的百分比，一般來說越大越好。

十二、Extra

表示額外的資訊。此字段能夠給出讓我們深入了解執行計劃進一步的細節資訊。

Using index

說明在select查詢中使用了覆寫索引。覆寫索引的好處是一條SQL通過索引就可以傳回我們需要的資料。

Using where

查詢時沒使用到索引，然後通過where條件過濾擷取到所需的資料。

Using temporary

表示在查詢時，MySQL需要建立一個臨時表來儲存結果。臨時表一般會比較影響性能，應該盡量避免。

有時候使用DISTINCT去重時也會産生Using temporary。

Using filesort

我們知道索引除了查詢中能起作用外，排序也是能起到作用的，是以當SQL中包含 ORDER BY 操作，而且無法利用索引完成排序操作的時候，MySQL不得不選擇相應的排序算法來實作，這時就會出現Using filesort，應該盡量避免使用Using filesort。

總結

一般優化SQL語句第一步是要知道這條SQL語句有哪些需要優化的，explain執行計劃就相當于一面鏡子，能把詳細的執行情況給開發者列出來。是以說善用explain執行計劃，能解決80%的SQL優化問題。

explain的資訊中，一般我們要關心的是type，看是什麼級别，如果是在網際網路公司一般需要在range以上的級别，接着關心的是Extra，有沒有出現filesort或者using template，一旦出現就要想辦法避免，接着再看key使用的是什麼索引，還有看filtered篩選比是多少。

這篇文章就講到這裡了，希望大家看完之後能對SQL優化有更深入的了解，感謝大家的閱讀。

覺得有用就點個贊吧，你的點贊是我創作的最大動力~

我是一個努力讓大家記住的程式員。我們下期再見！！！

能力有限，如果有什麼錯誤或者不當之處，請大家批評指正，一起學習交流！

要精通SQL優化？那就學一學explain吧！前言Explain有哪些資訊Explain執行計劃詳解總結

前言