spring boot:用shardingjdbc实现多数据源的分库分表(shardingsphere 4.1.1/spring boot 2.3.1)

一,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,数据库结构

  如图:

spring boot:用shardingjdbc实现多数据源的分库分表(shardingsphere 4.1.1/spring boot 2.3.1)  

4,项目结构:

   如图:

spring boot:用shardingjdbc实现多数据源的分库分表(shardingsphere 4.1.1/spring boot 2.3.1) 

三,配置文件说明:

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.java
public 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/
spring boot:用shardingjdbc实现多数据源的分库分表(shardingsphere 4.1.1/spring boot 2.3.1)
 
2,查看订单列表: 
访问: /order/list/
 spring boot:用shardingjdbc实现多数据源的分库分表(shardingsphere 4.1.1/spring boot 2.3.1)

六,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)

 

spring boot:用shardingjdbc实现多数据源的分库分表(shardingsphere 4.1.1/spring boot 2.3.1)

上一篇:ProxyMySQL的Admin管理接口


下一篇:MySQL ERROR 1356 (HY000)