下面以两库三表且以学生的学号作为分片列整个demo,将SpringBoot和sharding-jdbc整合起来实现分库分表。
一、库表结构
库表结构如下:
├─db0
│ └─student_0
│ └─student_1
│ └─student_2
├─db1
│ └─student_0
│ └─student_1
│ └─student_2
建表语句如下:
DROP TABLE IF EXISTS `student_0`;
CREATE TABLE `student_0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`sname` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
`sno` bigint(20) NOT NULL DEFAULT '0' COMMENT '学号',
`age` tinyint(2) NOT NULL DEFAULT '0' COMMENT '年龄',
PRIMARY KEY (`id`),
UNIQUE KEY `uqe_idx_sno` (`sno`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5807 DEFAULT CHARSET=utf8 COMMENT='学生表_0';
二、添加依赖
1、添加sharding-jdbc依赖
在正常的SpringBoot项目中的pom文件中添加如下依赖:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
2、添加数据源
数据源我使用的是HikariCP——号称史上最快的,速度打败Druid的王者。SpringBoot2.0之后已经采用HikariCP作为默认连接池配置。
我在引入mybatis时已经引入了HikariCP,所以无需额外添加HikariCP的依赖。如下图:
若想使用druid数据源可以添加如下依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
3、添加mysql-connector-java
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
三、 配置文件
数据库的相关配置配置在db.properties中。
使用HikariCP和Druid作为数据源的配置有些不同,分别贴出来。
1、HikariCP为数据源的配置
注:使用HikariCP作为数据源,数据库url用的是jdbc-url,否则会报错。
# 数据库名,db0和db1,可以看做是两个数据库的别名
sharding.jdbc.datasource.names=db0,db1
# 配置数据库db0
spring.shardingsphere.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db0.driverClassName=com.mysql.jdbc.Driver
# 使用HikariCP作为数据源,这块用的是jdbc-url
# ip添连接ip,数据库名添真实库名
spring.shardingsphere.datasource.db0.jdbc-url=jdbc:mysql://ip:3306/数据库名?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.db0.username=luffylv
spring.shardingsphere.datasource.db0.password=luffylv
spring.shardingsphere.datasource.db0.initialSize=20
spring.shardingsphere.datasource.db0.maxActive=50
spring.shardingsphere.datasource.db0.maxIdle=35
spring.shardingsphere.datasource.db0.minIdle=25
# 配置数据库db1
spring.shardingsphere.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db1.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.jdbc-url=jdbc:mysql://ip:3306/数据库名?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.db1.username=luffylv
spring.shardingsphere.datasource.db1.password=luffylv
spring.shardingsphere.datasource.db1.initialSize=20
spring.shardingsphere.datasource.db1.maxActive=50
spring.shardingsphere.datasource.db1.maxIdle=35
spring.shardingsphere.datasource.db1.minIdle=25
# 分库配置,采用默认数据库分库策略,即db0和db1均采用以下策略
# 分库列,即以学号作为分片列,与数据库字段一致
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=sno
# 分库策略使用精准算法,根据分片列在此处实现具体分片逻辑,后面会讲到
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.luffylv.shardingspherespringbootdemo.config.datasource.strategy.standard.PreciseShardingDatabaseAlgorithm
# 分表配置,tables后为逻辑表名
# 实际数据节点,采用的是groovy表达式,实际上也就是[db0.student_0,db0.student_1,db0.student_2,db1.student_0,db1.student_1,db1.student_2]
spring.shardingsphere.sharding.tables.student.actual-data-nodes=db$->{0..1}.student_${0..2}
spring.shardingsphere.sharding.tables.student.table-strategy.standard.sharding-column=sno
spring.shardingsphere.sharding.tables.student.table-strategy.standard.precise-algorithm-class-name=com.luffylv.shardingspherespringbootdemo.config.datasource.strategy.standard.PreciseShardingTableAlgorithm
# 打印sql,可用于观察分片后的sql语句
spring.shardingsphere.sharding.props.sql.show=true
2、Druid为数据源的配置
注:使用Druid作为数据源,数据库url用的是url,否则会报错。
# 数据库名,db0和db1,可以看做是两个数据库的别名
spring.shardingsphere.datasource.names=db0,db1
# 配置数据库db0
spring.shardingsphere.datasource.db0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db0.driverClassName=com.mysql.jdbc.Driver
# 使用Druid作为数据源,这块用的是url
# ip添连接ip,数据库名添真实库名
spring.shardingsphere.datasource.db0.url=jdbc:mysql://ip:3306/数据库名?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.db0.username=luffylv
spring.shardingsphere.datasource.db0.password=luffylv
spring.shardingsphere.datasource.db0.initialSize=20
spring.shardingsphere.datasource.db0.maxActive=50
spring.shardingsphere.datasource.db0.maxIdle=35
spring.shardingsphere.datasource.db0.minIdle=25
# 配置数据库db1
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driverClassName=com.mysql.jdbc.Driver
# 使用Druid作为数据源,这块用的是url
# ip添连接ip,数据库名添真实库名
spring.shardingsphere.datasource.db1.url=jdbc:mysql://ip:3306/数据库名?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.db1.username=luffylv
spring.shardingsphere.datasource.db1.password=luffylv
spring.shardingsphere.datasource.db1.initialSize=20
spring.shardingsphere.datasource.db1.maxActive=50
spring.shardingsphere.datasource.db1.maxIdle=35
spring.shardingsphere.datasource.db1.minIdle=25
# 分库配置,采用默认数据库分库策略,即db0和db1均采用以下策略
# 分库列,即以学号作为分片列,与数据库字段一致
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=sno
# 分库策略使用精准算法,根据分片列在此处实现具体分片逻辑,后面会讲到
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.luffylv.shardingspherespringbootdemo.config.datasource.strategy.standard.PreciseShardingDatabaseAlgorithm
# 分表配置,tables后为逻辑表名
# 实际数据节点,采用的是groovy表达式,实际上也就是[db0.student_0,db0.student_1,db0.student_2,db1.student_0,db1.student_1,db1.student_2]
spring.shardingsphere.sharding.tables.student.actual-data-nodes=db$->{0..1}.student_${0..2}
spring.shardingsphere.sharding.tables.student.table-strategy.standard.sharding-column=sno
spring.shardingsphere.sharding.tables.student.table-strategy.standard.precise-algorithm-class-name=com.luffylv.shardingspherespringbootdemo.config.datasource.strategy.standard.PreciseShardingTableAlgorithm
# 打印sql,可用于观察分片后的sql语句
spring.shardingsphere.sharding.props.sql.show=true
总结起来就四点:
1、配置要分库的数据库别名
2、分别配置每个数据库
3、配置库的分片列和分片规则。若采用默认分库策略,则该策略对1中配置的所有库有效。若具体库采用具体分片策略,则每个库都需配置其对应的分库策略。
3、配置表的分片列和分片规则。
四、分库分表策略
本例中,库和表均以按学号取模为分片策略。
1、分库策略
package com.luffylv.shardingspherespringbootdemo.config.datasource.strategy.standard;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
/**
* 数据库精准分片策略,需实现PreciseShardingAlgorithm接口,泛型为分片列的类型。本例按学号sno分片,其类型为Integer。
* 重写doSharding方法,实现具体分库逻辑。
*/
public class PreciseShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Integer> {
/**
* 学号sno按数据库数量取模,模为0分片到数据库db0,模为1分片到数据库db1
*
* @param dbNames 所有库名的集合,本例为[db0,db1]
* @param preciseShardingValue 精确分片值,其有三个final属性,String columnName(分片列名:本例为sno),
* String logicTableName(逻辑表名:本例为student),
* T value(分片列的值:本例为sno的值)
* @return 库名
*/
@Override
public String doSharding(Collection<String> dbNames, PreciseShardingValue<Integer> preciseShardingValue) {
Integer mod = preciseShardingValue.getValue() % dbNames.size();
for (String dbName : dbNames) {
if (dbName.endsWith(String.valueOf(mod))) {
return dbName;
}
}
throw new UnsupportedOperationException();
}
}
2、分表策略
package com.luffylv.shardingspherespringbootdemo.config.datasource.strategy.standard;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
/**
* 表精准分片策略,需实现PreciseShardingAlgorithm接口,泛型为分片列的类型。本例按学号sno分片,其类型为Integer。
* 重写doSharding方法,实现具体分库逻辑。
*/
public class PreciseShardingTableAlgorithm implements PreciseShardingAlgorithm<Integer> {
/**
* 学号sno按表数量取模,模为0分片到表student_0,模为1分片到表student_1,模为2分片到表student_2
*
* @param tableNames 所有表名的集合,本例为[student_0,student_1,student_2]
* @param preciseShardingValue 精确分片值,其有三个final属性,String columnName(分片列名:本例为sno),
* String logicTableName(逻辑表名:本例为student),
* T value(分片列的值:本例为sno的值)
* @return 表名
*/
@Override
public String doSharding(Collection<String> tableNames, PreciseShardingValue<Integer> preciseShardingValue) {
Integer mod = preciseShardingValue.getValue() % tableNames.size();
for (String tableName : tableNames) {
if (tableName.endsWith(String.valueOf(mod))) {
return tableName;
}
}
throw new UnsupportedOperationException();
}
}
五、数据源配置
引用刚才配置的分库分表配置db.properties
。
package com.luffylv.shardingspherespringbootdemo.config;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
@Configuration
@PropertySource(value = {"db.properties"})
public class DataSourceConfig {
}
六、测试
就以新增学生为例验证分库分表是否成功。将学生名为luffy,学号为16,年龄为17的学生插入表中。如下:
mapper.xml写的比较简单,如下:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.luffylv.shardingspherespringbootdemo.dao.StudentDao">
<!-- sql写法和之前类似,对于增删改查此处使用的是逻辑表名student,不是实际表名student_0、student_1、student_2等 -->
<insert id="addStudent">
insert into student
(sname,sno,age) value (#{student.sname,jdbcType=VARCHAR},#{student.sno,jdbcType=BIGINT},#{student.age,jdbcType=TINYINT})
</insert>
</mapper>
按照上面的分片策略,16%2=0,16%3=1,该学生应插入到db0.sutdent_1中。
下面发送该请求,看下该学生是否添加到db0.sutdent_1表中。控制台日志如下:
通过日志可以看出黄框中的逻辑SQL即为在mapper.xml中写的sql语句。经过sharding-jdbc处理后,将逻辑SQL改写成红框中的实际SQL。可以看出,实际是操作db0库的student_1表。数据库中也确实有该条记录,如下图:
以上便是SpringBoot整合shrding-jdbc分库分表的学习记录,如有错误烦请指出,共同学习。
注:刚开始使用的是io.shardingsphere的sharding-jdbc,遇到过两个坑,后来果断弃坑选用org.apache.shardingsphere的sharding-jdbc。使用中遇到的两个坑如下:
解决io.shardingsphere.core.exception.ShardingException:The URL of JDBC is not supported.异常
记录io.shardingsphere的3.X版本与pagehelper同时使用时导致offset始终为0的问题