SQL语句的调优
刚开始用SQL Server的时候，我没有用显示执行计划来对查询进行分析。我曾经一直认为我递交的SQL查询都是最优的，而忽略了查询性能究竟如何，从而对“执行计划”重视不够。在我职业初期，我只要能获取数据就很开心，而不去考虑数据是如何返回的，“执行计划”对我的查询作了什么工作。我以为SQL Server会自己去处理查询的性能问题的。作为一个刚进入IT行业或者刚学到新技术的软件工程师，在编写代码前不太可能有时间去学习其实必须掌握的知识。也许这是因为IT行业竞争太激烈的缘故。
随着时间的流逝，数据库容量慢慢变大了。终于某天，客户对应用系统的查询性能感到不满意了。他面带怒容来找我，抱怨由于查询太慢，使得他需要花更多的时间来处理公务。最初，我建议客户升级其系统资源，例如作为临时解决方案，增加硬盘容量。虽然硬盘价格现在很便宜了，但是客户还是要求我提供一个永久性的解决方案，检查和好好调试查询语句，来替代那种无休止地升级资源的临时方案。因为客户的满意度对IT行业来说是十分重要的，因此我不得不考虑他的个人建议。我答应他一定会检查和调整我的代码。
如何入手呢?
在刚进入IT行业时，我知道SQL Server的基础只是。说实话，向客户承诺检查系统的时候，我还没有一点入手的头绪。不过我相信我可以通过GOOGL和BOL来获取相应的信息。
我阅读了一些关于SQL Server的书籍，BOL，以及在网上搜索的信息。于是我知道了“显示执行计划”的概念。可以在查询管理器中将该选项的开关设置为ON。“显示执行计划”是一个图形化工具，可以帮助开发者和DBA分析，优化查询，从而改善性能。
“显示执行计划”中不同的任务具有不同的图标。本文中我主要对“Table Scan”、“Index Scan”、“Index Seek”、“Cluster Index Scan”以及“Clustered Index Seek”感兴趣。也许在以后，可以对别的任务进行另外介绍。
时间以F1方程式的速度开始流逝，我觉得该是我全面理解“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”、和“Clustered Index Seek”如何工作的时候了。
我准备开始分析并优化我的查询。在分析之前，我想到了一些问题。
- MS-SQL Server什么时候使用"Table Scan"?
- MS-SQL Server什么时候使用"Index Scan"?
- MS-SQL Server什么时候使用"Index Seek"?
- MS-SQL Server什么时候使用"Clustered Index Scan"?
- MS-SQL Server什么时候使用"Clustered Index Seek"?
我主要关注SQL Server是根据什么来使用“执行计划”分析查询的。在经过一段时间学习后，我了解了一些相关知识。这些知识应该对开发和DBA新手有帮助。于是我决定写这篇文章，共享我的知识以帮助别人来理解“执行计划”。
如果你喜欢，可以慢慢读完，也可以在SQL Server上，模拟我下面做的实验。
开始入手
为了解释“显示执行计划”中的“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”，先创建新表，并添加一些示例数据进去。下面是创建新表的脚本：
|
Create Table PerformanceIssue ( PRID UniqueIdentifier NOT NULL, PRCode Int NOT NULL, PRDesc Varchar (100) NOT NULL ) ON [PRIMARY] |
表创建后需要添加一些数据。使用下面的脚本添加100,000条记录进去。脚本执行时间可能比较长，请耐心等待其执行完毕。
|
Declare @Loop Int Declare @PRID UniqueIdentifier Declare @ PRDesc Varchar (100) Set @Loop = 1 Set @ PRDesc = '' WHILE @Loop <= 100000 BEGIN Set @PRID = NewID() Set @PRDesc = ' PerformanceIssue - ' + Convert( Varchar(10),@Loop ) Insert Into PerformanceIssue Values (@PRID, @Loop, @PRDesc) Set @Loop = @Loop + 1 END |
脚本成功执行后，数据就添加进去了。
用下面语句来看一下表的内容：
|
Select PRID, PRCode, PRDesc From PerformanceIssue GO |
由于记录较长，因此这里就不列出查询结果了。
正如我前面讲到，我想解释何时会有“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”。上述哪个会改善性能呢？
当SQL Server返回数据时，我们想知道SQL Server采取何种扫描机制来协助获取数据。首先看一下“Table Scan”。我们想了解什么时候“Table Scan”会产生。
选择“显示执行计划”或者使用热键“Alt + Q”来激活“显示执行计划”，当然也可以用快捷键“Ctrl+K”。
看一下执行下面查询后的“执行计划”结果。
|
Select PRID, PRCode, PRDesc From PerformanceIssue GO |
上面的“执行计划”中，SQL Server用到了“Table Scan”。我问自己为什么会有“Table Scan”，SQL Server是根据什么来使用该方法的。难道是因为我想获取所有100,000条记录吗？于是我换了一个角度进行思考，如果来避免查询中出现“Table Scan”呢？此时我对SQL Server的扫描机制还不是很清楚，那么该如何优化查询呢？下面的SELECT查询中仅选择两列：[PRID, PRCode]。
|
Select PRID, PRCode From PerformanceIssue GO |
查询执行后，执行计划和第一个查询一样。于是将查询改变为只检索一个字段 [PRID]。
|
Select PRID From PerformanceIssue GO |
查询执行后，执行计划仍然和第一个查询的相同。对“Estimated row size”属性不需要太大关注。意思我立刻决定只获取一条记录，看看执行计划会如何。查询语句如下：
|
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine GO |
执行完成后，执行计划显示：
查询仍然使用了“Table Scan”方法来显示数据。
那么，我需要想其它办法来避免“Table Scan”。首先我想到应该给表加上索引。于是我在PRID字段上创建非聚集索引。添加了索引后是否就能避免“Table Scan”？下面我们开始讨论关于“Index Scan”和“Index Seek”的主题。
Index Scan 和 Index Seek
首先在PRID字段上创建非聚集索引。
|
CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID ON PerformanceIssue (PRID) GO |
本文假定读者已经知道非聚集索引如何工作的知识。了解非聚集索引更详细的信息，请阅读BOL相关主题，也可参看 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我们详细讲述“Index Scan”是如何工作的。
执行下面语句并查看执行计划的结果。
|
Select PRID, PRCode, PRDesc From PerformanceIssue GO |
奇怪了，“Table Scan”仍然用到了。为什么SQL Server没有用到那个非聚集索引？于是继续优化查询语句，选择检索两个字段 [PRID, PRCode] 。
|
Select PRID, PRCode From PerformanceIssue GO |
执行结果是和上一个查询结果一摸一样。于是修改查询为只检索一个字段 [PRID] 。
|
Select PRID From PerformanceIssue GO |
执行计划结果如下：
“Index Scan”在查询中被用到了，这很好。很自然，接下来的问题就是“Index Scan”什么时候会被用到。字段PRID上有一个索引，查询语句中选中的字段为PRID。执行查询的时候，SQL Server扫描索引页，因此用到了“Index Scan”方法。前面的查询中选择了有索引的和没有索引的字段，SQL Server无法使用“Index Scan”。当查询中只选择有索引的字段时，SQL Server就使用了“Index Scan”。我不清楚SQL Server底层到底是如何判断的，不过通过这些试验，我认为当查询中只选择有索引的字段时，SQL Server就使用“Index Scan”方法。
下面看“Index Seek”方法何时产生。当我看到“Seek”这个词时，第一反应就是条件查询这个主意。
我尝试三种不同的带WHERE语法的查询语句，以找出那种会用“Index Seek”。第一种语句如下：
|
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8 GO |
结果显示，执行计划使用了“Table Scan”。
第二种语句如下：
|
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRDesc = ' PerformanceIssue - 8' GO |
执行计划仍然使用“Table Scan”方法。
第三种查询语句如下：
|
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine GO |
查询用到了“Index Seek”和“Bookmark Lookup”方法。用到“Index Seek”是因为WHERE后面使用带索引的字段PRID来进行过滤。“Bookmark Lookup”方法被用到是因为查询中选择了没有索引的字段。如果去掉这两个没有索引的字段，那么“Bookmark Lookup”方法就可以去掉。当然如果只返回PRID字段，那么该查询就没什么意义了，因为WHERE语句后面已经给出PRID具体取值了。
我认为“Index Seek”在性能改善上比“Index Scan”和“Table Scan”要好，这主要表现在下面几个方面：
- “Index Seek”不需要对表和索引页进行扫描；而“Table Scan”和“Index Scan”需要。
- “Index Seek”利用“WHERE”来过滤获取的数据，这样比用“Index Scan”和“Table Scan”快很多。
当我完成这些测试后，我同事问我一个很有意思的问题：SQL Server什么时候使用“Clustered Index Scan”和“Clustered Index Seek”？下面对“Clustered Index Scan”和“Clustered Index Seek”进行实验。
我决定在PRCode上建一个聚集索引来测试“Clustered Index Scan”和“Clustered Index Seek”。
Clustered Index Scan & Clustered Index Seek
下面的脚本删除PRID字段上的索引，并在PRCode字段上创建聚集索引。
|
Drop Index PerformanceIssue.UNC_PRID GO CREATE UNIQUE CLUSTERED INDEX UC_PRCode ON PerformanceIssue( PRCode) GO ------------- Clustered index has been created successfully. Index has been created. |
关于聚集索引的基础知识请查阅联机帮助的相关主题或者 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我们将重点放在“Clustered Index Scan”和“Clustered Index Seek”如何被使用上。
执行下面查询语句：
|
Select PRID, PRCode, PRDesc From PerformanceIssue GO |
查询执行后，可以看到执行计划中用到了“Clustered Index Scan”。
下面用三种不同的WHERE方式来试验何时SQL Server会用到“Clustered Index Seek”。第一种形式如下：
|
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRDesc = ' PerformanceIssue - 8' GO |
查询执行后，可以看到执行计划中用到了“Clustered Index Scan”。
第二种形式如下：
|
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine GO |
查询执行后，发现执行计划中用到的仍然是“Clustered Index Scan”。
第三种形式：
|
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8 GO |
这次执行计划用到了“Clustered Index Seek”。
当在WHERE后用到PRCode字段的时候，“Clustered Index Seek”被用到。执行计划对聚集索引表检索的时候，因为在选取的字段中，包括没有索引的字段，所以不用用到“Bookmark Lookup”方法。
我个人认为，从改善性能角度考虑，“Clustered Index Seek”比“Clustered Index Scan”和“Index Seek”要好。
- “Clustered Index Seek”不需要扫描整个聚集索引页。
- 和“Index Scan”相比，对于检索选择的字段包含那些没有索引的字段时，“Clustered Index Seek”不会有“Bookmark Lookup”方法出现。
通过这些试验，我对执行计划的应用积累了实际经验。我知道哪种扫描机制可以提高性能，从而是的客户满意
