MySQL中一條查詢語句的執行全過程是怎樣的？
mysql作為最常用的關系型數據庫，無論是在應用還是在面試中都是必須掌握的技能。
要印在腦子里面的東西
DDL:數據定義，它用來定義數據庫對象，包括庫，表，列，通過ddl我們可以創建，刪除，修改數據庫和表結構；
DML:數據操作語言，增加刪除修改數據表中的記錄；
DCL:數據控制語言，定義訪問權限和安全級別；
DQL:數據查詢語言，用它來查詢想要的記錄。
SQL執行順序:
- from;
- join
- on
- where;
- group by;
- avg,sum.... 使用聚集函數進行計算;
- having;
- select;
- distinct;
- order by;
- limit;
今天我們一起討論下如何查看mysql的執行計劃。
Explain是mysql中sql調優的重要工具，它可以模擬mysql優化器執行sql語句，并通過可視化說明分析出查詢語句的執行信息，有助于我們分析出sql語句的性能瓶頸。
使用示例：
explain select * from t where name='123'
在查詢sql語句前面加explain關鍵字，mysql就會在查詢的時候設置一個標記，mysql在處理的時候就不會去真正執行這條sql語句，而是返回這條語句的執行計劃。但是如果from中有子查詢，子查詢會真正的執行，并且會將結果先放入臨時表中。
1準備
我們準備幾個表先，如果下面有需要案例來說明的內容，我們就用這幾個表為例。
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` INT ( 11 ) NOT NULL,
`name` VARCHAR ( 45 ) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO `actor` ( `id`, `name`, `update_time` )
VALUES
( 1, 'a', NOW() ),
( 2, 'b', NOW() ),
( 3, 'c', NOW() );
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR ( 10 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_name` ( `name` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO `film` ( `id`, `name` )
VALUES
( 3, 'film0' ),
( 1, 'film1' ),
( 2, 'film2' );
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` INT ( 11 ) NOT NULL,
`film_id` INT ( 11 ) NOT NULL,
`actor_id` INT ( 11 ) NOT NULL,
`remark` VARCHAR ( 255 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_film_actor_id` ( `film_id`, `actor_id` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERTINTO `film_actor` ( `id`, `film_id`, `actor_id` )
VALUES
( 1, 1, 1 ),
( 2, 1, 2 ),
( 3, 2, 1 );2Explain字段說明
執行 explain select * from actor; 可以看到結果如下
圖片
通過上圖中我們看下每個字段代表的含義
id列
id列的編號是select的序列號，有幾個select就有幾個id，并且id的順序是按select出現的順序增長的。id列越大執行優先級越高，id相同則從上往下執行，id為NULL最后執行。
select_type
select_type表示對應行是簡單查詢還是復雜的查詢。這個字段有五個值，分別代表不同的含義
通過一個查詢例證來說明:
首先執行下面的語句用來關閉mysql5.7新特性對衍生表的合并優化：
set session optimizer_switch='derived_merge=off'然后執行下面語句看執行過程的select_type列：
explain select (select 1 from actor where id=1) from (select * from film where id=1) t
圖片
- simple表示簡單查詢，查詢不包含子查詢和union
- primary：復雜查詢中最外層的select
- subquery：包含在select中的子查詢（不在from子句中）
- derived：包含在from子句中的子查詢。MySQL會將結果存放在一個臨時表中，也稱為派生表
- union：在union中的第二個和隨后的select
關于union，我們通過下面語句來理解
explain select 1 union all select 1
table列
這一列表示explain的一行正在訪問哪個表。
當from子句中有子查詢時，table列是< derivenN > 格式，表示當前查詢依賴id=N的查詢，于是先執行id=N的查詢。
當有union時，UNION RESULT的table列的值為<union1,2>，1和2表示參與union的select行id。
type
這一列表示關聯類型或訪問類型，即MySQL決定如何查找表中的行，查找數據行記錄的大概范圍依次從最優到最差分別為：system>const>eq_ref>ref>range>index>ALL一般來說，得保證查詢達到range級別，最好達到ref
列為空是因為mysql能夠在優化階段分解查詢語句，在執行階段用不著再訪問表或索引。例如：在索引列中選取最小值，可以單獨查找索引來完成，不需要再進行回表訪問。
const：這個類型最快，當查詢通過優化器優化后可以走主鍵索引或者唯一索引（primarykey或uniquekey）的時候，這種情況只需要掃描1條數據，mysql能夠迅速定位到數據。
system：system是特殊的const類型，即當const類型查詢的表里面恰好只有一條數據的時候，這種概率很小，可以忽略，而且有時候即便是表里面只有一條數據執行計劃中看到的也是const類型，這個不用太糾結。
舉個例子看下:
explain select * from (select * from actor where id = 1) t;
圖片
eq_ref：上面的兩種類型是主鍵索引或者唯一索引（primarykey或uniquekey）查詢，并且最多只有一條記錄匹配，而eq_ref類型說的是同樣是主鍵索引或者唯一索引（primarykey或uniquekey）查詢，但是返回的是多條數據，比如下面例子：聯表查詢的時候
explain select * from film_actor left join film on film_actor.film_id=film.id
圖片
這是一種主鍵索引或者唯一索引來進行聯表的方式。也在const之外最好的聯接類型了，簡單的select查詢不會出現這種type
ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行。
1.簡單select查詢，name是普通索引（非唯一索引）
explain select * from film where name='film1';
圖片
2.關聯表查詢，idx_film_actor_id是film_id和actor_id的聯合索引，這里使用到了film_actor的左邊前綴film_id部分
explain select film_id from film left join film_actor on film.id=film_actor.film_id
圖片
range：索引范圍掃描，通常出現在in(),between,>,<,>=等操作中。使用一個索引來檢索給定范圍的行
explain select * from actor where id>1index：全索引掃描就能拿到結果，一般是掃描某個二級索引，這種掃描不會從索引樹根節點開始快速查找，而是直接對二級索引的葉子節點遍歷和掃描，速度還是比較慢的，這種查詢一般為使用覆蓋索引，二級索引一般比較小，所以這種通常比ALL快一些。
ALL：即全表掃描，掃描你的聚簇索引的所有葉子節點。這是最慢的一種查詢類型，通常情況下這需要增加索引來進行優化了。
possible_keys列
這一列顯示查詢可能使用哪些索引來查找。explain時可能出現possible_keys有列，而key顯示NULL的情況，這種情況是因為表中數據不多，mysql認為索引對此查詢幫助不大，選擇了全表查詢。如果該列是NULL，則沒有相關的索引。
key列
這一列顯示mysql實際采用哪個索引來優化對該表的訪問。如果沒有使用索引，則該列是NULL。如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用forceindex、ignoreindex
key_len列
這一列顯示了mysql在索引里使用的字節數，通過這個值可以算出具體使用了索引中的哪些列。
舉例來說，film_actor的聯合索引idx_film_actor_id由film_id和actor_id兩個int列組成，并且每個int是4字節。通過結果中的key_len=4可推斷出查詢使用了第一個列：film_id列來執行索引查找。
explain select * from film_actor where film_id=2;
圖片
key_len計算規則如下：
- 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數，而不是字節數，如果是utf-8，一個數字或字母占1個字節，一個漢字占3個字節。
char(n)：如果存漢字長度就是3n字節
varchar(n)：如果存漢字則長度是3n+2字節，加的2字節用來存儲字符串長度，因為varchar是變長字符串
- 數值類型
tinyint：1字節
smallint：2字節
int：4字節
bigint：8字節
時間類型
date：3字節
timestamp：4字節
datetime：8字節
如果字段允許為NULL，需要1字節記錄是否為NULL,索引最大長度是768字節，當字符串過長時，mysql會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引。
ref列
這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），字段名（例：film.id）
rows列
這一列是mysql估計要讀取并檢測的行數，不一定是最終查詢所要掃描的行數，更不是結果集里的行數。
這里順便說一下rows數值是怎么得到的
MySQL在真正開始執行語句之前，并不能精確地知道滿足這個條件的記錄有多少條，而只能根據統計信息來估算記錄數。這個統計信息就是索引的“區分度”。顯然，一個索引上不同的值越多，這個索引的區分度就越好。而一個索引上不同的值的個數，我們稱之為“基數”。也就是說，這個基數越大，索引的區分度越好。那么這個基數是怎么來的呢？這個基數是通過采樣統計來的，為什么要采樣統計呢？因為把整張表取出來一行行統計，雖然可以得到精確的結果，但是代價太高了，所以只能選擇采樣，采樣統計的時候，InnoDB默認會選擇N個數據頁，統計這些頁面上的不同值，得到一個平均值，然后乘以這個索引的頁面數，就得到了這個索引的基數。然后再根據這個基數得到預估行數。
但是數據表是會持續更新的，索引統計信息也不會固定不變。所以，當變更的數據行數超過1/M的時候，會自動觸發重新做一次索引統計。在MySQL中，有兩種存儲索引統計的方式，可以通過設置參數innodb_stats_persistent的值來選擇：
設置為on的時候，表示統計信息會持久化存儲。這時，默認的N是20，M是10。
設置為off的時候，表示統計信息只存儲在內存中。這時，默認的N是8，M是16。
由于是采樣統計，所以不管N是20還是8，這個基數都是很容易不準的。
總之，基數小，區分度小，掃描行數就相對多，基數大，區分度大，掃描行數就相對少。
Extra列
這一列展示的是額外信息。常見的重要值如下：
1.Using index：使用覆蓋索引
mysql執行計劃explain結果里的key有使用索引，如果select后面查詢的字段都可以從這個索引的樹中獲取，這種情況一般可以說是用到了覆蓋索引，extra里一般就會有using index。
覆蓋索引一般針對的是輔助索引，整個查詢結果只通過輔助索引就能拿到結果，不需要通過輔助索引樹找到主鍵，再通過主鍵去主鍵索引樹里獲取其它字段值。
explain select film_id from film_actor where film_id=1;
圖片
2.Using where：使用where語句來處理結果，并且查詢的列未被索引覆蓋
explain select * from actor where name='a'
圖片
3.Using index condition：查詢的列不完全被索引覆蓋，where條件中是一個前導列的范圍
就是應用了索引的最左前綴原則
explain select * from film_actor where film_id>14.Using temporary：mysql需要創建一張臨時表來處理查詢。
出現這種情況一般是要進行優化的，首先是想到用索引來優化。
actor.name沒有索引，此時創建了張臨時表來distinct
explain select distinct name from actor;5.Using filesort：將用外部排序而不是索引排序
數據較小時從內存排序，否則需要在磁盤完成排序。這種情況下一般也是要考慮使用索引來優化的。
1.actor.name未創建索引，會瀏覽actor整個表，保存排序關鍵字name和對應的id，然后排序name并檢索
explain select * from actor order by name;
6.Using join buffer(Block Nested Loop)：join語句相關
當join語句中的被驅動表沒有索引時候會走Block Nested Loop算法，這種情況就會把驅動表的數據全部放入join buffer內存中，然后進行匹配，后面我們會詳細介紹join原理
7.Using MRR：這是一種回表優化
mysql在做查詢的時候，我們知道當應用到二級索引的時候會存在回表現象，你想一下，索引是有序的，當我們通過二級索引查到主鍵，再根據主鍵去主鍵索引樹查找數據的時候，用主鍵索引在主鍵樹查找的這個動作是隨機讀，我們知道隨機讀肯定沒有順序讀快，因此MRR算法就是解決這個問題的。這個我們后續會詳細講解。
3總結
執行計劃是我們進行sql優化的依賴。通過里面各個字段的信息我們能得到優化結論，其中有些字段的含義需要我們有一些底層基礎比如Extra列，這一列會告訴我們當前查詢是否走了索引，是否用了臨時表，如何進行排序，有什么算法進行join，這些可能需要我們去了解排序原理，聯表原理等等。這樣才能對sql的調優做到游刃有余。
