优化思路整理-3-sql优化

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，这时应考虑如何避免函数内复杂逻辑

<统计答案>做统计查询时，常要对全年数据统计，数据量太大，不妨以天为单位（统计最小单位）保存每单位的统计结果，直接操纵统计结果，得到答案。

这种对于历史报表的统计有明显效果，对于实时报表的统计也有一定参考价值，因为有的实时统计其实可以牺牲一点实时性。

<分页>