什么时候以及为什么数据库连接是昂贵的?

连接表的顺序非常重要。如果您有两组数据，请尝试以一种方式构建查询，因此将首先使用最小的数据，以减少查询必须处理的数据量。

详述别人说过的话，

连接只是一些唇彩的笛卡尔积。{1,2,3,4}X{1,2,3}将给出12种组合(nXn=n^2)。这个计算集作为应用条件的参考。DBMS应用条件(比如左边和右边都是2或3)来给我们匹配条件。实际上，它更优化，但问题是一样的。对集合大小的更改将以指数方式增加结果大小。所消耗的内存和cpu周期的数量都以指数形式受到影响。

当我们非规格化时，我们完全避免了这种计算，就像在你的书的每一页上贴上一个彩色的粘性纸。你可以不使用参考而推断出信息。我们付出的代价是，我们损害了DBMS的本质(数据的最佳组织)。

去规范化以提高业绩?这听起来很有说服力，但根本站不住脚。

Chris Date和Ted Codd博士一起是关系数据模型的最初支持者，他对反对标准化的错误观点失去了耐心，并使用科学方法系统地推翻了它们:他得到了大型数据库并测试这些断言。

我想他在关系数据库写作1988-1991中写了它，但这本书后来被卷进了数据库系统概论的第6版，这是的关于数据库理论和设计的权威文本，在我写的时候，在它的第8版中，并且很可能在未来的几十年里继续印刷。当我们大多数人还光着脚到处跑的时候，克里斯·戴特已经是这个领域的专家了。

他发现:

• 其中一些是针对特殊情况的
• 所有这些都不能用于一般用途
• 在其他特殊情况下，所有这些都要严重得多

这一切都归结于减小工作集的大小。包含正确选择的键和正确设置的索引的连接是廉价的，而不是昂贵的，因为它们允许对结果之前进行显著的修剪。

实现该结果需要大量磁盘读取，这是该操作中成本最高的一个数量级。相比之下，执行连接在逻辑上只需要检索键。在实践中，甚至不获取键值:键哈希值用于连接比较，减少了多列连接的成本，并从根本上降低了涉及字符串比较的连接的成本。不仅缓存容量大大增加，而且读取磁盘的工作量也大大减少。

此外，一个好的优化器会选择最严格的条件，并在执行连接之前应用它，非常有效地利用连接对高基数索引的高选择性。

诚然，这种类型的优化也可以应用于非规范化的数据库，但那些想要来非规范化模式的人通常在设置索引时不考虑基数。

了解表扫描(在产生连接的过程中检查表中的每一行)在实践中很少发生是很重要的。查询优化器只在下列一个或多个条件成立时才会选择表扫描。

• 关联中的行数少于200行(在这种情况下，扫描会更便宜)
• 联接列上没有合适的索引(如果在这些列上联接是有意义的，那么为什么不为它们建立索引?修复它)
• 在比较列之前需要进行类型强制转换(WTF?!)要么修理它，要么回家
• 比较的参数之一是一个表达式(没有索引)

执行操作比不执行操作代价更大。然而，执行错误的操作，被迫进行无意义的磁盘I/O，然后在执行真正需要的连接之前丢弃糟心部分，是多更昂贵的。即使预先计算了“错误的”操作，并且合理地应用了索引，仍然会有很大的损失。去规范化以预计算连接(尽管包含更新异常)是对特定连接的承诺。如果你需要一个不同的连接，这个承诺将花费你大。

如果有人想提醒我，这是一个不断变化的世界，我想你会发现，在更笨重的硬件上，更大的数据集只会夸大Date的发现的传播。

对于所有在计费系统或垃圾邮件生成器工作的人(可耻)，并愤怒地将手放在键盘上告诉我，你知道一个事实，非规范化更快，对不起，但你生活在一个特殊的情况下-具体地说，你处理数据的所有的情况，按顺序。这不是一般情况，你是在你的策略中是合理的。

你有不理由错误地概括它。有关在数据仓库场景中适当使用非规范化的更多信息，请参阅注释部分的末尾。

我还想回应

连接只是一些唇彩的笛卡尔积

真是一派胡言。限制会尽早实施，最严格的限制先实施。你读过这个理论，但你还没有理解它。查询优化器将连接治疗作为“谓词应用的笛卡尔积”只有。这是一种符号表示(实际上是一种规范化)，以促进符号分解，这样优化器就可以生成所有等效的转换，并根据成本和选择性对它们进行排序，以便选择最佳的查询计划。

让优化器产生笛卡尔积的唯一方法是不能提供谓词:SELECT * FROM A,B

笔记

David Aldridge提供了一些重要的补充信息。

除了索引和表扫描之外，确实还有很多其他的策略，而现代的优化器在产生执行计划之前会消耗掉这些策略。

一个实用的建议:如果它可以用作外键，那么就索引它，这样对优化器来说，索引策略就是可用。

我曾经比MSSQL优化器更聪明。这在两个版本之前发生了改变。现在它通常教我。从一个非常真实的意义上说，它是一个专家系统，将许多非常聪明的人在一个非常封闭的领域中的所有智慧编成法典，以规则为基础的系统是有效的。

“胡扯”可能不太得体。他们要求我不要那么傲慢，并提醒我数学不会说谎。这是事实，但并不是所有数学模型的含义都必须从字面上理解。负数的平方根是非常方便的，如果你仔细避免检查它们的荒谬(双关语)，并在你试图解释你的方程之前确保你把它们都消掉了。

我反应如此激烈的原因是声明的措辞是这样说的

加入是笛卡尔积…

这可能不是什么意思，但它是所写的，这是绝对不真实的。笛卡尔积是一种关系。连接是一个函数。更具体地说，连接是一个关系值函数。对于空谓词，它将产生笛卡尔积，检查它这样做是数据库查询引擎的正确性检查，但没有人在实践中编写无约束连接，因为它们在课堂之外没有实际价值。

我把它提出来是因为我不想让读者陷入把模型和被建模的东西混淆的古老陷阱。模型是一种近似值，为了方便操作而刻意简化。

选择表扫描连接策略的截止日期可能因数据库引擎而异。它受到许多实现决策的影响，如树节点填充因子、键值大小和算法的微妙性，但广义上讲，高性能索引的执行时间为k log n + c。C项是一个固定的开销，主要由设置时间组成，曲线的形状意味着你不会得到回报(与线性搜索相比)，直到n达到数百。

有时候，去规范化是个好主意

非规范化是对特定连接策略的承诺。如前所述，这会干扰其他连接策略。但是，如果您有大量的磁盘空间、可预测的访问模式以及处理大部分或全部数据的趋势，那么对连接进行预计算是非常值得的。

您还可以确定操作通常使用的访问路径，并预先计算这些访问路径的所有连接。这是数据仓库背后的前提，或者至少当它们由知道为什么要做他们正在做的事情的人构建时是这样，而不仅仅是为了遵从流行词汇。

通过规范化事务处理系统的批量转换，可以定期生成设计合理的数据仓库。这种操作和报告数据库的分离具有消除OLTP和OLAP(在线事务处理即数据输入和在线分析处理即报告)之间冲突的非常理想的效果。

这里重要的一点是，除了定期更新之外，数据仓库是只读。这使得更新异常的问题变得毫无意义。

不要犯使OLTP数据库(数据输入发生的数据库)非规范化的错误。它可能更快的计费运行，但如果你这样做，你会得到更新异常。有没有试过让《读者文摘》停止给你寄东西?

现在磁盘空间很便宜，所以请自便。但非规范化只是数据仓库故事的一部分。更大的性能收益来自预先计算的汇总值:每月总计，诸如此类。它是关于减少工作集的总是。

ADO。NET问题与类型不匹配

假设您有一个SQL Server表，其中包含一个类型为varchar的索引列，并且您使用AddWithValue传递一个参数来约束对该列的查询。c#字符串是Unicode，因此推断的参数类型将是NVARCHAR，这与VARCHAR不匹配。

VARCHAR到NVARCHAR是一种扩展转换，所以它是隐式发生的——但是跟索引说再见吧，希望你能找到原因。

“计算磁盘的点击率”(里克·詹姆斯)

如果所有东西都缓存在RAM中，JOINs是相当便宜的。也就是说，归一化没有太多性能损失。

如果一个“规范化”模式导致JOINs大量地访问磁盘，但是等效的“非规范化”模式不必访问磁盘，那么非规范化就会在性能竞争中获胜。

来自原作者的评论:现代数据库引擎非常擅长组织访问排序，以最大限度地减少连接操作期间的缓存丢失。上面所说的虽然是正确的，但可能会被误解为暗示连接在大数据上的代价必然是有问题的。这将导致缺乏经验的开发人员做出糟糕的决策。

大多数评论者没有注意到的是，在复杂的RDBMS中可以使用广泛的连接方法，而非规范化者总是掩盖维护非规范化数据的更高成本。并不是每个连接都基于索引，数据库有许多用于连接的优化算法和方法，旨在降低连接成本。

在任何情况下，连接的成本取决于它的类型和其他一些因素。它一点也不贵——举个例子。

• 散列连接，即批量数据的等价连接，确实非常便宜，只有当哈希表不能缓存在内存中时，成本才会变得显著。不需要索引。在连接的数据集之间进行等量分区会有很大的帮助。
• 排序-合并连接的成本是由排序的成本而不是归并驱动的——基于索引的访问方法实际上可以消除排序的成本。
• 索引上嵌套循环连接的开销由b-tree索引的高度和表块本身的访问驱动。它速度很快，但不适合批量连接。
• 基于集群的嵌套循环连接要便宜得多，每个连接行所需的logicAL IO更少——如果连接的表都在同一个集群中，那么通过连接行的托管，连接将变得非常便宜。

数据库是为连接而设计的，它们在如何进行连接方面非常灵活，通常性能非常好，除非连接机制出错。

我认为整个问题都建立在一个错误的前提上。大型表上的连接不必然昂贵。事实上，高效地进行连接是关系数据库存在的主要原因之一根本没有。大型集上的连接通常是昂贵的，但很少希望将大型表A的全部内容与大型表b的全部内容连接起来，相反，您编写查询时使用每个表的只有重要的行，并且连接保留的实际集保持较小。

此外，您还具有Peter Wone提到的效率，这样在最终结果集物化之前，每条记录的重要部分只需要存储在内存中。此外，在具有许多连接的大型查询中，您通常希望从较小的表集开始，然后逐步到较大的表集，以便内存中保留的表集尽可能长时间地保持较小。

如果操作得当，连接通常是最好的方法，用于比较、组合或筛选大量数据。

瓶颈几乎是总是磁盘I/O，甚至更具体地说——随机磁盘I/O(相比之下，顺序读取相当快，可以用预读策略进行缓存)。

join 可以增加随机搜索-如果你在一个大表中跳跃读取一小部分。但是，如果查询优化器认为这样更好的话，它会将其转换为顺序表扫描(丢弃不需要的行)。

单个非规格化表也有类似的问题——行很大，因此不太适合单个数据页。如果您需要相距很远的行(大的行大小使它们之间的距离更远)，那么您将有更多的随机I/O。同样，为了避免这种情况，可能会强制进行表扫描。但是，这一次，由于行大，表扫描必须读取更多数据。再加上你的复制数据从一个位置到多个位置的事实，RDBMS有更多的读取(和缓存)。

对于2个表，您还可以获得2个聚集索引—并且通常可以索引更多(因为插入/更新开销更少)，这可以极大地提高性能(主要是因为索引(相对)较小，可以快速从磁盘读取(或缓存成本低)，并减少需要从磁盘读取的表行数量)。

联接的唯一开销来自于计算匹配的行。Sql Server使用3种不同类型的连接(主要基于数据集大小)来查找匹配的行。如果优化器选择了错误的连接类型(由于统计数据不准确、索引不足，或者只是优化器错误或边缘情况)，就会极大地影响查询时间。

• 对于(至少1个)小数据集，循环连接是非常便宜的。
• 合并连接首先需要对两个数据集进行排序。但是，如果您连接的是一个索引列，那么索引已经排序，不需要做进一步的工作。否则，在排序过程中会有一些CPU和内存开销。
• 哈希连接需要内存(存储哈希表)和CPU(构建哈希表)。同样，相对于磁盘I/O来说，这是相当快的。然而，如果没有足够的RAM来存储哈希表，Sql Server将使用tempdb存储哈希表的一部分和找到的行，然后一次只处理哈希表的一部分。与所有磁盘一样，这是相当慢的。

在最佳情况下，这些操作不会导致磁盘I/O，因此从性能角度看可以忽略不计。

总而言之，在最坏的情况下——从x个连接表中读取相同数量的逻辑数据实际上应该更快，因为它是从单个非规范化表中读取，因为磁盘读取更小。要读取相同数量的物理数据，可能会有一些轻微的开销。

由于查询时间通常由I/O成本决定，并且数据的大小不会因非规范化而改变(减去一些非常微小的行开销)，因此仅仅将表合并在一起并没有巨大的好处。倾向于提高性能的非规范化类型IME是缓存计算值，而不是读取计算这些值所需的10,000行。

当您考虑连接的复杂性类时，决定是去规范化还是规范化是相当简单的过程。例如，当查询为O(k log n)时，我倾向于用规范化设计数据库，其中k相对于所需的输出大小。

反规格化和优化性能的一个简单方法是考虑对规范化结构的更改如何影响反规格化结构。然而，这可能会有问题，因为它可能需要事务逻辑才能在非规范化的结构化上工作。

关于正常化和非正常化的争论不会结束，因为问题是巨大的。许多问题的自然解决方案需要两种方法。

作为一般规则，我总是存储可以重构的规范化结构和非规范化缓存。最终，这些缓存帮我解决了未来的规范化问题。