这篇文章主要讲解了“ShardingSphere5.0.0-alpha如何实现mysql分库分表”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“ShardingSphere5.0.0-alpha如何实现mysql分库分表”吧!
本文会基于 Springboot + mybatis + shardingsphere + mysql5.6 + druid 进行实战讲解
本文在上一篇文章[数据分表]的基础上增加了 分库 的功能
本文不会介绍 shardingsphere 以及分库分表的相关概念
本文采用的 shardingsphere 版本是 5.0.0-alpha, 具体见 pom 文件
本文涉及的源码请参考 分库
如果看官方文档时, 请选择对应的版本 !!!
文中涉及的源码可能会有误, 请以上传到 gitee 的源码为准.
我们有两个数据库 miaosha2 和 miaosha3, 每个数据库中都有 2 张被拆分过的用户表 user_info0 和 user_info1
当我们往用户表插数据时, 会按照一定的规则(根据自增id取模), 落到某个 miaosha 库中的某张 user_info 表中.
create database miaosha2;
DROP TABLE IF EXISTS `miaosha2`.`user_info0`;
CREATE TABLE `miaosha2`.`user_info0`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`username` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`email` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`phone` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(128) COLLATE utf8_bin NOT NULL,
`active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 7
DEFAULT CHARSET = utf8
COLLATE = utf8_bin;
DROP TABLE IF EXISTS `miaosha2`.`user_info1`;
CREATE TABLE `miaosha2`.`user_info1`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`username` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`email` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`phone` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(128) COLLATE utf8_bin NOT NULL,
`active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARSET = utf8
COLLATE = utf8_bin;
create database miaosha3;
DROP TABLE IF EXISTS `miaosha3`.`user_info0`;
CREATE TABLE `miaosha3`.`user_info0`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`username` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`email` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`phone` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(128) COLLATE utf8_bin NOT NULL,
`active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 7
DEFAULT CHARSET = utf8
COLLATE = utf8_bin;
DROP TABLE IF EXISTS `miaosha3`.`user_info1`;
CREATE TABLE `miaosha3`.`user_info1`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_label` varchar(32) COLLATE utf8_bin DEFAULT NULL,
`username` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`email` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`phone` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`password` varchar(128) COLLATE utf8_bin NOT NULL,
`active` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARSET = utf8
COLLATE = utf8_bin;
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.2.RELEASE</version>
<relativePath/>
</parent>
<groupId>com.nimo</groupId>
<artifactId>shardingsphere-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>shardingsphere-demo</name>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- shardingsphere -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-alpha</version>
</dependency>
<!-- 阿里数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.3</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
再次强调下, 本文采用的 shardingsphere 版本是 5.0.0-alpha. 不同版本配置会有差异.
本文在上一篇文章的基础上增加, 并修改了几个配置, 下面的源码中有标记出来
添加了一个数据源配置
添加了一个分库策略
添加了一个分库算法
server:
port: 8777
spring:
shardingsphere:
# 展示修改以后的sql语句
props:
sql-show: true
datasource:
# (这里增加了一个 ds1 的数据源)
names: ds0,ds1
common:
type: com.alibaba.druid.pool.DruidDataSource
ds0:
url: jdbc:mysql://127.0.0.1:3306/miaosha2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
username: root
password: '123456'
driver-class-name: com.mysql.cj.jdbc.Driver
# (新增的配置)
ds1:
url: jdbc:mysql://127.0.0.1:3306/miaosha3?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
username: root
password: '123456'
driver-class-name: com.mysql.cj.jdbc.Driver
rules:
sharding:
# 分布式序列算法配置
key-generators:
# 此处必须要配置,否则会导致报错
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
# 配置 user_info 表
tables:
user_info:
# 分库策略 (新增的配置)
database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: database-inline
# 配置user_info的分库分表的规则 (增加了数据源的配置)
actual-data-nodes: ds$->{0..1}.user_info$->{0..1}
# 单分片键的标准分片
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: table-inline
# 主键id生成策略(雪花算法)
key-generate-strategy:
key-generator-name: snowflake
column: id
# 配置分片算法
sharding-algorithms:
# 通过 id 取模的方式确定数据落到哪个库 (新增的配置)
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{id % 2}
# 通过 id 取模的方式确定数据落到哪个表
table-inline:
type: INLINE
props:
algorithm-expression: user_info$->{id % 2}
enabled: true
mybatis:
typeAliasesPackage: com.nimo.shardingdatabasedemo.entity
mapperLocations: classpath:mapper/*.xml
// sql
<insert id="addUser" parameterType="com.nimo.shardingdemo.entity.UserInfo">
insert into user_info(id, username, password) values (#{id}, #{username}, #{password})
</insert>
// 新增一个用户信息
@PostMapping("userinfo")
public Object addUserInfo(@RequestBody UserInfo userInfo) {
return userInfoMapper.addUser(userInfo);
}
curl -X POST --location "http://localhost:8777/userinfo" \
-H "Content-Type: application/json" \
-d "{
\"username\": \"wangbadan\",
\"password\": \"123456\"
}"
感谢各位的阅读,以上就是“ShardingSphere5.0.0-alpha如何实现mysql分库分表”的内容了,经过本文的学习后,相信大家对ShardingSphere5.0.0-alpha如何实现mysql分库分表这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。
原文链接:https://my.oschina.net/nimo10050/blog/5037099