SQL优化

SQL优化的各种误区

1.对讹传辨知

　　a）COUNT(*)与COUNT(列）

　　　　- COUNT(*)针对全表,COUNT(列)针对列，两者不等价，无法比较其快慢。

　　　　  - 对于COUNT(列)，优化器的算法是根据列的偏移量决定性能，列越靠后，访问的开销越大。COUNT(*)与偏移量无关，所以COUNT(*)最快，COUNT(最后列)最慢。

　　b）SQL编写顺序

　　　  - 表的查询顺序（针对多表查询）：Oracle的解析器从右到左的顺序处理From子句中的表名，因此From子句中卸载最后的表（基础表）将被最先处理。在From子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

　　　　- where子句中的连接顺序：Oracle采用自下而上的顺序解析Where子句，根据这个原理，当在where子句中有多个表连接时，where子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在where子句中的最后。

　　　　 - 以上两种优化说法，基于RBO（Rule规则）确实如此。但是现在已是基于CBO（Cost代价），这些已经过时。

 

　　c）IN与EXISTS

　　　　- 一般来说，anti的反连接算法比filter更高效，但是在10g时，Oracle的这个算法不完善，必须要指定非空，才可以让not in用anti算法。在11g的时候，这个情况已经改变了，无论是not in 还是not exists，无论列是否为空，都可以走到Oracle比较先进高效的anti反连接算法。

 

2.少做事

　　a）全局临时表

　　　　-构建基于Session的全局临时表(global temporary table t_name on commit preserve rows)：退出session该表记录会自动清空

　　　　-构建基于事务的全局临时表(global temporary table t_name on commit delete rows)：commit提交后，不等退出session，该表记录就会自动清空

　　b）分区表(create table t_name (columns...) partition by range(column)(partition p1 values...))

　　　　

　　c）递归函数调用

　　　　- 避免SQL中的函数调用，可以减少消耗

　　　　- 减少SQL中的函数调用，函数调用不要放在结果集中

　　d）集合写法

　　e）只取所需的列

　　　　- 只选择需要的列，而不用*。访问视图会变更快、表连接访问提速、索引不需要回表。

　　

3.根据场景选择技术

　　a）系统是个人用还是大家用？当前是忙还是闲？

 

　　b）系统是多读还是少读？访问量是多还是少？

　　　　- 索引的坏处：更新插入，因为索引性能会差；建索引引发锁表导致问题；建索引导致排序性能问题

　　　　- 缓存结果集

　　　　- 物化视图

　　　　- 位图索引

　　　　- Block如何设计

　　c）先看表是大还是小？返回记录是多还是少？

　　　　- 分区也会慢

　　　　- 绑定变量居然引发故障

 

4.考虑需求最小化

　　a）复杂的需求抽象简单化

　　b）需求有没有多余

　　　　- 需要去重吗

　　　　- 需要排序吗

　　　　- 需要监控吗

 

5.SQL改造等价性

　　a）看似等价，其实不等价

　　　　- insert all

insert all
    into tmp_transaction
    into tmp_session
select * from dba_objects;

不等价于

insert into tmp_transaction as select * from dba_objects;
insert into tmp_session as select * from dba_objects;

 

　　　　

　　　　- select max(), min()

select min(object_id), max(object_id) from t;
不等价于
select max(object_id) from t;
select min(object_id) from t;


select min(object_id), max(object_id) from t;
等价于
select max,min from (select max(object_id) max from t) a,
(select min(object_id) min from t) b;

 

　　　　- in与><范围

　　　　- count(*)与count（列）

 

　　b）看似不等价，其实等价

 

　　　　-关于表是否有记录的判断

 

 

6.需求是顶级优化（需求优化）

　　a）需求优化需要不断的考虑

　　　　- 界面权限设计优化（并非所有操作都需要做权限判定）

　　　　- 界面实时刷新改良（先显示汇总，再由用户点击展示）

　　　　- 单脚本对应多指示（不但多采集，还要去屏蔽规则）

　　　　- 表单展示的异步载入（界面内容自上而下逐步展现）

 

　　b）如下问答解决了很多性能问题

　　　　- 排序有必有吗

　　　　- 历史数据要保留多久

　　　　- 这个需求过时了吗

　　　　- 采集频率真要如此频繁吗

 

7.发现问题方法论

　　a）先判断后动手

　　　　- 故障未解决：备份相关证据、收集日志；分析主要矛盾，解决问题

　　　　- 故障已消除：分析系统的日志，了解故障的时段

　　b）先动态后静态

　　c）先整体后局部

 

---

 

未完，待续。。。。。