三、 建立索引的策略
索引是关系数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用ISAM索引结构。在OLTP应用中，事务处理在很大程度上依赖于索引。
1、索引的类型
(1)、分离索引(detached)或基于表达式的索引分片(expression based fragmented)。分离索引和分片索引可以使得索引的extent内页连续，因而能提高性能。而对于不分片的表来说，附加索引(attached)在建立索引时，索引页和数据页交叉存放在一起，因而会增加磁头寻找时间。对于中、小型表应该建立分离索引。对于经常访问的大表应考虑建立基于表达式的分片索引，以减少索引页的数量和提高检索速度。索引分片不宜太多，以免表达式计算开销过大。根据索引的大小，一般可以分为4片。
◆分离式索引与数据页分别存放在不同的数据空间中。例如:
CREATE INDEX index_a ON TABLE table_name(colA) in idxdbs;
◆基于表达式的分片索引在FRAGMENT BY EXPRESSION 子句指定的数据空间中创建。例如:
CREATE INDEX index_a on TABLE table_name(colA)
FRAGMENT BY EXPRESSION
colA>=0 and colA<=20000 in idxdbs1
colA>=20001 and colA<=50000 in idxdbs2;
(2)、隐式索引:在定义约束条件时如果没有可利用的索引，系统将创建隐式索引。用户不能对隐式索引指定数据空间的位置、分片策略或者填充因子。隐式索引创建在数据库(而非表)所在地数据空间中，这给磁盘管理和性能带来了一些影响。
2、建立索引的代价
虽然索引可以很大地提高高选择性查询的性能，但维护这些索引是需要付出代价的。
(1)、存储开销:建立索引将占用许多页的数据空间。
(2)、处理开销:对一个索引表的数据进行编辑时都要同时更新索引。
◆ INSERT语句:在进行插入时系统首先将读取被插入表的索引以定位新记录关键字的位置。然后系统在将新记录写入数据页的同时还必须将新索引项写入索引节点。如果导致索引节点分裂，系统则必须多次写索引页。
◆ DELETE语句:与INSERT类似，DELETE语句也要求读入整个索引以定位索引节点位置，并置上删除标志。在删除索引时还需要处理索引节点合并、整理等问题。
◆ UPDATE语句:执行时必须首先定位并且删除旧的关键字然后插入新的关键字。所以在UPDATE语句必须两次读取索引。
在实际系统中通常把索引的根节点和第一级节点读入共享内存中，但如果需要访问更低层次的索引节点则必须进行磁盘操作。
3、索引策略的优化与原则
(1)、避免高度重复率字段建立索引
高度重复字段的索引会降低性能。如果觉得有必要在包含高度重复值的列上建立索引，最好与包含唯一值(或近似唯一的其它列)建立一个复合索引，服务器仍可以使用该索引来访问重复列值，而该索引得处理效率更高。
(2)、对同一表不要建立过多的索引
太多的索引与不充分、不正确的索引对性能都无益，在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理开支。
(3)、建索引的字段的大小尽量小，复合索引尽量少用。
(4)、复合(compound)索引。
◆ 在一个有唯一值的字段和有重复值的字段上共同建一个复合索引，有助于多重复值的字段的insert操作(增加唯一性)。
◆ 在join相关的多个字段上建复合索引。
◆ 在Where条件相关的多个字段上建复合索引。
(5)、建聚类(cluster)索引，减少索引文件碎片，以加快检索速度。
聚类索引对相对稳定的表较为有用，能加快查询。
◆ 聚类和生聚类都需花费大量磁盘空间和时间
◆ 数据录入时就是有序的，则无需聚类。
(6)、使用FILLFACTOR(填充因子)控制索引页中的空间
填充因子决定了在建立索引时每一索引页的填充度。该参数是可以改变的。参数FILLFACTOR缺省值为90。如果只是改变某个索引的设置，请使用CREATE INDEX的FILLFACTOR子句。例如:下列语句在建立索引时将FILLFACTOR设为70。
CREATE INDEX index_a ON TABLE table_name(colA) in idxdbs FILLFACTOR 70;
设置较大的填充因子可以使索引更紧凑、使缓冲区更有效同时也可以减少检索记录时读取的页数。
◆ 对于只读表可将FILLFACTOR设置为100。
◆ 对于只执行读和删除操作的表，如果将FILLFACTOR设置为100,在删除记录时可以减少合并索引节点、整理索引树的可能性。
◆ 对于有大量插入和更新操作的表，可将FILLFACTOR设置为50到70。由于填充因子较小，在插入和更新操作时将延迟索引节点(页面)的分裂，从而提高系统性能。
(7)、在经常进行连接，但是没有指定为外键的字段上建立索引，而不经常连接的字段则由优化器自动生成索引。
(8)、Where条件子句经常用到的字段上加索引。
(9)、在频繁进行排序或分组(即进行group by或order by操作)的字段上建立索引。
(10)、限制在频繁update的表上建索引的数目。
(11)、对大批量update操作(如load数据),首先drop index,再update;然后再建索引, 能提高性能。
(12)、数据量小的表是否建索引影响不大，一般不要对记录数小于200的小表建立索引，因为从使用索引得到的速度不能抵消在表上打开和检索索引文件所需的时间。
(13)、用永久索引代替自动索引。
(14)、建primary key。
更多内容请看PCdog.com--informix开发 数据库相关文章专题
