MySQL说明:“使用索引"与“使用索引条件" [英] MySQL EXPLAIN: "Using index" vs. "Using index condition"
问题描述
The MySQL 5.4 documentation, on Optimizing Queries with EXPLAIN, says this about these Extra remarks:
- 使用索引
- 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.
[...]
- 使用索引条件
通过访问索引读取表 元组并首先对其进行测试 确定是否读取完整表 行.这样，索引信息 用于推迟(下推")阅读 全表行，除非是
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")?
推荐答案
一个示例可以最好地说明这一点:
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
该查询将使用'使用索引'进行解释，因为它完全不需要 来命中" myUsedCarInventory表本身，因为"Make and Year"索引发现"了它的需要关于与该表相关的WHERE子句的元素.
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.
现在，想像一下，我们将查询保持不变，但是要在颜色上添加条件
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'
此查询可能会解释为使用索引条件"(可能"，在这种情况下，丰田+年估计得没有足够的选择性，优化器可能决定只扫描表格).这意味着MySQL将 FIRST 使用索引来解析Make + Year，并且还必须查找表中的相应行， 仅用于 满足Make + Year条件的行.这就是有时所说的" push下优化 ".
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 FIRST use the index to resolve the Make + Year, and it would have to lookup the corresponding row in the table as well, only for the rows that satisfy the Make + Year conditions. That's what is sometimes referred as "push down optimization".
这篇关于MySQL说明:“使用索引"与“使用索引条件"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
