Informix 优化建立索引时的系统环境

在学习完本章后，你将能够： 
了解建立索引的需求 
描述动态服务器系统中不同的索引类型 
描述并行建立索引的步骤 
为并行排序优化环境 
使用FILLFACTOR（填充因子） 
描述建立索引的步骤 
为创建索引优化参数配置文件(onconfig) 
了解数据聚集的时机 


在OLTP和DSS环境下建立索引 

如果查询结果仅为一行或很少几行时（高选择性high selectivity)，利用索引进行查询会大大提高效率。相比之下，如果没有索引，查询则只能顺序扫描整个表。在OLTP环境下，事务处理在很大程度上依赖于索引。只有在表很小时，才会顺序扫描表。系统会根据SQL语句中的WHERE子句判断是否使用索引。顺序扫描表会使系统性能受到严重影响。sysmaster中sysptntab表中的pf_seqscnas列显示了所进行的顺序扫描。SET EXPLAIN命令同样可以提供关于SQL语句如何访问数据库中的重要信息。 

DSS环境中的应用经常会查询出大量数据（低选择性 low selecviity)，甚至整张表。顺序扫描对于这样的查询更为适合，因为此时顺序扫描可以利用light scan。light scan缓冲区位于共享内存的虚拟段与驻留段无关。关于light scan，以后章节中还将详述。 


建立索引的代价 

虽然索引可以很大地提高高选择性查询的性能，但维护这些索引是需要付出代价的。 

以INSERT语句为例，在进行插入时系统首先将读取被插入表的索引以定位新记录关键字的位置。然后系统在将新记录写入数据页的同时还必须将新索引项写入索引节点。如果导致索引节点分裂，系统则必须多次写索引页 

与INSERT语句相似，DELETE语句也要求读入整个索引以定位索引节点位置，并置上删除标志。在删除索引时还需要处理索引节点合并、整理等问题。 

在执行UPDATE语句时，必须首先定位并且删除旧的关键字然后插入新的关键字。所以在UPDATE语句必须两次读取索引。 

在实际系统中通常把索引的根节点和第一级节点读入共享内存中，但如果需要访问更低层次的索引节点则必须进行磁盘操作。 


索引类型 

通常建立分离索引（detached)或基于表达式的索引分片（expression based fragmented）。分离索引和分片索引可以使得索引的extent内页连续，因而能提高性能。而对于不分片的表来说，附加索引（attached)在建立索引时，索引页和数据页交叉存放在一起，因而会增加磁头寻找时间。 

对于中、小型表应该建立分离索引。对于经常访问的大表应建立基于表达式的分片索引，以减少索引页的数量和提高检索速度。索引分片不宜太多，以免表达式计算开销过大。根据索引的大小，一般可以分为4片。索引分片不支持轮转法（round robin)。 

分离式索引与数据页分别存放在不同的数据空间中。例如： 

CREATE INDEX index1 ON TABLE table_name(col1) in idxldbs; 

基于表达式的分片索引在FRAGMENT BY EXPRESSION 子句指定的数据空间中创建。例如： 

CREATE INDEX index1 on TABLE table_name(col1) 

FRAGMENT BY EXPRESSION 

col1>=0 and col1<=25000 in idxldbs1 

col1>=25001 and col1<=50000 in idxldbs2 

col1>=50001 and col1<=75000 in idxldbs3 

col1>=750001 and col1<= 100000 in idxldbs4; 


隐式索引 

在定义约束条件时如果没有可利用的索引，系统将创建隐式索引。用户不能对隐式索引指定数据空间的位置、分片策略或者填充因子。隐式索引创建在数据库（而非表）所在地数据空间中，这给磁盘管理和性能带来了一些影响。 

用户如果需要建立约束条件，最好能先创建与约束条件完全匹配的显式索引然后用ALTER TABLE命令增加约束条件。这样该约束条件就可以利用显式索引而不必再建立隐式索引。例如按如下方式： 

CREATE TABLE table_name( 

col1 INTEGER, 

col2 INTEGER, 

col3 CHAR(25), 

) in tableldbs; 

CREATE unique INDEX index1 ON TABLE table_name(col1) in idxldbs; 

ALTER TABLE table_name ADD CONSTRAINT PRIMARYKEY(col1); 


而不要按如下方式： 

CREATE TABLE table_name( 

col1 INTEGER, 

col2 INTEGER, 

col3 CHAR(25), 

PRIMARY KEY (col1） 

) in tableldbs; 



FILLACTOR(填充因子） 

对于SELECT操作，将FILLFACTOR 设为100 

对于SELECT 和DELETE操作将FILLFACTOR 设为100 

对于INSERT 和 UPDATE操作将FILLFACTOR设为50到70 

CREATE INDEX index1 ON TABLE table_name(col1) in idx1 dbs FILLFACTOR 70; 


填充因子决定了在建立索引时每一索引页的填充度。该参数是可以改变的。参数FILLFACTOR缺省值为90。如果只是改变某个索引的设置，请使用CREATE INDEX的FILLFACTOR子句。 

设置较大的填充因子可以使索引更紧凑、使缓冲区更有效同时也可以减少检索记录时读取的页数。对于只读表应将FILLFACTOR设置为100。 

对于只执行读和操作的表，如果将FILLFACTOR设置为100,在删除记录时可以减少合并索引节点、整理索引树的可能性。 

对于有大量插入和更新操作的表，应该将FILLFACTOR设置为50到70。由于填充因子较小，在插入和更新操作时将延迟索引节点（页面）的分裂，从而提高系统性能。 

有如下的SQL FILLFACTOR例句： 

CREATE INDEX index1 ON TABLE table_name(col1) in idxldbs FILLFACTOR 70; 


建立索引的步骤 

建立索引必须遵循如下步骤： 


确定需要建立的索引 
决定索引的类型，分离索引还是分片索引。如果是分片索引，确定表达式 
为每一索引确定填充因子 
计算索引所需空间 
决定索引数据空间在磁盘上的位置 
为索引创建数据空间 
决定所需临时数据空间大小和位置。临时数据空间最好能分布存储。 
利用DBSPACETEMP 设置临时数据空间的大小，利用onspace命令的-t选项创建临时数据空间 
设置环境变量PDQPRIORITY 和PSORT_NPROCS 
创建相应的配置文件。其中各选项的最优设置参见并行排序和载入环境 


配置参数 

建立索引时参数的优化设置与上一章载入环境中的设置相同。在此作一简单回顾： 

NUMCPUVPS 　　　　　　设置为CPU数目 

BUFFERS 　　　　　　　设置尽量多的缓冲区。初始时应为缓冲区分配最多至25％的内存。　 

SHMVIRTSIZE　　　　　　赋予初始段最大值。最多至可用内存的75％ 

CKPTINTVL　　 　　　　3000。由物理日志决定何时生成检查点。 

LRUS 　　　　　　　　 一个LRU队列对含500－700个缓冲并允许LRU队列对最大至128 

LUR_MAX_DIRTY 　　　　设置为80 

LUR_MIN_dirty　　　　 设置为70 　　 

RA_PAGES 　　　　　　　设置为128， 

RA_THRESHOLD 　　　　　设置为120 

DBSPACETEMP　　　　　　设置多个大小相同的临时数据空间分布在不同的设备上 

DS_TOTAL_MEMORY 　　　 90％*SHMVIRTSIZE 

DS_MAX_SCANS 　　　　　待建索引表的最大分片数 

下面是对环境变量的简单回顾： 

PSORT_NPROCS 　　　　　设置为CPU数目，最大至10 

PDQPRIORITY 　　　　　 设置为100 


数据聚集 

如果经常根据索引从表中读取大量的数据，那么最好对这些数据建立聚集。通过建立聚集可以减少读取页的数量，缩短检索时间，并可以充分利用预读功能顺序扫描数据。 

数据的聚集通过创建聚集索引（clustered index)完成。利用ALTER INDEX ...TO CLUSTER 语句可以将原有索引改为聚集索引，原有索引中的数据记录顺序将作重排。如果直接创建聚集索引，系统也会将数据记录排序。请注意，在ATLTER INDEX ...TO CLUSTER 和CREATE CLUSTER INDEX 语句执行后，系统将建立新表并删除旧表。所以必须有足够的空间来容纳新旧两张表。 

如果可能，在载入数据前先对其按索引顺序排序，这样建立索引时不必设置CLUSTER子句，从而不必对数据进行聚集操作，避免建新表删旧表过程的开销。