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