Informix Dynamic Server数据库分段存储
Informix Dynamic Server分段存储，也称之为分片存储，其原理是对数据表中的记录或索引有规则地分散存储在磁盘不同的区域，达到将数据存储在多个磁盘上，通过减少对磁盘I/O的竞争，来提高数据库的效率。与此相反的是基本存储原则，其鼓励将数据库表中的数据尽量连续地存放在一个存储设备上，在Informix Dynamic Server读出数据时，将数据批量读出，以提高数据库的效率。
分段存储主要是通过均衡磁盘 I/O，实现内部查询的并行操作、并行地扫描多个磁盘上的数据来提高查询效率，其使数据库性能的提高主要来自于I/O并行度的提高，而不是I/O性能的提高。实际上分段存储是对一些数据量较大的表在存储时提供的一个方法，用户可以选择分段存储也可以不选择分段存储。一个好的分段存储工具可以很好地提高查询效率。否则，不但数据库的查询效率得不到提高反而会降低。在实际应用中，Informix Dynamic Server并不会在意数据放在哪个具体的物理盘上，而是具体到相应的dbspace上，因为Informix Dynamic Server查询可并发执行的最小单位就是dbspace。
分段存储的原理
分段存储可以分为轮转法和基于表达式的方法。轮转法是使用Informix Dynamic Server内部定义的规则将表分段存储。在建表时，可以指定对数据的存储采用轮转法，其语法是：
| CREATE TABLE tablename (aa integer …) FRAGMENT BY ROUND ROBIN IN dbspace1,dbspace2, ……dbspaceN。 |
基于表达式的方法是使用用户定义的规则将表或索引分段存储在不同的dbspace中。基于表达式的方法又可以分为范围规则和绝对规则，范围规则是用SQL的关系（>、<、>=、<=）或逻辑操作（and、or）定义表的数据分片的边界，一般用一个字段，也可以根据需要用2个或多个字段。范围规则的语法是：
CREATE TABLE tablename (a1integer....) FRAGMENT BY EXPRESSION a1 <= 0 IN dbspace1， a1 >= 0 AND a1 <= 100000 IN dbspace2， REMAINDER IN dbspace3; |
绝对规则是用关系操作符（>、<、>=、<=）与逻辑操作符（and、or）对规则进行定义，可以是表中的多个字段参与分段。绝对规则的语法是：
CREATE TABLE tablename (a1 integer....) FRAGMENT BY EXAMPLE a1 = 100000 or a1=200000 IN dbspace1， a1 = 300000 or a1=400000 IN dbspace2， REMAIDER IN dbspace3; |
由于轮转法采用的是Informix Dynamic Server内部定义的规则，所以在使用的过程中，比较简单和方便。基于表达式的方法，使用的是用户定义的规则，为了提高查询效率，就要减轻CPU的负荷，均衡磁盘的I/O操作。因此，用户在使用表达式定义分段规则时，应该遵循以下原则：
1．要使表达式尽可能的简单明了，避免在表达式中使用数据类型的转换，以便在informix Dynamic Server写入或读出数据时尽可能快地解析表达式；
2．为了减少表达式的计算量，在表达式中应该把限制性最强的部分放在前面；
3．在分段表达式中要避免有经常更新的字段，使得分段存储的数据有一个相对固定的位置；
4．对所存储的数据、查询语句进行分析，明确查询输出的结果，使被频繁访问的数据能均衡地分布在多个磁盘上。
分段存储设计的目标是为了给用户提供更好的响应时间，更好的并发性，更好而且更快的备份和恢复，还具有较好的可用性。但是分段存储无形中给数据库增加了管理成本，而且数据库的转移也不太方便。为了达到这样地目标，在对数据库进行分段设计时，就要考虑主机的硬件特性，如CPU个数、速度、磁盘的数目、磁盘控制器的数目、每个磁盘控制器连接的磁盘数目等，及操作系统的性能。在硬件特性和操作系统性能满足分段存储时，可以对一些数据存储量大的表进行分段存储，这样其性能将会有所提高。
分段存储的实例
本文在对分段存储的测试中，使用的是一台HP服务器。该服务器有4个较慢速的CPU，2GB内存，硬盘采用的是raid 5 技术。操作系统是 SCO Unix 5.0.6，数据库是Informix 7.31uc5。在测试的过程中，机器基本闲置。使用onstat -d 命令输出结果如下：
Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 7 days 20:44:47 -- 307200 Kbytes Dbspaces address number flags fchunk nchunks flags owner name 92c5413c 1 1 1 1 N informix rootdbs 92c54d94 2 1 2 1 N informix phydbs 92c54e50 3 1 3 1 N informix logdbs 92c54f0c 4 2001 4 1 N T informix tmpdbs 92c54fc8 5 1 5 7 N informix datadbs 92c55084 6 1001 12 1 N informix datatest1 92c55140 7 1001 13 1 N informix datatest2 7 active, 2047 maximum Chunks address chk/dbs offset size free bpages flags pathname 92c541f8 1 1 100 250000 146465 PO- /home/informix/DBS/rootdbs 92c54344 2 2 100 150000 4947 PO- /home/informix/DBS/phydbs 92c54420 3 3 100 500000 90347 PO- /home/informix/DBS/logdbs 92c544fc 4 4 100 500000 499947 PO- /home/informix/DBS/tmpdbs ……省去了datadbs使用的7个chunk 92c54bdc 12 6 50 950000 484235 PO- /home/informix/DBS/data_chunk7 92c54cb8 13 7 50 950000 420499 PO- /home/informix/DBS/data_chunk8 13 active, 2047 maximum |
测试中使用的dbspace是datatest1和datatest2，其分别对应了data_chunk7和data_chunk8的两个chunk。测试用的数据库建立在datatest1中，在数据库中有两个表，表的名称是dcc_saacnamt和dcc_saacnamt1，两个表的结构一样，存储的数据一样，记录数都是148万条，都没有建立索引。
表dcc_saacnamt存储在datatest2中，没有采用分段存储；表dcc_saacnamt1采用分段存储，分别存储在datatest1和datatest2中。首先采用基于表达式的范围规则对dcc_saacnamt1进行分段存储，建表的表达式为：
create table dcc_saacnamt1 ( sa_acct_no char(28) not null , …… etl_load_date date ) FRAGMENT BY EXPRESSION etl_load_date <= "20050630" and etl_load_date >= "20050101" in datatest2, etl_load_date <="20051231" and etl_load_date>="20050701" in datatest1; |
为了方便测试，将etl_load_date的取值定在2005年1月1日到2005年12月31日之间。
测试用shell如下：
date >aa {输出开始时间} dbaccess -s testfrag<<!{打开测试用数据库} set explain on;{打开informix跟踪器} SET OPTIMIZATION FIRST_ROWS;{打开informix选择最优的查询路径} set pdqpriority high;{打开informix PDQ并行查询开关} drop table test_poll1; create table test_poll1{建立测试用表} ( sa_no char(28), etl_date date ) in datatest1; insert into test_poll1 (sa_no,etl_date) select sa_acct_no,etl_load_date from dcc_saacnamt1 从分段存储表中读取数据} where (etl_load_date <= "20050630" and etl_load_date>= "20050101"); ! date >>aa{输出分段存储读取数据结束时间} dbaccess -s testfrag<<!{打开测试用数据库} set explain on;{打开informix跟踪器} SET OPTIMIZATION FIRST_ROWS; {打开informix选择最优的查询路径} set pdqpriority high;{打开informix PDQ并行查询开关} drop table test_poll; create table test_poll{建立测试用表} ( sa_no char(28), etl_date date ) in datatest1; insert into test_poll (sa_no,etl_date) select sa_acct_no,etl_load_date from dcc_saacnamt {从非分段存储表中读取数据} where (etl_load_date <= "20050630" and etl_load_date>= "20050101"); ! date >>aa{输出结束时间} |
文件aa记录的结果：
Fri Feb 24 09:47:23 CST 2006
Fri Feb 24 09:47:29 CST 2006
Fri Feb 24 09:48:13 CST 2006
可以看出采用分段存储，读取数据及写表使用了6秒的时间，不分段存储使用了44秒的时间。
在对Informix跟踪器输出的sqexplain.out文件进行分析，发现在从dcc_saacnamt1表中读取数据时，由于表dcc_saacnamt1采用了分段存储，所以在读取和写入数据时，采用了并行操作。对没有分段存储的表dcc_saacnamt的查询只是一般的扫描操作。sqexplain.out文件结果如下：
QUERY: {表分段存储后查询数据时Informix跟踪结果} insert into test_poll1 (sa_no,etl_date) select sa_acct_no,etl_load_date from dcc_saacnamt1 where (etl_load_date <= "20050630" and etl_load_date>= "20050101") Estimated Cost: 2 Estimated # of Rows Returned: 11 Maximum Threads: 1 1) brow.dcc_saacnamt1: SEQUENTIAL SCAN (Parallel, fragments: 0) Filters: (brow.dcc_saacnamt1.etl_load_date <= 2005/06/30 AND brow.dcc_saacna mt1.etl_load_date >= 2005/01/01 ) QUERY: {表非分段存储后查询数据时Informix跟踪结果} ...... insert into test_poll (sa_no,etl_date) select sa_acct_no,etl_load_date from dcc_saacnamt where (etl_load_date <= "20050630" and etl_load_date>= "20050101") Estimated Cost: 2 Estimated # of Rows Returned: 111204 Maximum Threads: 1 1) brow.dcc_saacnamt: SEQUENTIAL SCAN Filters: (brow.dcc_saacnamt.etl_load_date <= 2005/06/30 AND brow.dcc_saacnam t.etl_load_date >= 2005/01/01 ) |
在使用轮转法对dcc_saacnamt1进行分段存储，也采用相同的测试方法，结果基本一致，但是效果没有基于表达式的方法好。
综上所述，分段存储能提高数据库的效率，是数据库查询优化方法之一，但是它也有一定的缺陷。在对数据库进行查询优化时，可以使用分段存储。但不能对数据库中所有的表都实行分段存储，而是要有选择的进行。
