Shulou(Shulou.com)06/01 Report--

This article shows you what the concept of database sub-database is, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The sub-database is widely used in projects with a large amount of data, and everyone has their own experience and experience. However, it is not so easy to systematically explain what is going on and what matters needing attention and problems. The following is mainly about the related concepts of sub-libraries.

1. What is data segmentation

To put it simply, it means that through some specific conditions, the data stored in the same database will be scattered to multiple databases (hosts), in order to achieve the effect of dispersing the load of a single device.

The Sharding of data is divided into two segmentation modes according to the type of segmentation rules. One is to split the data into different databases (hosts) according to different tables (or Schema), which can be called vertical (vertical) segmentation of data; the other is to split the data in the same table to multiple databases (hosts) according to certain conditions according to the logical relationship of the data in the table, which is called horizontal (horizontal) segmentation of data.

The most important feature of vertical segmentation is that the rules are simple and the implementation is more convenient, which is especially suitable for systems with very low coupling between businesses, little interaction and clear business logic. In this system, it is easy to split the tables used by different business modules into different databases. Splitting according to different tables will have less impact on the application, and the splitting rules will be relatively simple and clear.

Taking the database apart according to the business already belongs to the sub-database.

Horizontal segmentation is slightly more complex than vertical segmentation. Because you want to split different data in the same table into different databases, the split rule itself is more complex for the application than splitting based on the table name, and later data maintenance will be more complex.

Horizontal segmentation is estimated to be the sub-library in everyone's mind, right?

2. Vertical segmentation

A database is composed of many tables, and each table corresponds to a different business. Vertical sharding means that tables are classified and distributed to different databases according to business, so that the data or pressure is shared to different databases, as shown below:

The system is divided into several modules: user, order transaction and payment.

That's what everyone seems to do.

In the application system with better architecture design, the overall function is composed of many functional modules, and the data needed by each functional module corresponds to one or more tables in the database. In the architecture design, the more unified the interaction points between the functional modules are, the lower the coupling degree of the system is, and the better the maintainability and expansibility of the system modules are. In such a system, it is easier to realize the vertical segmentation of data.

This means high cohesion and low coupling.

However, it is often difficult for some tables in the system to be completely independent, and there is the case of expanding the library join. For this kind of tables, we need to balance whether the database concession business, share a data source, or be divided into multiple libraries, and the business is called through the interface. In the initial stage of the system, when the amount of data is relatively small, or the resources are limited, the shared data source will be chosen, but when the data develops to a certain scale and the load is very heavy, it is necessary to do segmentation.

That's why programmers always feel that their exes dig holes for themselves.

Generally speaking, it is difficult to segment a business where there is a complex join, and it is often easy to segment a business independently. How to split and to what extent is a difficult problem to test the technical architecture.

To tell you the truth, I have done less business, and I never thought of the problem of cross-database join before reading this article.

Advantages and disadvantages of vertical slicing:

Advantages:

After the split, the business is clear and the split rules are clear.

It is easy to integrate or expand between systems

Data maintenance is simple.

Disadvantages:

Some business tables can not be join, so they can only be solved by interface, which increases the complexity of the system.

Due to the different limitations of each business, there is a performance bottleneck of a single database, so it is not easy to expand data and improve performance.

Transaction processing is complex. Because vertical splitting divides tables into different databases according to business classification, some business tables are too large, and there are bottlenecks in reading, writing and storage of a single database, so horizontal splitting is needed to solve the problem.

Will vertical segmentation be troublesome in the business of strong transactions such as payment?

III. Horizontal segmentation

As opposed to vertical split, horizontal split does not classify tables, but is dispersed into multiple libraries according to certain rules of a field, each table containing part of the data. To put it simply, the horizontal segmentation of data can be understood as the segmentation of data rows, that is, some rows in the table are split into a database, while other rows are split into other databases, as shown in the figure:

To split the data, you need to define sharding rules. Relational database is a two-dimensional model of rows and rows, and the first principle of splitting is to find the split dimension. For example, from a member's point of view, if a merchant order trading system queries a member's order in a certain day or month, then it needs to be split according to the member's union date, and the different data is grouped according to the member's ID, so that all data queries join will be solved in a single database; if from the merchant's point of view, to query all orders of a merchant on a certain day, you need to split according to the merchant's ID However, if the system wants to split by member, but also by business data, there will be some difficulties. How to find the appropriate slicing rules needs to be comprehensively considered.

This point is a good indicator of the level of programmers. Wechat searches "architect's Road", and there are several articles on this official account that introduce the skills in this area.

Several typical slicing rules include:

According to the user ID model, the data is distributed to different databases, and the data of users with the same data are scattered into one database.

Spread the data from different months or even days into different databases according to the date

When I first worked, a project was divided into libraries according to time. At that time, I thought it was very low, but it was actually simple and effective.

Touch according to a specific field, or spread to different libraries according to a specific range of segments.

A colleague asked how to divide the existing clue table into a database, and the clue table may be queried by two or three dimensional fields. I think we can extract some features of these two or three dimensional fields, generate a sub-library field, and divide the library through this generated sub-library field.

As shown in the figure, the sharding principle is to find suitable sharding rules and distribute them to different libraries according to the business. Here is an example of user ID:

Now that the data has been split, there are advantages and disadvantages.

Advantages:

The splitting rules are abstract, and the join operation can basically be done in the database.

There is no performance bottleneck of single library big data and high concurrency.

There is less modification on the application side.

The stability and load capacity of the system are improved.

Disadvantages:

Split rules are difficult to abstract

Putting aside the business and talking about the structure is a hooligan.

The consistency of fragment transaction is difficult to solve.

It is difficult to expand the data many times and has a great amount of maintenance.

There is a way to double the capacity of this problem, and it can be more efficient without relying on middleware such as mycat.

Cross-library join has poor performance.

Fourth, common shortcomings of data segmentation

Talking about the differences and advantages and disadvantages between vertical segmentation and horizontal segmentation, we will find that each segmentation method has its shortcomings, but the common characteristics and disadvantages are:

The problem of introducing distributed transactions

The problem of cross-node Join

Cross-node merge sorting paging problem

Multi-data source management problems.

The above content is what is the concept of database sub-database, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.