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

This article mainly explains "what is the separation of read and write, vertical split, horizontal split and split table of the database". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "database read-write separation, vertical split, horizontal split, sub-database table is what" bar!

1. Preface

I believe that you are often confused by the terms "read-write separation, vertical split, horizontal split, and sub-database table".

two。 Separation of reading and writing

This is relatively easy to understand, that is, the database is divided into master and slave libraries, one master library (Master) is used to write data, and multiple slave libraries (Slaver) are used to poll and read data. The master and slave libraries synchronize data through some communication mechanism, which is a common database architecture. The following picture shows the structure of "one master and two slaves":

2.1 Why should read and write be separated?

Most Internet data operations tend to read more and write less. With the growth of data, the "read" of the database will first become a bottleneck. If we want to improve the read and write performance of the database linearly, we need to keep the read and write from each other as little as possible. Before using read-write separation, we should consider whether using caching can solve the problem. Then consider grouping databases according to "read" and "write". Read-write separation means to disperse the integrated structure, and the following problems should be considered in scenarios with large amount of data and high concurrency

How to ensure the high availability of Master, fault transfer, fuse current limit and so on.

The distinguishing rules of read and write operations, how to deal with read commands and write commands at the code level, and try not to perceive no business intrusion.

Tolerance of data consistency. Although it is data synchronization, it is still a problem that can not be ignored because of the uncertainty of the network.

3. Sub-library

Vertical split of database and horizontal split of database are collectively referred to as sub-database. It refers to splitting the data in the same database into multiple databases (hosts) according to specific conditions and dimensions in order to achieve the effect of dispersing the load of a single database (host). In this way, we reduce the size of the dataset in disguise and trade space for time to improve performance.

3.1 Vertical split of database

Vertical splitting of the database refers to grouping the tables in the database according to the business and putting the tables in the same group into a new database (logically, not an instance). It is necessary to divide the large business into small business based on the actual business. For example, the user-related tables, order-related tables and logistics-related tables in the whole business of the mall are independently classified to form the user system database, the order system database, and the logistics system database as shown below:

This brings some benefits: (a) clear business, single responsibility (b) easy to maintain, easy to expand (c) data service. At the same time, there are some negative effects: (a) increase the complexity of the whole application, and form cross-database transactions (b) cause "bucket effect", any deficiency may affect the whole system (c) part of the table relationship can not be maintained only through service transfer. Even data inconsistencies are caused by network problems.

When there is a need for library splitting, vertical splitting is usually preferred.

3.2 horizontal split of the database

After the performance bottleneck of the stand-alone database is encountered after the vertical split of the database, you can consider the horizontal split of the database. The reason for the vertical split before the horizontal split is that after the vertical split, the data service is clear and single, so it is more convenient to specify the horizontal standard. For example, the horizontal split of the user system after the vertical split of the mall business is easier to find the dimension than the horizontal split of the entire mall business. We can split the data according to the user registration time interval, the user's region or the user's ID range, hash and other conditions, and then associate the records of the relevant tables. If you put it in the whole mall business, it is not good to consider whether you are based on the user or the order.

We split the user system level according to each 1 million interval as follows:

The advantages of this split are: (a) the capacity of a single library is controllable; (b) single-entry records ensure data integrity; (c) data relations can be maintained through join; (d) cross-database transactions are avoided; there are also disadvantages: (a) splitting rules have a certain impact on coding (b) partition interactions between different businesses need to be designed as a whole.

4. Sub-table

The sub-table is also divided into vertical split and horizontal split of the data table.

4.1 Vertical split of data table

Vertical splitting of the data table is to divide the columns in the table into multiple tables longitudinally, changing the table from "wide" to "narrow". The following points are generally followed for splitting:

Hot and cold separation, put the commonly used columns in a table, less commonly put in a table.

Large field columns are stored independently

The columns of the associated relationship are put together closely together.

We separate the commonly used and uncommonly used large fields in the user table into two tables:

4.2 horizontal split of the data sheet

The horizontal split of the table feels the same as the horizontal split of the library, but the granularity is different. The table structure remains unchanged. In other words, the union of the dataset after the split is equal to the dataset before the split. After understanding Chapter 3.2, there is nothing more to say about this.

At this point, I believe that everyone has a deeper understanding of "database read-write separation, vertical split, horizontal split, sub-database sub-table", might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.