When only cluster key and idx_price
exist as indexes..
当只有簇键和IDX_PRICE作为索引存在时..
Query: select id , name from product where price < 20000
查询:选择id,名称取自价格<20000的产品
Explane:
ExPlane:
detail |
value |
id |
1 |
select_type |
SIMPLE |
table |
product |
partitions |
NULL |
type |
range |
possible_keys |
idx_price |
key |
idx_price |
key_len |
9 |
ref |
NULL |
rows |
7 |
filtered |
100.00 |
Extra |
Using index condition |
1 row in set 1 warning (0.00 sec) |
|
As far as I know, the Using index condition
occurs depending on the presence or absence of the index of where clause
.
据我所知,USING INDEX条件的出现取决于WHERE子句的索引的存在与否。
But I just created and used idex_price
for where clause.
但我刚刚为WHERE子句创建并使用了IDEX_PRICE。
What did I miss?
我错过了什么?
更多回答
If you don't have an index, it has to do a full scan to test the condition on every row. If you have an index, it uses the index. Isn't that what you expect?
如果您没有索引,它必须执行完全扫描以测试每一行上的条件。如果你有一个索引,它会使用这个索引。这不就是你所期待的吗?
This means it is using the index to satisfy your where clause, but still needs to access the row in the clustered index to read the other columns, as they are not contained in the index. If you add the name
column to your index it would be "covering". See Using index
and Using index condition
.
这意味着它正在使用索引来满足WHERE子句,但仍然需要访问聚集索引中的行来读取其他列,因为它们不包含在索引中。如果将名称列添加到索引中,它将是“Covering”。请参阅使用索引和使用索引条件。
@Barmar I wonder why 'Using index condition' occurs in that query
@Barmar我想知道为什么在该查询中会出现‘Using INDEX Condition’
@user1191247 'Extra : Using index condition' is same 'Index Condition Pushdown(ICP)' ? If it means the same, why did 'ICP' occur in that query? Does index range scan cause 'ICP'?
@user1191247‘Extra:Using Index Condition’是否与‘Index Condition Push Down(ICP)’相同?如果意思相同,为什么查询中会出现‘icp’?指数范围扫描会导致‘ICP’吗?
@user1191247 As far as I know, 'ICP' can occur when columns that exist in 'where clause' do not exist in the index table. So why does ICP occur when only 'price' exists in the 'where clause' of that query?
@user1191247据我所知,当‘WHERE子句’中存在的列在索引表中不存在时,可能会出现‘icp’。那么,当该查询的“WHERE子句”中只存在“价格”时,为什么会出现比较呢?
"Extra: Using index condition" means that it's making use of Index Condition Pushdown Optimization. It's using the idx_price
index to optimize the price < 20000
condition in the WHERE
clause. Instead of scanning the table data to find the rows that satisfy the condition, it only scans the index.
“Extra:Using Index Condition”意味着它正在利用索引条件下推优化。它使用idx_PRICE索引来优化WHERE子句中的价格<20000条件。它不是扫描表数据来查找满足条件的行,而是只扫描索引。
"Type: range" means that only rows in the range < 20000
are retrieved. Since indexes are B-trees, range conditions are easily optimized. In this case it can search the B-tree for 20000
and then return all the rows before this in the tree.
“Type:Range”表示只检索范围<20000的行。由于索引是B树,因此范围条件很容易优化。在这种情况下,它可以在B树中搜索20000,然后返回树中在此之前的所有行。
Before ICP (see Barmar's answer), the "Handler" took care of some of the filtering. With the Pushdown, the storage engine (InnoDB) does more of work. The performance benefit comes from not going back and forth between the Handler and the Storage Engine.
在国际比较方案之前(见Barmar的回答),“操纵者”负责一些过滤。通过下推,存储引擎(InnoDB)可以完成更多的工作。性能优势来自于无需在处理程序和存储引擎之间来回切换。
ICP, I think, only refers to the WHERE
clause.
我认为,国际比较方案只指WHERE子句。
"Using index" is a better Extra -- It says that all the columns anywhere in the SELECT
were found in the INDEX
. That is, the query can be completely performed using only the index's BTree -- no bouncing back and forth between that and the data's BTree.
“Using index”是一个更好的补充--它说明在索引中找到了SELECT中任何位置的所有列。也就是说,仅使用索引的BTree就可以完全执行查询--不会在该索引和数据的BTree之间来回切换。
更多回答
Understood. Thanks for the detail. I guess I lacked an understanding of the 'ICP',
明白了。谢谢你的细节。我想我对《国际比较方案》缺乏理解,
Then, should I understand that the price<2000
clause caused 'ICP'?
那么,我是不是应该理解为价格<2000条款导致了‘比较价’呢?
If so, I was expecting using where
, but I posted this question because "ICP" occurred.
如果是这样的话,我预计会使用Where,但我发布这个问题是因为出现了“icp”。
@tray - Without name
(and with the range test on price
), the entire query could be performed in the index ("Using index"). Without the range test, there would be a table scan -- likely to be much slower.
@托盘-没有名称(并且对价格进行范围测试),整个查询可以在索引中执行(“使用索引”)。如果没有射程测试,就会有表格扫描--可能会慢得多。
我是一名优秀的程序员,十分优秀!