原文一共有三篇(参考文末链接),概括了一下作者的主要观点整理成一篇,有删改。
一、 分区的优势
Oracle最早的分区版本,就已经定义了分区的原则:段segment的分割。传统的分区实现了两个功能,一是人为的在数据对象定义层面,将其划分为多个逻辑存储部分,第二是确定了数据记录归属原则,什么样的数据,放在哪个分区中。
传统的认识中,一个数据表或者索引作为独立的段对象,占据磁盘存储空间并且单独进行计量。而分区技术的出现,改变了这个情况。分区表被分割在多个数据逻辑segment中,如果条件允许,这些Segment是可以分布在多个表空间、以至到多个物理存储设备上的。分区提供了在定义阶段确定的一种数据存储规划策略。
Oracle分区Partition技术的原始出发点是应对海量数据表,这也就是Partition至今归属在Oracle DW(Data Warehouse)产品技术序列的原因。从根本上看,Oracle Partition技术带给我们的是两方面的好处:性能和管理。
性能提升,可能是大多数开发架构师和数据库设计人员选择Partition的初衷。好像一张数据表大了之后,我们只要分区了,就可以多少倍的提升性能。但是,很多时候事与愿违。在Oracle的世界中,同样没有“Silver Bullet”。一项技术的引入,必然有前提和适应范围。如果使用正确,分区配合适当的索引方案的确可以提高性能。
管理优势其实并不算Oracle Partition的初衷。但是随着版本的升级,Oracle对于分区管理手段支持提供了更多的功能特性。管理方面的优势已经在很多方面超越性能,成为我们选择分区技术的首要因素。管理优势具体来说包括:管理便捷性(Ease of Administration)、数据删除(Data Purge)、数据归档(Data Archive)、数据生命周期管理(Data Lifecycle Management)和高效备份(Efficiency Backup)几个层面。
二、 分区与性能提升
分区对于性能的提升,主要体现在分散IO和分区裁剪(partition pruning)两个方面。
1. 分散IO
一般来说,存储设备的上限TPS是硬件参数,没有过高的超越空间存在。解决的方法之一就是并行,将一个SQL的IO过程分散在多个磁盘设备上同时进行。如果总线或者网络条件允许的话,并行是可以超过单个IO盘的吞吐量的。
如果将一个数据表拆分为多个段segment结构,进而放在不同的表空间,最后放在不同的磁盘上。针对数据表不同分区的访问就可以实现IO分散的效果,总体上就可以实现性能提升。
但是,随着技术的发展,这样的优势已经不存在,或者说已经基本不需要了。硬件层面,RAID技术的不断发展,特别是条带化(Stripe),已经实现了数据分散在多个存储设备上,获取硬件资源提升。软件层面上,OS提供的Logical Volume、Oracle ASM都是将数据“打散”的技术。
2. 分区裁剪
分区裁剪的基本思想在于:原先需要访问全部表数据才能确定的结果,借用分区规则带来的规律性,可以仅访问需要的分区,节省一部分IO。
分区裁剪案例参考:从10046看Oracle分区裁剪_Hehuyi_In的博客-CSDN博客_如何验证oralce 分区裁剪
在分区表情况下,要使用分区裁剪,就只能顺应分区键约束。如果应用的SQL语句是和分区键无关的,那么SQL成本通常是更高的。
我们说:数据是由业务活性的。无论是业务分区、还是时间,很多这种活性都是我们选择分区键,加入SQL语句的依据。但是困难在于两个方面:一个是分区键的选择,设计人员是否可以预见到应用中SQL语句必然加入的条件?第二个是如何保证所有的开发都自觉将分区条件加入到SQL语句,即使很多时候不是很需要?
另一方面,使用分区裁剪,大部分情况下需要Local Index的配合。我们在实际中经常遇到这样的现象,分区之后的数据表SQL:要不就出现了分区裁剪,在单个分区中进行FTS(全表扫描),要不就只走了创建的global索引,浪费了分区“地利”。在很多时候,我们的确是需要借用Local Index来同时发挥分区+索引的优势。分区表对应的通常是海量表,生产环境上一个分区都是几个G左右,一个分区的全表扫描也是很可怕的。所以要进行多层次、多角度的优化,才能综合形成结果。
Partition的提出,最初就是为了性能。但是应该说随着硬件技术的发展,IO方面已经有了很好的提升。而分区裁剪在侧重一方面性能提升的情况下,是可能会限制其他访问方式的SQL性能的。相当于无形之中,数据表的使用被人为加入很多限制。而且,分区裁剪在很多时候要求开发设计人员具有一定的基础知识。相对于性能方面的优势,管理方面的优势是近年来更加受到侧重选择的方面。
三、 分区与数据管理
1. 仅操作分区对象
借助分区,可以将大对象划分为一系列小对象进行单独处理。也就是将一个大操作转变为一系列的小规模操作进行,从而减少对其他正在运行作业的影响。
可以单独move分区至不同表空间:
SQL> alter table t_part move partition p1 tablespace users;
Table altered
SQL> select partition_name, tablespace_name from dba_segments where owner='SYS' and segment_name='T_PART';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 USERS
P2 SYSTEM
P3 SYSTEM
针对分区索引,如果出现失效的情况,也可以进行部分rebuild。
SQL> select partition_name, status from dba_ind_partitions where index_owner='SYS' and index_name='IDX_T_PART_IDP';
PARTITION_NAME STATUS
------------------------------ --------
P1 UNUSABLE
P2 USABLE
P3 USABLE
SQL> alter index idx_t_part_idp rebuild partition p1;
Index altered
数据删除
alter table t_part drop partition p3;
Drop Partition是一个DDL过程,这个过程中,数据并没有被删除,而是数据表的分区定义发生了变化,让Oracle“不再承认”分区的存在。这种方法删除数据和Truncate Table有相似之处。优点是速度快,适合大数据分区表的删除动作。
对于drop partition操作,Global Index和Local Index的行为是有差异的。如果是Local Index,其他分区的变化并不影响本分区的索引作用。所以,Local Index还是valid的。但是,Global Index由于结构上的特殊性,需要进行额外的rebuild操作。当然,可以在drop partition的时候,连带加入update global indexes字句到drop partition命令中,实现自动的global索引重构。
alter table t_part drop partition p2 update global indexes;
2. 数据归档
实际生产环境中,直接删除的情况是比较少的,大部分情况是需要保留一个备份数据。只是将这个备份数据Offline出系统范围。使用分区表,可以快速的进行归档动作,卸载数据。下面是一个例子:
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
9 select * from dba_objects;
Table created
我们希望归档P2数据,可以先创建一个空数据表作为载体。
SQL> create table t_arch_p2 as select * from t_part where 1=0;
Table created
交换分区
SQL> alter table t_part exchange partition p2 with table t_arch_p2;
Table altered
SQL> select count(*) from t_arch_p2;
COUNT(*)
----------
27703
Exchange partition操作是一种非常强大的操作。语句基于操作定义层面的变化,将数据分区和数据表定义转换,操作速度是非常有优势的。将待归档数据拆分到t_arch_p2独立数据表之后,就可以将其导出或者备份到其他设备上。
3. 数据转移
海量数据一个很常见的模式就是ETL动作。数据分析的基础就是一系列的group汇总,从SQL角度看,group by是一个很消耗资源的动作。
比如,基础交易数据t_part,希望将其根据owner汇总为t_part_summary。最直接、最传统的做法就是简单的进行group by之后insert。
SQL> create table t_part_sum as select owner, sum(object_id) sum_value from t_part where 1=0 group by owner;
Table created
SQL> insert into t_part_sum select owner, sum(object_id) from t_part group by owner;
32 rows inserted
这种方法潜在两个问题:
- insert 会瞬间产生大量的undo和redo数据,在生产环境下可能会引起一些性能问题。
- 第二个问题在于事务的规模。我们一次性的生成数据表全部数据,底层业务数据在group by的全过程中是要一次性读入并且汇总计算。这个操作时间是很难控制的,同时group by动作的调整和PGA调优、Temp空间调优紧密相关。我们经常遇到的一个场景,就是长时间的group by动作,最后以Temp空间耗尽告终。
解决的方法在于数据操作的平缓化。将一个海量的、不可知总量的操作转化为一系列可控的操作系列,是我们处理海量数据的最常规思路。
我们首先还是创建汇总表,但是这次的汇总表是一个分区表。
create table t_part_summary partition by list (owner)
(partition p0 values ('SYS'),
partition p1 values ('PUBLIC'),
partition p2 values (default))
as select owner, object_type, sum(object_id) sum_value
from t_part
where 1=0
group by owner, object_type;
创建出一个临时表,按照分区的方式进行数据部分汇总动作。注意:这边一次性的操作只是在一个分区里面进行,带来的负载相对小。
create table t_part_sum_temp as select owner, object_type, sum(object_id) sum_value from t_part where owner='SYS' group by owner, object_type;
最后,通过exchange方式进行替换。
alter table t_part_summary exchange partition p0 with table t_part_sum_temp including indexes;
将insert+group by转化为一系列的group by和exchange partition,是可以适应海量数据维护工作的。
4. 数据生命周期管理
这种管理思路的基础在于一个假设:数据是有生命周期的,有明显的活性,即有明显的冷热数据之分。数据性能中,很大一个因素在于IO。IO直接是从成本决定的。如果我们有快的IO,还有慢的IO,就可以利用分区技术将不同活性的数据分散在不同的存储设备上,从而获得最优均衡。
详细参考 Oracle ILM相关(Information lifecycle management)_Hehuyi_In的博客-CSDN博客
5. 数据备份
Partition能够优化备份工作,如果一个分区数据没有活性,不会发生变化了,一种好的做法是将其单独放在一个表空间里面,设置表空间为只读read only。
alter tablespace users read only;
Tablespace altered
一旦表空间被设置为read only,Oracle进行备份识别的时候,只会备份这个表空间一次。在第二次进行备份的时候,这个表空间就不会进行备份了。
四、 结论
Partition是Oracle的一项重要技术,也是常见的优化手段。使用分区,首先要明确使用的目的。在实践中,分区可能会成为双刃剑。分区策略的选择,性能和管理,是需要设计人员明确的重要取舍。如果更关注性能,而且需求层面有明显的数据访问规律性,那么可以使用分区。如果更关注管理,数据活跃性是否明显也是我们考量的方面。
参考
聊聊分区Partition——我们为什么要分区(上)_ITPUB博客