SQL:内部连接两个大型表

我不是SQL调优专家,但在我所知道的任何数据库系统中,在一个VARCHAR字段中加入数亿行听起来都不是一个好主意。

对于大型联接,有时会显式选择 loop join 加快速度:

SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM 
INNER LOOP JOIN BioEntity 
    ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId

一如既往,公布您的预计执行计划可以帮助我们提供更好的答案。

MERGE JOIN :

SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM 
INNER JOIN BioEntity 
    ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
OPTION (MERGE JOIN)

首先,100M行连接根本不是不合理或不常见的。

您对日志文件增长如此之大的观察基本上证实了这一点。

也许有点离题,但是: “我注意到电脑偶尔会每隔30秒(给或拿)堵塞几秒钟。”

这种行为是便宜的RAID5阵列(或者可能是单个磁盘)在复制(并且您的查询主要复制数据)千兆字节信息时的特征。

关于这个问题的更多信息-您不能将查询划分为更小的块吗?例如以A、B等开头的名称或特定范围内的ID?这可以大大减少事务/锁定开销。

我会尝试删除“LIKE”操作符;因为你似乎没有做任何通配符匹配。

按照建议,我会将名称散列以使联接更合理。如果可能的话,我将强烈考虑通过查找查找批处理中的ID分配,因为这将消除以后加入连接的必要性(并且潜在地重复执行这样低效的连接)。

SELECT EGM.Name
       ,BioEntity.BioEntityId
INTO AUX 
FROM EGM 
INNER JOIN BioEntity  
    ON EGM.TypeId = BioEntity.TypeId -- Hopefully a good index
    AND EGM.NameHash = BioEntity.NameHash -- Should be a very selective index now
    AND EGM.name LIKE BioEntity.Name

1亿张唱片是巨大的。我想说,要使用这么大的数据库,您需要一个专用的测试服务器。在执行类似的查询时,使用同一台机器做其他工作是不实际的。

您的硬件功能相当强大,但要让这么大的连接正常运行,您需要更多的电源。8GB的四核系统将是一个良好的开端。除此之外,您还必须确保索引设置正确。

你有主键或索引吗?你能分阶段选择吗?i、 e.其中名称如“A%”,其中名称如“B%”,等等。

不要 希望您的TypeId是这些表上的聚集索引。你想要生物实体&要群集的EGMId(将 身体上 按磁盘上聚集索引的顺序对数据进行排序。你想要

在我们的环境中,每个表大约有1000-2000万条记录。我们做了很多类似于您的查询,我们在一列或两列上组合两个数据集。为添加索引 每个

请记住,对于1亿条记录,这些索引将需要 很多

斯科特有一篇很好的文章 here 这更深入地解释了一些问题。

TypeId的选择性如何?如果100M+行中只有5、10甚至100个不同的值,那么索引对您没有任何帮助——特别是因为您选择了所有行。

CREATE TABLE BioEntity
 (
   BioEntityId  int
  ,Name         nvarchar(4000)
  ,TypeId       int
  ,NameLookup  AS checksum(Name) persisted
 )

然后像这样创建一个索引(我会使用聚集索引,但即使是非聚集索引也会有帮助):

CREATE clustered INDEX IX_BioEntity__Lookup on BioEntity (NameLookup, TypeId)

(检查BOL,在计算列上构建索引有一些规则和限制,这些规则和限制可能适用于您的环境。)

SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
 FROM EGM INNER JOIN BioEntity 
 ON EGM.NameLookup = BioEntity.NameLookup
  and EGM.name = BioEntity.Name
  and EGM.TypeId = BioEntity.TypeId

编辑:如果这是一个正在进行的过程,您应该在两个表之间强制执行外键约束,以便将来进行导入/转储。如果没有进行,哈希可能是你最好的方法。

我会尝试用开箱即用的方法来解决这个问题,也许有其他算法可以比数据库做得更好更快。当然,这一切都取决于数据的性质,但有一些字符串搜索算法非常快(Boyer Moore、ZBox等),或者其他数据挖掘算法(MapReduce?),通过仔细设计数据导出,可以将问题转化为更优雅、更快的解决方案。此外,可以更好地并行化问题,通过一个简单的客户机利用您周围系统的空闲周期,有一个框架可以帮助解决这个问题。

它的输出可以是一个refid元组列表,您可以使用它更快地从数据库中获取完整数据。

这并不妨碍您尝试索引,但如果您必须等待6天的结果,我认为这证明了您有理由花费资源探索其他可能的选项。

导出表后,编写一个脚本来执行这个简单的连接。它将花费大约相同的时间来执行,但不会杀死数据库。

对于脚本,您需要为较大的数据集编制索引,然后遍历较小的数据集并查找较大的数据集索引。这将是O(n*m)运行。

如果散列匹配占用了太多的资源,那么一次批量执行查询,例如10000行,“遍历”TypeID列。您没有提到TypeID的选择性,但它的选择性可能足以完成如此小的批处理,并且一次完全覆盖一个或多个TypeID。您还需要在批处理中查找循环联接,因此如果仍然获得哈希联接,则可以强制循环联接或减小批处理大小。

在简单恢复模式下,使用批处理还可以防止tran日志变得非常大。即使在简单恢复模式下,像您这样的大型连接也会消耗大量空间,因为它必须保持整个事务处于打开状态,而在进行批处理时,它可以为每个批重新使用日志文件,将其大小限制为一个批处理操作所需的最大大小。

如果您真的需要加入名称,那么您可能会考虑一些帮助表将名称转换为IDS,基本上暂时修复非规范化设计(如果无法永久修复)。

关于校验和的想法也可以很好,但我自己还没有玩过很多。

在任何情况下,这样一个巨大的哈希匹配都不会像批处理循环联接那样执行。如果你能得到一个合并加入它将是可怕的。。。

我想知道执行时间是由连接还是由数据传输占用的。

编辑: 好的,我看到您正在插入Aux表。数据库恢复模型的设置是什么?

要调查硬件方面的瓶颈,可能需要考虑限制资源是读取数据还是写入数据。例如,您可以启动windows性能监视器的运行,并捕获磁盘读写队列的长度。