运行EXPLAIN时，如果key的字段值不为null但Extra为空，是否使用了key? [英] When running EXPLAIN, if the field value for key is not null but Extra is blank, is the key used?

问题描述

在运行没有索引的EXPLAIN查询时

When running this EXPLAIN query without an index

EXPLAIN SELECT exec_date,
    100 * SUM(CASE WHEN cached = 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_no,
    100 * SUM(CASE WHEN cached != 'no' THEN 1 ELSE 0 END)  / SUM(1) cached_yes
FROM requests
GROUP BY exec_date

这是输出

id  select_type table       type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      requests    ALL     NULL            NULL    NULL    NULL    478619  Using temporary; Using filesort

如果我创建索引

ALTER TABLE requests ADD INDEX exec_date(exec_date);

输出为

id  select_type table       type    possible_keys   key         key_len ref     rows    Extra
1   SIMPLE      requests    index   NULL            exec_date   4       NULL    497847  

由于Extra的值为空白，是否表示未使用键exec_date?

Since the value of Extra is blank, does that mean the key exec_date is not being used?

在测试服务器上，带有和不带有索引的实际(不是EXPLAIN语句)查询的执行时间是相同的.

On a test server, the execution time of the actual (not the EXPLAIN statement) query with and without the index is the same.

推荐答案

Using index并不意味着您认为的意思.如果它出现在Extra列中，则表明优化器实际上并未读取整个行，而是使用索引(排他性地)查找列信息.

Using index doesn't mean what you think it means. If it is present in the Extra column, it indicates that the optimizer isn't actually reading the entire rows, it is using the index (exclusively) to find column information.

该键可能仍用于其他用途，例如，如果有WHERE子句，则执行查找等.在您的特定情况下，例如，Using temporary;的消失实际上意味着您的索引正在被使用，因为MySQL不再需要将表的内容重新排列到新的临时表中以执行GROUP BY.

The key could still be in use for other things, for example to perform lookups if you have a WHERE clause etc. In your specific scenario, for example, the disappearance of the Using temporary; actually does mean that your index is being utilized, since MySQL no longer needs to rearrange the contents of your table into a new temporary table to perform the GROUP BY.

这篇关于运行EXPLAIN时，如果key的字段值不为null但Extra为空，是否使用了key?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！