sql - 在MySQL中，对于连接谓词之间具有多对多关系的大型表，最有效的查询设计是什么？

在我们的应用程序中，我们收集有关汽车发动机性能的数据-基本上是根据发动机类型，运行该发动机的车辆和发动机设计来获取有关发动机性能的数据。当前，新行插入的基础是引擎的开启/关闭时间；我们会根据发动机状态从有效变为无效(反之亦然)来监控性能变量。相关的engineState表如下所示:

+---------+-----------+---------------+---------------------+---------------------+-----------------+
| vehicle | engine    | engine_state  | state_start_time    | state_end_time      | engine_variable |
+---------+-----------+---------------+---------------------+---------------------+-----------------+
| 080025  | E01       | active        | 2008-01-24 16:19:15 | 2008-01-24 16:24:45 |             720 |
| 080028  | E02       | inactive      | 2008-01-24 16:19:25 | 2008-01-24 16:22:17 |             304 |
+---------+-----------+---------------+---------------------+---------------------+-----------------+

对于特定的分析，我们希望基于分钟的行粒度而不是当前事件/非事件引擎状态的基础来分析表内容。为此，我们正在考虑创建一个简单的productionMinute表，该表在我们分析期间的每一分钟都有一行，并将productionMinute和engineEvent表连接到每个表的日期时间列中。因此，如果我们的分析时间段是从2009-12-01到2010-02-28，我们将创建一个新表，其中包含129,600行，该三个月期间每天每一分钟一个。 productionMinute表的前几行:

+---------------------+
| production_minute   |
+---------------------+
| 2009-12-01 00:00    |
| 2009-12-01 00:01    |
| 2009-12-01 00:02    |
| 2009-12-01 00:03    |
+---------------------+

表之间的联接为:

     FROM engineState AS es
LEFT JOIN productionMinute AS pm ON pm.production_minute >= es.state_start_time
                                AND pm.production_minute <= es.event_end_time

但是，这种连接带来了多个环境问题:

engineState表具有500万行，而productionMinute表具有130,000行

当engineState行跨越一分钟以上(即es.state_start_time和es.state_end_time之间的差异大于一分钟)时，就像上面的示例一样，有多个productionMinute表行连接到单个engineState表行

如果在任何给定的分钟内有多个引擎在运行，同样按照上面的示例，多个engineState表行将合并到单个productionMinute行

在测试我们的逻辑时，仅使用一小部分表提取(对于productionMinute表，是一天而不是3个月)，查询需要一个多小时才能生成。在研究此项目以提高性能以便可以查询三个月的数据时，我们的想法是从engineEvent中创建一个临时表，消除对分析不重要的任何表数据，并加入临时表到productionMinute表。我们还计划试验不同的联接，特别是内部联接，以查看这是否可以提高性能。

上面概述的联接谓词之间具有多对多关系的联接表的最佳查询设计是什么？最佳联接类型是什么(左/右，内部)？

最佳答案

我同意vy32。您只需执行一次此查询即可以适合分析的格式获取数据。您应该使用适当的ETL工具(或简单的Perl或简单的工具)将数据从engineState表中取出，计算生产分钟数，然后将其加载到为分析类型查询正确建模的另一个DB中。

如果您一直认为问题出在，只是在对数据进行非规范化并分配分钟数作为代理键。这是一个相对容易(且很常见)的ETL问题，在直接SQL中无法实现，但在其他语言和工具中却很简单。

真正的ETL流程可轻松处理您的生产量。

关于sql - 在MySQL中，对于连接谓词之间具有多对多关系的大型表，最有效的查询设计是什么？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2439697/