一、explain工具介紹

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

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

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

  

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

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

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)識(shí) 0：已刪除 1：未刪除 默認(rèn)0',`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '創(chuàng)建時(shí)間',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)識(shí) 0：已刪除 1：未刪除 默認(rèn)0',`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '創(chuàng)建時(shí)間',PRIMARY KEY (`id`) USING BTREE,KEY `idx_roleName` (`role_name`)
) ENGINE = InnoDB COMMENT = '角色表';INSERT INTO `role` VALUES (1, '超級(jí)管理員', '最大角色', 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個(gè)列分別代表不同指標(biāo)，這里會(huì)舉例介紹。

3.1、id列

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

3.2、select_type列

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

• 1、simple：簡(jiǎn)單查詢，查詢不包含子查詢和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 中的第二個(gè)和隨后的 select

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

3.3、table列

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

3.4、partitions列

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

3.5、type列

這一列表示關(guān)聯(lián)類型或訪問(wèn)類型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍。
依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL
一般來(lái)說(shuō)，得保證查詢達(dá)到range級(jí)別，最好達(dá)到ref

NULL

mysql能夠在優(yōu)化階段分解查詢語(yǔ)句，在執(zhí)行階段用不著再訪問(wèn)表或索引。例如：在索引列中選取最小值，可以單獨(dú)查找索引來(lái)完成，不需要在執(zhí)行時(shí)訪問(wèn)表。

EXPLAIN SELECT MIN(id) FROM role;

system

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

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

const

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

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

eq_ref

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

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

ref

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

EXPLAIN SELECT * FROM role where role_name = '超級(jí)管理員';

range

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

EXPLAIN SELECT * FROM role where id < 3;

index

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

EXPLAIN SELECT role_name FROM role;

ALL

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

EXPLAIN SELECT * FROM role WHERE del_flag = 1;

3.6、possible_keys列

這一列顯示查詢可能使用哪些索引來(lái)查找。

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

3.7、key列

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

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

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

3.8、key_len列

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

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

EXPLAIN SELECT * FROM role_menu WHERE role_id = 1;

• key_len計(jì)算規(guī)則如下：
  • 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個(gè)數(shù)字或字母占1個(gè)字節(jié)，一個(gè)漢字占3個(gè)字節(jié)
    • char(n)：如果存漢字長(zhǎng)度就是 3n 字節(jié)
    • varchar(n)：如果存漢字則長(zhǎng)度是 3n + 2 字節(jié)，加的2字節(jié)用來(lái)存儲(chǔ)字符串長(zhǎng)度，因?yàn)関archar是變長(zhǎng)字符串
  • 數(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)字符串過(guò)長(zhǎng)時(shí)，mysql會(huì)做一個(gè)類似左前綴索引的處理，將前半部分的字符提取出來(lái)做索引。

3.9、ref列

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

3.10、rows列

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

3.11、filtered列

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

EXPLAIN SELECT * FROM role  WHERE  role_name = '超級(jí)管理員';

3.12、Extra列

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

Using index

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

EXPLAIN SELECT role_name FROM role WHERE role_name = '超級(jí)管理員';

Using where

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

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

Using index condition

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

EXPLAIN SELECT * FROM role WHERE role_name > '超級(jí)管理員';

Using temporary

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

EXPLAIN SELECT DISTINCT remark FROM role;

Using filesort

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

EXPLAIN SELECT * FROM role ORDER BY create_time;

Select tables optimized away

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

EXPLAIN SELECT MIN(id) FROM role;