MySQL Sub-Library sub-table tips

Table is a good way to distribute the pressure of the database.
Table, the most straightforward meaning, is to divide a table structure into multiple tables, and then, can be in the same library, can also be placed in different libraries.
Of course, the first thing you need to know is how to divide the table. Individuals think that the number of single-table records to reach the million to tens the use of the table.
Classification of sub-tables:
1. Vertical Sub-table
The content that would have been in the same table could have been artificially divided into multiple tables. (So-called originally, refers to the third paradigm according to the relational database requirements, is supposed to be in the same table.) ）
Sub-table reason: Separate according to the activity of the data (because different active data, processing method is different)
Case:
For a blog system, the article title, author, classification, creation time, etc., is the change frequency slow, the number of queries, and preferably have good real-time data, we call it cold data. and the number of blog views, replies, and so on, similar statistics, or other high-frequency changes in the data, we call it active data. Therefore, in the design of database structure, we should consider the sub-table, first of all, the processing of vertical table.
This is followed by a longitudinal sub-table:
First, the use of the storage engine is different, cold data using MyISAM can have better query data. Active data, can use InnoDB, can have better update speed.
Second, the cold data is more from the library configuration, because more operations when queried, so as to speed up the query. For thermal data, it is possible to handle the horizontal table of the main library relatively.
In fact, for some special active data, you can also consider the use of memcache, Redis and other caches, such as accumulated to a certain amount to update the database. or MongoDB type of NoSQL database, here is just an example, not to say this first.
1. Horizontal Sub-table
Literally, it can be seen that the large table structure, horizontal cutting for the same structure of the different tables, such as user Information table, user_1,user_2 and so on. The table structure is exactly the same, however, according to some specific rules to partition the table, such as according to the user ID to take the module partition.
Sub-table reason: According to the size of the data volume to divide, to ensure that the capacity of the single table is not too large, so as to ensure the single-table query processing capacity.
Case: With the above example, the blog system. When the amount of blog is very large, you should take the horizontal segmentation to reduce the pressure of each single table, to improve performance. For example, a blog cold data table, if divided into 100 tables, when there are 1 million users in the browsing, if it is single table, will make 1 million requests, and now after the table, it is likely that each table for 10,000 data requests (because, can not be absolute average, just assume), so the pressure is much lower.

Database replication can solve the problem of access, and does not solve large-scale concurrent write problems, to solve this problem is to consider MySQL data segmentation

Data segmentation, as the name implies, is scattered data, a host of data spread to multiple units, reduce the load pressure of a single host, there are two ways to divide, one is the sub-Library, that is, according to the business module divided into multiple libraries, each library table is not the same, there is a sub-table, The data is split on different hosts according to certain business rules or logic, and the table on each host is the same, which is somewhat similar to Oracle's table partitioning.

Sub-Library is called vertical partitioning, this way is relatively simple to implement, it is important for the business to be refined, when the sub-Library to understand the interaction between the various modules of the business, to avoid the future to write the program when there are too many cross-library operations.

Sub-table is also called the horizontal partition, this way is more complicated than the vertical partition, but it can solve the vertical partition cannot solve the problem, that is, the single table access and write very frequently, this time can be based on a certain business rules (PS: Internet BBS Forum membership level concept: According to the membership level of the table) to sub-table, This reduces the single-gauge pressure and also solves the frequent interaction between the various modules.

The advantages of the sub-Library are: simple implementation, the library and library boundaries between clear, easy to maintain, the disadvantage is not conducive to frequent cross-library operations, single-table data volume of large problems can not be solved.

The advantages of the sub-table is: To solve the sub-Library of the shortcomings, but the shortcomings are precisely the advantages of the sub-database, the sub-table is more complex to achieve, in particular, the Division of the rules of the table, the programming of the program, as well as the subsequent database split transplant maintenance.

In practical applications, the general Internet Enterprise's route is the first sub-library and then the table, the combination of the two, to learn from each other, so that play the biggest advantage of MySQL extension, but the disadvantage is that the architecture is very large, very complex, the application of the writing is more complex.

These are some of the MySQL data segmentation concepts, data cut out, now what to do is to be integrated to facilitate access to the outside world, because the program access to the portal is always only one, now the more commonly used solution is through the intermediary agent layer to control all data sources uniformly.

MySQL Sub-Library sub-table tips