Preface:
Today, we will talk about sharing-JDBC database and table middleware. This middleware belongs to the application layer dependent middleware, which is strongly coupled with the application layer. It needs to display Jar packages adding dependencies in the application.
After a brief talk about Sharding-JDBC, this article mainly discusses the process of SpringBoot integration sharding-JDBC;
The small Demo:
A Demo of SpringBoot integration with Sharding-JDBC was written specifically for this purpose, and this Demo also supports many other features;
Main line of this article:
- Talk about sharding-JDBC basics and considerations;
- Sharding-jdbc integration Demo introduction;
- Sharding-jdbc integration process;
Sharding-jdbc introduction
First post the official website, you can go to check: Sharding-JDBC introduction
1. Basic Concepts:
Sharding-jdbc is positioned as a lightweight Java framework that provides additional services in Java’s JDBC layer, so it is an application-layer dependent class middleware.
It uses the client directly connected to the database, in the form of JAR package to provide services, without additional deployment and dependence, can be understood as an enhanced VERSION of THE JDBC driver, fully compatible with JDBC and various ORM frameworks.
2. Compatibility:
- Works with any Java-based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
- Database connection pool based on any third party such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
- Support for any database that implements the JDBC specification. Currently supports MySQL, Oracle, SQLServer and PostgreSQL.
3. Architecture Diagram:
Image source: Sharding-JDBC official website
4. Data sharding:
When we divide database and table, we can’t get around the knowledge of data sharding.
Data sharding: Data stored in a single database is distributed to multiple databases or tables according to a certain dimension to improve performance bottlenecks and availability.
Data fragmentation can be divided into:
- Vertical fragmentation
- Horizontal sharding (most commonly used)
Vertical sharding:
The method of business function splitting is called vertical splitting, and its core idea is dedicated library.
For example: a library originally made up of order form and the user table, because the concurrency value and amount of data is too large, it had a library can be split, split into two libraries, library of an order, there is only one order list, a user repository, there was only a user table, so use two libraries can support a larger concurrency value, improve database concurrency bottlenecks.
Disadvantages:
Vertical sharding often requires architectural and design adjustments.
In general, vertical sharding is not fast enough to cope with the rapidly changing demands of the Internet business; And it doesn’t really solve a single point of bottleneck. Vertical split can alleviate the problems caused by data volume and visits, but it cannot cure them.
Horizontal sharding:
Horizontal sharding is also called horizontal splitting.
As opposed to vertical sharding, it no longer categorizes data according to business logic. Instead, it divides data into multiple libraries or tables according to certain rules through a certain field (or fields), with each shard containing only a portion of the data.
Note: Horizontal sharding breaks through the bottleneck of single machine data processing theoretically, and expands relative freedom. It is the standard solution of database and table.
For example, the sub-library sub-table implemented in this Demo is the horizontal shard used.
Fragments are performed according to the user name field in the user table. When adding user data, determine the database and table to which the data of the user name is added according to the sharding policy (including the sharding algorithm) configured.
5. Sharding algorithm:
Data fragmentation splits data according to a certain dimension. The sharding algorithm (method) refers to how to route SQL to specific data nodes after the database and table are divided.
Common sharding algorithm:
- Hash way
- Consistent hash
- By data range
For any sharding algorithm to consider the following questions:
- Does it support dynamic capacity expansion and dynamic addition of database node machines?
- When the database of a node goes down, can the data loss be minimized and can the tasks on this node be evenly distributed to other nodes?
These three kinds of specific sharding algorithm will not be introduced in this article, we can through this article with the problem of learning distributed system data sharding for detailed understanding;
Note: The sharding algorithm used in this Demo is the consistent hash algorithm, which can meet the above two requirements; This algorithm can be specifically referred to the vernacular analysis: Consistent hashing algorithm for consistent hashing;
6. SQL execution process:
Simply describe the execution process of the internal core of SQL in the project in Sharding-JDBC:
SQL parsing => Executor optimization => SQL routing => SQL rewriting => SQL execution => Result merge
The following describes the concepts of SQL routing and SQL rewriting:
- SQL routing: matches the sharding policy configured by the user according to the parsing context and generates the final routing path.
- SQL rewriting: Rewriting SQL to statements that can be executed correctly in a real database node;
Execution flow chart:
Image source: Sharding-JDBC official website
Two, Demo introduction:
1. Project Catalog:
Note: in this Demo, for example, some functions in the configuration file add limit switch, may not be enabled by default, if you want to use it, you need to set in the configuration file, turn on the switch;
For example, the Quartz scheduled task is disabled by default.
2. Engineering Environment:
2.1, pom. XML:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.6. RELEASE</version>
<relativePath/>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>1.7</java.version>
<mybatis-spring-boot>1.2.0</mybatis-spring-boot>
<mysql-connector>5.1.39</mysql-connector>
<fastjson>1.2.41</fastjson>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
<version>2.2.1. RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<! -- Spring Boot Mybatis
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<! MySQL connection driver dependency -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<! --druid connection pool -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.16</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson}</version>
</dependency>
<! --swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<! -- PageHelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
<! -- sharding-jdbc -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0 - RC1</version>
</dependency>
<! -- Hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-setting</artifactId>
<version>5.2.4</version>
</dependency>
<! -- Quartz timed task -->
<dependency>
<groupId>org.quartz-scheduler</groupId>
<artifactId>quartz</artifactId>
<version>2.2.1</version>
</dependency>
</dependencies>
Copy the codeSuggestion: It is better not to change the versions of dependencies in POM.xml, because if some dependency versions in pom.xml are changed, it may cause compatibility problems with dependency versions and ultimately lead to program failure.
2.2 SQL Environment:
The Mysql database used in this Demo, the procedure is to run before the need to configure the database in advance;
- Since the T_USER table is partitioned, two libraries need to be created: database names springboot0 and SpringbooT1
- And also the T_user table is divided into t_user0, T_user1, T_user2 three tables
SQL > create table from springBoot0;
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(128) NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_user0
-- ----------------------------
DROP TABLE IF EXISTS `t_user0`;
CREATE TABLE `t_user0` (
`id` int(65) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(10) DEFAULT NULL COMMENT 'name',
`age` int(2) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_user1
-- ----------------------------
DROP TABLE IF EXISTS `t_user1`;
CREATE TABLE `t_user1` (
`id` int(65) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(10) DEFAULT NULL COMMENT 'name',
`age` int(2) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_user2
-- ----------------------------
DROP TABLE IF EXISTS `t_user2`;
CREATE TABLE `t_user2` (
`id` int(65) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(10) DEFAULT NULL COMMENT 'name',
`age` int(2) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;
Copy the codeSelect * from springbooT1; select * from springbooT1;
DROP TABLE IF EXISTS `t_user0`;
CREATE TABLE `t_user0` (
`id` int(65) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(10) DEFAULT NULL COMMENT 'name',
`age` int(2) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_user1
-- ----------------------------
DROP TABLE IF EXISTS `t_user1`;
CREATE TABLE `t_user1` (
`id` int(65) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(10) DEFAULT NULL COMMENT 'name',
`age` int(2) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_user2
-- ----------------------------
DROP TABLE IF EXISTS `t_user2`;
CREATE TABLE `t_user2` (
`id` int(65) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(10) DEFAULT NULL COMMENT 'name',
`age` int(2) DEFAULT NULL COMMENT 'age'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;
Copy the codeAfter introducing the basic information of the above Demo, we will introduce the main part of the Sharding-JDBC integration path.
Iii. Integration Process:
Sharding-jdbc configuration file
Here is the contents of the Sharding-JDBC configuration file;
Note:
In this Demo, only t_user table is divided into database and table, other tables are not divided into database and table, those tables are not divided into database and table is the default database setting;
There are two shard keys and three shard tables. The shard key is the name field in the T_USER table.
(The following configuration of the number of libraries and virtual nodes is mainly to achieve consistent hash algorithm for sharding.)
# Number of branches
sharding.datasource.count=2
# Number of virtual nodes
sharding.datasource.virtual.node.count=360
# virtual nodes map to physical nodes: for example, in this article, we fragment based on name, so we use the hash value of name to mod the number of virtual nodes.
If the remainder is in the range of 0-179, the data shard accesses the SpringBoot0 data source.
If the remainder is in the range 180-359, the data is fragmented to the SpringBooT1 data source; The following table works the same way.
sharding.datasource.virtual.node.count.rang=0-179180-359
# Number of sub-tables
sharding.table.count=3
# Table number of virtual nodes
sharding.table.virtual.node.count=360
Map virtual nodes to physical node scope
sharding.table.virtual.node.count.rang=0-119120-249250-359
The actual data source name
spring.shardingsphere.datasource.names=springboot0,springboot1
# data source
spring.shardingsphere.datasource.springboot0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.springboot0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.springboot0.url=jdbc:mysql://localhost:3306/springboot0? characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.springboot0.username=root
spring.shardingsphere.datasource.springboot0.password=root
spring.shardingsphere.datasource.springboot1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.springboot1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.springboot1.url=jdbc:mysql://localhost:3306/springboot1? characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.springboot1.username=root
spring.shardingsphere.datasource.springboot1.password=root
### Sharding strategy uses a custom sharding algorithm
## Actual data nodes, groovy syntax; The {0.. 1} refers to the number between 0 and 1, including 0, 1, which means there are two branches.
## and the concatenation after Springboot constitutes the actual data source name configured above
spring.shardingsphere.sharding.tables.t_user.actualDataNodes=springboot$->{0.. 1}.t_user$->{0.. 2}
## Shard key: name field
spring.shardingsphere.sharding.tables.t_user.databaseStrategy.standard.shardingColumn=name
## Custom sub-library algorithm
spring.shardingsphere.sharding.tables.t_user.databaseStrategy.standard.preciseAlgorithmClassName=com.lyl.algorithm.MyPreciseDBShardingAlgorithm
## Shard key: name field
spring.shardingsphere.sharding.tables.t_user.tableStrategy.standard.shardingColumn=name
## Custom sub-table algorithm
spring.shardingsphere.sharding.tables.t_user.tableStrategy.standard.preciseAlgorithmClassName=com.lyl.algorithm.MyPreciseTableShardingAlgorithm
Use springboot0 as the default data source; For example, the T_ROLE table in this article does not proceed
The default data source for the t_role table is Springboot0
spring.shardingsphere.sharding.default-data-source-name=springboot0
Print the database and statements executed
spring.shardingsphere.props.sql.show=true
Copy the codeIf you need to change the number of branches, or the number of branches, you also need to change the configuration file. For example: Change the number of branches from 2 to 3; The following configuration file contents need to be changed:
Original configuration:
sharding.datasource.count=2
sharding.datasource.virtual.node.count.rang=0-179.180-359
spring.shardingsphere.sharding.tables.t_user.actualDataNodes=springboot$->{0.1.}.t_user$->{0.2.}
Copy the codeModified configuration contents:
sharding.datasource.count=3
sharding.datasource.virtual.node.count.rang=0-119.120-249.250-359
spring.shardingsphere.sharding.tables.t_user.actualDataNodes=springboot$->{0.2.}.t_user$->{0.2.}
Copy the code2. Sharding algorithm:
After the introduction of the above data sharding situation, and then introduce the project in the custom data sharding algorithm;
2.1. User-defined sub-library algorithm:
2.2 custom table sorting algorithm:
3. Matters needing attention
In this project, only t_USER table is divided into database and table, while T_ROLE table is not divided into database and table. The addition, deletion and modification of T_ROLE table are different from the default data source springboot0.
Note: Sharding-JDBC does not support cross-library queries; When the use of joint query SQL, if there is a table in the joint query and not the table in the joint query, then there may be cross-library query, at this time, the data can not be queried; For example, in this Demo, if the t_user and T_ROLE tables are used for associated query, cross-library problems will occur.
Advice provided:
-
If the united query SQL must be used, all tables in the united query need to be divided into databases, and the selected shard key fields should be consistent, and the tables should be set as bound tables to improve the speed of the united query.
-
Generally, it is not recommended to use multi-table joint query after sub-database sub-table. Because the above problems may occur, it is recommended to use single-table query. When using single-table queries, you can use application layer code to consolidate the result sets of multiple single-table queries.
Why is it recommended to use a single table query:
-
Using a single table query can effectively reduce the database read and write pressure; Because in the use of multi-table associated query, database SQL parsing is more complex, and need to go through a lot of calculation to get the final result set and so on; Therefore, the database pressure is very heavy in the case of multi-table associated query. If the pressure on the database side is too large, it needs to be expanded, which involves data consistency and other problems, which is very troublesome.
-
When using a single table query, most of the stress is placed on the service application code. If the service application code is stressed, it can be as simple as deploying a cluster (multiple machines) + Nginx request forwarding to improve overall system throughput.
Since then the SpringBoot integration Sharding-JDBC road is over, this article due to the length of the problem, just give you a broad introduction, not detailed description;
But it doesn’t matter ah, you can go to [Muzilei] public number or directly scan the following TWO-DIMENSIONAL code, input JDBC to obtain the address of the Demo in this article, you can carefully read the code to understand, if you don’t understand or there are problems in the Demo code can leave a message to discuss!
♡ like + comment + forwarding yo
If this article is helpful to you, please wave your love to make a fortune of the little hand under the praise ah, your support is my continuous creation of power, thank you!
You can wechat search [Muzilei] public number, a large number of Java learning articles, you can have a look at yo!
