SQL Server 性能调优 之运行计划(Execution Plan)调优
运行计划中的三种 Join 策略
SQL Server 存在三种 Join 策略:Hash Join,Merge Join,Nested Loop Join。
Hash Join:用来处理没有排过序/没有索引的数据,它在内存中把 Join 两边数据(的关联key)分别建立一个哈希表。比如有下面的查询语句,关联的两张表没有建立索引,运行计划将显示为Hash Join。
SELECT
sh.*
FROM
SalesOrdHeaderDemo AS sh
JOIN
SalesOrdDetailDemo AS sd
ON
sh.SalesOrderID=sd.SalesOrderID
GO
Merge Join:用来处理有索引的数据,它比Hash Join轻量化。我们为前面两张表的关联列建立索引,然后再次上面的查询,运行计划将变更为Merge Join
CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON SalesOrdHeaderDemo (SalesOrderID)
GO
CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)
GO
Nested Loop Join:在满足Merge Join的基础上,假设某一边的数据较少,那么SQL Server 会把数据较少的那个作为外部循环,还有一个作为内部循环来完毕Join处理。继续前面的样例为查询语句加上WHERE语句来降低 Join 一边的数据量,运行计划显示为Nested Loop Join。
SELECT
sh.*
FROM
SalesOrdHeaderDemo AS sh
JOIN
SalesOrdDetailDemo AS sd
ON
sh.SalesOrderID=sd.SalesOrderID
WHERE
sh.SalesOrderID=43659
运行计划中的(table/index scan)的改进
在很多场合我们须要在一张包括很多数据的表中提取出一小部分数据,此时应当避免Scan,由于扫描处理会遍历每一行,这是相当耗时耗力的。以下我们来看一个样例:
SELECT
sh.SalesOrderID
FROM
SalesOrdHeaderDemo AS sh
JOIN
SalesOrdDetailDemo AS sd
ON
sh.SalesOrderID=sd.SalesOrderID
WHERE
sh.OrderDate='2005-07-01 00:00:00.000'
GO
图中的红圈标出了table scan,而且运行计划也智能得建议建立索引。我们先尝试在SalesOrdHeader 表上建立一个索引:
CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_SalesOrderID ON SalesOrdHeaderDemo (SalesOrderID)
GO
然后再次运行同样的查询语句,运行计划变成下面的模样:
table scan 变为了 Index Scan,继续给还有一张表也加上索引:
CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)
GO
运行计划发生下面的变化:
尽管不能说 Scan 比 Seek 差,但绝大多数的场合(尤其是在很多数据中查找少量数据时)Seek 是更好的选择。举例来说假设你有一个上亿条数据的表,你要取当中的100条,那么你应当保证其採用 Seek,但假设你须要取出当中绝大多数(比方95%)的数据时,Scan 可能更好。(有较权威的文章给出了这个阀值为30%,即取出超过30%数据时 scan 更高效;反之则 Seek 更好)
另外你可能注意到两张表上都建立了索引但一张表在运行计划中表现为 Clustered index scan,而还有一张表现为 Clustered index seek,我们期待的不是两个 Clustered index seek 吗?这是由于前一张表没有断言(predicate),而后一张表通过 ON keyword对SalesOrderID 进行了断言限制。
运行计划中的 Key Lookup
为了兴许的演示样例,我们先在同一张表上建立两个不同的索引:
CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderlID ON SalesOrdDetailDemo (SalesOrderID,SalesOrderDetailID)
GO
CREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)
GO
运行下面的查询:
SELECT
ModifiedDate
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO
运行计划例如以下图,他利用了我们先前建立在 ModifiedDate 字段上的 Non-Clustered Index,生成为一个Index Seek 处理。
我们改造一下查询语句,SELECT 中多加两个字段:
SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO
运行计划例如以下图,基本没变:
上面选出的字段不是属于 Non-Clustered Index 就是属于 Clustered Index,假设再添加几个其它的字段呢?
SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO
乖乖,运行计划一下多了两个处理(Key Lookup, Nested Loop):
Key Lookup 是一个繁重的处理,我们能够使用keyword WITH 来指定使用 Clustered Index,以此回避Key Lookup。
SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO
运行计划应声而变成为一个 Clustered Index Scan:
前文提过 Scan 似乎也不是一个非常好的处理,那么矮子里拔高个,使用 SET STATISTICS IO ON 来比較一下:
SET STATISTICS IO ON
GO SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo WITH(INDEX=idx_non_clust_SalesOrdDetailDemo_ModifiedDate)
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO
比較下来,採用了 clustered index 的查询表现最差,另外 SET STATISTICS IO 输出的数据中clustered index 的查询在 logical reads 上花费了很多其它的时间。
看起来採用 non-clustered index + Key Lookup 运行计划表现还不错,但假设能回避 Key Lookup 就完美了,我们来把 non-clustered index 改动一下,用 INCLUDE keyword在索引中包括其它的字段:
DROP INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo
GO
CREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)
INCLUDE
(
ProductID,
UnitPrice
)
GO -- 清下缓存,仅用于开发环境!
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
再次运行之前的查询:
SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO
这下完美了,由于我们的查询字段都包括在索引中,所以运行计划终于被优化为 Index Seek。
最新文章
- 使用rsync+inotify同步两台服务器文件
- 设置 tableview 的背景颜色,总是有蒙层
- Convert HTML to Text(转载)
- js接受url参数
- 李洪强-C语言关键字、标识符和注释
- 如何做好一名DBA【转】
- RTP, RTCP, RTSP 协议介绍
- 办理英属哥伦比亚大学(本科)学历认证『微信171922772』UBC学位证成绩单使馆认证University of British Columbia
- Entity FrameWork 实体属性为decimal时默认只保存2位小数
- 【Android Widget】FragmentTabHost
- Apache配置虚拟主机后,不能访问localhost
- Node.js_Buffer 缓冲区
- 利用RALL机制来事项String类的赋值操作
- 第9章 Linux进程和信号超详细分析
- php 安装扩展
- [转]使用C#调用cmd来执行sql脚本
- java 调用windows bat脚本
- java.lang.ClassNotFoundException: org.I0Itec.zkclient.exception.ZkNoNodeException 异常 如何处理
- OkHttp3实现Cookies管理及持久化
- 使用gdb和gdbserver调试Android C/C++程序
热门文章
- Mapxtreme C#鹰眼地图
- spring 整合quartz的方式——简单介绍
- SQLSERVER复制优化之一《减少包大小》
- Oracle EBS WMS特征(一)
- Parse 和 Swift 搭建一个像 Instagram
- 应用程序框架实战十三:DDD分层架构之我见(转)
- Win7安装和配置Tigase 5.2server
- javascript/jquery读取和修改HTTP headers
- ASP.NET MVC+EF框架+EasyUI实现权限管理系列之开篇
- 兼容安卓的javaproject1.0