利用Sharding

利用Sharding

目录1. Sharding-Jdbc介绍2. Sharding-Jdbc引入使用3. 配置广播表4. 配置绑定表5. 读写分离配置

1. Sharding-Jdbc介绍

https://shardingsphere.apache.org/

sharding-jdbc是一个分布式的关系型数据库中间件客户端代理模式，不需要搭建服务器，只需要后端数据库即可，有个IDE就行了定位于轻量级的java框架，以jar的方式提供服务可以理解为增强版的jdbc驱动完全兼容主流的ORM框架

sharding-jdbc提供了4种配置

Java APIyamlpropertiesspring命名空间

与MyCat的区别

MyCat是服务端的代理，Sharding-Jdbc是客户端代理实际开发中如果企业有DBA建议使用MyCat，都是开发人员建议使用sharding-jdbcMyCat不支持在一个库内进行水平分表，而sharding-jdbc支持在同一个数据库中进行水平分表

名词解释

逻辑表：物流的合并表真实表：存放数据的地方数据节点：存储数据的mysql节点绑定表：相当于MyCat中的子表广播表：相当于MyCat中的全局表

2. Sharding-Jdbc引入使用

# 0.首先在两个MySQL上创建两个数据：shard_order

# 1.分表给两个库创建两个表order_info_1,order_info_2

CREATE 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.HikariDataSource

spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_order

spring.shardingsphere.datasource.ds0.username=gavin

spring.shardingsphere.datasource.ds0.password=123456

# 数据源链接ds1要和命名一致

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_order

spring.shardingsphere.datasource.ds1.username=gavin

spring.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=id

spring.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_id

spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$->{user_id % 2 + 1}

//测试代码

@SpringBootTest

class 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

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

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.HikariDataSource

spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_order

spring.shardingsphere.datasource.ds0.username=gavin

spring.shardingsphere.datasource.ds0.password=123456

# 数据源链接ds1要和命名一致

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_order

spring.shardingsphere.datasource.ds1.username=gavin

spring.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=id

spring.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_id

spring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expressiboqIvagbzon=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_id

spring.shardingsphere.sharding.tables.order_item.database-strategy.inline.algorithm-expression=ds$->{order_idhttp:// % 2}

# 分表的规则

spring.shardingsphere.sharding.tables.order_item.table-strategy.inline.sharding-column=user_id

spring.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数据源http://链接配置

spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.master0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_order

spring.shardingsphere.datasource.master0.username=gavin

spring.shardingsphere.datasource.master0.password=123456

# master0slave0数据源链接配置

spring.shardingsphere.datasource.master0slave0.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.master0slave0.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.master0slave0.jdbcUrl=jdbc:mysql://39.99.212.46:3306/shard_order

spring.shardingsphere.datasource.master0slave0.username=gavin

spring.shardingsphere.datasource.master0slave0.password=123456

# master1数据源链接配置

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.master1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_order

spring.shardingsphere.datasource.master1.username=gavin

spring.shardingsphere.datasource.master1.password=123456

# master1slave0数据源链接配置

spring.shardingsphere.datasource.master1slave0.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.master1slave0.driver-class-name=com.mysql.jdbc.Driver

spring.shardingsphere.datasource.master1slave0.jdbcUrl=jdbc:mysql://localhost:3306/shard_order

spring.shardingsphere.datasource.master1slave0.username=root

spring.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=id

spring.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_id

spring.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_id

spring.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_id

spring.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=master0

spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0

spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robin

spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1

spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0

spring.shardingsphere.sharding.master-slave-rules.ds1.load-balance-algorithm-type=random

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。