[技术干货] HDZ读书月-SQL优化核心思想读书笔记

阅读心得 LEARNINGS
  1. Oracle的Cost计算公式

单块读耗时:IO寻道寻址耗时+块大小/IO传输速度,典型的估算值:10+8K/4K=12ms
多块读耗时IO寻道寻址耗时+db_file_multiblock_count*块大小/IO传输速度,
  1. SQL优化的核心思想

就是想方设法减少SQL的物理I/O次数(不管是单块读次数还是多块读次数)徐老师还说,可以理解为减少sql 扫描的数据块数。即使有缓存,减少块的扫描次数也可以降低CPU的占用。
  1. 子查询非嵌套(SubqueryUnnesting)

当where子查询中有in、notin、exists、notexists等,CBO会尝试将子查询展开(unnest),从而消除FILTER,这个过程就叫作子查询非嵌套。子查询非嵌套的目的就是消除FILTER。
  1. 视图合并(View Merge)

当SQL语句中有内联视图(in-line view,from后面的子查询),或者SQL语句中有用create view创建的视图,CBO会尝试将内联视图/视图拆开,进行等价的改写,这个过程就叫作视图合并。如果没有发生视图合并,在执行计划中,我们可以看到VIEW关键字,而且视图/子查询会作为一个整体。如果发生了视图合并,那么视图/子查询就会被拆开,而且执行计划中视图/子查询部分就没有VIEW关键字。
  1. 谓词推入(Pushing Predicate)

当SQL语句中包含不能合并的视图,同时视图有谓词过滤(也就是where过滤条件),CBO会将谓词过滤条件推入视图中,这个过程就叫作谓词推入。谓词推入的主要目的就是让Oracle尽可能早地过滤掉无用的数据,从而提升查询性能。
6.两个超大表人工分区超大表为了避免PGA不够,可以使用人工分析的方式1)对连接列增加一个伪列,例如ora_hash(object_id,4),把object_id哈希为0、1、2、3、42)使用这个伪列对两个表分区
7.分页查询
多表连接消除排序

对于主表(需要排序的表)来说,这个关联的表也相当于一个过滤条件,同样也只有大部分的数据满足查询条件的情况下可以通过index full scan来消除排序。要求以排序表为驱动表,两表嵌套循环

  1. 嵌套循环的算法

驱动表返回一行数据, 通过连接列传值给被驱动表,驱动表返回多少行, 被驱动表就要被扫描多少次。
在执行计划中,离NESTED LOOPS关键字最近的表就是驱动表。
  1. HASH连接的算法

两表等值关联,返回大量数据,将较小的表选为驱动表,将驱动表的“select列和join列”读入PGA中的work area, 然后对驱动表的连接列进行hash运
算生成hash table, 当驱动表的所有数据完全读入PGA中的work area之后,再读取被驱动表(被驱动表不需要读入PGA中的workarea),对被驱动表的
连接列也进行hash运算, 然后到PGA中的work area去探测hash table, 找到数据就关联上,没找到数据就没关联上。
  1. 排序合并连接的算法

两表关联,先对两个表根据连接列进行排序,将较小的表作为驱动表, 然后从驱动表中取出连接列的值,到已经排好序的被驱动表中匹配数据,如果匹配上数据,就关联成功。 驱动表返回多少行,被驱动表就要被匹配多少次,这个匹配的过程类似嵌套循环,但是嵌套循环是从被
驱动表的索引中匹配数据,而排序合并连接是在内存中(PGA中的workarea) 匹配数据。排序合并连接主要用于处理两表非等值关联。
4.笛卡儿连接
两个表关联没有连接条件的时候会产生笛卡儿积,这种表连接方式就叫笛卡儿连接。
执行计划中MERGE JOIN CARTESIAN就表示笛卡儿连接。
在多表关联的时候,两个表没有直接关联条件,但是优化器错误地把某个表返回的Rows算为1行(注意必须是1行),这个时候也可能发生笛卡儿连接。
5.标量子查询优化建议
尽量避免使用标量子查询,假如主表返回大量数据,主表的连接列基数很高,那么子查询中的表会被多次扫描,从而严重影响SQL性能。
当SQL里面有标量子查询,我们可以将标量子查询等价改写为外连接,从而使它们可以进行HASH连接。


重点摘录 NOTES
  1. 我们如果将SQL改写为in/not in这种写法,子查询虽然被固化为整体,但是子查询中没有主表连接列字段,这个时候CBO就不会选择FILTER。

  2. 当CBO选择hash join时,连接谓词也不会下推

  3. 常量的谓词推入对性能的提升都是有益的。常量的谓词推入就是谓词是正常的过滤条件,而非连接列。

  4. 分页语句中也不能有distinct、groupby、max、min、avg、union、unionall等关键字。因为当分页语句中有这些关键字,我们需要等表关联完或者数据都跑完之后再来分页,这样性能很差

SQL优化核心思想day4.png