mysql中一條查詢語句的執行全過程是怎樣的？

來預案：碼農本農

mysql作為最常用的關係型資料庫，無論是在應用還是在面試中都是必須掌握的技能。

要印在腦子裡面的東西

DDL:資料定義，它用來定義資料庫物件，包括庫，表，列，透過ddl我們可以建立，刪除，修改資料庫和表結構；

DML:資料操作語言，增加刪除修改資料表中的記錄；

DCL:資料控制語言，定義訪問許可權和安全級別；

DQL:資料查詢語言，用它來查詢想要的記錄。

SQL執行順序:

1. from;
2. join
3. on
4. where;
5. group by;
6. avg,sum.... 使用聚集函式進行計算;
7. having;
8. select;
9. distinct;
10. order by;
11. 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>1

index：全索引掃描就能拿到結果，一般是掃描某個二級索引，這種掃描不會從索引樹根節點開始快速查詢，而是直接對二級索引的葉子節點遍歷和掃描，速度還是比較慢的，這種查詢一般為使用覆蓋索引，二級索引一般比較小，所以這種通常比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個位元組。

1. char(n)：如果存漢字長度就是3n位元組
2. varchar(n)：如果存漢字則長度是3n+2位元組，加的2位元組用來儲存字串長度，因為varchar是變長字串

1. tinyint：1位元組
2. smallint：2位元組
3. int：4位元組
4. bigint：8位元組

1. date：3位元組
2. timestamp：4位元組
3. datetime：8位元組

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>1

4.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的調優做到遊刃有餘。