为什么MySQL不使用这些可能的键中的任何一个? [英] Why isn't MySQL using any of these possible keys?

问题描述

我有以下查询:

SELECT t.id
FROM account_transaction t
JOIN transaction_code tc ON t.transaction_code_id = tc.id
JOIN account a ON t.account_number = a.account_number
GROUP BY tc.id

当我执行EXPLAIN时，第一行显示了以下内容:

When I do an EXPLAIN the first row shows, among other things, this:

table: t
type: ALL
possible_keys: account_id,transaction_code_id,account_transaction_transaction_code_id,account_transaction_account_number
key: NULL
rows: 465663

为什么键为NULL?

推荐答案

这可能是因为统计数据已损坏，或者是因为它知道您在两个表之间始终具有1:1的比例.

It might be because the statistics is broken, or because it knows that you always have a 1:1 ratio between the two tables.

您可以强制在查询中使用索引，并查看这样做是否可以加快速度.如果是这样，请尝试运行ANALYZE TABLE以确保统计信息是最新的.

You can force an index to be used in the query, and see if that would speed up things. If it does, try to run ANALYZE TABLE to make sure statistics are up to date.

通过指定USE INDEX(index_list)，可以告诉MySQL仅使用命名索引之一来查找表中的行.备用语法IGNORE INDEX(index_list)可用于告诉MySQL不要使用某些特定的一个或多个索引.如果EXPLAIN显示MySQL使用的索引可能不正确，则这些提示很有用.

By specifying USE INDEX (index_list), you can tell MySQL to use only one of the named indexes to find rows in the table. The alternative syntax IGNORE INDEX (index_list) can be used to tell MySQL to not use some particular index or indexes. These hints are useful if EXPLAIN shows that MySQL is using the wrong index from the list of possible indexes.

您还可以使用FORCE INDEX，其作用与USE INDEX(index_list)相似，但另外，假定表扫描非常昂贵.换句话说，仅当无法使用给定索引之一在表中查找行时才使用表扫描.

You can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the given indexes to find rows in the table.

每个提示都需要索引名称，而不是列名称.主键的名称为PRIMARY.要查看表的索引名称，请使用SHOW INDEX.

Each hint requires the names of indexes, not the names of columns. The name of a PRIMARY KEY is PRIMARY. To see the index names for a table, use SHOW INDEX.

来自 http://dev.mysql.com/doc/refman/5.1/zh/index-hints.html

这篇关于为什么MySQL不使用这些可能的键中的任何一个?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！