如何防止MySQL在有更好的索引时选择一个索引？

Question

我有一个有30,000行(还在增长)的表，我将它与另一个表连接起来。在一些页面中，我需要运行这些查询的一些100+，然后事情变得很慢。如果我使用EXPLAIN查询，我注意到一个表使用主键并且很快，但是另一个表使用它的索引之一，这不是最好的索引。下面是一个概述：

SIMPLE | acc_entries | ref | ledger,date,type,status,status_ledger_date_type | type | 1 | const | 15359 | Using where

这是一个示例查询：

SELECT SUM(usd) AS total FROM acc_entries
LEFT JOIN acc_ledgers ON acc_entries.ledger = acc_ledgers.id
WHERE acc_entries.status = 1 AND 
acc_ledgers.account = 3004 AND 
date >= '2011-01-01' AND 
date <= '2011-08-30' AND 
type = 'credit'

正如您所看到的，我在WHERE中使用了字段status、ledger (这是与acc_ledgers.account连接的字段)、date和type。所有这些字段都有索引。但是，也有一个特定索引用于所有这些索引，顺序相同。它被称为status_ledger_data_type，正如您所看到的，它是MySQL考虑使用的索引之一。但是，在最后，MySQL选择使用type作为索引。这个索引有大约15,000个可能的行(表的一半)，而另一个组合索引只包含其中的一小部分。所以我的问题是:当有更好的索引可用时，MySQL为什么选择这个索引，我如何防止这种情况发生？

Answer 1

您可以尝试使用索引提示来强制使用所需的索引。

MySql docs on Index Hints

The Battle Between Force Index and the Query Optimizer

7 ways to convince MySQL to use the right index

Answer 2

实际上，您希望您的索引基于较小的粒度。Acc_Entries表中的分类帐将在其主索引ID上连接到ACC_Ledgers表，因此Acc_Ledgers实际上并没有将分类帐部分用于WHERE子句。您的索引应该与常见查询的WHERE子句匹配得尽可能紧密。在本例中，我将有一个索引

(帐户、状态、类型、日期)

Account的原因首先是较小的结果集。你可能有5,000个条目。其中，300个条目对应于单个帐户帐户，因此您已经消除了要处理的大量数据。然后，状态...在300中，你可以有100 @ status 1,100 @ status 2,100 @ status 3，所以你现在已经通过其他类型和日期的标准进一步减少了集合，依此类推。

否则你的查询是完全正确的...这只是我个人的写作风格，我试着用WHERE条件以相同的顺序编写与索引非常匹配的查询，所以我只需要首先使用Account子句，然后是Status、Type和Date……但是再说一次，这是写查询的个人风格。