首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >在MySQL中,连接连接谓词之间的多到多关系的大型表,最有效的查询设计是什么?

问在MySQL中,连接连接谓词之间的多到多关系的大型表,最有效的查询设计是什么?
EN

Stack Overflow用户
提问于 2010-03-13 19:27:39
回答 6查看 2.4K关注 0票数 9

在我们的应用中,我们收集了关于汽车发动机性能的数据--基本上是基于发动机类型、车辆运行和发动机设计的发动机性能数据。当前,新行插入的基础是引擎关闭期;我们根据引擎状态从活动状态更改为非活动状态来监视性能变量,反之亦然。相关的engineState表如下所示:

代码语言:javascript
复制
+---------+-----------+---------------+---------------------+---------------------+-----------------+
| 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表的前几行:

代码语言:javascript
复制
+---------------------+ 
| production_minute   |
+---------------------+
| 2009-12-01 00:00    |
| 2009-12-01 00:01    |
| 2009-12-01 00:02    |     
| 2009-12-01 00:03    |
+---------------------+

表之间的连接将是:

代码语言:javascript
复制
     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 

然而,这种加入带来了多个环境问题:

  1. engineState表有500万行,而productionMinute表有13万行,当engineState行跨越超过1分钟(即es.state_start_time和es.state_end_time之间的差值大于1分钟)时,有多个<engineState>D17表行连接到单个engineState表行H 219H 120,也像上面的例子一样,多个engineState表行连接到单个d22H223/code>G224/code>。

在测试我们的逻辑时,只使用一个小的表提取(对于productionMinute表只使用一天而不是3个月),这个查询需要超过一个小时才能生成。为了提高性能以便能够查询三个月的数据,我们的想法是从engineEvent表创建一个临时表,消除对分析不重要的任何表数据,并将临时表加入到productionMinute表中。我们还计划尝试不同的连接--特别是内部连接--看看这是否会提高性能。

连接多个表的最佳查询设计是什么:如上所述,连接谓词之间的许多关系?什么是最佳的连接类型(左/右,内)?

EN

回答 6

Stack Overflow用户

发布于 2010-03-29 17:05:56

我同意vy32的观点。您需要执行此查询一次,并且只需执行一次,才能以适合分析的格式获取数据。您应该使用适当的ETL工具(或者仅仅是perl或其他简单的工具)将数据从engineState表中提取出来,计算生产分钟,然后将其加载到另一个为分析类型查询建模的DB中。

如果您认为您的问题,通过您只是去或错误的您的数据和分配分钟数作为代孕键。这是一个相对容易(也是常见的) ETL问题,它在直接SQL中不具有性能,但在其他语言和工具中很简单。

您的产量将很容易由一个真正的ETL过程处理。

票数 2
EN

Stack Overflow用户

发布于 2010-03-23 13:11:12

数据检索性能是

访问磁盘上数据的

  • 速度(取决于索引的存在、表的大小、缓存的大小、原始I/O速度)
  • 中需要返回的记录数(一些连接减少了返回的行数,一些增加了,一些条件可以应用于索引,有些条件必须放到记录中),需要返回

所需的

  • 列数

对于所有这些,您可以优化

通过垂直分区将表的大小添加到indexes

  • reducing (将表分割成两个或更多语义不同的表--例如,如果从您的5m表中,您实际上只使用100 k记录,99.5%的时间,您可能可以将表拆分为活动/非活动或similar)

  • providing,您不能垂直拆分一个表--该表中的列的数目也会影响检索速度(但much)

  • finally可以通过透明地将表拆分成多个硬表来提高原始I/O的速度)磁盘(但知道基础文件系统属性)

索引对性能的影响最大,因为它们可以将磁盘访问时间和内存操作速度减少数量级(它们以维护索引结构为代价将O(n)转换为log O(n);因此它们会减慢更新速度)。

为了获得最大的检索速度,索引应该涵盖所有的连接,以及条件和查询应该以这样一种方式编写,这样查询优化器就可以确定如果首先执行哪个条件和查询会产生最大的效益(最高的选择性)。

对于特定的示例,尝试对不同的索引组合进行基准测试。

  1. pm.production_minute应该使用
  2. 和es.state_start_time和es.state_end_time进行索引,您有4种可能的索引选项(您可以组合这些选项):

es.state_start_time索引

es.state_end_time索引

索引(es.state_start_time,es.state_end_time)

(es.state_end_time,es.state_start_time) 索引

知道你的数据会让你决定哪一个是最优的。如果您发现拥有最后两个列索引的效果最好,我也不会感到惊讶。或具有一列和二列索引(但按列的相反顺序)。

在这两种情况下,体面的优化器都可以通过读取索引甚至不查看实际记录来确定结果集,从而大大减少了磁盘访问。

票数 1
EN

Stack Overflow用户

发布于 2010-03-13 21:13:57

性能将取决于表中数据的结构。

只有当您想要选择投影的左表或右表中的所有值,而且这些值可能在表中没有要连接的内容时,左或右外部联接才会有用。

相信您的查询优化器会为您的数据找到最有效的联接算法.它的建造是为了知道如何把它的工作做好。如果存在性能问题,请查看数据的结构和存储方式。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/2439697

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档