Database Sub-Library sub-table

First, what is the sub-database sub-table

It is literally simple to understand that a chunk of data stored in one library is stored on multiple libraries, and the data that is originally stored in a single table is stored on multiple tables
Second, why should the sub-database sub-table

The amount of data in the database is not necessarily controllable, in the case of no sub-database, with the development of time and business, the table in the library will be more and more, the amount of data in the table will be more and more large, the corresponding, data manipulation, adding and deleting the cost of the change will be larger; And the resources of a server (CPU, disk, memory, IO, etc.) are limited, the final database can carry the amount of data, data processing capacity will encounter bottlenecks

Three, the implementation strategy of sub-database sub-table

There are two types of dividing table with vertical and horizontal segmentation

1> What is vertical segmentation, the table according to the function module, the relationship between the degree of close, deployed to different libraries

2> What is horizontal slicing, when the amount of data in a table is too large, I can divide the data of the table into a number of rules, such as the UserID hash, and then store it in multiple tables with the same structure, and on different libraries

3> should be used to implement the database sub-Library sub-table, which depends on the data volume bottleneck in the database, and comprehensive project business type to consider.

If the database is caused by too many tables and large amounts of data, and the business logic of the project is clear and low-coupling, then the rules are simple and easy to implement vertical segmentation must be the first choice

And if the database is not many tables, but the amount of data in a single table, or the data is very high, this situation should be selected horizontal segmentation, horizontal segmentation than vertical segmentation to be more complex, it will be logically belong to one of the data is physically divided, in addition to the segmentation of the granularity of the segmentation to do a good job of evaluation, Considering data averaging and load averaging, the latter will also incur additional data management burdens for project personnel and applications

In real-world projects, these two situations are often both, which requires a trade-off, even if vertical segmentation is required and horizontal segmentation is required. Our project uses a combination of vertical and horizontal segmentation, where we first split the database vertically and then slice it horizontally for a subset of tables, usually user data tables.

Four, the problem of sub-database sub-table

1> Transaction Issues

After performing the Sub-library table, database transaction management is difficult because the data is stored on different libraries. If you rely on the Distributed transaction management function of the database itself to perform transactions, it will pay a high performance cost, if the application to assist the control, the formation of procedural logic transactions, but also create a convenient programming burden

Join problem of 2> cross trousers and crotch table

After performing the sub-database table, it is difficult to avoid the original logically related to the very light of the data into different tables, different libraries, when the association of the table will be limited, we can not join the table in different sub-libraries, or join the table of different granularity, the result of a query can be completed business, Multiple queries may be required to complete

3> additional data management burden and data computing power

The additional data management burden, the most obvious is the location of the data and the data deletion and re-examination of the recurrence of the problem, which can be resolved through the application, but inevitably cause additional logic operations, for example, for a record user performance of the User data table usertable, Business requirements to investigate the best 100-bit, before the table, only a single order by statement can be done, but after the table, will need n ORDER BY statement, the first 100 user data of each sub-table is identified, and then the data are combined to calculate the results

Database Sub-Library sub-table