Informix数据库利用索引提高查询效率

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

2、询出大量数据 (低选择性lowselecviity)，甚至 整张表。顺序扫描对于这样的查询更为适合，因为此时顺序扫描可以利用 lightscan 。 lightscan 缓冲区位于共享内存的虚拟段与驻留段无关。关于 lightscan ，以后章节中还将详述。建立索引的代价虽然索引可以很大地提高高选择性查询的性能， 但维护这些索引是需要付出 代价的。以INSERT语句为例，在进行插入时系统首先将读取被插入表的索引以 定位新记录关键字的位置。 然后系统在将新记录写入数据页的同时还必须将新索 引项写入索引节点。如果导致索引节点分裂，系统则必须多次写索引页。与INSERT语句相似，DELETED句也要

3、求读入整个索引以定位索引节点位置， 并置上删除标志。在删除索引时还需要处理索引节点合并、整理等问题。在执行UPDATE!句时，必须首先定位并且删除旧的关键字然后插入新的关 键字。所以在UPDATED句必须两次读取索引。在实际系统中通常把索引的根节点和第一级节点读入共享内存中， 但如果需 要访问更低层次的索引节点则必须进行磁盘操作。索引类型通 常 建 立 分 离 索 引 ( detached) 或 基 于 表 达 式 的 索 引 分 片 ( expressionbasedfragmented )。分离索引和分片索引可以使得索引的 extent 内页连续，因而能提高性能。而对于不分片的表来说，附加

4、索引( attached) 在 建立索引时，索引页和数据页交叉存放在一起，因而会增加磁头寻找时间。对于中、小型表应该建立分离索引。 对于经常访问的大表应建立基于表达式 的分片索引， 以减少索引页的数量和提高检索速度。 索引分片不宜太多， 以免表 达式计算开销过大。 根据索引的大小， 一般可以分为 4 片。索引分片不支持轮转 法( roundrobin) 。分离式索引与数据页分别存放在不同的数据空间中。例如：CREATEINDEXindex1ONTABLEtable_name(col1)inidxldbs;基于表达式的分片索引在FRAGMENTBYEXPRES子句旨定的数据空间中创 建。例如：C

5、REATEINDEXindex1onTABLEtable_name(col1)FRAGMENTBYEXPRESSION col1>=0andcol1<=25000inidxldbs1col1>=25001andcol1<=50000inidxldbs2 col1>=50001andcol1<=75000inidxldbs3col1>=750001andcol1<=100000inidxldbs4; 隐式索引 在定义约束条件时如果没有可利用的索引， 系统将创建隐式索引。 用户不能 对隐式索引旨定数据空间的位置、 分片策略或者填充因子。 隐式索引创建

6、在数据 库(而非表)所在地数据空间中，这给磁盘管理和性能带来了一些影响。用户如果需要建立约束条件， 能先创建与约束条件完全匹配的显式索引然后 用ALTERTABL命令增加约束条件。这样该约束条件就可以利用显式索引而不必 再建立隐式索引。例如按如下方式： CREATETABLEtable_name(col1INTEGER,col2INTEGER,col3CHAR(25),)intab leldbs;CREATEuniqueINDEXindex1ONTABLEtable_name(col1)inidxldbs;ALTERTABLEtable_nameADDCONSTRAINTPRIMARYKEY(

7、col1); 而不要按如下方式：CREATETABLEtable_name(col1INTEGER,col2INTEGER,col3CHAR(25),PRIMARYKEY(col1)intableldbs;FILLACTOR填 充因子)对于SELECT操作，将FILLFACTO设为100对于SELECT和DELET操作将FILLFACTO设为100对于INSERT和UPDAT操作将FILLFACTO设为50至U 70 CREATEINDEXindex1ONTABLEtable_name(col1)inidx1dbsFILLFACTOR70; 填充因子决定了在建立索引时每一索引页的填充度。该参数

8、是可以改变的。参数FILLFACTOR缺省值为90。如果只是改变某个索引的设置，请使用 CREATEINDE的 FILLFACTO子句。设置较大的填充因子可以使索引更紧凑、 使缓冲区更有效同时也可以减少检索记录时读取的页数。对于只读表应将 FILLFACTORS置为100。对于只执行读和操作的表，如果将FILLFACTORS置为100,在删除记录时可 以减少合并索引节点、整理索引树的可能性。对于有大量插入和更新操作的表，应该将 FILLFACTORS置为50到70。由 于填充因子较小，在插入和更新操作时将延迟索引节点(页面)的分裂，从而提 高系统性能。有如下的SQLFILLFACTC例句：CR

9、EATEINDEXi ndex1ONTABLEtable_name(col1)i ni dxldbsFILLFACTOR70;建 立索引的步骤建立索引必须遵循如下步骤：确定需要建立的索引决定索引的类型，分离索引还是分片索引。如果是分片索引，确定表达式 为每一索引确定填充因子计算索引所需空间决定索引数据空间在磁盘上的位置 为索引创建数据空间 定髓临时数据空间大小和位置。临时数据空间能分布存储。利用DBSPACETEMS置临时数据空间的大小，利用 on space命令的-t选项 创建临时数据空间设置环境变量 PDQPRIORIT和 PSORT_NPROCS仓U建相应的配置文件。其中各选项的设置参见

10、并行排序和载入环境 配置参数建立索引时参数的优化设置与上一章载入环境中的设置相同。 在此作一简单 回顾：NUMCPUVPSS为 CPU数目BUFFER设置尽量多的缓冲区。初始时应为缓冲区分配最多至25%的内存。SHMVIRTSIZ赋予初始段值。最多至可用内存的 75%CKPTINTVL300Q由物理日志决定何时生成检查点。LRUS-个LRU队列对含500- 700个缓冲并允许LRU队列对至128 LUR_MAX_DIRTS置为 80LUR_MIN_dirty 设置为 70RA_PAGE设置为 128,RA_THRESHO设置为 120DBSPACETEMJS多个大小相同的临时数据空间分布在不同

11、的设备上DS_TOTAL_MEMORY*9S0HMVIRTSIZEDS_MAX_SCA待健索引表的分片数下面是对环境变量的简单回顾：PSORT_NPROiC置为 CPU数目，至 10PDQPRIORIT设置为 100数据聚集如果经常根据索引从表中读取大量的数据， 那么对这些数据建立聚集。 通过 建立聚集可以减少读取页的数量， 缩短检索时间， 并可以充分利用预读功能顺序 扫描数据。数 据的 聚 集通过创 建聚集索 引 ( clusteredindex) 完成 。利用ALTERINDEX.TOCLUSTE语句可以将原有索引改为聚集索引，原有索引中的数 据记录顺序将作重排。 如果直接创建聚集索引， 系