`
shendixiong
  • 浏览: 398069 次
  • 性别: Icon_minigender_1
  • 来自: 长沙
社区版块
存档分类
最新评论

如何让你的SQL运行得更快

阅读更多
在使用SQL往往会陷入一个区,即太注于所得的果是否正确,而忽略了不同的实现方法之可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如机事务处OLTP或决策支持系DSS)中表得尤
笔者在工作践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的接条件和不可化的where子句。
们进行适当的化后,其运行速度有了明地提高!
下面我将从三个方面分别进总结
了更直问题,所有例中的SQL运行时间经过测试,不超1秒的均表示< 1秒)。----
测试环: 主机:HP LH II---- 330MHZ---- 内存:128----
操作系Operserver5.0.4----
数据Sybase11.0.3
 
一、不合理的索引设计----
例:表record620000行,看在不同的索引下,下面几个 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 date28秒)
select count(*) from record where date >'19990901' and place in ('BJ','SH')14秒)
---- 分析:---- 在群集索引下,数据在物理上按序在数据上,重复值也排列在一起,因而在范围查,可以先找到个范的起末点,且只在个范描数据,避免了大范围扫描,提高了查询速度。
---- 3.placedateamount上的合索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 26秒)
select date,sum(amount) from record group by date27秒)
select count(*) from record where date >'19990901' and place in ('BJ, 'SH')< 1秒)
---- 分析:---- 是一个不很合理的合索引,因它的前列是place,第一和第二条SQL没有引用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在合索引中,形成了索引覆盖,所以它的速度是非常快的。
---- 4.dateplaceamount上的合索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1)
select date,sum(amount) from record group by date11秒)
select count(*) from record where date >'19990901' and place in ('BJ','SH')< 1秒)
---- 分析:---- 是一个合理的合索引。它将date列,使SQL都可以利用索引,并且在第一和第三个SQL中形成了索引覆盖,因而性能达到了最
---- 5.总结----
缺省情况下建立的索引是非群集索引,但有它并不是最佳的;合理的索引设计要建立在种查询的分析和预测上。
一般来
.有大量重复值、且常有范围查询between, >,< >=,< =)和order bygroup by生的列,可考建立群集索引;
.常同存取多列,且列都含有重复值可考建立合索引;
.合索引要尽量使关键查询形成索引覆盖,其前列一定是使用最繁的列。
 
二、不充份的接条件:
例:表card7896行,在card_no上有一个非聚集索引,表account191122行,在account_no上有一个非聚集索引,看在不同的表接条件下,两个SQL行情况:
select sum(a.amount) from account a,card b where a.card_no = b.card_no20秒)
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+(外account191122*card对应表第一行所要找的3=595907I/O
在第二个接条件下,最佳查询方案是将card作外表,account作内表,利用account上的索引,其I/O次数可由以下公式估算:外card上的1944+(外card7896*account对应一行所要找的4= 33528I/O
,只有充份的接条件,真正的最佳方案才会被行。
总结
1.多表操作在被实际执行前,查询优化器会根据接条件,列出几可能的接方案并从中找出系统开销最小的最佳方案。接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外表中的匹配行数*表中一次找的次数确定,乘最小最佳方案。
2.行方案的方法-- set showplanon,打showplan选项,就可以看到序、使用何索引的信息;想看更详细的信息,需用sa角色dbcc(3604,310,302)
 
三、不可化的where子句
1.例:下列SQL条件句中的列都建有恰当的索引,但行速度却非常慢:
select * from record wheresubstring(card_no,1,4)='5378'(13)
select * from record whereamount/30< 100011秒)
select * from record whereconvert(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.例:表stuff200000行,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数据性能的影响。
明,表的行数越多,工作表的性能就越差,当stuff620000时间竟达到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 asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d
直接算出果,时间同上面一快!
 
---- 总结---- ,所谓优化即where子句利用了索引,不可化即生了表描或开销
1.任何列的操作都将致表描,它包括数据函数、算表达式等等,查询时要尽可能将操作移至等号右
2.inor子句常会使用工作表,使索引失效;如果不生大量重复值,可以考把子句拆;拆的子句中应该包含索引。
3.要善于使用存储过程,它使SQL得更加灵活和高效。
从以上些例子可以看出,SQL化的实质就是在果正确的前提下,用化器可以识别句,充份利用索引,减少表描的I/O次数,尽量避免表搜索的生。其SQL的性能化是一个复杂程,上述些只是在次的一,深入研究及数据库层源配置、网络层的流量控制以及操作系统层设计
分享到:
评论

相关推荐

    如何让你的SQL运行得更快.

    如何让你的SQL运行得更快.

    SQL运行得更快.rar

    "SQL运行得更快"这个主题涵盖了多种优化策略和技术,旨在帮助用户更好地理解和应用这些方法。以下是对PDF文件"SQL运行得更快"中可能涉及的关键知识点的详细阐述: 1. **查询优化器**:SQL查询优化器是数据库管理...

    如何让SQL运行得更快.doc

    SQL 优化是数据库管理员和开发者经常遇到的问题,如何让 SQL 运行得更快是数据库性能优化的关键。通过对索引设计和连接条件的优化,可以大幅度提高 SQL 的执行速度。 一、索引设计的重要性 索引是数据库中一个非常...

    基于微信小程序的在线办公小程序答辩PPT.pptx

    基于微信小程序的在线办公小程序答辩PPT.pptx

    机器学习(预测模型):2000年至2015年期间193个国家的预期寿命和相关健康因素的数据

    这个数据集来自世界卫生组织(WHO),包含了2000年至2015年期间193个国家的预期寿命和相关健康因素的数据。它提供了一个全面的视角,用于分析影响全球人口预期寿命的多种因素。数据集涵盖了从婴儿死亡率、GDP、BMI到免疫接种覆盖率等多个维度,为研究者提供了丰富的信息来探索和预测预期寿命。 该数据集的特点在于其跨国家的比较性,使得研究者能够识别出不同国家之间预期寿命的差异,并分析这些差异背后的原因。数据集包含22个特征列和2938行数据,涉及的变量被分为几个大类:免疫相关因素、死亡因素、经济因素和社会因素。这些数据不仅有助于了解全球健康趋势,还可以辅助制定公共卫生政策和社会福利计划。 数据集的处理包括对缺失值的处理、数据类型转换以及去重等步骤,以确保数据的准确性和可靠性。研究者可以使用这个数据集来探索如教育、健康习惯、生活方式等因素如何影响人们的寿命,以及不同国家的经济发展水平如何与预期寿命相关联。此外,数据集还可以用于预测模型的构建,通过回归分析等统计方法来预测预期寿命。 总的来说,这个数据集是研究全球健康和预期寿命变化的宝贵资源,它不仅提供了历史数据,还为未来的研究和政策制

    基于微信小程序的“健康早知道”微信小程序答辩PPT.pptx

    基于微信小程序的“健康早知道”微信小程序答辩PPT.pptx

    基于微信小程序的电影交流平台答辩PPT.pptx

    基于微信小程序的电影交流平台答辩PPT.pptx

    计算机字符编码GB18030.PDF

    计算机字符编码GB18030

    Hive 操作基础(进阶版)多级分区数据文件2

    Hive 操作基础(进阶版)多级分区数据文件2

    基于java的贫困生管理系统答辩PPT.pptx

    基于java的贫困生管理系统答辩PPT.pptx

    pandas-2.1.4-cp312-cp312-win_amd64.zip

    pandas whl安装包,对应各个python版本和系统(具体看资源名字),找准自己对应的下载即可! 下载后解压出来是已.whl为后缀的安装包,进入终端,直接pip install pandas-xxx.whl即可,非常方便。 再也不用担心pip联网下载网络超时,各种安装不成功的问题。

    TA_Lib轮子无需编译-TA_Lib-0.4.18-cp38-cp38-win32.whl.zip

    TA_lib库(whl轮子),直接pip install安装即可,下载即用,非常方便,各个python版本对应的都有。 使用方法: 1、下载下来解压; 2、确保有python环境,命令行进入终端,cd到whl存放的目录,直接输入pip install TA_lib-xxxx.whl就可以安装,等待安装成功,即可使用! 优点:无需C++环境编译,下载即用,方便

    课设毕设基于SpringBoot+Vue的瑜伽体验课预约系统源码可运行.zip

    本压缩包资源说明,你现在往下拉可以看到压缩包内容目录 我是批量上传的基于SpringBoot+Vue的项目,所以描述都一样;有源码有数据库脚本,系统都是测试过可运行的,看文件名即可区分项目~ |Java|SpringBoot|Vue|前后端分离| 开发语言:Java 框架:SpringBoot,Vue JDK版本:JDK1.8 数据库:MySQL 5.7+(推荐5.7,8.0也可以) 数据库工具:Navicat 开发软件: idea/eclipse(推荐idea) Maven包:Maven3.3.9+ 系统环境:Windows/Mac

    tornado-6.2b2.tar.gz

    tornado-6.2b2.tar.gz

    javawe论坛项目 原生技术

    javawe论坛项目 原生技术

    tornado-6.2b1-cp310-cp310-macosx_10_9_universal2.whl

    tornado-6.2b1-cp310-cp310-macosx_10_9_universal2.whl

    基于司机信用评价的货运管理系统(springboot+vue+mysql+说明文档).zip

    随着物流行业的快速发展,货运管理变得愈发重要。为了提高货运效率,确保货物安全,我们开发了这款基于司机信用评价的货运管理系统。 该系统主要包含了货物信息管理、订单评价管理、货主管理等多个功能模块。在货物信息管理模块中,用户可以查看和管理货物的详细信息,包括货物名称、规格、装车状态、运输状态以及卸货状态等,方便用户随时掌握货物的动态。 订单评价管理模块是该系统的核心之一,它允许货主对司机的服务进行评价,系统会根据评价数据对司机进行信用评分。这一功能不仅有助于提升司机的服务质量,还能为货主提供更加可靠的货运选择。 此外,货主管理模块提供了货主信息的录入、修改和查询等功能,方便用户管理自己的货主资料。系统界面简洁明了,以蓝色为主色调,设计现代且专业,为用户提供了良好的使用体验。 通过该系统,用户可以轻松实现货物信息的查看和管理,对司机的服务进行评价,提高货运效率和服务质量。同时,系统也为司机提供了一个展示自我、提升信用的平台,有助于推动物流行业的健康发展。

    毕业生交流学习平台 SSM毕业设计 附带论文.zip

    毕业生交流学习平台 SSM毕业设计 附带论文 启动教程:https://www.bilibili.com/video/BV1GK1iYyE2B

    基于java的广场舞团答辩PPT.pptx

    基于java的广场舞团答辩PPT.pptx

Global site tag (gtag.js) - Google Analytics