水平分表(不分库)
- 项目源码
- ShardingSphere 5.1.1 读写分离实战 | sharding-jdbc | 详细步骤
- mysql 垂直分表实战 | sharding-jdbc | 详细步骤
- mysql 垂直分库 | sharding-jdbc 实战详细步骤
- mysql 水平分表(不分库)| shardingsphere 实战详细步骤
- mysql 水平分表(分库)| sharding-jdbc实战详细步骤
- 多表关联 shardingsphere 实战详细步骤
- 广播表 shardingsphere实战详细步骤
1.1数据库规划
在同一个mysql服务器上的订单表t_order水平拆分为t_order_0、t_order_1,根据user_id取模分片,各放一半数据。注意表的order_id不能自增,需要应用自己设置order_id值。
1.2创建表
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;
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> {
}
1.5配置分片
# 运行模式
spring.shardingsphere.mode.type=Memory
# 配置真实数据源
spring.shardingsphere.datasource.names=order_ds
# 配置第 1 个数据源
spring.shardingsphere.datasource.order_ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.order_ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.order_ds.jdbc-url=jdbc:mysql://mysql3:3306/mytest
spring.shardingsphere.datasource.order_ds.username=root
spring.shardingsphere.datasource.order_ds.password=
# 标准分片表配置
# 由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持 inline 表达式。
# 缺省表示使用已知数据源与逻辑表名称生成数据节点,用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=order_ds.t_order_0,order_ds.t_order_1
# 用于单分片键的标准分片场景
# 分表策略
# 分片列名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=user_id
# 分片算法名称
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=alg_mod
# 分片算法配置
# 分片算法类型
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.type=MOD
# 分片算法属性配置
spring.shardingsphere.rules.sharding.sharding-algorithms.alg_mod.props.sharding-count=2
# 打印SQl
spring.shardingsphere.props.sql-show=true
1.6测试
@SpringBootTest
@Slf4j
public class OrderMapperTest {
@Autowired
private OrderMapper orderMapper;
@Test
public void testInert() {
for (long i = 0; i < 12; i++) {
Order order = new Order(null, i + 200, 1L, new BigDecimal(200 + i),1);
orderMapper.insert(order);
}
}
}
输出显示,user_id为偶数的数据在t_order_0上,奇数的则在t_order_1上。