MySql中explain之后，type字段和Extra字段中的index-区别

一个查询语句经过explain之后，type字段可能会出现index，Extra中可能会出现using index。

那么二者有什么区别呢？他们是迥然不同的。

type中的index，仅仅是说，查询类型index，表示本次查询仅仅扫描索引树，没有其他读取操作。

Extra中的using index，意思是说，查询使用到了“覆盖索引”。关于覆盖索引，看这里

-----------------------------------------------------------------

表fa的id字段上有主键索引。无其他索引。

看表结构：

mysql> show create table fa;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| fa    | CREATE TABLE `fa` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

看查询：

mysql> explain select * from fa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | fa    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select id from fa;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | fa    | index | NULL          | PRIMARY | 4       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

第二个查询中，type中是index，仅仅扫描了索引。并且在Extra中出现了using index，意思是用到了覆盖索引。