informix数据库sql优化常规套路(原则:在尽量不修改应用程序和表结构的前提下,优化程序和表设计不在讨论之内)
1、统计更新
统计更新的对象是表内数据的分布采样。用概率进行统计。
现在数据库优化器是基于代价的优化器,一个sql语句在执行之前,会由优化器进行优化,选择代价最小的查询计划。
对于informix统计更新可能会影响优化器的分析行为,影响查询计划;如果统计更新前后查询计划没有差异,
定时做统计更新也会不同程度提高数据库性能。所以在优化之前建议先做统计更新,确定当前的最优查询计划,然后展开优化。
统计更新可以对全库,全表,指定列做,有HIGH级和LOW级,例如UPDATE STATISTICS HIGH FOR TABLE tabname(colname);
2、查询计划
在查询计划中涉及访问计划,连接计划和过滤器
访问计划是指 informix 数据库是通过什么方法来读取磁盘上的数据。
顺序扫描(Sequential scan):数据库服务器按照物理顺序读取表中的所有记录。
索引扫描(Index scan):数据库服务器读取索引页,并通过相应的 rowid来读取相关的记录。
连接计划是指多表连接查询的时候,各表之间如何连接。
过滤器是指在表上通过何种条件来过滤数据以缩小有效数据行数。如join后的on,where后的过滤器等
3、索引
索引有几种,根据不同数据分布特点有不同类型的索引可供选择(如位图索引,函数索引等)
我们平时用的索引是btree索引,逻辑结构就是一个树。树的叶子节点就是索引条目,索引条目里保存rowid和col列值等数据。
如果查询计划中未用到索引,考虑根据查询条件(数据过滤条件)建索引。如果已用到索引,查询系统表,看所用到索引的具体情况。
比如,索引的层级,一般4级或超过4级的索引效率开始低下,考虑更换索引(定义或类型)。
对于多列索引,比如index idx01 on table(col1,col2,col3),index idx02 on table(col1)如果查询条件为where col1=?则idx01和idx02有一个为冗余索引。
由于对表插入删除等操作会维护索引,所以冗余的索引对查询无益(至少是让优化器多做了一次未必更好的选择),对表删除插入操作有害。
索引的查询有命中率。试想,索引列上的值重复度比较高,比如性别(男 or 女),如果在人口表中用性别作为索引,则大概会有一半的数据被命中。
假设人口表有数据10000条,男女各5000。
一次查询最终满足所有条件的行数是50行,那么性别索引过滤得到的5000行中只有50行是有效的;命中率=1%
而身份证索引过滤得到只有50行,这50行都是有效的;命中率=100%
4、变量中继
有时需要反复查询一个sql来取一个中间结果集,作为其他查询的条件或来源。
尤其是在一些不支持内联视图的数据库,此时有两种选择,一种是视图,一种是临时表。
临时表的好处是,建一个临时表(保存复杂查询的结果),则将n次复杂查询改为n-1次单表的简单查询+1次复杂查询。
但是临时表不可滥用,如多个数据源切换时可能有问题,还有其他问题。
5、数据库参数
比如数据库内部各种缓存,池等的size等
6、sql优化
sql优化是个比较复杂不易一次说全面的问题,但是大体可以分两个思路,形式优化和逻辑优化,具体要看查询计划
<逻辑优化>比如有col1,col2,两列可供查询过滤,恰逢在一些时候,两列虽然业务含义不同但是过滤效果一样,此时可选择更快的列
<形式优化>sql优化的原理就是寻找等效更优形式。前提是等效,目的是更优。
<函数优化>使用更快的函数,比如字符串列=日期列一般要to_date(char)=date或char=to_char(date),一般to_date会慢些
<低端失误>比如col like '12345',col='12345'
<无关子查询,相关子查询>in和exists的使用
<集合运算>差集,并集
<关键字留意>not,distinct,group by,order by,null等
<过程分析>比如一个查询要用sum函数,对100条,200条,300条。。。的求和时间承等差递增形式,
那么基本可以确定问题就是在于sum函数上,如sum(case when taskfinished='1' then 1 else 0 end) as completenum,这时应考虑如何避免函数内复杂逻辑
<统计答案>做统计查询时,常要对全年数据统计,数据量太大,不妨以天为单位(统计最小单位)保存每单位的统计结果,直接操纵统计结果,得到答案。
这种对于历史报表的统计有明显效果,对于实时报表的统计也有一定参考价值,因为有的实时统计其实可以牺牲一点实时性。
<分页>