MySQL explain type详解

2021-01-29 10:41:20 浏览数 (1)

SQL性能调优的依据就是explain，其中type对结果影响最大，本文详细介绍了一下各个不同的type所表示的意义

ps: 网上有很多文档，但是有的并不能复现，所以再仔细研究了一下，并实验了结果

  • 版本
代码语言：javascript复制
mysql> SELECT VERSION();
 ----------- 
| VERSION() |
 ----------- 
| 8.0.22    |
 -----------

type类型从快到慢：system > const > eq_ref > ref > range > index > ALL

system

表中只有一行记录（系统表）。是const类型的一个特殊情况。（目前InnoDB已经没有，在MyISAM可以）

代码语言：javascript复制
CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into user_innodb(name) values('tenmao');

CREATE TABLE `user_myiasm` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
insert into user_myiasm(name) values('tenmao');

mysql> explain select * from user_innodb;
 ---- ------------- ------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- 
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 ---- ------------- ------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- 
|  1 | SIMPLE      | user_innodb | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
 ---- ------------- ------------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- 
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user_myiasm;
 ---- ------------- ------------- ------------ -------- --------------- ------ --------- ------ ------ ---------- ------- 
| id | select_type | table       | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 ---- ------------- ------------- ------------ -------- --------------- ------ --------- ------ ------ ---------- ------- 
|  1 | SIMPLE      | user_myiasm | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
 ---- ------------- ------------- ------------ -------- --------------- ------ --------- ------ ------ ---------- ------- 
1 row in set, 1 warning (0.00 sec)

const

表中最多只有一行匹配的记录。一般用在主键索引或者唯一键索引上的等值查询（如果是多字段索引，则需要全匹配）

代码语言：javascript复制
SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
代码语言：javascript复制
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into user(name, email) values('tenmao', 'tenmao@example.com');
explain select * from user where id=1;
explain select * from user where email='tenmao@example.com';

eq_ref

跨表join时，对于驱动表的每一行记录，被动表最多只会读取一行记录。跟单表查询不一样（system，const最快），在跨表查询中，eq_ref是最好的

代码语言：javascript复制
CREATE TABLE `email_msg` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(32) NOT NULL,
  `title` varchar(128) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into email_msg(email, title, content) values('tenmao@example.com', 'email title', 'email content');
mysql> explain select email_msg.* from email_msg join user using(email);
 ---- ------------- ----------- ------------ -------- --------------- ----------- --------- -------------------------- ------ ---------- ------------- 
| id | select_type | table     | partitions | type   | possible_keys | key       | key_len | ref                      | rows | filtered | Extra       |
 ---- ------------- ----------- ------------ -------- --------------- ----------- --------- -------------------------- ------ ---------- ------------- 
|  1 | SIMPLE      | email_msg | NULL       | ALL    | idx_email     | NULL      | NULL    | NULL                     |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | user      | NULL       | eq_ref | idx_email     | idx_email | 130     | aics_tim.email_msg.email |    1 |   100.00 | Using index |
 ---- ------------- ----------- ------------ -------- --------------- ----------- --------- -------------------------- ------ ---------- -------------

ref

等值查询，但是可能匹配大于1行记录。比如普通的非唯一索引，或者联合主键和联合唯一索引的左前缀匹配（非全匹配）

代码语言：javascript复制
mysql> explain select * from user where name='tenmao';
 ---- ------------- ------- ------------ ------ --------------- ---------- --------- ------- ------ ---------- ------- 
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------ --------------- ---------- --------- ------- ------ ---------- ------- 
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 130     | const |    1 |   100.00 | NULL  |
 ---- ------------- ------- ------------ ------ --------------- ---------- --------- ------- ------ ---------- -------

ref_or_null

与ref类似，但是条件中多了一个IS NULL判断

代码语言：javascript复制
alter table user add address varchar(128) default null;
alter table user add index idx_address(address);
mysql> explain select * from user where address='hello' or address is null;
 ---- ------------- ------- ------------ ------------- --------------- ------------- --------- ------- ------ ---------- ----------------------- 
| id | select_type | table | partitions | type        | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
 ---- ------------- ------- ------------ ------------- --------------- ------------- --------- ------- ------ ---------- ----------------------- 
|  1 | SIMPLE      | user  | NULL       | ref_or_null | idx_address   | idx_address | 515     | const |    2 |   100.00 | Using index condition |
 ---- ------------- ------- ------------ ------------- --------------- ------------- --------- ------- ------ ---------- -----------------------

range

范围查询，一般用在BETWEEN, LIKE, >, <等

代码语言：javascript复制
mysql> explain select * from user where name like 'tenmao';
 ---- ------------- ------- ------------ ------- --------------- ---------- --------- ------ ------ ---------- ----------------------- 
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
 ---- ------------- ------- ------------ ------- --------------- ---------- --------- ------ ------ ---------- ----------------------- 
|  1 | SIMPLE      | user  | NULL       | range | idx_name      | idx_name | 130     | NULL |    1 |   100.00 | Using index condition |
 ---- ------------- ------- ------------ ------- --------------- ---------- --------- ------ ------ ---------- -----------------------

index

扫描整个索引，效率很低，仅仅因为辅助索引的空间比主键索引小，所以比ALL效率高一点。最常用的有SELECT COUNT(*)

代码语言：javascript复制
mysql> explain select count(*) from user;
 ---- ------------- ------- ------------ ------- --------------- ----------- --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------- --------------- ----------- --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_email | 130     | NULL |    1 |   100.00 | Using index |
 ---- ------------- ------- ------------ ------- --------------- ----------- --------- ------ ------ ---------- -------------

ALL

全表扫描

参考

  • EXPLAIN Join Types
join myisam ps range ref

0 人点赞