SQL优化技术及应用

SOL优化技术及应用sQL优化技术及应用徐新静(天津天铁冶金集团有限公司计控电讯厂,河北省涉县056404[摘要]随着信息化进程的加快管理工作的逐步细化QL的优化直接影响着系统的运行状态和速度。根据软件设计开发过程中数据库设计和程序开发的技术和经验通过实例总结了数据库访问SQL优化的方法和技巧及在项目中的应用。通过优化,提高了系统运行速度减少了系统故障用户反馈良好关键词数据库主键外键索引关联优化1前言况下我们关心的是进出厂货物的净重,如果每次查随着信息化技术在天铁的逐步推广应用信息化询数据分析都由计算得出净重必然耗费时间,所以已经覆盖了包括进出厂物资的计量、设备材料及备品增加了净重这个冗余字段以提高速度,同时也降低了飞备件的采购发放、生产过程的控制、产成品的销售等SQL语句的复杂度。产、供、销的各个环节。随着管理的进一步细化数据例: lect车号,日期,净重from<称重表题量逐步增加,要快速有效地访问这些数据,对这些数 where净重>60据进行综合的分析,并且做到并发共享使用这些数Select车号,日期,毛重-皮重frm<称重表>据,就要想方设法优化数据结构和访问的SQL语句, where毛重-皮重>60提高其运行效率212主键的选择2SQL优化方法及应用一个表只有一个主键,主键能唯一确定一个实抛却数据库系统的因素来优化SQL,不外乎从数体,其他字段就是主键所确定实体的一些属性特点,据库设计和程序实现两个方面进行优化。在工作中同它们之间没有相互依赖关系。主键将频繁地用于增样的数据库服务器,由于不同的设计理念造成了不同删、改、查询等SQL语句主键的选择至关重要。主键的数据库设计、不同的访问速度结果是大相径庭。怎般以常用的列作为主键,主键尽可能短;对于复合样提高数据访问速度对一个系统程序员来说至关重主键,需要几个字段联合做主键时,主键列要尽可能要,也是一个应用系统成败的关键地少实在不行就人为增加一单一的主键列,因为主警21数据库设计阶段的优化键的长短将影响索引的大小和外建表的大小,导致增211建表加磁盘的M0和表关联的难度。建表时要基本满足三个范式。即:表的记录要求21.3外键外键的作用是建立和加强两个表数据之间的链性具有原子性不可再分解对字段冗余的约束即表接,即关系型数摇库中的表间关系它实观了数据库里的每一个字段都不能由别的字段衍生和计算得出。表间的参照完整性约束外键约束不仅控制存储在外鹦但在实际工作中可根据实际情况设置字段冗余,以达键表中的数据,还可以控制对主键表中数据的修改。到空间换时间的效果。如称重表中有毛重、皮重净重如果不通过外键建立这种表间关系,那么在表上进行三个字段,净重可由毛重减去皮重获得,在大多数情记录的增删改时就会使数据的完整性遭到破坏。护》护护护机护机护护护护护器加》护护护器加机》加护器器护加》》护》》护机机护》》》5]刘金海刘怀章孙开明刘日新钢管热处理过程的“黑匣子”动态温度测试A1]2004年全国炼钢轧钢生产技术会议文集[C12004作者简介6]JBr37367-1994质量管理中常用的统计工具S刘金海,男,1987年毕业于北京钢铁学院热能工程系,高级工7张树坤张利民36Mn2V钢石油套管的亚温淬火强韧化处理工程师中国煤化工人事钢管热处理和螺纹艺钢管,2005(6)20-22CNMH保部部长。8]陈建伟,赵冰27 MnCrv钢石油套管强韧化热处理工艺研究J天津钢管,2007(1):1-3.微机应用与自动化例:销售系统订单表中有订单号、订货品种、订货中,由于数据量小数据访问不是很频繁、并发用户少量、订货用户编号…客户表中有订货用户编号、地等原因,SQL语句的优劣基本体现不出来,而对于数址、税号、银行账号、联系方式……在客户表中订货用据量大、数据关系复杂的数据库环境,优劣立现,尤其户编号是主键,而在订单表中订货用户编号是外键。,是 Where子句优化,它的优化在很大程度上就是尽量如果删除客户表中的某个客户,必须保证订单表中没减少全表扫描。我在工作实践中总结出来,按照下面有该客户的订单,否则破坏了数据的完整性找不到的一些规则对SQL进行适当的优化后,其运行速度有该订单的客户信息,系统无法进行下一步的发货结了明显地提高算等一系列的工作。221语句优化2.14索引在 select语句中一定要明确选取的字段,不要把索引就像字典的目录,利用索引能提高数据访问没用的字段列入字段表,更不能用“ select*from表的速度就不难理解了。建立索引的目的是加快对表中名”这样的语句。记录的查找或排序。但是为表设置索引是要付出代价在公司产销系统中,有几个画面总提示数据字段的,它不但增加了数据库的存储空间,而且在插入和总数超出界定范围,但实际并没有超出,经过分析修改数据时要花费时间来维护索引,所以并不是索引SQL语句发现,这些画面所涉及的表其字段多达100越多越好,弄不好会事倍功半。这里建议在经常需要多个,有的甚至上200个,而画面所取的字段不过用于 where子句条件、排序、分组以及多表关联的列二十个,这时如果用 select*fom表名 where<条上建立索引。而对于查询几率小、数据值少(性别取值件>这样不但浪费了大量的Uo资源、服务器资源数只有男女两个)字段数据量大(text、imae等数据类据查询的速度也大打折扣并且因字段太多而前台画型)的列则不适合建立索面报错。在使用索引时要注意查询条件列的顺序和索引能用 where语句的尽量避免使用 having子句,的关系。聚合索引条件放在前面其次是非聚合索引, having子句要在检索出所有记录之后才对结果集进最后是普通字段条件,条件语句中用到复合索引中的行过滤。例起始字段才能显著提高速度,如果仅用复合索引的非优化前 select司磅工,日期, count(*)from称重表起始列则没什么效果。where货名=精矿’ group by司磅2.1.5表的分割工,日期 having日期>’201001-01表的字段数目过多或者根据实际情况预测表的优化后 select司磅工,日期,coun(磅单号)记录数非常大,那么要考虑表的分割。对于前者可按fom称重表 where货名=精矿'and照字段使用的频度进行垂直分割,后者按时间或记录日期>2010-01-01’ group by司磅工,日期的完整与否等规则实施水平分割。对于同一SQL语句,其字段列表、 where子句条公司产销系统在运行半年以后,一些履历表中的件尽量按规范和顺序填写。字段列要写在运算符的左记录数已达到相当的规模,有数百万条之多,而且还边值写运算符右边,如; where年龄>22和 where22会逐日增加,如果有多用户并发操作,同时进行增、年龄两个语句含义和结果一样,执行效率却不一样,改、查询等操作,这时就出现速度超慢或干脆服务吊如果年龄字段有索引的话,后者因不符合规范不但死现象。为了解决这个问题,对履历表按照一定规则增加了语句分析的次数,还放弃索引进行全表扫描,距进行了水平分割,运行表只保留最近N天的数据而降低了执行效率。另外,同样的语句按规范和顺序书抵把N天以前的数据放到历史表中,一般情况下只对运写第一次用这个语句时可能耗时长,但再次调用该行表进行各种操作,速度会有很大提高,如果需要查语句时速度大大加快,否则增加了数据库系统分析语询历史数据时从历史表中查询即可,不会影响运行表句所需的时间和服务器资源占用。例如:在一个有而影响现场工作900多万条的一个 Oracle数据表里运行“ select字段22程序设计阶段的优化列中国煤化工条件2”查询语句,我们在使用SQL语句时往往关注于结果的正确第CNMHG运行时仅用218与否,而忽略了其速度和成本。在一些小的应用系统∞〈微机应用与自动化)∞L优化技术及应用通过case、 decode等一些函数减少对数据库访问表扫描,对于已建立索引的列建议将“∞”或“!=”操的次数。例在雇员表 employee中有雇员编号h,出作符改用其他操作符替代。生日期bith两个字段,要求统计各个年龄段的人数。例如:字段名a为已建立索引列,建议将a∞>0改方法1为(a<0oa>0)Select count(bh)“30岁以下” from employee where避免在操作符的左边对索引列进行各种运算,那样系统将放弃索引而进行全表扫描Select count(bh)“30到45岁”" from employee where尽量使用 UNION ALL操作符,而不是UNON操datediff(year, birth, getdate0)>=30 and datediff(year.bih,作符。223其它Select count(bh)"45岁以上" from employee where要尽量避免大的事务,尤其不能在事务中间与用5datedifflyear, birth, getdate)>45户交互。大的事务占用资源多耗时长,还常常是独占E器方法2:资源状态,这样影响其他事务的响应速度,如果事务select count(bh),(case when datediff(year, birth getdate(0)<中间有与用户交互信息这时如果用户不操作事务30then30岁以下将一直占用资源到用户操作为止,容易发生死锁。when datediff (year, birth, getdate O)between'"3结束语多30and45then30到45岁在项目设计和后续的维护过程中,通过对这些优Lelse45岁以上’end)化技术和方法的合理运用,提高了系统运行速度减少了系统故障,用户反馈良好。尤其是公司销售系统,group by( case when datediff (year. birth, getdate 0)<30在初期运行阶段经常报数据溢出或查询吊死错误,通ghen30岁以下过对选取字段的控制和大数据量表的水平分割问题when datediff (year, birth, getdate O)between得到解决,系统运行两年来没有再出现类似问题查30and45then30到45岁询的平均响应时间也缩短了30%左右。另外SQL的else45岁以上’end优化,各种数据库之间都是互通的,但也不仅相同,有22,2操作符的优化些还与所采用的优化器的不同而需区别对待在工作减少IN、 NOT IN操作符的使用,对于能转换成中还要根据实际情况来具体对待。多表连接的要转换成多表连接。因为 ORACLE遇到IN时会试图将IN操作符转换成多个表的连接,用IN收稿2010-12-10责编赵实鸣)操作符将增加转换时间。避免对索引列NULL值进行判断,对NULL值的作者简介判断将产生全表扫描,不如在数据库设计时给字段设徐新静,女,19%年毕业于河北科技大学计算机及应用专业,置默认值.没有默认值的尽量用其它更优的等价语句高级工程师现在天铁集团计控电讯厂计算机中心从事软件开发工包数作“不等于”操作符不用索引,它的处理将会产生全天津钢管5亿元短融券票面利率为483%信天津钢管集团股份有限公司3月31日发布公告称,29日发行的5亿元366天期短融券票面利率为483%。本期短融券起息日为3月30日,上市流通日为3月31日,到期兑付日为2012年月30日公司早前公告称,募集资金中3亿元偿还集H中国煤化工于购买原材料对「■流动资金的需求CNMHG(摘自一财网2011-3-31)∞〈微机应用与自动化〉∞