1.优化基本的概念

1.1 基数（CARDINALITY）

基数（cardinality）列唯一键（distinct_keys）的数量。
比如性别，该列只有男女之分，所以这一列基数为2，主键列的基数等于行数。
基数的高低影响列的数据分布。

当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描。
当然这个结论太绝对了，这里暂且记住5%这个界限就行。

如果某个列基数很低，该列数据分布就会非常不均衡，由于该列数据分布不均衡，会导致SQL查询可能走索引，也可能走全表扫描，还有可能导致ROWS估算错误。
如果一列的基数很低的列，应该怎么处理？组合索引，避免全表扫描。

在做SQL优化的时候，如果怀疑列数据分布不均衡，我们可以使用

select 列,count(*) from 表 group by 列 order by 2 desc;

来查看列的数据分布。

1.2 选择性（SELECTIVITY）

基数与总行数的比值再乘以100%就是某个列的选择性。单独看列的基数是没有意义的，基数必须对比总行数才有实际意义。

什么样的列必须要创建索引呢？当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能。
当然了，如果表只有几百条数据，那我们就不用创建索引了。

下面抛出SQL优化核心思想第一个观点：只有大表才会产生性能问题。

1.3 直方图（HISTOGRAM）

当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。
如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。

当列没有收集直方图统计信息的时候，CBO会认为该列数据分布是均衡的。

执行计划里面的Rows是假的。执行计划中的Rows是根据统计信息以及一些数学公式计算出来的。

在做SQL优化的时候，经常需要做的工作就是帮助CBO计算出比较准确的Rows。
注意：我们说的是比较准确的Rows。CBO是无法得到精确的Rows的，因为对表收集统计信息的时候，统计信息一般都不会按照100%的标准采样收集，即使按照100%的标准采样收集了表的统计信息，表中的数据也随时在发生变更。
另外计算Rows的数学公式目前也是有缺陷的，CBO永远不可能计算得到精确的Rows。
如果CBO每次都能计算得到精确的Rows，那么相信我们这个时候只需要关心业务逻辑、表设计、SQL写法以及如何建立索引了，再也不用担心SQL会走错执行计划了。

Oracle12c的新功能SQL Plan Directives在一定程度上解决了Rows估算不准而引发的SQL性能问题。

为了让CBO选择正确的执行计划，我们需要对owner列收集直方图信息，从而告知CBO该列数据分布不均衡，让CBO在计算Rows的时候参考直方图统计。

对列收集完直方图之后，CBO估算的Rows就基本准确了，一旦Rows估算对了，那么执行计划也就不会出错了。

为什么收集完直方图之后，Rows计算得那么精确，收集直方图究竟完成了什么操作呢？
对owner列收集直方图其实就相当于运行了以下SQL。

select owner,count(*) from test group by owner;

如果列数据分布均衡，基本上SQL不会出现问题；如果列数据分布不均衡，我们需要对列收集直方图统计。
在我们看来，只需要知道直方图是用来帮助CBO在对基数很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows就够了。
什么样的列需要收集直方图呢？当列出现在where条件中，列的选择性小于1%并且该列没有收集过直方图，这样的列就应该收集直方图。
注意：千万不能对没有出现在where条件中的列收集直方图。对没有出现在where条件中的列收集直方图完全是做无用功，浪费数据库资源。   

1.4 回表（TABLE ACCESS BY INDEX ROWID）

当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。
通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。
在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！

为什么返回表中5%以内的数据走索引、超过表中5%的数据走全表扫描？根本原因就在于回表。

在无法避免回表的情况下，走索引如果返回数据量太多，必然会导致回表次数太多，从而导致性能严重下降。

Oracle12c的新功能批量回表（TABLE ACCESS BY INDEX ROWID BATCHED）在一定程度上改善了单行回表（TABLE ACCESS BY INDEX ROWID）的性能。关于批量回表本文不做讨论。

什么样的SQL必须要回表？

select * from table where ...

这样的SQL就必须回表，所以我们必须严禁使用Select *。那什么样的SQL不需要回表？

select count(*) from table

这样的SQL就不需要回表。

当要查询的列也包含在索引中，这个时候就不需要回表了，所以我们往往会建立组合索引来消除回表，从而提升查询性能。

当一个SQL有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤（TABLE ACCESS BY INDEX ROWID前面有“*”），也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。

1.5 集群因子（CLUSTERING FACTOR）

集群因子介于表的块数和表行数之间。
如果集群因子与块数接近，说明表的数据基本上是有序的，而且其顺序基本与索引顺序一样。这样在进行索引范围或者索引全扫描的时候，回表只需要读取少量的数据块就能完成。
如果集群因子与表记录数接近，说明表的数据和索引顺序差异很大，在进行索引范围扫描或者索引全扫描的时候，回表会读取更多的数据块。
集群因子只会影响索引范围扫描（INDEXRANGESCAN）以及索引全扫描（INDEXFULLSCAN），因为只有这两种索引扫描方式会有大量数据回表。
集群因子不会影响索引唯一扫描（INDEXUNIQUESCAN），因为索引唯一扫描只返回一条数据。集群因子更不会影响索引快速全扫描（INDEXFASTFULLSCAN），因为索引快速全扫描不回表。

集群因子太大会严重影响索引回表的性能。
集群因子究竟影响的是什么性能呢？集群因子影响的是索引回表的物理I/O次数。
我们假设索引范围扫描返回了1000行数据，如果buffercache中没有缓存表的数据块，
假设这1000行数据都在同一个数据块中，那么回表需要耗费的物理I/O就只需要一个；
假设这1000行数据都在不同的数据块中，那么回表就需要耗费1000个物理I/O。
因此，集群因子影响索引回表的物理I/O次数。

请注意，不要尝试重建索引来降低集群因子，这根本没用，因为表中的数据顺序始终没变。
唯一能降低集群因子的办法就是根据索引列排序对表进行重建（create table new_table as select * from old_table order by 索引列），
但是这在实际操作中是不可取的，因为我们无法照顾到每一个索引。
怎么才能避免集群因子对SQL查询性能产生影响呢？集群因子只影响索引范围扫描和索引全扫描。
当索引范围扫描，索引全扫描不回表或者返回数据量很少的时候，不管集群因子多大，对SQL查询性能几乎没有任何影响。

在进行SQL优化的时候，往往会建立合适的组合索引消除回表，或者建立组合索引尽量减少回表次数。

如果无法避免回表，怎么做才能消除回表对SQL查询性能产生影响呢？
当我们把表中所有的数据块缓存在buffer cache中，这个时候不管集群因子多大，对SQL查询性能也没有多大影响，因为这时不需要物理I/O，数据块全在内存中访问速度是非常快的。

1.6 表与表之间关系

表与表之间关系关系型数据库中，表与表之间会进行关联，在进行关联的时候，我们一定要理清楚表与表之间的关系。
表与表之间存在3种关系。一种是1∶1关系，一种是1∶N关系，最后一种是N∶N关系。
搞懂表与表之间关系，对于SQL优化、SQL等价改写、表设计优化以及分表分库都有巨大帮助。
两表在进行关联的时候，如果两表属于1∶1关系，关联之后返回的结果也是属于1的关系，数据不会重复。
如果两表属于1∶N关系，关联之后返回的结果集属于N的关系。
如果两表属于N∶N关系，关联之后返回的结果集会产生局部范围的笛卡儿积，N∶N关系一般不存在内/外连接中，只能存在于半连接或者反连接中。
如果两个表时n:n关系，一定要把其中一个表汇总为1的关系。

如果我们不知道业务，不知道数据字典，怎么判断两表是什么关系呢？
我们以下面SQL为例子。

select * from emp e,dept d where e.deptno = d.deptno;

我们只需要对两表关联列进行汇总统计就能知道两表是什么关系。

SQL> select deptno,count(*) from emp group by deptno order by 2 desc;

    DEPTNO   COUNT(*)
---------- ----------
    30        6
    20        5
    10        3

SQL> select deptno,count(*) from dept group by deptno order by 2 desc;

    DEPTNO   COUNT(*)
---------- ----------
    10        1
    40        1
    30        1
    20        1

从上面查询我们可以知道两表emp与dept是N∶1关系。