sql 执行计划嵌套循环_性能调优–嵌套和合并SQL循环与执行计划

最新推荐文章于 2022-07-19 17:41:12 发布

culuo4781

最新推荐文章于 2022-07-19 17:41:12 发布

阅读量978

点赞数

文章标签： java mysql 数据库 sql python

原文链接：https://www.sqlshack.com/performance-tuning-nested-and-merge-loops-with-execution-plans/

版权

本文详细介绍了SQL执行计划中的嵌套循环和合并循环，重点讨论了它们在性能调优和T-SQL调试中的作用。通过分析Adventure Works数据库中的例子，解释了如何理解和利用执行计划来优化查询，特别是涉及INNER JOIN操作时。

摘要由CSDN通过智能技术生成

sql 执行计划嵌套循环

In this article, we will explore Nested and Merge SQL Loops in the SQL Execution plan from a performance tuning view.

在本文中，我们将从性能调整的角度探讨SQL执行计划中的嵌套和合并SQL循环。

Even though reading execution is technical, it is more an art than science. The main iterator used when joining tables is a Loop. Nested and Merge loops are 2 of the most common. A plan can even have a loop without joining tables when a Seek needs a Lookup to find additional columns. This art of reading execution plan loops can help with performance tuning and debugging T-SQL. Once over the hump of reading a plan, going from beginner to intermediate is simple.

尽管阅读执行是技术性的，但它更是一门艺术而不是科学。连接表时使用的主要迭代器是循环。嵌套循环和合并循环是最常见的两种。当搜索需要查找以查找其他列时，计划甚至可以具有不连接表的循环。读取执行计划循环的这种技巧可以帮助性能调整和调试T-SQL。一旦阅读了计划书，从初学者到中级就很简单。

The first loop to look at is the Nested SQL Loop. Figure 1 is a Nested Loop from the INNER JOIN of tables SalesOrderHeader and Customer in the Adventure Works database.

要看的第一个循环是嵌套SQL循环。图1是Adventure Works数据库中SalesOrderHeader和Customer表的INNER JOIN的嵌套循环。

The actual T-SQL is in the Code 1 example below. This example shows a Clustered Index Seek finding one row in the Customer table from the WHERE clause CustomerID = 11091.

实际的T-SQL在下面的代码1示例中。此示例显示了聚集索引查找从WHERE子句CustomerID = 11091在Customer表中找到一行。

SELECT cust.CustomerID, soh.SalesOrderID
	FROM Sales.Customer cust
		INNER JOIN Sales.SalesOrderHeader soh
			ON soh.CustomerID = cust.CustomerID
	WHERE cust.CustomerID = 11091

The WHERE clause in the T-SQL looks for key of the customer table. The Clustered Index Seek in Figure 1 returns one row for the customer in the PK_Customer_CustomerID index. This is the Primary Key (and clustered index) of the Sales.Customer table. Figure 2 shows the detail properties of the Seek Iterator. These properties include Cost, Rows, descriptions and many others that are helpful. The cost is separated into I/O, CPU, Subtree and Operator.

T-SQL中的WHERE子句查找客户表的键。图1中的“聚集索引搜索”在PK_Customer_CustomerID索引中为客户返回一行。这是Sales.Customer表的主键（和聚集索引）。图2显示了Seek迭代器的详细信息属性。这些属性包括“成本”，“行”，“描述”以及许多其他有用的属性。成本分为I / O，CPU，子树和运算符。