一,shardingjdbc的用途
1,官方站介绍: Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈, 它由 JDBC、Proxy 和 Sidecar(规划中)这 3 款相互独立,却又能够混合部署配合使用的产品组成。 它们均提供标准化的数据分片、分布式事务和数据库治理功能, 可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景 2,网址: 官方站:http://shardingsphere.apache.org/index_zh.html官方示例:
https://github.com/apache/shardingsphere-example官方文档(4.x):
https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/
说明:刘宏缔的架构森林是一个专注架构的博客,地址:https://www.cnblogs.com/architectforest
对应的源码可以访问这里获取: https://github.com/liuhongdi/
说明:作者:刘宏缔 邮箱: 371125307@qq.com
二,演示项目的相关信息
1,项目地址(完整代码):https://github.com/liuhongdi/shardingjdbc
2,项目说明:
两个数据库资源:saleorder01,saleorder02
下面包含了相同结构的数据表各两个,分别是:
t_order_1,
t_order_2,
t_order_3,
t_order_4
3,数据库结构
如图:
4,项目结构:
如图:
三,配置文件说明:
1,数据库的创建sql:CREATE DATABASE `saleorder01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
CREATE DATABASE `saleorder02` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */2,数据表的创建sql:
CREATE TABLE `t_order_1` ( `orderId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `goodsName` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name', PRIMARY KEY (`orderId`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='order'
其他三个表sql相同
3,application.properties:#shardingsphere spring.shardingsphere.datasource.names=saleorder01,saleorder02 spring.shardingsphere.datasource.saleorder01.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.saleorder01.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.saleorder01.jdbc-url=jdbc:mysql://127.0.0.1:3306/saleorder01?characterEncoding=utf-8 spring.shardingsphere.datasource.saleorder01.username=root spring.shardingsphere.datasource.saleorder01.password=passdemo spring.shardingsphere.datasource.saleorder02.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.saleorder02.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.saleorder02.jdbc-url=jdbc:mysql://127.0.0.1:3306/saleorder02?characterEncoding=utf-8 spring.shardingsphere.datasource.saleorder02.username=root spring.shardingsphere.datasource.saleorder02.password=passdemo spring.shardingsphere.sharding.default-data-source-name=saleorder01 spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=orderId spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.shardingjdbc.demo.algorithm.DatabasePreciseShardingAlgorithm spring.shardingsphere.sharding.binding-tables=t_order spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=saleorder0$->{1..1}.t_order_$->{1..2},saleorder0$->{2..2}.t_order_$->{3..4} spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=orderId spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.shardingjdbc.demo.algorithm.OrderTablePreciseShardingAlgorithm spring.shardingsphere.props.sql.show=true
说明:
com.shardingjdbc.demo.algorithm.DatabasePreciseShardingAlgorithm:数据库得到数据源的算法
com.shardingjdbc.demo.algorithm.OrderTablePreciseShardingAlgorithm:t_order表得到表名的算法
spring.shardingsphere.datasource.names=saleorder01,saleorder02: 指定数据源的名字
spring.shardingsphere.sharding.binding-tables=t_order: 指定绑定表的名字
spring.shardingsphere.props.sql.show=true:打印sql
四,java代码说明
DatabasePreciseShardingAlgorithm.javapublic class DatabasePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) { Long curValue = shardingValue.getValue(); String curBase = ""; if (curValue > 0 && curValue<=200) { curBase = "saleorder01"; } else { curBase = "saleorder02"; } return curBase; } }
说明:根据id返回数据库资源名
OrderTablePreciseShardingAlgorithm.java
public class OrderTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) { Long curValue = shardingValue.getValue(); String curTable = ""; if (curValue > 0 && curValue<=100) { curTable = "t_order_1"; } else if (curValue > 100 && curValue<=200) { curTable = "t_order_2"; } else if (curValue > 200 && curValue<=300) { curTable = "t_order_3"; } else { curTable = "t_order_4"; } return curTable; } }
说明:根据id返回数据表名
五,效果演示
1,添加一个订单: 访问: /order/add/ 2,查看订单列表: 访问: /order/list/六,shardingjdbc使用中的注意事项:
1,如果有的表比较小,可以存在于各个库中, 这里可以使用公共表(广播表):例:spring.shardingsphere.sharding.broadcast-tables=t_dict写入时会写入到各个库, 读取时从本地库中读取,可以避免跨节点的查询 2,打开sql显示,用于调试 spring.shardingsphere.props.sql.show= #是否开启SQL显示,默认值: false
spring.shardingsphere.props.sql.show=true
3,如果是用户表分表,需要使用表内唯一的字段如:用户名/手机号作为sharding column做拆分 4,即使做了分表,仍然要添加查询时使用到的索引, 否则效率仍然会成问题 5,不参与分表的数据表,要指定所在的数据源:如下:
spring.shardingsphere.sharding.default-data-source-name=saleorder01
七,查看spring boot版本
. ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v2.3.1.RELEASE)