mysql - 在许多(可能是空的)表中表达 MySQL 查询的正确方法

Question

我正在尝试对数据库表执行一组简单的集合操作:多个交集和一个并集。但我似乎无法用简单的方式表达这一点。

我有一个名为 Moment 的 MySQL 表，其中有数百万行。 (它恰好是一个时间序列表，但这不会影响我这里的问题；但是，这些数据有一个列“源”和一个列“时间”，两者都已索引。)从该表中提取数据的查询是动态创建的(来自 API)，最终归结为一小堆临时表，指示我们关心哪个“源”，也许还有我们关心的“时间”范围。

假设我们正在寻找

(source in Temp1) AND (
    ((source in Temp2) AND (time > '2017-01-01')) OR
    ((source in Temp3) AND (time > '2016-11-15'))
)

只是为了兴奋，假设 Temp2 为空 --- API 请求的该部分是有效的，但碰巧包含“没有实际来源”。

如果我那么做

SELECT m.* from Moment as m,Temp1,Temp2,Temp3
WHERE (m.source = Temp1.source) AND (
  ((m.source = Temp2.source) AND (m.time > '2017-01-01')) OR
  ((m.source = Temp3.source) AND (m.time > '2016-11'15'))
)

...我什么也没得到，因为在我们到达 WHERE 子句之前，空的 Temp2 给出了一个空的笛卡尔积。

好的，我可以

SELECT m.* from Moment as m
  LEFT JOIN Temp1 on m.source=Temp1.source
  LEFT JOIN Temp2 on m.source=Temp2.source
  LEFT JOIN Temp3 on m.source=Temp3.source
WHERE (m.source = Temp1.source) AND (
  ((m.source = Temp2.source) AND (m.time > '2017-01-01')) OR
  ((m.source = Temp3.source) AND (m.time > '2016-11-15'))
)

...但即使在我相对较小的开发数据库上，这也需要 >70 毫秒。

如果我手动消除空表，

SELECT m.* from Moment as m,Temp1,Temp3
WHERE (m.source = Temp1.source) AND (
  ((m.source = Temp3.source) AND (m.time > '2016-11-15'))
)

...它在 10 毫秒内完成。这正是我所期望的时间。

我还尝试将单个不匹配的行放入空表中并执行 SELECT DISTINCT，它在大约 40 毫秒内分割了差异。不过，这似乎是一个奇怪的解决方案。

这真的感觉就像我对查询的概念化是错误的，我要求数据库做比它需要的更多的工作。向数据库询问这个问题的正确方法是什么？

谢谢!

--更新--

我对我的实际数据库做了一些实际的基准测试，并得出了一些真正意想不到的结果。

对于上面的场景，所有在比较的列上建立索引的表，以及一个空表，

• 使用左连接完成这件事花了 3.5 分钟(!!!)
• 在没有连接的情况下执行此操作(仅“FROM...WHERE”)并向空表添加空行，花费了 3.5 秒

更引人注目的是，没有一个空表，而是每个临时表中有大约 1000 行，

• 在一个查询中完成整个操作需要 28 分钟(!!!!!)，但是，
• 分别执行三个 AND 子句，然后在代码中执行最终组合，只用了不到一秒的时间。

我仍然觉得我正在以某种愚蠢的方式表达查询，因为我再次尝试做的就是一组并集(OR)和一些集合交集。看起来数据库确实在不需要的时候却在制造这个巨大的笛卡尔积。总而言之，正如下面的答案所指出的，在代码中保留一些智能似乎是更好的方法。

Answer 1

有多种方法可以解决这个问题。不用说，这取决于

1. 有多少查询发送到数据库，
2. 您在某个时间间隔内处理的数据量，
3. 如何配置数据库后端来管理它。

对于您的用例，更多信息会有所帮助。通过在查询中使用 CASE/COUNT(*) 或 CASE/LIMIT 组合来排序空表来优化查询将是一种选择。但是，类似 if 的查询会花费更多时间。

您可以拆分 SQL 代码，将问题的规模从 1*N^x 降级为 y*N^z，其中 z 应小于 x。

你说涉及到一个API，也许你可以以不同的方式处理临时的“无数据”表，甚至不存储它们？

另一个选项是启用查询缓存: https://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html