使用explain關鍵字可以模拟優化器執行SQL查詢語句，進而知道MySQL是如何處理你的SQL語句的，分析你的查詢語句或是表結構的性能瓶頸。
explain執行計劃包含的資訊
每列的内容
列
含義
id
執行計劃的id标志
select_type
select的類型
table
輸出記錄的表
partitions
比對的分區
type
join的類型
possible_keys
優化器可能選擇的索引
key
優化器實際選擇的索引
key_len
使用索引的位元組長度
ref
進行比較的索引列
rows
優化器預估的記錄數量額外的顯示選項
filtered
根據條件過濾得到的記錄的百分比
extra
額外的顯示選項
1、執行計劃的 id
select 查詢的序列号，辨別執行的順序
- id 相同，執行順序由上至下
- id 不同，如果是子查詢，id 的序号會遞增，id 值越大優先級越高，越先被執行
2、執行計劃的 select_type
查詢的類型，主要是用于區分普通查詢、聯合查詢、子查詢等。
- SIMPLE：簡單的 select 查詢，查詢中不包含子查詢或者 union
- PRIMARY：查詢中包含子部分，最外層查詢則被标記為 primary
- UNION：表示 union 中的第二個或後面的 select 語句
- DEPENDENT UNION：union 中的第二個或後面的 select 語句，依賴于外面的查詢
- UNION RESULT：union 的結果
- SUBQUERY：子查詢中的第一個 select
- DEPENDENT SUBQUERY：子查詢中的第一個 select，依賴于外面的查詢
- DERIVED：派生表的 select（from 子句的子查詢）
- MATERIALIZED：物化子查詢
- 産生中間臨時表（實體）
- 臨時表自動建立索引并和其他表進行關聯，提高性能
- 和子查詢的差別是，優化器将可以進行 MATERIALIZED 的語句自動改寫成 join，并自動建立索引
- UNCACHEABLE SUBQUERY：不會被緩存的并且對于外部查詢的每行都要重新計算的子查詢
- UNCACHEABLE UNION：屬于不能被緩存的 union 中的第二個或後面的 select 語句
3、執行計劃的 table
查詢涉及到的表。
- 通常就是使用者操作的使用者表
- ：由 ID 等于 M，N 的語句 union 得到的結果表
- ：派生表，由 ID 等于 N 的語句查詢得到的結果表
- ：由子查詢物化産生的表，由 ID 等于 N 的語句查詢得到的結果表
4、執行計劃的 type
通路類型，SQL 查詢優化中一個很重要的名額，結果值從好到壞依次是：system > const > eq_ref > ref > range > index > ALL。
- system：系統表，少量資料，往往不需要進行磁盤IO
- const：常量連接配接
- eq_ref：主鍵索引（primary key）或者非空唯一索引（unique not null）等值掃描
- ref：非主鍵非唯一索引等值掃描
- range：範圍掃描
- index：索引樹掃描
- ALL：全表掃描（full table scan）
const:
資料準備： CREATE TABLE user( id int(11) NOT NULL, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); 然後執行: explain select * from user where id=1;
const 掃描的條件為：
- 命中主鍵（primary key）或者唯一（unique）索引
- 被連接配接的部分是一個常量（const）值
如上例，id 是 主鍵索引，連接配接部分是常量1。
eq_ref
資料準備: CREATE TABLE user( id int(11) NOT NULL, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) NOT NULL, age int(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然後執行: explain select * from user,user_ex where user.id=user_ex.id;
eq_ref 掃描的條件為，對于前表的每一行（row），後表隻有一行被掃描。
再細化一點：
- join 查詢
- 命中主鍵（primary key）或者非空唯一（unique not null）索引
- 等值連接配接；
如上例，id 是主鍵，該 join 查詢為 eq_ref 掃描。
資料準備: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) DEFAULT NULL, age int(11) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然後執行: explain select * from user,user_ex where user.id=user_ex.id;
如果把上例 eq_ref 案例中的主鍵索引，改為普通非唯一（non unique）索引。就由 eq_ref 降級為了 ref，此時對于前表的每一行（row），後表可能有多于一行的資料被掃描。
select * from user where id=1;
當 id 改為普通非唯一索引後，常量的連接配接查詢，也由 const 降級為了 ref，因為也可能有多于一行的資料被掃描。
ref 掃描，可能出現在 join 裡，也可能出現在單表普通索引裡，每一次比對可能有多行資料傳回，雖然它比 eq_ref 要慢，但它仍然是一個很快的 join 類型。
range
資料準備: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, KEY id (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'),(2,'zhangsan'),(3,'lisi'),(4,'wangwu'),(5,'zhaoliu'); 然後執行: explain select from user where id between 1 and 4; explain select from user where id in(1,2,3); explain select * from user where id > 3;
ange 掃描就比較好了解了，它是索引上的範圍查詢，它會在索引上掃碼特定範圍内的值。
像上例中的 between，in，> 都是典型的範圍（range）查詢。
index
explain select count(*) from user;
如上例，id 是主鍵，該 count 查詢需要通過掃描索引上的全部資料來計數，它僅比全表掃描快一點。
ALL
資料準備: CREATE TABLE user ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user values(1,'shenjian'); insert into user values(2,'zhangsan'); insert into user values(3,'lisi'); CREATE TABLE user_ex ( id int(11) DEFAULT NULL, age int(11) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into user_ex values(1,18); insert into user_ex values(2,20); insert into user_ex values(3,30); insert into user_ex values(4,40); insert into user_ex values(5,50); 然後執行: explain select * from user,user_ex where user.id=user_ex.id;
如果 id 上不建索引，對于前表的每一行（row），後表都要被全表掃描。
文章中，這個相同的 join 語句出現了三次：
- 掃描類型為 eq_ref，此時 id 為主鍵
- 掃描類型為 ref，此時 id 為非唯一普通索引
- 掃描類型為 ALL，全表掃描，此時id上無索引
總結
- explain 結果中的 type 字段，表示（廣義）連接配接類型，它描述了找到所需資料使用的掃描方式；
- 常見的掃描類型有：system>const>eq_ref>ref>range>index>ALL，其掃描速度由快到慢；
- 各類掃描類型的要點是：
- system 最快：不進行磁盤 IO
- const：PK 或者 unique 上的等值查詢
- eq_ref：PK 或者 unique 上的 join 查詢，等值比對，對于前表的每一行，後表隻有一行命中
- ref：非唯一索引，等值比對，可能有多行命中
- range：索引上的範圍掃描，例如：between、in、>
- index：索引上的全集掃描，例如：InnoDB 的 count
- ALL 最慢：全表掃描
- 建立正确的索引，非常重要；
- 使用 explain 了解并優化執行計劃，非常重要；
5、執行計劃 possible_keys
查詢過程中有可能用到的索引。
6、執行計劃 key
實際使用的索引，如果為 NULL ，則沒有使用索引。
7、執行計劃 rows
根據表統計資訊或者索引選用情況，大緻估算出找到所需的記錄所需要讀取的行數。
8、執行計劃 filtered
表示傳回結果的行數占需讀取行數的百分比， filtered 的值越大越好。
9、執行計劃 Extra
十分重要的額外資訊。
- Using filesort：MySQL 對資料使用一個外部的檔案内容進行了排序，而不是按照表内的索引進行排序讀取。
- Using index：表示 SQL 操作中使用了覆寫索引（Covering Index），避免了通路表的資料行，效率高。
- Using index condition：表示 SQL 操作命中了索引，但不是所有的列資料都在索引樹上，還需要通路實際的行記錄。
- Using index for group by：優化器隻需要使用索引就能處理 group by 或 distinct 語句。
- Using join buffer (Block Nested Loop)：表示 SQL 操作使用了關聯查詢或者子查詢，且需要進行嵌套循環計算。
- Using MRR：優化器使用 MRR 優化
- Using temporary：使用臨時表儲存中間結果，也就是說 MySQL 在對查詢結果排序時使用了臨時表，常見于order by 或 group by。
- Using where：表示 SQL 操作使用了 where 過濾條件。
- Select tables optimized away：基于索引優化 MIN/MAX 操作或者 MyISAM 存儲引擎優化 COUNT(*) 操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即可完成優化。
資料準備: create table user( id int(11) not null, name varchar(20) default null, sex varchar(5) default null, primary key (id), key name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 使用者表：id 主鍵索引，name 普通索引（非唯一），sex 無索引。 四行記錄：其中 name 普通索引存在重複記錄 lisi。
Using filesort
執行: explain select * from user order by sex;
Extra 為 Using filesort 說明，得到所需結果集，需要對所有記錄進行檔案排序。
這類 SQL 語句性能極差，需要進行優化。
典型的，在一個沒有建立索引的列上進行了 order by，就會觸發 filesort，常見的優化方案是，在 order by 的列上添加索引，避免每次查詢都全量排序。
Using temporary
執行： explain select * from user group by name order by sex;
(備注：一開始執行時報錯 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 原因是 ： 錯誤1055（42000）：選擇清單的表達式1不在GROUP BY子句中，并且包含未聚合的列“test.fruits.f_id”，它在功能上不依賴GROUP BY子句中的列；這與SQL_mode=only_full_group_by不相容） 解決辦法： 在mysql中輸入 mysql> set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; ) 重新查詢就可以了
Extra 為 Using temporary 說明，需要建立臨時表（temporary table）來暫存中間結果。
這類 SQL 語句性能較低，往往也需要進行優化。
典型的 group by 和 order by 同時存在，且作用于不同的字段時，就會建立臨時表，以便計算出最終的結果集。
臨時表存在兩種引擎，一種是 Memory 引擎，一種是 MyISAM 引擎，如果傳回的資料在 16M 以内（預設），且沒有大字段的情況下，使用 Memory 引擎，否則使用 MyISAM 引擎。
Using index
執行: explain select id from user;
Extra 為 Using index 說明，SQL 所需要傳回的所有列資料均在一棵索引樹上，而無需通路實際的行記錄。
這類 SQL 語句往往性能較好。
Using index condition
執行: explain select id, name, sex from user where name='shenjian';
Extra 為 Using index condition 說明，确實命中了索引，但不是所有的列資料都在索引樹上，還需要通路實際的行記錄。
這類 SQL 語句性能也較高，但不如 Using index。
Using where
explain select * from user where sex='no';
Extra 為 Using where 說明，查詢的結果集使用了 where 過濾條件，比如上面的 SQL 使用了
sex = 'no'
的過濾條件
Select tables optimized away
explain select max(id) from user;
比如上面的語句查詢 id 的最大值，因為 id 是主鍵索引，根據 B+Tree 的結構，天然就是有序存放的，是以不需要等到執行階段再進行計算，查詢執行計劃生成的階段即可完成優化。
Using join buffer (Block Nested Loop)
explain select * from user where id in (select id from user where sex='no');
Extra 為 Using join buffer (Block Nested Loop) 說明，需要進行嵌套循環計算。内層和外層的 type 均為 ALL，rows 均為4，需要循環進行4*4次計算。
這類 SQL 語句性能往往也較低，需要進行優化。
典型的兩個關聯表 join，關聯字段均未建立索引，就會出現這種情況。常見的優化方案是，在關聯字段上添加索引，避免每次嵌套循環計算。
更多技術資訊可檢視雲掣官網
https://www.dtstack.com/dtsmart/