sql查询初学者指南
We have discussed how to created estimated execution plans and actual execution plans in various formats in my previous article SQL Server Query Execution Plan for beginners – Types and Options.
在我之前的文章“针对初学者SQL Server查询执行计划-类型和选项”中，我们讨论了如何创建各种格式的估计执行计划和实际执行计划。
In this article we will continue discussing the various execution plan operators related to clustered indexes, and what they do, when do they appear and what happens when they do.
在本文中，我们将继续讨论与聚簇索引相关的各种执行计划运算符，以及它们的作用，何时出现以及何时发生。
When you look at execution plans and start actually doing query optimizations, each of these operators will provide you with some indicator of how SQL server is running.
当您查看执行计划并开始实际进行查询优化时，每个这些运算符都会为您提供有关SQL Server运行方式的一些指示。
These operators need to be understood, fundamentally, from the contextual activity, when they are seen, whether they are good or bad, as it pertains to performance implications.
从本质上讲，需要从上下文活动中了解这些运算符，因为它们与性能影响有关，无论它们是好是坏。
If an operator occurs, we should be able to determine the action plan that we need to take so that these operators do not arise again or if they do arise we understand why.
如果有一个操作员出现，我们应该能够确定我们需要采取的行动计划，这样这些操作员就不会再出现了，或者如果他们确实出现了，我们就会明白为什么。
It is most critical to understanding that these particular recommendations have to be taken with a pinch of salt but can be used without much of harm to SQL server.
理解这些特别的建议必须非常谨慎，但是可以在不对SQL Server造成很大损害的情况下使用它们。
Let me start with a table scan.
让我从表格扫描开始。
表扫描(Table scan)
When:Table Scans occur when a table without a clustered index is accessed. Hence, there is no specific order in which SQL server goes about storing them, this particular heap is to be actually queried like in table scan.时间：访问没有聚集索引的表时，将进行表扫描。 因此，SQL Server没有存储它们的特定顺序，实际上就像在表扫描中一样查询此特定堆。Good or bad:For very small tables it does not sometimes make difference. But if it is for a larger number of rows, you will not be happy with the appearance of this operator.好坏：对于很小的桌子，有时没有什么区别。 但是，如果用于更多行，您将不满意该运算符的出现。Action item:Create a clustered index. Generally, a good practice is that if you have only one index on the table, it is better to be a clustered index.操作项目：创建聚簇索引。 通常，一种好的做法是，如果表上只有一个索引，那么最好是一个聚集索引。Now, let me show you table scan operator. We will create the following simple table depending on [SalesOrderDetail] table from [AdventureWorks] database using the following script:
现在，让我向您展示表扫描运算符。 我们将使用以下脚本根据[AdventureWorks]数据库中的[SalesOrderDetail]表创建以下简单表：
USE AdventureWorksGOSELECT *INTO [SQL_SHACK].[dbo].[MySalesOrderDetail] FROM [AdventureWorks].[Sales].[SalesOrderDetail]GO
Then, I will demonstrate something interesting.
然后，我将展示一些有趣的东西。
First, let us try toselect *from our sample table with including actual execution plan and statistics IO.
首先，让我们尝试从示例表中选择*，其中包括实际的执行计划和统计信息IO。
SET STATISTICS IO ONGOSELECT *FROM [SQL_SHACK].[dbo].[MySalesOrderDetail]
Here you can notice that we get the result of 121,317 rows with logical reads of 1,497.
在这里您可以注意到，我们得到121,317行的结果，逻辑读取为1,497。
Now, let us try to run the same query using a specific range of values:
现在，让我们尝试使用特定范围的值运行相同的查询：
SET STATISTICS IO ONGOSELECT *FROM [SQL_SHACK].[dbo].[MySalesOrderDetail]WHERE SalesOrderID = 60726 AND SalesOrderDetailID = 74616
The interesting thing, alluded to previously, is that when you don’t have a clustered index you will end up with table scan which means you will have the same logical reads no matter how big the result you are selecting is. So, it will be much better if you have created a clustered index. This will be further demonstrated in the next section.
以前提到过的有趣的事情是，当您没有聚集索引时，最终将进行表扫描，这意味着无论选择的结果多大，您都将具有相同的逻辑读取。 因此，如果您创建了聚集索引，那就更好了。 这将在下一部分中进一步说明。
聚簇索引扫描 (Clustered index scan)
When:Table with a clustered index is accessed时间：访问具有聚集索引的表 The table does not have non-clustered index 该表没有非聚集索引 The query cannot use the non-clustered index 查询不能使用非聚集索引Good or bad:If I had to make a decision whether it is a good or bad, it could be a bad. Unless a large number of rows, with many columns and rows, are retrieved from that particular table, a Clustered Index Scan, can degrade performance.是好是坏：如果我必须决定是好是坏，那可能是坏事。 除非从该特定表中检索到具有许多列和行的大量行，否则聚集索引扫描会降低性能。Action item:Evaluate clustered index keys操作项目：评估聚簇索引键聚集索引搜寻 (Clustered index seek)
When:A table with clustered index is being accessed and the B tree structure is able to narrow down, based on your clustered index, to get a limited set of rows from the table.何时：具有聚集索引的表正在访问，并且B树结构能够根据您的聚集索引缩小范围，以从表中获取有限的行集。Good or bad:This is the ideal condition. It is generally good to see the Clustered Index Seek好坏：这是理想的条件。 最好看到聚簇索引寻求Action item:Evaluate the possibility of using a non-clustered index, so that you gain the possibility of eliminating, if required, even the Clustered Index Seek.行动项目：评估使用非聚集索引的可能性，以便您有可能消除（如果需要）甚至聚集索引寻求。For the previously created table “MySalesOrderDetail”, we are going to create a clustered index primary key on [SalesOrderID] and [SalesOrderDetailID].
对于先前创建的表“ MySalesOrderDetail”，我们将在[SalesOrderID]和[SalesOrderDetailID]上创建聚簇索引主键。
ALTER TABLE [SQL_SHACK].[dbo].[MySalesOrderDetail]ADD CONSTRAINT [PK_MySalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED ([SalesOrderID] ASC,[SalesOrderDetailID] ASC)GONow, let us go ahead and do the same, select all, from the tableSET STATISTICS IO ONGOSELECT *FROM [SQL_SHACK].[dbo].[MySalesOrderDetail]
We can see that the query completes with 121,317 rows. Which is same as what we used to get before.
我们可以看到查询完成了121,317行。 与我们以前获得的相同。
The messages tab, ironically, is giving me 1,502 logical reads. Now, this is a little bit more when compared to what we used to get with 1,497. The 5 additional pages that you are actually getting are based on the B tree that had to be formed because the intermediate notes and the first IAM had to be built and that is exactly what 1,502 is.
具有讽刺意味的是，“消息”选项卡给了我1,502个逻辑读物。 现在，与我们以前在1,497上获得的价格相比，它要多一些。 您实际获得的另外5个页面基于必须形成的B树，因为必须构建中间注释和第一个IAM，而这恰好是1,502。
If I get to the execution plan here, you can see that unlike the previous time of Table Scan, you are getting Clustered Index Scan. This is a slight improvement that we have seen.
如果我在这里执行计划，您会发现与上一次表扫描不同，您将获得群集索引扫描。 我们已经看到了一点改进。
Now, let me get to the specific case where we went about doing value collection. In this particular case same as what we selected before
现在，让我进入进行价值收集的具体案例。 在这种情况下，与我们之前选择的相同
SET STATISTICS IO ONGOSELECT *FROM [SQL_SHACK].[dbo].[MySalesOrderDetail]WHERE SalesOrderID = 60726 AND SalesOrderDetailID = 74616
Here you get a single row and if I get to the messages tab I can see it shows three logical reads.
在这里，您只有一行，如果我转到消息选项卡，我可以看到它显示了三个逻辑读。
If I move to the execution plan, you can see the Clustered Index Scan which was in the previous case has been transformed into a Clustered Index Seek, hence SQL Server was able to narrow down, using the clustered index key, to obtain this particular value based on the where condition, which is in the seek predicate
如果我转到执行计划，则可以看到在以前的情况下，“聚簇索引扫描”已转换为“聚簇索引寻求”，因此SQL Server能够使用聚簇索引键来缩小范围以获得该特定值基于where条件，该条件位于seek谓词中
摘要(Summary)
Execution plans are a very important tool for optimizing query performance. Every DBA needs to know all operators that appear in the execution plan and decide whether it is good or bad and what to do if it is the latter. I tried to be simple in discussing the very basic details about the operators related only to clustered index. I hope this article has been informative for you.
执行计划是优化查询性能的非常重要的工具。 每个DBA都需要知道执行计划中出现的所有运算符，并确定它是好是坏，如果是后者，该怎么办。 我试图简单地讨论关于仅与聚簇索引有关的运算符的非常基本的细节。 希望本文对您有所帮助。
Previous article in this series:
本系列的上一篇文章：
SQL Server Query Execution Plans for beginners – Types and Options面向初学者SQL Server查询执行计划–类型和选项
翻译自: /sql-server-query-execution-plan-beginners-clustered-index-operators/
sql查询初学者指南
