'Sub-table + partition'

For a lot of years, a mobile sector developer has been adopting the approach of 'sub-table + partition' when designing against enormous databases. This technique is still employed in the ongoing design and development of a brand new generation of mobile enterprise support systems. The following are the relevant facts and my assessment:

Build table partitions by the very first two user id
cs_scoreuse_rd points consumption record table
cs_userdetail_info user information table
cs_userdetaildead_info user details provision table
CS_UserAdd_info_ $ X user extra data table
CS_UserAdddead_info user further information provision table
CS_UserState_info_ $ X user state modify track table
The detailed meaning is: for the reason that the initial two fields in the user id field represent cities, in actual fact, this type of table is range partitioned by city. What's the purpose of such a partition? By communicating together with the designer, it is actually truly to divide these massive tables into smaller physical tables. However, the amount of information in each prefecture is uneven, so the data in every single partition can also be uneven. The access overall performance of each and every partition is uneven, and it doesn't assure the most beneficial all round performance of data access.

Ten tables according to the last digit of user id
CS_UserAdd_info_ $ X user extra information and facts table
CS_UserState_info_ $ X user state change track table
The truth is, these two tables represent 10 tables, by way of example, the CS_UserAdd_info_ $ X table represents CS_UserAdd_info_0, CS_UserAdd_info_1 ... CS_UserAdd_info_9. Then partition the 10 tables by the first two digits with the user id. This is the standard 'sub-table + partition' method. Why is it so designed? The designer's answer is the fact that if there is certainly no table division, because there are actually lots of CS_UserAdd_info records, the user IDs are divided directly by the very first two customers by city, and there are actually nevertheless a lot of table division records. Do you see the issue? I'll analyze it in detail below, leaving a forehead first.

All historical tables are year and month tables
Inside the program design and style, the above tables all have historical tables, along with the designer divides such historical tables into years and months. One example is: the history table of user facts table (cs_userdetail_info) contains: cs_userdetail_info_201401, cs_userdetail_info_201402, cs_userdetail_info_201403, cs_userdetail_info_201404, ....

You may first consider how a lot of tables the method will have because the operating time grows?



Disadvantages of 'sub-table'
Prior to Oracle introduced the partitioning technologies, we are able to only adopt the strategy of dividing tables for the access of significant tables. This tactic has lots of drawbacks, and Oracle introduced the partitioning technology as early as the Oracle8 version in 1996, and it has been practically 20 years. Oracle continues to be generating unremitting efforts to develop this technical field. It is a pity that the developer continues to be utilizing the backward notion of sub-table approach. What will be the disadvantages of sub-tables? Depending on the distinct circumstance of the method, we analyze as follows:

1st of all, also many tables and indexes are developed, which results in substantial workload of operation and upkeep, and also lots of database dictionary contents, which impacts the complete operation efficiency. As a basic example, suppose the business needs to add a field, we'll add this field on all the sub-tables, how foolish!
Second, it leads to inflexible and difficult application improvement logic. Not merely does each statement must be stitched, as well as the associated tables that must be accessed are spelled out depending on the year and month facts entered by the consumer, but in the event you ought to query statistical services across months, you'll want to create a big number of SQL statements, execute UNION operations and style a sizable number of VIEW Wait. App developers usually are not also troublesome?
The table style results in the dynamic modify of the table name, which makes it difficult to implement the automatic optimization tool (AutomaticTuning) and SQLProfile just after Oracle11g, which significantly affects the optimization effect of SQL statements.
https://en.wikipedia.org/wiki/Video -table design in the application level can not assure data integrity. For instance, we found that in the existing CRM program, the DCUSTPAYOWEDET200703 table includes non-200703 data.


In-depth trigger analysis
I had supplied services for the mobile consumer as early as 2006, and at that time, I did not have an understanding of this tactic of 'sub-table + partition'. It wasn't until 2014 that the developer created a new generation of core systems. We lastly had the opportunity to meet the designers face-to-face, only to know the deeper causes.

First of all, we feel that designers usually do not blindly reject Oracle partitioning technologies. Otherwise, they'll fully abandon the Oracle partitioning technologies and all adopt the table division strategy.

Secondly, and most fundamentally, via exchanges, we identified that designers did not realize Oracle partitioning technology. Far more particularly, it turns out that they only know that Oracle has variety partitions, and is confused about HASH partitions, in particular Oracle combined partitions.

Now clarify the earlier foreshadowing: Why do designers divide the user additional details table (CS_UserAdd_info) into tables and partitions? Repeat the preceding description: the designer believes that there are lots of CS_UserAdd_info records, and they may be straight partitioned by city and city in accordance with the initial two user IDs. You will find nevertheless many records in the partition table, so very first divide into 10 tables in line with the final user ID and after that partition. Oops, you are able to straight HASH partition depending on the user id field, including 4 copies, eight copies, 16 copies, 32 copies ..., to ensure that not merely the information of each and every partition is uniform, but in addition is usually flexibly divided in to the number of copies you'd like, It has nothing to accomplish together with the city! What concerning the defects of HASH partition? Mass data management can't be performed by partition. But this can be a user extra data table, we'll not have management operations for instance mass deletion of user data, so this worry is superfluous.

As for why the history table needs to be divided into tables? Similarly, the designer does not know that we are able to 1st execute one-dimensional partitioning by time field, then HASH partitioning by user id. To become additional precise, designers may not even realize that Oracle also has 9 combined partitioning technologies which include Range-List, Range-Hash, Range–Range, Interval–List, Interval–Hash, and Interval–Range.

If we are martial arts people today, assuming that we do n’t even understand how numerous weapons we have, should you only use a red spear inside your hand, would you dare to fight the world?



Abandon the 'sub-table' and adopt the 'partition' strategy in an all-round way
Undoubtedly, partitioning has a extensive advantage over sub-tables. Hence, it really is undoubted to abandon the 'sub-table' and adopt a 'partition' approach in an all-round way. This is our zoning transformation strategy combined using the above case:

Hash partitioned table with user id
In the current design and style, the variety is partitioned as outlined by the very first two digits from the user id (indicating the city), and its purpose will be to break up the table evenly. For this reason, we advise directly HASH partitioning the user id. The benefit of this can be that the partition process is easier along with the information distribution is extra uniform. SanDisk SD Card Video recovery is as follows:

cs_userdetail_info user particulars table. For example, 16 HASH partitions are performed in accordance with the user id field.
cs_userdetaildead_info user detailed info provision table. As an example, 16 HASH partitions are performed in accordance with the user id field.
CS_UserAdd_info user added information and facts table. As an example, 64 HASH partitions are performed in line with the user id field. In the similar time, cancel the sub-table design in the table, that may be, cancel the CS_UserAdd_info_ $ X table.
CS_UserAdddead_info user extra info provision table. As an example, 16 HASH partitions are performed in accordance with the user id field.
Combined partitioned table with Range-HASH or Interval-Hash
The following tables are all combined with Range-HASH or Interval-Hash, where the initial dimension could be the time partition plus the second dimension is definitely the user id field.

cs_scoreuse_rd points consumption record table. For sd card , the first dimension is partitioned by month or year in the time field, along with the second dimension is partitioned by 16 HASH partitions by the user id field.
CS_UserState_info user state alter track table. For instance, the first dimension is partitioned by month or year in the time field, along with the second dimension is partitioned by 16 HASH partitions by the user id field.
All history tables. For example, the very first dimension is partitioned by month or year in the time field, as well as the second dimension is partitioned by 16 HASH partitions by the user id field.
The time field is made use of as the first-dimension partition field, primarily taking into consideration the want for batch removal of historical data. In the event the quantity of data isn't very massive, for example, the data of each and every partition doesn't exceed 10 million right after the one-dimensional partition, you could also take into account implementing only one-dimensional partition from the time field, cancel the second-dimensional partition of the user id field, and simplify the design.

Index partition recommendations
Hash partitioned table with user id
Given that this kind of table is primarily accessed by user id, it is recommended that the user id be established as a LocalPrefixed index. In this way, performance is usually guaranteed.

Combined partitioned table with Range-HASH or Interval-Hash
If the query situation incorporates the first-dimension partition time field, it is actually encouraged to establish such query situation as LocalPrefixed index. In this way, not simply the functionality might be assured, but additionally the higher availability of this sort of partitioned index.

When the query condition doesn't contain the first-dimension partition time field, it is actually suggested to establish such query condition as a GlobalHashPartition index. For example, if only querying by user id, the user id are going to be established as a 16-part GlobalHashPartition index. The overall performance of this type of index might be assured. The disadvantage is that when the historical information is cleaned by partition operation (drop, etc.), this sort of index will probably be invalid. But provided that the historical data cleaning frequency is not high, it needs to be an acceptable solution. It's recommended to not design this type of index as a localnon-prefix index, which will cause poor functionality in every day queries.



Rome was not built in a day
Despite the fact that each design and style developers and mobile users are aware of the benefits of partitioning. Nevertheless, when realizing the immense renovation project, some developers are worried about it. The application transformation workload they're most worried about is in numerous names. One example is, it emphasizes that the current system is comparatively stable and large-scale renovation will not be appropriate. I bluntly say that they're not aiming in the high quality of style and improvement, however the workload of application transformation.

Alas, this part on the developer's staff is as well self-centered and lacks a long-term perspective, only contemplating their own workload, and ignoring the excellent of design and style and development itself. It's accurate that altering the sub-table to partition will result within a key transformation with the application application, but this transformation is usually a substantial simplification in the current application software program, and it can be when and for all and is optimistic power. Even though the new-generation core technique is becoming created and created, embarking on this transformation straight away will lay a superb foundation for the long-term stability with the system.

Let's revisit the great man's poem: 'Long scenery ought to be spread far.'



Subjects connected to MySQL
The mobile client's new core technique also features a feature that the trading method makes use of an Oracle database, along with the reporting and historical information management makes use of a MySQL database. Consequently, sustaining application compatibility between two distinctive platforms and minimizing the difficulty of application development and management has come to be one more concentrate of developers.

Did you absolutely abandon the sub-table and totally adopt the zoning approach? I have to think about no matter if MySQL supports partitioning. Also, the developer continues to be worried concerning the limit on the number of MySQL single-table records. To this end, we specifically consulted Oracle's MySQL authorities, as well as the response we got was:

In theory, MySQL table records have no explicit restrictions. Mainly is dependent upon the file limit of your operating system.
MySQL already supports partitioning technologies. Specifically, range / list / hash as well as other procedures are supported at the partition level, and hash / crucial and also other strategies are supported at the subpartition level.
Within this case, we're so bold to admonish: working with the partitioning technologies supported by Oracle and MySQL, simplify database style and application development, boost manageability and scalability.



2014. ten.18 in Beijing