文盲的数据库指令优化心得:第二部分,指令变形和执行计划

本文主要是介绍文盲的数据库指令优化心得:第二部分,指令变形和执行计划,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

首先,文盲先写了四个语句,这四个预计得到的结果集是完全相同的,但是第一个语句对索引的要求最多,也是我们平时所写的语句格式,因为这么写起来字符最少,维护方便。。。。。。但是从效率和索引维护上来将,第一条语句其实不是最好的,所以,我们产生了三中变形,分别是exists子查询,apply子查询,join子查询


select id from test_index where n1>40 and n5<100 order by n5select main.id from test_index main  
inner join (select id,n5 from test_index where n5<100) app_n5 on main.id=app_n5.id  
where n1>40 and n2=n2 and b1=b1 and d1=d1  
order by app_n5.n5 select a.id from (select id from test_index where n1>40 and n2=n2 and b1=b1 and d1=d1) a
cross apply(select n5 from test_index where n5<100 and id=a.id) n5
order by n5select a.id from test_index a where n5<100
and exists(select top 1 1 from test_index where id=a.id and n1>40 and n2=n2 and b1=b1 and d1=d1)
order by n5

exists 是直接跟随在where之后的,但是,这个子查询得到的结果是无法参与下一步计算的,比如order,所以可以看到第四个语句的查询主条件已经变了,这个说实话,不太方便,那么 exists 的使用范围就限定了,只能是条件,但不能参与更进一步的运算


再看看join,这个关联查询到时可以返回多个字段参与下一步运算,但是join语句无法直接使用主语句返回的结果,所以如果需要使用主查询的结果集,就需要把运算写在on语句里,十分麻烦,尤其是无法直接使用表值函数对主查询的值进行计算,这点反而是exists可以做到


最后看看apply,首先,这个指令可以直接使用主查询的结果,其次,由apply提供的列可以参与下一步计算,所以apply的适用范围更广,好了变形方式第一个知识点完毕


然后再看看这些指令的下一组比较

select id from test_index where n1>40 and (n5<100 or n5 is null)SQL Server 分析和编译时间: CPU 时间 = 0 毫秒,占用时间 = 18 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 5,逻辑读取 21319 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 405 毫秒,占用时间 = 815 毫秒。



select id from test_index where n1>40 and isnull(n5,0)<100SQL Server 分析和编译时间: CPU 时间 = 0 毫秒,占用时间 = 13 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 5,逻辑读取 21319 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 266 毫秒,占用时间 = 786 毫秒。



select id from test_index where n1>40 and n5<100
union
select id from test_index where n1>40 and n5 is nullSQL Server 分析和编译时间: CPU 时间 = 0 毫秒,占用时间 = 6 毫秒。(367267 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'test_index'。扫描计数 2,逻辑读取 20936 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 172 毫秒,占用时间 = 761 毫秒。


select id from test_index a 
where n1>40 and n2=n2 and b1=b1 and d1=d1
and exists(select 1 from test_index where isnull(n5,0)<100 and id=a.id)SQL Server 分析和编译时间: CPU 时间 = 6 毫秒,占用时间 = 6 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 10,逻辑读取 10574 次,物理读取 0 次,预读 9 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 544 毫秒,占用时间 = 869 毫秒。



select id from test_index a
where n1>40 and n2=n2 and b1=b1 and d1=d1
and exists(select 1 from test_index where n5<100 and id=a.id union select 1 from test_index where n5 is null and id=a.id)SQL Server 分析和编译时间: CPU 时间 = 14 毫秒,占用时间 = 14 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 11,逻辑读取 7022 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 578 毫秒,占用时间 = 838 毫秒。



select a.id from test_index a
inner join (select id from test_index where isnull(n5,0)<100) b on a.id=b.id
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 16 毫秒,占用时间 = 18 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 10,逻辑读取 10574 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 468 毫秒,占用时间 = 824 毫秒。



select a.id from test_index a
inner join (select id from test_index where n5<100 union select id from test_index where n5 is null) b on a.id=b.id
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 11,逻辑读取 7022 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 577 毫秒,占用时间 = 818 毫秒。



select id from test_index a
cross apply (select n5 from test_index where isnull(n5,0)<100 and id=a.id) b
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒,占用时间 = 21 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 10,逻辑读取 10574 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 515 毫秒,占用时间 = 898 毫秒。



select id from test_index a
cross apply (select n5 from test_index where n5<100 and id=a.id union select n5 from test_index where n5 is null and id=a.id) n5
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 5,逻辑读取 11025992 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 6786 毫秒,占用时间 = 2340 毫秒。



select id from test_index a
cross apply (select n5 from (select id,n5 from test_index where n5<100 union select id,n5 from test_index where n5 is null) b where id=a.id) n5
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 11,逻辑读取 7022 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 594 毫秒,占用时间 = 820 毫秒。



好了,同一个条件的,同样索引情况下的各种变形指令列出来了9种,可以看到,执行计划即便是全部是索引查找的也未必一定的最快的,比如最第九个语句,而在语句中存在了列计算或者or的时候,则变成了索引扫描,所以,到底一个实际的应用到底效率如何,还是需要把指令拿出来具体分析,统计一下到底如何写效率才是最高的


关于条件中出现or要用union代替,别人说过了,列参与计算会变成索引扫描,别人说过了,使用索引来提速,前一篇也说过了。。。。那么在这组比较中我们发现,第七条指令和第十条指令相对较快,执行计划相对合理,那么根据这组比较来看,我们对查询指令的优化并无定向,只能试试各种方式看看到底如何才能得到最高效率的查询方式


我么由此得到结论,指令的各种变形会直接影响到索引的使用和执行计划的规划,所以,多试试各种写法,对提高查询指令效率是有帮助的

----------------------------------------------------------------------------------------

文盲的数据库指令优化心得:第一部分,关于索引

文盲的数据库指令优化心得:第二部分,指令变形和执行计划


SQL SERVER全面优化-------Expert for SQL Server 诊断系列


这篇关于文盲的数据库指令优化心得:第二部分,指令变形和执行计划的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/746659

相关文章

Java实现数据库图片上传功能详解

《Java实现数据库图片上传功能详解》这篇文章主要为大家详细介绍了如何使用Java实现数据库图片上传功能,包含从数据库拿图片传递前端渲染,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1、前言2、数据库搭建&nbsChina编程p; 3、后端实现将图片存储进数据库4、后端实现从数据库取出图片给前端5、前端拿到

IDEA连接达梦数据库的详细配置指南

《IDEA连接达梦数据库的详细配置指南》达梦数据库(DMDatabase)作为国产关系型数据库的代表,广泛应用于企业级系统开发,本文将详细介绍如何在IntelliJIDEA中配置并连接达梦数据库,助力... 目录准备工作1. 下载达梦JDBC驱动配置步骤1. 将驱动添加到IDEA2. 创建数据库连接连接参数

Jmeter如何向数据库批量插入数据

《Jmeter如何向数据库批量插入数据》:本文主要介绍Jmeter如何向数据库批量插入数据方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录Jmeter向数据库批量插入数据Jmeter向mysql数据库中插入数据的入门操作接下来做一下各个元件的配置总结Jmete

MySql中的数据库连接池详解

《MySql中的数据库连接池详解》:本文主要介绍MySql中的数据库连接池方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql数据库连接池1、概念2、为什么会出现数据库连接池3、原理4、数据库连接池的提供商5、DataSource数据源6、DBCP7、C

StarRocks数据库详解(什么是StarRocks)

《StarRocks数据库详解(什么是StarRocks)》StarRocks是一个高性能的全场景MPP数据库,支持多种数据导入导出方式,包括Spark、Flink、Hadoop等,它采用分布式架构,... 目录StarRocks介绍什么是StarRocks?StarRocks适合什么场景?StarRock

Windows环境下安装达梦数据库的完整步骤

《Windows环境下安装达梦数据库的完整步骤》达梦数据库的安装大致分为Windows和Linux版本,本文将以dm8企业版Windows_64位环境为例,为大家介绍一下达梦数据库的具体安装步骤吧... 目录环境介绍1 下载解压安装包2 根据安装手册安装2.1 选择语言 时区2.2 安装向导2.3 接受协议

Java嵌套for循环优化方案分享

《Java嵌套for循环优化方案分享》介绍了Java中嵌套for循环的优化方法,包括减少循环次数、合并循环、使用更高效的数据结构、并行处理、预处理和缓存、算法优化、尽量减少对象创建以及本地变量优化,通... 目录Java 嵌套 for 循环优化方案1. 减少循环次数2. 合并循环3. 使用更高效的数据结构4

grom设置全局日志实现执行并打印sql语句

《grom设置全局日志实现执行并打印sql语句》本文主要介绍了grom设置全局日志实现执行并打印sql语句,包括设置日志级别、实现自定义Logger接口以及如何使用GORM的默认logger,通过这些... 目录gorm中的自定义日志gorm中日志的其他操作日志级别Debug自定义 Loggergorm中的

SpringBoot使用Jasypt对YML文件配置内容加密的方法(数据库密码加密)

《SpringBoot使用Jasypt对YML文件配置内容加密的方法(数据库密码加密)》本文介绍了如何在SpringBoot项目中使用Jasypt对application.yml文件中的敏感信息(如数... 目录SpringBoot使用Jasypt对YML文件配置内容进行加密(例:数据库密码加密)前言一、J

Python调用Orator ORM进行数据库操作

《Python调用OratorORM进行数据库操作》OratorORM是一个功能丰富且灵活的PythonORM库,旨在简化数据库操作,它支持多种数据库并提供了简洁且直观的API,下面我们就... 目录Orator ORM 主要特点安装使用示例总结Orator ORM 是一个功能丰富且灵活的 python O