Oracle Acs资深顾问罗敏 老罗技术核心感悟:分表还是分区?

作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

 

 

  1. 分表 + 分区”

多年来,某移动行业开发商在针对海量数据库进行设计时,一直在采用“分表 + 分区”策略。在正在进行的新一代移动业务支撑系统设计开发中,也仍然沿用这种策略。以下就是相关细节及本人的评估:

  • 按用户id头两位建表分区
  • cs_scoreuse_rd积分消费记录表
  • cs_userdetail_info用户详细信息表
  • cs_userdetaildead_info用户详细信息拨备表
  • CS_UserAdd_info_$X 用户附加信息表
  • CS_UserAdddead_info用户附加信息拨备表
  • CS_UserState_info_$X用户状态变更轨迹表

详细含义是:由于用户id字段的前两位代表地市,实际上这类表是按地市进行范围分区。这样分区的目的是什么呢?通过与设计人员沟通,其实就是为了将这些大表划分成更小的物理表。但是,每个地市的数据量是不均匀的,因此每个分区的数据也是不均匀的,每个分区访问性能良莠不齐,并不能保证数据访问整体性能的最佳。

  • 按用户id最后一位分十张分表
  • CS_UserAdd_info_$X用户附加信息表
  • CS_UserState_info_$X用户状态变更轨迹表

实际上这两张表分别代表10张表,例如CS_UserAdd_info_$X表是代表CS_UserAdd_info_0、CS_UserAdd_info_1… …CS_UserAdd_info_9。然后再按用户id头两位对这10张表进行上述范围分区。这就是典型的“分表 + 分区”策略。为什么要这么设计呢?设计人员的回答是如果不分表,由于CS_UserAdd_info记录非常多,直接按用户id头两位按地市分区,分区表记录仍然很多。大家看出问题了吗?下面我再详细分析,先留个伏笔。

  • 所有历史表均为年月分表

在该系统设计中,上述表均有历史表,设计人员将这类历史表均按年月分表。例如:用户详细信息表(cs_userdetail_info)的历史表包括:cs_userdetail_info_201401、cs_userdetail_info_201402、cs_userdetail_info_201403、cs_userdetail_info_201404、… …。

大家首先可以想像一下,随着运行时间的增长,该系统将有多少表?

 

  1. 分表”的弊端

在Oracle公司推出分区技术之前,针对大表的访问,我们只能采取分表的策略,这种策略弊端重重,而Oracle早在1996年的Oracle 8版就推出了分区技术,并且将近20年了,Oracle仍然在不懈努力地发展这一技术领域。可惜啊,该开发商仍然在沿用分表策略这一落后理念。分表到底有什么弊端呢?我们结合该系统具体情况,剖析如下:

  • 首先,设计的表和索引太多,导致运行维护工作量浩大,数据库字典内容也太多,影响整个运行效率。举个简单例子,假设业务需要增加一个字段,我们将在所有分表上面都增加这个字段,多么愚昧呀!
  • 其次,导致应用开发逻辑不灵活、复杂化。不仅每个语句都要采取拼接方式,根据客户输入的年月等信息拼出需要访问的相关表,而且如果需要查询统计业务跨月份,都需要编写大量SQL语句,并进行UNION操作以及设计大量VIEW等。应用开发人员就不嫌麻烦?
  • 分表设计导致表名动态变化,使得Oracle 11g之后自动优化工具(Automatic Tuning)和SQL Profile功能难以实施,极大地影响了SQL语句优化效果。
  • 在应用级人为进行多表设计,无法保证数据的完整性。例如我们发现在现有CRM系统中,DCUSTPAYOWEDET200703表就包含了非200703的数据。

 

  1. 深层次原因分析

本人早在2006年就曾为该移动客户提供过服务,当时针对这种“分表 + 分区”策略非常不理解。一直到2014年该开发商开发新一代核心系统了,我们终于有了与设计人员面对面的机会,才了解了更深层次的原因。

首先,我们感觉设计人员并不一味拒绝Oracle分区技术。否则,他们将全面抛弃Oracle分区技术,而全部采用分表策略。

其次,也是最根本的,通过交流我们发现,原来是设计人员不太了解Oracle分区技术。更具体的,原来他们只知道Oracle有范围分区,对HASH分区,尤其是Oracle组合分区等是一脸茫然。

现在解释前面的伏笔:设计人员为什么要将用户附加信息表(CS_UserAdd_info)既分表又分区?重复前面叙述:设计人员认为CS_UserAdd_info记录非常多,直接按用户id头两位按地市分区,分区表记录仍然很多,因此先按用户id最后一位先分成10张表,再分区。哎哟唉,你可以直接根据用户id字段进行HASH分区呀,例如4份、8份、16份、32份… …,这样不仅每个分区数据均匀,而且可以灵活地分成你想需要的份数,与地市无关!HASH分区的缺陷呢?不能按分区进行大批量数据管理。但这是用户附加信息表,我们不会存在大批量删除用户数据等管理操作的,因此这种担心是多余的。

至于历史表为什么要分表呢?同样地,设计人员不知道我们可以先按时间字段进行一维分区,再按用户id进行HASH分区。更准确地讲,设计人员可能根本不知道Oracle还有Range -List、Range -Hash、Range –Range、Interval – List、Interval – Hash、Interval–Range等9种组合分区技术。

如果我们是习武之人,假设连自己到底有多少种兵器都不知道,仅凭手中一杠红缨枪,你就敢包打天下?

 

  1. 摈弃“分表”,全面采取“分区”策略

毋庸置疑,分区相比分表有全面优势,因此,摈弃“分表”,全面采取“分区”策略是毫无疑问的。这就是我们结合上述案例的分区改造方案:

  • 以用户id进行HASH分区的表

现有设计中按用户id头两位(表示地市)进行范围分区,其目的就是均匀打散该表。为此,我们建议直接对用户id进行HASH分区。这样的好处是分区方法更简单,而且数据分布更均匀。具体的表如下:

  • cs_userdetail_info用户详细信息表。例如按用户 id字段进行16份HASH分区。
  • cs_userdetaildead_info用户详细信息拨备表。例如按用户 id字段进行16份HASH分区。
  • CS_UserAdd_info 用户附加信息表。例如按用户 id字段进行64份HASH分区。同时取消该表的分表设计,即取消CS_UserAdd_info_$X表。
  • CS_UserAdddead_info用户附加信息拨备表。例如按用户 id字段进行16份HASH分区。
  • 以Range-HASH或Interval-Hash进行组合分区的表

以下表均以Range-HASH或Interval-Hash进行组合分区,其中第一维为时间分区,第二维为用户id字段。

  • cs_scoreuse_rd积分消费记录表。例如第一维按时间字段的月或年分区,第二维按用户 id字段进行16份HASH分区。
  • CS_UserState_info用户状态变更轨迹表。例如第一维按时间字段的月或年分区,第二维按用户 id字段进行16份HASH分区。
  • 所有历史表。例如第一维按时间字段的月或年分区,第二维按用户 id字段进行16份HASH分区。

将时间字段作为第一维分区字段,主要考虑历史数据成批清除需要。若数据量不是非常大,例如一维分区之后每个分区数据不超过1千万,也可以考虑只实施时间字段的一维分区,取消用户id字段的第二维分区,简化设计。

  • 索引分区建议
  • 以用户id进行HASH分区的表

由于这类表主要按用户id进行访问,因此建议将用户id建立成Local Prefixed索引。这样,性能能得到保障。

  • 以Range-HASH或Interval-Hash进行组合分区的表

若查询条件包含第一维分区时间字段,则建议将此类查询条件建立成Local Prefixed索引。这样,不仅性能能得到保障,这类分区索引的高可用性也得到保障。

若查询条件不包含第一维分区时间字段,则建议将此类查询条件建立成Global Hash Partition索引。例如,若只按用户id进行查询,则将用户id建立成16份的Global Hash Partition索引。此类索引性能将得到保障,缺陷是当进行按分区操作(drop等)进行历史数据清理时,此类索引将失效。但只要历史数据清理频度不高,应该是可以接受的方案。建议不要将此类索引设计为local non-prefix索引,这类索引将导致日常查询性能低下。

 

  1. 罗马不是一日建成的

尽管无论是设计开发人员,还是移动用户都对分区优势有目共睹。但真要实施浩大的改造工程时,开发商部分人员又是顾虑重重,他们最担心的应用改造工作量,以各种名义进行抵触,例如强调现有系统已经比较稳定,不宜进行大规模改造。恕本人直言,他们不是以设计和开发质量为目标,而是以应用改造工作量为目标了。

唉,开发商的这部分人员太本位主义,也太缺乏长远眼光了,只考虑自己的工作量,而忽略了设计和开发本身的质量。的确,将分表改为分区将导致应用软件大幅度改造,但这种改造是对现有应用软件的大幅度简化,而且是一劳永逸的,是正能量的。趁新一代核心系统正在设计开发之际,立即着手这种改造,将会为该系统的长治久安打下良好基础。

大家重温一下伟人一句诗吧:“风物长宜放远量”。

 

  1. 与MySQL相关的话题

该移动客户的新一带核心系统还有一个特点,即交易系统采用Oracle数据库,而报表、历史数据管理等采用MySQL数据库。于是,保持两个不同平台应用兼容性,降低应用开发和管理难度,成了开发商又一个关注重点。

是否完全摈弃分表,而全面采用分区策略?不得不考虑MySQL是否支持分区了,另外开发尚还担心MySQL单表记录数有限制。为此,我们专门咨询了Oracle公司在MySQL方面的专家,得到的答复是:

  • 理论上,MySQL的表记录没有明确限制。主要取决于操作系统的文件限制。
  • MySQL已经支持分区技术。具体而言,在partition一级支持range/list/hash和其它等方式,在subpartition一级支持hash/ key等方式

既然如此,我们就如此大胆谏言了:采用Oracle和MySQL都支持的分区技术,简化数据库设计和应用开发,提高可管理性和扩展性。

 

2014.10.18于北京

Comments

  1. 深圳-刀 says

    文中少考虑了一种因素就是:拆库。很多设计上的拆表,都是为了在单个Oracle集群遇到性能天花板时进行拆库做准备。

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号