• sql server 数据库优化--显示执行计划


     

    刚开始用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”要好,这主要表现在下面几个方面:

    1. “Index Seek”不需要对表和索引页进行扫描;而“Table Scan”和“Index Scan”需要。
    2. “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”要好。

    1. “Clustered Index Seek”不需要扫描整个聚集索引页。
    2. 和“Index Scan”相比,对于检索选择的字段包含那些没有索引的字段时,“Clustered Index Seek”不会有“Bookmark Lookup”方法出现。

    通过这些试验,我对执行计划的应用积累了实际经验。我知道哪种扫描机制可以提高性能,从而是的客户满意。

  • 相关阅读:
    习题4.7利用vector实现数据复制
    习题4.18
    4.8编写一小段程序实现两vector是否相等的比较
    关于野指针
    学习c++的50条忠告
    c++头文件
    习题4.14
    容器和迭代器
    Android上C++对象的自动回收机制分析
    Windows下载Android源码
  • 原文地址:https://www.cnblogs.com/firstdream/p/7274165.html
Copyright © 2020-2023  润新知