mysql explain type_MySQL explain type详解

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 |

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

ALL

全表扫描

参考