MySQL explain 應用詳解（吐血整理）

什麼是explain

使用優化器可以模擬優化器執行SQL查詢語句，從而知道MySQL怎麼處理你的SQL語句的，分析你的查詢語句和表結構的效能瓶頸。

explain能夠幹什麼

• 讀取表的順序
• 哪些索引能夠被使用
• 數據讀取操作的操作型別
• 哪些索引能夠被實際使用
• 表之間的參照
• 每張表有多少行被物理查詢

建立一個學習用的數據庫

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mydb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mydb`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`id`,`name`) values 
(1,'語文'),(2,'高等數學'),(3,'視聽說'),(4,'體育'),(5,'馬克思概況'),(6,'民族理論'),(7,'毛中特'),(8,'計算機基礎'),(9,'深度學習'),(10,'Java程式設計'),(11,'c語言程式設計'),(12,'操作系統'),(13,'計算機網路'),(14,'計算機組成原理'),(15,'數據結構'),(16,'數據分析'),(17,'大學物理'),(18,'數位邏輯'),(19,'嵌入式開發'),(20,'需求工程');

/*Table structure for table `stu_course` */

DROP TABLE IF EXISTS `stu_course`;

CREATE TABLE `stu_course` (
  `sid` int(10) NOT NULL,
  `cid` int(10) NOT NULL,
  PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `stu_course` */

insert  into `stu_course`(`sid`,`cid`) values 
(1,2),(1,4),(1,14),(1,16),(1,19),(2,4),(2,8),(2,9),(2,14),(3,13),(3,14),(3,20),(4,5),(4,8),(4,9),(4,11),(4,16),(5,4),(5,8),(5,9),(5,11),(5,12),(5,16),(6,2),(6,14),(6,17),(7,1),(7,8),(7,15),(8,2),(8,3),(8,7),(8,17),(9,1),(9,7),(9,16),(9,20),(10,4),(10,12),(10,14),(10,20),(11,3),(11,9),(11,16),(12,3),(12,7),(12,9),(12,12),(13,1),(13,5),(13,13),(14,1),(14,3),(14,18),(15,1),
(15,9),(15,15),(16,2),(16,7);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `name_age` (`name`,`age`),
  KEY `id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`age`) values 
(25,'乾隆',17),(14,'關羽',43),(13,'劉備',12),(28,'劉永',12),(21,'後裔',12),(30,'呂子喬',28),(18,'嬴政',76),(22,'孫悟空',21),(4,'安其拉',24),(6,'宋江',22),(26,'康熙',51),(29,'張偉',26),(20,'張郃',12),(12,'張飛',32),(27,'朱元璋',19),(11,'李世民',54),(9,'李逵',12),(8,'林沖',43),(5,'橘右京',43),(24,'沙和尚',25),(23,'豬八戒',22),(15,'王與',21),(19,'王建',23),(10,'王莽',43),(16,'秦叔寶',43),(17,'程咬金',65),(3,'荊軻',21),(2,'諸葛亮',71),(7,'鍾馗',23),(1,'魯班',21);

這個數據庫實際上的業務是:學生表 - 選課表 - 課程表

如何使用explain

使用而explain很簡單就是，在你書寫的SQL語句加一個單詞 - explain，然後將 explain+ SQL執行後會出現一個表，這個表會告訴你MySQL優化器是怎樣執行你的SQL的。

就比如執行下面 下麪一句語句：

EXPLAIN SELECT * FROM student

MySQL會給你反饋下面 下麪一個資訊：

    id  select_type  table    partitions  type    possible_keys  key       key_len  ref       rows  filtered  Extra        
------  -----------  -------  ----------  ------  -------------  --------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      index   (NULL)         name_age  68       (NULL)      30    100.00  Using index  

具體這些資訊是幹什麼的，會對你有什麼幫助，會在下面 下麪告訴你。

explain各個欄位代表的意思

• id ：select查詢的序列號，包含一組數位，表示查詢中執行select子句或操作表的順序
• select_type ：查詢型別 或者是 其他操作型別
• table ：正在存取哪個表
• partitions ：匹配的分割區
• type ：存取的型別
• possible_keys ：顯示可能應用在這張表中的索引，一個或多個，但不一定實際使用到
• key ：實際使用到的索引，如果爲NULL，則沒有使用索引
• key_len ：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度
• ref ：顯示索引的哪一列被使用了，如果可能的話，是一個常數，哪些列或常數被用於查詢索引列上的值
• rows ：根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需讀取的行數
• filtered ：查詢的錶行佔表的百分比
• Extra ：包含不適合在其它列中顯示但十分重要的額外資訊

上面介紹了每個欄位的意思，可以大體看一下，下面 下麪會逐一介紹每個欄位表示的啥？該關注什麼？

id與table欄位

爲什麼要將id和table放在一起講呢？因爲通過這兩個欄位可以完全判斷出你的每一條SQL語句的執行順序和表的查詢順序。

先看id後看table，id和table在SQL執行判斷過程中的關係就像是足球聯賽的積分榜，首先一個聯賽的球隊排名應該先看積分，積分越高的球隊排名越靠前，當兩支或多隻球隊的積分一樣高怎麼辦呢？那我們就看淨勝球，淨勝球越多的球隊，排在前面。而在explain中你可以把id看作是球隊積分，table當作是淨勝球。

比如說我們explain一下這一條SQL：

EXPLAIN
SELECT 
	S.id,S.name,S.age,C.id,C.name
FROM course C JOIN stu_course SC ON C.id = SC.cid
JOIN student S ON S.id = SC.sid

結果是這樣：

    id  select_type  table   partitions  type    possible_keys        key      key_len  ref      
------  -----------  ------  ----------  ------  -------------------  -------  -------  ----------- 
     1  SIMPLE       SC      (NULL)      index   PRIMARY              PRIMARY  8        (NULL)     
     1  SIMPLE       C       (NULL)      eq_ref  PRIMARY              PRIMARY  4        mydb.SC.cid 
     1  SIMPLE       S       (NULL)      eq_ref  PRIMARY,id_name_age  PRIMARY  4        mydb.SC.sid 

我們看到id全是1，那就說明光看id這個值是看不出來每個表的讀取順序的，那我們就來看table這一行，它的讀取順序是自上向下的，所以，這三個表的讀取順序應當是：SC - C - S。

再來看一條SQL

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` = (
	SELECT SC.`cid` 
	FROM stu_course AS SC 
	WHERE SC.`sid` = 
	(
		SELECT 
			S.`id` 
		FROM student AS S
		WHERE  S.`name` = "安其拉"
	) ORDER BY SC.`cid` LIMIT 1
)

這條語句是查詢結果是：一個叫安其拉的學生選的課裏面，課程id最小的一門課的資訊，然後來看一下explain的結果吧！

    id  select_type  table   partitions  type    possible_keys  key      key_len  ref    
------  -----------  ------  ----------  ------  -------------  -------  -------  ------  
     1  PRIMARY      C       (NULL)      const   PRIMARY        PRIMARY  4        const   
     2  SUBQUERY     SC      (NULL)      ref     PRIMARY        PRIMARY  4        const  
     3  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const 

此時我們發現id是不相同的，所以我們很容易就看出表讀取的順序了是吧！C - SC - S

注意！！！！！！你仔細看一下最裏面的子查詢是查詢的哪個表，是S這張表，然後外面一層呢？是SC這張表，最外面這一層呢？是C這張表，所以執行順序應該是啥呢？是…是…難道是S - SC - C嗎？是id越大的table讀取越在前面嗎？是的！這就像剛纔說的足球聯賽積分，分數越高的球隊的排序越靠前。

當然還有下面 下麪這種情況

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` IN (
	SELECT SC.`cid` 
	FROM stu_course AS SC 
	WHERE SC.`sid` = 
	(
		SELECT 
			S.`id` 
		FROM student AS S
		WHERE  S.`name` = "安其拉"
	)
)

這個查詢是：查詢安其拉選課的課程資訊

    id  select_type  table   partitions  type    possible_keys  key      key_len  ref           
------  -----------  ------  ----------  ------  -------------  -------  -------  ----------- 
     1  PRIMARY      SC      (NULL)      ref     PRIMARY        PRIMARY  4        const       
     1  PRIMARY      C       (NULL)      eq_ref  PRIMARY        PRIMARY  4        mydb.SC.cid 
     3  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const        

結果很明確：先看id應該是S表最先被讀取，SC和C表id相同，然後table中SC更靠上，所以第二張讀取的表應當是SC，最後讀取C。

select_type欄位

• SIMPLE 簡單查詢，不包括子查詢和union查詢

  EXPLAIN 
  SELECT * FROM student JOIN stu_course ON student.`id` = sid
  

      id  select_type  table       partitions  type    possible_keys        key      
  ------  -----------  ----------  ----------  ------  -------------------  --------  
       1  SIMPLE       student     (NULL)      index   PRIMARY,id_name_age  name_age  
       1  SIMPLE       stu_course  (NULL)      ref     PRIMARY              PRIMARY  
  

• PRIMARY 當存在子查詢時，最外面的查詢被標記爲主查詢

• SUBQUERY 子查詢

  EXPLAIN
  SELECT SC.`cid` 
  FROM stu_course AS SC 
  WHERE SC.`sid` = 
  (
  	SELECT 
  		S.`id` 
  	FROM student AS S
  	WHERE  S.`name` = "安其拉"
  )
  

      id  select_type  table   partitions  type    possible_keys  key      key_len  ref      
  ------  -----------  ------  ----------  ------  -------------  -------  -------  ------ 
       1  PRIMARY      SC      (NULL)      ref     PRIMARY        PRIMARY  4        const  
       2  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const   
  

• UNION 當一個查詢在UNION關鍵字之後就會出現UNION

• UNION RESULT 連線幾個表查詢後的結果

  EXPLAIN
  SELECT * FROM student WHERE id = 1
  UNION
  SELECT * FROM student WHERE id = 2
  

      id  select_type   table       partitions  type    possible_keys        key      
  ------  ------------  ----------  ----------  ------  -------------------  ------- 
       1  PRIMARY       student     (NULL)      const   PRIMARY,id_name_age  PRIMARY  
       2  UNION         student     (NULL)      const   PRIMARY,id_name_age  PRIMARY 
  (NULL)  UNION RESULT  <union1,2>  (NULL)      ALL     (NULL)               (NULL)   
  

  上面可以看到第三行table的值是<union1,2>

• DERIVED 在FROM列表中包含的子查詢被標記爲DERIVED（衍生），MySQL
  會遞回執行這些子查詢，把結果放在臨時表中
  MySQL5.7+ 進行優化了，增加了derived_merge（派生合併），預設開啓，可加快查詢效率

  如果你想瞭解更詳細的派生合併請點選這裏

  當你的MySQL是5.7及以上版本時你要將derived_merge關閉後才能 纔能看到DERIVED 狀態

  set session optimizer_switch='derived_merge=off';
  set global optimizer_switch='derived_merge=off'; 
  

  EXPLAIN
  SELECT * FROM 
  (
  	SELECT * 
  	FROM student AS S JOIN stu_course AS SC 
  	ON S.`id` = SC.`sid`
  ) AS SSC
  

      id  select_type  table       partitions  type    possible_keys        key       
  ------  -----------  ----------  ----------  ------  -------------------  -------- 
       1  PRIMARY      <derived2>  (NULL)      ALL     (NULL)               (NULL)    
       2  DERIVED      S           (NULL)      index   PRIMARY,id_name_age  name_age 
       2  DERIVED      SC          (NULL)      ref     PRIMARY              PRIMARY  
  

  上面我們觀察，最外層的主查詢的表是,而S和SC表的select_type都是DERIVED,這說明S和SC都被用來做衍生查詢，而這兩張表查詢的結果組成了名爲的衍生表，而衍生表的命名就是<select_type + id>。

partitions欄位

該列顯示的爲分割區表命中的分割區情況。非分割區表該欄位爲空（null）。

type欄位

注意！！！注意！！！重點來了！

首先說一下這個欄位，要記住以下10個狀態，（從左往右，越靠左邊的越優秀）

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

• NULL MySQL能夠在優化階段分解查詢語句，在執行階段用不着再存取表或索引

  有沒有這樣一種疑惑，不查詢索引也不查詢表那你的數據是從哪裏來的啊？誰說SELECT語句必須查詢某樣東西了？

  EXPLAIN SELECT 5*7
  

      id  select_type  table   partitions  type    possible_keys  key     
  ------  -----------  ------  ----------  ------  -------------  ------ 
       1  SIMPLE       (NULL)  (NULL)      (NULL)  (NULL)         (NULL) 
  

  我就簡簡單單算個數不好嗎？好啊