没有合适的资源?快使用搜索试试~ 我知道了~
SQL 语句书写与性能调优规范
4星 · 超过85%的资源 需积分: 15 11 下载量 113 浏览量
2011-05-03
10:28:40
上传
评论
收藏 150KB DOC 举报
温馨提示
试读
22页
SQL 语句书写与性能调优规范很好 很强大! SQL语句各种写法的性能优劣,但是如果将系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一
资源推荐
资源详情
资源评论
SQL 语句书写和性能优化的基本规范
在应用系统开发初期,由于应用数据库中的数据比较少,对于查询 SQL 语
句的编写等体会不出 SQL 语句各种写法的性能优劣,但是如果将系统提交实际
应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决
的最主要的问题之一。系统优化中一个很重要的方面就是 SQL 语句的优化。对
于大量数据,劣质 SQL 语句和优质 SQL 语句之间的速度差别可以达到上百倍,
可见对于一个系统不是简单地能实现其功能就可,而是要写出高质量的 SQL 语
句,提高系统的可用性。
由于业务量的连年增加和多年的数据积累,数据库中的表的记录数大大增加,
很多表的数据达到了百万级数量,一些表到了千万级数据量。因此,SQL 语句
的编写技术将直接影响到系统的运行性能,一些多表的组合关联查询可轻易造
成系统死机或数据库崩停,严重的会造成未完成事务无法回滚引起数据库无法
重起。为保证新开发的项目和程序不存在严重的运行性能问题,对 SQL 语句强
制规定和一些建议如下:
一、 关联查询相关问题:
1. 严格禁止 3 个表(含 3 个表)以上的关联查询,无论表内记录数多少。
Select processtype, DS_PROCESS.DS_PROCESS_ID,
DS_PROCESS.CREATEPERSON,
DS_PROCESS.STARTDATETIME,
DSB_APPLY.DSB_APPLY_ID,
ds_activity.executeorg, ds_activity.step
from ds_activity, DS_PROCESS, DSB_APPLY
where
DS_PROCESS.DS_PROCESS_ID=DSB_APPLY.DS_PROCESS_ID
and DS_PROCESS.PROCESSTYPE=3 and
DS_PROCESS.NEXTACTIVITYTYPE=2 and
DS_PROCESS.CREATEPERSON=3394
and
ds_activity.ds_activity_id=ds_process.ds_activity_id
order by DSB_APPLY.DSB_Apply_ID desc
以上语句严格禁止,只要有一表超过 100 万记录,一般 10 分钟内查询
出的可能性不大。性能与表间书写次序有很大关系。
2. 严格禁止两表均超过 5000 条以上记录的两个表的关联查询。
两表均超过 5000 条以上记录的关联表查询要填定<<关联 SQL 语句编
写审批表>>,经审批同意才能按审批同意的语句使用。
性能分析:
以下 select 的情况:
选择一个好的表联接顺序(这是一个比较重要的原则)
当在 WHERE 子句中有多个表联接时,WHERE 子句中排在最后的表应
当是返回行数可能最少的表,有过滤条件的子句应放在 WHERE 子句中
的最后。
如:设从 emp 表查到的数据比较少或该表的过滤条件比较确定,能大大
缩小查询范围,则将最具有选择性部分放在 WHERE 子句中的最后:
A. 性能好:
select * from emp e,dept d where d.deptno >10 and
e.deptno =30 ;
如果 dept 表返回的记录数较多的话,上面的查询语句会比下面的查
询语句响应快得多。
B. 性能差:
select * from emp e,dept d where e.deptno =30 and
d.deptno >10 ;
以下 update 的情况
A.(性能好,其中 tmp_cx_ptc 为小表,riskspec 为大表,小表在前
效率高)
exec sql update tmp_cx_ptc set chktype=(select
saleattr from riskspec where
tmp_cx_ptc.appno=riskspec.appno);
B.(性能很差,其中 tmp_cx_ptc 为小表,riskspec 为大表,大表在
前效率并)
exec sql update tmp_cx_ptc set chktype=(select
saleattr from riskspec where
riskspec.appno=tmp_cx_ptc.appno);
C.(通过 rowid 性能更差,且有可能会有不确定结果)
exec sql declare replcur cursor for
select rowid,appno
into :irowid,:iappno
from tmp_cx_ptc;
exec sql open replcur;
exec sql fetch replcur;
if (sqlca.sqlcode==0)
gexit=0;
else
gexit=1;
while (gexit==0)
{
//销售属性
exec sql select unique saleattr into :isaleattr
from riskspec
where appno=:iappno;
if (sqlca.sqlcode!=0)
strcpy(isaleattr," ");
//是否自动续保
exec sql select dealid into :idealid
from conensdeal
where policyno =:iappno;
if (sqlca.sqlcode!=0)
strcpy(idealid," ");
exec sql update tmp_cx_ptc
set chktype=:isaleattr,
oper=:idealid
where rowid=:irowid;
exec sql fetch replcur;
if (sqlca.sqlcode!=0)
gexit=1;
}
exec sql close replcur;
exec sql free replcur;
二、 关于事务提交的处理方法
提交事务原则上应在后台程序进行,严格禁上 C/S 结构、B/S 结构类系统在
前台处理程序中提交离散性、等待性事务。事务开始与事务结束之间,只能
有 insert,update,delete 语句,且应集中编写,一次性提交。事务开始与结
束之间,禁上有 select 语句存在。(但可以有开发工具本身的不引起数据库
操作的函数和语句,也可有 PB 自身的自动提交功能)。
示例:
(可以)
begin work
insert
update
a=1
delete
commit/rollback
(禁止)
begin work
select
go to…
update
commit/rollback
调用存储过程或函数或应用服务(此过程、函数、应用包含或引起离散性操
作数据库,如离散性的 select,update 等,或需等待运行时间超过 10 秒种的后
台程序运行完成、或此过程中有人工操作界面或等待按键功能,此类情况一律严
格禁止存在)
三、关于多用户并发抢占唯一流水号的处理
多用户并发抢号机制建议采用“共享-抢占-重试”的方式进行,不建议采用“抢
点-锁定-解锁”的方式进行。
抢号示例:
(建议)
select max(no) into :ino from aa
i=1
ino++;
while i<1000
{
inset into bb ( no ) valuse (:ino)
if 成功
exlt
else
select max(no) into :ino from aa
ino++;
}
显示号码
(不建议)
select max(no) into :ino from aa
显示号码
delete from bb
insert into bb ( no ) valuse (:ino);
lock bb(或 lock 此记录)
...
unlock bb
多用户并发授权建议用显式产生临时表的方式进行,不建议采用采用固
定表通过记录删除、插入的方式进行。
(建议)
select * from menu where userid=”aaaaa”
into temp menu_temp
再通过临时 menu_temp 显示各用户授权菜单。
…
(不建议)
delete from menu_user(menu_user 为非临时表)
select * from menu where userid=”aaaaa”
insert to menu_user
再过 menu_user 显示相应的各用户菜单权限
四、关于生成临时表的处理
1. 生成临时表要加上“with no log”防止逻辑日志空间不足。
select *
from aa
into temp bb
with no log
2. 使用临时表可以加速查询
把表的一个子集进行排序并创建临时表,有时能加速查询。它有助于避免多
重排序操作,而且在其他方面还能简化优化器的工作。例如:
SELECTcust.name,rcvbles.balance,……othercolumns
FROMcust,rcvbles
WHEREcust.customer_id=rcvlbes.customer_id
ANDrcvblls.balance>0
ANDcust.postcode>“98000”
ORDERBYcust.name
如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放
在一个临时文件中,并按客户的名字进行排序:
SELECTcust.name,rcvbles.balance,……othercolumns
FROMcust,rcvbles
WHEREcust.customer_id=rcvlbes.customer_id
ANDrcvblls.balance>0
ORDERBYcust.name
INTOTEMPcust_with_balance
然后以下面的方式在临时表中查询:
SELECT* FROMcust_with_balance
WHEREpostcode>“98000”
临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了
磁盘 I/O,所以查询工作量可以得到大幅减少。
3. 如果有排序和连接操作,可以先 select 数据到一个临时表中,然后再对
临时表进行处理。因为临时表是建立在内存中,所以比建立在磁盘上表
操作要快的多。
如:
SELECTtime_records.*,case_name
FROMtime_records,OUTERcases
WHEREtime_records.client="AA1000"
ANDtime_records.case_no=cases.case_no
ORDERBYtime_records.case_no
这个语句返回 34 个经过排序的记录,花费了 5 分钟 42 秒。而:
SELECTtime_records.*,case_name
FROMtime_records,OUTERcases
WHEREtime_records.client="AA1000"
ANDtime_records.case_no=cases.case_no
INTOtempfoo;
SELECT*fromfooORDERBYcase_no
返回 34 条记录,只花费了 59 秒。
4. 如何加快 SELECTCOUNT(DISTINCT)的速度
通常“SELECTCOUNT(DISTINCT)”这样的操作要花费比较长的时间,
可以:
SELECTUNIQUExxxINTOTEMPXXX"然后再"SELECTCOUNT(*)
FROMTEMPXXX"
剩余21页未读,继续阅读
资源评论
- oim5nu2014-10-15有参考作用,但要实践验证
zaicaiwang
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功