Explain工具介紹

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

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

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

• 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中的每個(gè)列的信息。

1、id列

id列的編號(hào)是select的序號(hào)，有幾個(gè)select就有幾個(gè)id，并且id的順序是按select出現(xiàn)的順序增長(zhǎng)的。

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

2、select_type

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

simple：簡(jiǎn)單查詢。查詢不包含子查詢和union

如上圖

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

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

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

用這個(gè)例子來了解 primary、subquery 和 derived 類型

mysql> set session optimizer_switch='derived_merge=off'; #關(guān)閉mysql5.7新特性對(duì)衍生表的合并優(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 中的第二個(gè)和隨后的 select

mysql> explain select 1 union all select 1;

3、table列

這一列表示 explain 的一行正在訪問哪個(gè)表。

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

當(dāng)有 union 時(shí)，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級(jí)別，最好達(dá)到ref

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

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

mysql> explain select min(id) from film;

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

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

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

mysql> show warnings;

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

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

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

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

簡(jiǎn)單 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 ,> ,<, >= 等操作中。

使用一個(gè)索引來檢索給定范圍的行。

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

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

mysql> explain select * from film;

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

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

mysql> explain select * from actor;

5、possible_keys列

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

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

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

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

6、key列

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

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

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

7、key_len列

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

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

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

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

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

字符串

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

數(shù)值類型

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

時(shí)間類型

date：3字節(jié)

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

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

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

8、ref列

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

9、rows列

這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù)，注意這個(gè)不是結(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條件中是一個(gè)前導(dǎo)列的范圍；

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

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

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

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

mysql> explain select distinct name from actor;

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

mysql> explain select distinct name from film;

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

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

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

mysql> explain select * from actor order by name;

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

mysql> explain select * from film order by name;

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

mysql> explain select min(id) from film;

Using filesort文件排序原理詳解

filesort文件排序方式

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

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

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

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

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

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

我們?cè)倏聪码p路排序的詳細(xì)過程：

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

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

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

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

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

注意，如果全部使用sort_buffer內(nèi)存排序一般情況下效率會(huì)高于磁盤文件排序，但不能因?yàn)檫@個(gè)就隨便增大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ū)動(dòng)表)中讀取行，在這行數(shù)據(jù)中取到關(guān)聯(lián)字段，根據(jù)關(guān)聯(lián)字段在另一張表(被驅(qū)動(dòng)表)里取出滿足條件的行，然后取出兩張表的結(jié)果合集。

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

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

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

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

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

一般 join 語句中，如果執(zhí)行計(jì)劃 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 步。

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

因此整個(gè)過程掃描了 200 行。

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

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

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

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ù)做對(duì)比
• 返回滿足 join 條件的數(shù)據(jù)

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

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

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

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

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

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

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

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

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

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

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

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

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

(因?yàn)閘eft join，right join已經(jīng)代表指定了表的執(zhí)行順序)盡可能讓優(yōu)化器去判斷，因?yàn)榇蟛糠智闆r下mysql優(yōu)化器是比人要聰明的。

使用straight_join一定要慎重，因?yàn)椴糠智闆r下人為指定的執(zhí)行順序并不一定會(huì)比優(yōu)化引擎要靠譜。

in和exsits優(yōu)化

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

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

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

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

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

select * from A where exists (select 1 from B where B.id = A.id)
 #等價(jià)于:
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替換,官方說法是實(shí)際執(zhí)行時(shí)會(huì)忽略SELECT清單,因此沒有區(qū)別

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

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

索引最佳實(shí)踐

1.全值匹配

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

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

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

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

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

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

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

9.字符串不加單引號(hào)索引失效

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

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

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

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

總結(jié)

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