




已阅读5页,还剩2页未读, 继续免费阅读
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
SQL查询优化技巧1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=03.应尽量避免在 where 子句中使用!=或操作符,否则将引擎放弃使用索引而进行全表扫描。4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or num=20可以这样查询:select id from t where num=10union allselect id from t where num=205.in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3)对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 36.下面的查询也将导致全表扫描:select id from t where name like %abc%若要提高效率,可以考虑全文检索。7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:select id from t where num=num可以改为强制查询使用索引:select id from t with(index(索引名) where num=num8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where num/2=100应改为:select id from t where num=100*29.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where substring(name,1,3)=abc-name以abc开头的idselect id from t where datediff(day,createdate,2005-11-30)=0-2005-11-30生成的id应改为:select id from t where name like abc%select id from t where createdate=2005-11-30 and createdate2005-12-110.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。12.不要写一些没有意义的查询,如需要生成一个空表结构:select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:create table #t(.)13.很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)用下面的语句替换:select num from a where exists(select 1 from b where num=a.num)14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。21.避免频繁创建和删除临时表,以减少系统表资源的消耗。22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。29.尽量避免大事务操作,提高系统并发能力。30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理SQL性能优化1.查询的模糊匹配 尽量避免在一个复杂查询里面使用 LIKE %parm1% 红色标识位置的百分号会导致相关列的索引无法使用,最好不要用.解决办法:其实只需要对该脚本略做改进,查询速度便会提高近百倍。改进方法如下: a、修改前台程序把查询条件的供应商名称一栏由原来的文本输入改为下拉列表,用户模糊输入供应商名称时,直接在前台就帮忙定位到具体的供应商,这样在调用后台程序时,这列就可以直接用等于来关联了。 b、直接修改后台根据输入条件,先查出符合条件的供应商,并把相关记录保存在一个临时表里头,然后再用临时表去做复杂关联2.索引问题 在做性能跟踪分析过程中,经常发现有不少后台程序的性能问题是因为缺少合适索引造成的,有些表甚至一个索引都没有。这种情况往往都是因为在设计表时,没去定义索引,而开发初期,由于表记录很少,索引创建与否,可能对性能没啥影响,开发人员因此也未多加重视。然一旦程序发布到生产环境,随着时间的推移,表记录越来越多,这时缺少索引,对性能的影响便会越来越大了。 这个问题需要数据库设计人员和开发人员共同关注法则:不要在建立的索引的数据列上进行下列操作:避免对索引字段进行计算操作避免在索引字段上使用not,!=避免在索引列上使用IS NULL和IS NOT NULL 避免在索引列上出现数据类型转换避免在索引字段上使用函数 避免建立索引的列中使用空值。3.复杂操作部分UPDATE、SELECT 语句 写得很复杂(经常嵌套多级子查询)可以考虑适当拆成几步,先生成一些临时数据表,再进行关联操作4.update同一个表的修改在一个过程里出现好几十次,如: update table1 set col1=. where col2=.; update table1 set col1=. where col2=. . 象这类脚本其实可以很简单就整合在一个UPDATE语句来完成(前些时候在协助xxx项目做性能问题分析时就发现存在这种情况)5.在可以使用UNION ALL的语句里,使用了UNIONUNION 因为会将各查询子集的记录做比较,故比起UNION ALL ,通常速度都会慢上许多。一般来说,如果使用UNION ALL能满足要求的话, 务必使用UNION ALL。还有一种情况大家可能会忽略掉,就是虽然要求几个子集的并集需要过滤掉重复记录,但由于脚本的特殊性,不可能存在重复记录,这时便应该使用UNION ALL,如xx模块的某个查询程序就曾经存在这种情况,见,由于语句的特殊性,在这个脚本 中几个子集的记录绝对不可能重复,故可以改用UNION ALL)6.在WHERE 语句中,尽量避免对索引字段进行计算操作 这个常识相信绝大部分开发人员都应该知道,但仍有不少人这么使用,我想其中一个最主要的原因可能是为了编写方便吧,但如果仅为了编 写简单而损害了性能,那就不可取了 9月份在对XX系统做性能分析时发现,有大量的后台程序存在类似用法,如: . where trunc(create_date)=trunc(:date1) 虽然已对create_date 字段建了索引,但由于加了TRUNC,使得索引无法用上。此处正确的写法应该是 where create_date=trunc(:date1) and create_datetrunc(:date1)+1 或者是 where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60) 注意:因between 的范围是个闭区间(greater than or equal to low value and less than or equal to high value.), 故严格意义上应该再减去一个趋于0的小数,这里暂且设置成减去1秒(1/(24*60*60)),如果不要求这么精确的话,可以略掉这步7.对Where 语句的法则7.1 避免在WHERE子句中使用in,not in,or 或者having。可以使用 exist 和not exist代替 in和not in。可以使用表链接代替 exist。Having可以用where代替,如果无法代替可以分两步处理。例子SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN (SELECT CUSTOMER_NAME FROM CUSTOMER)优化SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist (SELECT CUSTOMER_NAME FROM CUSTOMER)7.2 不要以字符格式声明数字,要以数字格式声明字符值。(日期同样)否则会使索引无效,产生全表扫描。例子使用:SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;不要使用:SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 73697.3 WHERE后面的条件顺序影响 Oracle从下到上处理Where子句中多个查询条件,所以表连接语句应写在其他Where条件前,可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响,如 Select * from zl_yhjbqk where dy_dj = 1KV以下 and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1 and dy_dj = 1KV以下 以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = 1KV以下条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。8.对Select语句的法则在应用程序、包和过程中限制使用select * from table这种方式。例子使用SELECT empno,ename,category FROM emp WHERE empno = 7369而不要使用SELECT * FROM emp WHERE empno = 73699. 排序避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行,耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序10.临时表慎重使用临时表可以极大的提高系统性能11.ORDER BYORDER BY 子句只在两种严格的条件下使用索引. ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序. ORDER BY中所有的列必须定义为非空.12.SQL书写的影响(共享SQL语句可以提高操作效率) 同一功能同一性
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 人教版生物七年级下册第四单元第七章第二节探究环境污染对生物的影响说课稿
- 2025年海洋能产业政策调整与市场应用研究报告
- 28.3圆心角和圆周角第1课时圆心角说课稿2023-2024学年冀教版九年级数学上册
- 2025年储能电池在电网侧储能电站的经济效益评估报告
- 2025低空经济「无人驾驶」行业报告:技术演进与未来市场布局
- 2025年低空经济「无人机培训」行业现状与职业规划报告
- 2025年电动汽车电池回收市场潜力及2026-2030年发展趋势研究报告
- 2024-2025学年高中物理 第2章 5 力的合成说课稿 教科版必修1
- 达标测试人教版八年级上册物理光现象《光的直线传播》定向测评试题
- 《项目四 任务二 辛勤的物流包装工》说课稿 -2023--2024学年浙教版初中劳动技术八年级上册
- 国防动员课件教学课件
- 江苏省南通市2024-2025学年七年级上学期期中英语试题(含答案不含听力原文及听力音频)
- 电梯日管控、周排查、月调度内容表格
- 统编语文四年级上册第六单元教材解读及集体备课
- 马工程《文学理论》
- 统编版(2024新版)七年级上册道德与法治各单元教材分析 讲义
- 课程纲要(知识清单)人教版美术五年级上册
- 医学信息集成标准与技术 课件 第六章 医疗健康信息集成规范IHE
- (正式版)QC∕T 1207-2024 燃料电池发动机用空气压缩机
- 2024年辽宁沈阳市近海控股集团招聘24人公开引进高层次人才和急需紧缺人才笔试参考题库(共500题)答案详解版
- 【幼儿角色游戏中教师的有效介入的方法及实施效果探析11000字(论文)】
评论
0/150
提交评论