基于Oracle和温度数据的SQL优化方案

黄天峰

摘要：该文基于Oracle数据库和某物流系统，使用SQL优化技术和冷温热数据划分技术，设计并且驗证了一个物流系统出库表在Oracle数据库中的SQL查询优化方案，并且对方案的可行性做出了论证和实现，基本达到了期初目标的效果。本优化方案可以广泛地应用于各个类似与订单系统中数据库查询效率的优化。

关键词：物流系统；Oracle；SQL优化；冷温热数据

中图分类号：TP391 文献标识码：A 文章编号：1009-3044（2018）21-0003-02

Abstract： This dissertation based on Oracle and a logistics system， using a variety of SQL optimization and temperature data partitioning technology. The SQL query optimization scheme of a logistics system outbound table in Oracle is designed and validated. The feasibility of the scheme is demonstrated and realized. Basically， the initial target has been achieved. This optimization scheme can be widely applied to the optimization of database query efficiency in various similar order systems.

Key words： Logistics System； Oracle； Temperature Data； SQL Optimization

1 国内外SQL优化现状

查询优化概念自70年代启以来一直成为研究的热点，从近十年的研究情况来看，SQL性能优化可以分为以下几个方面：

第一、优化SQL本身。从减少查询嵌套、减少子查询、查询转换、group by、视图合并、公共子表达式消除、半连接、反连接、星形转换、避免多个查询块中的自连接、物化视图、游标变量、批处理绑定等等方面优化SQL查询效率。

第二、优化Oracle优化器。通过研发新的优化器，寻找更好的执行计划。替代Oracle自身优化器生成的执行计划，以提高SQL查询的执行效率。

第三、优化数据资源调配。在大型的分布是系统中，数据库不只是一个节点，通过多来源数据资源访问的调配，达到最短时间检索到数据的目标。

第四、数据存储优化。在集中式数据库中，通过海量数据存储的优化，通过数据分层等操作缩小数据检索范围、减少I/O的操作来提升SQL查询的效率。

第五、存储设备优化研究。任何数据都是以存储设备为媒介进行存储，存储媒介有磁带、软盘、硬盘、闪存等存储介质。通过对硬件的研究，从硬件的I/O响应速度和数据块读取效率出发提高SQL查询的执行效率。

第六、优化业务应用程序。数据库中SQL查询的执行效率低下大部分是由于业务系统SQL的不合理产生，直接优化业务应用程序以提高SQL查询的执行效率。

2 SQL查询优化需求

2.1 SQL查询效率

物流系统是一个庞大的系统，物流数据库中有许多表，每张表都围绕物流业务展开。本文以物流出库单为例展开讨论，物流出库单在Oracle数据库中主要涉及两张表，物流出库单主表和物流出库单子表。在运营过程中，每一单出库都会生成一条记录，并且主单细单同时生成，因此这两张表数据量非常庞大，特别是一个系统运行几年以后，其数据已经达到不可想象的程度。物流中一个出库的完成需要经过许多工序，如生成单据、修改单据、关闭单据、打开单据和废除单据等，他们在数据库中的体现就是Update、Insert和Select操作。由于物流特性，系统中不存在删除单据，因此对于出库单不存在Delete操作。物流出库单的这些属性使得出库单表使用非常频繁，系统高峰期SQL的性能非常低下。

2.2 数据质量保证

脏数据，系统中任何操作都会产生一系列事物，每一个事物都可能出错，从而产生一些重复、错误和无效的数据。由于物流系统非常庞大涉及全国的物流运维，每小时的有效出库单据量达到上万单。其中产生的脏数据量非常庞大，因此需要数据清洗。数据安全分为数据本身的安全和数据防护的安全。数据本身安全是指数据保密性、数据完整性、数据一致性等保护。数据防护的安全是指使用一些技术手段对数据进行防护，比如数据备份、数据容灾等手段保证数据的安全。

2.3 物流出库数据划分

物流出库表中的数据自从物流系统成立20年以来一直未做处理，表中已经具有10亿左右条数据，并且随着时间的推移一直在增长。在物流出库业务高峰期，每小时的出库单据量可以达到1万单以上。在这庞大的数据量中，为提高SQL的查询效率，提高系统的运营能力，首先需要对10亿数据进行划分。

2.4 存储设备分配

存储分配，即冷温热数据的存储归属。将经常访问的数据（热数据）存储在快速存储器上，不经常访问的数据（温数据）存储在速度稍慢的存储器上，极少访问的数据（冷数据）存储在速度很慢且比较便宜的存储器上。如果热数据变冷并且不经常访问，那么可通过动态方式将其移至速度较慢的存储器。

3 SQL查询优化方案设计

3.1 优化方案总体设计

通过存储分配、热力度处理等操作对系统进行优化，如图1所示。

3.2 物流出库表数据冷温热划分

首先按照月份年份把物流出库单主表划分成当月、当年非当月和非当年三类数据。根据业务关系，每月初是前月对账结转时间，对账结转代表前月数据已经进入查询和少量更改的操作，因此物流出库单主表中数据可以做以下定义：定义当月数据为热数据；当年非当月数据为温数据；非当年数据为冷数据。

3.3 热数据划分

对数据整体进行划分后，其中热数据部分进行进一步划分。首先，热数据从0到9共分为10个等级，0最低9最高。每月结转时，该月的数据全部设定为N。每月28至31天不等，每个等级固定对应3天。每次SQL检索范围缩小为0至3天的数据量，有效地提升了SQL的效率。热力度的数学模型如下，S代表分区表PSBILL_OUT_H的整体优化查询系统。系统中有两种分区方式，一种是时间分区，另一种是数据热力度的分区。S={To，Tn，Po，Pn，HD}，其中：

To：结转后数据，按照每月一个分区表设计。

Tn：未结转的本月数据，按照热力度等级分布在各热力分区上。

Po：为结转数据分区，其命名格式为PSBILL_OUT_HYYMM，如PSBILL_OUT_H1701上存放2017年01月数据，其中PSBILL_OUT_H18MX为特殊分区，防止生产停止所设额外预留分区。

Pn：为未结转本月数据分区，其命名格式为PSBILL_OUT_HN，如PSBILL_OUT_H2存放热力度为2的数据，其中N满足HDn函数。

HD：未结转数据热力度标记，由于物流出库单单据的热力度由时间决定，因此离当前时间sysdate越近则热力度越高，所以其满足函数

[HDn=0≤sysdate-TS-3*n≤2，其中n∈0，9null，月结时统一至为null并且n=N]

根据HDn函数，可以把函数转换成热数据图灵机，如图2所示。根据物流业务特点，图灵机每日深夜执行。根据数学模型，如果不考虑业务对数据分布形态的影响，那么对于物流出库表中每一条数据查询的复杂度为n/2，即每条数据的平均查询次数为5次。而查询一条数据的时间和数据扫描的范围是成指数关系。假设查询范围为X万条数据，查询一条数据所消耗的时间为T，可以得到公式T=A[X]，A>1决定时间的增长基数。随着时间的推移，T将变成原来的1/n，数据的查询效率得到极大的提高。

3.4 物流出库表详细设计图

通过热数据的划分，结合数据模型和图灵机，物流出库表主表的详细设计如图3所示。字段HD（热力标志）表示热力度等级，当月的热力度分区共有11个分区，其中HD0至HD9用于划分好的热数据使用，HDN在月末给月结使用，并且月结标志着数据从热数据转为温数据。HDN对应的表空间存放于相对于SSD闪存比较缓慢的FC/SAS RAID存储阵列。

4 总结

本课题论文的工作内容是具体研究了一个物流系统出库表在Oracle数据库中的SQL查询优化方案的制定与实现。期望可以通过大数据划分可以减少物理I/O的技术，基于Oracle数据库的Partition对于数据块控制的管理办法，以热数据的热力度划分为核心，完成对SQL查询的优化。本文课题与课题设计出的优化方案以及实现逻辑，在大多数企业的生产应用具有一定的指导意义，特别是对已经运行很长时间的系统有明确的指导方向。

参考文献：

[1]D. Li， L. Han and Y. Ding.SQL Query Optimization Methods of Relation Database System[J].Computer Engineering and Applications （ICCEA）， 2010 ，1 （1） ：557-560.

[2]S. Chande and M. Sinha.Genetic optimization for the join ordering problem of database Queries[J]. India Conference （INDICON）， 2012：1-5.

[3]S. Bellamokanda， R. Ahmand and A. Witkowski.Enhanced Subquery Optimizations in oracle[J]. Proceeding of the VLDB Endowment， 2009，2 （2） ：1366-1377.

[4]F. Sun and L. Wing.Paging Query Optimization of Massive Data in Oracle 10g Database[J]. Computer and Information Science and Service System （CSSS）， IEEE International Conference， 2011，28 （9） ：2388-2391.

[5]A. Hameurlain.Evolution of Query Optimization Methods： From Centralized Database Systems to Data Grid Systems[C]. Proceedings of the 20th International Conference on Database and Expert Systems Applications， 2009， 5690 ：460-470.

[6]H. Herodotou， N. Borisov and S. Babu.Query Optimization Techniques for Partitioned Tables[C]. ACM SIGMOD International Conference on Management of data， 2011，10 （4） ：49-60.

[7]張师超.大数据RD分割方法.中华人民共和国，知识产权类，G06F，2013.10.02.

[8]Maksym Petrenko，Mike Winer and Joyce Coleman.Best Practices Multi-temperature Data Management[J].IBM? Smart Analytics System，2011：4-5.

[9]Guy Harrison.Oracle性能优化求生指南[M].北京：人民邮电出版社，2012：56-72.

【通联编辑：梁书】