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

听到大牛们说执行计划,总是很惶恐,是对知识的缺乏的惶恐,所以必须得学习执行计划,以减少对这一块知识的惶恐,下面是对执行计划的第一讲-理解执行计划。

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

(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 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

->>【T-SQL】系列文章全文目录(2017-06-26更新)

作  者: Jackson0714 出  处:http://www.cnblogs.com/jackson0714/ 关于作者:专注于微软平台的项目开发。如有问题或建议,请多多赐教! 版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。 特此声明:所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误,共同进步。或者直接私信我 声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是作者坚持原创和持续写作的最大动力!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

一次非常有意思的SQL优化经历

发现没有用到索引,type全是ALL,那么首先想到的就是建立一个索引,建立索引的字段当然是在where条件的字段。

601
来自专栏python3

mysql-索引

数据库中专门用于帮助用户快速查找数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置吗,然后直接获取

862
来自专栏后端技术探索

MySQL中的两种临时表 外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭。这...

750
来自专栏Linyb极客之路

MySql查询性能优化

在访问数据库时,应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源,并增加网络开销。 例如在处理分页时,应该使用LIMIT限制My...

1494
来自专栏Linyb极客之路

SQL优化指南

slow_launch_time:表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

962
来自专栏极客慕白的成长之路

SQL复杂查询语句

 进行多表连接查询,掌握多表连接查询的连接条件或连接谓词,理解内连接、左连接和右连接的含义并熟练操作。

901
来自专栏java一日一条

浅谈SQL语句的执行过程

写在前面的话:有时不理解SQL语句各个部分执行顺序,导致理解上出现偏差,或者是书写SQL语句时随心所欲,所以有必要了解一下sql语句的执行顺序。可以有时间自己写...

672
来自专栏PHP在线

性能调优之MYSQL高并发优化

一、数据库结构的设计 表的设计具体注意的问题: 1、数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询...

4367
来自专栏Java进阶干货

MySQL命令,一篇文章替你全部搞定

MySQL的基本操作可以包括两个方面:MySQL常用语句如高频率使用的增删改查(CRUD)语句和MySQL高级功能,如存储过程,触发器,事务处理等。而这两个方面...

1872
来自专栏Java技术

为什么你创建的数据库索引没有生效?

几乎所有的小伙伴都可以随口说几句关于创建索引的优缺点,也知道什么时候创建索引能够提高我们的查询性能,什么时候索引会更新,但是你有没有注意到,即使你设置了索引,有...

641

扫码关注云+社区