2020-09-18
发表者 Venus
mysql中explain分析sql执行type字段值的含义已关闭评论
# mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all、index、range、ref、eq_ref、const。
# 从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，应当尽量优化你的sql语句，使它的type尽量靠右
# 资料来自：https://blog.csdn.net/dennis211/article/details/78170079
创建2张表测试
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`version` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
insert into `user` (`id`, `name`, `version`) values('1','aaaaaaa','11');
insert into `user` (`id`, `name`, `version`) values('2','bbbbbb','10');
CREATE TABLE `info` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`num` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_num` (`num`),
KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
insert into `info` (`id`, `name`, `age`, `num`) values('1','user1','1','11');
insert into `info` (`id`, `name`, `age`, `num`) values('2','user2','2','12');
insert into `info` (`id`, `name`, `age`, `num`) values('3','user3','3','13');
insert into `info` (`id`, `name`, `age`, `num`) values('4','user4','4','14');
insert into `info` (`id`, `name`, `age`, `num`) values('5','user5','5','15');
测试：
# type = all ，age字段无索引，则进行全表扫描
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE age = 1;'
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | info | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
# type = index 且 Extra = NULL 时，为走索引的全表扫描。这样的索引，在排序时比type = all的排序速度快。
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info ORDER BY id DESC;'
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | SIMPLE | info | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
# type = index 且 Extra = Using index 则为索引覆盖，从索引直接获取了数据，不用查表。name字段是普通索引
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT `name` FROM info;'
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | info | NULL | index | NULL | index_name | 303 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
# type = range ， id自增主键，范围扫描
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE id <= 100;'
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | info | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
# type = ref ， name字段普通索引，sql使用了索引，但该索引列的值并不唯一，有重复
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE `name` = "user1";'
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | info | NULL | ref | index_name | index_name | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
# type = eq_ref ， 字段索引类型唯一，但单表测试结果是const，下面换联合查询可以。
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE num = 11;'
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | info | NULL | const | index_num | index_num | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
mysql -uroot -p"root" -e 'use aaa;EXPLAIN
SELECT i.*
FROM `user` AS u
LEFT JOIN info AS i
ON u.id = i.id;'
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+
| 1 | SIMPLE | u | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Using index |
| 1 | SIMPLE | i | NULL | eq_ref | PRIMARY | PRIMARY | 4 | aaa.u.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------------+
# const ， id自增主键，精确定位
mysql -uroot -p"root" -e 'use aaa;EXPLAIN SELECT * FROM info WHERE id = 1;'
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | info | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
