热门标签
热门文章
- 1ROS-语音控制-会说话的机器人(3)-科大讯飞离线语音合成+图灵机器人_科大讯飞ros机器人
- 2【Linux系统编程】—进程学习笔记（fork进程创建、退出、僵死进程与孤儿进程、如何避免僵死进程）_fork 后会退出吗
- 3uniapp华为手机真机测试保姆教程_uniapp真机p40pro
- 4Publii和GitHub：搭建个人网站的完美组合
- 5【愚公系列】2023年12月 HarmonyOS教学课程 016-ArkUI组件（Toggle）
- 6阿里云ECS服务器入门使用流程 新手必看教程_用户可以对云服务器ecs进行下列哪些操作
- 7【iVX 开发 - 入门】开发环境、应用对象树介绍(含操作演示)_ivx前端开发教程
- 8CardView间距问题_安卓cardview上下有间隙
- 9Java在线考试系统
- 10python读取文件并打印_Python3.4如何读取一个文件并打印
当前位置: article > 正文
springboot整合shardingSphere-JDBC_springboot shardingsphere-jdbc
作者：你好赵伟 | 2024-03-08 15:16:48
赞
踩
springboot shardingsphere-jdbc
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`)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
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`)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
整合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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
配置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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
测试验证
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]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
按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]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
未配置分片规则的表路由
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]
- 1
- 2
- 3
下面将进行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"; } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
新增spi配置文件
src/main/resources/META-INF/services/org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
文件内容：
org.example.sharding.CatIdShardingAlgorithm
- 1
修改配置文件
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
测试验证
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, 新品上市]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
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, 新品上市]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
查询
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]
- 1
- 2
- 3
- 4
- 5
- 6
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/210903
推荐阅读
相关标签
