Chinaunix

标题: informix数据库晋级之路——主键与索引！ [打印本页]

作者: netpop 时间: 2007-02-15 15:45
标题: informix数据库晋级之路——主键与索引！
讨论这个之前；需要跟大家提到一个概念-“主键”;其实主键是一种特殊的唯一索引；当建立一个主键是；在系统中其实建立了一个不能为空的唯一索引。他和唯一索引的区别也就是不能为空这么一点了。因此他能做到唯一识别表中的一条记录的作用。索引都有索引名在create index  idx-name中定义；主键在定义是没有指定名称；但实际上系统会给自动命名一个unnn_nnn【n为数字】的一个名字。可以通过如下语句查得：【如查basetab_pps主键名字】

select constrname  from sysconstraints
where tabid in (select tabid from systables where tabname='basetab_pps');

下面可以谈谈如何恢复损坏得索引了：

1、  如果是普通得索引。这样就相对得简单了；删除并重建他就可以了。

drop  index  idx-name;
create index idx-name  on  tabname(colname1,colname2…);

2、  如果是主键损坏；同样可以删除并重建他。

A、删除主键
select constrname  from sysconstraints
where tabid in (select tabid from systables where tabname='tabname');
alter  table  tabname  drop  constraint  cons_name;

B、重建主键
alter table basetab_pps  add  constraint primary key (colname1,colname2…);

3、如果重建都有问题；那么最后一招；只有将表重建了。导出数据和建表语句；删除表、重建表、重建索引。。。

关于索引的问题最后讨论一下；统计更新【upadte statistics】的作用

举个简单的例子：

试验一：
在dbaccess 中执行如下操作：
drop table t1 ;
create table t1 (c1  int,c2 char(10));
create  index i_t1 on t1 (c1);
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,1);

set explain on ;  --
select * from t1 where c1 = 2;

在运行目录下的sqexplain.out文件中看到：

QUERY:

------

select * from t1 where c1 = 2
Estimated Cost: 2
Estimated # of Rows Returned: 2
1) smpmml.t1: SEQUENTIAL SCAN
Filters: smpmml.t1.c1 = 2

试验二：
在dbaccess 中执行如下操作：
drop table t1 ;
create table t1 (c1  int,c2 char(10));
create  index i_t1 on t1 (c1);
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,1);

update statistics for table t1;
set explain on ;
select * from t1 where c1 = 2;

在运行目录下的sqexplain.out文件中看到：

QUERY:
------
select * from t1 where c1 = 2
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) smpmml.t1: INDEX PATH
(1) Index Keys: c1
   Lower Index Filter: smpmml.t1.c1 = 2

试验三：
在dbaccess 中执行如下操作：
drop table t1 ;
create table t1 (c1  int,c2 char(10));
create unique index i_t1 on t1 (c1);
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,1);

set explain on ;
select * from t1 where c1 = 2;

在运行目录下的sqexplain.out文件中看到：
QUERY:
------
select * from t1 where c1 = 2
Estimated Cost: 2
Estimated # of Rows Returned: 1

1) smpmml.t1: INDEX PATH
(1) Index Keys: c1
   Lower Index Filter: smpmml.t1.c1 = 2

分析一下：
试验一：建立了一般的索引；对按索引字段进行搜索；但是没有用到刚建立起来的索引。按全表扫描SEQUENTIAL SCAN进行查找。
试验二：建立了一般的索引；并且进行了统计更新后；对按索引字段进行搜索；使用到建立起来的索引。INDEX PATH
试验三：建立了唯一索引；对按索引字段进行搜索；使用到了刚建立起来的索引。INDEX PATH；显然如果建立了主键【特殊的唯一索引】；现象将是一样的。
我们可以这样说：如果建立了主键或唯一索引，立马就能生效；而如果建立的不是唯一索引，就需要执行统计更新才能用到索引。

实际上统计更新除了能决定是否使用到索引外；还有使用这些统计信息如何使用索引及其他方式进行查询的目的。具体如何使用这些信息来决定查询路径就比较底层了。只知道通过执行update statistics命令，就可以使系统表systables、sysdistrib、syscolumns、sysindexes等表内的记录数、表空间的页数、记录长度、字段不同值个数、字段值的分布、索引的层数等信息得到更新。而服务器在进行语法分析后能通过查询优化器根据这些统计信息找到最有效的执行SQL的路径。

[ 本帖最后由 netpop 于 2007-2-16 19:02 编辑 ]

作者: czw1413_cn 时间: 2007-02-15 21:25
提示: 作者被禁止或删除内容自动屏蔽

作者: lianyong 时间: 2007-02-16 15:14
恩，顶一下

作者: 邓蒂斯 时间: 2007-02-25 14:06
言简意赅，好贴

作者: sunlan 时间: 2007-02-25 15:02
不错的帖子！顶一下！
不过我一般都是使用唯一索引。感觉唯一索引在使用方面比主键灵活

作者: hongxin_q 时间: 2007-02-26 08:25
真是好文章，让我一下明白了索引的概念。。最近正在思考更改一个表的唯一索引为普通索引的影响，帮助很大。。谢谢。。

作者: tyffly 时间: 2007-02-26 10:27
提示: 作者被禁止或删除内容自动屏蔽

作者: netpop 时间: 2007-03-01 13:09
标题: 关于查询优化器！

原帖由 tyffly 于 2007-2-26 10:27 发表
不错的贴子
好像不是能一概而论吧?跟复合索引好像也有关系
有时候update for table还是不行的,要直接update statistics for table t1(c1)才有效
不知道有没有人解释一下索引结构,查询优化器,如何利用索引等

以前同事整理的，共享一下：
如何做数据库优化：

Informix在执行查询（特别是多表查询）指令前，会利用其所提供的优化器(cost－based optimizer，基于成本的优化器)，依据当时系统所记载的有关各表的相关信息，按照一定的判断法则进行分析并选择出一条最有效率的途径来执行。系统必须掌握各表的正确数据，才不至于做出错误的选择。但出于系统效率上的考虑，不可能随时修改记录各表最新状况的相关文件，否则会增加许多额外的输入/输出负担。因此，应定期执行这种信息的更新操作。

给定查询的不同执行策略可能会有不同的代价，构造具有最小查询执行代价的查询执行计划是数据库系统的职责。查询优化是为了查询选择最有效的查询策略的过程。查询优化是尽量找出与给定表达式等价的、但是执行效率更高的一个表达式，而且决定执行运算时所采用的具体算法以及将使用的特定索引等。
为了在诸多查询策略中作出选择，数据库系统的优化器必须估计每个查询策略的代价，磁盘访问次数(IO效率)常常是衡量代价的主要标准。在没有按照某策略执行查询前，准确计算出该策略的代价是不可能的，所以，优化器要利用数据库系统中的统计信息，来估计查询策略的代价。Informix数据库系统这些统计信息保存在SYSMASTER数据库中，
如果要维护准确的统计值，那么每当表数据修改时，相应的统计值也必须更新，这种更新会带来很大的代价，因此Informix系统不是在每次修改时对统计值更新。因此，用于选择查询策略的统计数据不一定完全正确，有时会遇到查询用不到应该使用的索引，就是统计信息没有更新的原因。对Informix数据库系统，这些统计信息保存在SYSMASTER数据库中，可以使用UPDATE STATISTICS命令更新。

处理过程：
以下是用于估计代价的信息：
1) 记录数
2) 表空间的页数
3) 记录长度
4) 字段不同值个数
5) 字段值的分布
6) 索引的层数
7) 索引叶结点数目

索引B+树的深度
9) 索引是升序还是降序或聚类索引
10) 索引占用的页面数目

可见统计信息主要是关于该表的总体统计信息。

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”可以得到指定表或字段的分布信息。

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

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

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

2、HIGH：
此时构建的分布信息是准确的，而不是统计意义上的。
因为耗费时间和占用CPU 资源，可以只对表或字段执行HIGH操作。对于非常大的表，数据库服务器将扫描一次每个字段的所有数据。可以配置DBUPSPACE环境变量来决定可以利用的最大的系统磁盘空间。

3、MEDIUM：
抽样选取数据分布信息，故所需时间比HIGH要少。

建议与总结：
什么时候应该执行update ststistics ？
建议在以下情况，执行update statistics 操作：
1) 对数据做了大量修改，大量是针对数据的分布而言，若数据分布没有明显的改变则可以不做
2) 改变的数据库表有与之相关的存储过程，避免在运行时存储过程重新优化
3) 数据库升级之后完成对索引的转变

考虑到速度性能因素，执行update statistics的推荐方法：
1) 对表执行：update  statistics medium for table  xxxx  distributions only
2) 对每个索引的首字段执行：update statistics high for  table  iuser_monrechggift(msisdn)
3) 对复合索引执行：update statistics low
4) 必要时对非索引字段但在条件中使用到的字段执行Update statistics high操作

   《仅供参考，有任何疑问可以回贴或给我邮件：netpop@126.com！》

作者: wushanyink 时间: 2007-03-11 20:23
哈哈，楼主我好像认识。。
最近忙不？还在市网维那边吗？

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)