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

This article mainly explains the concept of "Mysql sub-library sub-table", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn the "Mysql sub-library sub-table concept" bar!

There are generally two ways to split the database and tables: vertical split and horizontal split. Next, we can take a look at these two ways of sub-database and sub-table:

Vertical split

There are two main types of vertical splitting: vertical split and vertical split.

Vertical sub-database: vertical sub-database is actually easier to understand logically. For example, at present, our database has user-related tables such as user tables, integral tables, real-name authentication tables, and order-related tables, such as commodity tables, group purchase tables, and commodity-related commodity tables. If all tables are placed in the same database, performance will definitely be affected due to the lack of database connections and disk IO. At this time, we can put user-related in the user database, merchandise-related in the goods database, order-related in the order database. Then different databases are stored on different servers, which can also avoid the impact of server bottlenecks on database performance due to the increasing number of users. So sub-library is actually building multiple different databases on multiple servers, and then storing different tables in different databases according to different business logic.

Vertical subtable:

The vertical sub-table is mainly used by hundreds of data tables with hundreds of data columns in the table. the solution is to create an auxiliary table, move the database with a large amount of data or less commonly used in the table to the auxiliary table, and leave the commonly used fields in the original table. in this way, we can avoid when we query large tables due to uncommonly used large fields affect the performance of the query.

Horizontal split

There are also two kinds of horizontal split: horizontal sub-table and horizontal sub-database sub-table.

Horizontal subtable:

The difference between a horizontal table and a vertical table is that the vertical table is for columns, splitting less commonly used columns into auxiliary tables. The horizontal table is for tables, creating multiple tables in the same database. For example, we create three order tables order1,order2,order3 in the order database, and then take the balance of id to 3 when we insert the order, and store it in different order tables according to different values. However, because the horizontal table stores the data table in the same database, the horizontal table can reduce the amount of data of a single standard and help to improve the query efficiency. If the business volume is very large, it will also be bottlenecked by the server IO, so we generally use horizontal sub-database tables more frequently.

Horizontal sub-database table:

Horizontal sub-database and sub-table is actually creating databases and data tables on several different machines, such as order tables. We can create order databases and order data tables on three different machines, and then store different data in different machine databases according to certain logic. Generally speaking, there are the following splitting rules for horizontal sub-database and sub-table:

Range splitting: for example, the order table is split by id, the id is in server a's order database at 1 to 100000, the id is stored in server b's order database at 100001 to 200000, and so on.

Geographic split: the order table splits the data in the table into order databases on different servers according to different regions. For example, it can be split by province.

Take the mold split: the module is just mentioned in the horizontal sub-table, for example, there are three order tables, you can take the balance of id to 3, but the difference is that the horizontal sub-database sub-table stores the same database and data tables on different machines, so it can effectively alleviate the bottleneck problem of a single machine.

Time split: split the data in the table according to time. You can split the data in the table that has been created for more than one year to the order database storage of other databases. Only the data with a high probability of query is retained in the original table, and this part of the data is often created recently, so hot and cold data can be separated by time split.

Zoning

What does zoning mean? Partitioning actually means that data from different rows in the same data table is recorded in different partitions, and each partition has an .idb file, so partitioning can help us split a data table into smaller parts. Mysql only supports local index partitions but not global partitions. Each partition of mysql stores both data and indexes. MySQL partitions are mainly divided into the following categories:

RANGE partitions: RANGE partitions are mainly used for partitioning date columns and are partitioned based on column values of consecutive intervals. RANGE partitioning is also the most commonly used partitioning method.

LIST partitioning: the values of LIST partitioned columns are hashed, and it is precisely because the values of partitioned columns are hashed that you need to manually define the values of partitioned columns.

HASH partitions: HASH partitions distribute data evenly to different partitions through a custom algorithm to ensure that the data in all partitions are roughly the same. HASH partitions only support integer partitions.

KEY partition: the key partition is partitioned using the database's native functions. KEY partitions can support data column partitions other than BLOB and TEXT columns.

Pay attention to the partition

The main thing to note about partitioning is that if the table to be partitioned has a primary key or unique key, the primary / unique key of the partitioned table must contain the partitioning key. The significance of partitioning is to divide a large table into several small tables according to the partition conditions, but it is still a table for data, which can improve the scalability and manageability of the large table, and improve the efficiency of the database.

Advantages of zoning

You can store more data than you can store it on a single disk.

For those invalid data, it is usually possible to delete the data by deleting the corresponding partition, which is more efficient than delete.

Some queries can be greatly optimized so that the data that satisfies a given WHERE statement can be saved in only one partition so that you don't have to look for other remaining partitions when looking.

Queries involving aggregate functions can be processed in parallel. This means that the aggregate function query can be performed on each partition at the same time, and the final result only needs to be obtained by summing the results of all partitions.

Problems caused by sub-database and sub-table

Sub-database and sub-table can remove the bottleneck of the database to a certain extent, but the inevitable sub-database and sub-table will bring some problems. There are mainly two problems: the difficulty of join operation and the problem of distributed transaction.

Join table query

At the beginning, it is very simple to use join join table operation in the case of single database and multiple tables. We can get the results we need by using a simple sql statement with join, but after dividing the database, we may not be able to complete a query. We may need to first go to server a to query user information, and then go to server b, c, d to query order information to complete our query function. Join table query function I think a more suitable logic is to query the data we need separately, and then the code is responsible for assembling all the data in the service layer, and then return it to the client, although it is more complex, but the performance of the database can be maximized.

Distributed transaction

After the sub-library operation, the transaction operation becomes a distributed transaction, and when talking about Redis before, we specifically mentioned excessive layout. We all know that the distributed logic involved is relatively complex, and if we use the distributed transaction management function that comes with Mysql, the performance will be greatly reduced, which is about 10% of the performance of a single library. So there are generally two kinds of solutions: using distributed transaction middleware such as Mycat or business logic in the code to control, but if controlled in the code, it may make the system more complex and less maintainable.

Thank you for your reading, the above is the content of "the concept of Mysql sub-library sub-table", after the study of this article, I believe you have a deeper understanding of the concept of Mysql sub-library sub-table, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.