[MySQL 優化] Explain 之 type 詳解

AR414發表於2020-05-06

select version()：5.7.21

MySQL 提供了一個 EXPLAIN 命令, 它可以對 SQL 語句進行分析, 並輸出 SQL 執行的詳細資訊, 以供開發人員針對性優化.

例如分析一條 SELECT 語句
EXPLAIN SELECT * FROM `user` WHERE id = 1 

explain-type

Tips：常見的掃描方式

system：系統表，少量資料，往往不需要進行磁碟IO
const：常量連線
eq_ref：主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描
ref：非主鍵非唯一索引等值掃描
range：範圍掃描
index：索引樹掃描
ALL：全表掃描(full table scan)

`type`掃描方式由快到慢

system > const > eq_ref > ref > range > index > ALL

1.system

mysql> explain select * from mysql.proxies_priv;
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | proxies_priv | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+

上例中，從系統庫mysql的系統標proxies_priv裡查詢資料，這裡的資料在Mysql服務啟動時候已經載入在記憶體中，不需要進行磁碟IO。

官方文件中的解釋：該表只有一行（=系統表）。這是const聯接型別的特例

2.const

模擬資料

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

Explain分析結果

mysql> explain select * from user where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

上例中id是主鍵(primary key)，連線部分是常量1，通過索引一次就能找到，速度非常快

場景：

命中主鍵(primary key)或者唯一索引(unique)
被連線的部分是一個常量值(const)

3.eq_ref

模擬資料

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

create table user_balance (
  uid int primary key,
  balance int
)engine=innodb;

insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);

Explain分析結果

mysql> explain select * from user left join user_balance on user.id = user_balance.uid where user.id=user_balance.uid;
+----+-------------+--------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | user         | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL           |    4 |   100.00 | NULL  |
|  1 | SIMPLE      | user_balance | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | school.user.id |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+

上例中對於前表user表中的每一行（row），對應後user_balance表只有一行被掃描，這類掃描的速度也非常的快

場景：

聯表(join)查詢
命中主鍵(primary key)或者非空唯一索引(unique not null)
等值連線

4.ref

模擬資料

同eq_ref模擬資料區別：user_balance表中的主鍵索引改為普通索引

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

create table user_balance (
  uid int,
  balance int,
  index(uid)
)engine=innodb;

insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);

Explain分析結果

聯表查詢

mysql> explain select * from user left join user_balance on user.id = user_balance.uid where user.id=user_balance.uid;
+----+-------------+--------------+------------+------+---------------+------+---------+----------------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref            | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | user         | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL           |    4 |   100.00 | NULL  |
|  1 | SIMPLE      | user_balance | NULL       | ref  | uid           | uid  | 5       | school.user.id |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------+------+----------+-------+

由於後表使用了普通非唯一索引，對於前表user表的每一行(row)，後表user_balance表可能有多於一行的資料被掃描

單表查詢

mysql> explain select * from user_balance where uid = 1;
+----+-------------+--------------+------------+------+---------------+-----+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+-----+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_balance | NULL       | ref  | uid           | uid | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+-----+---------+-------+------+----------+-------+

當id改為普通非唯一索引後，常量的連線查詢，也由const降級為了ref，因為非唯一索引所以有多於一行的資料被可能被掃描

ref每一次匹配可能有多行資料返回，雖然它比eq_ref要慢，但它仍然是一個很快的join型別

場景：

聯表查詢
普通非唯一索引

5.range

模擬資料

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

Explain分析結果

between

mysql> explain select * from user where id between 1 and 4;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

in

mysql> explain select * from user where id in(1,2,3);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

>,>=,<,<=

mysql> explain select * from user where id>3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> explain select * from user where id<3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

range比較好理解，它是索引上的範圍查詢，它會在索引上掃碼特定範圍內的值

6.index

話外音：當前測試表為InnoDb，MyISAM 內建了一個計數器，count()時它直接從計數器中讀

mysql> explain select count(1) from user;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

index型別，需要掃描索引上的全部資料，它僅比全表掃描快一點

7.ALL

模擬資料

create table user (
  id int,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

Explain分析結果

mysql> explain select * from user where id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如果id上不建索引,則全表掃描

總結

type型別從快到慢：system>const>eq_ref>ref>range>index>ALL
作為一名合格的後端開發者應該熟悉掌握Explain
結合業務建立正確索引，而不是每個欄位建立索引（濫用）

本作品採用《CC 協議》，轉載必須註明作者和本文連結

mysql優化之explain 指令
2018-10-11
MySql優化AI
MySQL Explain詳解
2017-04-18
MySqlAI
MySQL EXPLAIN 詳解
2017-07-23
MySqlAI
MySQL優化學習筆記之explain
2018-10-25
MySql優化筆記AI
mysql優化之explain備忘筆記
2013-07-09
MySql優化AI筆記
MySQL之SQL優化詳解（一）
2019-06-20
MySql優化
MySQL之SQL優化詳解（二）
2019-06-30
MySql優化
MySQL之SQL優化詳解（三）
2019-07-01
MySql優化
MySQL explain命令詳解
2020-02-16
MySqlAI
詳解 MySQL 中的 explain
2016-06-29
MySqlAI
Mysql Explain 詳解 [原創]
2008-08-06
MySqlAI
MySQL查詢優化利刃-EXPLAIN
2020-06-08
MySql優化AI
MySQL EXPLAIN 命令詳解學習
2013-12-06
MySqlAI
MySQL: 使用explain 優化查詢效能
2021-10-29
MySqlAI優化
mysql查詢優化檢查 explain
2018-05-04
MySql優化AI
Explain Extended檢視MySQL的優化
2014-04-29
AIMySql優化
MySQL優化從執行計劃開始(explain超詳細)
2021-03-17
MySql優化AI
SQL之EXPLAIN語法詳解
2017-04-26
SQLAI
MySQL Explain執行計劃 - 詳解
2021-09-25
MySqlAI
mysql explain 執行計劃詳解
2019-01-06
MySqlAI
MySQL執行計劃EXPLAIN詳解
2017-12-15
MySqlAI
MySQL的EXPLAIN命令結果詳解
2016-05-23
MySqlAI
Mysql調優之profile詳解
2018-11-22
MySql
mysql調優之——執行計劃explain
2019-03-24
MySqlAI
MySQL explain 中 key_len的詳解
2020-04-07
MySqlAI
MySQL explain 和 profiling 詳解
2023-04-05
MySqlAI
MySQL進階【八】—— MySQL優化之my.conf配置詳解
2020-09-25
MySql優化
MySQL explain執行計劃詳細解釋
2021-09-09
MySqlAI
使用explain優化sql
2017-07-10
AI優化SQL
MySQL學習之explain
2024-04-27
MySqlAI
mongodb之使用explain和hint效能分析和優化
2016-08-04
MongoDBAI優化
淺析MySQL語句優化中的explain引數
2019-03-03
MySql優化AI
MySQL·效能優化·SQL錯誤用法詳解
2017-04-08
MySql優化
MySQL-5.5.32 配置檔案優化詳解
2018-05-17
MySql優化
Android App 優化之 ANR 詳解
2016-11-16
AndroidAPP優化
mysql explain 命令講解
2016-05-26
MySqlAI
MySQL查詢最佳化之explain的深入解析
2017-05-28
MySqlAI
MySQL 效能優化之索引優化
2015-04-01
MySql優化索引

[MySQL 優化] Explain 之 type 詳解

type掃描方式由快到慢

1.system

2.const

模擬資料

Explain分析結果

3.eq_ref

模擬資料

Explain分析結果

4.ref

模擬資料

Explain分析結果

5.range

模擬資料

Explain分析結果

6.index

7.ALL

模擬資料

Explain分析結果

總結

相關文章

`type`掃描方式由快到慢