mysql extra 为空_运行EXPLAIN时，如果key的字段值不为null但Extra为空，是否使用了key？...

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

This is the output

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

If I create an index

ALTER TABLE requests ADD INDEX exec_date(exec_date);

The output is

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE requests index NULL exec_date 4 NULL 497847

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

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 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.

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.