SQL优化——核心概念

1、基数(数据分布)

某个列唯一键（Distinct_Keys）的数量叫作基数。比如性别列，该列只有男女之分，所以这一列基数是2。主键列的基数等于表的总行数。基数的高低影响列的数据分布。

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

大家在以后的工作中一定要注意列的数据分布！

2、选择性

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

在进行SQL优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义，正是因为这个原因，我们才引出了选择性这个概念。

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

3、直方图（HISTOGRAM）

当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。

如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。

直方图是用来帮助CBO在对基数很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows就够了。

什么样的列需要收集直方图呢？当列出现在where条件中，列的选择性小于1%并且该列没有收集过直方图，这样的列就应该收集直方图。注意：千万不能对没有出现在where条件中的列收集直方图。对没有出现在where条件中的列收集直方图完全是做无用功，浪费数据库资源。

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前面有“*”），也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。

5、集群因子（CLUSTERING FACTOR）

集群因子用于判断索引回表需要消耗的物理I/O次数。

集群因子介于表的块数和表行数之间。

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

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

集群因子只会影响索引范围扫描（INDEX RANGE SCAN）以及索引全扫描（INDEX FULL SCAN），因为只有这两种索引扫描方式会有大量数据回表。

集群因子不会影响索引唯一扫描（INDEX UNIQUE SCAN），因为索引唯一扫描只返回一条数据。集群因子更不会影响索引快速全扫描（INDEX FAST FULL SCAN），因为索引快速全扫描不回表。

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

请注意，不要尝试重建索引来降低集群因子，这根本没用，因为表中的数据顺序始终没变。唯一能降低集群因子的办法就是根据索引列排序对表进行重建（create table new_table as select * from old_table order by 索引列），但是这在实际操作中是不可取的，因为我们无法照顾到每一个索引。

怎么才能避免集群因子对SQL查询性能产生影响呢？其实前文已经有了答案，集群因子只影响索引范围扫描和索引全扫描。当索引范围扫描，索引全扫描不回表或者返回数据量很少的时候，不管集群因子多大，对SQL查询性能几乎没有任何影响。

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

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

6、表与表之间关系

关系型数据库中，表与表之间会进行关联，在进行关联的时候，我们一定要理清楚表与表之间的关系。表与表之间存在3种关系。一种是1∶1关系，一种是1∶N关系，最后一种是N∶N关系。搞懂表与表之间关系，对于SQL优化、SQL等价改写、表设计优化以及分表分库都有巨大帮助。

两表在进行关联的时候，如果两表属于1∶1关系，关联之后返回的结果也是属于1的关系，数据不会重复。如果两表属于1∶N关系，关联之后返回的结果集属于N的关系。如果两表属于N∶N关系，关联之后返回的结果集会产生局部范围的笛卡儿积，N∶N关系一般不存在内/外连接中，只能存在于半连接或者反连接中。

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

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

查询结果如下：

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

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

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

从上面查询我们可以知道两表emp与dept是N∶1关系。搞清楚表与表之间关系对于SQL优化很有帮助。

举例，我们曾遇到一个案例，SQL运行了12秒，SQL文本如下：

select count(*) from a left join b on a.id=b.id;

案例中a与b是1∶1关系，a与b都是上千万数据量。因为a与b是使用外连接进行关联，不管a与b是否关联上，始终都会返回a的数据，SQL语句中求的是两表关联后的总行数，因为两表是1∶1关系，关联之后数据不会翻番，那么该SQL等价于如下文本。

select count(*) from a;

我们将SQL改写之后，查询可以秒出。如果a与b是n∶1关系，我们也可以将b表去掉，因为两表关联之后数据不会翻倍。如果b表属于n的关系，这时我们不能去掉b表，因为这时关联之后数据量会翻番。