2.2.4 max()与min()

前面的章节中,我们提到COUNT(*)的聚合查询可以利用索引扫描而规避回表取数的动作,而且索引扫描过程也可以选择INDEX FAST FULL SCAN的高效方式。现在,我们再来说说另一种聚合查询min()与max()。这个和索引有关系吗,能用到索引进行优化吗?也许有读者会说:“是的,但索引列必须要有NOT NULL的约束,或者在查询过程中过滤掉NULL。”这个回答是值得鼓励和肯定的。但是,另一个问题出来了,它能用到哪种索引扫描方式呢?像COUNT(*)一样,INDEX FAST FULL SCAN吗?min()与max()是依赖于顺序的,能接受不排序的输出吗?

下面的例子告诉我们,在主键索引列上的min()与max(),是可以用到索引快速全扫描的,而且COST下降比较明显,达到了优化的目的。

SQL> select /*+full(alex_t02)*/ min(id), max(id)
2  from alex_t02;
--------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     5 |   113   (3)|
|   1 |  SORT AGGREGATE    |          |     1 |     5 |            |
|   2 |   TABLE ACCESS FULL| ALEX_T02 |   100K|   488K|   113   (3)|
--------------------------------------------------------------------
 
SQL> select /*+index_fs(alex_t02 pk_alex_t02)*/ min(id), max(id)
2  from alex_t02;
--------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     5 |    45   (5)|
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |
|   2 |   INDEX FAST FULL SCAN| PK_ALEX_T02 |   100K|   488K|    45   (5)|
--------------------------------------------------------------------------
 

我们说主键索引的键值具有唯一性,而且顺序排列,这固然能用到INDEX FAST FULL SCAN,而如果是有重复性的非唯一索引呢?就只能走INDEX FULL SCAN了,甚至可能会因为COST太高,而选择走全表扫描。

不知道有没有读者还沉浸在我们之前的想象中呢?如果有,相信你已经想象到了更好的优化方法。我们都知道索引的树形结构,各层节点块都是有序排列的,默认升序的情况下,从左到右即是从小到大,min()就是最左叶节点块,max()则是最右叶节点块。想象完成,通过实例来验证一下吧。

从下面的例子可以看到,执行计划变成了索引全扫描的方式,但是多了(MIN/MAX)的字样。这是CBO优化器对索引全扫描的一个优化,其间蕴藏着一个STOPKEY的机制,简单地来说,就是扫描到需要的东西就退出,不继续扫描了。执行计划中INDEX FULL SCAN (MIN/MAX)的COST开销只有2,较之INDEX FAST FULL SCAN更优。

SQL> select min(id) from alex_t02;
-------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |     5 |     2   (0)|
|   1 |  SORT AGGREGATE            |             |     1 |     5 |            |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_ALEX_T02 |   100K|   488K|     2   (0)|
-------------------------------------------------------------------------------
 

细心的读者应该注意到了,上面的SQL语句只做了min(),并没有同时取min()与max(),同时取的话,是什么效果呢?下面的例子告诉我们,仍然会走INDEX FAST FULL SCAN。原因很简单,索引扫描只能是单向的扫描,要么从左到右,要么从右到左,现在是需要同时返回min()和max(),如果STOPKEY在取到min()退出,就取不到max(),反之亦然。所以,INDEX FULL SCAN (MIN/MAX)就未被使用。

SQL> select min(id), max(id) from alex_t02;
--------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |     5 |    45   (5)|
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |
|   2 |   INDEX FAST FULL SCAN| PK_ALEX_T02 |   100K|   488K|    45   (5)|
--------------------------------------------------------------------------
 

但事实上,从COST计算来看,即使单独取一次min(),再单独取一次max(),取开销之和都较索引快速全扫描要小。我们可以把SQL语句改写如下:

SQL> select (select min(id) from alex_t02) min_value,
2  (select max(id) from alex_t02) max_value from dual;
-------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |       |     2   (0)|
|   1 |  SORT AGGREGATE            |             |     1 |     5 |            |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_ALEX_T02 |   100K|   488K|     2   (0)|
|   3 |  SORT AGGREGATE            |             |     1 |     5 |            |
|   4 |   INDEX FULL SCAN (MIN/MAX)| PK_ALEX_T02 |   100K|   488K|     2   (0)|
|   5 |  FAST DUAL                 |             |     1 |       |     2   (0)|
-------------------------------------------------------------------------------
 

min()与max()通常是最常用的高频SQL写法之一,特别是在一些计费系统和报表系统的应用中,这样的写法比比皆是。希望读者们能充分利用INDEX FULL SCAN (MIN/MAX)这一蕴藏STOPKEY机制的特性来提高查询性能,甚至可以通过改写SQL,将原本用不到该特性的查询也能利用上,其间尽量保证该列为NOT NULL。

再多说一句关于STOPKEY特性的应用,在TOP-N的查询或者分页查询中,它的作用是不容小觑的。示例如下所示:

SQL> select * from (select id, name from alex_t02 order by id desc)
2  where rownum<=10;
----------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |    10 |   650 |     3   (0)|
|*  1 |  COUNT STOPKEY                |             |       |       |            |
|   2 |   VIEW                        |             |    10 |   650 |     3   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| ALEX_T02    |   100K|  1562K|     3   (0)|
|   4 |     INDEX FULL SCAN DESCENDING| PK_ALEX_T02 |    10 |       |     2   (0)|
----------------------------------------------------------------------------------
 

综上所述,索引的扫描和快速取数往往是大家建索引的目的,甚至有的时候会被认为是唯一的目的,而忽略掉了索引对排序的意义,因此丢失了原本应该更优的性能。通过本节的介绍,希望能对读者有所启示,能够更全面地了解和思考索引的设计和使用。

 

Trackback

no comment untill now

Add your comment now

切换到手机版