关于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 |
表创建后需要添加一些数据。使用下面的脚本添加100,000条记录进去。脚本执行时间可能比较长,请耐心等待其执行完毕。
Declare @Loop Int |
脚本成功执行后,数据就添加进去了。
用下面语句来看一下表的内容:
Select PRID, PRCode, PRDesc |
由于记录较长,因此这里就不列出查询结果了。
正如我前面讲到,我想解释何时会有“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 |
上面的“执行计划”中,SQL Server用到了“Table Scan”。我问自己为什么会有“Table Scan”,SQL Server是根据什么来使用该方法的。难道是因为我想获取所有100,000条记录吗?于是我换了一个角度进行思考,如果来避免查询中出现“Table Scan”呢?此时我对SQL Server的扫描机制还不是很清楚,那么该如何优化查询呢?下面的SELECT查询中仅选择两列:[PRID, PRCode]。
Select PRID, PRCode |
查询执行后,执行计划和第一个查询一样。于是将查询改变为只检索一个字段 [PRID]。
Select PRID |
查询执行后,执行计划仍然和第一个查询的相同。对“Estimated row size”属性不需要太大关注。意思我立刻决定只获取一条记录,看看执行计划会如何。查询语句如下:
Select PRID, PRCode, PRDesc |
执行完成后,执行计划显示:
查询仍然使用了“Table Scan”方法来显示数据。
那么,我需要想其它办法来避免“Table Scan”。首先我想到应该给表加上索引。于是我在PRID字段上创建非聚集索引。添加了索引后是否就能避免“Table Scan”?下面我们开始讨论关于“Index Scan”和“Index Seek”的主题。
Index Scan 和 Index Seek
首先在PRID字段上创建非聚集索引。
CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID |
本文假定读者已经知道非聚集索引如何工作的知识。了解非聚集索引更详细的信息,请阅读BOL相关主题,也可参看 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我们详细讲述“Index Scan”是如何工作的。
执行下面语句并查看执行计划的结果。
Select PRID, PRCode, PRDesc |
奇怪了,“Table Scan”仍然用到了。为什么SQL Server没有用到那个非聚集索引?于是继续优化查询语句,选择检索两个字段 [PRID, PRCode] 。
Select PRID, PRCode From PerformanceIssue |
执行结果是和上一个查询结果一摸一样。于是修改查询为只检索一个字段 [PRID] 。
Select PRID |
执行计划结果如下:
“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 |
结果显示,执行计划使用了“Table Scan”。
第二种语句如下:
Select PRID, PRCode, PRDesc |
执行计划仍然使用“Table Scan”方法。
第三种查询语句如下:
Select PRID, PRCode, PRDesc |
查询用到了“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 |
关于聚集索引的基础知识请查阅联机帮助的相关主题或者 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我们将重点放在“Clustered Index Scan”和“Clustered Index Seek”如何被使用上。
执行下面查询语句:
Select PRID, PRCode, PRDesc |
查询执行后,可以看到执行计划中用到了“Clustered Index Scan”。
下面用三种不同的WHERE方式来试验何时SQL Server会用到“Clustered Index Seek”。第一种形式如下:
Select PRID, PRCode, PRDesc |
查询执行后,可以看到执行计划中用到了“Clustered Index Scan”。
第二种形式如下:
Select PRID, PRCode, PRDesc |
查询执行后,发现执行计划中用到的仍然是“Clustered Index Scan”。
第三种形式:
Select PRID, PRCode, PRDesc |
这次执行计划用到了“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”方法出现。
通过这些试验,我对执行计划的应用积累了实际经验。我知道哪种扫描机制可以提高性能,从而是的客户满意。