MYSQL中的type:index 和 Extra:Using index

原创水平有限，如有错误请指出


考虑下面执行计划中的TYPE和Extra

+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | index | NULL          | id2  | 10      | NULL |    3 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+

type:index 不使用索引B+树结构,只使用索引叶子结点链表结构进行扫描，我们知道在索引的叶子结点有一个叶子结点之间的双向指针，
           并且叶子结点的数据是排序好的。他和ALL的方式类似，访问效率并不高，其主要的应用场景为用于避免order by使用using filesort
           也就是避免排序。他是一种访问数据的方式，和const、ref、eq_ref等一样
Extra:Using index  当二级索引包含了所有的查询需要的所有字段的时候，select查询只需要通过索引及可以
                   获得全部的数据，那么就不需要回表了。注意这里全部数据是条件谓词和查询字段的全部
                   总和比如
                   select id1 from test where id2=1；
                   这个索引必须包含id1和id2，这里有种特殊的情况叫做Index Extensions在后面说明
                   它可以考虑B+树结构如使用type:ref也可以不考虑使用type:index
                   一般来说索引的大小要远远小于表的大小，不管从回表还是读取物理文件的大小来说，使用
                   Using index 都可以提高查询性能。也叫索引覆盖扫描

这两个地方是让人经常容易混淆的，并且它们并不是总是一起出现(虽然可能性不小)，实际上他们没有必然的联系
下面是我的测试表结构
mysql> show create table testud;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                        |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testud | CREATE TABLE `testud` (
  `id1` int(11) NOT NULL,
  `id2` int(11) DEFAULT NULL,
  `id3` int(11) DEFAULT NULL,
  `id4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`,`id3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

1、可以单独的出现type:index
mysql> explain select * from testud force index(id2) order by id2;
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | testud | NULL       | index | NULL          | id2  | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这里只是代表type=index避免的排序，但是需要从头到尾使用双向链表来访问整个叶子结点
2、可以单独出现Extra:Using index
mysql> explain select id2 from testud where id2=1;
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | ref  | id2           | id2  | 5       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
这里type为ref,代表通过一个非唯一的索引进行了单个值的扫描 id2=1,也就是这里的(id2,id3)是非唯一索引,而1是单个值，他考虑了索引
的B+树的结构也就是不仅仅考虑了叶子结点，需要从根结点到分支节点(如果有)，再到叶子结点来完成id2=1这种条件的过滤
而因为id2包含在索引(id2,id3)中当然也就使用Using index 就可以了。
从上面两种情况来看type:index和Extra:Using index并没有必然的联系。他们各自代表值的意思

3、共同出现这个就很简单了。
mysql> explain select id2 from testud;
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | index | NULL          | id2  | 10      | NULL |    3 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


需要从头到尾使用双向链表来访问整个叶子结点，而索引id2包含了全部的需要的数据。


这里还需要提高Using index的一种特殊场景，也是很多人问过的。官方文档叫做
9.2.1.7 Use of Index Extensions
简单来说比如上面的KEY `id2` (`id2`,`id3`)，我们知道叶子结点除了索引自己的数据实际上还有主键的数据在末尾，这个我在前面
已经做过验证，参考：
http://blog.itpub.net/7728585/viewspace-2128817/
这个时候实际上索引id2 包含了 id2 id3 id1 这样排列的数据如果id2相等按照id3排序如果id3相等按照id1排序的这样一种结构，那么
我们的using index就扩大了范围比如下的语句：
mysql> explain select id1,id2,id3 from testud where id2=1;
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | testud | NULL       | ref  | id2           | id2  | 5       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


我们可以看到Using index是生效的。具体可以参考官方文档

最后我们来简单说明一下ORACLE中的索引覆盖扫描
ORACLE中分为2种
index fast full scan:主要按照磁盘物理顺序进行扫描，我们知道链表之所以叫做链表是因为它有指向前或者后的指针比如C语言中经常用
                     *next *pr 来表示前后，既然是指向关系在物理上不一定是有序的。但是这种方式更快，可以使用物理上的多块读取
                     但是其返回数据并不有序，仔细考虑实际上MYSQL中没有这种方式
index full scan：这种访问返回就是有序的，他有点像MYSQL中的index+Using index 方式进行扫描，同样他也是为了避免排序而大量使用
                 的。


作者微信：