依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
application.properties配置
# 定义两个数据源分别为ds0,ds1
spring.shardingsphere.datasource.names=ds1,ds2
# ds1具体配置
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://192.168.174.128:3306/sharding_jdbc_node1
spring.shardingsphere.datasource.ds1.username=zhushanglin
spring.shardingsphere.datasource.ds1.password=ZSL1992@!@#zsl
# ds2具体配置
spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbcUrl=jdbc:mysql://192.168.174.129:3306/sharding_jdbc_node2
spring.shardingsphere.datasource.ds2.username=zhushanglin
spring.shardingsphere.datasource.ds2.password=ZSL1992@!@#zsl
# 分片策略: 注意一定要将mapper.xml中的t_order_1全局替换为t_order逻辑表,否则报错:Missing the data source name: 'null'
# t_order表的真实数据节点是ds1或ds2下的t_order_1或t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{1..2}.t_order_$->{1..2}
# 数据库选择策略,根据user_id字段对2取余数得到0或1,再+1 就是 ds1或ds2
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1}
# 表选择策略,在MySharding类中自定义
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
##主键通过雪花算法,自动填充(分布式id)
# 定义主键列
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
# 定义主键生产方式 雪花算法
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 定义worker.id 1024内的数字随便给
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=345
# 定义回调时间,10毫秒
spring.shardingsphere.sharding.tables.t_order.key-generator.props.max.tolerate.time.difference.milliseconds=10
# 配置mybatis
mybatis.mapper-locations=/mybatis/*.xml
logging.pattern.dateformat=HH:mm:ss
自定义分表策略类MySharding
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
@Slf4j
public class MySharding implements PreciseShardingAlgorithm<Long> {
/**
* availableTargetNames 是可选择的表,t_order_1和t_order_2, actual-data-nodes=ds$->{0..1}.t_order_$->{1..2}配置的
* shardingValue 是封装了逻辑表logicTableName=t_order,分片字段columnName=order_id和雪花算法算出来的值value=756917110673084417
*/
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
Long id = shardingValue.getValue();
long mode =id % availableTargetNames.size();
String[] strings = availableTargetNames.toArray(new String[0]);
mode = Math.abs(mode);
log.info("选择的表是:t_order_"+(mode+1));
return strings[(int)mode];
}
}
坑1
一定要做的事:将mapper.xml文件中的物理表名修改为逻辑表名
否则就会报错:Missing the data source name: ‘null’
对应的两个库4张表:
坑2
在自定义表的分片策略类MySharding中,如果断点查看availableTargetNames的值为t_order一个值,而不是t_order_1,t_order_2的时候,肯定是配置中
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=dsKaTeX parse error: Expected group after '_' at position 17: …>{1..2}.t_order_̲->{1…2}
写成了
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{1…2}.t_order
测试
/**
* 测试插入数据
*/
@Test
public void testOrderSharding(){
for(int i=0; i<100; i++){
Order order = new Order();
order.setUserId(32);
// order.setOrderId(2);
order.setOrderAmount(BigDecimal.TEN);
order.setOrderStatus(3);
orderMapper.insertSelective(order);
}
}
/**
* 测试查询数据
*/
@Test
public void testSelectOrderSharding(){
OrderExample orderExample = new OrderExample();
orderExample.createCriteria().andIdEqualTo(2).andUserIdEqualTo(22);
List<Order> orders = orderMapper.selectByExample(orderExample);
orders.forEach(e -> System.out.println(e.getId()+"---"+e.getUserId()));
}
不同的userId和Id会落到不同的库的不同的表中。
广播表
每个库里都有的表,也叫全局表
application.properties配置
#配置广播表(每个库里都有的表也叫全局表)
spring.shardingsphere.sharding.broadcast-tables=area
测试插入
@Test
public void testGlobal(){
Area area = new Area();
area.setId(2);
area.setName("上海");
areaMapper.insert(area);
}
结果发现,每个库里都插入了这条数据
测试查询
@Test
public void testSelectGlobal() {
AreaExample areaExample = new AreaExample();
areaExample.createCriteria().andIdEqualTo(2);
List<Area> areas = areaMapper.selectByExample(areaExample);
System.out.println("area.size() = "+areas.size());
}
绑定表
也叫子表,比如t_order_item订单详情表应该和t_order订单表,如果他们的user_id和order_id一样 这两张表的数据应该落在同一个库中,这样方便查询的时候join
注意点:t_order,t_order_item这两张表中同名字段就是两张表的默认关联关系,无法指定t_order的id字段对应t_order_item的order_id字段,所以只能把t_order的id字段,更名为order_id
application.properties配置
#配置绑定表(也叫子表, 要保证t_order,t_order_item两张表的分库分表策略一致)
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
# 分片策略: **注意有绑定关系的两张表分库分表的规则要保持一模一样**
# t_order表的真实数据节点是ds0下的t_order_1和t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order
# t_order_item表的真实数据节点是ds0下的t_order_item_1和t_order_item_2
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item
# t_order表 数据库选择策略,根据user_id字段
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
# t_order表 数据库选择策略,算法表达式user_id对2取余数
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# t_order_item表 数据库选择策略,根据user_id字段
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.sharding-column=user_id
# t_order_item表 数据库选择策略,算法表达式user_id对2取余数
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
## t_order表选择策略,根据id字段
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
## t_order表选择策略,算法表达式id对2取余
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2 + 1}
## t_order_item表选择策略,根据id字段
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
## t_order_item表选择策略,算法表达式id对2取余
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2 + 1}