多表关联
- 项目源码
- ShardingSphere 5.1.1 读写分离实战 | sharding-jdbc | 详细步骤
- mysql 垂直分表实战 | sharding-jdbc | 详细步骤
- mysql 垂直分库 | sharding-jdbc 实战详细步骤
- mysql 水平分表（不分库）| shardingsphere 实战详细步骤
- mysql 水平分表（分库）| sharding-jdbc实战详细步骤
- 多表关联 shardingsphere 实战详细步骤
- 广播表 shardingsphere实战详细步骤
1.1数据库规划
mysql3的order_db0上有t_order_0、t_order_1、t_order_item_0、t_order_ item _1
mysql5的order_db1上有t_order_2、t_order_3、t_order_item_2、t_order_ item _3
根据user_id选择是哪台服务器的数据库，在根据order_id选择到具体的哪张表。order表、order_item表的分片规则是一样的。
1.2创建表
1.2.1 mysql3的order_db0
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_order_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
`order_id` bigint NOT NULL,
`user_id` bigint NULL DEFAULT NULL,
`amount` decimal(10, 2) NULL DEFAULT NULL,
`merchant_id` bigint NULL DEFAULT NULL,
`order_status_id` int,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
`order_id` bigint NOT NULL,
`user_id` bigint NULL DEFAULT NULL,
`amount` decimal(10, 2) NULL DEFAULT NULL,
`merchant_id` bigint NULL DEFAULT NULL,
`order_status_id` int,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE `t_order_item_0` (
`order_item_id` bigint NOT NULL,
`order_id` bigint NULL,
`user_id` bigint NULL,
`merchant_id` bigint NULL,
`amount` decimal(10, 2) NULL,
PRIMARY KEY (`order_item_id`)
);
CREATE TABLE `t_order_item_1` (
`order_item_id` bigint NOT NULL,
`order_id` bigint NULL,
`user_id` bigint NULL,
`merchant_id` bigint NULL,
`amount` decimal(10, 2) NULL,
PRIMARY KEY (`order_item_id`)
);
###
1.2.2 mysql5的order_db1
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_order_2
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
`order_id` bigint NOT NULL,
`user_id` bigint NULL DEFAULT NULL,
`amount` decimal(10, 2) NULL DEFAULT NULL,
`merchant_id` bigint NULL DEFAULT NULL,
`order_status_id` int,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_order_3`;
CREATE TABLE `t_order_3` (
`order_id` bigint NOT NULL,
`user_id` bigint NULL DEFAULT NULL,
`amount` decimal(10, 2) NULL DEFAULT NULL,
`merchant_id` bigint NULL DEFAULT NULL,
`order_status_id` int,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE `t_order_item_2` (
`order_item_id` bigint NOT NULL,
`order_id` bigint NULL,
`user_id` bigint NULL,
`merchant_id` bigint NULL,
`amount` decimal(10, 2) NULL,
PRIMARY KEY (`order_item_id`)
);
CREATE TABLE `t_order_item_3` (
`order_item_id` bigint NOT NULL,
`order_id` bigint NULL,
`user_id` bigint NULL,
`merchant_id` bigint NULL,
`amount` decimal(10, 2) NULL,
PRIMARY KEY (`order_item_id`)
);
1.3创建实体类
@TableName("t_order")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {
@TableId(type = IdType.ASSIGN_ID)
private Long orderId;
private Long userId;
private Long merchantId;
private BigDecimal amount;
private Integer orderStatusId;
}
1.4创建mapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.readwrite.model.Order;
public interface OrderMapper extends BaseMapper<Order> {
}
public interface OrderItemMapper extends BaseMapper<OrderItem> {
}
1.5配置分片
绑定表
指分片规则一致的一组分片表。 使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。 例如：t_order 表和 t_order_item 表，均按照 order_id 分片，并且使用 order_id 进行关联，则此两张表互为绑定表关系。 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。
# 运行模式
spring.shardingsphere.mode.type=Memory
# 配置真实数据源
spring.shardingsphere.datasource.names=order_ds0,order_ds1
# 配置第 1 个数据源
spring.shardingsphere.datasource.order_ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.order_ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.order_ds0.jdbc-url=jdbc:mysql://mysql3:3306/order_db0
spring.shardingsphere.datasource.order_ds0.username=root
spring.shardingsphere.datasource.order_ds0.password=
# 配置第 1 个数据源
spring.shardingsphere.datasource.order_ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.order_ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.order_ds1.jdbc-url=jdbc:mysql://mysql5:3306/order_db1
spring.shardingsphere.datasource.order_ds1.username=root
spring.shardingsphere.datasource.order_ds1.password=
# 标准分片表配置
# 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。
# 缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=order_ds0.t_order_0,order_ds0.t_order_1,order_ds1.t_order_2,order_ds1.t_order_3
spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=order_ds0.t_order_item_0,order_ds0.t_order_item_1,order_ds1.t_order_item_2,order_ds1.t_order_item_3
# 分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
# 用于单分片键的标准分片场景
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=my_inline
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-name=my_inline
# 分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=my_split
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=my_split
# 分片算法配置
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.my_inline.type=INLINE
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.my_inline.props.algorithm-expression=order_ds$->{ user_id % 2}
# 绑定表规则列表
spring.shardingsphere.rules.sharding.binding-tables[0]=t_order,t_order_item
# 分片算法配置
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.type=CLASS_BASED
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.props.sharding-count=2
spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.props.algorithmClassName=com.example.readwrite.alg.MyShardingAlgorithm
# 打印SQl
spring.shardingsphere.props.sql-show=true
1.6测试
1.6.1 插入
@SpringBootTest
@Slf4j
public class MultiJoinTest {
@Autowired
private OrderMapper orderMapper;
@Autowired
private OrderItemMapper orderItemMapper;
@Test
public void testInert() {
for (long i = 0; i < 12; i++) {
Order order = new Order(null, i + 200, 1L, new BigDecimal(0),1);
orderMapper.insert(order);
for (long j = 0; j < 3; j++) {
OrderItem orderItem = new OrderItem(null, order.getOrderId(), order.getUserId(), order.getMerchantId(), new BigDecimal(2));
order.setAmount(order.getAmount().add(orderItem.getAmount()));
orderItemMapper.insert(orderItem);
}
Order order1 = new Order();
order1.setOrderId(order.getOrderId());
order1.setAmount(order.getAmount());
orderMapper.updateById(order1);
}
}
}
输出
1.6.2 关联查询
1.6.2.1 创建实体类
@Data
public class OrderVo {
private Long orderId;
private BigDecimal amount;
}
1.6.2.2 修改mapper
public interface OrderMapper extends BaseMapper<Order> {
List<OrderVo> selectAllOrderVo();
}
1.6.2.3 增加xml
在resources下创建mapper文件夹，在mapper下创建OrderMapper.xml
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lcNt6IW5-1669027659102)(file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/01/clip_image010.jpg)]
写入
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.readwrite.mapper.OrderMapper">
<select id="selectAllOrderVo" resultType="com.example.readwrite.model.OrderVo">
select o.order_id, sum(oi.amount) as amount
from t_order as o join t_order_item as oi on o.order_id = oi.order_id
group by o.order_id
</select>
</mapper>
1.6.2.4 测试
@Test
public void testJoin() {
List<OrderVo> orderVos = orderMapper.selectAllOrderVo();
*log*.info(String.*valueOf*(orderVos));
}
输出显示，order表与order_item表一一对应。
2022-11-21 12:45:38.731 INFO 16192 --- [ main] ShardingSphere-SQL : Logic SQL: select o.order_id, sum(oi.amount) as amount
from t_order as o join t_order_item as oi on o.order_id = oi.order_id
group by o.order_id
2022-11-21 12:45:38.731 INFO 16192 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-21 12:45:38.732 INFO 16192 --- [ main] ShardingSphere-SQL : Actual SQL: order_ds0 ::: select o.order_id, sum(oi.amount) as amount
from t_order_0 as o join t_order_item_0 as oi on o.order_id = oi.order_id
group by o.order_id ORDER BY o.order_id ASC
2022-11-21 12:45:38.732 INFO 16192 --- [ main] ShardingSphere-SQL : Actual SQL: order_ds0 ::: select o.order_id, sum(oi.amount) as amount
from t_order_1 as o join t_order_item_1 as oi on o.order_id = oi.order_id
group by o.order_id ORDER BY o.order_id ASC
2022-11-21 12:45:38.732 INFO 16192 --- [ main] ShardingSphere-SQL : Actual SQL: order_ds1 ::: select o.order_id, sum(oi.amount) as amount
from t_order_2 as o join t_order_item_2 as oi on o.order_id = oi.order_id
group by o.order_id ORDER BY o.order_id ASC
2022-11-21 12:45:38.732 INFO 16192 --- [ main] ShardingSphere-SQL : Actual SQL: order_ds1 ::: select o.order_id, sum(oi.amount) as amount
from t_order_3 as o join t_order_item_3 as oi on o.order_id = oi.order_id
group by o.order_id ORDER BY o.order_id ASC
2022-11-21 12:45:38.849 INFO 16192 --- [ main] com.example.readwrite.MultiJoinTest : [OrderVo(orderId=1594547494870622210, amount=6.00), OrderVo(orderId=1594547500436463618, amount=6.00), OrderVo(orderId=1594547500566487042, amount=6.00), OrderVo(orderId=1594547500692316161, amount=6.00), OrderVo(orderId=1594547500818145281, amount=6.00), OrderVo(orderId=1594547500943974401, amount=6.00), OrderVo(orderId=1594547501069803522, amount=6.00), OrderVo(orderId=1594547501195632642, amount=6.00), OrderVo(orderId=1594547501258547202, amount=6.00), OrderVo(orderId=1594547501384376323, amount=6.00), OrderVo(orderId=1594547501510205442, amount=6.00), OrderVo(orderId=1594547501636034561, amount=6.00)]
1259
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
