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）先整体后局部
未完，待续。。。。。
