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