如何理解MYSQL中的type:index 和 Extra:Using

70次阅读

共计 3304 个字符，预计需要花费 9 分钟才能阅读完成。

今天就跟大家聊聊有关如何理解 MYSQL 中的 type:index 和 Extra:Using，可能很多人都不太了解，为了让大家更加了解，丸趣 TV 小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

考虑下面执行计划中的 TYPE 和 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;

| 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;

| 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;

| 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;

| 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 方式进行扫描，同样他也是为了避免排序而大量使用的。

看完上述内容，你们对如何理解 MYSQL 中的 type:index 和 Extra:Using 有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注丸趣 TV 行业资讯频道，感谢大家的支持。

正文完