sql_优化



[03] SQL优化

1.SQL优化的实质
　　充分利用索引；
　　访问尽量少的数据块；
　　减少表扫描的I/O次数；
　　尽量避免全表扫描和其他额外开销；

2.oracle数据库常用的两种优化器
　　RBO(rule-based-optimizer)：基于规则的优化器，根据优先级来决定执行计划.
　　CBO(cost-based-optimizer)：基于开销的优化器，根据表及索引的状态信息来决定执行计划.

3.驱动表和被驱动表　　

　　多表嵌套连接时，先全表扫描驱动表，将驱动表返回的结果集一行一行去匹配被驱动表（使用索引），所以应该选择小表作为驱动表，大表作为被驱动表。

　　在基于规则的优化器RBO中，ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表、驱动表)被最先处理，所以要选择记录数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

　　在基于成本的优化器CBO中，ORACLE会选择最合适的驱动表，但不一定选择较小的表作为驱动表。

4.减少表扫描的I/O次数

　　访问数据库时，ORACLE会在内部执行许多如解析SQL语句, 估算索引的利用率, 绑定变量, 读数据块等工作。每向数据库发送一条语句，都会用掉很多服务器资源，所以高流量网站都将查询语句缓存起来。Memcached是一种分布式内存缓存系统，它可以减轻数据库的负载，来加速基于动态数据库的网站。

　　使用ROWID删除重复记录(最高效)： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

　　避免循环查询，将SQL放在循环语句中会给你的数据库增加负担，可以使用存储过程进行批量操作（存储过程是预编译的）。

　　使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

5.其他额外开销

　　ORACLE采用自下而上的顺序解析WHERE子句，所以表之间的连接必须写在其他WHERE条件之前，而将能过滤掉最大数据量的条件写在WHERE的末尾。

　　ORACLE在解析的过程中, 会通过查询数据字典将SELECT中的'*'依次转换成所有的列名，所以应该在查询的字段上加上表的别名。

　　带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.

　　用Where子句替换HAVING子句, HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。

　　当进行包含一对多表信息的查询时，用EXISTS替换DISTINCT，因为RDBMS核心模块将在子查询的条件一旦满足后，会立刻返回结果。

　　用UNION ALL代替UNION合并两个查询结果集，先以UNION-ALL的方式合并, 然后再排序输出；使用UNION ALL不需要排序，但会重复输出相同的记录。

　　ORDER BY子句使用索引的条件：ORDER BY中所有的列必须定义为非空，ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。　

　　使用LIMIT来限定你想选定的数据的行数，否则会遍历所有行。

　　不要使用全词通配符(%hello%)，用前缀通配符(hello%)或后置通配符(%hello)。在百万数量级的数据上采用全词通配符来搜索会让你的数据库当机。

　　采用join来替换子查询：虽然子查询很有用，但可用join语句替换它，join语句执行起来更快：　　　　

SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_post FROM authors a
--替换成
SELECT a.id, MAX(p.created) AS latest_post FROM authors a INNER JOIN posts p ON (a.id = p.author_id) GROUP BY a.id

 6.in和exists的性能区别

　　in和exists主要是造成了驱动顺序的改变（性能变化的关键），我们以驱动表的快速返回为目标（会考虑到索引及结果集的关系）。

　　in：以内层表为驱动表。内层子查询结果集较少，主查询中的表较大且又有索引时应使用in；

　　exists：以外层表为驱动表。外层主查询结果集较少，子查询中的表较大且又有索引时应使用exists；　　

--EXISTS的执行流程
select * from t1 where exists ( select null from t2 where y = x );
--可以理解为:
for x in ( select * from t1 ) loop
  if ( exists ( select null from t2 where y = x.x ) ) then 
     OUTPUT THE RECORD
  end if

 　　in不对null进行处理：select 1 as x from dual where null in (0, 1, 2, null);