mysql執行計劃explain

一、介紹：explain

執行計劃的幾個屬性：

1、id：表示查詢中select操作表的順序,按順序從大到依次執行（不是表中的自增主鍵！）。id值相同執行順序從上到下；id值不同時id值大的先執行。

2、select_type：這一列顯示了對應行是簡單還是複雜SELECT.取值如下:SIMPLE值意味著查詢不包括子查詢和UNION。查詢有任何複雜的子部分，則最外層標記為PRIMARY.取值如下：

3、type ：該屬性表示訪問型別,有很多種訪問型別。最常見的其中包括以下幾種: ALL(全表掃描), index(索引掃描),range(範圍掃描),ref (非唯一索引掃描),eq_ref(唯一索引掃描,),(const)常數引用, 訪問速度依次由慢到快。其中 : range(範圍)常見於 between and …, 大於 and 小於這種情況。

提示 : 慢SQL是否走索引,走了什麼索引,也就可以通過該屬性檢視了。

4、table ：輸出資料行所在的表的名稱

 5、possible_keys ：顧名思義,指出MySQL能使用哪些索引來優化查詢，查詢所涉及的列上的索引都會被列出，但不一定會被使用，算是個提示作用！

6、key：顯示MySQL實際使用的索引,其中就包括主鍵索引(PRIMARY),或者自建索引的名字。如果沒有可用的索引，則顯示為NULL。

7、key_len ：表示索引欄位的最大可能長度，KEY_LEN的長度由欄位定義計算而來，並非資料的實際長度，當 key 欄位的值為 null時，索引的長度就是 null。注意，key_len的值可以告訴你在聯合索引中 MySQL 會真正使用了哪些索引。

8、ref：連線匹配條件,如果走主鍵索引的話,該值為: const, 全表掃描的話,為null值，表示哪些列或常量被用於查詢索引列上的值。該表中所有符合檢索值的記錄都會被取出來和從上一個表中取出來的記錄作聯合。ref用於連線程式使用鍵的最左字首或者是該鍵不是 primary key 或 unique索引（換句話說，就是連線程式無法根據鍵值只取得一條記錄）的情況。當根據鍵值只查詢到少數幾條匹配的記錄時，這就是一個不錯的連線型別。 ref還可以用於檢索欄位使用=操作符來比較的時候。以下的幾個例子中，MySQL將使用 ref 來處理ref_table，和eq_ref的區別是-用到的索引是否唯一性。

9、rows （關鍵）：

掃描行數,也就是說，需要掃描多少行,才能獲取目標行數,一般情況下會大於返回行數。通常情況下,rows越小,效率越高, 大部分SQL優化，都是在減少這個值的大小。注意: 理想情況下掃描的行數與實際返回行數理論上是一致的,但這種情況及其少,如關聯查詢,掃描的行數就會比返回行數大大增加)。

10、Extra ：這個屬性非常重要,該屬性中包括執行SQL時的真實情況資訊,如上面所屬,使用到的是”using where”，表示使用where篩選得到的值,常用的有:“Using temporary”: 使用臨時表 “using filesort”: 使用檔案排序。

二、單表查詢舉例：

1、準備工作：如現有表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '名稱',
  `age` int(11) DEFAULT NULL COMMENT '年齡',
  `year` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

2、查詢：

下面檢視此查詢語句的執行計劃：

3、給name加上索引：

ALTER TABLE `test`
ADD INDEX `index_name` (`name`) ;

再看下執行計劃：

 只掃描了一行，效能比較高。

三、連線查詢舉例：

課程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10))
資料200條-------------------------

學生表:
create table Student(
id int PRIMARY KEY,
name varchar(10))
資料80000條-----------------------

學生成績表SC
CREATE table SC(
    sc_id int PRIMARY KEY,
    s_id int,
    c_id int,
    score int)
資料80w條--------------------------

select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

執行時間：30248.271s（29.648s）
為什麼這麼慢，先來檢視下查詢計劃：
EXPLAIN  select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

 

發現沒有用到索引，type全是ALL，先給sc表的c_id和score建個索引：
CREATE index sc_c_id_index on SC(c_id);     CREATE index sc_score_index on SC(score);

再次執行上述查詢語句，時間為: 1.054s(2.428s)。

但是2s的時間還是太長了，還能進行優化嗎，仔細看執行計劃：

 接下來再次優化：這次我們用連線查詢！（先刪除索引）
alter table SC drop index sc_c_id_index;
alter table SC drop index sc_score_index;
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100

再次執行上述查詢語句，時間為: 0.088s

EXPLAIN SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
再看執行計劃：

發現一個ALL，extra欄位中顯示where
所以我們嘗試加索引：
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

再次執行上條sql：
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
再次執行上述查詢語句，時間為: 0.010s

再看sql執行計劃：

總結：
1.mysql巢狀子查詢效率確實比較低
2.可以將其優化成連線查詢
3.連線表時，可以先用where條件對錶進行過濾，然後做表連線（雖然mysql會對連表語句做優化）
4.建立合適的索引，必要時建立多列聯合索引
5.當然我們最主要的是要學會分析sql執行計劃，mysql會對sql進行優化，所以分析執行計劃很重要 

6、索引優化：上面講到子查詢的優化，以及如何建立索引，而且在多個欄位索引時，分別對欄位建立了單個索引，後面發現其實建立聯合索引效率會更高，尤其是在資料量較大，單個列區分度不高的情況下。create index sc_c_id_score_index on SC(c_id,score);時間為: 0.008s(由於資料量有限，效果不明顯，資料大的時候效率更高)。