对非标识列进行聚集索引以加快批量插入?

现在插入块的速度与我有聚集标识键的情况相比相对较快,与我没有任何聚集索引时的速度差不多。删除块比使用或不使用聚集索引都要快。

更新 :经过一年的设计经验,我可以说,对于这种方法的工作,有必要安排所有索引的定期重建(我们每周进行一次)。否则,索引很快就会变得支离破碎,性能就会丧失。尽管如此,我们正在迁移到一个新的数据库设计中,使用分区表,这基本上在各个方面都更好—除了企业服务器许可证成本,但我们现在已经忘记了这一点。至少我有。

聚集索引是物理索引、物理数据结构和行顺序。如果在聚集索引的中间插入,则数据将物理地插入到当前数据的中间。我认为在这种情况下会出现严重的性能问题。我只从理论上知道这一点,因为如果我在实践中这样做,根据我的理论知识,这将是一个错误。

因此,我只在字段上使用(并建议使用)聚集索引,这些字段总是物理地插入在末尾,保持顺序。

聚集索引可以放在datetime字段上,该字段标记插入时刻或类似的内容,因为从物理上讲,它们将在追加一行之后排序。Identity也是一个很好的聚集索引,但并不总是与查询相关。

你不能试试这个吗,实验?我这里也有类似的情况,我有40亿行,不断有更多的行在插入(高达每秒100行),表没有主键,也没有聚集索引,所以这个主题中的命题对我来说也非常有趣。

从未! 想象一下,您需要将另外一百万行放入该表中,并对它们进行物理排序—从长远来看,这是性能上的巨大损失。

当然。顺便说一下,聚集索引不是万能的,可能比普通索引慢。

看一看这个 System.Data.SqlClient.SqlBulkCopy 应用程序编程接口。如果您需要在数据库中写大量的行,那么它可能就是您所需要的?

大容量复制在单个操作中将数据流式传输到表中,然后执行一次索引检查。我使用它在数据库表中复制500000行,它的性能比我尝试过的任何其他技术都要好一个数量级,假设您的应用程序可以被构造为使用API?

我最近一直在玩一些etl的东西。我通过jsut定期插入到表中,然后在插入前后删除和读取索引,尝试merge语句,最后尝试ssis。我被ssis出卖了。就在昨天,我通过让ssis处理插入操作,成功地将etl进程(约2400万条记录,~6gb)从每次运行约1-1个半小时缩短到了约24分钟。

我相信通过高级服务,您应该能够使用ssis。

浅学误人。有许多问题需要考虑;它们必须一起考虑。处理任何一个问题并孤立地检查它是管理数据库的一种非常零碎的方式:你将永远发现一些新的真相并改变你以前所想的一切。在开始之前,请阅读以下内容 ▶question/answer◀ 上下文。

别忘了,现在任何有键盘和调制解调器的人都可以发表他们的“论文”。他们中的一些人为微软工作,宣扬最新的“增强”;另一些人则发表热情洋溢的报告,指出他们从未在某个上下文中使用过或只使用过一次的功能,但他们发表的报告说,这些功能在每种上下文中都有效。(看看斯彭斯的回答:他很热情,而且“被出卖了”,但在仔细观察之下,这些说法是错误的;他不是坏人,只是微软世界中典型的大众,以及他们是如何运作的;他们是如何发表的。)

• 注意:我用MicroSofties这个词来描述那些相信gatesian概念的人,即任何不合格的人都可以管理数据库;微软会解决一切问题。这并不是一种侮辱,更像是一种亲昵,因为人们相信魔法,并中止了物理定律。

聚集索引

是为关系数据库设计的,由真正的工程师(Sybase,在微软获得代码之前)设计的,他们的大脑比所有微软的总和还要多。关系数据库有关系键,而不是 Id 物联网钥匙。这些是多列键,自动分发数据,因此插入加载,例如始终插入不同公司的发票(尽管在我们讨论的“块”情况下不是这样)。

• 如果您拥有良好的关系键,那么CI将提供范围查询(您的(1)和(2))以及NCIs所没有的其他优势。

• 身份证件 在对数据进行建模和标准化之前,列会严重阻碍建模和标准化过程。

• 身份证件 物联网数据库,那么你将有更多的索引比没有。许多MS数据库的内容不是“关系型”的,它们通常只是非标准化的文件系统,索引比标准化的数据库多得多。因此,有一个很大的推动,许多MS的“增强”尝试给这些流产一点速度。修复症状,但不要靠近引起症状的问题。

• 在其他方面更糟 独联体的普遍性已经丧失。

• NCI携带CI是不正确的(CI是基本的单一存储结构;NCI是次要的,并且依赖于CI;这就是为什么重新创建CI时,所有NCI都是次要的 重新创建)。NCIs负责CI 在叶子层面。

• 微软也有自己的问题,这些问题随着主要版本的变化而变化(但并未消除):

  • 在MS中,这并不是有效的,所以NCI索引 是大的;在企业DBMS中,当这是有效地完成时,这不是一个考虑因素。

  • 因此,在MS世界中,CI键应该尽可能短,这只是事实的一半。如果您知道考虑的是NCIs的大小,并且如果您愿意承担该费用,那么由于精心构造的CI,返回的表非常快,那么这是最佳选择。

  • CI应该是 物联网专栏是完全错误的。CI键的最坏值是单调递增的值(IDENTITY、DATETIME等)。为什么?因为您已经保证了所有并发插入都将争夺当前插入位置,即索引上的最后一页。

  • 分区的真正目的(微软在企业供应商10年后提供了分区)是分散这个负载。当然,他们必须提供一种分配分区的方法,你猜怎么着,除了一个关系键什么都没有;但是首先,现在 物联网密钥分布在32或64个分区上,提供更好的并发性。

• CI必须是唯一的。关系数据库需要唯一的密钥,所以这是一个不需要考虑的问题。

  • 身份证件

  • 更新,因为这一点正在其他地方受到质疑。 我已经说过,微软一直在改变方法,但没有解决问题。

    • MS Online manual ,用他们漂亮的图片(不是技术图表)告诉我们,在2008年,他们用可爱的“唯一化者”取代了(用一个替代另一个)溢出页。

    • 这完全满足了微软。非唯一CI不是问题。它是用魔法处理的。结案。

    • 但这些陈述没有逻辑性或完整性,合格的人会问明显的问题:这个“唯一化者”在哪里?在每一行,或者只是需要“唯一化”的行。DBBC页显示它在每一行上。因此,MS刚刚在每一行中添加了一个4字节的秘密列(包括处理开销),而不是只为非唯一行添加几个溢出页。这是工程学的理念。

  • 无论如何,重点仍然是,非唯一CI有大量开销(现在比以前多),应该避免。最好自己添加一个1或2字节的列,以强制实现唯一性。

• 因此,从一开始(1984年)就没有变化,CI的最佳候选对象是多列唯一关系键(我不能说您的键是确定的,但它看起来确实是这样)。

• 并将任何单调递增的键(IDENTITY,DATETIME)放入NCI。

• 还要记住,CI是一个单一的存储结构,它消除了(否则)堆;cib树与叶级的行相结合;叶级条目 是 排成一排。这保证了每次访问都少读一次。

  • 将行写入单独的存储结构。但是微软相信魔法,他们已经中止了物理定律。
    .

• 你还需要学习和使用许多其他特性,我将至少提到FILLFACTOR和RESERVEPAGEGAP,以使这篇文章更加完整。在理解这些功能之前不要使用它们。所有性能特性都有一个您需要理解和接受的代价。

• CI在页面和数据块级别都是自动修剪的,没有浪费空间。页面分割是需要监视的(仅限于随机插入),而且很容易通过FILLFACTOR和RESERVEPAGEGAP进行调整。

• 无论如何,去掉你的代理密钥( 额外的

• 公司识别器+部门识别器+[识别器]正是我要说的。现在注意到它们已经是int了,而且非常快,所以这样做是非常愚蠢的 添加 数字(10,0) 身份证件 物联网钥匙。使用1或2字节的列强制唯一性。

• 如果你做对了,你可能不需要分区许可证。

• CompanyIdentifier+DepartmentIdentifier+[uniquifier]是CI的最佳候选对象(除了您发布的db之外,对您的db一无所知),在您定期执行大规模删除/插入的上下文中。详见上文。

  • 与其他人所说的相反,这是一件好事,并没有使词支离破碎。假设你有20家公司,删除了1家,占数据的5%。这整个页面链,这是合理的连续,现在降级为自由页面链,连续和完整的。准确地说,你有一个单一的碎片点,但不是正常使用这个词的意义上的碎片。猜猜看,如果你转过身来进行大规模插入,你认为数据会流向哪里?这与删除的行的物理位置完全相同。FreePageChain一次移动到PageChain、extent和page。
    .

现在,请善待我。在你问我20个问题之前,先读一点,了解一下我在这里定义的所有问题。