`
tk_zhang
  • 浏览: 234595 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

如何让你的SQL运行得更快

阅读更多

---- 人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略
了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库
环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。笔者在工作实践
中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的whe
re子句。在对它们进行适当的优化后,其运行速度有了明显地提高!下面我将从这三个
方面分别进行总结:
---- 为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均
表示为(< 1秒)。

 

 


---- 测试环境--


---- 主机:HP LH II
---- 主频:330MHZ
---- 内存:128兆
---- 操作系统:Operserver5.0.4
----数据库:Sybase11.0.3


一、不合理的索引设计


----例:表record有620000行,试看在不同的索引下,下面几个 SQL的运行情况:


---- 1.在date上建有一非个群集索引
select count(*) from record where date >
'19991201' and date < '19991214'and amount >
2000 (25秒)
select date,sum(amount) from record group by date
(55秒)
select count(*) from record where date >
'19990901' and place in ('BJ','SH') (27秒)
---- 分析:
----date上有大量的重复值,在非群集索引下,数据在物理上随机存放在数据页上,在
范围查找时,必须执行一次表扫描才能找到这一范围内的全部行。


---- 2.在date上的一个群集索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 (14秒)
select date,sum(amount) from record group by date
(28秒)
select count(*) from record where date >
'19990901' and place in ('BJ','SH')(14秒)
---- 分析:
---- 在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范
围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范
围扫描,提高了查询速度。


---- 3.在place,date,amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 (26秒)
select date,sum(amount) from record group by date
(27秒)
select count(*) from record where date >
'19990901' and place in ('BJ, 'SH')(< 1秒)
---- 分析:
---- 这是一个不很合理的组合索引,因为它的前导列是place,第一和第二条SQL没有引
用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在组
合索引中,形成了索引覆盖,所以它的速度是非常快的。


---- 4.在date,place,amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000(< 1秒)
select date,sum(amount) from record group by date
(11秒)
select count(*) from record where date >
'19990901' and place in ('BJ','SH')(< 1秒)
---- 分析:
---- 这是一个合理的组合索引。它将date作为前导列,使每个SQL都可以利用索引,并
且在第一和第三个SQL中形成了索引覆盖,因而性能达到了最优。


---- 5.总结:
---- 缺省情况下建立的索引是非群集索引,但有时它并不是最佳的;合理的索引设计要
建立在对各种查询的分析和预测上。一般来说:
---- ①.有大量重复值、且经常有范围查询
(between, >,< ,>=,< =)和order by
、group by发生的列,可考虑建立群集索引;
---- ②.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;
---- ③.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。

 

二、不充份的连接条件:


---- 例:表card有7896行,在card_no上有一个非聚集索引,表account有191122行,在
account_no上有一个非聚集索引,试看在不同的表连接条件下,两个SQL的执行情况:

select sum(a.amount) from account a,
card b where a.card_no = b.card_no(20秒)
---- 将SQL改为:
select sum(a.amount) from account a,
card b where a.card_no = b.card_no and a.
account_no=b.account_no(< 1秒)
---- 分析:
---- 在第一个连接条件下,最佳查询方案是将account作外层表,card作内层表,利用
card上的索引,其I/O次数可由以下公式估算为:
---- 外层表account上的22541页+(外层表account的191122行*内层表card上对应外层
表第一行所要查找的3页)=595907次I/O
---- 在第二个连接条件下,最佳查询方案是将card作外层表,account作内层表,利用
account上的索引,其I/O次数可由以下公式估算为:
---- 外层表card上的1944页+(外层表card的7896行*内层表account上对应外层表每一
行所要查找的4页)= 33528次I/O
---- 可见,只有充份的连接条件,真正的最佳方案才会被执行。

 

 


---- 总结:


---- 1.多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方
案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的
表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘
积最小为最佳方案。
---- 2.查看执行方案的方法-- 用set showplanon,打开showplan选项,就可以看到连
接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,30
2)。


三、不可优化的where子句


---- 1.例:下列SQL条件语句中的列都建有恰当的索引,但执行速度却非常慢:
select * from record where
substring(card_no,1,4)='5378'(13秒)
select * from record where
amount/30< 1000(11秒)
select * from record where
convert(char(10),date,112)='19991201'(10秒)
---- 分析:
---- where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不
进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么
就可以被SQL优化器优化,使用索引,避免表搜索,因此将SQL重写成下面这样:
select * from record where card_no like
'5378%'(< 1秒)
select * from record where amount
< 1000*30(< 1秒)
select * from record where date= '1999/12/01'
(< 1秒)
---- 你会发现SQL明显快起来!


---- 2.例:表stuff有200000行,id_no上有非群集索引,请看下面这个SQL:
select count(*) from stuff where id_no in('0','1')
(23秒)
---- 分析:
---- where条件中的'in'在逻辑上相当于'or',所以语法分析器会将in ('0','1')转化
为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找,再将结果
相加,这样可以利用id_no上的索引;但实际上(根据showplan),它却采用了"OR策略"
,即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉
重复行,最后从这个临时表中计算结果。因此,实际过程没有利用id_no上索引,并且完
成时间还要受tempdb数据库性能的影响。
---- 实践证明,表的行数越多,工作表的性能就越差,当stuff有620000行时,执行时
间竟达到220秒!还不如将or子句分开:
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
---- 得到两个结果,再作一次加法合算。因为每句都使用了索引,执行时间只有3秒,
在620000行下,时间也只有4秒。或者,用更好的方法,写一个简单的存储过程:
create proc count_stuff as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no='0'
select @b=count(*) from stuff where id_no='1'
end
select @c=@a+@b
select @d=convert(char(10),@c)
print @d
---- 直接算出结果,执行时间同上面一样快!


---- 总结:


---- 可见,所谓优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。

 

---- 1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时
要尽可能将操作移至等号右边。
---- 2.in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把
子句拆开;拆开的子句中应该包含索引。
---- 3.要善于使用存储过程,它使SQL变得更加灵活和高效。


---- 从以上这些例子可以看出,SQL优化的实质就是在结果正确的前提下,用优化器可
以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。其实S
QL的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会
涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

分享到:
评论

相关推荐

    CSDN博客之星:技术交流与个人品牌共筑的分享盛会

    文案: “CSDN博客之星”是技术人的闪耀舞台,汇聚创新与分享的力量!通过参与评选,你不仅能提升个人品牌,还能链接行业精英,拓展技术视野。活动见证无数博主的成长,助力优质内容传播。无论你是技术爱好者还是资深从业者,这里都能让你展现才华,加速成长。原创干货、粉丝互动、持续输出——掌握这些秘诀,让你的博客脱颖而出,成为下一个“博客之星”!

    mpls-ospf全all

    mpls-ospf全all

    三菱FX3U PLC基于ST结构化文本与梯形图的四仓位配方控制系统解析

    内容概要:本文详细介绍了基于三菱FX3U PLC的四仓位配方控制系统,重点讲解了如何利用ST结构化文本和梯形图两种编程方式实现工业级配方管理。主要内容包括配方存储采用结构体数组的方式,使配方参数管理更加高效;配方执行过程中使用ST语言实现复杂的三段速控制逻辑,确保精确配料;通信方面通过FX3U-485ADP模块进行Modbus通信,保障数据传输的实时性和稳定性;报警系统采用状态码机制,便于快速定位和解决问题;此外,还涉及了分期付款功能以及暂停续料功能的具体实现方法。整个系统经过实际项目的验证,能够稳定应对每日200+批次的生产任务。 适合人群:从事工业自动化领域的工程师和技术人员,尤其是对PLC编程有一定了解并希望深入掌握ST结构化文本和梯形图混合编程技巧的人群。 使用场景及目标:适用于需要高精度、高效率配方管理的工业生产线,如食品加工等行业。主要目标是提高生产效率,减少人为错误,增强系统的可靠性和易维护性。 其他说明:文中提供了大量具体的代码片段和实际案例,有助于读者更好地理解和应用所介绍的技术。同时强调了全中文变量命名的优势,使得新入职员工也能迅速上手。

    嵌入式系统开发-蓝桥杯STM32实战解析-第十四届模拟题代码与考点精讲

    内容概要:本资源包含2023年第十四届蓝桥杯嵌入式组省赛第一套模拟题的完整实现代码,涵盖STM32CubeMX工程配置、HAL库开发、传感器数据采集、LCD显示控制、按键中断处理等核心模块。配套代码注释详细,包含模块化工程结构设计思路及竞赛评分要点解析。 适用人群:电子类专业本科/高职学生、蓝桥杯嵌入式组参赛选手、STM32开发初学者、嵌入式系统设计爱好者。 使用场景及目标:适用于蓝桥杯赛前专项训练、嵌入式系统开发实战演练、STM32HAL库应用学习。通过本资源可掌握竞赛级项目开发规范,提升外设驱动开发能力,理解实时数据采集与界面交互的实现逻辑。 其他说明:代码基于STM32G4系列开发板实现,包含多任务调度框架设计,涉及ADC/DAC、TIM定时器、GPIO中断等关键外设操作。建议配合官方开发板使用,资源包含硬件连接示意图及调试排错指南,注意部分外设配置需根据实际硬件调整。

    基于MATLAB的8字漂移轨迹车辆动力学仿真建模与实现

    内容概要:本文详细介绍了如何利用MATLAB构建一个能够模拟8字漂移动态特性的车辆模型。首先,通过设定车辆的基本参数(如质量、轴距、转动惯量)以及控制器参数(如比例系数、滑移率微分系数),并采用双频正弦波叠加的方法生成8字轨迹。接着,深入探讨了轮胎滑移率的非线性特性及其对横摆角速度的影响,展示了如何通过引入迟滞效应使仿真的物理行为更加逼真。此外,文中还讨论了转向增益、扭矩分配等关键因素对漂移稳定性和轨迹精度的作用,并提供了具体的代码实现方法。最后,通过轨迹可视化工具验证了模型的有效性。 适合人群:对汽车动力学感兴趣的研究人员、工程师以及有一定MATLAB编程基础的学习者。 使用场景及目标:适用于研究车辆动态性能、开发自动驾驶系统或进行赛车运动分析等领域。主要目标是帮助读者掌握车辆动力学建模的基本原理和技术手段,同时提高其解决复杂工程问题的能力。 其他说明:文中不仅给出了完整的代码示例,还分享了许多实用的小贴士,如如何调整参数以获得更好的仿真效果,以及如何优化代码结构以提升运行效率。对于希望深入了解车辆控制系统设计的人来说,这是一份不可多得的学习资料。

    ssm服装定制系统 LW PPT.zip

    Java项目基于ssm框架的课程设计,包含LW+ppt

    Delphi 12.3控件之WebView2Loader.rar

    Delphi 12.3控件之WebView2Loader.rar

    网页基础开发指南:HTML、CSS、JavaScript、JSON与Ajax详解

    内容概要:本文详细介绍了网页开发的基础技术,涵盖HTML、CSS、JavaScript、JSON和Ajax五个方面。首先讲解了HTML的历史和发展,重点介绍了HTML标签及其用法;接着阐述了CSS的导入方式、选择器和样式设置;随后深入探讨了JavaScript的基础语法、内置对象和DOM操作;再者解释了JSON的语法和数据类型,强调其在数据交换中的重要性;最后介绍了Ajax技术及其应用场景,展示了如何使用原生XMLHttpRequest、jQuery和Axios进行异步请求。 适合人群:适用于初学者和有一定经验的前端开发人员,帮助他们全面掌握网页开发的基础知识和技术。 使用场景及目标:① 初学者可以通过本文快速入门HTML、CSS和JavaScript,搭建简单的网页;② 已有基础的开发者可以深入了解JSON和Ajax,提升数据处理和异步交互的能力。 阅读建议:本文内容详尽,建议按章节逐步学习,结合实例代码进行练习,以便更好地理解和掌握各项技术要点。

    计算机科学与技术- 软件开发工具 培训资料

    计算机科学与技术- 软件开发工具 培训资料

    FX3U PLC控制器硬件与嵌入式开发详解:STM32F103VCT6为核心的工业控制解决方案

    内容概要:本文深入剖析了FX3U PLC控制器的硬件架构及其嵌入式开发细节。首先介绍了控制器的整体规格,如尺寸、主控芯片(STM32F103VCT6)、电源设计等。接着详细讲解了数字量输入输出模块的设计,包括继电器输出和光耦隔离的应用。对于模拟量处理部分,则探讨了ADC的校准与抗干扰措施。此外,通讯模块的设计也是重点之一,涵盖了CAN总线、RS485等接口的具体实现方法。最后,文章还提到了开发资料的完整性以及一些优化建议。 适合人群:从事工业自动化领域的工程师和技术人员,尤其是对PLC控制器和嵌入式开发感兴趣的读者。 使用场景及目标:帮助读者理解FX3U PLC控制器的工作原理,掌握其硬件设计特点和嵌入式编程技巧,适用于小型产线控制系统或智能仓储系统的开发。 其他说明:文中提供了大量源代码片段,便于读者更好地理解和实践相关知识点。同时强调了在实际应用中需要注意的问题,如电磁兼容性和信号完整性等。

    ssm高校网课管理系统lw+ppt.zip

    Java项目基于ssm框架的课程设计,包含LW+ppt

    基于MATLAB仿真的三电平逆变器SVPWM控制技术详解及其应用

    内容概要:本文详细介绍了基于MATLAB/Simulink的三电平逆变器SVPWM(空间矢量脉宽调制)控制系统的构建方法。首先,文章讲解了NPC(中点箝位)结构的三电平逆变器主电路搭建步骤,包括IGBT模块的选择和参数配置。然后深入探讨了SVPWM算法的具体实现,涵盖扇区判断、矢量合成、作用时间计算以及开关状态选择等关键技术点。此外,还讨论了电容电压平衡控制、死区时间和载波生成等重要细节。最后,通过FFT分析验证了系统的性能,展示了良好的波形质量和低谐波失真率。 适用人群:电力电子工程师、自动化专业学生、从事逆变器研究的技术人员。 使用场景及目标:适用于希望深入了解三电平逆变器SVPWM控制原理的研究人员和技术开发者。目标是掌握如何利用MATLAB进行高效、精确的逆变器仿真建模,优化波形质量,减少谐波失真。 其他说明:文中提供了大量实用的MATLAB代码片段,帮助读者更好地理解和实践相关理论。同时提醒了一些常见的仿真陷阱,如求解器选择不当可能导致的问题。强调了动手实践的重要性,鼓励读者自行搭建模型以加深理解。

    Delphi 12.3控件之nrCommLib Pro v9.54 Full Source for D12.7z

    Delphi 12.3控件之nrCommLib Pro v9.54 Full Source for D12.7z

    基于MATLAB的界面GUI指纹识别系统(高分项目).zip

    项目已获导师指导并通过的高分毕业设计项目,可作为课程设计和期末大作业,下载即用无需修改,项目完整确保可以运行。 该系统功能完善、界面美观、操作简单、功能齐全、管理便捷,具有很高的实际应用价值。 项目都经过严格调试,确保可以运行!可以放心下载

    Notepad-v3.2.0 最新版(2025年3月27日)

    notepad–是一个国产跨平台、轻量级的文本编辑器,是替换notepad++的一种选择。其内置强大的代码对比功能,让你丢掉付费的beyond compare。

    Delphi 12.3控件之TMS MQTT v2.0.8.0.7z

    Delphi 12.3控件之TMS MQTT v2.0.8.0.7z

    数据科学领域中层次聚类算法的详细解析及其Python实现

    内容概要:本文全面介绍了层次聚类算法,一种无监督学习方法,广泛应用于数据挖掘、机器学习和模式识别。文章首先阐述了聚类算法的基础理论,特别是层次聚类的独特之处——生成树状图展示数据点之间的相似性关系。随后,详细讲解了凝聚型层次聚类的工作原理,包括初始化、合并和重复步骤,并探讨了多种距离度量方法(如单链、完全链、平均链和重心法)。文中通过Python代码实例展示了如何使用SciPy库进行层次聚类,并生成树状图。此外,文章还讨论了层次聚类在生物信息学、图像分割和文本数据分析中的具体应用场景,以及评估聚类质量的方法(如轮廓系数和Calinski-Harabasz指数)。最后,文章总结了层次聚类的优点和缺点,并展望了未来的优化方向和挑战。 适合人群:数据科学家、机器学习工程师、研究人员和其他对聚类算法感兴趣的从业者。 使用场景及目标:①理解层次聚类的基本原理和工作流程;②掌握如何使用Python实现层次聚类;③学会评估聚类质量和优化聚类性能;④了解层次聚类在不同领域的应用。 其他说明:本文不仅提供了理论知识,还包括丰富的代码示例,使读者能够在实践中加深对层次聚类的理解。同时,文章还探讨了层次聚类

    中国房地产经纪人生存状况及发展趋势分析-基于58安居客百万经纪人调研

    本文基于58安居客发布的《百万房地产经纪人生存报告》,详细分析了中国房地产经纪人的生存状况和发展趋势。报告显示,房地产经纪人呈现出年轻化、长期化的趋势,95后从业者超过三成,八成经纪人从业时长超过1年。从业人员性别分布以男性为主,但女性经纪人比例显著增加。学历水平逐年提升,大专及以上学历占比过半。获客渠道主要集中在网络端口和老客户介绍,疫情期间,VR技术和私域流量成为重要手段。多数经纪人收入受疫情影响减少,但仍对未来市场保持信心。此外,经纪人在工作地置业的比例较高,显示出较强的职业稳定性。 适用人群:房地产行业从业者、研究机构、政府相关部门、投资者。 使用场景及目标:帮助了解中国房地产经纪人的职业特点和发展趋势,为相关政策制定、企业管理及个人职业规划提供参考。 报告数据来源于58安居客房产研究院,涵盖2022年4月至5月期间的数据,反映了当时市场环境下经纪人的实际情况。

Global site tag (gtag.js) - Google Analytics