不使用 possible_keys 的 SELECT 语句
我有一个来自遗留系统的表，它没有主键。它记录工厂发放材料的交易数据。
为了简单起见，假设每行包含 job_number、part_number、数量和数量。发布日期。
我在发布日期列中添加了索引。当我运行 EXPLAIN SELECT * FROM Issued_parts WHERE date_issued > 时'20100101'，它显示：
+----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+ | 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 | +----+-------------+----------------+------+-------------------+------+---------+------+---------+-------------+
所以它看到了密钥，但没有使用它？ 有人可以解释为什么吗？
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.
For simplicities sake, lets say each row contains job_number, part_number, quantity & date_issued.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
有件事告诉我 MySQL 查询优化器的决定是正确的。
您可以通过以下方法判断。运行这些：
行计数
与查询匹配的行计数
如果实际检索的行数超过表总数的 5%，MySQL 查询优化器会认为执行全表扫描会更省力。
现在，如果您的查询更精确，例如，使用以下命令：
那么，您将完全得到一个不同的 EXPLAIN 计划。
Something tells me the MySQL Query Optimizer decided correctly.
Here is how you can tell. Run these:
Count of Rows
Count of Rows Matching Your Query
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:
then, you will get a different EXPLAIN plan altogether.
possible_keys使用相关列来命名键，但这并不意味着其中的每个键都对查询有用。在这种情况下，没有一个是。possible_keysnames keys with the relevant columns in, but that doesn't mean that each key in it is going to be useful for the query. In this case, none are.索引有多种类型（索引？）。哈希索引是在给定特定值的情况下查找项目的快速方法。如果您要查询一堆离散值（例如，包含 10 个日期的列表），那么您可以计算每个值的哈希值，并在索引中查找它们。由于您不是对特定值进行查找，而是进行比较，因此哈希索引对您没有帮助。
另一方面，B 树索引可以为您提供帮助，因为它为其索引的元素提供了排序。例如，请参见此处： http://dev.mysql.com /doc/refman/5.0/en/mysql-indexes.html 用于 mysql（搜索 B 树索引特征）。您可能需要检查表的索引列是否使用 B 树索引。
There are multiple types of indexes (indices?). A hash index is a fast way to do a lookup on an item given a specific value. If you have a bunch of discreet values that you are querying against, (for example, a list of 10 dates) then you can calculate a hash for each of those values, and look them up in the index. Since you aren't doing a lookup on a specific value, but rather doing a comparison, a hash index won't help you.
On the other hand, a B-Tree index can help you because it gives an ordering to the elements it is indexing. For instance, see here: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html for mysql (search for B-Tree Index Characteristics) . You may want to check that your table is using a b-tree index for it's index column.