MSSQL优化查询
Tracy.T.Zhang1 使用“执行计划”. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
2 比较执行时间. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
3 查看IO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
4 提升效率的几点原则. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
5 简单应用——比较两种分页算法的效率. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
5.1 执行计划比较. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10
5.2 运行时间比较. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
5.3 IO比较. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
公司的数据库属于具有海量数据的OLTP系统数据库所以对于效率的要求很高。很多时候脚本被DBA发回来也是因为这方面的原因。下面就结合实际工作讲一下我对SQL脚本优化的一些经验。
网上有很多关于数据库脚本优化的经验文章公司也有SQL脚本规范这些都写得很好都是前人积累的优秀经验但是一条条死记是比较辛苦一点的而且超出提到的范围之后也不一定能发现我们的脚本存在问题就更不用说去修改了。下面就说一下怎样利用MSSQL现有的工具发现我们脚本中的问题。
1使用“执行计划”
MSSQL提供了一个比较好的工具执行计划通过这个工具我们至少可以知道一下两件事情
1、 几个返回结果等效的脚本究竟哪一个效率更高
2、在一个脚本中哪一个地方消耗了最多的资源
就以我曾经做过的一个脚本为例来说明吧。这个脚本里面有一段类似这样的语句
按快捷键Ctr l+L调出预计的执行计划如图
我们发现消耗成本最大的是图中用红色边框标出来的那一部分将鼠标停留在上面进一步查看详细信息如图
得知影响效率的应该是这一句
找到原因了我们开始尝试修改改成了这样的脚本
用预计的执行计划对比一下两条语句如图
可以看到原来的脚本执行成本占总成本的84.30%经过改进后的脚本执行成本占总成本的15.70%经过改进后的脚本效率明显提高。
还可不可以有进一步的改善呢我们注意到数据库里有这样一个视图dbo.table_arcash可以替换子查询a于是有了第二次改进的脚本
现在再来看一下预计的执行计划
可以看到原来的脚本执行成本占总成本的73.12%第一次改进后的脚本占13.62%第二次改进的脚本占13.26%。说明经过第二次改进效率又有提升。
2比较执行时间
目前我们只是直观的从执行计划上看到提升的百分比但是具体到执行时间上有什么具体的提升呢这就需要引入第二个知识点查看执行时间。
很多人可能会说这还不简单查询分析器的状态栏每次都会显示执行时间。对的不
过这里只能看出到秒的差异而且大部分时候是看不出来有什么差异的。
我们只需要把脚本放到这样两条语句之间就可以看到细化到毫秒的差异了
下面我们把以上提到的三个脚本放进来执行以下看看时间上有什么差异结果如下
可以看到执行时间上面第二和第三个脚本比第一个脚本提升了很多。究竟为什么会有这么大的提升呢这就要引入第三个知识点查看IO
3查看IO
我们只需要把脚本放到这样两条语句之间就可以看到IO情况了
下面我们把以上提到的三个脚本放进来执行以下看看IO上有什么差异结果如下
我们主要观察logical reads的值越小说明脚本效率越高三个脚本差别最大的地方已经用黄色标出。从1155到105的变化足以说明效率为什么会有显著提升了。
4提升效率的几点原则
1) 减少IO次数 IO的次数越少效率将会越高。
2) 避免全表扫描。如果发现全表扫描看看是否有索引可以使用如果没有可以添加索引。
3) 如果数据量比较大在查询要用到的列上建索引如果数据量小不建索引的效率更高。
4) 先筛选再联结直接用符合条件的100条数据和10000条数据联接总比10000条数据
和10000条数据联结再选出符合条件的100条效率要高。
5简单应用——比较两种分页算法的效率
两种常用的分页算法NOT IN+TOP和>MAX+TOP下面作简单比较
稳爱云(www.wenaiyun.com)是创建于2021年的国人IDC商家,主要目前要出售香港VPS、香港独立服务器、美国高防VPS、美国CERA VPS 等目前在售VPS线路有三网CN2、CN2 GIA,该公司旗下产品均采用KVM虚拟化架构。机房采用业内口碑最好香港沙田机房,稳定,好用,数据安全。线路采用三网(电信,联通,移动)回程电信cn2、cn2 gia优质网络,延迟低,速度快。自行封装的...
BuyVM 商家算是有一些年头,从早年提供低价便宜VPS主机深受广大网友抢购且也遭到吐槽的是因为审核账户太过于严格。毕竟我们国内的个人注册账户喜欢账户资料乱写,毕竟我们看英文信息有些还是比较难以识别的,于是就注册信息的时候随便打一些字符,这些是不能通过的。前几天,我们可以看到BUYVM商家有新增加迈阿密机房,而且商家有提供大硬盘且不限制流量的VPS主机,深受有一些网友的喜欢。目前,BUYVM商家有...
妮妮云的来历妮妮云是 789 陈总 张总 三方共同投资建立的网站 本着“良心 便宜 稳定”的初衷 为小白用户避免被坑妮妮云的市场定位妮妮云主要代理市场稳定速度的云服务器产品,避免新手购买云服务器的时候众多商家不知道如何选择,妮妮云就帮你选择好了产品,无需承担购买风险,不用担心出现被跑路 被诈骗的情况。妮妮云的售后保证妮妮云退款 通过于合作商的友好协商,云服务器提供2天内全额退款到网站余额,超过2天...