mysql - 使用 CASE WHEN 优化查询

Question

我的查询看起来像这样:

select cat1, cat3, cat4
       sum(case when cat2='x' then val end) as sumValForX,
       sum(case when cat2='y' then val end) as sumValForY
from your_table WHERE date between somedate and someotherdate AND mainname=somename
group by cat1, cat3, cat4 

基本上，这将我的表分组为 cat1,3,4；并分别显示当 cat2='x' 和 cat2='y' 时结果组的累积总数。

我正在处理一个非常大的表(比如 500 万条记录)，所以这当然需要很多时间。这是可行的，但我只是想问问这里的一些 MySQL 专家，我可以对我的表进行什么样的优化，以加快速度。

这里使用的最佳技术是什么

1. 分组速度更快

2. WHERE 更快(索引？分区？)

3. 速度更快的情况(我认为这是主要的减速)。

关于数据的一些见解:

在大约 500 万条记录中，

mainname 是重复值最多的列。所以 5 种类型的 mainname 可能需要 100 万。

在这 100 万个日期中，我在大约 90 个日期范围内执行 BETWEEN 语句。

此外，在每 100 万个中，最多有 4-5 个不同的 cat2。因此可能还有“x”、“y”或者“z”、“z1”，但不会更多。我有与 cat2 的类型数量相对应的附加 sum(case when... 语句。

换句话来说，我需要每种类型 cat2 的分组总和。

非常感谢。

Answer 1

CASE 表达式不太可能导致太大的“减速”。您可以通过从 SELECT 列表中删除这些表达式来测试这一点。)

合适的覆盖索引可能是提高此查询性能的最佳选择。

根据我的经验，对于大型集合上的此类查询，GROUP BY 是最大的减慢速度。我知道，使用 InnoDB 和合适的覆盖索引，我有时能够消除“使用文件排序”操作(如 EXPLAIN 输出所示)。MySQL 可以使用具有适当前导列的索引来优化 GROUP BY 操作，而不是使用“使用文件排序”操作。 date 列上的范围谓词(在 WHERE 子句中)可能会出现问题，并会产生干扰。

我们需要运行EXPLAIN来验证。

根据查询，我建议使用覆盖索引:

... ON your_table (mainname, cat1, cat3, cat4, date, cat2)

首先是mainname列(由于WHERE子句中的等式谓词，MySQL可以使用索引范围扫描)。

接下来是 GROUP BY 子句中的三列(以优化 GROUP BY 操作)。

后面是查询中引用的附加列(使其成为“覆盖”索引，无需引用基础表中的页面。)

我们希望在 EXPLAIN 输出中看到“使用索引”，而不是“使用临时”和“使用文件排序”。

(我在这里假设对 somedate、someotherdate 和 somename 的引用是对语句中提供的文字值的引用，而不是列引用文献。)

<小时/>

如果返回的行数非常小(与表的大小相比)，那么您可以尝试创建一个索引，以 maindate 作为前导列，后跟 date 列，然后是按任意顺序排列的其他列。使用该索引，MySQL 可以对date 列进行范围扫描，但随后需要执行“文件排序”操作来执行 GROUP BY。

如果返回行的顺序并不重要，您可以尝试添加ORDER BY NULL。我没有看到任何性能提升，但文档表明优化是可能的(也许在最近或 future 的版本中？)

<小时/>

对于一个大集合，我的直觉是优化 GROUP BY。

<小时/>

如果我无法获得良好的执行计划(date 列上的谓词可能是个问题)，我会探索重写语句以将该谓词重新定位到的选项CASE 表达式:

SUM(CASE WHEN t.date BETWEEN 'foo' AND 'bar' AND t.cat2 = 'x' THEN t.val END)

请注意，这可能会通过返回未出现在指定日期范围内的 (cat1,cat3,cat4) 值来更改结果集。如果这是一个问题，我可能会想出一个 HAVING 子句来消除那些“额外”的行。

如果可移植性不是问题，我可能会选择等效的 MySQL IF() 表达式

SUM(IF(t.date BETWEEN 'foo' AND 'bar' AND t.cat2 = 'x', t.val, NULL))

(但这不太可能对性能产生任何改变。)