Sub-database sub-table

1 What is the basic idea of a sub-database table?
It is literally a simple way to store data that is stored in a single library on multiple libraries, storing data that was originally stored in a single table on multiple tables.
2 Why should the basic idea be divided into sub-tables?

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, correspondingly, data operation, adding and deleting the cost of the change will be more

In addition, because of the inability of distributed deployment, and the resources of a single server (CPU, disk, memory, IO, etc.) is limited, the final database can be hosted by the amount of data, data processing capacity will encounter bottlenecks.
3 The implementation strategy of the sub-list of the library.

There are two kinds of dividing table: vertical slicing and horizontal slicing.
3.1 What is vertical slicing, which divides the table into different libraries according to the function module and the close degree of the relationship. For example, we will establish a definition database WORKDB, commodity database paydb, user database UserDB, log database logdb, etc.

It is used to store project data definition table, commodity definition table, user data table, log data table, etc.
3.2 What is horizontal slicing, when the amount of data in a table is too large, we can divide the data of that 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. For example, in our UserDB

User data table, each table of data is very large, you can divide the UserDB into the same structure of multiple userdb:part0db, part1db, and then userdb the User data table usertable, cut into a lot of usertable:

UserTable0, UserTable1, and so on, and then store these tables on more than one userdb with a certain set of rules.
3.3 Which method should be used to implement the database sub-Library sub-table, this depends on the data volume in the database bottleneck, 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 preferred.
If there are not many tables in the database, but the amount of data in a single table is very large, or the data is very hot, this situation should choose horizontal segmentation, Horizontal segmentation than vertical segmentation to be more complex, it will originally logically belong to the body of the data is physically divided

, in addition to the segmentation of the granularity of the partition to be evaluated, considering the average data and load average, the latter will also have additional data management burden 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 game project uses a combination of vertical and horizontal segmentation, and we first split the database vertically,

Then, for a subset of tables, usually the user data table, horizontal slicing.
4 The problem exists in the sub-list of the library.

4.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 capabilities of the database itself to perform transactions, you will pay a high performance cost; If the application assists

Control, the process logic of the transaction, but also cause the burden of programming.
4.2 Cross-Library join issues across tables.
After performing the sub-database table, it is difficult to avoid the original logical association of the data into different tables, different libraries, when the association operation of the table will be limited, we can not join the table in different libraries, or join the sub-table granularity does not

The same table, the result of the original query can be completed business, may require multiple queries to complete.
4.3 Additional data management burden and data computing pressure.
The additional data management burden, the most obvious is the location of the data and data deletion and modification of the repeated implementation of the problem, which can be resolved through the application, but inevitably cause additional logic operations, for example, for a record of user performance

User Data Sheet usertable, business requirements to identify the best 100-bit, before the table, only an order BY statement can be done, but after the table, will need n ORDER BY statement, each of the first 100 of the table

User data, and then consolidate the data to produce the results.
The above arrangement in the Internet

Sub-database sub-table