MySQL Sub-Library 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, the data operation, adding and deleting the cost will be more and more; In addition, because the distributed deployment 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.

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 the definition of database WORKDB, commodity database paydb, user database UserDB, log database logdb, respectively, for storing Project data definition table, commodity definition table, user data table, log data table and so on.

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 has a large amount of data, you can divide userdb into the same structure of multiple userdb:part0db, part1db, and then userdb the User data table usertable, Cut into many 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.

And if the database table is not many, but the data volume of a single table is very large, or the data heat is very high, this situation should choose horizontal segmentation, Horizontal segmentation than vertical segmentation to be more complex, it will logically belong to one of the data is physically divided, in addition to the segmentation of the granularity of the segmentation to do the 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 game 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.

----------Excerpt from http://blog.csdn.net/dancen/article/details/9737591