ITPub博客

首页 > 数据库 > Oracle > 利用B*TREE的特性,为列值分布极端不平衡的表创建特殊索引来提高效率

利用B*TREE的特性,为列值分布极端不平衡的表创建特殊索引来提高效率

原创 Oracle 作者:zhang41082 时间:2019-06-27 19:57:04 0 删除 编辑
问题提出:
一个订单处理系统,需要频繁的取出最早提交但是还未处理的订单来进行处理,随着时间的累加,表中数据越来越多,性能也越来越低。[@more@]

首先来看看表结构,简化后如下:
ID NUMBER(10) Y
COMMITDATE DATE Y
STATUS NUMBER(1) Y
其中ID是主键,唯一的标明了一个订单,COMMITDATE表示订单的提交时间,STATUS表示订单的处理状态,其中0表示未处理,1表示已处理。当然此表还有很多列来描述一笔订单,这里只是简化的结构。此表当初建立了tab_order(commitdate desc,status)的索引。那么查询最早提交并且未处理的sql的实现如下:
SELECT *
FROM (SELECT A.*, ROWNUM RW
FROM (SELECT *
FROM TAB_ORDER
WHERE STATUS = 0
ORDER BY COMMITDATE DESC) A)
WHERE RW = 1;

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 460 | 22080 | 49 (0)| 00:00:01 |
|* 1 | VIEW | | 460 | 22080 | 49 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | VIEW | | 460 | 16100 | 49 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TAB_ORDER | 460 | 16100 | 49 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | IDX_ORDER | 435 | | 49 (3)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
325 consistent gets
0 physical reads
0 redo size
716 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到,首先是对索引进行全扫描,然后得到表中的数据,再进行过滤,得到时间最早的一行。因为前面建立的是基于COMMITDATE的倒序索引,所以把排序的过程省略了。但是当数据量很大的时候,全扫描这个索引的cost也是很大的,而且业务规定不能限定COMMITDATE时间,那么因为可能由于某种原因导致的很早的订单一直没有得到处理。那么这样的环境肯定会导致数据越来越多,速度越来越慢。
改进方案:
考虑到系统中99%以上的订单都是已经处理掉的,而未处理的订单永远是少数,因此可以利用B*TREE中不会包含NULL值的特性,只为status为0的建立索引,这样索引的大小就永远和status为0的记录数相关,只要未处理的订单不会很多,这个索引就永远会很小,那么效率就不会随着数据的增长而下降。建立索引如下:
create index idx_order on tab_order(decode(status,0,0,null));
改写sql如下以便让执行计划走索引:
SELECT *
FROM (SELECT A.*, ROWNUM RW
FROM (SELECT *
FROM TAB_ORDER
WHERE DECODE(STATUS, 0, 0, NULL) = 0
ORDER BY COMMITDATE DESC) A)
WHERE RW = 1;

执行计划如下:
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 460 | 22080 | 2 (50)| 00:00:01 |
|* 1 | VIEW | | 460 | 22080 | 2 (50)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | VIEW | | 460 | 16100 | 2 (50)| 00:00:01 |
| 4 | SORT ORDER BY | | 460 | 16100 | 2 (50)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TAB_ORDER | 460 | 16100 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_ORDER | 435 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
716 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
经过对比可以发现,consistent gets已经大大的下降,程序效率得到提升,最关键的问题是,程序的效率不会随着数据量的增加而下降。

总结:当表中某些列的数据分布极端不平衡,而符合查询条件的返回结果又相当少的时候,可以通过DECODE函数转换和利用B*TREE不存储NULL值的特性,使得需要使用的索引很小,而且索引大小不随数据量的变大而变大,从而提高了程序效率。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25016/viewspace-927527/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-11

  • 博文量
    105
  • 访问量
    82270