SQL优化（一）基础概念

基数（cardinality）

表中某个列的唯一键的数量叫做基数，主键列的基数就是表中数据的总行数。

可以用select count(distinct 列名) from 表名来计算基数。

基数的高低影像列的数据分布。

例如：先用Scott账户创建一个测试表test

create table test  as select * from dba_objects

owner列和object_id的基数如下所示

select count(distinct owner),count(distinct object_id),count(*) from test

可以看出owner列的基数很小，再查询一下owner列的分布

可以看出owner列数据分布极不均匀，所以基数的大小代表数据分布情况。

选择性（selectivity）

选择性是某列的基数除以表数据的总行数在乘以100%计算出的值，代表该列数据分布均不均衡。

数据均衡分布是指该列基数中的值的条数大多在同一数量级上，或者最多与最少的数量级相差不大，例如主键列，各值的条数都是1。

要查看选择性，首先要对test表收集统计信息：

begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1',
                                no_invalidate    => false,
                                degree           => 1,
                                cascade          => true);
end;
--method_opt传入for all columns size 1代表不收集直方图

查看选择性，column_name为各列名称，num_rows为总行数，selectivity列即为选择性，cardinality为基数

select a.column_name,
       b.NUM_ROWS,
       a.num_distinct cardinality,
       round(a.num_distinct / b.NUM_ROWS * 100, 2) selectivity,
       a.HISTOGRAM,
       a.num_buckets
  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';

一般来说，选择性大于20，该列数据比较均衡，适合建立索引

直方图（histogram）

直方图是一种对数据分布情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导基于成本的优化器（CBO）根据数据的分布做出正确的选择。如果没有对基数低的列收集直方图信息，CBO会认为该列数据均衡分布，从而会影响CBO使用索引扫描还是全表扫描的决策，也就是影响SQL的执行计划。

 这个图的histogram列代表了是否收集直方图，NONE就是没有收集。执行以下程序对owner列收集直方图：

begin
  dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                method_opt       => 'for columns owner size skewonly',
                                no_invalidate    => false,
                                degree           => 1,
                                cascade          => true);
end;
--for columns owner size skewonly，对owner列收集，也可以对其他基数低的列收集
--method_opt传入for all columns size 1代表不收集直方图

现在看出owner的直方图已经被收集了,对owner列收集直方图相当于执行

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

然后将结果保存在shared_pool的数据字典中，CBO硬解析SQL时会参考数据字典的数据。

对于基数很低，选择性很低的列，强烈建议收集直方图。

数据块（data block）、区（extent）、段（segment）

数据块是oracle数据库存储的最小逻辑单元，数据块为2k、4k、8k、16k、32k等，默认块大小是8k，可以通过以下语句查询

select  bytes/blocks,f.* from dba_data_files f;

通过首列可以看出该数据库的块大小为8k。

区（extent）表示一系列连续的数据块集合，是比块更大一级的存储结构，是表中数据增长的基本单位。一个Oracle对象包含至少一个数据区。一个区的所有数据块都在同一个物理文件内。

段（segment）是为特定的数据库对象（如表段、索引段、回滚段、临时段）分配的一系列数据区，一个数据库对象就是一个段。一个段由多个区组成，对于表来说，一般一个表对应一个数据段（如果没有clob/blob字段）。一个段的所有区的分布可以跨多个物理文件。

现在有一个表GSPUSER，该表只有一个段，查询该段下的所有区：

select * from dba_extents where owner='GMFSSC' and segment_name='GSPUSER'

EXTEND_ID为区的id号，FILE_ID为物理文件的id号，BLOCK_ID是数据块的id号，BLOCKS是区的数据块的数量 。

再查GSPUSER段本身的信息：

select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS
from dba_segments 
where owner='GMFSSC' and segment_name='GSPUSER'

可以看出该段有20个区，共计640个数据块。 

逻辑读/物理读（consistent gets/physical reads）

 Oracle数据库读写的基本单位是数据块。当用户最终得到的结果可能只是某个数据块中的几行或几列。
物理读： 当一个查询语句被执行时，Oracle服务器进程会将相关的数据块从磁盘的数据文件中加载到内存中的一块区域(buffer cache)。这个过程就叫做物理读。每读取一个数据块，即是一次物理读。物理读是真正操作磁盘IO，速度很慢。

逻辑读：将数据从buffer cache内存读取到PGA中，之后再返回给用户的过程。

由于内存中操作较快，所以得出同等数据量，逻辑读速度明显优于物理读，在SQL优化过程中要减少物理读次数。

索引（index）

索引是一种用于提高数据检索速度的数据库对象。对一个列创建索引，索引会包含该列键值以及对应行的rowid，rowid是行的唯一标识。

索引一般有两种数据结构：B+树和位图。

按类型分为：普通索引，唯一索引，主键也是一种唯一索引。

创建一个索引用以下语句，可以支持多列创建索引，又叫组合索引：

create index 索引名 on 表名(列名1,...列名n..)

SQL执行计划（SQL plan）

执行计划是对SQL执行的过程解析，执行计划显示了数据库如何处理SQL，包括数据的检索顺序、使用的索引、连接类型以及数据的处理方式等。通过执行计划可以看出SQL语句是怎么执行的，有没有按照预计的方案执行，有没有按照最高效的方式执行。

select * from test where owner='SCOTT'

对以上SQL解释执行计划，得到下图

 由于这个SQL比较简单，所以执行计划也很简单，只需要一步操作全表扫描（TABLE ACCESS FULL）就可以检索出数据。

现在对owner列创建索引

   create index idx_test_owner on test(owner)

再次查看执行计划

 因为我们对owner列已经收集过统计信息，CBO知道了该列的分布情况，使用了索引范围扫描（INDEX RANGE SCAN），并且少量回表取得数据。如果没有收集过统计信息，CBO认为该列是均匀分布的，很有可能会走全表扫描。

单条回表/批量回表

通过索引中的rowid再去访问表中的数据叫做回表，回表分单条和批量：

在执行计划中分别对应

TABLE ACCESS BY INDEX ROWID；

TABLE ACCESS BY INDEX ROWID BATCHED；

这个图中就存在单条回表，不过这里回表次数很少，性能影响比较小。

如果是大量单条回表，并且数据没有被缓存在buffer cache里，将会产生大量的物理读，会有严重的性能问题。在SQL优化中要尽量消除单条回表。

批量回表改善了单条回表的性能问题，但出现次数仍不宜过多。

总结

SQL优化涉及概念较多，需要不懂的概念及时查阅理解。