mysql 水平分表（分库）| sharding-jdbc实战详细步骤

tong@已存在

已于 2022-11-22 12:47:16 修改

阅读量809

点赞数 1

分类专栏： sharding-jdbc 文章标签： mysql 数据库 java

于 2022-11-21 18:44:46 首次发布

本文链接：https://blog.csdn.net/2201_75287294/article/details/127970179

版权

sharding-jdbc 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

水平分表（分库）

当单表记录达到500万条或磁盘空间占用达2GB时需要考虑水平分表。水平分表是按行切分为多个表，若放在同一个服务器里能够满足性能要求就不用分库。若不能满足要求就要分库了，将表放在不同的服务器上。

1.1数据库规划

根据user_id选择是哪台服务器的数据库，在根据order_id选择到具体的哪张表。

在这里插入图片描述

1.2创建表

1.2.1 mysql3的order_db0

在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;

1.2.2 mysql5的order_db1

在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;

1.3创建实体类

创建Order

@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

创建OrderMapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
 import com.example.readwrite.model.Order;
 
 public interface OrderMapper extends BaseMapper<Order> {
 }

1.5自定义分片算法

因为MOD算法只能处理t_order_0、t_order_1这样从零开始的，无法处理t_order_2、t_order_3序号从2开始的，MOD算法的源码是ModShardingAlgorithm，比较简单，只需要稍加修改就能符合我们的需求。

import com.google.common.base.Preconditions;
 import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
 import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
 import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
 import java.util.Collection;
 import java.util.Properties;
 
 public class MyShardingAlgorithm implements StandardShardingAlgorithm<Comparable<?>> {
 
   private static final String *SHARDING_COUNT_KEY* = "sharding-count";
   private Properties props = new Properties();
   private int shardingCount;
 
   public MyShardingAlgorithm() {
   }
 
   @Override
   public void init() {
     this.shardingCount = this.getShardingCount();
   }
 
   @Override
   public String doSharding(Collection<String> collection, PreciseShardingValue<Comparable<?>> preciseShardingValue) {
     String[] strings = collection.toArray(new String[0]);
     int idx = (int) (this.getLongValue(preciseShardingValue.getValue()) % (long)this.shardingCount);
     return strings[idx];
   }
 
   @Override
   public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Comparable<?>> rangeShardingValue) {
     return null;
   }
 
   private long getLongValue(Comparable<?> value) {
     return value instanceof Number ? ((Number)value).longValue() : Long.*parseLong*(value.toString());
   }
 
   private int getShardingCount() {
     Preconditions.*checkArgument*(this.props.containsKey(*SHARDING_COUNT_KEY*), "Sharding count cannot be null.");
     return Integer.*parseInt*(this.props.get(*SHARDING_COUNT_KEY*).toString());
   }
 
   @Override
   public String getType() {
     return "CLASS_BASED";
   }
 
 
   public void setProps(Properties props) {
     this.props = props;
   }
 
 }

1.6配置分片

# 运行模式
 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_ds1.t_order_2
 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.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.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.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.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.7测试

@SpringBootTest
 @Slf4j
 public class HorizontalSplitTest {

   @Autowired
   private OrderMapper orderMapper;

   @Test
   @Transactional
   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);
     }
   }

 }

tong@已存在

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 水平分表（分库）| sharding-jdbc实战详细步骤

因为MOD算法只能处理t_order_0、t_order_1这样从零开始的，无法处理t_order_2、t_order_3序号从2开始的，MOD算法的源码是ModShardingAlgorithm，比较简单，只需要稍加修改就能符合我们的需求。
复制链接

扫一扫

专栏目录