Mysql creates sub-database and sub-table

Sub-library and sub-table

1. Horizontal sub-database

Concept: Based on the field, according to a certain strategy (hash, range, etc.), the data in one database is split into multiple databases.

result:

-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 full data;

Scenario: The absolute amount of concurrency in the system has increased, and the sub-metering is difficult to solve the problem fundamentally, and there is no obvious business attribution to vertical sub-database.

Analysis: With more libraries, the pressure on io and cpu can naturally be relieved exponentially.

2. Level score table

Concept: Based on the field, according to a certain strategy (hash, range, etc.), the data in one table is split into multiple tables.

result:

-The structure of each table is the same;

-The data of each table is different, there is no intersection;

-The union of all tables is full data;

Scenario: The absolute concurrency of the system has not risen, but the amount of data in a single table is too much, which affects the efficiency of SQL, increases the burden on the CPU, and becomes a bottleneck.

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

3. Vertical sub-library

Concept: Based on the table, different tables are split into different libraries according to different business attributions.

result:

-The structure of each library is different;

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

-The union of all libraries is full data;

Scenario: The absolute concurrency of the system is up, and separate business modules can be abstracted.

Analysis: At this point, it can basically be servicing. For example, with the development of business, there are more and more common configuration tables, dictionary tables, etc. At this time, these tables can be split into separate libraries, or even service-oriented. Furthermore, with the development of the business, a set of business models has been incubated. At this time, the related tables can be separated into a separate library, or even service-oriented.

4. Vertical sub-table

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

result:

-The structure of each table is different;

-The data of each table is different. Generally speaking, the fields of each table have at least one column of intersection, usually the primary key, which is used to associate data;

-The union of all tables is full data;

Scenario: The absolute concurrency of the system has not come up, the table has not many records, but there are many fields, and the hot data and non-hot data are together, and the storage space required for a single row of data is large. As a result, the number of data rows cached by the database is reduced, and a large amount of random read IO will be generated when querying the disk data, resulting in an IO bottleneck.

Analysis: You can use the list page and the details page to help understand. The principle of splitting the vertical table is to put the hot data (the data that may be redundant and often queried together) together as the main table, and the non-hot data together as the extended table. In this way, more hot data can be cached, thereby reducing random read IO. After dismantling, if you want to get all the data, you need to associate two tables to fetch the data.

But remember, don't use join, because join will not only increase the CPU burden but also talk about two tables coupled together (must be on a database instance). Associated data, you should make a fuss about the business service layer, obtain the main table and the extended table data separately, and then use the associated fields to associate all the data.