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

This "MySQL sub-database sub-table case analysis" article of the knowledge points of most people do not understand, so the editor summed up the following content, detailed, clear steps, with a certain reference value, I hope you can get something after reading this article, let's take a look at this "MySQL sub-database sub-table example analysis" article.

First, why is it necessary to divide the database and table

Evolution of database architecture

At the beginning, it is enough for most projects to use a stand-alone database. With more and more server traffic and more and more requests, we have separated the read and write of the database, using multiple copies of the slave database (Slave) for reading and writing, and using the master database (Master) for writing. Master and slave synchronously update the data through master-slave replication to keep the data consistent. Slave can be scaled horizontally from the library, so more read requests are not a problem

But when the level of users rises and there are more and more write requests, how do you ensure that the database is loaded enough? Adding a Master will not solve the problem, because to preserve data consistency, write operations require synchronization between two master, which is equivalent to repetition, and the architecture design is more complex.

In this case, we need to use sharding to store the library and tables on different MySQL Server, and each server can balance the number of write requests.

Second, the problem caused by the large database table.

The single library is too large: the processing capacity of the single library is limited, the disk space on the server is insufficient, and the IO bottleneck is encountered, so it is necessary to split the single library into more and smaller libraries.

Single table is too large: the efficiency of CURD is very low, the amount of data is too large, the index file is too large, and it takes time for disk IO to load the index, resulting in query timeout. So you can't just use an index, you need to split a single table into tables with smaller datasets. The table splitting algorithms provided by MyCat are all in rule.xml, and can be split according to different table splitting algorithms, such as splitting according to time, consistent hash, directly using the primary key to take modules for the number of split tables, and so on.

Split strategy

If a single library is too large, first consider whether there are too many tables or data:

If there is too much data because there are too many tables, vertical split is used, that is, split into different libraries according to the business

If the amount of data in a single table is too large, horizontal split is used, that is, the data of the table is split into multiple tables according to some rule (the split table algorithm defined by rule.xml).

The principle of sub-database and sub-table should be to consider vertical split first, and then horizontal split.

III. Vertical split

Sub-library, sub-table and read-write separation can be carried out together.

1. Vertical sub-library

Server.xml

123456USERDB1,USERDB2

USERDB1 and USERDB2 logic libraries are configured.

Schema.xml

Select user () select user ()

Two logical libraries correspond to two different data nodes, and two data nodes correspond to two different physical machines

Mytest1 and mytest2 are divided into different libraries on different machines, each containing some tables that used to be grouped together, and are now split vertically on the same machine.

The client needs to connect to different logic libraries, different logic libraries according to business operations.

Then two write libraries are configured, and the two machines divide the library equally, sharing the pressure of the original single machine. The sub-library is accompanied by the sub-table, and the table is split from the business.

two。 Vertical subtable

Vertical subtable, based on column fields. Generally, it is aimed at this kind of large table with hundreds of columns, and it also avoids the "spread" problem caused by too much data when querying.

Generally speaking, there are more fields in the table, and the less commonly used ones with larger data and longer length (such as text type fields) are split into the extended table. The frequently accessed fields are placed in a separate table.

IV. Horizontal sub-database sub-table

For a single table with a large amount of data (such as order table), it is divided into multiple tables according to certain rules (RANGE, HASH, etc.). However, these tables are still in the same library, so database operations at the library level still have IO bottlenecks, which is not recommended.

Split the data of a single table into multiple servers, each server has a part of the library and table, but the data set in the table is different. Horizontal sub-library and sub-table can effectively alleviate the performance bottleneck and pressure of single machine and single library, and break through the bottleneck of IO, connection number, hardware resources and so on.

Sub-library and sub-table can be carried out at the same time as master-slave replication, but not based on master-slave replication; read-write separation is based on master-slave replication.

Server.xml

123456 USERDB

Schema.xml

Select user () select user ()

User represents an ordinary table, directly on the data node dn1, on a machine, without splitting the table.

The primaryKey of the student table is id, which is split according to id and placed on dn1 and dn2. In the end, the table is divided into two machines, physically separated, but logically it is still one, which table is added, query on the two machines and then how to merge these operations are done by mycat.

The rule of splitting is mod-long, and the number of machines present on the id module for each insertion (2)

In addition, the following splitting algorithm needs to be configured in rule.xml

Find the algorithm mod-long, because we map the logical table student to two hosts separately, so the number of modified data nodes is 2.

two。 Test level subtable

Linux host

Windows host

Log in to port 8066 of mycat

Use MyCat to insert two pieces of data into the user table

Because the logical table user exists only in the mytest1 library of the Linux host in the schema.xml configuration file, the logical table user operated by mycat affects the physical tables on the Linux host, but not the tables on the Windows host. Let's take a look at the user table of the Linux and Windows hosts respectively:

We insert two pieces of data into the student table through MyCat

We know that in the schema.xml configuration file, the logical table student corresponds to two tables in the two libraries mytest1 and mytest2 on the two hosts, so the two pieces of data inserted into the logical table will actually affect the two physical tables (use the number of id% machines to decide which physical table to insert). Let's take a look at the student table of the Linux and Windows hosts respectively:

Then insert id=3 and id=4 data through MyCat, and should insert different physical tables on different hosts.

This is equivalent to splitting the student table horizontally.

When querying through MyCat, you only need to input normally. What we configure is that the table is split and placed on these two data nodes, and MyCat will query and merge the data on the two databases according to the configuration.

The above is about the "MySQL sub-database sub-table case analysis" of this article, I believe we all have a certain understanding, I hope the editor to share the content to help you, if you want to know more related knowledge, please 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.