Explain工具介紹

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，分析你的查詢語句或是結(jié)構(gòu)的性能瓶頸

在 select 語句之前增加 explain 關(guān)鍵字，MySQL 會在查詢上設(shè)置一個標(biāo)記，執(zhí)行查詢會返回執(zhí)行計劃的信息，而不是執(zhí)行這條SQL

注意：如果 from 中包含子查詢，仍會執(zhí)行該子查詢，將結(jié)果放入臨時表中。

• actor建表語句

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of actor
-- ----------------------------
INSERT INTO `actor` VALUES ('1', 'a', '2020-01-11 19:57:26');
INSERT INTO `actor` VALUES ('2', 'b', '2020-01-11 19:57:38');
INSERT INTO `actor` VALUES ('3', 'c', '2020-01-11 19:57:57');

• film建表語句

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`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of film
-- ----------------------------
INSERT INTO `film` VALUES ('1', 'film0');
INSERT INTO `film` VALUES ('2', 'film1');
INSERT INTO `film` VALUES ('3', 'film2');

• film_actor建表語句

DROP TABLE IF EXISTS `file_actor`;
CREATE TABLE `file_actor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of file_actor
-- ----------------------------
INSERT INTO `file_actor` VALUES ('1', '1', '1', null);
INSERT INTO `file_actor` VALUES ('2', '1', '2', null);
INSERT INTO `file_actor` VALUES ('3', '2', '1', null);

Explain展示的字段

explain列

展示explain中的每個列的信息。

1、id列

id列的編號是select的序號，有幾個select就有幾個id，并且id的順序是按select出現(xiàn)的順序增長的。

id值越大優(yōu)先級越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行。

2、select_type

select_type表示對應(yīng)行是簡單還是復(fù)雜的查詢

simple：簡單查詢。查詢不包含子查詢和union

如上圖

primary：復(fù)雜查詢中最外層的select

3)subquery：包含在 select 中的子查詢(不在 from 子句中)

4)derived：包含在 from 子句中的子查詢。MySQL會將結(jié)果存放在一個臨時表中，也稱為派生表(derived的英文含義)

用這個例子來了解 primary、subquery 和 derived 類型

mysql> set session optimizer_switch='derived_merge=off'; #關(guān)閉mysql5.7新特性對衍生表的合并優(yōu)化
mysql> explain select (select 1 from actor where id = 1) from (select * from filmwhere id = 1) der;

mysql> set session optimizer_switch='derived_merge=on'; #還原默認(rèn)配置

5)union：在 union 中的第二個和隨后的 select

mysql> explain select 1 union all select 1;

3、table列

這一列表示 explain 的一行正在訪問哪個表。

當(dāng) from 子句中有子查詢時，table列是 格式，表示當(dāng)前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查詢。

當(dāng)有 union 時，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的select 行id。

4、type列

這一列表示關(guān)聯(lián)類型或訪問類型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍。

依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL

一般來說，得保證查詢達(dá)到range級別，最好達(dá)到ref

NULL：mysql能夠在優(yōu)化階段分解查詢語句，在執(zhí)行階段用不著再訪問表或索引。

例如：在索引列中選取最小值，可以單獨查找索引來完成，不需要在執(zhí)行時訪問表

mysql> explain select min(id) from film;

const, system：mysql能對查詢的某部分進行優(yōu)化并將其轉(zhuǎn)化成一個常量(可以看showwarnings 的結(jié)果)。

用于 primary key 或 unique key 的所有列與常數(shù)比較時，所以表最多有一個匹配行，讀取1次，速度比較快。system是const的特例，表里只有一條元組匹配時為system 

mysql> explain extended select * from (select * from film where id = 1) tmp;

mysql> show warnings;

eq_ref：primary key 或 unique key 索引的所有部分被連接使用 ，最多只會返回一條符合條件的記錄。

這可能是在 const 之外最好的聯(lián)接類型了，簡單的 select 查詢不會出現(xiàn)這種type。

mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行。

簡單 select 查詢，name是普通索引(非唯一索引)

mysql> explain select * from film where name = 'film1';

關(guān)聯(lián)表查詢，idx_film_actor_id是film_id和actor_id的聯(lián)合索引，這里使用到了film_actor的左邊前綴film_id部分。

mysql> explain select film_id from film left join film_actor on film.id =film_actor.film_id;

range：范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。

使用一個索引來檢索給定范圍的行。

mysql> explain select * from actor where id > 1;

index：掃描全表索引，這通常比ALL快一些。

mysql> explain select * from film;

ALL：即全表掃描，意味著mysql需要從頭到尾去查找所需要的行。

通常情況下這需要增加索引來進行優(yōu)化了

mysql> explain select * from actor;

5、possible_keys列

這一列顯示查詢可能使用哪些索引來查找。

explain 時可能出現(xiàn) possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因為表中數(shù)據(jù)不多，mysql認(rèn)為索引對此查詢幫助不大，選擇了全表查詢。

如果該列是NULL，則沒有相關(guān)的索引。

在這種情況下，可以通過檢查 where 子句看是否可以創(chuàng)造一個適當(dāng)?shù)乃饕齺硖岣卟樵冃阅?，然后?explain 查看效果。

6、key列

這一列顯示mysql實際采用哪個索引來優(yōu)化對該表的訪問。

如果沒有使用索引，則該列是 NULL。

如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force index、ignore index。

7、key_len列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過這個值可以算出具體使用了索引中的哪些列。

舉例來說，film_actor的聯(lián)合索引 idx_film_actor_id 由 film_id 和 actor_id 兩個int列組成，并且每個int是4字節(jié)。

通過結(jié)果中的key_len=4可推斷出查詢使用了第一個列：film_id列來執(zhí)行索引查找。

mysql> explain select * from film_actor where film_id = 2;

key_len計算規(guī)則如下：

字符串

• char(n)：n字節(jié)長度
• varchar(n)：2字節(jié)存儲字符串長度，如果是utf-8，則長度3n+2

數(shù)值類型

• tinyint：1字節(jié)
• smallint：2字節(jié)
• int：4字節(jié)
• bigint：8字節(jié)

時間類型

date：3字節(jié)

• timestamp：4字節(jié)
• datetime：8字節(jié)

如果字段允許為 NULL，需要1字節(jié)記錄是否為 NULL

索引最大長度是768字節(jié)，當(dāng)字符串過長時，mysql會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引。

8、ref列

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有：const(常量)，字段名(例：film.id)

9、rows列

這一列是mysql估計要讀取并檢測的行數(shù)，注意這個不是結(jié)果集里的行數(shù)。

10、Extra列

這一列展示的是額外信息。常見的重要值如下：

1)Using index：使用覆蓋索引

mysql> explain select film_id from film_actor where film_id = 1;

2)Using where：使用 where 語句來處理結(jié)果，查詢的列未被索引覆蓋

mysql> explain select * from actor where name = 'a';

3)Using index condition：查詢的列不完全被索引覆蓋，where條件中是一個前導(dǎo)列的范圍；

mysql> explain select * from film_actor where film_id > 1;

4)Using temporary：mysql需要創(chuàng)建一張臨時表來處理查詢。

出現(xiàn)這種情況一般是要進行優(yōu)化的，首先是想到用索引來優(yōu)化。

1、actor.name沒有索引，此時創(chuàng)建了張臨時表來distinct

mysql> explain select distinct name from actor;

2、film.name建立了idx_name索引，此時查詢時extra是using index,沒有用臨時表

mysql> explain select distinct name from film;

5)Using filesort：將用外部排序而不是索引排序，數(shù)據(jù)較小時從內(nèi)存排序，否則需要在磁盤完成排序。

這種情況下一般也是要考慮使用索引來優(yōu)化的。

1、actor.name未創(chuàng)建索引，會瀏覽actor整個表，保存排序關(guān)鍵字name和對應(yīng)的id，然后排序name并檢索行記錄

mysql> explain select * from actor order by name;

2、film.name建立了idx_name索引,此時查詢時extra是using index

mysql> explain select * from film order by name;

6)Select tables optimized away：使用某些聚合函數(shù)(比如 max、min)來訪問存在索引的某個字段是

mysql> explain select min(id) from film;

Using filesort文件排序原理詳解

filesort文件排序方式

單路排序：是一次性取出滿足條件行的所有字段，然后在sort buffer中進行排序；用trace工具可以看到sort_mode信息里顯示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >

雙路排序(又叫回表排序模式)：是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行數(shù)據(jù)的行 ID，然后在 sort buffer 中進行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里顯示< sort_key, rowid >

MySQL 通過比較系統(tǒng)變量 max_length_for_sort_data(默認(rèn)1024字節(jié)) 的大小和需要查詢的字段總大小來判斷使用哪種排序模式。

• 如果 max_length_for_sort_data 比查詢字段的總長度大，那么使用 單路排序模式；
• 如果 max_length_for_sort_data 比查詢字段的總長度小，那么使用 雙路排序模式。

我們先看單路排序的詳細(xì)過程：

• 從索引name找到第一個滿足 name = ‘xx’ 條件的主鍵 id
• 根據(jù)主鍵 id 取出整行，取出所有字段的值，存入 sort_buffer 中
• 從索引name找到下一個滿足 name = ‘xx’ 條件的主鍵 id
• 重復(fù)步驟 2、3 直到不滿足 name = ‘xx’
• 對 sort_buffer 中的數(shù)據(jù)按照字段 position 進行排序
• 返回結(jié)果給客戶端

我們再看下雙路排序的詳細(xì)過程：

• 從索引 name 找到第一個滿足 name = ‘xx’ 的主鍵id
• 根據(jù)主鍵 id 取出整行，把排序字段 position 和主鍵 id 這兩個字段放到 sort buffer 中
• 從索引 name 取下一個滿足 name = ‘xx’ 記錄的主鍵 id
• 重復(fù) 3、4 直到不滿足 name = ‘xx’
• 對 sort_buffer 中的字段 position 和主鍵 id 按照字段 position 進行排序
• 遍歷排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回給客戶端

其實對比兩個排序模式，單路排序會把所有需要查詢的字段都放到 sort buffer 中，而雙路排序只會把主鍵和需要排序的字段放到 sort buffer 中進行排序，然后再通過主鍵回到原表查詢需要的字段。

如果 MySQL 排序內(nèi)存配置的比較小并且沒有條件繼續(xù)增加了，可以適當(dāng)把 max_length_for_sort_data 配置小點，讓優(yōu)化器選擇使用雙路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根據(jù)主鍵回到原表取數(shù)據(jù)。

如果 MySQL 排序內(nèi)存有條件可以配置比較大，可以適當(dāng)增大 max_length_for_sort_data 的值，讓優(yōu)化器優(yōu)先選擇全字段排序(單路排序)，把需要的字段放到 sort_buffer 中，這樣排序后就會直接從內(nèi)存里返回查詢結(jié)果了。

所以，MySQL通過 max_length_for_sort_data 這個參數(shù)來控制排序，在不同場景使用不同的排序模式，從而提升排序效率。

注意，如果全部使用sort_buffer內(nèi)存排序一般情況下效率會高于磁盤文件排序，但不能因為這個就隨便增大sort_buffer(默認(rèn)1M)，mysql很多參數(shù)設(shè)置都是做過優(yōu)化的，不要輕易調(diào)整。

Join關(guān)聯(lián)查詢優(yōu)化

mysql的表關(guān)聯(lián)常見有兩種算法

• Nested-Loop Join 算法
• Block Nested-Loop Join 算法

嵌套循環(huán)連接 Nested-Loop Join(NLJ) 算法

一次一行循環(huán)地從第一張表(稱為驅(qū)動表)中讀取行，在這行數(shù)據(jù)中取到關(guān)聯(lián)字段，根據(jù)關(guān)聯(lián)字段在另一張表(被驅(qū)動表)里取出滿足條件的行，然后取出兩張表的結(jié)果合集。

mysql> EXPLAIN select*from t1 inner join t2 on t1.a= t2.a;

從執(zhí)行計劃中可以看到這些信息：

驅(qū)動表是 t2，被驅(qū)動表是 t1。

先執(zhí)行的就是驅(qū)動表(執(zhí)行計劃結(jié)果的id如果一樣則按從上到下順序執(zhí)行sql)；優(yōu)化器一般會優(yōu)先選擇小表做驅(qū)動表。

所以使用 inner join 時，排在前面的表并不一定就是驅(qū)動表。使用了 NLJ算法。

一般 join 語句中，如果執(zhí)行計劃 Extra 中未出現(xiàn) Using join buffer 則表示使用的 join 算法是 NLJ。

上面sql的大致流程如下：

• 從表 t2 中讀取一行數(shù)據(jù)；
• 從第 1 步的數(shù)據(jù)中，取出關(guān)聯(lián)字段 a，到表 t1 中查找；
• 取出表 t1 中滿足條件的行，跟 t2 中獲取到的結(jié)果合并，作為結(jié)果返回給客戶端；
• 重復(fù)上面 3 步。

整個過程會讀取 t2 表的所有數(shù)據(jù)(掃描100行)，然后遍歷這每行數(shù)據(jù)中字段 a 的值，根據(jù) t2 表中 a 的值索引掃描 t1 表中的對應(yīng)行(掃描100次 t1 表的索引，1次掃描可以認(rèn)為最終只掃描 t1 表一行完整數(shù)據(jù)，也就是總共 t1 表也掃描了100行)。

因此整個過程掃描了 200 行。

如果被驅(qū)動表的關(guān)聯(lián)字段沒索引，使用NLJ算法性能會比較低(下面有詳細(xì)解釋)，mysql會選擇Block Nested-Loop Join算法。

基于塊的嵌套循環(huán)連接 Block Nested-Loop Join( BNL )算法

把驅(qū)動表的數(shù)據(jù)讀入到 join_buffer 中，然后掃描被驅(qū)動表，把被驅(qū)動表每一行取出來跟 join_buffer 中的數(shù)據(jù)做對比。

mysql>EXPLAIN select*from t1 inner join t2 on t1.b= t2.b;

Extra 中 的Using join buffer (Block Nested Loop)說明該關(guān)聯(lián)查詢使用的是 BNL 算法。

上面sql的大致流程如下：

• 把 t2 的所有數(shù)據(jù)放入到 join_buffer 中
• 把表 t1 中每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對比
• 返回滿足 join 條件的數(shù)據(jù)

整個過程對表 t1 和 t2 都做了一次全表掃描，因此掃描的總行數(shù)為10000(表 t1 的數(shù)據(jù)總量) + 100(表 t2 的數(shù)據(jù)總量) =10100。

并且 join_buffer 里的數(shù)據(jù)是無序的，因此對表 t1 中的每一行，都要做 100 次判斷，所以內(nèi)存中的判斷次數(shù)是100 * 10000= 100 萬次。

被驅(qū)動表的關(guān)聯(lián)字段沒索引為什么要選擇使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二條sql使用 Nested-Loop Join，那么掃描行數(shù)為 100 * 10000 = 100萬次，這個是磁盤掃描。

很顯然，用BNL磁盤掃描次數(shù)少很多，相比于磁盤掃描，BNL的內(nèi)存計算會快得多。

因此MySQL對于被驅(qū)動表的關(guān)聯(lián)字段沒索引的關(guān)聯(lián)查詢，一般都會使用 BNL 算法。

如果有索引一般選擇 NLJ 算法，有索引的情況下 NLJ 算法比 BNL算法性能更高

對于關(guān)聯(lián)sql的優(yōu)化

關(guān)聯(lián)字段加索引，讓mysql做join操作時盡量選擇NLJ算法

小標(biāo)驅(qū)動大表，寫多表連接sql時如果明確知道哪張表是小表可以用straight_join寫法固定連接驅(qū)動方式，省去mysql優(yōu)化器自己判斷的時間

straight_join解釋：straight_join功能同join類似，但能讓左邊的表來驅(qū)動右邊的表，能改表優(yōu)化器對于聯(lián)表查詢的執(zhí)行順序。

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql選著 t2 表作為驅(qū)動表。

straight_join只適用于inner join，并不適用于left join，right join。

(因為left join，right join已經(jīng)代表指定了表的執(zhí)行順序)盡可能讓優(yōu)化器去判斷，因為大部分情況下mysql優(yōu)化器是比人要聰明的。

使用straight_join一定要慎重，因為部分情況下人為指定的執(zhí)行順序并不一定會比優(yōu)化引擎要靠譜。

in和exsits優(yōu)化

原則：小表驅(qū)動大表，即小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集

• in：當(dāng)B表的數(shù)據(jù)集小于A表的數(shù)據(jù)集時，in優(yōu)于exists

select * from A where id in (select id from B)
 #等價于：
   for(select id from B){
  select * from A where A.id = B.id
  }

• exis`ts：當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時，exists優(yōu)于in　　

將主查詢A的數(shù)據(jù)，放到子查詢B中做條件驗證，根據(jù)驗證結(jié)果(true或false)來決定主查詢的數(shù)據(jù)是否保留

select * from A where exists (select 1 from B where B.id = A.id)
 #等價于:
for(select * from A) {
  select * from B where B.id = A.id
}

A表與B表的ID字段應(yīng)建立索引

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查詢中的SELECT * 也可以用SELECT 1替換,官方說法是實際執(zhí)行時會忽略SELECT清單,因此沒有區(qū)別

2、EXISTS子查詢的實際執(zhí)行過程可能經(jīng)過了優(yōu)化而不是我們理解上的逐條對比

3、EXISTS子查詢往往也可以用JOIN來代替，何種最優(yōu)需要具體問題具體分析

索引最佳實踐

1.全值匹配

2.最左前綴法則(指的是查詢從索引的最左前列開始并且不跳過索引中的列)

3.不在索引列上做任何操作(計算、函數(shù)、(自動or手動)類型轉(zhuǎn)換)，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描

4.存儲引擎不能使用索引中范圍條件右邊的列

5.盡量使用覆蓋索引(只訪問索引的查詢(索引列包含查詢列))，減少select *語句

6.mysql在使用不等于(！=或者<>)的時候無法使用索引會導(dǎo)致全表掃描

7.is null,is not null 也無法使用索引

8.like以通配符開頭('$abc...')mysql索引失效會變成全表掃描操作

9.字符串不加單引號索引失效

10.少用or或in，用它查詢時，mysql不一定使用索引，mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例、表大小等多個因素整體評估是否使用索引，詳見范圍查詢優(yōu)化

11.范圍查詢優(yōu)化(縮小范圍)

12.查詢個數(shù)推薦使用count(*)

like KK%相當(dāng)于=常量，%KK和%KK% 相當(dāng)于范圍

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論