首页 > 代码库 > 【SQL进阶】03.执行计划之旅1 - 初探
【SQL进阶】03.执行计划之旅1 - 初探
听到大牛们说执行计划，总是很惶恐，是对知识的缺乏的惶恐，所以必须得学习执行计划，以减少对这一块知识的惶恐，下面是对执行计划的第一讲-理解执行计划。
本系列【T-SQL】主要是针对T-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事务+隔离级别+阻塞+死锁（推荐 +165）
【T-SQL基础】09.可编程对象
SQL进阶
【SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子
【SQL进阶】02.理解SQL查询的底层原理（推荐 +5）
【SQL进阶】03.执行计划之旅1 - 初探 （最新）
SQL性能调优
【SQL性能调优】01.TempDB的使用和性能问题
持续更新......欢迎关注我！
一、为什么需要执行计划？
（1）帮助分析
当我们想要去分析SQL语句存在很慢时，需要有一个分析工具帮助我们分析SQL语句中哪些地方存在性能问题，而这个分析工具就是执行计划，看懂执行计划就能知道哪些地方有性能问题，然后结合自己已有的SQL知识分析为什么这些地方有性能问题，进而尝试提出解决方案，并测试自己的方案是否能提高性能，以及方案是否合理。
（2）获取其他信息
a.哪些索引被用在查询中
b.数据是怎样关联起来的
c.数据是怎样检索的
d.为什么SQL Server没有使用这些索引
e.SQL语句的执行顺序
二、什么是执行计划？
SQL语句执行之前，需要有一个执行的方案，而这个方案是由查询优化器（查询分析器）产生的，并且是高效的、开销最小的方案，这就是执行计划。不知道查询优化器的可以看我写之前写的一篇博客：
【T-SQL进阶】02.理解SQL查询的底层原理
三、如何显示执行计划？
执行计划有三种格式：图形化执行计划，文本化执行计划，XML格式的执行计划。
（1）图形化执行计划
优点：可视性好。
a.估计的执行计划
可以通过鼠标点击图标显示估计的执行计划或者通过快捷键Ctrl+L显示估计执行计划。预估执行计划不会真正执行，只是预估出来的执行计划。
b.实际的执行计划
单击实际的执行计划图标，该图标处于选中状态，然后执行SQL语句，将会显示实际执行的执行计划。
（2）文本化执行计划
用独立的行来代表每一个迭代器。使用竖线（符号“|”）来代表查询树中迭代器之间的父子关系。数据都是从子迭代器流向父迭代器。
优点：和图形计划比较，文本执行计划更容易保存、处理、搜索和比较。
--显示完整的预估执行计划信息SET SHOWPLAN_TEXT ONGO
--显示预估执行计划的有限信息，可以用osql.exe等工具分析SET SHOWPLAN_ALL ONGO
--显示完整的实际执行计划信息SET STATISTICS PROFILE ONGO
总结：
（3）XML执行计划
优点：三种执行计划中最详细的。图形执行计划可以保存为扩展名为.sqlplan的XML格式的计划文件，打开此文件将会以图形化的执行计划展示。
--显示预估执行计划SET SHOWPLAN_XML ONGO
--显示实际计划的XML格式数据SET STATISTICS XML ONGO
总结
四、如何分析执行计划？
下面分析三种情况的执行计划：
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‘
关于非聚集索引的那些事：
- 如果只有非聚集索引时，非聚集索引不包含查询列时，则SQL查询优化器选择非聚集索引扫描。
- 只有非聚集索引时，非聚集索引不包含过滤条件列时，则选择表扫描。
- 非聚集索引具有独立于数据行的结构。 非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。
- 从非聚集索引中的索引行指向数据行的指针称为行定位器。 行定位器的结构取决于数据页是存储在堆中还是聚集表中。 对于堆，行定位器是指向行的指针。 对于聚集表，行定位器是聚集索引键。
- 您可以向非聚集索引的叶级添加非键列（包含列）以跳过现有的索引键限制（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_INDEXDROP 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列？？
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
【SQL进阶】03.执行计划之旅1 - 初探