Recently, there has been a requirement to distinguish different results generated by different channels. For example, the results generated by the web, the mini-program, and the app. In fact, the table structure is completely the same; it just requires different distinctions.
After the experience, if it is not a large project, or if the importance of sharding is very high, it is not recommended to use sharding jdbc.
This thing has too many problems, the documentation is scarce, and there are many pitfalls.
For simple small projects, you can directly use the dynamic table name plugin of mybatis-plus.
Sharding#
The first thing that comes to mind is sharding. Most sharding is based on data volume due to the well-known single-table bottleneck of MySQL databases.
3 million in MySQL can easily handle it.
6 million data starts to lag, optimization can solve it (table structure, index design).
8 million to 10 million, even excellent DBAs will encounter bottlenecks.
Now the requirement is sharding based on business, so you need to set the sharding logic yourself.
Sharding-JDBC#
When talking about sharding, one cannot avoid SHARDING-JDBC.
It is positioned as a lightweight Java framework that provides additional services at the JDBC layer of Java. It connects directly to the database using the client and provides services in the form of a jar package, without requiring additional deployment and dependencies. It can be understood as an enhanced version of the JDBC driver, fully compatible with JDBC and various ORM frameworks.
The core functionality of Sharding-JDBC is data sharding and read-write separation. Through Sharding-JDBC, applications can transparently use JDBC to access multiple data sources that have been sharded and separated for reading and writing, without worrying about the number of data sources and how the data is distributed.
Install Dependencies#
To use Sharding-JDBC, you first need to add dependencies in the pom file.
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!--mysql driver-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>8.0.23</version>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<!-- sharding-jdbc sharding dependency-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
It is important to note that if you use druid, the dependency must be druid, and cannot be druid-spring-boot-starter. issues
Configuration File#
After installation, you need to declare the sharding configuration and rules in the configuration file.
# You need to add the following configuration to allow bean name overriding, mainly for later SQL operations on tables. MybatisPlus uses the class name as the table name.
spring.main.allow-bean-definition-overriding=true
# Sharding strategy, multiple data sources are separated by commas, e.g.: ds0,ds1
spring.shardingsphere.datasource.names=ds0
# Configure the data source content. The ds0 below is set above, so it needs to have the same name.
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/jk_test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
# The table's location in which data source (database) and which table. The inspection_result in tables.inspection_result indicates what the table name starts with.
# Configure which tables are specified for sharding. Here, inspection_result and inspection_result_home are specified.
spring.shardingsphere.sharding.tables.inspection_result.actual-data-nodes=ds0.inspection_result, ds0.inspection_result_home
# Specify the generation strategy for the primary key cid in the inspection_result table. SNOWFLAKE is the snowflake algorithm.
#spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# Table sharding strategy
spring.shardingsphere.sharding.tables.inspection_result.table-strategy.standard.sharding-column=id
# Based on business sharding, this is a custom sharding strategy.
spring.shardingsphere.sharding.tables.inspection_result.table-strategy.standard.precise-algorithm-class-name=com.jkkj.config.ResultPreciseShardingAlgorithm
# Enable SQL output logs
spring.shardingsphere.props.sql.show=true
Custom Sharding Strategy#
You can use a custom sharding strategy to meet business requirements.
public class ResultPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
Long id = preciseShardingValue.getValue();
if (id < 8000000) {
return "inspection_result";
} else {
return "inspection_result_home";
}
}
}
Just determine the returned table name based on the id.
Range Sharding#
The strategy.standard.precise we use represents the standard sharding strategy, which provides support for sharding operations in SQL statements using = and IN.
Think about it, the current sharding strategy is based on the size of the id. If you want to query data within a certain range, can it succeed?
The answer is no. Standard sharding does not support range queries, but range sharding can be used.
# Table sharding strategy
spring.shardingsphere.sharding.tables.inspection_result.table-strategy.standard.sharding-column=id
# Based on business sharding, this is a custom sharding strategy.
# standard.precise-algorithm under the standard strategy includes 2 precise + range; range is optional, but if range is used, it must be used together with precise.
# Precise sharding algorithm class name, used for = and IN.
spring.shardingsphere.sharding.tables.inspection_result.table-strategy.standard.precise-algorithm-class-name=com.jkkj.config.ResultPreciseShardingAlgorithm
# Range sharding algorithm class name, used for BETWEEN, and supports <, <=, >, >=.
spring.shardingsphere.sharding.tables.inspection_result.table-strategy.standard.range-algorithm-class-name=com.jkkj.config.ResultPreciseShardingAlgorithm
With the range sharding algorithm, there must also be a range sharding strategy.
You can see that both range sharding and standard sharding use the same ResultPreciseShardingAlgorithm implementation class.
So you need to modify the sharding strategy and add range sharding.
public class ResultPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer>, RangeShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
try {
Integer id = preciseShardingValue.getValue();
if (id < 8000000) {
return "inspection_result";
} else {
return "inspection_result_home";
}
} catch (NumberFormatException e) {
log.error("Error converting id during sharding");
throw new RuntimeException(e);
}
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {
Range<Integer> valueRange = rangeShardingValue.getValueRange();
// Integer lowerEndpoint = valueRange.lowerEndpoint();
// Integer upperEndpoint = valueRange.upperEndpoint();
log.info("range : {}", valueRange);
Collection<String> tables = new ArrayList<>();
if (Range.closed(0, 8000000).encloses(valueRange)) {
tables.add("inspection_result");
} else {
tables.add("inspection_result_home");
}
log.info("tables: {}", tables);
return collection;
}
}
Complete Sharding#
Other business logic does not need to be modified. When querying the inspection_result table, Sharding-JDBC will automatically query the two tables and combine them.
ShardingSphere
Sharding-jdbc Practical Introduction to Horizontal Sharding (1)
Sharding-JDBC Quick Start (Only Horizontal Sharding)
Sharding-JDBC: Implementation of Single Database Sharding
Sharding-JDBC Quick Start
ShardingSphere startup error: Property ‘sqlSessionFactory‘ or ‘sqlSessionTemplate‘ are required
Sharding-JDBC RangeShardingAlgorithm (Range Sharding Algorithm)
Sharding JDBC (4) Sharding Strategy 1: Standard Sharding Strategy