2.3 索引设计优化

现在,我们知道了B树索引的结构特点,也了解到其对查询和排序优化的意义,但是这并不代表我们就能建好用好索引了。在实际工作中,是不是还是会遇到走了索引反而查询变慢的情况呢?虽然说不是所有的情况下索引扫描都是优于全表扫描的,但是对于一套设计成熟的系统来说,索引扫描往往是值得坚持的,应该定期进行全库SQL语句执行计划的审查,抓出全表扫描的SQL进行优化。

说一千道一万,我们创建索引就是为了使用索引,尽可能地使查询操作能够走索引。但是,很遗憾,不是我们说走索引就能走索引,还是需要取决于CBO优化器的成本计算和比较情况。这其中会影响CBO优化器计算结果的因素有很多,比如:统计信息过旧、缺少直方图信息、数据分布影响索引选择度、索引聚簇因子偏差,等等。我们要用好索引,就必须要充分地了解这些特性和影响因素。

本节仍然沿袭概念结合实例的方式,首先,给读者介绍索引选择度、索引聚簇因子的概念;再辅以数据分布和数据存储的变化对索引使用的影响;最后,再分析实际工作中的一些常见的索引被无视的情况。

 

2.3.1 索引选择度

在这里,我们先抛出一个问题:什么时候需要用索引呢?有些人会说选择度20%以下,有些人会说10%以下,这样的回答都不尽然,甚至可以说是不负责的。我们还是需要根据具体情况来具体分析的。

众所周之,需要索引发挥出较高的使用效率,则在通过索引进行查询的时候,其选择度需要控制在一个较小的范围内。但是,也需要考虑数据的实际分布情况。这里我们引入两个概念:

q  索引理想选择度 = 1 / 索引列的DISTINCT数;

q  索引实际选择度 = 返回结果集行数 / 全表行数。

通常情况下,我们所说的索引选择度就是上面所说的实际选择度。那么理想选择度是怎么来的呢?

我们知道当一个表的NUM_DISTINCT值越接近NUM_ROWS值,则优化器越倾向于走索引扫描。那么最优情况是NUM_DISTINCT=NUM_ROWS(典型代表为唯一索引,此类索引为理想状态索引,扫描效率最高),最差情况则是NUM_DISTINCT=1(不考虑空表和Null值)。引进一个比例NUM_ROWS/NUM_DISTINCT来表示其关联性,该比例的值越小则越优。通过一般情况与最差情况的对比,可以得到一般情况下的选择度,这个选择度就是一般情况的理想选择度。

理想选择度 = (NUM_ROWS / NUM_DISTINCT) / (NUM_ROWS / 1) = 1 / NUM_DISTINCT

可以说理想选择度反映了优化器选择的倾向性,换而言之,如果希望优化器尽可能地选择索引扫描,则索引设计上尽可能地参考理想选择度的原则,它给索引设计提供一定的指导意义。

至于NUM_ROWS和NUM_DISTINCT的值可以通过扫描表获取,也可以通过如下方式查询数据字典获取(必须收集统计信息):

SQL> select num_rows from dba_tables;
SQL> select num_distinct from dba_tab_cols;

细心的读者读到这里,应该已经察觉到了,我们这里说的理想选择度其实就是执行计划中“集势”(Cardinality)的一个重要因子。集势因子是CBO优化器COST成本计算的重要标准,可以说它反映了优化器的计算方式,也是决定是否走索引的关键因素。

索引列的集势因子如表2-3所列:

表2-3 索引列集势因子

查询条件

索引列集势因子

=

1/NUM_DISTINCT

<、>、<=、>=

1/NUM_DISTINCT+1/NUM_ROWS

IN

NUM_VARIABLE*(1/NUM_DISTINCT)

<>

1-1/NUM_DISTINCT

NOT IN

(1-1/NUM_DISTINCT)^ NUM_VARIABLE

从集势因子来看,不难看出“<>”和“NOT IN”的情况比较难以高效使用索引。而对“=”,“<、>、<=、>=”和“IN”的情况,基本上都可以由1/NUM_DISTINCT决定,也可以说它们最小公约数单元就是等值查询。这个因子,也就是我们所说的理想选择度基本上可以反映索引列各种高效查询的优化器成本计算情况。

Trackback

no comment untill now

Add your comment now

切换到手机版