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 is just a matter of making 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 MySQL can easily handle
6 million data starts to lag, optimization can solve it (table structure, index design)
8 million to 10 million even a brilliant DBA will encounter bottlenecks.
The current requirement is sharding based on business, so you need to set the sharding logic yourself.
Sharding-JDBC#
When it comes to sharding, one cannot avoid SHARDING-JDBC.
It is positioned as a lightweight Java framework that provides additional services at the JDBC layer in Java. It connects directly to the database using a client, providing services in the form of a jar package, without the need for 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 function 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 dependencies-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
It should be noted that if you use druid, the dependency must use 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, for example: 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 location of the table in which data source (database) and which table. The inspection_result in tables.inspection_result is the prefix of the table name.
# 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
# Business-based 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";
}
}
}
You can determine the returned table name based on the id.
Range Sharding#
The strategy.standard.precise we use indicates the standard sharding strategy, which provides support for sharding operations in SQL statements such as = and IN.
Consider this: 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. The 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
# Business-based sharding, this is a custom sharding strategy.
# standard.precise-algorithm The standard strategy's sharding algorithm 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.
It can be seen that both range sharding and standard sharding use the same ResultPreciseShardingAlgorithm implementation class.
So you need to modify the sharding strategy to 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
Practical Introduction to Sharding-JDBC (Part 1)
Quick Start with Sharding-JDBC (Only Horizontal Sharding)
Implementation of Single Library Sharding-JDBC
Quick Start with Sharding-JDBC
Error when starting shardingsphere: Property ‘sqlSessionFactory‘ or ‘sqlSessionTemplate‘ are required
Sharding-JDBC RangeShardingAlgorithm (Range Sharding Algorithm)
Sharding JDBC (Part 4) Sharding Strategy 1: Standard Sharding Strategy