MySQL explain type详解

十毛

发布于 2021-01-29 10:41:20

1K0

发布于 2021-01-29 10:41:20

文章被收录于专栏：用户1337634的专栏

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

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

版本

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.22    |
+-----------+

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

system

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

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

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

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

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是最好的

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行记录。比如普通的非唯一索引，或者联合主键和联合唯一索引的左前缀匹配（非全匹配）

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判断

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, >, <等

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(*)

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 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+