ShardingSphere集成SpringBoot实现分库分表(二)-分库分表

最新推荐文章于 2025-06-27 14:41:17 发布

浅唱~幸福

最新推荐文章于 2025-06-27 14:41:17 发布

阅读量847

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.csdn.net/weixin_39643007/article/details/125853305

该博客介绍了如何使用Spring Boot结合ShardingSphere进行分库分表配置，具体包括application.yml配置、数据库脚本、实体类、Mapper接口以及测试用例。示例中，message表根据contact_id和msg_id的奇偶性进行分库分表，并展示了插入与查询操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.继上一篇文章的基础上进行实现分库分表操作 application.yml配置文件为：

spring:
  shardingsphere:
    datasource:
      names: ds-0,ds-1
      ds_0:
        url: jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: hzp123456
      ds_1:
        url: jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=false
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: root
        password: hzp123456
    sharding:
      tables:
        user:
          actual-data-nodes: ds-$->{0..1}.user
          database-strategy:
            standard:
              sharding-column: age
              precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker.id: 11
              executor.size: 4
              max.connections.size.per.query: 1
              check.table.metadata.enabled: false
        message:
          actual-data-nodes: ds-$->{0..1}.message_$->{0..1}
          database-strategy:
            standard:
              sharding-column: contact_id
              precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm
          table-strategy:
            standard:
              sharding-column: msg_id
              precise-algorithm-class-name: com.shardingsphere.algorithm.DefaultModuloPreciseShardingAlgorithm
          key-generator:
            column: msg_id
            type: SNOWFLAKE
            props:
              worker.id: 22
              executor.size: 4
              max.connections.size.per.query: 1
              check.table.metadata.enabled: false
    props:
      sql.show: true

主要对以上配置文件中的message表进行分库分表如以上根据message表中的contact_id进行分库根据contact_id的奇偶数判断分别入库到ds-0 ds-1中根据message表中的msg_id进行分表根据 msg_id的奇偶数判断分别入库到ds-0 ds-1中message_0，message_1 执行的先后顺序是先分库后分表

2.在库ds-0 ds-1中分别创建表message_0，message_1 创建脚本如下：

DROP TABLE IF EXISTS `message_0`;
CREATE TABLE `message_0` (
`msg_id` bigint(20) NOT NULL,
`contact_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
`user_tag` bit(1) NULL DEFAULT NULL,
`recall_time` datetime NULL DEFAULT NULL,
`content` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`push_msg` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`creation_time` datetime NULL DEFAULT NULL,
PRIMARY KEY (`msg_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `message_1`;
CREATE TABLE `message_1` (
`msg_id` bigint(20) NOT NULL,
`contact_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
`user_tag` bit(1) NULL DEFAULT NULL,
`recall_time` datetime NULL DEFAULT NULL,
`content` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`push_msg` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`creation_time` datetime NULL DEFAULT NULL,
PRIMARY KEY (`msg_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

3.创建Message实体类：

package com.shardingsphere.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * 发送消息内容
 */
@Data
public class Message implements Serializable {
    private static final long serialVersionUID = 2921554930924807501L;

    @TableId(type = IdType.ASSIGN_ID)
    private Long msgId;

    private Long contactId;

    private Long userId;

    private Boolean userTag;

    private Date recallTime;

    private String content;

    private String pushMsg;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date creationTime;
}

4.创建MessageMapper接口并继承mybatisPlus中的BaseMapper 代码如下：

package com.shardingsphere.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shardingsphere.entity.Message;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

/**
 * @Author 何志鹏
 * @Date 2022/7/18 10:59
 * @Version 1.0
 */
@Mapper
@Repository
public interface MessageMapper extends BaseMapper<Message> {


}

5.创建测试方法DemoTest：

package com.shardingsphere;
import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONUtil;
import com.shardingsphere.Mapper.MessageMapper;
import com.shardingsphere.entity.Message;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Date;

/**
 * @Author 何志鹏
 * @Date 2022/7/15 15:15
 * @Version 1.0
 */
@SpringBootTest
public class DemoTest {
    
    @Autowired
    private MessageMapper messageMapper;

    
    /**
     * 测试Message分库
     */
    @Test
    void addMessage() {
       for (int i = 0; i < 10000; i++) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    Message message = new Message();
                    int randomNum = (int) (Math.random() * 9000 + 1000);

                    Snowflake snowflake = IdUtil.createSnowflake(1, 1);
                    message.setContactId(snowflake.nextId()+randomNum);

                    message.setUserId(new Long(randomNum));
                    message.setUserTag(Boolean.TRUE);
                    message.setRecallTime(new Date());
                    message.setContent("测试测试");
                    message.setPushMsg("111111111111");
                    message.setCreationTime(new Date());
                    messageMapper.insert(message);
                }
            }).run();
        }


        //ds-0库
        Message ds_0_Message_0 = messageMapper.selectById(755815881623298048L);
        System.err.println("ds-0库Message_0表:"+JSONUtil.parseObj(ds_0_Message_0));
        Message ds_0_Message_1 = messageMapper.selectById(755815839168552961L);
        System.err.println("ds-0库Message_1表:"+JSONUtil.parseObj(ds_0_Message_1));
        //ds-1库
        Message ds_1_Message_0 = messageMapper.selectById(755815841399922688L);
        System.err.println("ds-1库Message_0表:"+JSONUtil.parseObj(ds_1_Message_0));
        Message ds_1_Message_1 = messageMapper.selectById(755815845208350721L);
        System.err.println("ds-1库Message_1表:"+JSONUtil.parseObj(ds_1_Message_1));


    }
}
6.查看数据库中的插入数据和查询的结果 如下：