切身实际总结的Sql性能总结.docx
- 文档编号:10265081
- 上传时间:2023-02-09
- 格式:DOCX
- 页数:19
- 大小:21.73KB
切身实际总结的Sql性能总结.docx
《切身实际总结的Sql性能总结.docx》由会员分享,可在线阅读,更多相关《切身实际总结的Sql性能总结.docx(19页珍藏版)》请在冰豆网上搜索。
切身实际总结的Sql性能总结
SQL的编写技巧
目录
1.SQL语句要统一成大写3
2.在进行多个表连接时,FROM中的表的顺序要按照记录数由多到少的顺序来排列(Oracle)3
3.可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
(Oracle)3
4.涉及到多表检索时,明确地为每个字段指定表名3
5.对于经常使用的SQL语句(循环处理中使用的SQL等),可以通过预编译、绑定变量来提高性能4
6.对于索引列不要执行NULL值的检索4
7.对于索引列,不要使用"NOT"、"!
="、"<>"比较运算4
8.对于索引列不要使用函数和计算式4
9.尽可能将操作移至等号右边5
10.对于多键值索引,要按照索引的定义顺序来使用5
11.不要通过LIKE运算来执行中间一致或后方一致的检索5
12.去掉没有意义的GROUPBY、ORDERBY子语5
13.WHERE语句中不要使用NOTIN或者HAVING6
14.尽量避免较多地使用子查询6
15.避免不同类型的查询条件6
16.有使用IN或者EXISTS的语句吗?
6
17.如果DBMS能够产生执行计划,验证一下是否是最优的SQL?
6
18.避免全表扫描的查询方式7
19.当有多个索引可供选择时,使用的是DB设计者所希望的索引吗7
20.调整SQL后执行代价变得比原来更低了吗?
7
21.在循环处理中,是否存在执行大量SQL语句的情形7
22.SELECT子句中避免使用’*’(Oracle)7
1.SQL语句要统一成大写
原因:
SQL语句转换成大写,可缩短些SQL的解析时间。
通过统一成大写,可提高SQL的再利用率,缩短SQL解析时间。
×
select*froma_table
○
SELECT*FROMA_TABLE
2.在进行多个表连接时,FROM中的表的顺序要按照记录数由多到少的顺序来排列(Oracle)
原因:
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名
×
○
3.可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
(Oracle)
原因:
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
×
SELECT…FROMEMPE
WHERESAL>50000
ANDJOB=‘MANAGER’
AND25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO);
○
SELECT…FROMEMPE
WHERE25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO)
ANDSAL>50000
ANDJOB=‘MANAGER’;
4.涉及到多表检索时,明确地为每个字段指定表名
原因:
通过在A_TABLE、B_TABLE中指定别名「A」、「B」,就可不再需要调查A_ITEM、B_ITEM、A_KEY、B_KEY是哪儿个表中的项目,从而缩短SQL解析时间。
(为方便编码可以为表名指定名)
×
SELECTA_ITEM
FROMA_TABLE,B_TABLE
WHEREA_KEY=B_KEY;
○
SELECTA.A_ITEM,B.B_ITEM
FROMA_TABLEA,B_TABLEB
WHEREA.A_KEY=B.B_KEY;
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_TABLEA
WHEREA.KEYISNULL;
○
7.对于索引列,不要使用"NOT"、"!
="、"<>"比较运算
原因:
同5
×
SELECT*FROMA_TABLEA
WHEREA.KEY!
=1;
○
SELECT*FROMA_TABLEA
WHEREA.KEY<1ORA.KEY>1;
×
SELECT*FROMA_TABLEA
WHERENOTEXIST(
SELECT*FROMB_TABLEB
WHEREB.KEY=A.KEY);
○
8.对于索引列不要使用函数和计算式
原因:
索引将不能发挥索引的作用。
×
SELECT*FROMA_TABLEA
WHERETO_CHAR(A.KEY,'YYYYMMDD')='20030101'
○
SELECT*FROMA_TABLEA
WHEREA.KEY=TO_DATE('20030101','YYYYMMDD');
○
SELECT*FROMA_TABLEA
WHEREA.KEYBETWEENTO_DATE('20030101000000','YYYYMMDDHH24MISS')ANDTO_DATE('20030101235959','YYYYMMDDHH24MISS');
9.尽可能将操作移至等号右边
原因:
任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
×
○
10.对于多键值索引,要按照索引的定义顺序来使用
原因:
如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引。
提示:
需要修改索引的结合顺序或者重新设定复合索引。
可能会对其他业务有影响,所以需要经过充分讨论后再作决定。
新追加的索引对更新/检索方面的性能都可能会有极大影响,更需要十分慎重。
×
在按照A.KEY1+A.KEY2+A.KEY3来定义索引的情况下
SELECT*FROMA_TABLEA
WHEREA.KEY2='KEY2'ANDA.KEY3='KEY3';
○
SELECT*FROMA_TABLEA
WHEREA.KEY1='KEY1'ANDA.KEY2='KEY2';
11.不要通过LIKE运算来执行中间一致或后方一致的检索
原因:
中间或后方一致性检索时,索引是无效的。
提示:
重新分析一下看是否真的需要中间一致或后方一致检索,如果真的需要的话,可能需要重新规划一下业务流程。
×
SELECT*FROMA_TABLEA
WHEREA.KEYLIKE'%XYZ';
×
SELECT*FROMA_TABLEA
WHEREA.KEYLIKE'%EFG%';
○
SELECT*FROMA_TABLEA
WHEREA.KEYLIKE'ABC%';
12.去掉没有意义的GROUPBY、ORDERBY子语
原因:
对于数据库来说ORDERBY、GROUPBY执行起来最耗费资源的处理。
提示:
使用ORDERBY时,需要确认真的需要进行排序处理吗?
×
SELECTCOUNT(*),A.ITEMFROMA_TABLEA
WHEREA.ITEM='ABC'
GROUPBYA.ITEM;
#A.ITEM只抽取1条数据,所以GROUPBY结果也是1行,没有意义
○
SELECTCOUNT(*)FROMA_TABLEA
WHEREA.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变快,视具体情况来定
×
○
17.如果DBMS能够产生执行计划,验证一下是否是最优的SQL?
原因:
提示:
检查/调整的方法:
在返回相同结果的SQL中,I/O代价小的才是好的SQL;同一SQL的首次执行和以后的执行,因为缓冲的原因会有差异,所以在同一条件下的比较很重要。
×
○
18.避免全表扫描的查询方式
原因:
如果样本很少的话(几件或几十件,一个DISKI/O就可以取得的那种程度),应该是没有问题的。
但是一般来说要严禁整表扫描的。
提示:
仔细确认一下:
SQL语句的写法没有问题吗?
是否忘记了使用索引?
索引的使用方法没有问题吗?
×
○
19.当有多个索引可供选择时,使用的是DB设计者所希望的索引吗
原因:
使用能最大限度(效率高)地提取到数据的索引。
一般来说,能够抽取原始数据的1/50以内的索引,才是效率高的。
如果不具备这样的条件的话,或许全表扫描也许会很快。
×
○
20.调整SQL后执行代价变得比原来更低了吗?
原因:
对于执行代价的绝对值是没有意义;变更前后的差才是最重要的。
×
○
提示:
能否考虑循环处理之外的方法,比如是否能通过批处理来实现相同的功能。
21.在循环处理中,是否存在执行大量SQL语句的情形
原因:
即使单个SQL语句的处理时间短,但这个SQL语句因循环处理而被发行了几万次,几十万次的话...循环处理中的SQL语句,通常要注意循环次数(即使不准确但能够大致估算出来也是重要的)。
×
○
22.SELECT子句中避免使用’*’(Oracle)
原因:
ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。
×
○
SQL使用技巧
23.高效的sql语句
Sql优化方法
RBO(rule-basedoptimizer)
CBO
驱动表
1)2张行数不一致的表连接
表TAB1行数:
16,384行
表TAB2行数:
1行
×SELECTCOUNT(*)FROMTAB2,TAB1;
○SELECTCOUNT(*)FROMTAB1,TAB2;
2)3张表连接
×SELECT*
FROMEMPE,
LOCL,
CATC
WHEREE.emp_noBETWEEN1000AND2000
ANDE.cat_no=C.cat_no
ANDE.locn=L.locn;
○SELECT*
FROMLOCL,
CATC,
EMPE
WHEREE.emp_noBETWEEN1000AND2000
ANDE.cat_no=C.cat_no
ANDE.locn=L.locn;
Where语句顺序的效率
1)使用索引引起的where语句效率
使用AND语句时行数多的放在前面
×SELECT*
FROMempE
WHEREemp_sal>50000
ANDemp_type='MANAGER'
AND25<(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no);
○SELECT*
FROMempE
WHERE25<(SELECTCOUNT(*)FROMEMPWHERE
emp_mgr=E.emp_no)
ANDemp_sal>50000
ANDemp_type='MANAGER';
使用OR语句时,行数多的放在后面
×SELECT*
FROMempE
WHERE25<(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no)
OR(emp_sal>50000
ANDemp_type='MANAGER');
○SELECT*
FROMempE
WHERE(emp_sal>50000
ANDemp_type='MANAGER')
OR25<(SELECTCOUNT(*)FROMEMPWHEREemp_mgr=E.emp_no);
2)ROWID的使用
使用ROWID的WHERE语句效率最高。
SELECTROWID,...
INTO:
emp_rowid,...
FROMemp
WHEREemp.emp_no=56722
FORUPDATE;
UPDATEemp
SETemp.name=...,
WHEREROWID=:
emp_rowid;
3)减少访问次数
×SELECTemp_name,sal,grade
FROMemp
WHEREemp_no=0342;
SELECTemp_name,sal,grade
FROMemp
WHEREemp_no=0291;
○SELECTA.emp_name,A.sal,A.grade,
B.emp_name,B.sal,B.grade
FROMempA,empB
WHEREA.emp_no=0342
ANDB.emp_no=0291;
4)Where语句的索引的使用
(1)SUBSTR
×SELECTacc_name,trans_date,amount
FROMtransaction
WHERESUBSTR(account_name,1,7)='CAPITAL';
○SELECTacc_name,trans_date,amount
FROMtransaction
WHEREaccount_nameLIKE'CAPITAL%';
(2)!
=
×SELECTacc_name,trans_date,amount
FROMtransaction
WHEREamount!
=0;
○SELECTacc_name,trans_date,amount
FROMtransaction
WHEREamount>0;
(3)||
×SELECTacc_name,trans_date,amount
FROMtransaction
WHEREacc_name||acc_type='AMEXA';
○SELECTacc_name,trans_date,amount
FROMtransaction
WHEREacc_name='AMEX'
ANDacc_type='A';
(4)运算
×SELECTacc_name,trans_date,amount
FROMtransaction
WHEREamount+3000<5000;
○SELECTacc_name,trans_date,amount
FROMtransaction
WHEREamount<5000+3000;
SQL命令的使用秘诀
DECODE的使用
×SELECTCOUNT(*),SUM(salary)
FROMemp
WHEREdept_no=0020
ANDemp_nameLIKE'SMITH%';
SELECTCOUNT(*),SUM(salary)
FROMemp
WHEREdept_no=0030
ANDemp_nameLIKE'SMITH%';
○SELECTCOUNT(DECODE(dept_no,0020,'X',NULL)) D0020_count,
COUNT(DECODE(dept_no,0030,'X',NULL)) D0030_count,
SUM(DECODE(dept,0020,salary,NULL))D0020_sal,
SUM(DECODE(dept,0030,salary,NULL))D0030_sal
FROMemp
WHEREemp_nameLIKE'SMITH%';
表的别名的使用
×SELECTE.emp_no,name,tax_no,C.comp_code,comp_name
FROMcompanyC,empE
WHEREE.comp_code=C.comp_code;
○SELECTE.emp_no,E.name,E.tax_no,C.comp_code,C.comp_name
FROMcompanyC,empE
WHEREE.comp_code=C.comp_code;
去掉重复行
DELETEFROMempE
WHEREE.rowid>(SELECTMIN(X.rowid)
FROMempX
WHEREX.emp_no=E.emp_no);
表的行计数
SELECTCOUNT(有索引的列)FROMTRANS;
SELECTCOUNT(*)FROMTRANS;
SELECTCOUNT
(1)FROMTRANS;
用WHERE语句替换HAVING语句的使用
×SELECTregion,AVG(loc_size)
FROMlocation
GROUPBYregion
HAVINGregion!
='SYDNEY'
ANDregion!
='PERTH';
○SELECTregion,AVG(loc_size)
FROMlocation
WHEREregion!
='SYDNEY'
ANDregion!
='PERTH';
GROUPBYregion
使用表连接替代EXISTS使用
×SELECTemp_name
FROMemp
WHERE(emp_cat,sal_range)=
(SELECTMAX(category),MAX(sal_range)FROMemp_categories)
ANDemp_dept=0020;
○SELECTemp_name
FROMemp
WHEREemp_cat=(SELECTMAX(category)FROMemp_categories)
ANDsal_range=(SELECTMAX(sal_range)FROMemp_categories)
ANDemp_dept=0020;
使用EXISTS替代表连接
○SELECT...
FROMempE
WHEREEXISTS(SELECT'X'FROMdept
WHEREdept_no=E.dept_no
ANDdept_cat='A')
ANDE.emp_type='MANAGER';
使用EXISTS代替DISTINCT语句
×SELECTDISTINCTdept_no,dept_name
FROMdeptD,empE
WHERED.dept_no=E.dept_no;
○SELECTdept_no,dept_name
FROMdeptD
WHEREEXISTS(SELECT'X'
FROMempE
WHEREE.dept_no=D.dept_no);
使用NOTEXISTS代替NOTIN语句
×SELECT...
FROMemp
WHEREdept_noNOTIN(SELECTdept_no
FROMdept
WHEREdept_cat='A');
○SELECT...
FROMempE
WHERENOTEXISTS(SELECT'X'
FROMdept
WHEREdept_no=E.dept_no
ANDdept_cat='A');
使用unionall代替union语句
Union--------进行排序
UnionAll--------不排序
使用Union和IN代替OR语句
Loc_no,region上有索引
×SELECT...
FROMlocation
WHEREloc_id=10
ORregion='MELBOURNE';
○SELECT...
FROMlocation
WHEREloc_id=10
UNIONALL
SELECT...
FROMlocation
WHEREregion='MELBOURNE';
×SELECT...
FROMlocation
WHEREloc_id=10
ORloc_id=20
ORloc_id=30;
○SELECT...
FROMlocation
WHEREloc_idIN(10,20,30);
24.高效索引
索引的使用
使用索引时,要考虑以下因素:
1)索引列的计算
2)索引列的增加
3)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 切身 实际 总结 Sql 性能