mysql - 求和/格查询的最佳索引策略

Question

我有一个使用innodb作为存储引擎的MySQL数据库，并且我有许多采用基本形式的查询：

SELECT bd.billing,
  SUM(CASE WHEN tc.transaction_class = 'c'  THEN bd.amount ELSE 0 END) AS charges,
  SUM(CASE WHEN tc.transaction_class = 'a' THEN bd.amount ELSE 0 END) AS adjustments,
  SUM(CASE WHEN tc.transaction_class = 'p' THEN bd.amount ELSE 0 END) AS payments,
  SUM(bd.amount) AS balance_this_month
FROM billing_details bd
JOIN transaction_classes tc ON tc.transaction_code = bd.transaction_code
WHERE bd.entry_date BETWEEN '2013-06-04' AND '2013-07-01'
GROUP BY billing;

我正在尝试找出对采用这种形式的查询的列编制索引的最佳策略。在我开始之前，只有单列上的索引，并且有一个解释表明正在读取150万行（对于您来说，这里只有一个月的数据量）。

我的第一次尝试是将这个数字降低到300,000，这是通过建立索引（entry_date，billing，transaction_code）实现的。经过更多阅读（特别是高性能MySQL）后，我认为将entry_date（通常是范围表达式）作为最左列并不是最佳选择，因此我尝试了（计费，transaction_code，entry_date）并解释了类似4的内容。 -500,000行。仍然是对第一个数字的改进，但是随着我的深入研究，我开始怀疑：

对于这种查询，我可以从最佳索引中合理地期望什么？我猜想因为我正在执行聚合函数，所以它总是要建立一个临时表并执行文件排序……还是吗？我读得越多，就会越困惑。我的本能是将entry_date用作最左边的列，因为这是我的where子句中的唯一规定。更多的研究使我相信我应该把它放在最正确的位置，因为我要查询一系列的日期。但是然后，我所读的内容实际上只是在谈论where子句-该子句仅具有entry_date：像这样的总和/案例查询呢？能否以一种有益的方式向该索引添加数量，或者除非重新设计架构/查询，否则我将被束之高阁吗？

Answer 1

根据您的查询，不清楚不合格的列（例如entry_date）所指向的表。 （最佳实践是，为了读者的利益，限定查询中的所有列引用，并在将同名的列添加到查询中的其他表中时，从“歧义列”异常中证明您的查询。）

我将假设不合格的列来自billing_details表。

涵盖索引的最有可能的候选人是：

... ON billing_details (entry_date, billing, transaction_code, amount)

... ON transaction_classes (transaction_code, transaction_class)

对于两个表访问，EXPLAIN应该在 extra列中显示“正在使用索引”。 （如果transaction_classes表足够小，则索引可能根本不重要。）

“覆盖索引”意味着可以完全从索引中满足查询，而无需引用基础表的页面。

Optimizing Queries with EXPLAIN http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

此处的策略是首先获取索引中谓词中的列，以便可以执行索引范围扫描操作。我认为其他列的顺序不太重要。接下来使用计费列可能会对MySQL的GROUP BY有所帮助，但是我认为测试将显示这无关紧要。

JOIN操作可以受益于连接谓词中列的索引，在这种情况下，该索引位于较小的transaction_classes“ lookup”表上。但是，如果内部联接实际上是从billing_details表中过滤出行（transaction_classes表中没有匹配值的行，那么我们可能会将其视为过滤谓词并具有索引。但是，我怀疑，存在外键关系，并且billing_details表中此列不是NULL，因此billing_details表中的每一行在transaction_classes表中都有匹配的行。

如果要访问 billing_details表中的大多数行，则最好首先在GROUP BY中引用这些列，而不是在谓词中引用这些列，例如：

... ON billing_details (billing, entry_date, transaction_code, amount)

在这种情况下，MySQL可能能够避免使用“使用文件排序”操作来将行分组在一起。同样，我认为在那之后其他各列的顺序并不重要。在这种情况下，它将是全索引扫描，而不是范围扫描。索引中的每一行都需要检查entry_date，以确定是否包括在内。

如果 entry_date上的谓词返回一小部分行（例如，小于10％），则使用索引优先于该列的访问计划可能会执行得更好。



摘要

就此查询的性能而言，在谓词上获取索引可以显着减少识别要包括的行所需的工作量，而无需访问每一行。

下一个“大石头”是GROUP BY。如果要访问表中的每一行（根本没有谓词），则最佳索引位于GROUP BY子句中的列上。由于这些值是按此列排序的，因此MySQL可以避免必须执行排序操作，这在大型集合上可能会很昂贵。

除了在billing_details表上使用适当的索引之外，您可以做的下一件最好的事情是消除对transaction_classes表的联接，而仅使用transaction_code列中的值。

CASE中条件条件的处理对查询时间的贡献不大。需要花费时间的是访问需要处理的值，并对行进行排序，以便可以对它们进行“分组”。



跟进

'使用临时的；计划中使用filesort”是由于GROUP BY操作。 MySQL使用WHERE子句的索引来减少行数。现在，MySQL必须对这些行进行排序。这是预期的。

至少“使用索引”表明MySQL正在完全从索引获取行，而无法访问基础表（这通常可以提高性能）。

避免对GROUP BY（AFAIK）使用“使用文件排序”的唯一方法是使用GROUP BY中引用的列作为前导列的索引。

要查看MySQL是否将使用这样的索引，可以尝试禁用MySQL将索引用于WHERE子句的功能。执行此操作（用于测试）的最简单方法是将 bd.entry_date列引用包装在函数的WHERE子句中。

更改该谓词，并使用以下一些变体尝试 EXPLAIN

WHERE DATE(bd.entry_date) BETWEEN 
WHERE DATE(bd.entry_date) + INTERVAL 0 DAY BETWEEN
WHERE DATE_FORMAT(bd.entry_date,'%Y-%m-%d') BETWEEN

其中的一些（或全部）应该足以使MySQL禁止通过entry_date使用索引来满足WHERE子句。

通过有效禁用该索引作为选择，MySQL可以决定使用以 billing列为前导列的索引，以避免“使用文件排序”操作。 （在这种情况下，几乎必须使索引也包含entry_date列，因为该列将需要在表中的每一行上进行检查，实际上是对所有行进行“完全扫描”。

同样，对于一小部分行，此查询计划可能会更昂贵。这可能运行速度较慢，但​​确实需要进行测试。 （如果查询根本没有WHERE子句，并且正在拉出所有行，则这种类型的计划（很有可能）比执行排序操作快得多。）