「Using where; Using index」和「Using index」 区别是什么

Question

当我们在MySQL中通过EXPLAIN查看SQL时，观察Extra列会发现，有时候显示「Using index」；还有时候显示「Using where; Using index」，具体区别是什么？

Answer 1

MySQL官方手册解释

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.

Using where
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

得出结果

Using index不读数据文件，只从索引文件获取数据
Using where过滤元组和是否读取数据文件或索引文件没有关系

MySQL官方手册 Using 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.

所以有朋友如此理解

1 没有Using where只有Using index：则不读数据文件，所有字段都可以从索引上获得（Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups）

2 同时有Using where和Using index：因为“Without Using where...”这句上下文，则意味着同时有Using where和Using index则一定需要读取数据文件

其实不然

所以结论如下
Using where只是过滤元组，和是否读取数据文件或索引文件没有关系

Answer 2

using index表示你的sql语句查询使用到了覆盖索引，查询效率高。
using where表示你的sql语句虽然用到了索引，但是想要查询最终结果还需要进行回表，相对于using index来说，效率较低。

Answer 3

using index 是覆盖索引，说明你的查询语句可以只通过查询索引里的信息就能得到结果。using where 则表示需要查询磁盘里存储的数据，速度会慢很多