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

This article mainly introduces "what is the meaning of database sub-database and sub-table". In the daily operation, I believe that many people have doubts about the meaning of database sub-database and sub-table. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "what is the meaning of database sub-database and sub-table"! Next, please follow the editor to study!

Under what circumstances will it be dismantled

With the increasing amount of data, the current single database and data table has been unable to meet the huge amount of data. The index is under great pressure.

The throughput of the database reached a bottleneck.

It is hoped that when the capacity is expanded later, the configuration of the application layer will be changed least.

At this time, we need to redesign, one word, dismantle!

Second, common splitting techniques

The general idea of split: divide and rule

Horizontal split

Retain the original database (table) structure, split a library (table) into multiple libraries (tables). For example, the t_user table is split into t_user1 and t_user2.

Advantages:

The data of a single database (table) is kept within a certain order of magnitude to ensure performance.

The structure of syncopation is the same, and the code modification is less.

Improve the stability and load capacity of the system.

Piecewise latitude

Hash

The data are sliced evenly and the pressure is dispersed. However, the query needs to be aggregated.

time

It is suitable for those with obvious time characteristics. According to the different frequency of data access in different time periods, different hardware resources can be configured to save costs. It's also easy to file.

Vertical split

According to the latitude of the business, the original library (table) is divided into multiple libraries (tables), each of which is different from the original structure. For example, the t_user table is split into t_user_basic and t_user_detail.

Advantages:

Business logic is clearer

Static and dynamic separation, hot and cold separation

If there are many cold data queries, you can consider MyISAM engine; if there are many hot data updates, you can consider InnoDB engine.

If you read more and write less data, you can configure more slave libraries; for hot data, you can use multiple master libraries to build a sub-library and sub-table structure.

For special active data, you can consider using caches such as redis, and update the database when it accumulates to a certain amount of data.

Data maintenance is simple

Put the table on different machines according to the cost, the level of application and the type of application for easy management.

The disadvantage of unifying the two approaches:

Problems with distributed transactions

The problem with join

Sorting and paging across nodes

Management of multiple data sources

III. Implementation plan

Client slicing

Proxy layer slicing

Distributed databases supporting transactions (TiDB, OceanBase)

Fourth, the problems brought about

Expand capacity

Operation is tedious

When migrating data, the amount of data will lead to inconsistency. you should first clean the old data, then use the new rules after washing, and then make a full comparison.

Financial transaction data, separate the data from the movement. Historical data will not be updated, you can lengthen the double-write time window, after this window, directly migrate the historical data that will not be updated.

When the amount of data is large, sample comparison.

Query question

It is difficult to support multi-dimensional query, so you can do heterogeneous data redundancy. Or search engines.

The inquiry of the commodity information after the buyer places the order cannot be inconsistent with that before the order, and a snapshot needs to be made at this time.

Transaction problem

Cross-database problem of data in the same group

Try to put the same set of data on the same database server.

V. actual combat

In 2018 last year, in * Meishi, a logistics e-commerce project was done, in which the number of waybills per month had already been formed (millions) when the waybill was stored. Therefore, the order table is sliced according to time, and the primary key and complex query data are stored in es. In this way, when querying in the time dimension, go directly to DB, and in other query conditions, first go to es to query out the primary key, and then query DB according to the primary key information.

One advantage of creating tables in this way is that tables can be created and used indefinitely according to time.

At the same time, it will also bring disadvantages, when the business soars and the amount of data increases sharply, it may not be satisfied by month, so it is necessary to change the routing strategy.

The biggest downside is that this creates cold data problems, such as data that may no longer be accessed a year ago, two years ago, or more. In this case, you can use es or offline hive.

At this point, the study on "what is the meaning of database sub-database and sub-table" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.