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

日期:2008-6-20来源:赛迪网

  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动态服务器所提供的强大功能,正确观察和分析系统运行中提供的各种信息,充分结合实际应用特点,才能合理制定出良好的优化策略,实现快速、高效的数据查询和应用分析,同时也使硬件资源得到最充分的发挥。

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

评论
查看更多评论

敬请读者发表评论,本站保留删除与本文无关和不雅评论的权力。

Informix数据库>更多

  • 趣解数据库市场竞争格局

    数据库咨询顾问Curt Monash对数据库技术的发展格局进行了趣味解读,包括如Oracle、IBM、Sybase鞥厂商之间的竞争关系。

  • 盘点数据库2014:一步之遥到云端

    年终将至,盘点开始。数据库技术重换青春,市场竞争也达到了前所未有的激烈程度。可以肯定的是,数据库未来将沿着“云”和“大数据”这两个主要方向发展。

  • IBM 2014技术峰会开幕 “融合(Mix)”成今年主题

    IBM 2014技术峰会今天在北京正式拉开帷幕。在本次大会上,IBM全面解析了应对数字经济时代发展的全新“组合式业务”(Composable Business)理念。

  • SAP针对HANA发布最新预测分析软件

    SAP日前宣布将推出最新的预测分析软件BusinessObjects Predictive Analysis,它将以两种方式发布,一种是独立的软件平台,另外一种是与SAP HANA进行集成。

相关推荐

技术手册>更多

  • SQL Server十大技巧集锦

    新年干货大放送。TechTarget数据库网站总结了过去一年最实用、最受欢迎的十大SQL Server技巧,分享给各位DBA。预祝DBA们马年“马上有成功”!

  • 2013数据库工程师薪酬调查报告

    TechTarget数据库网站每两年就会进行一次“数据库工程师薪酬调查”,对不同行业、不同层次的数据库技术从业者的薪酬待遇情况进行一次摸底。

  • Oracle数据库Web开发工具指南

    在进行Oracle Web开发的时候,程序员再也没有理由说自己找不到合适的开发工具了。在过去几年中,出现了许多基于Oracle数据库的Web开发工具,这些工具可以帮助程序员更加快速高效便捷地进行Web应用开发。

  • Oracle 11g SecureFile技术详解手册

    SecureFile不仅是新一代 LOB,它们还为 LOB 带来了更多的价值,尤其是以前只能在文件系统领域中获得的特性。

TechTarget

最新资源
  • 安全
  • 存储
  • CIO
  • SOA
  • 云计算
  • 商务智能