SQL的编写技巧
1
目录1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.SQL语句要统一成大写.....................................................3在进行多个表连接时,FROM中的表的顺序要按照记录数由多到少的顺序来排列可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.(Oracle)..........3涉及到多表检索时,明确地为每个字段指定表名...............................3对于经常使用的SQL语句(循环处理中使用的SQL等),可以通过预编译,绑定变量来对于索引列不要执行NULL值的检索..........................................4对于索引列,不要使用"NOT","!=","<>"比较运算............................4对于索引列不要使用函数和计算式...........................................4尽可能将操作移至等号右边.................................................5对于多键值索引,要按照索引的定义顺序来使用...............................5不要通过LIKE运算来执行中间一致或后方一致的检索..........................5去掉没有意义的GROUPBY,ORDERBY子语....................................5WHERE语句中不要使用NOTIN或者HAVING....................................6尽量避免较多地使用子查询.................................................6避免不同类型的查询条件...................................................6有使用IN或者EXISTS的语句吗?...........................................6如果DBMS能够产生执行计划,验证一下是否是最优的SQL?....................6避免全表扫描的查询方式...................................................7当有多个索引可供选择时,使用的是DB设计者所希望的索引吗..................7调整SQL后执行代价变得比原来更低了吗?...................................7在循环处理中,是否存在执行大量SQL语句的情形.............................7SELECT子句中避免使用'*'(Oracle).....................................7
(Oracle)......................................................................3
提高性能......................................................................4
2
语句要1.SQL语句要统一成大写
原因:SQL语句转换成大写,可缩短些SQL的解析时间.通过统一成大写,可提高SQL的再利用率,缩短SQL解析时间.×○select*froma_tableSELECT*FROMA_TABLE
在进行多个表连接时,2.在进行多个表连接时,FROM中的表的顺序要按照记录数由多到少的顺序来排列(Oracle)
原因:ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名×○
子句的末尾.Oracle)3.可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.Oracle)(
原因:ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.×
SELECT…FROMEMPEWHERESAL>50000ANDJOB='MANAGER'AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);
○
SELECT…FROMEMPEWHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL>50000ANDJOB='MANAGER';
涉及到多表检索时,4.涉及到多表检索时,明确地为每个字段指定表名
原因:通过在A_TABLE,B_TABLE中指定别名A,B,就可不再需要调查A_ITEM,B_ITEM,A_KEY,B_KEY是哪儿个表中的项目,从而缩短SQL解析时间.(为方便编码可以为表名指定别名)×SELECTA_ITEMFROMA_TABLE,B_TABLEWHEREA_KEY=B_KEY;○SELECTA.A_ITEM,B.B_ITEMFROMA_TABLEA,B_TABLEBWHEREA.A_KEY=B.B_KEY;
3
语句(,可以通过预编译5.对于经常使用的SQL语句(循环处理中使用的SQL等)可以通过预编译,绑定变量,可以通过预编译,来提高性能
原因:由于SQL可以被再利用,所以可缩短SQL解析时间.×○SELECTCOUNT(*)FROMA_TABLEAWHEREA.KEY='0';SELECTCOUNT(*)FROMA_TABLEAWHEREA.KEY='1';:VAR='0';SELECTCOUNT(*)FROMA_TABLEAWHEREA.KEY=:VAR;:VAR='1';SELECTCOUNT(*)FROMA_TABLEAWHEREA.KEY=:VAR;
6.对于索引列不要执行NULL值的检索
原因:NULL检索是指[查找没有的东西],所以如果不全部都调查的话无法判断出是有还是没有.索引只做成[有]的数据.调整成不执行NULL检索的SQL,或者修改表的定义×○
SELECT*FROMA_TABLEAWHEREA.KEYISNULL;
对于索引列,不要使用"NOT""!=","<>"比较运算"NOT",7.对于索引列,不要使用"NOT","!=","<>"比较运算
原因:同5×○
SELECT*FROMA_TABLEAWHEREA.KEY!=1;SELECT*FROMA_TABLEAWHEREA.KEY<1ORA.KEY>1;
×
SELECT*FROMA_TABLEAWHERENOTEXIST(SELECT*FROMB_TABLEB
WHEREB.KEY=A.KEY);
○
8.对于索引列不要使用函数和计算式
原因:索引将不能发挥索引的作用.×○
SELECT*FROMA_TABLEAWHERETO_CHAR(A.KEY,'YYYYMMDD')='20030101'SELECT*FROMA_TABLEA
4
WHEREA.KEY=TO_DATE('20030101','YYYYMMDD');
○
SELECT*FROMA_TABLEAWHEREA.KEYBETWEENTO_DATE('20030101000000','YYYYMMDDHH24MISS')ANDTO_DATE('20030101235959','YYYYMMDDHH24MISS');
9.尽可能将操作移至等号右边
原因:任何对列的操作都将导致表扫描,它包括数据库函数,计算表达式等等,查询时要尽可能将操作移至等号右边.×○
对于多键值索引,10.对于多键值索引,要按照索引的定义顺序来使用
原因:如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引.提示:需要修改索引的结合顺序或者重新设定复合索引.可能会对其他业务有影响,所以需要经过充分讨论后再作决定.新追加的索引对更新/检索方面的性能都可能会有极大影响,更需要十分慎重.×
在按照A.KEY1+A.KEY2+A.KEY3来定义索引的情况下SELECT*FROMA_TABLEAWHEREA.KEY2='KEY2'ANDA.KEY3='KEY3';
○
SELECT*FROMA_TABLEAWHEREA.KEY1='KEY1'ANDA.KEY2='KEY2';
运算来执行中间一致后方一致的来执行中间一致或11.不要通过LIKE运算来执行中间一致或后方一致的检索
原因:中间或后方一致性检索时,索引是无效的.提示:重新分析一下看是否真的需要中间一致或后方一致检索,如果真的需要的话,可能需要重新规划一下业务流程.××○
SELECT*FROMA_TABLEAWHEREA.KEYLIKE'%XYZ';SELECT*FROMA_TABLEAWHEREA.KEYLIKE'%EFG%';SELECT*FROMA_TABLEAWHEREA.KEYLIKE'ABC%';
BY,12.去掉没有意义的GROUPBY,ORDERBY子语
原因:对于数据库来说ORDERBY,GROUPBY执行起来最耗费资源的处理.
5
提示:使用ORDERBY时,需要确认真的需要进行排序处理吗?×
SELECTCOUNT(*),A.ITEMFROMA_TABLEAWHEREA.ITEM='ABC'GROUPBYA.ITEM;#A.ITEM只抽取1条数据,所以GROUPBY结果也是1行,没有意义
○
SELECTCOUNT(*)FROMA_TABLEAWHEREA.ITEM='ABC';
语句中不要13.WHERE语句中不要使用NOTIN或者HAVING
原因:提示:考虑使用NOTEXISTS×○
14.尽量避免较多地使用子查询
原因:提示:可用连接实现的场合,就尽量避免使用子查询.×○
避免不同类型不同类型的查询条件15.避免不同类型的查询条件
原因:默认的类型转会导致索引变成无效.×○
SELECT*FROMA_TABLEAWHEREA.NUMBER_COL='123';SELECT*FROMA_TABLEAWHEREA.NUMBER_COL=123;
的语句吗?16.有使用IN或者EXISTS的语句吗?
原因:提示:使用本身是没有问题的,但是需要确认.如果这样的SQL耗时的话,能否用EXISTS代替IN,或者用IN代替EXISTS,通过改写或许可以使SQL变快,视具体情况来定×○
能够产生执行计划,SQL?17.如果DBMS能够产生执行计划,验证一下是否是最优的SQL?
原因:
提示:检查/调整的方法:在返回相同结果的SQL中,I/O代价小的才是好的SQL;同一SQL的首次执行和以后的执行,因为缓冲的原因会有差异,所以在同一条件下的比较很
6
重要.
×○
18.避免全表扫描的查询方式
原因:如果样本很少的话(几件或几十件,一个DISKI/O就可以取得的那种程度),应该是没有问题的.但是一般来说要严禁整表扫描的.提示:仔细确认一下:SQL语句的写法没有问题吗?是否忘记了使用索引?索引的使用方法没有问题吗?×○
当有多个索引可供选择时,19.当有多个索引可供选择时,使用的是DB设计者所希望的索引吗
原因:使用能最大限度(效率高)地提取到数据的索引.一般来说,能够抽取原始数据的1/50以内的索引,才是效率高的.如果不具备这样的条件的话,或许全表扫描也许会很
快.
×○
后执行代价变原来更低了吗更低了吗?20.调整SQL后执行代价变得比原来更低了吗?
原因:对于执行代价的绝对值是没有意义;变更前后的差才是最重要的.×○
在循环处理中,语句的情形21.在循环处理中,是否存在执行大量SQL语句的情形
原因:即使单个SQL语句的处理时间短,但这个SQL语句因循环处理而被发行了几万次,几十万次的话..循环处理中的SQL语句,通常要注意循环次数(即使不准确但能够大致估.算出来也是重要的).提示:能否考虑循环处理之外的方法,比如是否能通过批处理来实现相同的功能.×○
子句中避免使用'Oracle)22.SELECT子句中避免使用'*'(Oracle)
原因:ORACLE在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.×
7
○
8
