SQL - 查询加速技巧

Question

我目前使用以下查询，由于数据量较大（约14个月），返回结果需要大约8分钟。请问有什么方法可以加快速度吗？

所涉及的数据库是MySQL，使用的是InnoDb引擎。

select
    CUSTOMER as CUST,
    SUM(IF(PAGE_TYPE = 'C',PAGE_TYPE_COUNT,0)) AS TOTAL_C,
    SUM(IF(PAGE_TYPE = 'D',PAGE_TYPE_COUNT,0)) AS TOTAL_D
from
        PAGE_HITS
where
    EVE_DATE >= '2016-01-01' and EVE_DATE <= '2016-01-05'
    and SITE =  'P'
    and SITE_SERV like 'serv1X%'
group by
    CUST

数据按6个月进行分区。每个进入where子句的列都被索引。有相当多的索引，这里不方便一一列举。因此，只能用简洁的语言进行总结。就这个查询而言，EVE_DATE + PAGE_TYPE_COUNT是复合索引之一，CUST + SITE_SERV + EVE_DATE、EVE_DATE + SITE_SERV、EVE_DATE + SITE也是如此。

主键实际上是一个虚拟自增数字。说实话，它没有被使用。我无法访问解释计划。我会尽力为此做到最好。

如果您能提供任何帮助来改善这个问题，我将不胜感激。

- usert4jju7

---

你能说明使用了哪些索引（如果有的话），并且这些结构是什么样子的吗？是否使用了主键等？ - CR41G14

请您提供更多的细节信息：行数、索引、存储引擎等。 - Pavel Zimogorov

将现有查询用作内部查询，并将 SITE_SERV like 'serv1X%' 移至外部查询。 - artm

我刚刚更新了问题，加入了数据存储结构以回答上述评论。 - usert4jju7

如果没有对SELECT查询的EXPLAIN，很难（甚至不可能）确定哪些部分可以改进。 - Arjan

Answer 1

我没有数据，所以无法测试这个速度，但我认为它会更快。

select
    CUSTOMER as CUST,
    SUM(PAGE_TYPE_COUNT * (PAGE_TYPE = 'C')) AS TOTAL_C,
    SUM(PAGE_TYPE_COUNT * (PAGE_TYPE = 'D')) AS TOTAL_D
from
        PAGE_HITS
where
    EVE_DATE >= '2016-01-01' and EVE_DATE <= '2016-01-05'
    and SITE =  'P'
    and SITE_SERV like 'serv1X%'
group by
    CUST

在我的MySql 5.6 fiddle上它运行得非常好。

- xpy

---

不错的技巧，我一定会尝试用它来简化一些查询；就性能而言，你有任何指标吗？ - Preuk

谢谢Xpy。这看起来很棒。我一定会在其他地方使用它。在我的情况下，没有性能提升。不过这真的很好。 - usert4jju7

Answer 2

主要的优化因素是索引。应尽可能紧密地匹配您的查询，例如：

EVE_DATE, SITE, CUST, SITE_SERV

对于SITE_SERV作为最后一个值的情况来说，顺序很重要；因为你在它上面使用了LIKE，你将不会使用全值，这会降低下一列的索引效率。

如果你移除IF并返回类型和计数，可能还能稍微提高一点性能；或者你可以在前端应用程序中处理/格式化这个值。

无论如何，你应该首先使用EXPLAIN对当前查询进行分析，看看哪里出了问题。如果你无法这样做，可以在本地数据库上复制结构、索引和一些虚拟数据，此时数据量并不重要。

- Preuk

---

谢谢Preuk。我很高兴去掉“IF”，但有什么办法可以高效地计算条件“SUM”吗？你能帮忙吗？ - usert4jju7

我会建议只选择 PAGE_TYPE, SUM(PAGE_TYPE_COUNT) AS TOTAL 并在你的前端应用程序中处理 'C' 或 'D' 的情况；但正如我所说，这可能甚至不值得。我纠正了一些拼写错误，我的句子没有任何意义。 - Preuk

谢谢Preuk。我将在我的开发中使用这个建议。关于这个问题，我需要在数据库层处理数据 :( - usert4jju7

如果你不能跳过IF语句，那就保留它们吧 :) 检查一下是否有一个包含eve_date、site、cust和site_serv索引的模式，并尝试将其放在可以运行EXPLAIN命令的数据库上。这是我能推荐的全部内容。 - Preuk

Answer 3

好的，由于表范围分区基于EVE_DATE，因此DBMS应该很容易看到要读取哪个分区。因此，关键在于使用什么索引。

有一个列需要检查相等性（SITE = 'P'）。这应该是你的索引中的第一项。然后可以按任意顺序添加EVE_DATE和SITE_SERV。因此，你的索引应该能够尽快地定位相关的表记录。

如果你将查询中使用的其他字段添加到索引中，那么表甚至不必被读取，因为所有数据都可以在索引中获得：

create index on page_hits(site, eve_date, site_serv, customer, page_type, page_type_count);

如果我没有错的话，这应该是您查询的最佳索引。

- Thorsten Kettner

---

非常感谢你，Thorsten。这个改进提高了一些性能。 - usert4jju7

Answer 4

添加这两个索引：

INDEX(site, date)
INDEX(site, site_serv)

优化器将查看统计信息并在它们之间进行选择。粗略地说，如果该范围内具有较少的“P”和日期行，则第一个选项比“P”和“serv1X％”更好。

是的，Thorsten提到的“覆盖”索引可能更好，但其中包含的字段比我想在索引中放置的字段多。

分区可能会有所帮助，但信息太少无法确定。分区可能有所帮助的原因是您有一个“二维”查找——日期范围和“serv1X％”。您需要在日期或site_serv上进行分区，然后将PRIMARY KEY（站点，...，...）与（日期或site_serv）作为第二列。其余列需要包括分区键和某些列以使其唯一。这变得非常混乱，我不想思考它。

- Rick James