海量数据库及分区1——《12年资深DBA教你Oracle开发与优化——性能优化部分》

目录：

Oracle数据完整性和锁机制 

索引及优化之表分析 

表分析、约束及表间关系 

Oracle体系结构1

Oracle体系结构2 

海量数据库及分区1 

海量数据库及分区2 

海量数据库及分区3 

海量数据库及分区4 

高级SQL优化(一)  

高级SQL优化(二)  

高级SQL优化(三) 常用优化工具 

PPT和源码下载：    http://sishuok.com/forum/posts/list/6365.html

配套视频课程

    Oracle性能优化 http://sishuok.com/product/601 

    海量数据库和高级SQL优化 http://sishuok.com/product/602

 

 

原规划本课的内容为表分区，即单级分区表的简要知识，规划为一课。由于近年来行业内海量数据处理人才紧缺，现根据读者反馈将内容调整为四课，以详细讨论海量数据库的全部主要知识，并辅以大量实践练习，以期有志于研究学习VLDB的读者通过加量的课程学习能达到建立起VLDB基础理论体系，迅速适应相应的海量数据库设计和开发工作的效果。

 

VLDB概述

引子

很多现代企业如电子商务企业需要频繁的在一个包含数百个GB或者数个TB数据的数据库上运行关键任务。这种数据库称为超大型数据库（VLDB，Very Large Databases，又称海量数据库），其中的数据称为海量数据。

实践中，VLDB一般包含两个关键要素：一是用户达到百万级甚至千万级，数据库的容量一般超过1TB(1TB=1024GB)；二是系统必须提供实时响应功能，不能停机、具有高度的可靠性和可扩展性。例如亚马逊、淘宝、苏宁易购等系统的数据库。

对于VLDB的支持和维护的挑战工作催生了海量数据解决方案。VLDB的主要主题中，最关键的方法一般是使用分区、压缩、并行执行等技术。

分区简介

分区是将一个超大表或者索引分解成若干个更小、更易于管理的小的部分，这些小的部分称为分区。对于表而言称为分区表，对于索引而言称为分区索引。原来对超大表或超大索引的SQL查询和DML无需进行任何修改即可访问这些分区表和分区索引。定义了分区后， DDL语句就只访问和操纵单个分区，而不 是整个表或索引，因而分区带来了高可维护性的好处。

分区的一个基本要求是表或索引的每个分区具有 相同逻辑属性，如列名、约束等；但不同的分区可以具有不同的物理属性，如压缩的启用/禁用，物理存储设置、表空间等。对于许多不同类型的应用，尤其是管理大量数据的应用程序而言分区是非常有用的，如对于OLTP分区可提高其可管理性和可用性、性能，而对于数据仓库分区则可提高其性能和可管理性。

分区的优点如下：

 

• 显著缩短操作处理时间：这些操作包括数据装载、索引的创建和重建、备份与恢复。能显著缩短的原因是这些操作仅限于分区级(在个体的分区上)，而非表级（在整个表上)

• 提高查询响应性能：通常的查询只需要访问分区的一个子集即可完成，从而避免了访问整个表。这种技术称为分区裁剪，其对性能的提升可以达到量级单位

• 显著缩短因维护操作导致的计划宕机时间：分区是独立于分区维护操作的，因此允许在表的不同分区或索引不同的分区上执行并发维护操作。如可以对分区执行并发的SELECT和DML语句等维护操作，其二者不会相互影响

VLDB与分区

    海量数据库没有最小的绝对大小，VLDB虽然类似于一个小型数据库，但管理一个VLDB依然存在很多挑战，这些挑战与执行操作的数据库的 绝对规模及其 成本效用是有关系的。数据库大小的稳步增长与以下几个趋势因素有关：

• 系统开发的独立性和综合合并应用需求的驱动：每个系统独立开发，但跨部门的综合应用需要数据库和应用进行整合合并，而整合带来的一个是基础数据和存储结构的不统一，二就是导致数据库大小的持续增长

• 法律法规的规定：法律法规要求存储最小时间粒度的数据并保存一个较长时间，如日志信息、交易信息等

• 客户公司发展因素：如公司的增长发展、收购等导致用户规模和业务规模的增长，这些均能带来数据规模和并发规模的增长

    分区策略是管理超大容量数据库的一个核心特性。分区地址的增长是VLDB的最基本挑战之一，Oracle使用“分而治之”的技术策略管理数据库中的表和索引，当这些表和索引增长时，该技术非常有效。分区的一个特性是当管理一个超大容量数据库时，无需投入更多的管理资源和硬件资源即可提供具有高度可伸缩性和统一的可靠数据库性能的数据库服务。

Oracle建议，哪怕小规模数据库（以MB为单位）也可以分区，分区同样能获得与以TB为单位的VLDB相同的性能和高可维护性。因此从这个角度触发，建议对每个大数据表均进行分区，亦即分区是数据库优化的一个重要手段。

分区基础知识概览

 

1.分区的基本知识

从 数据库管理员的角度

来看，一个分区的对象有多个块，可以集体管理或单独管理，这赋予管理员在管理分区对象相当大的灵活性。

从 应用程序的角度来看，一个分区表，非分区表完全相同，使用SQL查询和DML语句访问分区表时，无需作任何修改。

 

 

2.分区键

  分区表的行必须明确的存储在单一的分区，分区键可以由一个或多个列组成，用来决定行所存储的分区，Oracle使用分区键来自动确定Insert、Update、Delete操作所对应的分区。

  如课程“索引及优化之表分析”中“用户交易表”基于年度、月度和日期建立分区，则该三列构成分区键，用来决定DML操作对应的分区。

 

3.分区表

  表可以被分割为无数个分区，但如果表中包含LONG或LONG RAW类型的列是则不可分区，而CLOB和BLOB类型的列是支持分区的。

  为了减少磁盘和内存使用情况（特别是高速缓存），可以将表和分区的分区表进行压缩，表压缩 也可以大大节约存储空间、加快查询执行速度。 然而，有轻微的增加CPU的负担。压缩适合的情况 是数据仓库环境，即读多写少的情况。

建议考虑使用表分区的情形：

(1).表的大小超过2GB时，或超过2000万行记录时

  (2).表中包含历史数据，新数据会被写入新分区时

  (3).表的内容必须被分布在不同类型的存储设备时

 

建议考虑使用索引分区的情形：

(1).对表数据进行迁移并且希望避免重建索引时

  (2).对部分数据进行维护，而不希望维护期间使整个索引无效时

  (3).减少索引中具有单一递增值的列引起的索引偏移时

4.分区与LOB类型数据

LOB类型列中存储的非结构化数据（图片、文档等）也能分区。当进行分区时，LOB类型的列存储在子集的表空间，且列存储在分区所在的表空间中。此技术在存储有大对象数据且不经常更新，而其他数据会经常被更新时非常高效，原因是将大对象数据与主数据进行了分离的存储。如员工记录数据，照片是大对象。

 

分区的好处

1.提高性能

分区通过限定操作数据的数量、对并行执行的数据是分布式的，因此带来了成倍的性能提升，主要包括：

(1).分区修剪

   分区修剪是最简单也是最本质的性能提高手段，原因是限定了数据范围。常常可以将查询性能提高几个数量级。如某个订单表按月分区,该表中存有2年的数据，现在查询某个月度的订单数，则只需访问访问一个分区，而不是24个分区，因此性可能会提升20倍。

(2).智能化分区连接

  多表连接时，该技术能在下列两种情形下会得到应用

  1).A表与B表连接，A表的键是colS，B表的键也是colS，两个表的分区均是基于列colS，且连接条件是A.colS= A.colS

  2).参考完整性中的参考分区表与其父表连接

  此时智能化分区连接技术能将一个大的连接打破使用一个小的连接，从而节省时间。

该技术无论是在串行化的还是在并行化的执行中具有重要意义。

2.易管理性

  由于分区后的存储是单个单元的，也就是提供了“分而治之”的管理方式，因此可以对单个分区而不是整个表进行维护操作，如备份等。

3.可用性

  不同的分区是独立的，因此提供可高可用性。如某个分区损坏，但其他分区依然可以使用，而不是整个表不能被使用。

每个分区也可以存储在不同的表空间中，每个分区也可以设置不同的存储参数。

分区策略概述

Oracle分区提供了三种基本的数据分区方法作为基本的分区策略，控制如何将数据放入单独的分区：

l范围Range

l哈希Hash

l列表List

使用三种基本的分区方法，表可以被分区成单一分区列表或复合分区表，分别称为单级分区和组合分区。

       每个分区策略都有不同的优点和设计考虑。 因此，不同的策略适合不同特定的情况。

分区表

环境准备

1.先备份前面的30课的数据

2.只准备使用表ACCOUNT_TRADE中的数据

 

 

 

 

 

单级分区

使用范围、哈希、列表三种分区策略的任何一中，在表的一个或多个列上建立分区。
1.范围分区策略－策略说明

    依据分区键值的范围不同进行分区，当数据在范围内均衡分布时，性能最好，是最常见的分区策略，一般与日期相关。

该策略的每个分区均具有一个VALUES LESS THAN子句，用来指定该分区包含的范围数据的上限。任何分区键的值大于等于该值时被添加到下一个更高的分区。 所有分区，除了第一个，有一个隐含的下限为前一个分区的VALUES LESS THAN值。此外，包含一个MAXVALUE用来义为最高的分区。 MAXVALUE代表一个虚拟的无限大的值，包括NULL值。

1.范围分区策略—语法

    

 

 

1.范围分区策略—语法注意事项

   如果基于多个列创建分区，列之间用逗号分隔；值部分也是用逗号分隔，且该值必须与列的类型相同。值部分也可以使用函数，如TO_DATE等。还要注意LESS THAN相当于小于号（<），如：

partition by range(ND,YD ) (

  partition ACCOUNT_TRADE_SR_P201001 values less than(2010,'02'),

  partition ACCOUNT_TRADE_SR_P201002 values less than(2010,'03'),

  partition ACCOUNT_TRADE_SR_P201003 values less than(2010,'04'),

  partition ACCOUNT_TRADE_SR_P201004 values less than(2010,'05'),

  partition ACCOUNT_TRADE_SR_P201005 values less than(2010,'06'),

  partition ACCOUNT_TRADE_SR_P201006 values less than(2010,'07'),

  partition ACCOUNT_TRADE_SR_P201007 values less than(2010,'08'),

  partition ACCOUNT_TRADE_SR_P201008 values less than(2010,'09'),

  partition ACCOUNT_TRADE_SR_P201009 values less than(2010,'10'),

  partition ACCOUNT_TRADE_SR_P201010 values less than(2010,'11'),

  partition ACCOUNT_TRADE_SR_P201011 values less than(2010,'12'),

  partition ACCOUNT_TRADE_SR_P201012 values less than(2011,'01'),

  partition ACCOUNT_TRADE_SR_PMORE   values less than(MAXVALUE,MAXVALUE));

 

1.范围分区策略—示例体验

l 创建一个结构类似表ACCOUNT_TRADE的单级范围分区表ACCOUNT_TRADE_SR

l从表ACCOUNT_TRADE读取2010年的数据写入表ACCOUNT_TRADE_SR

l分析表ACCOUNT_TRADE_SR

l查看分区数据并将两个表的数据进行对比

 

参见附件脚本3

1.范围分区策略—示例体验

 

 

 

 

2.散列分区策略－策略说明

    依据Oracle的散列分区算法将数据映射到分区上，该算法不可人工改变。散列分区会尽量将数据均匀的分布在不同的分区上。当业务数据中没有历史数据时，选择散列分区是一个比较好的替代方法。这个分区方法能够保持I/O平衡，但是不可用于范围查询或不等式查询。

    注意：Oracle推荐Hash分区的分区数是2的幂，如2、4、8、16等。

 

2.散列分区策略－语法

 

2.散列分区策略－语法注意事项

    单独散列分区（individual_hash_partitions）及其限制使用子名按照名字指定单个分区，分区名可以匆略。此时能指定的唯一子句是表空间。

按照数量的散列分区（hash_partitions_by_quantity）可以指定分区的数目，Oracle此时指定分区的名字SYS_Pn。STORE IN子句指定分区所分布的表空间的名称。表空间的数目不一定和分区的数目相等。如果分区的数目比表空间的数目大，则分区按照名称循环使用表空间。如果在按数量分区时指了STORE IN和分区存储子句的表空间，则SOTRE IN指定了表创建分区的位置，TABLESPACE子句指定随后操作的缺省表空间。此时语句partitions后面只能跟数字，一般为2的幂，否则会引起ORA-14152错误。

2.散列分区策略—按照数量分区示例体验

表为ACCOUNT_TRADE_SHQ，方法同前面，分区语句如下：

 

partition by hash

 ( JYSJ  )

    partitions 4  store in

    (

         TS_ORALEARN,

         TS_ORALEARN_IDX

    );

 

参见附件脚本4

 

 

2.散列分区策略—单独散列分区示例体验

表为ACCOUNT_TRADE_SHI，方法同前面，分区语句如下：

 

partition by hash( JYSJ  )(

        partition  ACCOUNT_TRADE_SHI_P1

             tablespace TS_ORALEARN,

        partition  ACCOUNT_TRADE_SHI_P2

              tablespace TS_ORALEARN_IDX,

        partition  ACCOUNT_TRADE_SHI_P3

              tablespace TS_ORALEARN,

        partition   ACCOUNT_TRADE_SHI_P4

              tablespace TS_ORALEARN

    );

 

参见附件脚本5

 

 

3.列表分区策略－策略说明

    列表分区通过为分区键指定一系列离散的值（即枚举值）来明确的控制行数据的存储分区，其优点是可以将一些无序和不相关的数据组织在一起。如，银行系统的数据可以按区域分区。

每个分区的list_values_clause（参见语法说明部分）最少有一个值。空值可能在多个分区中出现。你可以指定一个缺省分区，且缺省分区是定义分区的最后一个。 list_values_clause的每个值在表的所有分区必须唯一。

 

3.列表分区策略－语法

   

3.列表分区策略—示例体验

表为ACCOUNT_TRADE_SL，方法同前面，分区语句如下：

partition by list ( ND  )

    (

        partition ACCOUNT_TRADE_SL_P2010

        values ( 2010  )

  tablespace TS_ORALEARN,

        partition ACCOUNT_TRADE_SL_P2011

        values ( 2011  )

  tablespace TS_ORALEARN,

        partition ACCOUNT_TRADE_SL_PD

        values ( DEFAULT  )

  tablespace TS_ORALEARN_IDX

    );

 

 

 

参见附件脚本6

 

 

习题

1.什么是海量数据库，海量数据库具有那几个显著特征？

海量数据库中最常使用的技术策略包括那几种？

2.简要说分分区的理论原理，其逻辑存储特性和物理存储特性分别是什么？

3.请分别举例说明分区显著能缩短操作处理时间、提高查询性能、减少周期性维护的宕机时间三大特性。

4.画图并分别从数据库管理员角度和应用程序角度来看其特性。

5.对于设计人员或者管理员，什么情形下应该考虑对表进行分区？