Informix－Online数据库的查询优化策略

Informix－Online 动态服务器（IDS,Informix－Online Dynamic Server）作为Informix数据库产品技术的核心，以其动态可伸缩体系结构，高效的并行处理能力、共享内存技术及易管理性等特点，将硬件资源发挥得淋漓尽致。当前IDS V7正广泛地应用于我国金融、邮政、电信等行业的关键系统中，随着应用的不断深入,数据的积累,查询的复杂化,查询速度会变慢,致使响应时间过长。许多用户将其归结为硬件的原因,于是升级改造,或重新投资,数据库的调优并没有引起足够的重视。这其实是一种浪费,与国外发达国家轻硬件、重应用的思路正好相反。实践表明,数据库的不合理配置和不适当优化是其性能下降的主要因素。实施对IDS上数据库的管理维护、性能调优是系统管理员的主要工作，而能否得以良好的查询响应则集中体现了数据库的性能，因此也是调优的重点。从系统管理的角度，我们可以设置多线索、合理分配共享内存空间、建立数据库和表的分布及分片管理等来加快查询速度，但最终还要基于对数据库本身的全面理解，因为数据处于不断的变化和积累之中，并且随着应用的深入查询将日趋复杂化。本文从数据管理的角度阐述了几种查询优化策略，实际应用中有很好的收效，现说明如下。
一、查询的分类及要求
针对语句中所涉及的数据库表的数目查询可分为：单表查询、多表查询、联合查询、子查询等，多表查询建立在多张表的连接之上，分嵌套循环、合并排序、哈希连接三种方式，最为复杂，也是调优的重点。目前数据库的应用分为联机事务处理(OLTP,Online Transaction Processing）和决策支持系统(DSS,Decision Support System）两大类型，它们对查询的要求不尽相同。OLTP主要涉及单张表，SQL语句简单，数据按索引读取，查询行数少，对响应的时间要求非常苛刻，常在秒级或以下，多用于在线实时业务；DSS涉及多张表之间的连接查询，SQL语句复杂，数据按物理顺序读取，查询行数多，响应时间长，多用于建立在数据仓库技术之上的复杂的数据分析。但无论何种情况，我们都希望最快的响应速度，这也是调优的最终目标。
二、查询的优化策略
1.充分利用查询优化器
查询优化器提供了数据查询的优化策略分析和选择方式，通过设置相关参数，优化器能够选择最佳的连接策略，并在所有的查询路径中找出一条最优路径。选择良好的路径是查询优化中至关重要的一环，一条好的路径可以扫描最少的记录，以最少的磁盘I/O得到正确的查询结果。可通过以下步骤进行。
（1）设置连接策略
通过修改配置文件＄ONCONFIG中的OPTCOMPIND参数值来实现。
OPTCOMPIND 0：在连接中优化器只选择索引连接。
OPTCOMPIND 1：若事务处理为可重复读模式（Repeatable Read）,则选择索引策略，否则，优化器自动选择开销最低的连接策略。
OPTCOMPIND 2：优化器自动选择开销最低的连接策略。应尽量选择该参数。
（2）设置查询优化的模式
也即选择最优的查询路径，通过执行以下SQL语句来实现，格式为：
SET OPTIMIZATION [ HIGH | LOW | FIRST_ROWS | ALL_ROWS ]
其中,HIGH是缺省选项，表示对所有查询路径都进行检测，从中选择最优。
LOW表示采用深度优先法仅在部分路径中选择最优，即在每次连接比较中，遇到最优路径就继续深入而滤掉非最优路径，特点是优化时间短，但路径准确率低。
FIRST_ROWS和ALL_ROWS是自IDS V7.3开始增加的新选项，无论对OLTP还是DSS都非常有用。传统的查询(即ALL_ROWS方式)一次将所有查询结果输出到共享内存缓冲区中，时间的消耗非常大，然而实践表明，大部分用户仅关注最初的几屏输出内容，因此FIRST_ROWS选项为我们提供了很好的选择。FIRST_ROWS指导优化器选择一条查询路径,使其只输出填满一个缓冲区的记录数，如果用户继续查询则继续执行，这样避免了不必要的输出结果和时间浪费，也使查询速度大大提高。
由此可见，优化器的丰富功能为我们提供了灵活的手段，管理员可以根据不同应用情况选择最佳的方式，既能达到最佳的查询效果，又能将由此而造成的系统开销降至最低。
2.进行统计更新和数据分布
当数据库表做了大量的插入、删除操作或表的索引发生变化后，Online数据库系统表的相关信息与实际表的统计数字不一致，这对数据的完整性没有任何损害，但会影响到查询的速度。因为优化器所制定的计划和策略得以正确实施的前提是对系统表信息的精确读取，统计信息的正确性将直接影响到查询的执行效果，因此我们必须定期执行系统表信息的统计更新工作。此外还要经常做好数据分布工作，使数据的组织形式更为合理。通过数据分布，优化器可以根据有关信息确定如下内容：过滤器字段的选择率（Selectivity）、访问过滤器字段和表的策略、最佳的连接策略。一旦确定这些内容，查询的执行时间将会显著缩短。
进行统计更新和数据分布的唯一方法是运行SQL命令：Update Statistics……，其结果是：IDS 流览表和索引，一方面对统计信息加以编译，最终将编译好的信息存储到相应的系统表中，另一方面读取表中记录并对其进行排序以生成最好的组织形式。具体格式如下：
Update Statistics [ High | Medium | Low ] [ Distributions Only ] [ for table tabname [ (field1,field2……) ] ]
其中,High对表中的所有记录进行排序以产生数据分布。
Medium随时从表中选取部分记录进行排序以产生数据分布。
Low仅执行统计更新，即仅修改系统表systables、sysindexes、syscolumns的内容，但不进行数据分布。
Distributions Only进行数据分布和部分统计更新工作，不更新系统表sysindexes的内容。
为实现科学有效的统计更新和数据分布，通常应执行以下优化步骤:
（1）针对每张表运行
Update Statistics Medium for table tabname Distributions Only
（2）针对每张表中带索引的第一个字段运行
Update Statistics High for table tabname (fieldname)
（3）对某些表中带复合索引的每一个字段运行
Update Statistics Low for table tabname (fieldname1,fieldname2……)
以上顺序非常重要，不能搞错。为方便运行，我们可以将以上命令按顺序写入到一个shell程序文件中，让操作系统在每日数据最少改动时间定时运行该程序。
3.使用SQL语句缓存（SSC,SQL Statement Cache）功能
SSC是Informix IDS2000 V9中增加的新功能，提供了共享的语句缓存，从而实现了快速的SQL调用。传统情况下，每条SQL语句运行前都要进行逻辑分析以判断语法正误，还要在共享内存中为各语句分配空间。实践表明，无论是OLTP还是DSS应用，大量运行的SQL语句具有相同的格式，通过SSC，重复的SQL语句可以单一在共享内存中存储及共享使用，这样不仅大大减少了大量语句的分析过程，使查询的速度明显加快，而且节省了大量共享内存空间，带来了其他应用效果的改善。SSC的使用方法如下。
(1) 在IDS配置文件＄ONCONFIG中定义
STMT_CACHE 1
或运行SQL命令：onmode －e enable 以激活SSC功能。
（2）用户使用前还必须定义环境变量STMT_CACHE
export STMT_CACHE = 1
或运行SQL命令：set statement cache on
经过以上设置后，所有的查询都将充分基于SSC进行高效处理。
三、查看优化结果
查询优化器给用户提供了大量详尽的关于优化的信息，包括：
1．连接过程中的开销估计
2．查询过程中表的使用顺序（即查询路径）
3．查询过程中用到的临时表
4．对每个表的访问类型，如：顺序扫描、索引扫描、哈希连接等
一名合格的系统管理员应熟知每一项信息所代表的含义，并进行反复的优化和输出比较方可制定出最佳的优化方案。为使系统提供以上信息，要求执行查询前先运行SQL命令：set explain on ，查询完毕后再运行：set explain off，这样在用户当前工作目录下会生成一个包含以上信息sqlexplain.out文本文件。通过该文件内容，管理员可清楚地看到经过优化后的查询效果。
如果管理员想了解SSC的使用情况，可运行以下SQL命令：
onstat －g cac stmt
这时共享内存中每条SQL语句的命中情况将会详尽地显示出来，命中率越高，表明查询的效果越好，SSC得到了越充分的利用。
本文所列举的查询优化策略只是笔者工作经验的总结，实际上，数据库的优化是一个长期不懈、不断比较分析和调整的过程，因为数据在不断的变化中，应用在不断的发展中。系统管理员只有深入领会和掌握Informix动态服务器所提供的强大功能，正确观察和分析系统运行中提供的各种信息，充分结合实际应用特点，才能合理制定出良好的优化策略，实现快速、高效的数据查询和应用分析，同时也使硬件资源得到最充分的发挥。

原文转自：http://www.ltesting.net