MySQL 解释：“使用索引”与“使用索引条件”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1687548/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL EXPLAIN: "Using index" vs. "Using index condition"
提问by Piskvor left the building
The MySQL 5.4 documentation, on Optimizing Queries with EXPLAIN, says this about these Extra remarks:
在MySQL的5.4文档，与EXPLAIN优化查询，说这对这些额外的言论：
- 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 index condition
Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.
- 使用索引
仅使用索引树中的信息从表中检索列信息，而无需进行额外的查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。
[...]
- 使用索引条件
通过访问索引元组并首先测试它们以确定是否读取全表行来读取表。通过这种方式，索引信息用于推迟（“下推”）读取全表行，除非有必要。
Am I missing something, or do these two mean the same thing (i.e. "didn't read the row, index was enough")?
我是不是遗漏了什么，或者这两个意思是一样的（即“没有读取行，索引就足够了”）？
回答by mjv
An example explains it best:
一个例子最好地解释了它：
SELECT Year, Make --- possibly more fields and/or from extra tables
FROM myUsedCarInventory
WHERE Make = 'Toyota' AND Year > '2006'
Assuming the Available indexes are:
CarId
VIN
Make
Make and Year
This query would EXPLAIN with 'Using Index' because it doesn't need, at all, to "hit" the myUsedCarInventory table itself since the "Make and Year" index "cover" its need with regards to the elements of the WHERE clause that pertain to that table.
该查询将与“使用索引”，因为它并不需要解释所有，以“打”的myUsedCarInventory表本身，因为“Make和年”指数“覆盖”其需要与问候到WHERE子句中的元素属于那个表。
Now, imagine, we keep the query the same, but for the addition of a condition on the color
现在，想象一下，我们保持查询不变，但在颜色上添加一个条件
...
WHERE Make = 'Toyota' AND Year > '2006' AND Color = 'Red'
This query would likely EXPLAIN with 'Using Index Condition' (the 'likely', here is for the case that Toyota + year would not be estimated to be selective enough, and the optimizer may decide to just scan the table). This would mean that MySQL would FIRSTuse the index to resolve the Make + Year, and it would have to lookup the corresponding row in the table as well, only forthe rows that satisfy the Make + Year conditions. That's what is sometimes referred as "push down optimization".
这个查询可能会用“使用索引条件”来解释（“可能”，这里是为了估计 Toyota + year 的选择性不够的情况，并且优化器可能决定只扫描表）。这意味着MySQL的将FIRST使用索引来解决制作+年，并且它会查找相应的列在表中为好，只为满足制作+新年条件的行。这就是有时所说的“下推优化”。
回答by Andomar
The difference is that "Using index" doesn't need a lookup from the index to the table, while "Using index condition" sometimes has to. I'll try to illustrate this with an example. Say you have this table:
区别在于“使用索引”不需要从索引到表的查找，而“使用索引条件”有时必须。我将尝试用一个例子来说明这一点。假设你有这张表：
id, name, location
With an index on
带有索引
name, id
Then this query doesn't need the table for anything, it can retrieve all it's information "Using index":
然后这个查询不需要任何表，它可以检索所有它的信息“使用索引”：
select id, name from table where name = 'Piskvor'
But this query needs a table lookup for all rows where name equals 'Piskvor', because it can't retrieve location from the index:
但是此查询需要对 name 等于 'Piskvor' 的所有行进行表查找，因为它无法从索引中检索位置：
select id from table where name = 'Piskvor' and location = 'North Pole'
The query can still use the index to limit the results to the small sets of row with a particular name, but it has to look at those rows in the table to check if the location matches too.
查询仍然可以使用索引将结果限制为具有特定名称的一小部分行，但它必须查看表中的这些行以检查位置是否也匹配。
