SELECT语句不使用possible_keys [英] SELECT statement not using possible_keys
问题描述
我有遗留系统中没有主键的表。它记录了在工厂发布材料的交易数据。
I have a table from a legacy system which does not have a primary key. It records transactional data for issuing materials in a factory.
为简单起见,我们可以说每行包含job_number,part_number,quantity& date_issued。
For simplicities sake, lets say each row contains job_number, part_number, quantity & date_issued.
我在发布日期列中添加了一个索引。当我运行EXPLAIN SELECT * FROM issued_parts WHERE date_issued>'20100101'时,它显示:
I added an index to the date issued column. When I run an EXPLAIN SELECT * FROM issued_parts WHERE date_issued > '20100101', it shows this:
+----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | issued_parts | ALL | date_issued_alloc | NULL | NULL | NULL | 9724620 | Using where |
+----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+
因此它看到了密钥,但它没有使用它?
有人可以解释原因吗?
So it sees the key, but it doesn't use it? Can someone explain why?
推荐答案
有些东西告诉我MySQL查询优化器正确决定。
Something tells me the MySQL Query Optimizer decided correctly.
以下是您的判断方法。运行这些:
Here is how you can tell. Run these:
行数
SELECT COUNT(1) FROM issued_parts;
匹配查询的行数
SELECT COUNT(1) FROM issued_parts WHERE date_issued > '20100101';
如果您实际检索的行数超过表总数的5%,则MySQL查询优化程序决定进行全表扫描的工作量会减少。
If the number of rows you are actually retrieving exceeds 5% of the table's total number, the MySQL Query Optimizer decides it would be less effort to do a full table scan.
现在,如果您的查询更精确,例如,使用:
Now, if your query was more exact, for example, with this:
SELECT * FROM issued_parts WHERE date_issued = '20100101';
然后,您将获得完全不同的EXPLAIN计划。
then, you will get a different EXPLAIN plan altogether.
这篇关于SELECT语句不使用possible_keys的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!