mysql - EXPLAIN中的 "Using index"和 "Using where; Using index"有什么区别

Question

在mysql中解释的额外字段中你可以得到:

• 使用索引
• 使用where;使用索引

两者有什么区别？

为了更好地解释我的问题，我将使用下表:

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `another_field` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO test() VALUES(),(),(),(),();

最终的内容如下:

SELECT * FROM `test`;

id  another_field
1   0
2   0
3   0
4   0
5   0

根据我的研究发现

Why is this query using where instead of index?

The output of EXPLAIN can sometimes be misleading.

For instance, filesort has nothing to do with files, using where does not mean you are using a WHERE clause, and using index can show up on the tables without a single index defined.

Using where just means there is some restricting clause on the table (WHERE or ON), and not all record will be returned. Note that LIMIT does not count as a restricting clause (though it can be).

Using index means that all information is returned from the index, without seeking the records in the table. This is only possible if all fields required by the query are covered by the index.

Since you are selecting *, this is impossible. Fields other than category_id, board_id, display and order are not covered by the index and should be looked up.

我也发现了

https://dev.mysql.com/doc/refman/5.1/en/explain-output.html#explain-extra-information

Using index

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.

For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

(看第二段)

我的问题:

第一:第二段的写法我没看懂。

第二:

以下查询返回

EXPLAIN SELECT id FROM test WHERE id = 5;

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      test    const   PRIMARY         PRIMARY 8       const   1       Using index

(向右滚动)

这个其他查询返回:

EXPLAIN SELECT id FROM test WHERE id > 5;

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      test    range   PRIMARY         PRIMARY 8       NULL    1       Using where; Using index

(向右滚动)

除了一个查询使用范围搜索而另一个使用常量搜索这一事实之外，这两个查询都在表上使用一些限制子句(WHERE 或 ON)，并且不会返回所有记录.

第二个查询中的 Using where; 是什么意思？它不在第一个查询中的事实是什么意思？

---

额外

与使用索引条件有什么区别；使用哪里？(我没有添加这方面的示例，因为我无法在一个小的自包含的操作系统代码中重现它)

Answer 1

当您在说明的 Extra 部分看到 Using Index 时，这意味着(覆盖)索引对于查询来说已经足够了。
在您的示例中: SELECT id FROM test WHERE id = 5; 服务器不需要访问实际表，因为它可以满足查询(您只访问 id)仅使用索引(如解释所述)。如果您不知道 PK 是通过唯一索引实现的。

当你看到 Using Index;使用 where 意味着首先使用索引来检索记录(不需要对表的实际访问)，然后在此结果集之上完成 where 子句的过滤。
在此示例中: SELECT id FROM test WHERE id > 5; 您仍然从索引中获取 id ，然后应用大于条件过滤掉与条件不匹配的记录