数据库翻译

对于这个新的楼梯系列的第一部分，我将讨论交叉连接操作符。交叉连接算子简介交叉连接操作符可用于将一个数据集中的所有记录组合到另一个数据集中的所有记录。通过在两组记录之间使用交叉连接运算符，您正在创建所谓的笛卡尔积。下面是使用交叉连接运算符连接两个表A和B的简单示例：
注意，在使用交叉连接操作符时，没有连接两个表的JOIN子句，就像在两个表之间执行内部和外部连接操作时一样。您需要注意，使用交叉连接可以生成一个大型记录集。为了探索这种行为，让我们看两个不同的例子，说明交叉连接操作的结果集有多大。对于第一个例子，假设您交叉连接两个表，其中表A有10行，表B有3行。交叉连接的结果集为10乘3或30行。对于第二个例子，假设表A有1000万行，表B有300万行。表A和表B之间的交叉连接结果集中将有多少行？这将是一个惊人的3万，000，000，000行。这是很多行，创建结果集需要SQL Server大量的时间和大量资源。因此，在大型记录集上使用交叉联接运算符时，您需要小心。让我们通过研究几个例子来进一步研究如何使用交叉联接操作符。交叉连接使用基本示例对于前几个例子，我们将加入两个示例表。清单1中的代码将用于创建这两个示例表。确保在用户数据数据库中运行这些脚本，而不是在主数据库中运行这些脚本。
清单1：交叉连接的示例表对于第一个交叉联接示例，我将运行清单2中的代码。
清单2：简单的交叉连接示例当我在SQLServerManagementStudio窗口中运行清单2中的代码时，使用会话设置以文本输出结果时，我在报告1中获得了输出：
Report 1：运行清单2时的结果如果您查看报告1中的结果，您可以看到有15种不同的记录。前5个记录包含与SalesItem表中的5个不同行连接的Producttable第一行的列值。对于Producttable的2秒和3行也是如此。返回的行总数是Producttable中的行数乘以SalesItem表中的行数(15行)。创建笛卡尔产品的一个原因可能是生成测试数据。假设我想使用ProductandSalesItem表中的日期生成许多不同的产品。我可以使用交叉连接来实现这一点，如清单3所示：清单3：简单的交叉连接示例当我运行清单3中的代码时，我得到了报告2中的输出
Report?2：运行清单3时的结果正如您可以看到的，通过查看清单3中的代码，我生成了许多行，其中包含类似于我的Producttable中的数据的数据。通过使用ROW_Number函数，我能够在每一行上生成唯一的ID列。此外，我还使用SalesItem表中的ID列来创建唯一的ProductName和成本列值。生成的行数等于Producttable中的行数乘以SalesItem表中的行数。到目前为止，本节中的示例只对两个表执行了交叉连接。可以使用交叉连接运算符跨多个表执行交叉连接操作。清单4中的示例在三个表中创建了一个笛卡尔产品。
清单4：使用交叉连接操作符创建三个表的笛卡尔积运行清单4的输出有两个不同的交叉_连接操作。从此代码创建的笛卡尔产品将产生一个结果集，其总行数将等于sys.table中的行数乘以sys.objects中的行数乘以sys.sysuserts中的行数。当交叉连接像内部连接一样执行时在上一节中，我提到当您使用交叉连接运算符时，它将生成一个笛卡尔乘积。这不是一直都是这样的。当您使用限制交叉连接操作中涉及的表的联接的WHERE子句时，SQLServer不会创建笛卡尔产品。相反，它的功能类似于普通的联接操作。要演示这种行为，请查看清单5中的代码。
清单5：两个等效的SELECT语句。清单5中的代码包含两个SELECT语句。第一个SELECT语句使用交叉连接操作符，然后使用WHERE子句定义如何连接交叉连接操作中涉及的两个表。第二个SELECT语句使用带ON子句的普通内部联接运算符连接两个表。SQLServer的查询优化器非常聪明，可以知道清单5中的第一个SELECT语句可以重写为内部联接。当交叉连接操作与WHERE子句一起使用时，优化器知道可以重写查询。WHERE子句在交叉连接所涉及的两个表之间提供连接谓词。因此，SQLServer引擎为清单5中的两个SELECT语句生成相同的执行计划。当您不提供WHERE约束时，SQLServer不知道如何连接涉及交叉连接操作的两个表，因此它在与交叉连接操作关联的两个集合之间创建一个笛卡儿积。使用交叉连接查找未售出的产品前面几节中的示例帮助您理解交叉连接操作符以及如何使用它。使用交叉连接操作符的一个功能是使用它帮助在一个表中查找另一个表中没有匹配记录的项。例如，假设我想报告每个ProductName的总数量和总销售额，在ProductName的每个日期，我的任何一个产品项目被出售。由于在我的示例中，每个ProductName并不是每天都有销售，所以我的报告要求意味着对于那些在某一天没有售出的产品，我需要显示0的数量和0美元的总销售额。在这里，交叉连接操作符与左外部连接操作一起将帮助我识别那些在给定的一天内没有售出的物品。满足这些报告要求的代码可以在清单6中找到：
清单6：查找未使用交叉连接销售的产品让我给你介绍一下这段代码。我创建一个子查询，它选择所有不同的SalesDate值。这个子查询提供了所有销售日期。然后，我将它与我的ProductTable连接起来。这允许我在每个SalesDate和每个Productrow之间创建一个笛卡儿产品。从交叉连接返回的集合将有我在最终结果集中所需的所有值，除了每个售出产品的Qty和TotalSalesAmt之和。为了获得这些摘要值，我对SalesItem表执行一个左外部联接，将它与我用交叉联接操作创建的笛卡尔积连接起来。我基于Productid和SalesDate列执行了这个连接。通过使用左外部联接，我的笛卡尔产品中的每一行都将被返回，如果Productid和SalesDate有一个匹配的SalesDate记录，那么Qty和TotalesAmt值将与适当的行相关联。这个查询所做的最后一件事是使用GROUPBY子句来总结基于SalesDate和ProductName的Qty和TotalesA挂载。绩效考虑产生笛卡尔乘积的交叉连接算子有一些性能方面需要考虑。因为SQL引擎需要将一组中的每一行与另一集中的每一行连接起来，所以结果集可能相当大。如果我做一个交叉连接，一个表有1，000，000行，另一个表有100，000行，那么我的结果集将有1，000，000，000行，或100，000，000行。这是一个很大的结果集，创建它需要大量时间。交叉连接操作符可以是一个很好的解决方案，可以在两个集合的所有可能组合中识别一个结果集，就像每个月所有客户的所有销售一样，即使有几个月有些客户没有销售。当使用交叉连接运算符时，如果要优化性能，则应尽量减少交叉连接集的大小。例如，假设我有一个表，其中包含过去两个月的销售数据。如果我想要生成一个显示一个月内没有销售的客户的报告，那么识别一个月中的天数的方法可能会极大地改变我的查询的性能。为了演示这一点，让我首先为1000名客户创建一套为期两个月的销售记录。我将使用清单7中的代码来完成这个任务。
清单7：创建用于性能测试的示例数据的TSQL清单7中的代码为1，000个不同的客户创建了2个月的数据。此代码没有为每七个客户添加任何销售数据。此代码生成1，000条CUST表记录和52，338条销售表记录。为了演示如何使用交叉连接操作符，取决于在交叉连接输入集中使用的集合的大小，让我运行清单8和清单9中的代码。对于每个测试，我将记录返回结果所需的时间。
清单8：交叉连接所有销售记录
清单9：针对销售日期的不同列表交叉连接在清单8中，交叉连接操作符将1，000条Cust记录与52，338条销售记录连接起来，生成52，338，000行的记录集，然后用于确定一个月内零销售额的客户。在清单9中，我将选择条件从Sales表更改为只返回一组不同的SalesDate值。这个不同的集合只产生61个不同的SalesDate值，因此清单9中的交叉连接操作的结果只生成61，000条记录。通过减少交叉连接操作的结果集，清单9中的查询在1秒内运行，而清单8中的代码在我的机器上运行时间为19秒。造成这种性能差异的主要原因是SQL?Server需要为每个查询执行的不同操作处理大量记录。如果您查看这两个清单的执行计划，您会发现计划略有不同。但是，如果您查看从嵌套循环(内连接)操作生成的估计记录数量，在图形计划的右侧，您将看到清单8估计了52，338，000条记录，而清单9中的相同操作仅估计了61，000条记录。清单8的查询计划从交叉连接嵌套循环操作生成的大型记录集然后传递到几个附加操作。因为清单8中的所有这些操作都必须针对5200万条记录工作。清单8比清单9慢得多。如您所见，在交叉连接操作中使用的记录数量可能会极大地影响查询运行的时间长度。因此，如果您可以编写查询以最小化交叉连接操作中涉及的记录数量，则查询的执行效率将大大提高。结语交叉连接运算符在两个记录集之间产生一个笛卡尔积。此操作符有助于识别一个表中没有匹配记录的项。应注意尽量减少与交叉连接操作符一起使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码运行得越快。问答在本节中，您可以通过回答以下问题来回顾如何使用交叉联接运算符来理解。问题1：交叉联接运算符根据ON子句中指定的列，通过匹配两个记录集来创建结果集。(对还是错)？千真万确假的问题2：当表A和表B包含重复行时，哪一个公式可以用来标识从两个表A和B之间的无约束交叉连接返回的行数？表A中的行数乘以表B中的行数表A中的行数乘以表B中的唯一行数表A中的唯一行数乘以表B中的行数表A中唯一行数乘以表B中唯一行数问题3：哪种方法提供了减少交叉连接操作产生的笛卡尔产品的最佳机会？确保连接的两个集合尽可能多行。确保连接的两个集合尽可能少行。确保交叉连接操作左边的设置尽可能少行。确保交叉连接操作右侧的设置尽可能少行。答案：问题1：正确的答案是b。交叉连接运算符不使用ON子句来执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。当交叉连接连接两个集合时，它创建了一个笛卡尔积。问题2：正确的答案是A、b、c和d，因为如果表A或B中有重复行，则在为交叉联接操作创建笛卡尔积时，每个重复行都是联接。问题3：正确的答案是b。通过减少交叉连接操作中涉及的两个集合的大小，使由交叉连接操作创建的最终集的大小最小化。c和d还有助于减小交叉连接操作创建的最终集的大小，但并不像确保涉及到交叉连接操作的两个集合的行数最少一样优化。本文是高级T-SQL楼梯的一部分。注册我们的RSS提要，并得到通知，一旦我们发布一个新的层次在楼梯上！