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

作者为:? SHOUG成员 – 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、… …。

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

2. “分表”的弊端

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

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

3. 深层次原因分析

本人早在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种组合分区技术。

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

4. 摈弃“分表”，全面采取“分区”策略

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

• 以用户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索引，这类索引将导致日常查询性能低下。

5. 罗马不是一日建成的

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

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

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

6. 与MySQL相关的话题

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

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

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

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

2014．10．18于北京

Related posts:

1. Oracle Partitioning分区技术历年新特性回顾

原文地址：Oracle Acs资深顾问罗敏 老罗技术核心感悟：分表还是分区？, 感谢原作者分享。