第六章——根据执行计划优化性能(3)——键值查找
来源:互联网 发布:淘宝店铺装修策划方案 编辑:程序博客网 时间:2024/09/21 08:16
前言:
本文为本系列最后一篇,介绍键值查找的相关知识。
键值查找是具有聚集索引的表上的一个书签查找,键值查找用于SQLServer查询一些非键值列的数据。使用非聚集索引的查询不会有键值查找,但是所有键值查找会伴随非聚集索引出现。这里特别提醒的是键值查找总是伴有嵌套循环关联。
准备工作:
下面将创建一个表,通过执行计划看看键值查找的不同效果。为了产生键值查找,需要两件事情:
1、 聚集索引
2、 非聚集索引
当你在非聚集索引键值上有谓词时,查询的字段又不全部包含在非聚集索引上,需要通过聚集索引去查找,此时会产生键值查找。执行下面操作产生测试表:
USE AdventureWorksGOIF OBJECT_ID('SalesOrdDetailDemo') IS NOT NULL BEGIN DROP TABLE SalesOrdDetailDemo ENDGOSELECT *INTO SalesOrdDetailDemoFROM Sales.SalesOrderDetailGO
步骤:
1、 在测试表SalesOrdDetailDemo上创建一个聚集索引和一个非聚集索引:
CREATE UNIQUE CLUSTERED INDEX idx_SalesDetail_SalesOrderID ON SalesOrdDetailDemo(SalesOrderID,SalesOrderDetailID)GOCREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)GO
2、 执行下面的查询,并开启实际执行计划:
SELECT ModifiedDateFROM SalesOrdDetailDemoWHERE ModifiedDate = '2004-07-31 00:00:00.000'GO
3、 从执行计划的截图中看到,使用了一个非聚集索引(执行计划中叫做索引)查找:
如果你使用了文本化的执行计划,会看到:
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Index Seek(OBJECT:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[idx_non_clust_SalesOrdDetailDemo_ModifiedDate]), SEEK:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[ModifiedDate]=CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)
4、 对上面的查询语句进行少许的改动,多查询几列:
SELECT ModifiedDate , SalesOrderID , SalesOrderDetailIDFROM SalesOrdDetailDemoWHERE ModifiedDate = '2004-07-31 00:00:00.000'GO
5、 再检查执行计划:
它的文本化执行计划如下:
StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Index Seek(OBJECT:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[idx_non_clust_SalesOrdDetailDemo_ModifiedDate]), SEEK:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[ModifiedDate]=CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)
6、 在上面的查询中添加的列均包含在聚集索引和非聚集索引中,现在增加更多的列:
SELECT ModifiedDate , SalesOrderID , SalesOrderDetailID , ProductID , UnitPriceFROM SalesOrdDetailDemoWHERE ModifiedDate = '2004-07-31 00:00:00.000'GO
7、 查看执行计划,此时出现了两个新的操作符——键值查找和嵌套循环,如图:
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[SalesOrderID], [AdventureWorks].[dbo].[SalesOrdDetailDemo].[SalesOrderDetailID], [Expr1004]) WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[idx_non_clust_SalesOrdDetailDemo_ModifiedDate]), SEEK:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[ModifiedDate]='2004-07-31 00:00:00.000') ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[idx_SalesDetail_SalesOrderID]), SEEK:([AdventureWorks].[dbo].[SalesOrdDetailDemo].[SalesOrderID]=[AdventureWorks].[dbo].[SalesOrdDetailDemo].[SalesOrderID] AND [AdventureWo
8、 同时可以看到在键值查找上的百分比相当高,此时先试一下使用hint来改变优化器的行为:
SELECT ModifiedDate , SalesOrderID , SalesOrderDetailID , ProductID , UnitPriceFROM SalesOrdDetailDemo WITH ( INDEX=idx_SalesDetail_SalesOrderID )WHERE ModifiedDate = '2004-07-31 00:00:00.000'GO
9、 此时优化器使用了聚集索引,但是不能在上面进行查找,只能扫描,如图:
10、上图中显示的聚集索引扫描在返回少量数据的时候并不高效,所以应该考虑就近是聚集索引扫描好还是键值查询好,现在来再开启SET STATISTICS IO来监控一下IO情况,这次将三个查询都放到一起,其中两个是使用hint来分别把聚集索引和非聚集索引强制使用:
SET STATISTICS IO ON GOSELECT ModifiedDate , SalesOrderID , SalesOrderDetailID , ProductID , UnitPriceFROM SalesOrdDetailDemo WHERE ModifiedDate = '2004-07-31 00:00:00.000'GOSELECT ModifiedDate , SalesOrderID , SalesOrderDetailID , ProductID , UnitPriceFROM SalesOrdDetailDemo WITH ( INDEX=idx_SalesDetail_SalesOrderID )WHERE ModifiedDate = '2004-07-31 00:00:00.000'GOSELECT ModifiedDate , SalesOrderID , SalesOrderDetailID , ProductID , UnitPriceFROM SalesOrdDetailDemo WITH ( INDEX=idx_non_clust_SalesOrdDetailDemo_ModifiedDate )WHERE ModifiedDate = '2004-07-31 00:00:00.000'GOSET STATISTICS IO OFFGO
11、观察执行计划的开销情况:
然后观察一下IO情况:
12、通过对比,带有键值查找的非聚集索引貌似有更好的性能,但是如果移除了键值查找会不会更好?现在来尝试一下,这里先删除原有索引并创建一个覆盖索引或者带有INCLUDE列的索引。通知先清空一下缓存:
DROP INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemoGOCREATE NONCLUSTERED INDEX idx_non_clust_SalesOrdDetailDemo_ModifiedDate ON SalesOrdDetailDemo(ModifiedDate)INCLUDE (ProductID,UnitPrice)GO--不要在生产环境执行下面语句:DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSGO
13、再次执行没有hint的查询
14、从执行计划中可以看到这次成功去除了键值查找:
同时可以观察到IO,发现从305次已经降到了3次
分析:
在第二步中,查询带有一个谓词来筛选ModifiedDate,所以非聚集索引将进行查找,且索引键上就有所需的数据,所以此时不需要再进行任何查找。
在第四步中,在SELECT列中添加了SalesOrderID和SalesOrderDetailID,由于这两列在聚集索引中,所以此时依旧可以使用非聚集索引引用聚集索引的方式来实现。
在第六步中,再次添加了新列,这些列不在任何索引的索引键中,所以非聚集索引必须通过聚集索引的叶子节点查找这两列新增列的数值,此时键值查找和嵌套循环关联就会出现。由于键值查找是高开销的操作,所以在第八步中使用了hint来强制优化器使用聚集索引。但是此时使用了聚集索引扫描而不是查找,所以现在要思考哪种方式更快?
为了得到答案,在第十步中把三个查询放到一起。一个是没有hint,一个是使用聚集索引hint,另外一个使用非聚集索引hint。
从第十一步的百分比看到,SQLServer使用了带有键值查找的非聚集索引来代替聚集索引扫描。
现在可以初步得出带有键值查找的非聚集索引查找比较快,但是是否有更快的方法?
因为UnitPrice和ProductID不在的时候键值查找会消失,但是有时候确实需要这些列,所以使用覆盖索引或者带有INCLUDE列的非聚集索引来代替普通的非聚集索引。通过12、13步可以看出已经移除了键值查找并有更好的性能。
出现键值查找的主要原因之一是因为谓词中出现了符合非聚集索引的规则,但是在SELECT中的字段不存在于聚集索引键值或者非聚集索引键值中。此时聚集索引必须通过键值查找来找出这些数据。
- 第六章——根据执行计划优化性能(3)——键值查找
- 第六章——根据执行计划优化性能(2)——查找表/索引扫描
- 第六章——根据执行计划优化性能(1)——理解哈希、合并、嵌套循环连接策略
- Hadoop-impala十大优化之(4)—根据执行计划进行性能优化及最佳实践
- Hadoop-impala十大优化之(4)—根据执行计划进行性能优化及最佳实践
- SQL点滴27—性能分析之执行计划
- SQL点滴—性能分析之执行计划
- SQL点滴27—性能分析之执行计划
- 根据sid查找历史sql执行计划
- Oracle优化——外联结的执行计划
- 《高性能SQL调优精要与案例解析》一书谈主流关系库SQL调优(优化&TUNING)技术精髓之——执行计划获取及理解
- JavaScript性能优化(1)——加载与执行
- ACM暑期计划——第六天
- 第六周练习计划——贪心
- Oracle执行计划——Oracle 如何启用执行计划
- Oracle执行计划——Oracle 如何启用执行计划
- Oracle执行计划——Oracle 如何启用执行计划
- 【原创】构建高性能ASP.NET站点 第六章—性能瓶颈诊断与初步调优(下前篇)—简单的优化措施
- PHP导出mysql数据字典
- 正则表达式基础
- Spring 在 xml配置文件 或 annotation 注解中 运用Spring EL
- 博客
- sqlserver 索引
- 第六章——根据执行计划优化性能(3)——键值查找
- 资源与效率:java线程池
- Django配置Memcached
- 56民族下拉列表 民族下拉列表
- Android的Camera架构简介
- MFC非模态对话框的销毁
- C#验证码下载
- word2003按空格键为什么会出现小数点
- java向mysql数据库插入中文时乱码