基于Oracle数据库的检索优化研究与分析

引言

        数据库的重要性不言而喻，是信息系统中十分重要的部分。一个好的数据库的系统，设计当然是关键，对数据的优化也是必不可少的部分。管理者建立数据库时，只有认真分析其运行中出现的各种性能问题，才能保证数据库高效可靠地运行。因为数据库的性能调整是一个复杂的系统工程，涉及很多方面，不能仅仅靠一个时间点的情况就断定数据库运行性能的好与坏。如何有效、合理地进行调整，数据库管理员需要经过反反复复的过程。这些都需要在大量的实践学习中不断地积累经验。数据库的优化手段有着各自不同的优点。

         为了充分利用oracle数据库的功能特性，在设计信息系统时，数据库设计人员需要根据业务情况和现有资源为基础进行设计。大数据时代已经来临，在线交易系统中每天都会产生大量的数据，需要面对很多无法用传统方法进行处理的数据。本文主要在数据库规范化设计、数据库分区技术和数据库分片技术三个方面进行研究。这样的优化思路一方面寻求CPU和存储器之间的优化平衡，另一方面将大量的数据分离成小数据进行查询。进行oracle数据库查询时，通过对小数据块的遍历得出的效率可以得到明显的提高。以这种思想为基础，根据提前准备的数据模型，结合具体设计方案，来证明oracle数据优化的可行性。

1 Oracle的简介

1.1 Oracle起源以及发展

Oracle起源于20世纪七十年代，伴随着计算机技术的产生和发展而发展，最初是一间名为Ampex的软件公司为中央情报局设计的一套名叫Oracle的数据库，之后成为计算机在各行各业数据管理技术延伸、渗透、发展的产物。如今，Oracle已经成为世界上最优秀、使用最广泛的关系数据库管理系统之一，以能够提供分布式信息安全性、完整性、一致性，强大的并发控制和恢复能力以及管理超大规模数据库的能力而著称于世。

1.2 Oracle体系结构

数据库的体系结构可以大体划分为存储结构和软件结构。其中存储结构分为逻辑存储结构和物理存储结构，这两者既相互独立又相互联系。软件结构则由内存结构和进程结构组成。如图1所示

(1)    数据库逻辑存储结构。数据库逻辑存储结构是oracle数据库存储结构的核心内容，描述oracle内部组织和管理数据的方式。它是从逻辑的角度分析数据库的组成的，它包括方案、数据块、区间、段、表和表空间等。数据库由若干个表空间组成，表空间由多个段组成，段由区间组成，区间则由数据块组成。

(2)     物理存储结构。物理存储结构由构成数据库的操作系统文件所决定，它不是独立存在的，而是与逻辑存储结构有着密不可分的联系。oracle的数据在逻辑上存储在表空间中，而在物理上存储在表空间所对应的数据文件当中。

(3)     内存结构。内存结构是oracle数据库体系中最重要的一部分，内存也是影响数据库性能的第一因素。按照内存的使用方法的不同，oracle数据库的内存又可以分为系统全局区（SGA）和程序共享区（PGA）两种内存结构。

(4)     进程结构。oracle是一个多进程的系统，例程中的每个进程都执行特定的任务。通过把oracle和数据库应用程序的工作分解成不同的进程，多个用户和应用程序就可同时连接到一个数据库例程。oracle进程分为服务器进程和用户进程。

 

图1体系结构图

1.3 Oracle数据库性能的优化方向

当我们提升数据库性能的时候主要考虑的方向应该是：

（1）CPU方面。计算机的计算能力跟数据库的反应能力是成正比的，因此我们可以考虑提高计算机的计算能力来提高计算机的计算能力。

（2）内存方面。计算机的内存空间的提高可以考虑提高数据库的SGA的空间，当SGA的大小提高时能够提高数据库的缓存区的空间。内存空间的提高能够使得计算机的反应速率得到显著的提高，进而使得数据库的性能得到明显的提高，因此适当的提高内存空间的大小对于数据库性能的提高起到了至关重要的作用。

（3）硬盘方面。计算机的硬盘的读写速率主要考虑的方向应该是硬盘的写速率问题。硬盘的执行效率虽然能够提高计算机的实际运行效率但是我们在select研究的过程中，可以忽略写速率，建议使用固态硬盘来提高普通硬盘的效率。

综上考虑：考虑到硬件设备的限制条件，权衡性价比问题，建议按照先优化内存，次之CPU，最后硬盘的顺序进行处理。

2 Oracle数据库设计规范化

2.1数据库范式理论

构成数据库需要按一定的规则来进行。范式就是存在于数据库中的这种规则。范式是一种符合某种级别关系的集合。数据库中的关系必须满足不同的范式，符合一定的要求。到现在为止，一共存在六种范式，分别为第一范式、第二范式、第三范式、第四范式、第五范式和第六范式。其中第一范式满足最低要求，在第一范式的基础上符合更多需求的为第二范式，同理可得其它范式。在平常应用中，数据库满足第三范式即可。

第三范式以第一范式为基础，任何非主属性不依赖于其它非主属性。第三范式是第二范式的一个子集，即满足第三范式必须满足第二范式。简而言之，第三范式要求一个关系中不包含已在其它关系已包含的非主关键字信息。

2.2案例应用

“实践是检验真理的唯一标准”，本文选择某在线交易系统为案例，增强论证的事实性。在线交易系统中每天都会产生大量的数据，通过多表连接来解决大数据的应用需求是一个可行的优化思路。根据对于在线交易系统的分析，以及对于oracle这种关系型数据库的理解，通过对于系统的裂解，分离出如下几种数据表模型：（1）用户（2）客户（3）商品（4）订单（5）店铺（6）品类（7）品种（8）品名。这几种数据表模型简单的将在线交易系统关系描述出来(为了增加研究效果，暂时只考虑关系，忽略其他的业务需求)，增强研究的可视性。

这样的元素需要结合关系型数据库的范式理论为基础进行设计。如果在设计过程中过于追求第三范式，能够使数据库结构清晰明了的同时加重系统硬件设备的负担。关于数据库关系的设计提供如下两套设计方案：

2.2.1设计方案一

完全遵循第三范式的情况下，表与表之间的关联性达到一种最精简的配置，这样使得表与表之间的关联没有任何冗余，表与表之间达到低耦合连接，表与表之间的独立性特别强。弊端是大大增加了CPU的负担。

设计的E-R图如图2所示：

图2完全遵循第三范式的数据库概念结构设计图

用户表包括用户ID，用户名称，用户状态三个属性。如表1所示：

表1用户表(tab_user_info_for_test)

客户表包括客户ID，客户名称，客户状态三个属性。如表2所示：

表2客户表(tab_customer_info_for_test)

订单表包括订单ID，订单名称，订单状态，用户ID四个属性。如表3所示：

表3订单表(tab_order_info_for_test)

店铺表包括店铺ID，店铺名称，店铺状态，客户ID四个属性。如表4所示：

表4店铺表(tab_store_info_for_test)

订单明细表包括明细ID，明细名称，明细状态，商品ID，订单ID五个属性。如表5所示：

表5订单明细表(tab_order_detail_info_for_test)

品类表包括类型ID，类型名称，类型状态三个属性。如表6所示：

表6品类表(tabf_ commodity_type_for_test)

品种表包括种类ID，种类名称，种类状态，类型ID四个属性。如表7所示：

表7品种表(tabf_ commodity_class_for_test)

品名表包括品名ID，品名名称，品名状态，类型ID四个属性。如表8所示：

表8品名表(tabf_ commodity_goods_for_test)

商品表包括商品ID，商品名称，商品状态，店铺ID，商品ID五个属性。如表9所示：

表9商品表(tab_commodity_info_for_test)

当完全遵循第三范式来进行实际设计时数据库结构非常清晰，表与表之间的关系特别独立，甚至说是没有任何属性与属性之间的交集。这样会导致在进行多表连接查询的时候，对于系统的要求特别高，甚至是顶配的服务器才能满足大量数据的查询。这样的设计可以说是不完美的，是一种理想状态下的设计。

2.2.2设计方案二

不完全遵循第三范式就是消耗存储空间，增大表的冗余度，这样在数据处理的时候尽可能减少多表连接操作，减少了CPU负担。但是这样也存在一定的弊端使表与表之间的冗余度特别大。

设计的E-R图如图3所示：

 

图3不完全遵循第三范式数据库设计图

店铺表和商品表可以采用方案设计一进行，保持一致。将方案一的表1和表2设计成实体表。实体表包括实体ID，实体名称，实体状态，实体类型(对于用户和客户的区别)四个属性。如表10所示：

表10实体表(tab_entity_info_for_test)

将方案一的表3和表5设计成订单表以及订单流水表。其包括订单ID，订单名称，订单状态，用户ID，实体ID，商品ID，父类ID7个属性。如表11所示：

表11订单表以及订单流水表(tab_order_info_for_test)

将方案一的表6表7表8设计成品牌表。其包括品牌ID，品牌名称，品牌状态，父类ID，品牌类型5个属性。如表12所示： 

表12品牌表(tabf_commodity_brand_for_test)

这样的设计使得表与表之间的共性特别大，使表与表、对象与对象之间的关系没有那么明晰。单表的体积会得到大幅度的提高，对于存储器的要求相对就比较高。但是可以减少查询时间，提高查询效率。

2.2.3数据库设计的分析与优化

综上两种方案实质上是CPU与存储器之间的角逐。结合企业现实存在的成本情况来讲，将数据库设计方案的范式要求控制到第一范式以及第二范式之间(表与表之间采用必要的冗余度，例如订单明细里的商品名称)，必要的基础数据表(例如品种，品类，品名这些商品固有属性的翻译表)严格采用第三范式。这样会使开发成本得到大幅度的降低，还能使得数据库的效率得到质的提升。因此介于第一种和第二种之间的综合方案得以启发：

除了订单明细表外，其它表的设计完全参照设计方案一进行。订单明细表包括明细ID，明细名称，明细状态，商品ID，商品名称，订单ID六个属性。如表13所示：

表13订单明细表(tab_order_detail_info_for_test)

其实上述设计跟设计方案一是类似的，只不过商品名称属于订单明细表的冗余部分。如果严格遵循第三范式它不应该存放在设计思路中，但在综合方案中可以进行中和设计，适当的对订单明细表进行冗余。

虽然综合方案与严格遵循第三范式的第一种方案相比在表与表关系的表述上不够完美，但是它能够节省我们的成本，同时也能明确的表示出数据对象之间的关系。综合方案的设计原则会降低对范式的依赖性，在实际生产过程中尽可能的减少连表操作，使数据提取的效率得到较高层次的提升。这样将综合设计方案中的表空间定义为百万级数据，研究对象的设计思想主要应用为综合方案基础。如果在实际应用中查询订单中包含的商品信息时的操作如下：

设计方案一可以如下操作：

select *from tab_order_detail_info_for_test t left join tab_commodity_info_for_test t1on t.commodity_id=t1.commodity_id;查询用时为1100.047秒，如下图4所示:

图 4方案一用时图

综合方案可以如下操作：

select *from tab_order_detail_info_for_test t。查询用时为900.041秒，如下图5所示:

图 5综合方案用时图

3分区技术

3.1分区的好处

当数据库达到百万级甚至千万级的时候，如果只是单纯通过sql优化这样的处理，已经远远不能满足快速增长的用户需求与性能要求之间的关系。在这样的前提下我们需要考虑通过oracle的自身技术对数据表进行分区。数据表进行分区之后，在“外人”看来是一张完整的表，实质上是将数据表中的数据存放到若干表空间上。每次查询数据的时候至于进行全量查询，而是单独扫描对应的表空间。这样的操作会使系统的性能得到明显提升。

分区技术将数据表、数据索引或者数据索引组织表进一步细划分为数据段。每个数据段都有自己的标识，也可以单独选择自己的存储特性。对于DBA来讲可以对数据段进行集中管理，也可分散管理。从应用程度的角色关系方面看，需要访问数据时可以完全采用未分区之前的sql，无形中提高了效率。

面对数据表中百万级别的数据时，为提高数据的访问速度，采用数据分区技术对于关键的节点进行分区，可以使系统的性能得到优化。

3.2分区技术应用情景

（1）表的大小超过2GB。(按照理论此时百万规模的数据表应用数据库分区技术是存在些许浪费的，但是鉴于数据产生的延迟性以及提高研究的针对性，决定对于系统进行数据库分区操作。)

(2）表中包含大量的历史数据，新的数据需要被增加到新的分区中。

3.3分区技术研究

Oracle 数据库 10g 提供了以下技术用于对表进行分区：范围分区、列表分区、散列分区、组合范围散列分区、组合范围列表分区。

3.3.1 范围分区

（1）简介

范围分区将数据按照范围划分到分区中，这样的范围划分要求我们找到具有可范围划分的分区键。一般分区键可以使用id的范围，或者使用日期也是不错的选择。
   （2）规则

a分区中必须包括VALUESLESS THEN子句，他提供了一个左闭右开区间。对于大于等于VALUES LESS THEN值都会被自动分配到下一区间。

b隐藏的下限值，这个下限值就是上一个分区的上限值。

c在最高的分区中，MAXVALUE必须被定义。MAXVALUE代表一个可以膨胀的不确定的值。它高于任何分区键的值，是一个“神秘的角色”，他就是整个表的上限。

（3）分区方案(按照ID的大小进行分区)： 

PARTITION BY RANGE (user_id) 
( 
 PARTITION  user_id1  VALUES  LESS THAN (200000) TABLESPACE CUS_TS01, 
 PARTITION  user_id2  VALUES  LESS THAN (400000) TABLESPACE CUS_TS02，

 PARTITION  user_id3  VALUES LESS THAN (MAXVALUE) TABLESPACE CUS_TS03
)；

此方案就是将tab_user_info_for_test表按照user_id进行区分，将user_id为1-199999的存放到第一分区；将user_id为200000-399999的存放到第二分区，将400000之后的数据放到第三分区。当我们在遍历数据的时候就能够直接去分区上进行遍历，假设user_id为888888，那么直接遍历第三分区就行，这样的效率是直接能用眼睛来看出来的。
3.3.2 列表分区

（1）简介

列表分区主要针对于数据列中具体的值进行分区。

（2）分区方案(对于有效无效状态的区分)：

PARTITION BY LIST (user_status) 
( 
      PARTITION PROB_ACTIVE   VALUES (1) TABLESPACE PROB_TS01, 
      PARTITION PROB_INACTIVE VALUES (0) TABLESPACE PROB_TS02

);

此分区方案将user_status这一列按照列值进行分区，将状态为1的分为第一分区；将状态为0的分为第二分区。假设存在500000条1,500000条0，当查询0的时候就直接去第二分区查询，将会节省大量的时间成本。

3.3.3散列分区
（1）简介

散列分区的实现原理是在数据表的列值上使用hash算法。通过这样的算法来确定数据行归并与具体的分区中。当数据列中没有确定的条件时，散列分区成了一个不错的选择。

PARTITION BY HASH (user_id) 
( 
PARTITION PART01 TABLESPACE HASH_TS01, 
PARTITION PART02 TABLESPACE HASH_TS02, 
PARTITION PART03 TABLESPACE HASH_TS03 
)；

（2）分区方案

首先我们要明白这样的数据分布是相对均匀的，假如1000000条数据被分到是三个分区中，那么假设我们我们需要的数据在第二分区，那就根本不需要去遍历第一分区的数据，节省了大约333333条数据，节省了大量的时间成本。

   （3）实现原理

鉴于hash算法的特殊性，我们在进行分区个数的确定时，需要对于hash进行公平性划分，研究证实最好将分区个数定义在2的n次方，这样可以使数据的分布更加均匀。

 

3.3.4组合范围分区

（1）简介

组合范围分区是将范围分区和列表分区进行组合进而产生一种新分区的方式。首先按照列值进行范围分区，然后再按列值进行列表分区。

（2）分区案例

PARTITION BY RANGE(user_id) SUBPARTITION BY LIST (user_status)
(
   PARTITION P1 VALUES LESS THAN(200000)TABLESPACE rang1 
          ( 
              SUBPARTITION P1SUB1 VALUES (1) TABLESPACE rang1, 
              SUBPARTITION P1SUB2 VALUES (0) TABLESPACE rang1 
          ), 
   PARTITION P2 VALUES LESS THAN (400000) TABLESPACE rang1 
          ( 
              SUBPARTITION P2SUB1 VALUES (1) TABLESPACE rang1, 
              SUBPARTITION P2SUB2 VALUES (0) TABLESPACE rang1 
          ) ,

   PARTITION P3 VALUES LESS THAN (MAXVALUE) TABLESPACE rang1 
          ( 
              SUBPARTITION P3SUB1 VALUES (1) TABLESPACE rang1, 
              SUBPARTITION P3SUB2 VALUES (0) TABLESPACE rang1 
          ) 
);

这样的关系是一种即又的关系，先将数据按照范围分好区，再将数据按照列值分好区。假如我们有user_id为888888，user_status为1这样一条数据时，只需要去p3sub1上遍历，这样节约的成本不仅仅是400000条数据这么简单，甚至可能更多。

3.3.5复合范围散列分区

（1）简介

复合范围散列分区是将范围分区和散列分区进行组合进而产生新分区的方式。首先按列值进行范围分区，然后再按列值进行散列分区。

（2）分区案例

partition by range(user_id) subpartition by hash(user_name) subpartitions 3

store in (space1, space1, space1) 
( 
     partition part_01 values less than(200000), 
     partition part_02 values less than(400000), 
     partition part_03 values less than(maxvalue) 
);

其实这样的设计是跟组合范围分区有异曲同工之妙，通过组合的方式会比单种分区方法的效率更高。

3.4分区技术的案例应用

将前文中在线交易系统数据库设计综合方案中的用户表范围分区得[0,200000], [200001,400000],[400001-600000], [600001-800000], [800001-9999999999）根据这样的设计规则进行实际分区操作：

PARTITION BY RANGE (user_id) 
( 
 PARTITION  user_id1  VALUES  LESS THAN (200001) TABLESPACE CUS_TS01, 
 PARTITION  user_id2  VALUES  LESS THAN (400001) TABLESPACE CUS_TS02，

 PARTITION  user_id3  VALUES  LESS THAN (600001) TABLESPACE CUS_TS03，

 PARTITION  user_id4  VALUES  LESS THAN (800001) TABLESPACE CUS_TS04，

 PARTITION  user_id5  VALUES  LESS THAN (MAXVALUE) TABLESPACE CUS_TS05
)；

这样的设计规则在查询的时候直接定位到所在的分区，查询的时间将明显减少，在未分区和已分区的用户表中分别查询，执行SQL语句得到结果如下：

select* from tab_user_info_for_test t where t.user_id=900000;             未分区查询的消耗时间为1000.047秒如下图6所示:

图 6未分区查询用时图

分区之后执行sql消耗时间为700.011秒如下图7所示:

图 7分区查询用时图

在实际应用中，根据不同的情况需要做出不同的分区选择，选择合适的分区技术进行优化，提高数据库的效率。

4分库分表(分片)技术

4.1分库分表技术简介

分库分表是应用层的一种数据划分，它将一个大的数据库文件通过事先约定好的规则切分成若干个小数据块文件，这样就可以缓解性能问题。当存在大量的数据时，可以适当将关系密切的表切分放到一个数据库上。单表数据量巨大的情况可以按照某些规则切分到多个数据库中，切分技术主要分为垂直切分和水平切分两种形式。

4.2垂直切分

垂直划分是将业务逻辑关联性较大的业务放到一个数据库中，使小业务模块形成流程模块，降低了模块与模块之间的耦合性。不同的业务放到不同的数据库中，对于应用程序影响较低。如下图8所示：

 

图8垂直切分图

4.3水平切分

水平切分就是在某一个表的数据量巨大的时，我们对于这个表的查询就变得很慢，此时我们可以将此表拆分到多个数据库中，减少多表连接操作，减少查询时间，提高数据库效率。但是这样使我们后期的维护工作量变大。如下图9所示：

图9水平切分图

 

4.4切分技术的实际应用

4.4.1切分技术应用概述

垂直切分仅仅将业务划分的特别明确，水平切分仅仅将表变得短小。而在实际的应用开发中，往往既需要我们将业务划分的特别明确，又需要将单表的遍历效率变得高效。此时这样的业务需求就需要结合垂直切分和水平切分进行实际开发。一般需要我们先对系统进行垂直切分，然后再针对实际的单表进行水平切分。

图9水平和垂直切分综合应用图

4.4.2切分技术的应用

对于整个在线交易系统的划分使用多元化的处理来进行，对于百万级的数据兼容采用的是水平分割这样的处理方式。其实整个系统数据量比较大的集中在用户表、订单表、商品表这三个表，对于这三个表的处理直接影响到系统的性能是否能够得到明显的提升。在整个系统的垂直划分上采用现有的垂直切分，不做任何处理。在水平切分上，借助于后台端的分级处理控制技术将用户表这样的百万数据分离到十个表中，分为自定义为用户表、客户表、订单表、店铺表、订单明细表、品类表、品种表、品名表、商品表。通过事先定义好的数据规则，对查询数据先进行判断，判断好的数据再进行数据库的读写操作。这样使原先在数据库上的行为分离一部分到服务器上，更有利于整个系统模块独立性的提高，但是对于后台端的性能要求就提高了一个档次。

将系统中的用户表、客户表垂直切分为第一分区，将店铺表垂直切分为第二分区，然后将订单表、订单明细表垂直切分为第三分区 ,最后将商品表、品类表、品种表、品名表垂直切分为第四分区。这样在垂直层就会出现用户/管理员、店铺、订单、商品这样的四个分区，保证在不平衡增长的过程中仍然能够平衡系统的关系。在水平层上对用户表、订单表、商品表这样几个增长明显的表进行水平切分，使用户请求访问系统时，我们能够通过分布式部署将压力分摊到其它的服务器上。这样服务器的单机处理压力能够降下来，oracle数据库的性能得到很好的发挥，oracle在应用层的优化得到实现。但是这样需要一个比较综合的服务器部署方案，不仅仅要结合oracle的特性来进行，还需要根据实际业务进行实际分析。

5总结

数据库优化方案五花八门，每一个分支都是值得研究的方向。本文主要通过对数据库规范化设计方向、oracle自身优化策略的数据库分区技术和应用层的数据库分片技术的分析，结合在线交易系统的案例进行研究。在研究时首先要通过一个健全的数据库来进行，在设计数据库的时候并没有严格按照数据库的第三范式理论，而是牺牲了一部分存储空间。数据库范式从第一范式到第二范式进行，这样既能提高数据库应该有的性能，又可以降低成本。良好的数据库设计完成后将数据库扩展为百万级规格，这样产生的数据比较明显，具有针对性。数据库分区技术实现后，将数据分离到不同的分区中，这样遍历时直接在对应的分区查询，将大数据块分割为小数据块。数据库分片技术结合正确的策略(水平结合垂直)主要将数据库表分为几个表，这样通过应用层控制就能够实现对应数据库的查询，使查询效率得到提高。通过实质性的设计研究来证实结论的可行性。不管是数据库分区技术还是分片技术，其实两个的切入点都是将大数据块切分为若干个小数据块，这样在进行主要的查询操作时就能减少数据库的查询时间，通俗的讲就是能够尽快的找到我们所需要的数据。这样做既能提高数据库检索效率又能够使系统稳定性得到提高。优秀的系统的产生不是依靠调试，而是取决于设计的合理性，在数据库设计阶段就对整个系统的任务和需求进行充分的估计和分析，在方便应用程序开发的同时还会大大简化运行阶段的修改工作。

虽然论点在客观性上得到证实，但是并没有实际应用中的突发性，一切数据都是测试数据，并未完全展示实质客观性，在数据真实性上存在缺陷。本文的技术只是优化的一方面，并不是最全面的。当然，“优化”的实现并不会让人们一劳永逸，随着实际情况的改变进行适当的调整是基本的要求。当数据库设计发生变化时，为了满足当时的需求，也必须对原先的优化进行调整。

 

 

 

 

 

 

 

 

参考文献：

[1] 李妍,李占波.Oracle 数据库应用及基础[M].北京：清华大学出版社,2013.

[2] 简朝阳.MySQL 性能调优与架构设计[M].北京：电子工业出版社,2009.

[3] 盖国强,冯春培,叶梁等.Oracle 数据库性能优化[M].北京：人民邮电出版社,2005.

[4] 李俊民.精通SOL—结构化检索语言详解[M].2版.北京：人民邮电出版社,2008.

[5] 韩耀堂.探索oracle数据库性能优化策略[J].电脑编程技巧与维护,2011,(20):56-57.

[6] 魏冰,翁盛鑫.ORACLE数据库优化之分区技术[J].海军总医院报,2009,22(1):46-48.

[7] 蒋涛.分段时序查询优化方法研究[D].湖北：华中科技大学,2010.

[8] 刘博.Oracle数据库性能调整与优化[D].大连：大连理工大学,2007.