写点什么

五分钟带你上手 ShardingJDBC 实现 MySQL 分库分表

作者:知识浅谈
  • 2022 年 8 月 01 日
  • 本文字数:2696 字

    阅读完需:约 9 分钟

五分钟带你上手ShardingJDBC实现MySQL分库分表

🍁 作者:知识浅谈,CSDN 签约讲师,后端领域优质创作者,阿里云技术博主,热爱分享创作

💒 公众号:知识浅谈

📌 擅长领域:全栈工程师、爬虫、ACM 算法

🔥 联系方式 vx:zsqtcc


🤞这次都给他拿下🤞为什么 MySQL 分库分表使用逐渐增多了? 主要是数据量逐渐增多产生了这些解决方案。


正菜来了🛴🛴🛴

🎈Mysql 环境

ds0:192.168.31.241ds1:192.168.31.242ds2:192.168.31.243 数据库:testdb

🎈项目配置

🍮依赖引入

<dependencies>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter</artifactId>        </dependency>        <dependency>            <groupId>org.apache.shardingsphere</groupId>            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>            <version>5.1.0</version>        </dependency>
<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.3</version> </dependency>
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
复制代码

🍮配置文件配置

# 配置真实数据源,ds{0..2}spring.shardingsphere.datasource.names=ds0,ds1,ds2
# 配置第 1 个数据源spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.31.241:3306/testdbspring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=root
# 配置第 2 个数据源spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.31.242:3306/testdbspring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=root
# 配置第 3 个数据源spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://192.168.31.243:3306/testdbspring.shardingsphere.datasource.ds2.username=rootspring.shardingsphere.datasource.ds2.password=root
# 标准分片表配置ds->{0..2}的含义是# 针对employee表新增数据时,有三个数据源ds0、ds1、ds2中都有employee表# spring.shardingsphere.rules.sharding.tables.employee.actual-data-nodes=ds$->{0..2}.employee
# 定义数据源的分片规则,按employee表的id % 3 取模得到数据应放在哪个数据源spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINEspring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=ds$->{id % 3}# 定义哪一个列用于生成主键 employee对应的是相应的表名spring.shardingsphere.rules.sharding.tables.employee.key-generate-strategy.column=id
# 定义employee表哪个是分片字段,这里按主键字段id,这个表示基于哪一个列进行分片spring.shardingsphere.rules.sharding.tables.employee.database-strategy.standard.sharding-column=id# 将employee表与分片规则database-inline绑定spring.shardingsphere.rules.sharding.tables.employee.database-strategy.standard.sharding-algorithm-name=database-inline
# 默认主键生成策略采用snowflakespring.shardingsphere.sharding.default-key-generate-strategy.xxx=snowflake
#SNOWFLAKE算法配置spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE#机器唯一标识spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=666#显示分库分表后执行的SQL语句spring.shardingsphere.props.sql-show=true
复制代码

🍮测试使用

使用 mybatis-plus 进行操作使用📐Mapper 文件


import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.itlaoqi.shardingjdbc.entity.Employee;
public interface EmployeeMapper extends BaseMapper<Employee> {
}
复制代码


📐Entity 文件


mport com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;
@TableName("employee")public class Employee { @TableId private Long id;
private String name;
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }}
复制代码


📐Test 类


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.itlaoqi.shardingjdbc.entity.Employee;import com.itlaoqi.shardingjdbc.mapper.EmployeeMapper;import org.junit.jupiter.api.Test;import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;import java.util.List;
@SpringBootTestpublic class EmployeeTestor { @Resource private EmployeeMapper employeeMapper; @Test public void testInsert(){ for(int i = 0 ; i < 10 ; i++) { Employee employee = new Employee(); employee.setName("MJ" + i); employeeMapper.insert(employee); } }
@Test public void testSelect(){ List<Employee> employees = employeeMapper.selectList(new QueryWrapper<>()); }}
复制代码


发布于: 刚刚阅读数: 3
用户头像

知识浅谈

关注

公众号:知识浅谈 2022.06.22 加入

🍁 作者:知识浅谈,CSDN签约讲师,后端领域优质创作者,阿里云社区技术博主,热爱分享创作 💒 公众号:知识浅谈 📌 擅长领域:全栈工程师、爬虫、ACM算法 🔥 联系方式vx:zsqtcc

评论

发布
暂无评论
五分钟带你上手ShardingJDBC实现MySQL分库分表_八月月更_知识浅谈_InfoQ写作社区