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

This article mainly explains "how to use database middleware Mycat+SpringBoot to complete sub-database sub-table", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in-depth, together to study and learn "how to use database middleware Mycat+SpringBoot to complete sub-database sub-table" bar!

I. background

With the development of time and business, the growth of data in the database is uncontrollable, and the data in the database and tables will become larger and larger, which will bring higher disk, IO, system overhead, and even performance bottlenecks. After all, the resources of a service are limited, so it is necessary to split the database and tables to better provide data services.

When the user table reaches the level of 10 million, it will be very difficult to do a lot of operations, so when the data grows to more than 10 million, we need sub-database and sub-table to alleviate the pressure of single database (table).

2. What is the sub-database and sub-table [1]

To put it simply, it means that through some specific conditions, the data we store in the same database will be distributed to multiple databases (hosts), so as to achieve the effect of dispersing the load of a single device.

The Sharding of data can be divided into two segmentation modes according to the type of segmentation rules. One is to split the data into different databases (hosts) according to different tables (or Schema), which can be called vertical (vertical) segmentation of data; the other is to split the data in the same table to multiple databases (hosts) according to certain conditions according to the logical relationship of the data in the table, which is called horizontal (horizontal) segmentation of data.

The most important feature of vertical segmentation is that the rules are simple and the implementation is more convenient, which is especially suitable for systems with very low coupling between businesses, little interaction and clear business logic. In this system, it is easy to split the tables used by different business modules into different databases. Splitting according to different tables will have less impact on the application, and the splitting rules will be relatively simple and clear.

Horizontal segmentation is slightly more complex than vertical segmentation. Because you want to split different data in the same table into different databases, the split rule itself is more complex for the application than splitting based on the table name, and later data maintenance will be more complex.

III. Vertical segmentation [1]

A database consists of many tables, and each table corresponds to a different business. Vertical sharding means that tables are classified and distributed to different databases according to business, so that the data or pressure is shared to different databases, as shown below:

The system is divided into several modules, user, order transaction, and payment. A well-designed application system, its overall function must be composed of many functional modules, and the data needed by each functional module corresponds to one or more tables in the database. In the architecture design, the more unified the interaction points between the functional modules are, the lower the coupling degree of the system is, and the better the maintainability and expansibility of the system modules are. In such a system, it is easier to realize the vertical segmentation of data.

However, it is often difficult for some tables in the system to be completely independent, and there is the case of expanding the library join. For this kind of tables, we need to balance whether the database concession business, share a data source, or be divided into multiple libraries, and the business is called through the interface. In the initial stage of the system, when the amount of data is relatively small, or the resources are limited, the shared data source will be chosen, but when the data develops to a certain scale and the load is very heavy, it is necessary to do segmentation.

Generally speaking, it is difficult to segment a business where there is a complex join, and it is often easy to segment a business independently. How to split and to what extent is a difficult problem to test the technical architecture. Let's analyze the advantages and disadvantages of vertical segmentation:

Advantages:

After the split, the business is clear and the split rules are clear.

It is easy to integrate or expand between systems

Data maintenance is simple.

Disadvantages:

Some business tables can not be join, so they can only be solved by interface, which increases the complexity of the system.

Due to the different limitations of each business, there is a performance bottleneck of a single database, so it is not easy to expand data and improve performance.

Transaction processing is complex.

Because vertical splitting divides tables into different databases according to business classification, some business tables are too large, and there are bottlenecks in reading, writing and storage of a single database, so horizontal splitting is needed to solve the problem.

IV. Horizontal segmentation [1]

As opposed to vertical split, horizontal split does not classify tables, but is dispersed into multiple libraries according to certain rules of a field, each table containing part of the data. To put it simply, we can understand the horizontal segmentation of data as the segmentation of data rows, that is, some rows in the table are split into a database, while other rows are split into other databases, as shown in the figure:

To split the data, you need to define sharding rules. Relational database is a two-dimensional model of rows and rows, and the first principle of splitting is to find the split dimension. For example, from a member's point of view, if a merchant order trading system queries a member's order in a certain day or month, then it needs to be split according to the member's union date, and the different data is grouped according to the member's ID, so that all data queries join will be solved in a single database; if from the merchant's point of view, to query all orders of a merchant on a certain day, you need to split according to the merchant's ID However, if the system wants to split by member, but also by business data, there will be some difficulties. How to find the right slicing rules needs to be considered comprehensively. Several typical slicing rules include:

According to the user ID model, the data is distributed to different databases, and the data of users with the same data are scattered into one database.

Spread the data from different months or even days into different databases according to the date

Touch according to a specific field, or spread to different libraries according to a specific range of segments.

As shown in the figure, the segmentation principle is to find suitable segmentation rules and distribute them to different libraries according to the business. The following example is given by using the user ID

Now that the data has been split, there are advantages and disadvantages.

Advantages:

The splitting rules are abstract, and the join operation can basically be done in the database.

There is no performance bottleneck of single library big data and high concurrency.

There is less modification on the application side.

The stability and load capacity of the system are improved.

Disadvantages:

Split rules are difficult to abstract

The consistency of fragment transaction is difficult to solve.

It is difficult to expand the data many times and has a great amount of maintenance.

Poor performance of cross-library join

What is Mycat

It is an open source distributed database system and a Server that implements MySQL protocol. Front-end users can regard it as a database agent, which can be accessed by MySQL client tools and command line, while its back-end can communicate with multiple MySQL servers using MySQL native (Native) protocol, or with most mainstream database servers using JDBC protocol. Its core function is to divide tables and libraries. Split a large table horizontally into N small tables and store them in a back-end MySQL server or other database.

Common application scenarios:

Simple read-write separation, which is the easiest to configure. It supports read-write separation and master-slave switching.

Sub-table and sub-library. For more than 10 million tables, a maximum of 100 billion of single-table slices are supported.

Multi-tenant applications, each application has a library, but the application only connects to Mycat, so that the program itself is not modified and multi-tenancy is realized.

The report system handles the statistics of large-scale reports with the help of the sub-table ability of Mycat; replaces Hbase to analyze big data

As a simple and effective scheme for real-time query of massive data, for example, 10 billion frequently queried records need to query results within 3 seconds. In addition to primary key-based queries, there may also be range queries or other attribute queries. At this time, Mycat may be the most simple and effective choice.

6. The case of realizing sub-table and sub-database by SpringBoot+Mycat+MySQL.

With regard to sub-database and sub-table, Mycat has helped us implement the routing function internally. We only need to configure the following sharding rules in Mycat. For developers, we can treat Mycat as a database, and then we start to build the environment:

Step 1:

Mycat is a database middleware written in java, so it is necessary to prepare an environment for jdk before running Mycat, which requires an environment above jdk1.7. So you need to configure the environment variables of JAVA_HOME in the system.

Step 2:

Download Mycat, http://dl.mycat.io/1.6-RELEASE/ from the official website. We build the Mycat environment based on CentOS7, so download version: Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

Step 3:

Upload the downloaded installation package to the server and extract it. After decompression, the directory structure is as follows:

Step 4:

Configure the sharding rule: copy and paste the following configuration to overwrite the contents of the mycat/conf/schema.xml.

Select user ()

: represents the logical library configuration in mycat, and the logical library name is: TESTDB

Indicates the logical table configuration in mycat. The logical table name is: user, which is mapped to two database nodes dataNode. The sharding rule is: rule1 (in rule.xml configuration)

Represents a database node, which is not necessarily a single node and can be configured for read-write separation.

Address configuration of the real database

User heartbeat detection

Write the configuration of the library

Copy and paste the following configuration to overwrite the contents of mycat/conf/rule.xml.

What is defined here is the sharding rule, which is split according to the id column, and the sharding rule is modeled. 2: we have split multiple libraries (tables) here, which needs to be the same as the number of dataNode in the previous configuration, otherwise an error will occur.

Step 5:

Create two databases in the database, db01,db02. The following table creation statements are executed in each library:

CREATE TABLE `user` (`id` bigint (20) NOT NULL, `name` varchar (255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Step 6:

Start mycat and execute mycat/bin/startup_nowrap.sh

Step 7:

The project has been uploaded to github https://github.com/javalanxiongwei/springboot-mycat to build the SpringBoot environment, and the insert statement .application.properties is configured as follows:

Step 8:

Test: enter: http://localhost:8080/user/save?id=1&name=tomhttp://localhost:8080/user/save?id=2&name=jack in the address bar to view the database and find that the data with an id of 1 is inserted into the user table in the database db02. Data with an id of 2 is inserted into the user table in the database db01. Enter: http://localhost:8080/user/list in the address bar to see the two records you just inserted.

Well, at this point, we have completed the table and database.

Thank you for your reading, the above is "how to use database middleware Mycat+SpringBoot to complete sub-database sub-table" content, after the study of this article, I believe you on how to use database middleware Mycat+SpringBoot to complete sub-database sub-table this problem has a deeper understanding, 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.