一、explain工具介紹

• 使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL語句，分析你的查詢語句或是結(jié)構(gòu)的性能瓶頸在 select 語句之前增加 explain 關(guān)鍵字，MySQL 會在查詢上設(shè)置一個標(biāo)記，執(zhí)行查詢會返回執(zhí)行計劃的信息，而不是執(zhí)行這條SQL。

• 通過explain執(zhí)行SQL語句還能查看優(yōu)化器優(yōu)化之后的SQL，緊隨其后通過 SHOW WARNINGS; 命令可以得到優(yōu)化后的查詢語句，從而看出優(yōu)化器優(yōu)化了什么

  # 例如
  EXPLAIN
  SELECT * FROM role;
  SHOW WARNINGS;
  

  

二、添加示例表和數(shù)據(jù)用于后續(xù)演示

這里會使用角色加菜單關(guān)系三張表來做演示。

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu`  (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '菜單ID',`menu_name` varchar(50) DEFAULT NULL COMMENT '菜單名稱',`remark` varchar(255) DEFAULT NULL COMMENT '備注',`del_flag` tinyint(4) NULL DEFAULT 0 COMMENT '刪除標(biāo)識 0：已刪除 1：未刪除 默認(rèn)0',`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '創(chuàng)建時間',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB COMMENT = '菜單表';INSERT INTO `menu` VALUES (1, '系統(tǒng)管理', '管理系統(tǒng)用戶角色菜單相關(guān)', 0, '2023-09-28 10:45:41');
INSERT INTO `menu` VALUES (2, '商品管理', '管理系統(tǒng)商品', 0, '2023-09-28 10:46:04');DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色I(xiàn)D',`role_name` varchar(50) DEFAULT NULL COMMENT '角色名稱',`remark` varchar(255) DEFAULT NULL COMMENT '備注',`del_flag` tinyint(4) NULL DEFAULT 0 COMMENT '刪除標(biāo)識 0：已刪除 1：未刪除 默認(rèn)0',`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '創(chuàng)建時間',PRIMARY KEY (`id`) USING BTREE,KEY `idx_roleName` (`role_name`)
) ENGINE = InnoDB COMMENT = '角色表';INSERT INTO `role` VALUES (1, '超級管理員', '最大角色', 0, '2023-09-28 10:43:31');
INSERT INTO `role` VALUES (2, '普通人員', '小卡拉米', 0, '2023-09-28 10:45:14');DROP TABLE IF EXISTS `role_menu`;
CREATE TABLE `role_menu`  (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色菜單關(guān)聯(lián)ID',`role_id` bigint(20) NOT NULL COMMENT '角色I(xiàn)D',`menu_id` bigint(20) NOT NULL COMMENT '菜單ID',PRIMARY KEY (`id`) USING BTREE,KEY `idx_roleId_menuId` (`role_id`,`menu_id`)
) ENGINE = InnoDB COMMENT = '角色菜單關(guān)聯(lián)表';INSERT INTO `role_menu` VALUES (1, 1, 1);
INSERT INTO `role_menu` VALUES (2, 1, 2);
INSERT INTO `role_menu` VALUES (3, 2, 2);

三、explain中的列

explain中有12個列分別代表不同指標(biāo)，這里會舉例介紹。

3.1、id列

id列的編號是 select 的序列號，有幾個 select 就有幾個id，并且id的順序是按 select 出現(xiàn)的順序增長的，id列越大執(zhí)行優(yōu)先級越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行。

3.2、select_type列

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

• 1、simple：簡單查詢，查詢不包含子查詢和union

EXPLAIN SELECT * FROM role  WHERE id = 1;

• 2、primary：復(fù)雜查詢中最外層的 select
• 3、subquery：包含在 select 中的子查詢（不在 from 子句中）

EXPLAIN SELECT t1.id,(SELECT menu_name FROM menu WHERE id=t1.menu_id ) AS menuName FROM role_menu t1

• 4、union：在 union 中的第二個和隨后的 select

EXPLAIN SELECT * FROM role WHERE id=1 UNION SELECT * FROM role WHERE id=2;

3.3、table列

這一列表示 explain 的一行正在訪問哪個表，如果表設(shè)置了別名會顯示表的別名。

3.4、partitions列

指分區(qū)信息。數(shù)據(jù)庫優(yōu)化有分庫、分表、分區(qū)；這里的分區(qū)值表的分區(qū)信息。

3.5、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í)行時訪問表。

EXPLAIN SELECT MIN(id) FROM role;

system

system是const的特例，表里只有一條元組匹配時為system，可以看SHOW WARNINGS;的結(jié)果。

EXPLAIN SELECT * FROM (SELECT '666' AS num FROM dual) t1;
SHOW WARNINGS;

const

mysql能對查詢的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個常量（可以看SHOW WARNINGS;的結(jié)果）。用于primary key 或 unique key 的所有列與常數(shù)比較時，所以表最多有一個匹配行，讀取1次，速度比較快。

EXPLAIN SELECT * FROM role WHERE id=1;
SHOW WARNINGS;

eq_ref

primary key 或 unique key 索引的所有部分被連接使用 ，最多只會返回一條符合條件的記錄。這可能是在const 之外最好的聯(lián)接類型了，簡單的 select 查詢不會出現(xiàn)這種 type。

EXPLAIN
SELECT * FROM role t1 INNER JOIN role_menu t2 ON t1.id=t2.id where t1.id;

ref

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

EXPLAIN SELECT * FROM role where role_name = '超級管理員';

range

范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。使用一個索引來檢索給定范圍的行。

EXPLAIN SELECT * FROM role where id < 3;

index

掃描全索引就能拿到結(jié)果，一般是掃描某個二級索引，這種掃描不會從索引樹根節(jié)點開始快速查找，而是直接對二級索引的葉子節(jié)點遍歷和掃描，速度還是比較慢的，這種查詢一般為使用覆蓋索引，二級索引一般比較小，所以這種通常比ALL快一些。

EXPLAIN SELECT role_name FROM role;

ALL

即全表掃描，掃描你的聚簇索引的所有葉子節(jié)點。通常情況下這需要增加索引來進(jìn)行優(yōu)化了。

EXPLAIN SELECT * FROM role WHERE del_flag = 1;

3.6、possible_keys列

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

• explain 時可能出現(xiàn) possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因為表中數(shù)據(jù)不多，mysql認(rèn)為索引
  對此查詢幫助不大，選擇了全表查詢。
• 如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查 where 子句看是否可以創(chuàng)造一個適當(dāng)?shù)乃饕齺硖?br /> 高查詢性能，然后用 explain 查看效果。

3.7、key列

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

• 如果沒有使用索引，則該列是 NULL。如果想強制mysql使用或忽視possible_keys列中的索引，在查詢中使用 force
  index、ignore index。

  # 強制使用idx_roleName索引
  # 需要注意的就算我們指定了需要使用的索引，但是MySQL優(yōu)化器覺得全表掃描更加高效或者我們指定的索引查詢條件無法使用到，還是可能會忽略我們指定的索引。
  EXPLAIN SELECT * FROM role FORCE INDEX(idx_roleName) WHERE role_name = '超級管理員';
  # 忽略使用idx_roleName索引
  EXPLAIN SELECT * FROM role IGNORE INDEX(idx_roleName) WHERE role_name = '超級管理員';
  

3.8、key_len列

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

舉例來說，role_menu的聯(lián)合索引 idx_roleId_menuId由 role_id 和 menu_id 兩個bigint列組成，并且每個bigint是8字節(jié)。通
過結(jié)果中的key_len=8可推斷出查詢使用了第一個列：role_id 列來執(zhí)行索引查找。

EXPLAIN SELECT * FROM role_menu WHERE role_id = 1;

• key_len計算規(guī)則如下：
  • 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個數(shù)字或字母占1個字節(jié)，一個漢字占3個字節(jié)
    • char(n)：如果存漢字長度就是 3n 字節(jié)
    • varchar(n)：如果存漢字則長度是 3n + 2 字節(jié)，加的2字節(jié)用來存儲字符串長度，因為varchar是變長字符串
  • 數(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會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引。

3.9、ref列

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

3.10、rows列

這一列是MySQL估計要讀取并檢測的行數(shù)，注意這個不是結(jié)果集里的行數(shù)，這個只是預(yù)估值和實際值可能有偏差。

3.11、filtered列

filtered指標(biāo)表示掃描行數(shù)和條件過濾結(jié)果集的百分比，比如我們這邊查詢 role_name = ‘超級管理員’，全表掃描總掃描數(shù)據(jù)集2條過濾出1條得到比值50%。

EXPLAIN SELECT * FROM role  WHERE  role_name = '超級管理員';

3.12、Extra列

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

Using index

使用覆蓋索引，mysql執(zhí)行計劃explain結(jié)果里的key有使用索引，如果select后面查詢的字段都可以從這個索引的樹中獲取，這種情況一般可以說是用到了覆蓋索引，extra里一般都有using index；覆蓋索引一般針對的是輔助索引，整個查詢結(jié)果只通過輔助索引就能拿到結(jié)果，不需要通過輔助索引樹找到主鍵，再通過主鍵去主鍵索引樹里獲取其它字段值

EXPLAIN SELECT role_name FROM role WHERE role_name = '超級管理員';

Using where

使用 where 語句來處理結(jié)果，并且查詢的列未全部被索引覆蓋

EXPLAIN SELECT * FROM role WHERE role_name = "超級管理員" AND remark = "最大角色";

Using index condition

嘗試只使用索引來獲取數(shù)據(jù)，即能用索引就用；

EXPLAIN SELECT * FROM role WHERE role_name > '超級管理員';

Using temporary

用臨時表存儲中間結(jié)果，常用于DISTINCT、GROUP BY、ORDER BY 等操作。

EXPLAIN SELECT DISTINCT remark FROM role;

Using filesort

將用外部排序而不是索引排序，數(shù)據(jù)較小時從內(nèi)存排序，否則需要在磁盤完成排序。這種情況下一般也是要考慮使用索引來優(yōu)化的。

EXPLAIN SELECT * FROM role ORDER BY create_time;

Select tables optimized away

使用某些聚合函數(shù)（比如 max、min）來訪問存在索引的某個字段。

EXPLAIN SELECT MIN(id) FROM role;