springboot整合shardingSphere-JDBC
shardingSphere简介
参考官方文档: https://shardingsphere.apache.org/index_zh.html
github: https://github.com/apache/shardingsphere
Apache ShardingSphere 是一款分布式的数据库生态系统,可以将任意数据库转换为分布式数据库,并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。
ShardingSphere-JDBC 采用无中心化架构,与应用程序共享资源,适用于 Java 开发的高性能的轻量级 OLTP 应用; ShardingSphere-Proxy 提供静态入口以及异构语言的支持,独立于应用程序部署,适用于 OLAP 应用以及对分片数据库进行管理和运维的场景。
ShardingSphere-JDBC | ShardingSphere-Proxy | |
---|---|---|
数据库 | 任意 | MySQL/PostgreSQL |
连接消耗数 | 高 | 低 |
异构语言 | 仅 Java | 任意 |
性能 | 损耗低 | 损耗略高 |
无中心化 | 是 | 否 |
静态入口 | 无 | 有 |
数据库及表
t_order 用来演示分库分表
create table t_order_1(
`id` BIGINT NOT NULL COMMENT '主键',
`order_id` int COMMENT '订单号',
`user_id` int COMMENT '用户id',
`order_amount` DECIMAL(10,5) COMMENT '订单金额',
`order_remark` VARCHAR(100) COMMENT '订单备注',
`address_id` VARCHAR(100) COMMENT '收货地址 id',
PRIMARY KEY (`id`)
);
t_goods 演示一些特殊场景,先在ds1库创建t_goods一个表即可
create table t_goods(
`id` BIGINT NOT NULL COMMENT '主键',
`cat_id` int COMMENT '商品分类id',
`goods_sn` VARCHAR(20) COMMENT '商品编码',
`goods_name` VARCHAR(100) COMMENT '商品名称',
`goods_price` DECIMAL(10,5) COMMENT '商品价格',
`store_id` int COMMENT '店铺id',
`on_time` datetime COMMENT '上架时间',
`goods_remark` VARCHAR(100) COMMENT '商品备注',
PRIMARY KEY (`id`)
);
整合springboot
参考文档: https://shardingsphere.apache.org/document/5.0.0/cn/user-manual/shardingsphere-jdbc/usage/sharding/spring-boot-starter/
示例源代码:https://github.com/codefarmer008/springCloudDemo/releases/tag/v6.0 请参考shardingSphere-jdbc模块
引入依赖pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>springCloudDemo</artifactId>
<groupId>org.example</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>shardingSphere-jdbc</artifactId>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>knife4j-spring-boot-starter</artifactId>
<version>3.0.2</version>
<exclusions>
<exclusion>
<groupId>org.springframework.plugin</groupId>
<artifactId>spring-plugin-core</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.plugin</groupId>
<artifactId>spring-plugin-core</artifactId>
<version>2.0.0.RELEASE</version>
</dependency>
</dependencies>
</project>
配置t_order分库分表规则,主键使用雪花算法生成
server:
port: 8790
spring:
application:
name: shardingSphere-jdbc
shardingsphere:
props:
sql-show: true
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.220.125:3306/ds0
username: root
password: Root123
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.220.125:3306/ds1
username: root
password: Root123
rules:
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..1} # 配置 t_order 表规则
database-strategy: # 配置分库策略
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy: # 配置分表策略
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
测试验证
t_order表路由
使用postman发送几个创建订单的请求
从日志可以看到请求被路由到ds1库的t_order_1表
2023-01-14 13:22:24.015 INFO 12980 --- [nio-8790-exec-3] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_id,
user_id,
order_amount,
order_remark,
address_id ) VALUES ( ?,
?,
?,
?,
?,
? )
2023-01-14 13:22:24.015 INFO 12980 --- [nio-8790-exec-3] ShardingSphere-SQL : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2023-01-14 13:22:24.015 INFO 12980 --- [nio-8790-exec-3] ShardingSphere-SQL : Actual SQL: ds1 ::: INSERT INTO t_order_1 ( id,
order_id,
user_id,
order_amount,
order_remark,
address_id ) VALUES (?, ?, ?, ?, ?, ?) ::: [1614130774347350018, 1001, 1001, 54.33, 1001号订单, 111]
按id查询时则查询了2个库的4张表
2023-01-14 13:31:51.430 INFO 12980 --- [nio-8790-exec-6] ShardingSphere-SQL : Logic SQL: SELECT id,order_id,user_id,order_amount,order_remark,address_id FROM t_order WHERE id=?
2023-01-14 13:31:51.430 INFO 12980 --- [nio-8790-exec-6] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2023-01-14 13:31:51.431 INFO 12980 --- [nio-8790-exec-6] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT id,order_id,user_id,order_amount,order_remark,address_id FROM t_order_0 WHERE id=? ORDER BY id ASC ::: [1614130774347350018]
2023-01-14 13:31:51.431 INFO 12980 --- [nio-8790-exec-6] ShardingSphere-SQL : Actual SQL: ds0 ::: SELECT id,order_id,user_id,order_amount,order_remark,address_id FROM t_order_1 WHERE id=? ORDER BY id ASC ::: [1614130774347350018]
2023-01-14 13:31:51.431 INFO 12980 --- [nio-8790-exec-6] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT id,order_id,user_id,order_amount,order_remark,address_id FROM t_order_0 WHERE id=? ORDER BY id ASC ::: [1614130774347350018]
2023-01-14 13:31:51.431 INFO 12980 --- [nio-8790-exec-6] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT id,order_id,user_id,order_amount,order_remark,address_id FROM t_order_1 WHERE id=? ORDER BY id ASC ::: [1614130774347350018]
未配置分片规则的表路由
t_goods表未配置任何路由规则,且该表只存在于ds1库,此时若访问商品表的相关接口,shardingsphere该如何路由呢?
从日志打印的结果和数据库的内容来看,shardingsphere正确路由到了ds1库的t_goods表,并未报错。
2023-01-14 14:53:46.333 INFO 12980 --- [nio-8790-exec-9] ShardingSphere-SQL : Logic SQL: SELECT id,cat_id,goods_sn,goods_name,goods_price,store_id,on_time,goods_remark FROM t_goods WHERE id=?
2023-01-14 14:53:46.334 INFO 12980 --- [nio-8790-exec-9] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2023-01-14 14:53:46.334 INFO 12980 --- [nio-8790-exec-9] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT id,cat_id,goods_sn,goods_name,goods_price,store_id,on_time,goods_remark FROM t_goods WHERE id=? ::: [2]
下面将进行debug,分析下原因:
先看下官方给的sql执行流程图,流程由 SQL 解析 => SQL 绑定 => SQL 路由 => SQL 改写 => SQL 执行 => 结果归并 组成
我们先看下sql执行前,sql改写时的情况
从图中可以看到进行sql改写前,路由数据源已确定。
现在只要搞明白metaData变量何时初始化的就可以了,通过查找代码,发现该变量是在创建ShardingSpherePreparedStatement时进行初始化的,但是又是从connection中获取的,所以还需要看下connection中的代码
那么connection.getContextManager().getMetaDataContexts()是什么初始化的呢?通过查看代码发现是项目启动时创建ShardingSphereConnection时创建的,为了初始化元数据信息,项目启动时会连接每个数据库获取所有表名称,然后初始化逻辑库的路由规则
自定义分片算法
现在有个需求,t_goods表预计单表数据量很大,需要分表,分表规则是按商品分类id进行拆分,拆分规则如下:
1001-数码 -----》t_goods_1
1002-服装 -----》t_goods_2
1003-图书 -----》t_goods_3
其他 -----》t_goods_4
让我们先重建下t_goods表
新增分片算法
package org.example.sharding;
import lombok.Getter;
import lombok.Setter;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import java.util.Collection;
import java.util.Properties;
public class CatIdShardingAlgorithm implements StandardShardingAlgorithm<Comparable<?>> {
@Getter
@Setter
private Properties props = new Properties();
@Override
public void init() {
}
@Override
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Comparable<?>> shardingValue) {
Comparable<?> value = shardingValue.getValue();
if(value == null){
return "t_goods_4";
}
Integer catId = (Integer) value;
if(1001 == catId){
return "t_goods_1";
}else if(1002 == catId){
return "t_goods_2";
}else if(1003 == catId){
return "t_goods_3";
}else {
return "t_goods_4";
}
}
@Override
public Collection<String> doSharding(final Collection<String> availableTargetNames, final RangeShardingValue<Comparable<?>> shardingValue) {
throw new UnsupportedOperationException("catId sharding algorithm can not tackle with range query.");
}
@Override
public String getType() {
return "CATID";
}
}
新增spi配置文件
src/main/resources/META-INF/services/org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
文件内容:
org.example.sharding.CatIdShardingAlgorithm
修改配置文件
application.yml
server:
port: 8790
spring:
application:
name: shardingSphere-jdbc
shardingsphere:
props:
sql-show: true
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.220.125:3306/ds0
username: root
password: Root123
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.220.125:3306/ds1
username: root
password: Root123
rules:
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..1} # 配置 t_order 表规则
database-strategy: # 配置分库策略
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy: # 配置分表策略
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
t_goods:
actual-data-nodes: ds1.t_goods_$->{1..4} # 配置 t_goods 表规则
table-strategy: # 配置分表策略
standard:
sharding-column: cat_id
sharding-algorithm-name: table-cat-id
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 2}
table-cat-id:
type: CATID
keyGenerators:
snowflake:
type: SNOWFLAKE
测试验证
2023-01-14 16:28:29.385 INFO 11948 --- [nio-8790-exec-3] ShardingSphere-SQL : Logic SQL: INSERT INTO t_goods ( id,
cat_id,
goods_sn,
goods_name,
goods_price,
store_id,
on_time,
goods_remark ) VALUES ( ?,
?,
?,
?,
?,
?,
?,
? )
2023-01-14 16:28:29.385 INFO 11948 --- [nio-8790-exec-3] ShardingSphere-SQL : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2023-01-14 16:28:29.385 INFO 11948 --- [nio-8790-exec-3] ShardingSphere-SQL : Actual SQL: ds1 ::: INSERT INTO t_goods_1 ( id,
cat_id,
goods_sn,
goods_name,
goods_price,
store_id,
on_time,
goods_remark ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ::: [1614177617278193666, 1001, 20231001, 小米手机, 1999.00, 111, 2023-01-02 04:00:00.0, 新品上市]
2023-01-14 16:29:23.760 INFO 11948 --- [nio-8790-exec-2] ShardingSphere-SQL : Logic SQL: INSERT INTO t_goods ( id,
cat_id,
goods_sn,
goods_name,
goods_price,
store_id,
on_time,
goods_remark ) VALUES ( ?,
?,
?,
?,
?,
?,
?,
? )
2023-01-14 16:29:23.760 INFO 11948 --- [nio-8790-exec-2] ShardingSphere-SQL : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2023-01-14 16:29:23.760 INFO 11948 --- [nio-8790-exec-2] ShardingSphere-SQL : Actual SQL: ds1 ::: INSERT INTO t_goods_2 ( id,
cat_id,
goods_sn,
goods_name,
goods_price,
store_id,
on_time,
goods_remark ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ::: [1614177848686333953, 1002, 20231002, 连衣裙, 299.00, 222, 2023-01-02 04:00:00.0, 新品上市]
查询
2023-01-14 16:31:10.707 INFO 11948 --- [nio-8790-exec-8] ShardingSphere-SQL : Logic SQL: SELECT id,cat_id,goods_sn,goods_name,goods_price,store_id,on_time,goods_remark FROM t_goods WHERE id=?
2023-01-14 16:31:10.707 INFO 11948 --- [nio-8790-exec-8] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2023-01-14 16:31:10.707 INFO 11948 --- [nio-8790-exec-8] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT id,cat_id,goods_sn,goods_name,goods_price,store_id,on_time,goods_remark FROM t_goods_1 WHERE id=? ORDER BY id ASC ::: [1614177617278193666]
2023-01-14 16:31:10.707 INFO 11948 --- [nio-8790-exec-8] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT id,cat_id,goods_sn,goods_name,goods_price,store_id,on_time,goods_remark FROM t_goods_2 WHERE id=? ORDER BY id ASC ::: [1614177617278193666]
2023-01-14 16:31:10.707 INFO 11948 --- [nio-8790-exec-8] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT id,cat_id,goods_sn,goods_name,goods_price,store_id,on_time,goods_remark FROM t_goods_3 WHERE id=? ORDER BY id ASC ::: [1614177617278193666]
2023-01-14 16:31:10.707 INFO 11948 --- [nio-8790-exec-8] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT id,cat_id,goods_sn,goods_name,goods_price,store_id,on_time,goods_remark FROM t_goods_4 WHERE id=? ORDER BY id ASC ::: [1614177617278193666]