mysql 水平分表（不分库）| shardingsphere 实战详细步骤

水平分表（不分库）

• 项目源码
• 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上。