Informix 高级培训教材(三)

3.6. 关于语句写法
语句的效率与 SQL 语句的写法有很大关系。在几种写法中选择一个合适的写法相当重要，有时效率会急剧提高。因此，在效率有问题的地方多检查自己语句的写法，有可能会取得重大突破。以下是一些实例：
(1). 在保单表中查找 “ 003” 险种的 “ 0101” 区站的数据
select * from rta1 where bm_cert[1,7]=” 0030101”
该语句运行前设置 set explain on ，运行后查看 sqexplain.out 结果如下：
QUERY:
select * from rta1
where bm_cert[1,7]= " 0030107 "
Estimated Cost: 1
Estimated # of Rows Returned: 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
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=” 000000” )
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 value s " ,
" (?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?) "
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 value s "
" (?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?,?,?,?,?,?,?, " ,
" ?,?,?,?) "
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=” 0” and birthday >” 1982/12/1 ”
则索引建成如下形式效率最好。
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 异步：目标数据的复制可以根据应用要求设定，但最终要同步所有的数据。在一般的开放系统中，异步方式更为常用，因为它忽略了主机和网络的性能，这往往是一些系统的致命问题。
异步复制又分为如下几种模式：

主从模式：所有数据更新都是从原到目标，是单向数据复制。如图示：
工作流模式：数据在服务器间一一流转，也是单向数据复制。如图示：
任意更新模式：所有定义的数据库之间都互相具有读写权限，它的弊端是容易引起复制冲突，当然 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 的数据库之间的互访
分别在 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
分别在 picc21 和 ibm500 上的 /etc/hosts 文件包括如下 2 项：
176.176.21.78 picc21 picc21
176.176.66.103 ibm500
分别在 picc21 和 ibm500 上的 /etc/services 文件包括如下 2 项 :
ls370222_tcp 5889/tcp
ls370220_tcp 3720/tcp
在 picc21 上的 /etc/hosts.equiv 文件包括如下 1 项 :
ibm500
在 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 中定义）