使用两个范围的查询的索引设计

Question

当我的查询使用2个字段的范围时，我正在尝试了解如何为我的数据设计索引。

expenses_tbl:
idx        date     category      amount
auto-inc   INT       TINYINT      DECIMAL(7,2)
PK

列类别定义了费用的类型。比如，娱乐、服装、教育等等。其他的栏目很明显。

我对该表的一个查询是查找对于给定日期范围，费用超过50美元的所有实例。此查询将如下所示：

SELECT date, category, amount 
FROM expenses_tbl
WHERE date > 120101 AND date < 120811 
      AND amount > 50.00;

如何为这个特定的查询设计这个表上的索引/辅助索引。

假设:这个表非常大(目前还不是，但这给了我一个学习的机会)。

Answer 1

还有几点还没有提到：

索引中列的顺序可能会有所不同。您可能希望同时尝试这两个索引：

(date, amount)
(amount, date)

选择哪一个？通常，您希望最具选择性的条件是索引中的第一列。

• 如果您的日期范围很大，但只有少数费用超过50美元，则您希望将amount放在索引的第一位。
• 如果您的日期范围较窄，并且大多数费用超过50美元，则应将date放在第一位。
• 如果两个索引都存在，则MySQL将选择估计成本最低的索引。

您可以尝试添加这两个索引，然后查看EXPLAIN SELECT ...的输出，看看MySQL为您的查询选择了哪个索引。

您可能还想考虑一个覆盖索引。通过在索引中包含列category (作为最后一列)，这意味着查询所需的所有数据在索引中都可用，因此MySQL根本不需要查看基表即可获得查询结果。

Answer 2

MySQL通常不支持复合索引的多个部分上的范围。它要么使用日期的索引，要么使用金额的索引，但不能同时使用这两种索引。如果您有两个索引，那么它可能会进行索引合并，但我不确定。

我会在添加这些索引之前和之后检查EXPLAIN：

CREATE INDEX date_idx ON expenses_tbl (date);
CREATE INDEX amount_idx ON expenses_tbl (amount);

复合索引范围- http://dev.mysql.com/doc/refman/5.5/en/range-access-multi-part.html

索引合并- http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Answer 3

对您的问题的一般答案是，您需要一个具有两个键的综合索引。第一个是日期，第二个是金额。

请注意，此索引将适用于对日期、日期和费用有限制的查询。它不适用于仅对费用进行限制的查询。如果这两种类型都有，那么您可能需要另一个开销索引。

如果表非常非常大，那么您可能希望按日期对其进行分区，并根据每个分区中的开销构建索引。