# explain分析執行計劃
可以通過以下兩種方式定位執行效率較低的 SQL 語句。
## 1.explain示例
通過以上步驟查詢到效率低的 SQL 語句后，可以通過 EXPLAIN或者 DESC命令獲取 MySQL如何執行 SELECT 語句的信息，包括在 SELECT 語句執行過程中表如何連接和連接的順序。
查詢SQL語句的執行計劃 ：
示例1：
~~~
mysql> explain select * from city\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
~~~
示例2：
~~~
mysql> explain select * from city where city_id =1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
~~~
| 字段 | 含義 |
| ------------- | ------------------------------------------------------------ |
| id | select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序。 |
| select_type | 表示 SELECT 的類型，常見的取值有 SIMPLE（簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION 中的第二個或者后面的查詢語句）、SUBQUERY（子查詢中的第一個 SELECT）等 |
| table | 輸出結果集的表 |
| type | 表示表的連接類型，性能由好到差的連接類型為( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all ) |
| possible_keys | 表示查詢時，可能使用的索引 |
| key | 表示實際使用的索引 |
| key_len | 索引字段的長度 |
| rows | 掃描行的數量 |
| extra | 執行情況的說明和描述 |
## 2. 環境準備
```sql
CREATE TABLE `t_role` (
`id` varchar(32) NOT NULL,
`role_name` varchar(255) DEFAULT NULL,
`role_code` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user` (
`id` varchar(32) NOT NULL,
`username` varchar(45) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_role` (
`id` int(11) NOT NULL auto_increment ,
`user_id` varchar(32) DEFAULT NULL,
`role_id` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_ur_user_id` (`user_id`),
KEY `fk_ur_role_id` (`role_id`),
CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超級管理員');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系統管理員');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','學生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','學生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老師1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','學生','student','學生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老師','teacher','老師');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教學管理員','teachmanager','教學管理員');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理員','admin','管理員');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超級管理員','super','超級管理員');
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
```
## 3.explain 之 id
id 字段是 select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序。id 情況有三種 ：
1） id 相同表示加載表的順序是從上到下。
~~~
mysql> explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: r
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ur
partitions: NULL
type: ref
possible_keys: fk_ur_user_id,fk_ur_role_id
key: fk_ur_role_id
key_len: 99
ref: demo_01.r.id
rows: 1
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: u
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: demo_01.ur.user_id
rows: 1
filtered: 100.00
Extra: NULL
3 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
~~~
2） id 不同id值越大，優先級越高，越先被執行。
~~~
mysql> EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))\G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_role
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: user_role
partitions: NULL
type: ref
possible_keys: fk_ur_user_id
key: fk_ur_user_id
key_len: 99
ref: const
rows: 1
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 3
select_type: SUBQUERY
table: t_user
partitions: NULL
type: const
possible_keys: unique_user_username
key: unique_user_username
key_len: 137
ref: const
rows: 1
filtered: 100.00
Extra: Using index
3 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
~~~
3） id 有相同，也有不同，同時存在。id相同的可以認為是一組，從上往下順序執行；在所有的組中，id的值越大，優先級越高，越先執行。
~~~
mysql> EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: ur partitions: NULL type: ref
possible_keys: fk_ur_user_id,fk_ur_role_id
key: fk_ur_user_id
key_len: 99
ref: const
rows: 1
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: r
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: demo_01.ur.role_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
~~~
## 4.explain 之 table
表示 SELECT 的類型，常見的取值，如下表所示：
| select_type | 含義 |
| ------------ | ------------------------------------------------------------ |
| SIMPLE | 簡單的select查詢，查詢中不包含子查詢或者UNION |
| PRIMARY | 查詢中若包含任何復雜的子查詢，最外層查詢標記為該標識 |
| SUBQUERY | 在SELECT 或 WHERE 列表中包含了子查詢 |
| DERIVED | 在FROM 列表中包含的子查詢，被標記為 DERIVED（衍生） MYSQL會遞歸執行這些子查詢，把結果放在臨時表中 |
| UNION | 若第二個SELECT出現在UNION之后，則標記為UNION ； 若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為 ： DERIVED |
| UNION RESULT | 從UNION表獲取結果的SELECT
## 5.explain 之 table
展示這一行的數據是關于哪一張表的
## 6.explain 之 type
type 顯示的是訪問類型，是較為重要的一個指標，可取值為：
| type | 含義 |
| ------ | ------------------------------------------------------------ |
| NULL | MySQL不訪問任何表，索引，直接返回結果 |
| system | 表只有一行記錄(等于系統表)，這是const類型的特例，一般不會出現 |
| const | 表示通過索引一次就找到了，const 用于比較primary key 或者 unique 索引。因為只匹配一行數據，所以很快。如將主鍵置于where列表中，MySQL 就能將該查詢轉換為一個常亮。const于將 "主鍵" 或 "唯一" 索引的所有部分與常量值進行比較 |
| eq_ref | 類似ref，區別在于使用的是唯一索引，使用主鍵的關聯查詢，關聯查詢出的記錄只有一條。常見于主鍵或唯一索引掃描 |
| ref | 非唯一性索引掃描，返回匹配某個單獨值的所有行。本質上也是一種索引訪問，返回所有匹配某個單獨值的所有行（多個） |
| range | 只檢索給定返回的行，使用一個索引來選擇行。 where 之后出現 between ， < , > , in 等操作。 |
| index | index 與 ALL的區別為 index 類型只是遍歷了索引樹， 通常比ALL 快， ALL 是遍歷數據文件。 |
| all | 將遍歷全表以找到匹配的行 |
結果值從最好到最壞以此是：
~~~
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system > const > eq_ref > ref > range > index > ALL
~~~
一般來說， 我們需要保證查詢至少達到 range 級別， 最好達到ref 。
## 7. explain 之 key
```
possible_keys : 顯示可能應用在這張表的索引， 一個或多個。
key ： 實際使用的索引， 如果為NULL， 則沒有使用索引。
key_len : 表示索引中使用的字節數， 該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下， 長度越短越好 。
```
## 8.explain 之 rows
掃描行的數量。
## 9.explain 之 extra
其他的額外的執行計劃信息，在該列展示 。
| extra | 含義 |
| ---------------- | ------------------------------------------------------------ |
| using filesort | 說明mysql會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取， 稱為 “文件排序”, 效率低。 |
| using temporary | 使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見于 order by 和 group by； 效率低 |
| using index | 表示相應的select操作使用了覆蓋索引， 避免訪問表的數據行， 效率不錯。 |
- 鳴謝
- 安裝和配置
- Mac 安裝MySql 8
- 授權用戶遠程登錄
- MySql 命令加入系統命令
- 啟動Mysql
- 索引
- 索引介紹與優勢
- 索引結構
- 索引操作語法
- 索引設計原則
- 存儲引擎
- MySql的體系解構
- 存儲引擎
- 各種存儲引擎特性
- 存儲引擎的選擇
- 優化SQL
- 查看SQL執行效率
- 定為低效率執行SQL
- explain分析執行計劃
- show profile分析SQL
- tract 分析優化器執行計劃
- 索引的使用
- 驗證索引可以提高查詢效率
- 索引使用
- 查看索引的使用情況
- SQL優化
- 大批量插入數據
- 優化insert語句
- 優化order by語句
- 優化group by的優化
- 優化嵌套索引
- 優化OR條件
- 優化分頁查詢
- 使用SQL提示