mysql index_MYSQL中的type:index 和 Extra:Using index

原创有限，有错误请指出

考虑以下执行计划 TYPE 和 Extra

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+-------+-------------+

| ID | | 表 | | 类型 | | 键 | | ref | 行 | | 额外 |

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+-------+-------------+

| 1 | | | NULL | 索引 | NULL | id2 | 10 | NULL | 3 | 100.00 | 使用索引 |

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+-------+-------------+

type:index没有使用索引B+树结构，而只使用索引叶子节点链表结构进行扫描，我们知道在索引叶子节点之间有一个双向指针。

并且对叶子节点的数据进行了排序，和ALL类似，但是访问效率不高，其主要应用场景是避免使用order by。

也就是避免排序。它是一种访问数据的方式，就像 const、ref 等一样。

额外：使用索引当二级索引包含所有查询所需的所有字段时，查询只需要经过索引和

获取全部数据，那么就不需要返回表了。注意，这里的所有数据是指所有的条件谓词和查询字段。

总数是

id1 来自测试，其中 id2=1；

这个索引必须包含id1和id2，有一个特殊的情况叫Index，后面会讲到。

可以考虑B+树结构比如使用type:ref或者不考虑使用type:index

一般来说，索引的大小比表的大小要小很多，不管是从表的大小还是物理文件的大小来说。

使用索引可以提高查询性能。也叫索引覆盖扫描

这两个地方经常被混淆，而且它们并不总是一起出现（尽管可能性不小）。事实上，它们并不一定有联系。

下面是我的测试表结构

mysql>显示表；

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 表格 | 表格 |

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| | 表 ``（

`id1` int(11) 非空，

`id2` int(11) NULL，

`id3` int(11) NULL，

`id4` int(11) NULL，

键（`id1`），

键 `id2` (`id2`,`id3`)

）= = |

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

一组 1 行（0.05 秒）

1. Type:index 可以单独出现

mysql> * 从强制索引（id2）按id2排序；

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+------+------+------+------+

| ID | | 表 | | 类型 | | 键 | | ref | 行 | | 额外 |

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+------+------+------+------+

| 1 | | | NULL | 索引 | NULL | id2 | 10 | NULL | 3 | 100.00 | NULL |

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+------+------+------+------+

1 行，共 1 个，1（0.00 秒）

这正好代表了通过type=index避免的排序，但是需要一个双向链表才能从头到尾访问整个叶子节点。

2. 额外：使用索引可以单独出现

mysql> id2 来自其中 id2=1;

+----+-------------+--------+------------+------+-----------+--------+---------+---------+-------+---------+----------+-------------+

| ID | | 表 | | 类型 | | 键 | | ref | 行 | | 额外 |

+----+-------------+--------+------------+------+-----------+--------+---------+---------+-------+---------+-------------+

| 1 | | | NULL | ref | id2 | id2 | 5 | const | 1 | 100.00 | 使用索引|

+----+-------------+--------+------------+------+-----------+--------+---------+---------+-------+---------+-------------+

1 行，共 1 个，1（0.00 秒）

这里的type是ref，表示通过非唯一索引扫描单个值。id2=1，即(id2,id3)为非唯一索引，1为单个值。会考虑索引。

B+树的结构不仅仅考虑叶子节点，还需要从根节点过滤到分支节点（如果有的话），再到叶子节点完成id2=1的条件。

由于 id2 包含在索引 (id2, id3) 中，因此可以使用使用索引。

从上面两个案例可以看出type:index和Extra:Using index没有必然联系，它们各自代表了值的含义

3.如果它们一起出现的话，这就很简单了。

mysql> id2 来自；

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+-------+-------------+

| ID | | 表 | | 类型 | | 键 | | ref | 行 | | 额外 |

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+-------+-------------+

| 1 | | | NULL | 索引 | NULL | id2 | 10 | NULL | 3 | 100.00 | 使用索引 |

+----+-------------+--------+------------+-------+---------------+-----+--------+------+------+-------+-------------+

1 行，1（0.01 秒）

需要一个从头到尾的双向链表来访问整个叶节点，而索引 id2 包含了所有需要的数据。

这里需要改进一个使用索引的特殊场景，这个场景很多人都问过，官方文档叫

9.2.1.7 索引的使用

比如上面的KEY `id2`(`id2`,`id3`)，我们知道叶子节点除了索引自己的数据之外，其实最后还有主键数据。

已验证，参考：

此时索引id2中其实包含的数据是这样排列的：id2 id3 id1，如果id2相等，则按照id3排序，如果id3相等，则按照id1排序。

我们使用索引扩大了范围，包括以下语句：

mysql> id1,id2,id3，其中 id2=1；

+----+-------------+--------+------------+------+-----------+--------+---------+---------+-------+---------+-------------+

| ID | | 表 | | 类型 | | 键 | | ref | 行 | | 额外 |

+----+-------------+--------+------------+------+-----------+--------+---------+---------+-------+---------+-------------+

| 1 | | | NULL | ref | id2 | id2 | 5 | const | 1 | 100.00 | 使用索引|

+----+-------------+--------+------------+------+-----------+--------+---------+---------+-------+---------+-------------+

1 行，1（0.01 秒）

可以看到Using index是有效的，具体可以参考官方文档

最后，我们简单解释一下索引覆盖扫描

有两种类型

索引快速全扫描：主要按照磁盘的物理顺序进行扫描。我们知道链表之所以被称为链表，是因为它有指向前面或者后面的指针。例如在C语言中，经常用到

*next *pr 表示上一个和下一个。由于指向关系不一定是物理顺序的。不过这种方式速度更快，而且可以使用物理多块读取。

但是返回的数据并不是按顺序排列的，仔细想想，MYSQL中其实没有这个方法。

索引全扫描：这种访问返回的是有序的，它有点像MYSQL中的索引+使用索引扫描，它也使用了很多来避免排序

的。