sql优化笔记

表空间、段、区、数据块


数据库：


表空间：oracle中最大的逻辑存储结构；
表空间与物理上的数据文件相对应，一个表空间可以对应多个数据文件，但是一个数据文件只能对应一个表空间；
一个表空间的大小等于构成该表空间的所有数据文件大小的总和；

自动创建的表空间：SYSTEM、SYSAUX、TEMP、EXAMPLE、UNDOTBS1、USERS；

段：oracle中的一组盘区，这组盘区组成了被oracle视为一个单位的数据库对象，例如表或索引。
因此，段一般是数据库终端用户将处理的最小存储单位；

按照段中所存储数据的特征，可以将段分为5种类型：数据段、索引段、临时段、LOB段和回退段；

区：磁盘空间分配的最小单位；区由一个或多个数据块组成，一个或多个区组成段；


数据块：用来管理存储空间的最基本单位，也是最小的逻辑存储单位；
oracle数据库是以块为单位进行逻辑读写操作的；


id


operation：操作


name：对象名称；


rows：预估返回的记录数；


bytes：预估返回的字节数；


tempSpc：预估使用的临时表空间大小；


Cost：消耗；


CPU：CPU消耗；
IO：IO消耗；


time：预估消耗的时间；


Psart：访问的第一个区；
Pstop：访问的最后一个区；


***********************************************************************************
（1）为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
在这些字段上建立索引，可以有效地避免排序操作。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
（2）在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。
（3）为经常用作查询选择的字段，建立索引。
（4）在经常用作表连接的属性上，建立索引。
（5）考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。
除了以上原则，在创建索引时，我们还应当注意以下的限制：
（1）限制表上的索引数目。
对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
（2）不要在有大量相同取值的字段上，建立索引。
在这样的字段（例如：性别）上建立索引，字段作为选择条件时将返回大量满足条件的记录，优化器不会使用该索引作为访问路径。
（3）避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。
由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。
此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。
（4）对复合索引，按照字段在查询条件中出现的频度建立索引。
在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。
因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。
因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
（5）删除不再使用，或者很少被使用的索引。
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，
从而减少索引对更新操作的影响。




************************************************************************************
减少数据库的访问/表连接/表字段/存储过程/内部函数；


1、3种优化器；CBO(first_rows、all_rows)
2、基表选择；
3、where子句的顺序；
4、执行计划执行顺序；
5、索引失效：运算、通配符、is null/is not null;
6、索引等级：相同等级、不明等级、多列索引


7、执行计划：
rows：返回结果集数；
cost：CPU成本；
Time：时间
access：这个谓词条件的值会影响到访问路径；
filter：谓词条件的值不会影响到访问路径，只起到过滤作用；
表的访问方式：全表扫描、rowid扫描、索引扫描（索引唯一、索引范围、索引全扫、索引快速）
连接类型：排序-合并、嵌套循环、哈希连接
哈希连接：只适用于等值连接；
潜逃循环：适合小数量级的连接；
  索引类型：B树索引（有限记录）、位图索引（数据重复率高）、全文索引（全文搜索）
  分区：范围分区（过期化处理）、哈希分区（数据均匀分布）、列表分区（按字段值分区）、组合分区；
 


1、oracle 3种优化器：rule（规则）、cost（成本）、choose（选择）
为了使用基于成本的优化器，你必需经常运行analyze


2、访问table的方式：全表扫描、通过rowid访问表；
   rowid包含了表中记录的物理位置信息，oracle采用索引实现了数据与物理位置之间的联系；
   
3、共享sql语句：
   1、与共享池中的sql字符上完全相同；2、所指的对象必需完全相同；3、必需使用相同名字的绑定变量；
   这个功能并不适用于多表连接；
   
4、表名顺序（只在基于规则的优化器中有效）
   from 表（2个表） 中最右边的先被解析，因此最右边的是基础表，from 表（3个表） 中，被其他两个表引用的表是基础表；


5、where子句中的连接顺序
   oracle采用自下而上的顺序解析where子句，因此，表之间的连接必需写在其他where条件之前，哪些可以过滤掉最大数据记录的条件
   必需写在where子句末尾；
   
6、select子句避免使用'*'；


7、减少访问数据库的次数；


8、使用decode函数来减少处理时间；
select count(decode(dept_no, '0020', 'x', null)) d0020_count,
count(decode(dept_no, '0030', 'x', null)) d0030_count
from emp where ename like 'smith%';
decode函数也可以运用于group by和order by子句中；


9、整合简单、无关联的数据库访问；
SELECT E.NAME , D.NAME , C.NAME 
FROM CAT C , DPT D , EMP E,DUAL X 
WHERE NVL(‘X',X.DUMMY) = NVL(‘X',E.ROWID(+)) 
AND NVL(‘X',X.DUMMY) = NVL(‘X',D.ROWID(+)) 
AND NVL(‘X',X.DUMMY) = NVL(‘X',C.ROWID(+)) 
AND E.EMP_NO(+) = 1234 
AND D.DEPT_NO(+) = 10 
AND C.CAT_TYPE(+) = ‘RD';


10、count(*)、count(1)并没有显著的性能差异；


11、用where子句替换having子句；


12、在含有子查询的sql语句中，要注意减少对表的查询；
    子查询中访问两次的优化为访问一次；
    
13、通过内部函数function提高sql效率；


14、使用别名alias；


15、用exists代替in（1对多的情况下）；
通常not exists 代替 not in效率更高；
select * from emp where empno > 0
and empno in (select deptno from dept where loc = 'MELB')
替换为
select * from emp where empno > 0
and exist (select deptno from dept where dept.deptno = emp.deptno
and loc = 'MELB');


15.2、用exists代替distinct
    select distince dept_no, dept_name
    from dept d, emp e
    where d.dept_no = e.dept_no
    替换为
    select dept_no, dept_name
    from dept d
    where exists(select 'x' from emp e
    where e.dept_no = d.dept_no);


16、表连接替换exists; 外连接除外；


explain plan分析sql语句
    执行顺序，从内到外，从上到下，同一层次，操作号小的先执行；
    nested loop不按照上述规则执行，而是直接看操作号，操作号小的先执行；


17、用索引提高效率；

定期重构索引：alter index indexName rebuild tableSpaceName；

大多数情况下，优化器通过where子句访问索引；

    oracle对索引有两种访问模式：索引唯一扫描、索引范围扫描；
    通过索引扫描获得rowid，通过rowid访问表数据，如果select返回的都是索引列，则直接返回索引值即可，不用通过rowid访问表数据；
    
    where子句中，
    索引失效：运算、通配符、is null/is not null
    如果索引所对应的值的第一个字符通配符（%、_等）开始，或者对索引运算（SAL*12 > 2500），
    或者索引列是函数的一部分，或者在索引上使用NOT，或者在索引上使用is null或is not null，
    或者改变索引类型（to_char(indexName)），索引将不被采用；
    强制索引失效：索引 + 0 = 10；索引||'' = 'A';
    
    索引范围查询：1、基于一个范围的检索；2、基于非唯一索引的检索；
    
    基础表的选择
    如果你使用的是CBO，优化器会检查sql语句中的每个表的物理大小、索引状态，然后选用花费最低的执行路径；
    如果你使用的是RBO，并且所有的连接条件都有索引对应，在这种情况下，基础表就是FROM子句列在最后的那个表；
    
18、多个平等索引：
    在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 
    这个规则只有当WHERE子句中索引列和常量比较才有效.
    如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的. 
    如果相同表中两个相同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级. 
    如果不同表中两个想同等级的索引将被引用, FROM子句中表的顺序将决定哪个会被率先使用，FROM子句中最后的表的索引将有最高的优先级. 
    
19、等式比较和范围比较
    当where子句中有索引列，oracle不能合并它们，oracle将用等式索引；？？？？？？？？？？？？索引合并？？？？
    DEPTNO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引；
    select ename from emp where deptno > 20 and emp_cat = 'a';
    oracle将使用emp_cat索引；
    
20、不明确的索引等级
    当oracle无法判断索引的等级高低差别，优化器将只能使用一个索引，它就是where子句中被列在最前面的。
    DEPTNO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引；
    select ename from emp where deptno > 20 and emp_cat > 'a';
    oracle将使用deptno索引；
    
23、自动选择索引
    如果表中有两个以上索引，其中有一个唯一性索引，而其他是非唯一性，在这种情况下，oracle将使用唯一性索引而完全忽略非唯一性索引；
    另一个例子：
    empno唯一索引，emp_type非唯一索引
    select * from emp where empno >= 2 and emp_type = 'A';
    oracle将先访问emp_type索引，再访问empno索引；
    
    
24、避免在索引上使用NOT
    当oracle遇到NOT，他会停止使用索引转而执行全表扫描；
    注意：在某些时候，oracle优化器会自动将NOT转化为相应的操作符；
    
25、用>=替代>（对索引比较的情况下）；
    
36、用union替换or（适用于索引列）
    通常情况下，用union替换where子句中的or将会起到较好的效果，对索引列使用or将造成全表扫描。
    注意，以上规则只针对多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择or而降低；
    
37、用in替换or；


38、避免在索引列上使用is null和is not null；导致停用索引；
避免在索引中使用任何可以为空的列，oracle将无法使用该索引；
对于单列索引，如果列包含空值，索引中将不存在此记录，对于复合索引，如果每个列都为空，索引中同样不存在此记录，
如果至少有一个列不为空，则记录存在于索引中。
如果索引列都为空，oracle将认为整个键值为空，而空不等于空，因此你可以插入1000条具有相同键值的记录，当然它们都是空；
因为空值不存在于索引列中，所以where子句中对索引列进行空值比较将使oracle停用该索引；


39、总是使用索引的第一个列
    如果索引是建立在多个列上，只有在它的第一个列被where子句引用时，优化器才会选择使用该索引；
    
40、用union-all替换union
    当sql语句需要nuion两个查询结果集合时，这两个结果集合会以union-all的方式被合并，然后在输出最终结果前进行排序；
    如果用union-all替代union，这样排序就不是必要了，效率会因此得到提高；
    
42、使用hints
对于表的访问，可以使用两种hints：full和rowid；
select /*+full(emp)*/ * from emp where empno = 7893;
通常，你需要采用table access by rowid的方式特别是当访问大表的时候，使用这种方式，你需要知道rowid或使用索引；
如果一个大表没有被设定为缓存（CACHED）表而你希望它的数据在查询结束时仍然停留在SGA中，
你就可以使用CACHE hint来告诉优化器把数据保留在SGA中，通常CACHE hint和full hint一起使用； 
select /*+full(worker) cache(worker)*/ * from work;

如果该索引的重复值过多而你的优化器是CBO，优化器就可能忽略索引，这种情况下，你可以使用hint强制索引；

oracle hints还包括：all_rows, first_rows, rule, use_nl, use_merge, use_hash等等；

43、用where替代order by（其实order by也能使用索引）
order by子句只在两种严格的条件下使用索引：
order by中所有的列必需包含在相同的索引中并保持在索引中排列顺序；
order by中所有的列必须定义为非空；
where子句使用的索引和order by子句中使用的索引不能并列；

46、避免使用耗费资源的操作
    带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的sql语句会启动sql引擎执行耗费资源的排序功能；