聊聊分区Partition——我们为什么要分区(上)

 一直想系统的聊聊分区。网络上Oracle技术中,讨论的最多的话题之一就是Partition。各种分区类型、分区组合和随之而来的各种优缺点,一直被大家讨论。但是,实际中,我们往往看到很多以偏概全、舍本逐末的分区使用现象。

各种分区类型和创建语句,是很多文章探讨的中心。一些开发设计人员,甚至是DBA也感觉分区成了大表的万灵药。笔者这个系列文章不想讨论各种语法和分区使用,而是集中在几个常见话题难点。从“思想问题”,到“细枝末节”,归纳一下对于分区Partition技术,我们应该了解什么。

本篇作为系列的开篇,首先看看为什么要分区。

1、“大表要分区”

 

几年前,还经常有朋友在网络或者技术讨论沙龙中跳出来,发表所谓的设计经验集,其中就包括“大表要分区”。是不是数据表一大,我们就要分区,就要用各种手段将其“大卸八块”呢?

笔者的答案是:不一定。

让我们一起先看看Oracle分区技术的出发点和发展轨迹。

Oracle最早的分区版本,就已经定义了分区的原则:段segment的分割。传统的认识中,一个数据表或者索引作为独立的段对象,是占据磁盘存储空间,并且单独进行计量。

而分区Partition技术的出现,改变了这个情况。一个数据表未必是一个段对象,而是多个段对象。当然,一个段也可能承载多个数据表。传统的分区实现了两个功能,一是人为的在数据对象定义层面,将其划分为多个逻辑存储部分,第二是确定了数据记录归属原则,什么样的数据,放在哪个分区中。

下面是一个最简单的范围分区数据表定义。

 

 

SQL> create table t

  2  partition by range (object_id)

  3  ( partition p1 values less than (10000),

  4    partition p2 values less than (maxvalue)

  5  ) as select * from dba_objects;

 

Table created

 

SQL> select partition_name, segment_type from dba_segments where owner='SCOTT' and segment_name='T';

 

PARTITION_NAME                 SEGMENT_TYPE

------------------------------ ------------------

P1                             TABLE PARTITION

P2                             TABLE PARTITION

 

 

在定义中,SQL语句确定了分区数据表被分割在多个数据逻辑段segment中,如果条件允许,这些Segment是可以分布在多个表空间、以致到多个物理存储设备上的。分区提供了在定义阶段确定的一种数据存储规划策略。

Oracle分区Partition技术的原始出发点是应对海量数据表,这也就是为什么Partition至今归属在Oracle DWData Warehouse)产品技术序列的原因。从根本上看,Oracle Partition技术带给我们的是两方面的好处:性能和管理。

性能提升,可能是大多数开发架构师和数据库设计人员选择Partition的原始初衷。好像一张数据表大了之后,我们只要分区了,就可以多少倍的提升性能。但是,很多时候事与愿违。在Oracle的世界中,同样没有“Silver Bullet”。一项技术的引入,必然有前提和适应范围。如果使用正确,分区配合适当的索引方案的确可以提高性能。

管理优势其实并不算Oracle Partition的初衷。但是随着版本的升级,Oracle对于分区管理手段支持提供了更多的功能特性。管理方面的优势已经在很多方面超越性能,成为我们选择分区技术的首要因素。

管理优势是一个非常大的范围,主要是运维范畴和领域的问题。具体来说包括:管理便捷性(Ease of Administration)、数据删除(Data Purge)、数据归档(Data Archive)、数据全生命周期管理(Data Lifecycle Management)和高效备份(Efficiency Backup)几个层面。

下面笔者将从几个方面分别介绍分区的这些特性优势。

 

2、环境介绍

 

笔者选择Oracle 11g来进行试验,中间过程使用之前创建的海量数据表T

 

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE     11.2.0.1.0     Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

Executed in 0.031 seconds

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     72761

 

Executed in 0.016 seconds

 

 

3Partition性能优化

 

Partition最早的推出,就是为了性能上的优化。传统的Segment概念下,一个数据表对应一个数据段。而Partition的推出,将一个数据对象(数据表或者索引)拆分为多个段对象,进而可以放在不同的表空间里。

Partition对于性能的提升,主要体现在分散IO和分区裁剪(partition pruning)两个方面。

先聊聊分散IO

我们在过去一些Oracle优化建议中,经常看到一条“秘籍”:将数据表段和索引段分布在不同的表空间里。为什么会有这个概念呢?

表空间是由不同的数据文件构成。一个数据文件只能对应一个表空间,一个表空间可以有多个数据文件进行对应。在同一个表空间里,我们不能控制数据究竟是放在哪个文件上。

将数据段和索引段分布在不同的表空间里,才能保证两者在不同的数据文件里面。这个不是最关键的问题。最关键的问题在于:只有在不同的数据文件里面,我们才可能将其分布在不同的磁盘上。

我们的磁盘是IO的重要设备,性能领域中IO是一直需要关注的方面,也是非常容易形成瓶颈的方面。磁盘存储设备的IO体现在TPS上,这个往往是由于设备的物理上限决定的。通常我们的Oracle调优手段,比如索引、IOT,目的都是为了减少IO量,也就是减少SQL语句对IO的需求量。

但是需求通常是无限的。当我们的应用进行软优化之后,的确需要如并行或者高IO读取的时候,IO就成为应用的底线。一般来说,存储设备的上限TPS是硬件参数,没有过高的超越空间存在。解决的方法之一就是并行,将一个SQLIO过程分散在多个磁盘设备上进行。多个磁盘设备同时工作的时候,是有并行的效果的。如果总线或者网络条件允许的话,并行合力是可以体现出超过单个IO盘的吞吐量的。

如果我们规划过程,将一个数据表拆分为多个段segment结构,进而放在不同的表空间,最后放在不同的磁盘上。针对数据表不同分区的访问就可以实现IO“分散”的效果。总体合力上就可以实现性能提升。

但是,随着技术的发展,这样的优势已经不存在,或者说已经不需要了。硬件层面,RAID技术的不断发展,特别是条带化(Stripe),已经实现了数据分散在多个存储设备上,获取硬件资源提升。软件层面上,OS提供的Logical VolumeOracle ASM都是将数据“打散”的技术。

再说说分区裁剪(Partition Pruning)。

Oracle的分区中有一个重要方面是分区规则的确定,一旦确定分区规则,数据行就按照规则“自动”分配到各个分区段Segment中(11gSystem Partition例外)。分区裁剪的基本思想在于:原有需要访问全部数据表的数据才能确定的结果,在分区的情况下,借用分区规则带来的规律性,我们可以节省一部分的IO消耗量。

下面是一个分区裁剪的例子。

 

 

SQL> create table t_part

  2  partition by list (owner)

  3  (

  4     partition p1 values ('SYS'),

  5     partition p2 values ('PUBLIC'),

  6     partition p3 values (default)

  7  )

  8  as select * from t where 1=0;

 

Table created

 

SQL> insert into t_part select * from t;

72761 rows inserted

 

SQL> commit;

Commit complete

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);

 

PL/SQL procedure successfully completed

 

 

我们对t_part采用owner分区策略,之后比较按照owner进行检索的性能差异。

 

 

SQL> explain plan for select * from t where owner='SCOTT';

 

Explained

 

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    13 |  1261 |   289   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |    13 |  1261 |   289   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OWNER"='SCOTT')

 

13 rows selected

 

 

SQL> explain plan for select * from t_part where owner='SCOTT';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2970683307

--------------------------------------------------------------------------------

| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |        |    25 |  2450 |    53   (0)| 00:00:01 |

|   1 |  PARTITION LIST SINGLE|        |    25 |  2450 |    53   (0)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL   | T_PART |    25 |  2450 |    53   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OWNER"='SCOTT')

 

14 rows selected

 

 

从上面的执行计划,我们的确看到了性能上的提升。当我们使用适合分区工作的where条件时,Partition是可以有不错的性能提升的。

当我们没有分区的时候,Oracle检索owner=’SCOTT’的时候,因为是堆表Heap Table,存储是随机的,Server Process需要访问每一个数据表块,才能确认结果集合。在分区的时候,由于分区键和owner有关,Oracle可以明确的判定说,分区p1p2里面肯定没有owner=’SCOTT’的记录,所以就不用检查p1p2了,从而能够提高性能,降低成本。

但是,应该看到分区裁剪的两个问题。首先,如果分区表情况下,要使用分区裁剪,就只能(注意是只能)顺应分区键约束。如果应用的SQL语句是和分区键无关的,那么SQL成本通常是更高的。

 

 

SQL> explain plan for select * from t;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 72761 |  6892K|   289   (1)| 00:00:04 |

|   1 |  TABLE ACCESS FULL| T    | 72761 |  6892K|   289   (1)| 00:00:04 |

--------------------------------------------------------------------------

 

8 rows selected

 

SQL> explain plan for select * from t_part;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2002420342

--------------------------------------------------------------------------------

| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Ps

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |        | 72761 |  6892K|   267   (1)| 00:00:04 |

|   1 |  PARTITION LIST ALL|        | 72761 |  6892K|   267   (1)| 00:00:04 |

|   2 |   TABLE ACCESS FULL| T_PART | 72761 |  6892K|   267   (1)| 00:00:04 |

--------------------------------------------------------------------------------

 

9 rows selected

 

 

上面的SQL语句中,如果我们的条件中没有owner分区键,就会访问所有的分区,也就是Partition List ALL操作。相同数据量情况下,检索所有分区,进行所有分区的访问成本要高于不分区的数据表。这就形成了我们使用分区表,借助分区裁剪的一个现实条件:SQL语句中,要出现分区键。

我们说:数据是由业务活性的。无论是业务分区、还是时间,很多这种活性都是我们选择分区键,添加入SQL语句的依据。但是困难在于两个方面:一个是分区键的选择,设计人员是否可以“预见”到应用系统中SQL语句必然加入的条件。第二个困难是如何保证所有的开发人员都“自觉”将分区条件加入到SQL语句,即使很多时候不是很需要。

另一方面,使用分区裁剪,大部分情况下需要Local Index的配合。我们在实际中经常遇到这样的现象,分区之后的数据表SQL:要不就出现了分区裁剪,在单个分区中进行FTS(全表扫描),要不就走了创建的索引路径,浪费了分区“地利”。

 

 

SQL> create index idx_t_part_id on t_part(object_id);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select * from t_part where owner='SCOTT' and object_id=20000;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2004327352

--------------------------------------------------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |               |     1 |    98 |

|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_PART        |     1 |    98 |

|*  2 |   INDEX RANGE SCAN                 | IDX_T_PART_ID |     5 |       |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OWNER"='SCOTT')

   2 - access("OBJECT_ID"=20000)

 

15 rows selected

 

 

这种时候,设计人员就需要额外的数据库知识,就是本地索引。只有借用本地索引,才有可能两者兼得。

 

 

SQL> drop index idx_t_part_id;

Index dropped

 

SQL> create index idx_t_part_idp on t_part(object_id) local;

Index created

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select * from t_part where owner='SCOTT' and object_id=20000;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1293386573

--------------------------------------------------------------------------------

| Id  | Operation                          | Name           | Rows  | Bytes | Co

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                |     1 |    98 |

|   1 |  PARTITION LIST SINGLE             |                |     1 |    98 |

|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PART         |     1 |    98 |

|*  3 |    INDEX RANGE SCAN                | IDX_T_PART_IDP |     1 |       |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OWNER"='SCOTT')

   3 - access("OBJECT_ID"=20000)

 

16 rows selected

 

 

在很多时候,我们的确是需要借用Local Index来同时发挥分区+索引的优势。可能很多朋友会说,在实际中我们需要借助两个的优势吗?笔者的回答是:非常有必要!

分区表对应的通常是海量表,不管创建的初衷是什么。每个分区的体积通常是很大的,生产环境上一个分区都是几个G左右。即使分区了,检索一个分区的FTS也是很可怕的。所以要在优化层面上进行多层次、多角度的优化,才能综合形成结果。所以,真正有效的分区表索引,通常是本地索引Local Index

在分区表索引问题上,很多时候需要讨论PrefixNon-Prefix,笔者会在本系列的其他部分进行更详细的介绍。

Partition的提出,最初就是为了性能。但是应该说随着硬件技术的发展,IO方面已经有了很好的提升。而且分区裁剪在侧重一方面性能提升的情况下,是会起身其他访问方式的SQL性能的。相当于无形之中,数据表的使用被人为加入很多限制。而且,分区裁剪在很多时候要求开发设计人员具有一定的基础知识能力。

相对于性能方面的优势,管理方面的优势是近年来更加受到侧重选择的方面。

 


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

转载于:http://blog.itpub.net/17203031/viewspace-1061774/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值