MySQL-Sharing of tips for sub-database and table sub-databases

Table sharding is a good way to spread the pressure on the database.

The most straightforward meaning is to divide a table structure into multiple tables, and then they can be placed in the same library or in different libraries.
Of course, you must first know under what circumstances you need to divide the table. Personally, I think that when the number of records in a single table reaches millions to tens of millions, it is necessary to use sub-tables.
1. Classification of sub-tables
1>Vertical sub-tables
Artificially divide the content that could be in the same table into multiple tables. (The so-called original means that according to the requirements of the third paradigm of relational database, they should be in the same table.)
Reason for splitting tables: Separate according to the activity of the data, (because of different active data, processing methods are different)
Case:
For a blog system, the article title, author, classification, creation time, etc., the frequency of changes is slow, the number of queries is high, and it is best to have good real-time data, we Call it cold data. As for blog views, number of replies, similar statistical information, or other data that changes with a relatively high frequency, we call it active data. Therefore, when designing the database structure, you should consider table partitioning. The first is the processing of vertical table partitioning.
After dividing the table vertically like this:
First of all, the storage engine is used differently. Using MyIsam for cold data can provide better query data. For active data, you can use Innodb, which can have better update speed.
Secondly, configure more slave databases for cold data, because more operations are queried, thus speeding up the query speed. For hot data, there can be relatively more horizontal sub-table processing in the main database.
In fact, for some special active data, you can also consider using caches such as memcache and redis, and then update the database when the accumulation reaches a certain amount. Or a nosql database such as mongodb. This is just an example, so I won’t talk about this first.
2>Horizontal table splitting
The literal meaning, as you can see, is to cut a large table structure horizontally into different tables with the same structure, such as user information table, user_1, user_2, etc. The table structure is exactly the same, but the table is divided according to some specific rules, such as modular division based on user ID.
Reason for dividing tables: Divide according to the size of the data volume to ensure that the capacity of a single table is not too large, thereby ensuring the query and other processing capabilities of a single table.
Case: Same as the above example, blog system. When the volume of blogs reaches a large level, horizontal segmentation should be adopted to reduce the pressure on each single table and improve performance. For example, if the cold data table of a blog is divided into 100 tables, when 1 million users are browsing at the same time, if it is a single table, 1 million requests will be made. But now after the tables are divided, it may be for each table. Make 10,000 data requests (because it is impossible to get an absolute average, just an assumption), so the pressure will be reduced a lot.

Database replication can solve the access problem, but it cannot solve the large-scale concurrent writing problem. To solve this problem, we must consider mysql data segmentation.

Data segmentation, as the name suggests, is Data is dispersed, and the data on one host is distributed to multiple hosts to reduce the load pressure on a single host. There are two ways to segment the data. One is to divide the database into multiple databases according to the business module. Each database has The tables are different. Another method is to split the data into different hosts according to certain business rules or logic. The tables on each host are the same. This is somewhat similar to Oracle's table partitioning.

Sub-library is also called vertical partitioning. This method is relatively simple to implement. The important thing is to refine the business. When sub-library, you must think clearly about the interaction between the business modules of each module to avoid writing programs in the future. There are too many cross-database operations.

Table partitioning is also called horizontal partitioning. This method is more complicated to implement than vertical partitioning, but it can solve the problem that vertical partitioning cannot solve, that is, the access and writing of a single table are very frequent. At that time, the tables can be divided according to certain business rules (PS: For example, the membership level concept of the Internet BBS forum: tables are divided according to the membership level). This can reduce the pressure on a single table and solve the frequent conflicts between various modules. Interaction issues.

The advantages of sub-database are: simple implementation, clear boundaries between libraries, and easy maintenance. The disadvantage is that it is not conducive to frequent cross-database operations, and the problem of large data volume in a single table cannot be solved.

The advantage of sub-table is that it can solve the shortcomings of sub-database, but the disadvantage is exactly the advantage of sub-database. The implementation of sub-table is more complicated, especially the division of table sub-rules, the writing of programs, and Later database split migration and maintenance.

In practical applications, the general route of Internet companies is to first divide the database and then divide the table. The two are used in combination to learn from each other's strengths. This gives full play to the greatest advantage of mysql expansion, but the disadvantage is that the architecture is large and complex. Writing applications is also more complicated.

The above is the detailed content of MySQL-Sharing of tips for sub-database and table sub-databases. For more information, please follow other related articles on the PHP Chinese website!