转:数据库索引的优化

给定查询的不同执行策略可能会有不同的代价，构造具有最小查询执行代价的查询执行计划是数据库系统的职责。查询优化是为了查询选择最有效的查询策略的过程。查询优化是尽量找出与给定表达式等价的、但是执行效率更高的一个表达式，而且决定执行运算时所采用的具体算法以及将使用的特定索引等。
为了在诸多查询策略中作出选择，数据库系统的优化器必须估计每个查询策略的代价，磁盘访问次数常常是衡量代价的主要标准。在没有按照某策略执行查询前，准确计算出该策略的代价是不可能的，所以，优化器要利用数据库系统中的统计信息，来估计查询策略的代价。Informix数据库系统这些统计信息保存在SYSMASTER数据库中，
如果要维护准确的统计值，那么每当表数据修改时，相应的统计值也必须更新，这种更新会带来很大的代价，因此Informix系统不是在每次修改时对统计值更新。因此，用于选择查询策略的统计数据不一定完全正确，有时会遇到查询用不到应该使用的索引，就是统计信息没有更新的原因。  对Informix数据库系统，这些统计信息保存在SYSMASTER数据库中，可以使用UPDATE  STATISTICS命令更新。
以下是用于估计代价的信息：
记录数
表空间的页数
记录长度
字段不同值个数
字段值的分布
索引的层数
索引叶结点数目
索引B+树的深度
索引是升序还是降序或聚类索引
索引占用的页面数目
Informix  数据库服务器中的优化器为SQL语句的查询提供最有效的策略，这就使得你在进行表的连接查询时不必全面考虑究竟那个表首先搜索，以及究竟需要使用那个索引。
通过执行update  statistics命令可以更新系统的统计信息，使得优化器得到当前最新的统计信息。当修改或删除一个表的相关数据时，系统的统计信息并不自动更新。比如：如果使用delete命令删除一个数据库表内的一条记录，删除完成后查找systables内关于该表的记录信息时，将会发现nrows（数据库表的记录行数目）并没有改变。而通过执行update  statistics命令，就可以使系统表systables、sysdistrib、syscolumns、sysindexes等表内的信息得到更新。在运行完update  statistics后，这时就会发现systables内的nrows字段已得到更新。如果执行update  statistics    medium（high），在sysdistrib表内还可以得到更新的数据分布信息。所以，当大量地修改数据库表后最好执行一下update  statistics操作。另外，update  statistics将强迫存储过程的优化（对sysprocpplan更新）。以下是与update  statistics  相关的系统表：

1、syscolumns：
描述了数据库内的每个字段，其中的colmin、colmax存储了数据库各表字段的次小及次大值，这些值只有在该字段是索引且运行了Update  statistics之后才生效。如对于字段值1、2、3、4、5，则4为次大值，2为次小值。

2、sysdistrib：
存储了数据分布信息。该表内提供了详细的表字段的信息用于提供给优化器优化SQL    Select语句的执行。当执行update  statistics    medium（high）之后将往此表存入信息。
执行“dbschema  -hd”可以得到指定表或字段的分布信息

2、sysindexes：
描述了数据库内的索引信息。对于数据库内的每个索引对应一条记录。修改索引之后只有执行Update  statistics才能使其改变在该表内得到反映。同时也更新clust的数值，在该表的数据页数目及数据库记录条数之间

3、systables：
通过执行Update  statistics可以更新nrows数据

update  statistics有以下三种级别：

1、LOW：
缺省为LOW，此时搜集了关于column的最少量信息。只有systables、syscolumns、sysindexes内的内容改变，不影响sysdistrib。为了提高效率，一般对非索引字段执行LOW操作

2、HIGH：
此时构建的分布信息是准确的，而不是统计意义上的。
因为耗费时间和占用CPU  资源，可以只对表或字段执行HIGH操作。对于非常大的表，数据库服务器将扫描一次每个字段的所有数据。可以配置DBUPSPACE环境变量来决定可以利用的最大的系统磁盘空间
3、MEDIUM：
抽样选取数据分布信息，故所需时间比HIGH要少
什么时候应该执行update  ststistics  ？
建议在以下情况，执行update  statistics  操作：
对数据做了大量修改，大量是针对数据的分布而言，若数据分布没有明显的改变则可以不做
改变的数据库表有与之相关的存储过程，避免在运行时存储过程重新优化
数据库升级之后完成对索引的转变
update  ststistics  的方法
考虑到速度性能因素，执行update  statistics的推荐方法：
对表执行：update    statistics  medium  for  table  ####    distributions  only
对每个索引的首字段执行：update  statistics  high
对复合索引执行：update  statistics  low
必要时对非索引字段但在条件中使用到的字段执行Update  statistics  high操作