01-SQL优化学习-基本概念

第0章 环境

备份scott用户：
oracle*o11g-/home/oracle>$expdp \' / as sysdba \' dumpfile=expdp_scott.dump logfile=expdp_scott.log schemas=scott 

初始化环境：
oracle*o11g-/home/oracle>$sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 27 13:08:43 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@o11g>grant dba to scott; 

Grant succeeded.

SYS@o11g>alter user scott account unlock ;
SYS@o11g>alter user scott identified by tiger; 
SYS@o11g>conn scott/tiger;
SCOTT@o11g>create table test as select * from dba_objects ;

复制

第1章 SQL优化必懂概念

1.1、基数（CARDINALITY）

某个列的唯一键（Distinct_Keys）的数量；

主键列的基数等于表的总行数；

一般情况下，当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描；

1.2、选择性（SELECTIVITY)

基数与总行数的比值再乘以100%就是某个列的选择性；

1.3、收集表统计信息

begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size ?1/auto',
                                no_invalidate    => FALSE,
                                degree           => 1,
                                cascade          => TRUE);
end;
/
复制

1.4、获取表每个列的基数与选择性

SCOTT@o11g>select a.column_name,
       b.num_rows,
       a.num_distinct Cardinality,
       round(a.num_distinct / b.num_rows * 100, 2) selectivity
  from dba_tab_col_statistics a, dba_tables b
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = 'SCOTT'
       and a.table_name = 'TEST';

COLUMN_NAME	  NUM_ROWS CARDINALITY SELECTIVITY
--------------- ---------- ----------- -----------
OWNER		     86963	    30	       .03
OBJECT_NAME	     86963	 52421	     60.28
SUBOBJECT_NAME	     86963	   142	       .16
OBJECT_ID	     86963	 86963	       100
DATA_OBJECT_ID	     86963	  9077	     10.44
OBJECT_TYPE	     86963	    45	       .05
CREATED 	     86963	   925	      1.06
LAST_DDL_TIME	     86963	  1026	      1.18
TIMESTAMP	     86963	  1065	      1.22
STATUS		     86963	     1		 0
TEMPORARY	     86963	     2		 0
GENERATED	     86963	     2		 0
SECONDARY	     86963	     2		 0
NAMESPACE	     86963	    21	       .02
EDITION_NAME	     86963	     0		 0

15 rows selected.
复制

1.5、什么样的列必须创建索引呢？

当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引；

只有大表才会产生性能问题

1.6、抓出必须创建索引的列

1.6.1、刷新数据库监控信息

 begin
   dbms_stats.flush_database_monitoring_info;
 end;
 /
复制

1.6.2、获取需要创建索引的列

select r.name            owner,
       o.name            table_name,
       c.name            column_name,
       equality_preds, --等值过滤
       equality_preds, --等值join，比如where a.id=b.id
       nonequijoin_preds, -- 不等值join
       range_preds, --范围过滤次数 > >= < <= between and 
       like_preds, --like过滤
       null_preds, --null过滤
       timestamp
  from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
 where o.obj# = u.obj#
   and c.obj# = u.obj#
   and c.col# = u.intcol#
   and r.name = 'SCOTT'
   and o.name = 'TEST';
复制

1.7、直方图

for all columns size 1 表示对所有列都不收集直方图；

通过指定 dbms_stats 的 method_opt 参数，来创建直方图。在 method_opt 子句中有三个相关选项，即 skewonly、repeat 和 auto。
复制

“skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发现一个索引中具有不均匀分布的列，它将为该索引创建直方图，以帮助基于成本的 SQL 优化器决定是使用索引还是全表扫描访问。
复制

1.8、回表（TABLE ACCESS BY INDEX ROWID）

通过索引中记录的rowid访问表中的数据就叫回表；

回表一般是单块读；

回表次数太多会严重影响SQL性能；

1.9、获取数据存放的块数量

SCOTT@o11g>select count(distinct dbms_rowid.rowid_block_number(rowid)) from test where owner='SYS';
复制

1.10、集群因子

集群因子用于判断索引回表需要消耗的物理I/O次数；
集群因子介于表的块数和表行数之间；

如果集群因子与块数接近，说明表的数据基本上时有序的，而且其顺序基本与索引顺序一样。这样在进行索引范围或者索引全扫描的时候，回表只需要读取少量的数据块就能完成；

如果集群因子与表记录接近，说明表的数据和索引顺序差异较大，在进行索引范围扫描或索引全扫描的时候，回表会读取更多的数据块；

集群因子只会影响索引范围扫描（index range scan）以及索引全扫描（index full scan），只有这两种索引扫描方式会有大量数据回表；

集群因子影响索引回表的物理I/O次数；

1.11、表与表之间的关系

1:1

1:N

N:N