怎么用Sharding-Jdbc进行分库分表

这篇文章主要介绍"怎么用Sharding-Jdbc进行分库分表"的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇"怎么用Sharding-Jdbc进行分库分表"文章能帮助大家解决问题。

1. Sharding-Jdbc介绍

https://shardingsphere.apache.org/

• sharding-jdbc是一个分布式的关系型数据库中间件

• 客户端代理模式，不需要搭建服务器，只需要后端数据库即可，有个IDE就行了

• 定位于轻量级的Java框架，以jar的方式提供服务

• 可以理解为增强版的jdbc驱动

• 完全兼容主流的ORM框架

sharding-jdbc提供了4种配置

• Java API

• yaml

• properties

• spring命名空间

与MyCat的区别

• MyCat是服务端的代理，Sharding-Jdbc是客户端代理

• 实际开发中如果企业有DBA建议使用MyCat，都是开发人员建议使用sharding-jdbc

• MyCat不支持在一个库内进行水平分表，而sharding-jdbc支持在同一个数据库中进行水平分表

名词解释

• 逻辑表：物流的合并表

• 真实表：存放数据的地方

• 数据节点：存储数据的MySQL节点

• 绑定表：相当于MyCat中的子表

• 广播表：相当于MyCat中的全局表

2. Sharding-Jdbc引入使用

# 0.首先在两个MySQL上创建两个数据：shard_order# 1.分表给两个库创建两个表order_info_1,order_info_2CREATE TABLE `order_info_1` (  `id` int(11) NOT NULL,  `order_amount` decimal(10,2) DEFAULT NULL,  `order_status` int(255) DEFAULT NULL,  `user_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `order_info_2` (  `id` int(11) NOT NULL,  `order_amount` decimal(10,2) DEFAULT NULL,  `order_status` int(255) DEFAULT NULL,  `user_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 2.切分规则，按照id的奇偶数切分到两个数据库，在自己的数据库按照user_id进行表切分

代码导入POM依赖

             org.apache.shardingsphere            sharding-jdbc-spring-boot-starter            4.0.0-RC2        

配置properties

# 给两个数据源命名spring.shardingsphere.datasource.names=ds0,ds1# 数据源链接ds0要和命名一致spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_orderspring.shardingsphere.datasource.ds0.username=gavinspring.shardingsphere.datasource.ds0.password=123456# 数据源链接ds1要和命名一致spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_orderspring.shardingsphere.datasource.ds1.username=gavinspring.shardingsphere.datasource.ds1.password=123456# 具体的分片规则,基于数据节点spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{1..2}# 分库的规则spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$->{id % 2}# 分表的规则spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{user_id % 2 + 1}

//测试代码@SpringBootTestclass ShardingjdbcProjectApplicationTests {    @Autowired    JdbcTemplate jdbcTemplate;    @Test    void insertTest(){        String sql = "insert into order_info(id,order_amount,order_status,user_id) values(3,213.88,1,2)";        int i = jdbcTemplate.update(sql);        System.out.println("影响行数:"+i);    }}

作业：自己练习一下sharding-jdbc的分库分表

3. 配置广播表

先在两个库上创建广播表province_info

CREATE TABLE `province_info` (  `id` int(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在properties里增加配置

spring.shardingsphere.sharding.broadcast-tables=province_info

测试插入和查询的代码

 @Test    void insertBroadcast(){        String sql = "insert into province_info(id,name) values(1,'beijing')";        int i = jdbcTemplate.update(sql);        System.out.println("******* 影响的结果："+i);    }    @Test    void selectBroadcast(){        String sql = "select * from province_info";        List> result = jdbcTemplate.queryForList(sql);        for (Map val: result) {            System.out.println("=========== "+val.get("id")+" ----- "+val.get("name"));        }    }

4. 配置绑定表

首先按照order_info的建表顺序创建order_item分别在两个库上建立order_item_1,order_item_2

 @Test    void insertBroadcast(){        String sql = "insert into province_info(id,name) values(1,'beijing')";        int i = jdbcTemplate.update(sql);        System.out.println("******* 影响的结果："+i);    }    @Test    void selectBroadcast(){        String sql = "select * from province_info";        List> result = jdbcTemplate.queryForList(sql);        for (Map val: result) {            System.out.println("=========== "+val.get("id")+" ----- "+val.get("name"));        }    }

配置绑定表，将两个表的分表逻辑和order_info保持一致

# 给两个数据源命名spring.shardingsphere.datasource.names=ds0,ds1# 数据源链接ds0要和命名一致spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_orderspring.shardingsphere.datasource.ds0.username=gavinspring.shardingsphere.datasource.ds0.password=123456# 数据源链接ds1要和命名一致spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_orderspring.shardingsphere.datasource.ds1.username=gavinspring.shardingsphere.datasource.ds1.password=123456# 具体的分片规则,基于数据节点spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{1..2}# 分库的规则spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$->{id % 2}# 分表的规则spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{user_id % 2 + 1}# 具体的分片规则,基于数据节点spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds$->{0..1}.order_item_$->{1..2}# 分库的规则spring.shardingsphere.sharding.tables.order_item.database-strategy.inline.sharding-column=order_idspring.shardingsphere.sharding.tables.order_item.database-strategy.inline.algorithm-expression=ds$->{order_id % 2}# 分表的规则spring.shardingsphere.sharding.tables.order_item.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_item.table-strategy.inline.algorithm-expression=order_item_$->{user_id % 2 + 1}# 绑定表关系spring.shardingsphere.sharding.binding-tables=order_info,order_item# 广播表spring.shardingsphere.sharding.broadcast-tables=province_info

5. 读写分离配置

首先配置properties的数据源，如果有主机配置就必须要有从机配置

# 指定主从的配置节点spring.shardingsphere.datasource.names=master0,master0slave0,master1,master1slave0# master0数据源链接配置spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_orderspring.shardingsphere.datasource.master0.username=gavinspring.shardingsphere.datasource.master0.password=123456# master0slave0数据源链接配置spring.shardingsphere.datasource.master0slave0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master0slave0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master0slave0.jdbcUrl=jdbc:mysql://39.99.212.46:3306/shard_orderspring.shardingsphere.datasource.master0slave0.username=gavinspring.shardingsphere.datasource.master0slave0.password=123456# master1数据源链接配置spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_orderspring.shardingsphere.datasource.master1.username=gavinspring.shardingsphere.datasource.master1.password=123456# master1slave0数据源链接配置spring.shardingsphere.datasource.master1slave0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master1slave0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master1slave0.jdbcUrl=jdbc:mysql://localhost:3306/shard_orderspring.shardingsphere.datasource.master1slave0.username=rootspring.shardingsphere.datasource.master1slave0.password=gavin# 具体的分片规则,基于数据节点spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$->{0..1}.order_info_$->{1..2}# 分库的规则spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$->{id % 2}# 分表的规则spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{user_id % 2 + 1}# 具体的分片规则,基于数据节点spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds$->{0..1}.order_item_$->{1..2}# 分库的规则spring.shardingsphere.sharding.tables.order_item.database-strategy.inline.sharding-column=order_idspring.shardingsphere.sharding.tables.order_item.database-strategy.inline.algorithm-expression=ds$->{order_id % 2}# 分表的规则spring.shardingsphere.sharding.tables.order_item.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_item.table-strategy.inline.algorithm-expression=order_item_$->{user_id % 2 + 1}# 绑定表关系spring.shardingsphere.sharding.binding-tables=order_info,order_item# 广播表spring.shardingsphere.sharding.broadcast-tables=province_info# 读写分离主从关系绑定spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robinspring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0spring.shardingsphere.sharding.master-slave-rules.ds1.load-balance-algorithm-type=random

关于"怎么用Sharding-Jdbc进行分库分表"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注行业资讯频道，小编每天都会为大家更新不同的知识点。