绑定表:就是mycat的ER表。
如我们的order表进行了数据分片,那么order_item也要分片,同时要求分片的数据不会混乱,记录order_id = 1落入t_order1,那他对应的明细要落t_order_item_1。
查询时就是t_order1 join t_order_item_1,否则会出现笛卡尔积现象。
yml:
sharding:
tables:
t_order:
actualDataNodes: ds0.t_order$->{0..1}
table-strategy:
inline:
sharding-column: order_id
#根据字段值求模
algorithm-expression: t_order$->{order_id % 2}
key-generator:
column: order_id
type: SNOWFLAKE
props:
worker:
id: ${workerId}
max:
vibration:
offset: 1
t_order_item:
actualDataNodes: ds0.t_order_item$->{0..1}
table-strategy:
inline:
sharding-column: order_id
#根据字段值求模
algorithm-expression: t_order_item$->{order_id % 2}
key-generator:
column: id
type: SNOWFLAKE
props:
worker:
id: ${workerId}
max:
vibration:
offset: 1
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_adress
PS:注意位置,yml格式中bindingTables和tables是同级的。
数据表(t_order在之前inline分片中已展示):
@Data
@TableName("t_order_item")
public class OrderItemPo implements Serializable {
@TableId(value = "id",type = IdType.AUTO)
private Long id;
@TableField(value = "order_id")
private Long orderId;
@TableField("goods_name")
private String goodsName;
@TableField("quantity")
private Integer quantity;
@TableField("goods_price")
private BigDecimal goodsPrice;
}
测试:
@Test
void insertOrderDemo(){
String preCode = "ack_";
for (int i = 1; i <= 10; i++) {
OrderPo orderPo = new OrderPo(preCode + i, new Random().nextInt(10) + i, BigDecimal.TEN);
orderMapper.insert(orderPo);
orderItemMapper.insert(new OrderItemPo(orderPo.getOrderId(),"日化用品",10,BigDecimal.TEN));
}
}
全局表:分库时每个库都需要使用且数据量较小的表,此时跨库查询太过影响性能,选择每个库都创建一个相同的表,如字典表。
yml:
tables:
t_adress:
actualDataNodes: ds$->{0..1}.t_adress #这里是多个库,表只有一张
keyGenerator:
column: id
type: SNOWFLAKE
props:
worker:
id: ${workerId}
max:
vibration:
offset: 1
broadcastTables: #与tables同一级别,位置不能错
- t_adress
数据表:
@Data
@TableName("t_adress")
public class AdressPo implements Serializable {
@TableId(value = "id",type = IdType.AUTO)
private Long id;
@TableField("adress")
private String adress;
@TableField("code")
private String code;
}
测试:
@Test
void insertAdressDemo(){
String preCode = "ack_";
for (int i = 1; i <= 10; i++) {
//PS:执行一次,两个数据库都会插入
adressMapper.insert(new AdressPo("一个街道",preCode+i));
}
}