分类： DB2/Informix
2005-07-11 10:52:50
3.6. 关于语句写法
语句的效率与SQL 语句的写法有很大关系。在几种写法中选择一个合适的写法相当重要，有时效率会急剧提高。因此，在效率有问题的地方多检查自己语句的写法，有可能会取得重大突破。以下是一些实例：
(1).在保单表中查找“
select * from rta1 where bm_cert[1,7]=”
该语句运行前设置set explain on，运行后查看sqexplain.out 结果如下：
QUERY:
select * from rta1
where bm_cert[1,7]="0030107"
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) hsx.rta1: INDEX PATH 8
(1) Index Keys: bm_cert
Lower Index Filter: hsx.rta1.bm_cert[1,7] = '0030107'
显示的搜索路径为索引路径，但实际上效率很低。现改为如下写法：
select * from rta1 where bm_cert > = ”00301010000000000000”
and bm_cert<=”00301019999999999999”
该语句运行前设置set explain on，运行后查看sqexplain.out 结果如下：
QUERY:
select * from rta1 where bm_cert >= "003010100000000"
and bm_cert<=”00301019999999999999”
Estimated Cost: 1
Estimated # of Rows Returned: 3
1) hsx.rta1: INDEX PATH
(1) Index Keys: bm_cert
Lower Index Filter: hsx.rta1.bm_cert >= '003010100000000'
显示与第一个写法无明显差别，实际上第二种写法效率远远高于第一种。实际测试第一种写法的时间需要2 分41 秒而第二种写法的执行时间只需14 秒。
因此， 程序中最好不要使用bm_cert[x,y]=”???” 这种形式， 而改为 bm_cert>=”???” 这种形式。
(2). 在程序中，我们经常做这样的工作，先判断表内是否有该条数据，如果有就 update 该条数据，否则就插入一条。一般都写成如下形式：
select .. from . where key=t_key
if status=notfound then
insert into ..
else
update ..
end if
实际上，写成如下形式效率更高
update . where key=t_key
if status=0 and sqlca.sqlerrd[3]=0 then
insert into ..
end if
因为当该条数据存在时，只做一次写操作。
(3). 在应用系统中有这样一条SQL 语句。
select print_name from validate_item
where (acc_book_type = t_acc_book_type
and acc_book_code = t_acc_book_code
and （center_code = t_center_code or center_code=”
and item_code = t_item_code
and direction_idx = t_dir_idx_val1
and direction_other = t_dir_oth_val1)
( validate_item 表中acc_book_type , acc_book_code ,
center_code , item_code , direction_idx , direction_other 为唯一索引) 该语句的效率不高，改写为以下形式后效率得到了很大提高。在存储过程中，上面的写法此存储过程执行需用时1 分多钟，改为下面的写法后，用 时仅需1 秒.
select print_name
into t_str
from validate_item
where (acc_book_type = t_acc_book_type
and acc_book_code = t_acc_book_code
and center_code = t_center_code
and item_code = t_item_code
and direction_idx = t_dir_idx_val1
and direction_other = t_dir_oth_val1)
or (acc_book_type = t_acc_book_type
and acc_book_code = t_acc_book_code
and center_code = "000000"
and item_code = t_item_code
and direction_idx = t_dir_idx_val1
and direction_other = t_dir_oth_val1);
在SQL 语句中，where 条件中有or 的语句，where 条件尽量用大组合，而不要用小组合。
3.7. 关于使用put , execute 来代替insert 提高效率在做数据转换或登录大批量数据时，用put 或execute 替代insert ，将使效率大幅度提高。因为在循环体内，减少了对insert 的语法检查及预处理的时间。
Put 的用法如下：
let t_pre_1 = "insert into rta1 values ",
"(?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?)"
prepare pre_rta1_1 from t_pre_1
declare cur_ins_1 cursor for pre_rta1_1
open cur_ins_1
循环体(while , for , foreach)
{
put cur_ins_1 from p_rta1.*
}
close cur_ins_1
execute 的语法如下：
let t_pre_1 = "insert into rta1 values "
"(?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?,?,?,?,?,?,?,",
"?,?,?,?)"
prepare pre_rta1_1 from t_pre_1
循环体(while , for , foreach)
{
execute pre_rta1_1 using p_rta1.*
}
使用put 必须注意以下内容：
u put 语句必须在事物中。
u put 语句执行完后，马上从库中select 不一定能取到数
3.8. 使用隔离级别（isolation）
共有四种隔离级别
Dirty read
Commited read
Cursor stability
Repeatable read
<一> dirty read
可能读到未提交（commited）的记录，可能读到最后被回滚的记录，该记录是一个phantom。 在一些查询中，使用此隔离级别，可提高效率。因为不受其他进程锁表，锁记录的影响。
<二> committed read
读到的数据均为提交后的数据。两个进程可同时update 同一条记录。（缺省的隔离级别)
<三> cursor stability
当隔离级别设置为cursor stability 时，在某一游标内，当前记录不能被其他
的进程update,delete ,但游标内其他的记录可被其他进程update ,delete 。
<四> repeatable read
当隔离级别设置为repeatable read 时，在某一游标内有两种情况
1． 该游标的where 子句有索引，则满足条件的所有记录均不能被其他进程
update ,delete 。（该索引的搜索树的所有节点）
2． 该游标的where 子句不在索引上，则该表的所有记录均不能被其他进程 update,delete 。
所以，当使用repeatable read 时，最好有索引，否则相当于锁表，极大损害系统效率。
3.9. 使用优化器（update statistics）
对表，存储过程的优化的语法如下：
update statistics [low ] for procedure ..
[ medium] for table tablename [colname] resolution percent . conf
[high ]
优化使系统表的信息与实际一致，使搜索树的路径最优。
同时，整理索引树。
例：
1. update statistics for table
2. update statistics for procedure
3. update statistics for table rta1(bm_cert)
4. update statistics high for table rta1(bm_cert) resolution 0.5
5. update statistics medium for table rta1 resolution 0.5 0.89
3.10. 使用复合索引
建复合索引的原则：
1. 查询语句中不定的字段尽量放在复合索引的后面。
如 有查询语句select * from person where name=”王 艳” sex=”
则索引建成如下形式效率最好。
Create index idx_person on person(name, ,birthday ,sex)
而不要建成
Create index idx_person on person(birthday,sex,name)
2. 取值范围大，可能值多的尽量放在复合索引的前面。
性别只有两种取值“男“”女“ ，最好放在最后。
姓名可能值最多，最好放在最前，则索引为：
Create index idx_person on person(name,birthday,sex) 若与第一条有冲突，第一条优先。
3． 有单独作为条件的，最好放在前面。
如果如下的sql 语句出现的频率较高
select * from person where name >” “
或select * from person where name
matches “ “
则name 字段最好放在索引的前面。
复合索引的建立必须根据整个系统的sql 语句，均衡的考虑。
4. INformIX 数据库数据复制（CDR）
4.1. 基本概念
4.1.1. 数据复制类型：同步、异步
l 同步：源数据更新同时马上更新目标数据，系统使用2 步提交技术保证了数据的完整性。
2 步提交要求目标主机和网络环境有较高的可靠性。即任何时候都是可用的。
l 异步：目标数据的复制可以根据应用要求设定，但最终要同步所有的数据。在一般的开放系统中，异步方式更为常用，因为它忽略了主机和网络的性能，这往往是一些系统的致命问题。
异步复制又分为如下几种模式：
1) 主从模式：所有数据更新都是从原到目标，是单向数据复制。如图示：
2) 工作流模式：数据在服务器间一一流转，也是单向数据复制。如图示：
3) 任意更新模式：所有定义的数据库之间都互相具有读写权限，它的弊端是容易引起复制冲突，当然CDR 也提供了冲突解决规则。如图示：
4.1.2. 复制机制：基于触发器和基于日志 s5NT3uZ
l 触发器方式：这种将与系统竞争对数据库表读写的资源，对正常业务效率有较大影响。并且需要通过应用保证数据完整性。该种方式不可取。
l 日志方式：由于是采取捕获事物信息的方式，因此不会与系统竞争对数据库表读写资源，效率高。且由系统保持数据一致性、完整性。并有可集中管理的优势。
4.1.3. CDR 的局限性
l 仅提供IDS 之间的复制
l 不能与HDR 并行使用。
l 复制的表不能用view
l 一个数据库服务器只能参与一个复制服务
一旦建立了复制服务器：
l 不能用drop table；rename talbe； alter fragment 等SQL
l 复制的表必须有primary key,且不能修改
l 不能对字段增删改
l 不能建立聚合索引
4.1.4. CDR 如何实现
l 复制过程主要包括4 个部分：
1． 捕获事物
CDR 使用基于日志的事物捕获机制获得事物信息。这种方式不会与正常事物竞争对表的存取，所以能获得较好的性能。CDR 读log 后加上标志，并将它传送到下一步，复制评估。
2． 复制评估
CDR 要确定每一条记录的最后值及更新发生时间，以便决定是否进行复制。评估是并行处理的以保证较高性能。评估后CDR 将数据置于消息队列。
3． 数据分发
消息队列存取方式可以保证所有数据都可以准确到达目标SERVER，它可以忽略主机或网络的状态。当网络发生故障数据存放在本地发送消息队列中，当网络恢复后消息队列再自动将数据分发。
4． 数据同步 :
CDR 用同步处理保证数据能正确的复制到目标SERVER,当复制数据正确存储到目标SERVER 后，目标SERVER 向源SERVER 发送确认信息。
l CDR 的几个组成部分：
1． 复制SERVER:由一个dbserver 组成，与成员SERVER 可以在不同主机或同一主机上。
2． 复制对象：包括database table columns 还有一些option 如冲突解决规则和范围。
3． 成员：目标dbserver database table columns 限制条件：必须有primary key 只能复制单表；不能使用Join 和subquery.
4． 全局目录：是管理复制功能的一系列table , 全局目录在每个dbserver 都有一份,可集中管理。
l 复制规划
1． 确定系统拓扑结构，并制作如“三.复制实例”中的表3-1，拓扑如图示：采用树型主从模式。
2． 有关逻辑日志：逻辑日志的大小应至少容纳一次复制所复制的数据。
3． 有关消息队列：消息队列是一个dbspace,它的大小不仅与逻辑日志有关，而且与网
络中断期间所需缓冲的数据多少有关。（Onconfig 中定义）
4． 影子表：如果定义了冲突解决规则，就要定义影子表，其大小根据规则不同而不同，
如基于时间戳的冲突解决规则需要是复制表的2 倍。
5． 假脱机目录：可以指定事物中断后数据暂存目录（缺省：/tmp）。
6． 复制线程个数：是个均衡值即要保证性能也不能浪费（onconfig 中定义）。
7． 网络带宽：
流量计算如公式：(#transactions per hour)*(#bytes)*(#sites)*130%
8． 应用分析：估算每天大约有多少数据增删改，分析传送时间安排。
9．确定要复制的database 用的是unbuffer 方式的逻辑日志。
4.2. CDR 配置实例
4.2.1. 系统环境准备
1．数据复制源主机：IBM 370 SCO 5.0.5 IDS 7.31.UC2 CBPS 7
2．数据复制目的主机：IBM 250 SCO 5.0.5 IDS 7.31.UC2
3．检查逻辑日志（logical log）的大小
4．建立发送、接受队列dbspace
5．确定参与复制的Informix Server、database 及table
cdr 工作表：
Host name Informixserver group database table
Picc21 ld370222_tcp Grp_picc21 Picc2 *
Ibm500 ld370220_tcp Grp_ibm500 Picc2 *
省公司dbserver
地市1 dbserver 地市2 dbserver 地市3 dbserver
4.2.2. 确定复制环境
1. 复制模式：单向数据复制
2. 复制时间间隔：立即更新
4.2.3. 建立picc21 和ibm500 的数据库之间的互访
1) 分别在picc21 和scosysv 上编辑$INformIXDIR/etc/sqlhosts 文件如下：
grp_picc21 group - - i=1
ld370222_shm onipcshm picc21 ls370222_shm g=grp_picc21
ld370222_tcp ontlitcp picc21 ls370222_tcp g=grp_picc21
grp_ibm500 group - - i=2
ld370220_shm onipcshm ibm500 ls370220_shm g=grp_ibm500
ld370220_tcp ontlitcp ibm500 ls370220_tcp g=grp_ibm500
2) 分别在picc21 和ibm500 上的/etc/hosts 文件包括如下2 项：
176.176.21.78 picc21 picc21
176.176.66.103 ibm500
3) 分别在picc21 和ibm500 上的/etc/services 文件包括如下2 项:
ls370222_tcp 5889/tcp
ls370220_tcp 3720/tcp
4) 在picc21 上的/etc/hosts.equiv 文件包括如下1 项:
ibm500
5) 在ibm500 上的/etc/hosts.equiv 文件包括如下1 项:
picc21
以上各步完成picc21 和ibm500 的数据库互访，可以通过dbaccess-connect 验证。
4.2.4. 定义replicate server
执行上$INformIXDIR/cdr/crtser.sh
脚本内容如下：
cdr define server -s sendq -r recvq -A /tmp/ats -R /tmp/ris -I
grp_picc21
注：确保picc21 上的dbspaces sendq 和recvq 已建立
确保picc21 上的/tmp/ats 和/tmp/ris 目录已建立
4.2.5. 确定复制服务器已建立
执行picc21 上执行cdr list server
系统显示如下说明replicate server 已建立：
SERVER ID STATE STATUS CONNECTION CHANGED
grp_picc21 1 Active Local
注1． replicate server 建立时会在picc21 上建立一个名为syscdr 的database 用来
管理所有的repliate 信息，因此也可以通过用dbaccess 命令看是否建立了
syscdr 来确定replicate server 是否正确定义。
注2． replicate 必须用DBSERVER GROUP 代替DBSERVER （ DBSERVER GROUP 在
sqlhosts 中定义）
