sql子句的执行顺序_SQL Server查询执行计划– WHERE子句的示例

sql子句的执行顺序

previous part of this article, we explained how indexes affect SQL Server query execution performance and plans, and provided some basic examples. In this part, we will continue with examples that show how indexes affect query execution plans and costs. 前面 ，我们解释了索引如何影响SQL Server查询的执行性能和计划，并提供了一些基本示例。 在这一部分中，我们将继续使用示例，这些示例显示索引如何影响查询执行计划和成本。

在聚集索引上使用WHERE进行SELECT (SELECT with WHERE on a clustered index)

To narrow down the number of rows returned by the query, we will add the WHERE clause with a condition for the table clustered index.

为了缩小查询返回的行数，我们将在WHERE子句中添加表聚集索引的条件。

 
SELECT *
FROM Person.Address
where AddressID = 7
    

The SQL Server query execution plan now contains the Clustered index seek.

现在，SQL Server查询执行计划包含聚簇索引查找。

As its tooltip explains, the database engine scans a particular range of rows from the clustered index. It seeks for the clustered index, not scans for it, therefore we can expect lower query cost. The engine searches for the specific key values and can quickly find them, as the clustered index also sorts the data so the search is more efficient. Finding the right record in such ordered tables is quick and resource inexpensive, which is clearly shown by the cost numbers.

如其工具提示所述，数据库引擎从聚簇索引中扫描特定范围的行。 它查找聚簇索引，而不是扫描聚簇索引，因此我们可以预期更低的查询成本。 引擎搜索特定的键值并可以快速找到它们，因为聚簇索引还对数据进行排序，因此搜索效率更高。 在这样的有序表中找到正确的记录既快捷又节省资源，成本数字清楚地表明了这一点。

If you now compare the estimated operator, I/O, and CPU costs with the costs for the same query without WHERE, you can notice that the values when the WHERE clause is used are smaller by two orders of magnitude

如果现在将估计的运算符，I / O和CPU成本与不带WHERE的同一查询的成本进行比较，您会注意到使用WHERE子句时的值要小两个数量级。

在非聚集索引上使用WHERE进行SELECT (SELECT with WHERE on a nonclustered index)

A similar example is to use the WHERE clause with the condition on the column other than the clustered index, for example on a unique nonclustered index.

一个类似的示例是将WHERE子句与聚集索引以外的列上的条件一起使用，例如，在唯一的非聚集索引上。

 
CREATE UNIQUE NONCLUSTERED INDEX 
[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]
(
        [AddressLine1] ASC,
        [AddressLine2] ASC,
        [City] ASC,
        [StateProvinceID] ASC,
        [PostalCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
SELECT *
FROM    Person.Address
where City = 'New York'
    

Now, there are both the Nonclustered Index Scan and Key Lookup. Keep in mind that the query execution plans are read right to left, top to bottom. Therefore, the first operator here is the NonClustered Index Scan.

现在，既有非聚集索引扫描又有键查找 。 请记住，查询执行计划是从右到左，从上到下阅读的。 因此，这里的第一个运算符是NonClustered Index Scan 。

The query execution plans displays the “Missing index (Impact 89.952): CREATE NONCLUSTERED INDEX [<Name Of Missing Index, sysname,>] ON [Person].Address]([City]) message

查询执行计划显示“缺少索引（影响89.952）：CREATE NONCLUSTERED INDEX [<缺少索引的名称，系统名称，>] ON [Person] .Address]（[City]）消息

This is not an error, or warning, you should consider this message as an advice what you can do to improve the query execution performance by almost 90%. To see the recommended index, right-click the plan and select the Missing Index details option.

这不是错误或警告，您应该将此消息作为建议，可以做些什么来将查询执行性能提高近90％。 要查看建议的索引，请右键单击该计划，然后选择“ 缺少索引详细信息”选项。

The option returns the index details along with T-SQL for creating it.

该选项返回索引详细信息以及用于创建索引的T-SQL。

 
/*
Missing Index Details from SELECT Address4.sql - FUJITSU\SQL2012.AdventureWorks2012 (sa (56))
The Query Processor estimates that implementing the following index could improve the query cost by 89.952%.
*/
 
/*
USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [<name of missing index, sysname,>]
ON [Person].[Address] ([City])
 
GO
*/
    

All you have to do is enter the index name, remove the comments, and execute code

您要做的就是输入索引名称，删除注释并执行代码。

As you can see, the operators are the same, but there’s no warning anymore and the cost is distributed almost equally between the Index Seek and Key Lookup. While the Key Lookup cost stayed the same, the Index Seek cost is significantly reduced.

如您所见，运算符相同，但不再发出警告，并且代价在Index Seek和Key Lookup之间几乎平均分配。 尽管“ 关键查找”成本保持不变，但“ 索引寻找”成本却大大降低了。

However, it’s highly recommended not to create all indexes that are reported as missing by Query Optimizer in the query execution plans. Keep in mind that it’s only a recommendation for the specific plan and that it doesn’t mean that the whole system and workload would benefit from the index. As indexing has benefits as well as downsides, it’s necessary to determine whether this index will really improve overall performance. There are several factors you should consider when determining necessity of the index. The first one is how often the query is executed.

但是，强烈建议不要在查询执行计划中创建所有被Query Optimizer报告为丢失的索引。 请记住，这只是针对特定计划的建议，并不意味着整个系统和工作量都将从索引中受益。 由于索引既有好处也有缺点，因此有必要确定该索引是否会真正改善整体性能。 确定索引的必要性时，应考虑几个因素。 第一个是查询执行的频率。

The Index Scan output list shows only some of the table columns. These are the columns specified in the IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode index: AddressLine1, AddressLine2, City,StateProvinceID, and PostalCode. The clustered index and primary key column AddressID is always returned. The SELECT statement executed must return all table columns. The rest of the columns are read by the Key Lookup operator. The key value AddressID is used in the Key Lookup. As shown, the Key Lookup returns the rest of the Person.Address columns

索引扫描输出列表仅显示某些表列。 这些是在IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode索引中指定的列：AddressLine1，AddressLine2，City，StateProvinceID和PostalCode。 总是返回聚簇索引和主键列AddressID 。 执行的SELECT语句必须返回所有表列。 其余列由“ 关键字查找”运算符读取。 键值AddressID在“ 键查找”中使用 。 如图所示， Key Lookup返回其余的Person.Address列

A Key Lookup indicates that obtaining all records is done in two steps. To return all records in a single step, all needed columns should be returned by the Index Scan.

关键字查找指示获取所有记录的过程分两个步骤。 要在单个步骤中返回所有记录，所有必需的列应由Index Scan返回。

The simplest solution is to specify the list of columns returned by the Index Scan.

最简单的解决方案是指定“ 索引扫描”返回的列的列表。

 
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
  FROM Person.Address
  WHERE City = 'New York'
    

Whenever there is a Key Lookup operator, it’s followed by the Nested Loops which is actually a JOIN operator which combines the data returned by the Index Seek and Key Lookup operators. As shown in the tooltips, the Index Seek is performed only once (Number of executions = 1) and it returns 16 rows (Actual number of rows). The Key Lookup operator uses the 16 rows returned by the Index Seek and looks up for the records in the table. A lookup is performed for every row returned by the Index Seek, in this example 16 times, which is presented by the Key Lookup Number of executions.

每当有一个Key Lookup运算符时，它后面便是Nested Loops ，它实际上是一个JOIN运算符，它结合了Index Seek和Key Lookup运算符返回的数据。 如工具提示中所示， 索引查找仅执行一次（ 执行次数 = 1），并且返回16行（ 实际行数 ）。 关键字查找运算符使用索引查找返回的16行，并在表中查找记录。 对于索引查找所返回的每一行执行一次查找 ，在此示例中为16次，由执行的键查找数表示 。

For a small number of rows returned by Index Seek, this means a small number of executions by the Key Lookup. When the Key Lookup output is indexed, the time needed to perform a lookup is shorter, so this is a scenario with a small cost. In case the Index Seek provides all output columns, the Key Lookup is not needed as no additional columns should be retrieved, and the Nested Loops is not needed as there are no data sets to combine.

对于Index Seek返回的少量行，这意味着Key Lookup会执行少量执行。 索引关键字查找输出后，执行查找所需的时间会缩短，因此这是一种开销很小的方案。 如果“ 索引查找”提供了所有输出列，则不需要“ 键查找” ，因为不需要检索其他列，并且因为没有要组合的数据集，所以不需要“ 嵌套循环” 。

The SQL Server query execution plans can be complex and difficult to read and understand, as same code can be executed using different operators. As shown, the query execution plan structure depends on the query executed, table structure, indexing, and more. In this article, we focused on the table indexing and two basic query statements: SELECT and WHERE. We showed how a column list in the SELECT statement and WHERE condition on various columns in a table can significantly affect query execution and its performance.

SQL Server查询执行计划可能很复杂，难以阅读和理解，因为可以使用不同的运算符来执行相同的代码。 如图所示，查询执行计划结构取决于执行的查询，表结构，索引等。 在本文中，我们重点介绍表索引和两个基本查询语句：SELECT和WHERE。 我们展示了SELECT语句中的列列表以及表中各个列的WHERE条件如何显着影响查询的执行及其性能。

翻译自: https://www.sqlshack.com/sql-server-query-execution-plans-where-clause/

sql子句的执行顺序