ShardingSphere-mysql 垂直分库

最新推荐文章于 2024-09-19 11:55:18 发布

qq_39093474

最新推荐文章于 2024-09-19 11:55:18 发布

阅读量96

点赞数

分类专栏：数据库 springboot实际应用文章标签： mysql 数据库

springboot实际应用同时被 2 个专栏收录

36 篇文章 4 订阅

订阅专栏

数据库

27 篇文章 2 订阅

订阅专栏

本文详细描述了如何在MySQL数据库中通过垂直分库策略，使用ShardingSphere框架进行表划分，创建实体类、Mapper接口，并配置数据源和分片规则，最后通过测试展示了数据的插入操作。

摘要由CSDN通过智能技术生成

文章目录

一、垂直分库

一、垂直分库

垂直分库，将业务上不相关，即不需要join操作的表划分到不同的库。

1.1 数据库规划

mysql3服务器上mytest数据库有student表
mysql5服务器上user_db数据库有t_user表

1.2 创建表

mysql3上

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

 `id` int NOT NULL AUTO_INCREMENT,

 `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

 `age` int NULL DEFAULT NULL,

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

mysql5上

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (

 `user_id` bigint NOT NULL AUTO_INCREMENT,

 `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

 `nick_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

 PRIMARY KEY (`user_id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

1.3 创建实体类

@TableName("student")
 @Data
 public class Student {
   @TableId(type = IdType.AUTO)
   private int id;
   private String name;
   private int age;
 }

 

@Data
 @TableName("t_user")
 public class UserA {
   @TableId(type = IdType.AUTO)
   private Integer userId;
   private String username;
   private String nickName;
 }

1.4 创建mapper

public interface StudentMapper extends BaseMapper<Student> {
 
 }

 

public interface UserAMapper extends BaseMapper<UserA> {
 }

1.5 配置文件application.properties

# 运行模式
 spring.shardingsphere.mode.type=Memory
 
 # 配置真实数据源
 spring.shardingsphere.datasource.names=student_ds,user_ds
 
 # 配置第 1 个数据源
 spring.shardingsphere.datasource.student_ds.type=com.zaxxer.hikari.HikariDataSource
 spring.shardingsphere.datasource.student_ds.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.student_ds.jdbc-url=jdbc:mysql://mysql3:3306/mytest
 spring.shardingsphere.datasource.student_ds.username=root
 spring.shardingsphere.datasource.student_ds.password=
 
 # 配置第 1 个数据源
 spring.shardingsphere.datasource.user_ds.type=com.zaxxer.hikari.HikariDataSource
 spring.shardingsphere.datasource.user_ds.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.user_ds.jdbc-url=jdbc:mysql://mysql5:3306/user_db
 spring.shardingsphere.datasource.user_ds.username=root
 spring.shardingsphere.datasource.user_ds.password=
 
 # 标准分片表配置
 # 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。
 # 缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
 spring.shardingsphere.rules.sharding.tables.student.actual-data-nodes=student_ds.student
 spring.shardingsphere.rules.sharding.tables.t_user.actual-data-nodes=user_ds.t_user
 
 # 打印SQl
 spring.shardingsphere.props.sql-show=true

1.6 测试

@SpringBootTest
 @Slf4j
 public class VerticalDBTest {
 
   @Autowired
   private StudentMapper studentMapper;
   @Autowired
   private UserAMapper userAMapper;
 
   @Test
   @Transactional
   public void testInert() {
     Student student = new Student();
     student.setName("张三");
     student.setAge(20);
     studentMapper.insert(student);
 
     UserA userA = new UserA();
     userA.setUsername("zhangsan");
     userA.setNickName("大牛");
     userAMapper.insert(userA);
   }
 
 
 }

输出

2022-11-20 21:58:35.181 INFO 20440 --- [      main] ShardingSphere-SQL            : Logic SQL: INSERT INTO student ( name,

age ) VALUES ( ?,

? )

2022-11-20 21:58:35.181 INFO 20440 --- [      main] ShardingSphere-SQL            : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)

2022-11-20 21:58:35.181 INFO 20440 --- [      main] ShardingSphere-SQL            : Actual SQL: student_ds ::: INSERT INTO student ( name,

age ) VALUES (?, ?) ::: [张三, 20]

2022-11-20 21:58:35.260 INFO 20440 --- [      main] ShardingSphere-SQL            : Logic SQL: INSERT INTO t_user ( username,

nick_name ) VALUES ( ?,

? )

2022-11-20 21:58:35.261 INFO 20440 --- [      main] ShardingSphere-SQL            : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)

2022-11-20 21:58:35.261 INFO 20440 --- [      main] ShardingSphere-SQL            : Actual SQL: user_ds ::: INSERT INTO t_user ( username,

nick_name ) VALUES (?, ?) ::: [zhangsan, 大牛]