2.3.2 数据分布的影响

1.  均匀分布的情况

当数据分布是完全均匀的,则会发现理想选择度和实际选择度是相等的,也就是说理想选择度是一种完全理想化的数据分布状态。当CBO优化器在计算索引扫描成本的时候,如果缺少索引列直方图信息,则会认为数据是完全均匀分布的。这个时候无论COL的取值是多少,其选择度都是一样的,即理想选择度等于实际选择度。此时成本的比较上,通过索引扫描再回表取数要远小于全表扫描,如图2-7所示。

 book_ch02_07

图2-7  数据均匀分布

然而,这种情况基本上是不存在的。如果索引列上的数据分布较为均匀,那么与CBO优化器计算的结果偏差就不会太大,反之,则会导致执行计划跑偏。所以,索引设计的另一原则就是“尽可能地保证索引列数据分布均匀一些”。

说到数据的分布均匀程度会影响索引的效率,先来使用理想选择度和实际选择度来考察一个均匀分布的例子吧。具体步骤和SQL语句如下所示:

步骤1  创建一下相关的表和索引:

SQL> create table alex_t03 (
2  id number,
3  col1 number,
4  name varchar2(100)
5  );
SQL> alter table alex_t03 add constraint pk_alex_t03
2  primary key (id) using index;
SQL> create index idx_alex_t03_col1 on alex_t03 (col1);

步骤2  初始化数据,顺序均匀地插入10万行数据,col1列NUM_DISTINCT值控制为10:

SQL> declare
2    seq number := 1;
3  begin
4    for i in 1 .. 100000 loop
5      insert into alex_t03 values (i, seq, 'alex');
6      if mod(i, trunc(100000 / 10)) = 0 then
7        seq := seq + 1;
8      end if;
9    end loop;
10    commit;
11  end;
12  /

步骤3  收集一下表和索引的统计信息,设置method_opt参数为’FOR ALL COLUMNS SIZE 1′,先不收集直方图信息,看看数据分布对索引选择的影响。

SQL> exec dbms_stats.gather_table_stats('alex', 'alex_t03', method_opt=>'FOR ALL COLUMNS SIZE 1')
SQL> exec dbms_stats.gather_index_stats('alex', 'pk_alex_t03')
SQL> exec dbms_stats.gather_index_stats('alex', 'idx_alex_t03_col1')

此时,可以看到索引列col1上的数据分布是完全均匀的。所以,不论是col1=1,还是col1=5的情况,都是能够很好地利用索引扫描的。理想选择度和实际选择度均为10%,即优化器计算的选择度和实际情况是完全相符的。示例如下所示:

SQL> select * from alex_t03 where col1=1;
SQL> select * from alex_t03 where col1=5;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   | 10118 |   118K|    54   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T03          | 10118 |   118K|    54   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T03_COL1 | 10118 |       |    28   (0)|
--------------------------------------------------------------------------------------

2.  非均匀分布的情况

如果说在索引列上的数据分布不是均匀的呢?如图2-8所示,来看一个比较极端的例子。COL=2的行数占总行数的60%,那么查询COL=2的时候,实际选择度则为60%,然而COL为其他值的实际选择度仅为10%。如果按照实际选择度来走的话,COL=2走全表扫描,其他情况走索引扫描是一个比较理想的选择。

但是,优化器的实际处理情况未必如此。这个时候,如果我们没有COL列上直方图,优化器会认为数据是均匀分布的,则理想选择度 = 1/NUM_DISTINCT = 20%,很有可能都走了全表扫描。这对于COL不为2的情况几乎是不能接受的。

 book_ch02_08

图2-8  数据不均匀分布

这种情况相信在很多应用中都是存在的,只是有可能不会表现得这么极端而已。直方图的收集对于大表来说,是不可想象的,因为收集一次开销太大,对高并发的OLTP系统几乎是不可能完成的任务。那如何去解决这个问题呢?这又将说回到索引设计的话题上来了。对于这种数据分布极度倾斜的情况就不应该建索引,如果不得不建的话,尽可能收集直方图。

回到前面的例子,接下来我们人为来制造一些麻烦吧,打乱原有的均匀分布。如下例所示,可以看到,col1=5的记录行已经占了全表记录的总数的50%,col1索引列已经严重倾斜。但是,抛开col1=5的情况,其他几种情况,都还是有比较好的选择度的。

SQL> update alex_t03 set col1=5 where col1 between 4 and 8;
SQL> commit;
SQL> select col1,count(col1) from alex_t03 group by col1;
COL1   COUNT(COL1)
---------- -----------
5         50000
1         10000
2         10000
3         10000
9         10000
10         10000

表经历了大批量的DML操作,统计信息已经过旧了。如果仍采用旧的统计信息,上面基于col1的查询,仍然都将继续索引扫描的方式,这对于col1=5来说,就不是最优的了。

此时,需要重新收集一次表和索引的统计信息,仍然选择不收集直方图。因为没有直方图信息,优化器将无视数据分布的倾斜。下面的例子中,col1=1和col1=5的情况,都走了全表扫描,显然这个结果也是不能让人满意的。

SQL> select * from alex_t03 where col1=1;
SQL> select * from alex_t03 where col1=5;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 17406 |   203K|    85   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T03 | 17406 |   203K|    85   (3)|
-------------------------------------------------------------------

利用理想选择度和实际选择度来分析一下吧。数据分布倾斜后,col1=5的实际选择度变为50%,其他情况仍为10%。但是没有直方图信息,优化器意识不到这一点,它会认为理想选择度是从10%变为了16.7%,都应该走全表扫描了。

换个角度来思考一下,如果我们没有idx_alex_t03_col1这个索引,情况会如何呢?当然也将是全部走全表扫描的。那不得不反问一句,idx_alex_t03_col1这个索引还有存在的意义吗?

要让idx_alex_t03_col1索引有存在的意义,就需要依赖与col1列上的直方图信息收集。我们重新收集一下统计信息和直方图吧,示例如下所示:

SQL> exec dbms_stats.gather_table_stats('alex', 'alex_t03', method_opt=>'FOR ALL COLUMNS SIZE AUTO')
SQL> exec dbms_stats.gather_index_stats('alex', 'pk_alex_t03')
SQL> exec dbms_stats.gather_index_stats('alex', 'idx_alex_t03_col1')

再来对比一下col1=1和col1=5查询的执行计划看看,神奇地发现了两种截然不同的情况,而且都是最优的。

SQL> select * from alex_t03 where col1=1;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |  9873 |   115K|    55   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T03          |  9873 |   115K|    55   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T03_COL1 |  9902 |       |    30   (0)|
--------------------------------------------------------------------------------------

SQL> select * from alex_t03 where col1=5;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 17406 |   203K|    85   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T03 | 17406 |   203K|    85   (3)|
-------------------------------------------------------------------

说到这里,有人可能会提出质疑:“Oracle 11g提供的自适应游标共享(ACS)不是就可以根据不同的绑定变量生成不同的执行计划吗?”这真的是一个不错的主意,但也仅仅是不错而已。

我们不要忘了使用绑定变量的时候,Oracle有一个绑定变量窥探的机制,SQL语句第一次执行决定了以后同样的SQL语句的执行计划。如果在缺失直方图信息的情况下,其结果和不绑定变量的情况是一样的。如果很幸运的拥有直方图信息呢?我们第一次执行的col1=:VID绑定变量:VID:=1,则会走索引扫描,第二次:VID:=5也将继续索引扫描。反过来的话,先执行:VID:=5,再执行:VID:=1,则都会走全表扫描。这对我们来说是没有什么帮助的。即使Oracle 11g通过自适应游标共享进行了优化,但对数据分布倾斜也将是力不从心的。

细心的读者可能已经注意到了,上面我们说的两个例子,是很极端化的例子,一个过分均匀,一个过分倾斜,在实际应用中,我们还是需要中庸一点的。在索引设计和使用的时候,需要关注以下几点:

q  需要像优化器一样去思考,尽可能地参考理想选择度的原则。

q  尽可能的保证索引列数据分布均匀,否则不要建索引。

q  如果不得不对数据分布严重倾斜的列建索引,请收集直方图信息,同时关注绑定变量窥探的影响。

Trackback

no comment untill now

Add your comment now

切换到手机版