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_pa​​rts 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屋！