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

This article mainly shows you "what are MySQL's common sub-library and sub-table schemes". The content is simple and easy to understand, and the organization is clear. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn this article "what are MySQL's common sub-library and sub-table schemes".

I. Database bottleneck

Whether it is an IO bottleneck or a CPU bottleneck, it will eventually lead to an increase in the number of active connections in the database, which will approach or even reach the threshold of the number of active connections that the database can hold. In terms of Service, there are few or no available database connections. And then imagine (concurrency, throughput, crashes).

1. IO bottleneck

The first type: disk read IO bottleneck, too much hot data, database cache can not fit, each query will generate a large number of IO, reduce the query speed-> library and vertical table.

The second type: network IO bottleneck, too much data requested, insufficient network bandwidth-> sub-library.

2. CPU bottleneck

The first type: SQL problems, such as SQL contains join, group by, order by, non-index field conditional query, etc., add CPU operation-> SQL optimization, establish appropriate index, and perform business calculation at the business Service layer.

The second type: the amount of data in a single table is too large, too many rows are scanned during query, SQL efficiency is low, and CPU takes the lead in bottleneck-> horizontal tables.

II. Sub-warehouse and sub-table

1. Horizontal sub-library

Concept: Split the data in a library into multiple libraries according to a certain strategy (hash, range, etc.) based on fields.

Results:

The structure of each library is the same;

The data of each library is different, and there is no intersection;

The union of all libraries is the full amount of data;

Scenario: The absolute concurrency of the system has increased, and it is difficult to fundamentally solve the problem by table division, and there is no obvious business attribution to vertically divide the database.

Analysis: more libraries, io and cpu pressure can naturally be multiplied to ease.

2. Horizontal sub-table

Concept: Split the data in a table into multiple tables according to a certain strategy (hash, range, etc.) based on fields.

Results:

Each table has the same structure;

The data in each table is different and there is no intersection;

The union of all tables is the full amount of data;

Scenario: The absolute concurrency of the system has not increased, but the data volume of a single table is too large, which affects SQL efficiency and increases CPU burden, so that it becomes a bottleneck. Recommendation: An Analysis of SQL Query Optimization Principle

Analysis: The amount of data in the table is less, and the efficiency of single SQL execution is high, which naturally reduces the burden on the CPU.

3. Vertical library division

Concept: Split different tables into different libraries according to different business ownership based on tables.

Results:

The structure of each library is different;

The data of each library is also different, and there is no intersection;

The union of all libraries is the full amount of data;

Scenario: Absolute concurrency of the system is up, and individual business modules can be abstracted.

Analysis: At this point, it can basically be serviced.

For example, with the development of business, there are more and more common configuration tables and dictionary tables. At this time, these tables can be disassembled into separate libraries, or even serviced. Furthermore, as the business develops, a set of business patterns is hatched, and the related tables can be disassembled into separate libraries, or even serviced.

4. Vertical sub-table

Concept: Based on fields, the fields in a table are split into different tables (main table and extension table) according to the activity of the fields.

Results:

The structure of each table is different;

The data of each table is also different. Generally speaking, the fields of each table have at least one column intersection, which is usually a primary key for associating data.

The union of all tables is the full amount of data;

Scenario: The absolute concurrency of the system is not up yet. There are not many records in the table, but there are many fields. Moreover, hot data and non-hot data are together. The storage space required for a single line of data is large. As a result, the number of data lines cached in the database decreases, and a large number of random read IO will be generated when reading disk data during query, resulting in IO bottleneck.

Analysis: List pages and detail pages can be used to help understand. The principle of splitting vertical tables is to put hot data (data that may be redundant and often queried together) together as the main table and non-hot data together as the extended table. This allows more hot data to be cached, thereby reducing random read IO. After dismantling, in order to obtain all the data, you need to associate two tables to get the data.

But remember, never use join, because join not only increases CPU load but also couples two tables together (on a database instance). Associating data, you should make an issue of the business Service layer, obtain the data of the main table and the extended table respectively, and then use the association field to associate all the data.

III. Tools for dividing libraries and tables

sharding-sphere: jar, formerly sharding-jdbc;

TDDL：jar，Taobao Distribute Data Layer;

Mycat: Middleware.

Note: The advantages and disadvantages of tools, please do your own research, official website and community priority.

IV. Steps of dividing the warehouse into tables

Evaluate the number of sub-libraries or sub-tables according to capacity (current capacity and growth)-> select key (uniform)-> sub-table rules (hash or range, etc.)-> execute (general double writing)-> expansion problem (minimize data movement)

V. The problem of dividing warehouses and tables

1, non-partition key query problem

Based on the horizontal sub-library sub-table, the splitting strategy is the common hash method.

There is only one non-partition key as a conditional query except partition key on the terminal

mapping method

gene method

Note: When writing, the genetic method generates user_id, as shown in the figure. For xbit genes, for example, 8 tables should be divided, 23=8, so x takes 3, that is, 3 bit genes. When querying according to user_id, the module can be directly routed to the corresponding sub-library or sub-table.

When querying according to user_name, first generate user_name_code through user_name_code generation function, and then route it to the corresponding sub-library or sub-table. Id generation is commonly used snowflake algorithm.

There is more than one non-partition key on the terminal as a conditional query except partition key

mapping method

redundancy method

Note: When querying according to order_id or buyer_id, route to db_o_buyer library; when querying according to seller_id, route to db_o_seller library. It felt like putting the cart before the horse! Is there a better way? Change the technology stack?

In addition to partition key, there are various non-partition key combination condition queries in the background

NoSQL method

redundancy method

2. Non-partition key cross-database and cross-table paging query problem

Based on the horizontal sub-library sub-table, the splitting strategy is the common hash method.

Note: Use NoSQL method to solve (ES, etc.).

3. Expansion problem

Based on the horizontal sub-library sub-table, the splitting strategy is the common hash method.

Horizontal expansion library (upgrade from library method)

Note: Expansion is multiplied.

Horizontal expansion table (double-write transfer method)

Step 1:(synchronous double-write) modify the application configuration and code, plus double-write, deployment;

Step 2:(synchronous double-write) copy the old data in the old database to the new database;

Step 3:(synchronous double writing) proofread the old data in the new database based on the old database;

Step 4:(synchronous double-write) modify the application configuration and code, remove double-write, deploy;

Note: Double writing is a common scheme

The above is "MySQL common sub-library sub-table scheme what" all the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to 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.