Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the skills of sub-database and sub-table". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Sub-database sub-table

With the continuous development of business, when a single database and a single table can not carry the overall data storage, it is a storage scheme to store the whole data in different databases on different servers. Sub-database and sub-table can effectively alleviate the pressure of data storage, and sub-database and sub-table is a problem that will inevitably be encountered when the data storage reaches a certain scale. Mastering the ideas and skills of sub-database and sub-table will help friends to better solve the problem of data splitting in practical work.

Next, we will talk about some ideas and techniques for sub-tables and sub-libraries respectively.

Sub-table

Sub-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 put into different libraries. Of course, the first thing you need to know is when you need a sub-table. Personally, I think a sub-table should be used when the number of records in a single table reaches the level of 1 million to 10 million.

Classification of subtables

1. Vertical subtable

Artificially divide the contents that could have been in the same table into multiple tables. (the so-called original means that it should be in the same table according to the requirements of the third paradigm of relational databases.)

Sub-table technique: separate according to the activity of the data (because different active data are processed differently)

Case study:

For a blog system, article title, author, classification, creation time, etc., the frequency of change is slow, the number of queries is large, and it is better to have good real-time data, we call it cold data. On the other hand, the number of views and responses of the blog, similar statistics, or other data that changes frequently, we call it active data. Therefore, in the database structure design, we should consider the sub-table, the first is the vertical sub-table processing.

After dividing the table vertically in this way:

(1) first of all, the use of storage engine is different, cold data can have better query data using MyIsam. Active data, you can use Innodb, you can have a better update speed.

(2) secondly, the cold data is configured more from the database, because more operations are queried, so as to speed up the query speed. For thermal data, there can be more horizontal table processing of 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 until a certain amount is accumulated. Or a nosql database such as mongodb, which is just an example, not to mention this.

two。 Horizontal subtable

Literally, it can be seen that the large table structure is horizontally cut into different tables with the same structure, such as user information table, user_1,user_2 and so on. The table structure is exactly the same, however, tables are partitioned according to certain rules, such as modular partitioning based on user ID.

Sub-table skills: divide according to the scale of the amount of data to ensure that the capacity of the single table will not be too large, so as to ensure the query and other processing capabilities of the single table.

Case study:

The same as the example above, the blog system. When the number of blogs reaches a large amount, 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 there are 1 million users browsing at the same time, if it is a single table, there will be 1 million requests, and now after the sub-table, it is possible for each table to make requests for 10, 000 data (because, it is impossible to absolutely average, just assume), so the pressure is reduced a lot.

Note: database replication can solve the access problem, but can not solve the large-scale concurrent write problem. To solve this problem, we should consider MySQL data segmentation.

Data segmentation

As the name implies, the data is distributed and the data on one host is distributed to more than one, so as to reduce the load on a single host. There are two ways of segmentation, one is to divide libraries according to business modules, the tables in each library are different, and the other is to split the data into different hosts according to certain business rules or logic, and the tables on each host are the same. This is somewhat similar to Oracle's table partition.

Zoning

Sub-library, also known as vertical partition, this way is relatively simple to achieve, it is important to refine the business, sub-library to think clearly about the interaction between the various module business, to avoid excessive cross-library read and write operations when writing programs in the future.

Sub-table is also called horizontal partition, which is more complicated than vertical partition, but it can solve the problem that vertical partition can not solve, that is, a single table is accessed and written frequently, so it can be divided according to certain business rules (PS: for example, the concept of membership level of Internet BBS Forum, dividing tables according to membership levels), so that the pressure on single table can be reduced. And it can also solve the problem of frequent interaction between each module.

The advantages of sub-database are: easy to implement, clear boundaries between the library and the library, easy to maintain, the disadvantage is not conducive to frequent cross-database operation, can not solve the problem of large amount of data in a single table.

The advantage of sub-table is that it can solve the deficiency of sub-database, but the disadvantage is precisely the advantage of sub-database, and the realization of sub-table is more complex, especially the division of sub-table rules, the preparation of program, and the later database split, transplantation and maintenance.

Practical application

In practical application, the route of general Internet enterprises is to divide the library first and then the table, and the two are used together to learn from each other, which gives full play to the greatest advantages of MySQL expansion, but the disadvantage is that the architecture is very large and complex, and the writing of the application program is also more complex.

These are some of the concepts of data segmentation in MySQL. After the data is cut, what we need to do now is how to integrate them to facilitate external access, because there is always only one entry for program access, and now the more common solution is to manage and control all data sources through the intermediate proxy layer. For example, you can use glacier deep participation in the development of Mycat middleware, or you can use bright open source ShardingSphere middleware.

This is the end of the content of "what are the skills of sub-database and sub-table". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.