执行计划--WHERE条件的先后顺序对执行计划的影响
在编写SQL时,会建议将选择性高(过滤数据多)的条件放到WHERE条件的前面,这是为了让查询优化器优先考虑这些条件,减少生成最优(或相对最优)的执行计划的时间,但最终的执行计划生成过滤顺序还是决定这些条件的选择性与判断bool值的容易程度
测试代码:
GO SELECT * INTO #T1 FROM sys.all_columns GO SELECT * INTO #T2 FROM sys.all_columns GO SELECT * INTO #T3 FROM sys.all_columns GO SET STATISTICS IO ON SET STATISTICS TIME ON GO SELECT * FROM #T1 AS T1 WHERE T1.[object_id]=3 AND (SELECT COUNT(1) FROM #T2 AS T2 WHERE T2.column_id>T1.column_id)>1 SELECT * FROM #T1 AS T1 WHERE (SELECT COUNT(1) FROM #T2 AS T2 WHERE T2.column_id>T1.column_id)>1 AND T1.[object_id]=3
执行计划:
可以从查询计划看出,无论T1.[object_id]=3在何处,其计算bool值相对简单,而(SELECT COUNT(1) FROM #T2 AS T2 WHERE T2.column_id>T1.column_id)>1 需要访问其他表,因此执行优化器优先执行T1.[object_id]=3,在满足T1.[object_id]=3为ture时再坚持行是否满足(SELECT COUNT(1) FROM #T2 AS T2 WHERE T2.column_id>T1.column_id)>1。
但对于以下查询:
SELECT * FROM #T1 AS T1 WHERE (SELECT COUNT(1) FROM #T3 AS T3 WHERE T3.[object_id]>T1.[object_id])<1 AND (SELECT COUNT(1) FROM #T2 AS T2 WHERE T2.column_id>T1.column_id)>1 SELECT * FROM #T1 AS T1 WHERE (SELECT COUNT(1) FROM #T2 AS T2 WHERE T2.column_id>T1.column_id)>1 AND (SELECT COUNT(1) FROM #T3 AS T3 WHERE T3.[object_id]>T1.[object_id])<1
执行计划:
执行统计:
(25 row(s) affected) Table 'Worktable'. Scan count 29, logical reads 36813, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#T3_________________________________________________________________________________________________________________00000000000C'. Scan count 4, logical reads 80, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#T1_________________________________________________________________________________________________________________000000000009'. Scan count 5, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#T2_________________________________________________________________________________________________________________00000000000A'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 111 ms, elapsed time = 331 ms. (25 row(s) affected) Table '#T1_________________________________________________________________________________________________________________000000000009'. Scan count 5, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 10731, logical reads 87653, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#T3_________________________________________________________________________________________________________________00000000000C'. Scan count 4, logical reads 236, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#T2_________________________________________________________________________________________________________________00000000000A'. Scan count 1, logical reads 59, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 219 ms, elapsed time = 281 ms.
条件 (SELECT COUNT(1) FROM #T2 AS T3 WHERE T3.[object_id]>T1.[object_id])<1 能过滤掉大部分数据,而(SELECT COUNT(1) FROM #T3 AS T2 WHERE T2.column_id>T1.column_id)>1不能过滤任何数据,因此如果优先执行(SELECT COUNT(1) FROM #T3 AS T2 WHERE T2.column_id>T1.column_id)>1,则会大大减少判断(SELECT COUNT(1) FROM #T2 AS T2 WHERE T2.column_id>T1.column_id)>1的次数,从而提高查询速度,但SQL Server无法推断出该结论,因此只能顺序判断WHERE 条件。
总结:虽然在很多情况下SQL Sever引擎能帮助我们判定 WHERE 条件后的执行顺序,但我们仍应该将选择性高(过滤数据多)的条件放置在 WHERE 语句中的前面,尤其对于复杂的SQL 语句,应仔细分析测试。
你比SQL SERVER 更了解你的数据!!!
相关文章
- 判断一个进程是否在执行中
- 执行redis命令redis-trib.rb查看集群信息报错cannot load such file -- redis (LoadError)
- int.TryParse非预期执行引发的思考 ASP.NET -- WebForm -- 给图片添加水印标记 Windows -- 使用批处理文件.bat删除旧文件
- 反爬虫:利用ASP.NET MVC的Filter和缓存(入坑出坑) C#中缓存的使用 C#操作redis WPF 控件库——可拖动选项卡的TabControl 【Bootstrap系列】详解Bootstrap-table AutoFac event 和delegate的分别 常见的异步方式async 和 await C# Task用法 c#源码的执行过程
- python进程执行带有参数的任务(args、kwargs)
- 子类构造方法的执行过程是什么样的?
- 30activiti - 流程执行历史记录
- iBatis应用--控制执行SQL时的超时时间
- iBatis应用--控制执行SQL时的超时时间
- 并发编程--异步执行一批任务,有一个完成立即返回,其他取消
- 源码解析-- DolphinScheduler master server流程执行过程分析
- JS高级--函数进阶(原型、调用方法、this、bind、严格模式、闭包、垃圾回收、递归深拷贝、匿名函数、回调函数、立即执行函数)
- 执行buildman --fetch-arch arm提示"urllib2.URLError: <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:581)>"如何处理?
- Yii2 console执行定时脚本
- 漏洞复现----34、yapi 远程命令执行漏洞
- 漏洞复现----2、Nostromo Web服务器远程命令执行(CVE-2019-16278)
- JS——BOM概述、window常见事件、定时器、JS执行机制
- 《Adobe Flash CS4中文版经典教程》——1.8 在Flash中撤销执行的步骤
- 自动化之Python 中自动执行大部分 Excel 任务的函数
- CSS规则的执行顺序
- sql server 数据库优化--显示执行计划
- 浅析mybatis懒加载执行时机、配置方式以及懒加载未生效的原因
- 华为OD机试 -任务总执行时长(Java) | 机试题+算法思路+考点+代码解析 【2023】
- Cocos2d-x执行时错误:Cocos2d: Get data from file(xxx.xxx) failed!
- jenkins:多个job时怎么按照一定顺序执行构建
- Quartz框架多个trigger任务执行出现漏执行的问题分析--转
- JVM执行篇:使用HSDIS插件分析JVM代码执行细节--转
- 监控redis服务器执行的命令--类似于tomcat的local-access.log
- 【Windows】基于curl命令的Shell上线姿势--解决shiro命令执行工具命令回显拉胯的问题
- 重庆万州:运用“大数据+” 预算执行审计取得成效
- tic和toc用来记录matlab命令执行的时间