【黑马程序员西安中心】干货 | 关于SQL执行计划的“阅读理解”，你能得几分？

有小伙伴希望出一篇关于“SQL执行计划”的干货，小编本着好人的心态，所以给大家分享这篇干货。

 

正文开始

 

【T-SQL基础】01.单表查询-几道sql查询题

【T-SQL基础】02.联接查询

【T-SQL基础】03.子查询

【T-SQL基础】04.表表达式-上篇

【T-SQL基础】04.表表达式-下篇

【T-SQL基础】05.集合运算

【T-SQL基础】06.透视、逆透视、分组集

【T-SQL基础】07.数据修改

【T-SQL基础】08.30分钟全面解析-SQL事务+隔离级别+阻塞+死锁

【T-SQL基础】09.可编程对象

 

SQL进阶

【SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子

【SQL进阶】02.理解SQL查询的底层原理

【SQL进阶】03.执行计划之旅1 - 初探 （最新）

 

SQL性能调优

【SQL性能调优】01.TempDB的使用和性能问题

 

 

一、为什么需要执行计划？

 

（1）帮助分析

 

当我们想要去分析SQL语句存在很慢时，需要有一个分析工具帮助我们分析SQL语句中哪些地方存在性能问题，而这个分析工具就是执行计划，看懂执行计划就能知道哪些地方有性能问题，然后结合自己已有的SQL知识分析为什么这些地方有性能问题，进而尝试提出解决方案，并测试自己的方案是否能提高性能，以及方案是否合理。

 

（2）获取其他信息

a.哪些索引被用在查询中

b.数据是怎样关联起来的

c.数据是怎样检索的

d.为什么SQL Server没有使用这些索引

e.SQL语句的执行顺序

 

 

二、什么是执行计划？

 

SQL语句执行之前，需要有一个执行的方案，而这个方案是由查询优化器（查询分析器）产生的，并且是高效的、开销最小的方案，这就是执行计划。

 

 

三、如何显示执行计划？

 

执行计划有三种格式：图形化执行计划，文本化执行计划，XML格式的执行计划。

 

（1）图形化执行计划

 

优点：可视性好。

 

a.估计的执行计划

可以通过鼠标点击图标显示估计的执行计划或者通过快捷键Ctrl+L显示估计执行计划。预估执行计划不会真正执行，只是预估出来的执行计划。

 

b.实际的执行计划

单击实际的执行计划图标，该图标处于选中状态，然后执行SQL语句，将会显示实际执行的执行计划。

 

（2）文本化执行计划

 

 

用独立的行来代表每一个迭代器。使用竖线（符号“|”）来代表查询树中迭代器之间的父子关系。数据都是从子迭代器流向父迭代器。

 

优点：和图形计划比较，文本执行计划更容易保存、处理、搜索和比较。

 

--显示完整的预估执行计划信息
SET SHOWPLAN_TEXT ON
GO

 

--显示预估执行计划的有限信息，可以用osql.exe等工具分析
SET SHOWPLAN_ALL ON
GO

 

--显示完整的实际执行计划信息
SET STATISTICS PROFILE ON
GO

 

总结：

 

 

（3）XML执行计划

优点：三种执行计划中最详细的。图形执行计划可以保存为扩展名为.sqlplan的XML格式的计划文件，打开此文件将会以图形化的执行计划展示。

 

 

--显示预估执行计划
SET SHOWPLAN_XML ON
GO

 

--显示实际计划的XML格式数据
SET STATISTICS XML ON
GO

 

 总结

 

四、如何分析执行计划？

下面分析三种情况的执行计划：

 

1、堆表

2、聚集索引

3、非聚集索引

 

结构	扫描	查找	书签查找
堆表	表扫描	没有这种情况	RID 查找
聚集索引	聚集索引扫描	聚集索引查找	没有这种情况
非聚集索引	如果用到了索引，则是索引扫描	索引查找	Key 查找

 

关于表扫描的那些事：

• 没有索引的表称作堆表，查找匹配行用的是表扫描。

• 如果出现表扫描操作，则证明这个表上一定没有聚集索引。

   

关于索引查找的那些事：

假设[列1]上有一个单列索引，可以使用这个索引查找下面这些谓词：

  1、[列1] = 1.23

  2、[列1] > 1.23

  3、[列1] BETWEEN 1 AND 100

  4、[列1] LIKE 'abc%'

  5、[列1] IN (1,3,7,10)

 

不能使用这个索引对下列这些谓词进行查找：

  1、ABS[列1] = 1

  2、[列1] + 10 > 12

  3、[列1] LIKE '%abc'

 

关于非聚集索引的那些事：

1、如果只有非聚集索引时，非聚集索引不包含查询列时，则SQL查询优化器选择非聚集索引扫描。

 

2、只有非聚集索引时，非聚集索引不包含过滤条件列时，则选择表扫描。

 

3、非聚集索引具有独立于数据行的结构。 非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。

 

4、从非聚集索引中的索引行指向数据行的指针称为行定位器。 行定位器的结构取决于数据页是存储在堆中还是聚集表中。 对于堆，行定位器是指向行的指针。 对于聚集表，行定位器是聚集索引键。

 

5、您可以向非聚集索引的叶级添加非键列（包含列）以跳过现有的索引键限制（900 字节和 16 键列），并执行完整范围内的索引查询。

 

关于聚集索引的那些事：

1、如果表上有聚集索引，则扫描称作聚集索引扫描，查找称作聚集索引查找。

 

2、聚集索引扫描和表扫描的性能没多大差异。

 

3、聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。

 

4、索引定义中包含聚集索引列。

 

5、每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

 

6、只有当表包含聚集索引时，表中的数据行才按排序顺序存储。 如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

 

7、加了聚集索引不一定能提高性能，有些情况下，性能可能不如表扫描。

 

8、聚集索引就是表本身。表有多少行和多少列，聚集索引就有多少行和和多少列。

 

9、单表查询中，过滤条件中有聚集索引列，且能用这个索引查找过滤条件中的谓词，则是聚集索引查找，过滤条件中没有聚集索引列则是聚集索引扫描。

 

（1）没有索引的情况

创建myOrder表

 

USE [Test]

GO

CREATE TABLE [dbo].[myOrder](

   [id] [int] NOT NULL,

   [customer] [nvarchar](100) NOT NULL

) ON [PRIMARY]

GO

 

myOrder只有两列id，customer，这两列上面都没有索引。

 

SELECT [id]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] = 'ddd'

 

下面是执行计划：

 

 

customer列上面没有索引，SQL Server需要读取myOrder表的每一行来判断customer='ddd',如果结果为true,则返回此行。

 

查询的示例图如下，customer=ddd 存在三条记录。

 

注意：

1、扫描及查找是SQL Server用来从表和索引中读取数据的迭代器；

2、扫描用来处理整个表或索引的全部分支；

3、查找是在谓词基础上有效返回索引中一个或多个范围中的行。

 

（2）有非聚集索引的情况

在id上创建非聚集索引

 

USE [Test]

GO

--删除索引dbo.myOrder.ID_NON_INDEX

DROP INDEX dbo.myOrder.ID_NON_INDEX


CREATE NONCLUSTERED INDEX [ID_NON_INDEX] ON [dbo].[myOrder]

(

   [id] 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

 

1、查询条件的列上没有非聚集索引，查询列上没有非聚集索引->表扫描

 

--id列上有索引,customer列上没有索引,查询条件中用的是customer='ddd'进行过滤。

--由于customer列上没有索引，所以需要进行表扫描来找到符合customer='ddd'的行。

SELECT [id]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] = 'ddd'


SELECT [customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] = 'ddd'
  

SELECT [id],[customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] = 'ddd'


SELECT [id]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2 AND [customer] ='ddd'


SELECT [id],[customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2 AND [customer] ='ddd'

 

 

2、查询条件的列上有非聚集索引，查询列上没有非聚集索引->表扫描

 

--id列上有索引,customer列上没有索引,查询条件中用的是id=2进行过滤。

--SELECT查询需要返回customer列，由于customer列上没有索引，且索引[ID_NON_INDEX]不包含customer列，

--即使用非聚集索引扫描找到了符合过滤条件id=2的索引分支，但是只能在该索引分支上面拿到id列的值，因为该索引分支只包含了id列，其他列的值拿不到。

--所以还是需要进行表扫描来找到符合条件的行,然后获取该行的customer列的值。

--这里有个疑问：为什么找到索引分支后，不能继续找到对应的行，然后拿到这行的customer列？？
 

 

SELECT [customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2



SELECT [id],[customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2

 

 

 

3、查询条件的列上有非聚集索引，查询列上有非聚集索引->索引查找

 

--id列上有索引,customer列上没有索引,查询条件中用的是id=2进行过滤。

--SELECT查询需要返回id列，使用非聚集索引扫描找到了符合过滤条件id=2的索引分支，在找到的索引分支上拿到id列的值。

SELECT [id]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2

 

 

 

（3）有聚集索引的情况

1、查询条件的列上没有聚集索引->聚集索引扫描

 

--

--id列上有聚集索引,customer列上没有索引,查询条件中用的是customer='ddd'进行过滤。

--由于customer列上没有索引，所以需要进行扫描来找到符合customer='ddd'的行。

--只要有聚集索引，则扫描就是聚集索引扫描。聚集索引和表扫描的性能基本上一样。

SELECT [id]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] = 'ddd'

  

SELECT [customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] = 'ddd'

  

SELECT [id],[customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] = 'ddd'

 

　　

 

2、查询条件的列上有聚集索引->聚集索引查找

 

--id列上有索引,customer列上没有索引,查询条件中用的是id=2进行过滤。

--用聚集索引查找到了id=2的行，由于表中的行就是按照id列来排序的，所以找到了这一行，

--也就能找到这一行的所有列，所以能够拿到customer列。所以是聚集索引扫描。

SELECT [customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2

  

SELECT [id]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2

  

SELECT [id],[customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2

 

　　

 

3、查询条件中，有一列有聚集索引，另一列没有聚集索引->聚集索引查找

 

--id列上有索引,customer列上没有索引,查询条件中用的是[id] = 2,[customer] ='ddd'进行过滤。

--当过滤条件中有一个可以根据聚集索引来查找时,先用聚集索引来找到匹配的行（[id] = 2）,然后再在过滤出来的行中筛选处符合[customer] ='ddd'的行。

--所以是聚集索引查找。

--疑问：为什么第二步筛选操作在执行计划图中没有体现？？这个地方我想到的是拿到id=2的匹配行后，直接舍弃掉不符合条件[customer] ='ddd'的行，这个舍弃动作就没有直接体现出来。

 SELECT [id]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2 AND [customer] ='ddd'

  

 SELECT [id],[customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [id] = 2 AND [customer] ='ddd'

  

 SELECT [id],[customer]

 FROM [Test].[dbo].[myOrder]

 WHERE [customer] ='3333' AND [id] = 2

作者： Jackson0714 
来源：http://www.cnblogs.com/jackson0714/