说明:
Sharding-Jdbc是关系型数据库中间件,用于配置MySQL等数据库分片、读写分离等,属于客户端代理模式(增强版的JDBC驱动)。
官方文档:
MySQL0主机:192.168.233.141:3306
MySQL1主机:192.168.233.142:3306
安装/配置MySQL0主机+MySQL1主机:
https://blog.csdn.net/a526001650a/article/details/103930340
二、SpringBoot+Mybatis配置:
1.添加依赖包,pom.xml中导入:
<!-- 继承起步依赖spring-boot-starter-parent -->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.RELEASE</version>
</parent>
<dependencies>
<!-- 添加web依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- 添加mysql驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<!-- 添加mybatis依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<!-- 添加sharding-jdbc依赖 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-core-common</artifactId>
<version>4.0.0-RC1</version>
</dependency>
</dependencies>
2.创建实体类:
public class Order {
private Integer id;
private String name;
private Integer uid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", name='" + name + '\'' +
", uid=" + uid +
'}';
}
}
3.创建Dao类与Mapper映射文件:
(1)Dao类:
@Mapper //标记为Dao操作类
public interface OrderMapper {
void insert(Order order);
Order query(Integer id);
}
(2)Mapper映射文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yyh.mybatis.dao.OrderMapper"> <!-- 配置对象关系映射 -->
<insert id="insert" parameterType="com.yyh.mybatis.domain.Order"> <!-- id为方法名,parameterType传参类型-->
insert into t_order(id,name,uid) values(#{id},#{name},#{uid}) <!-- #{}类似?占位符,将对象属性值存入对应的同名列中 -->
</insert>
<select id="query" parameterType="int" resultType="com.yyh.mybatis.domain.Order"> <!-- resultType返回类型-->
select * from t_order where id=#{id};
</select>
</mapper>
4.创建启动类:
@SpringBootApplication //申明为引导类,类名自定义
public class MybatisApplication {
public static void main(String[] args) { //应用入口
SpringApplication.run(MybatisApplication.class, args); //运行自定义引导类
}
}
5.创建application.yml:
server:
port: 9998
#mybatis相关配置
mybatis:
type-aliases-package: com.yyh.mybatis.domain #实体类包扫描
mapper-locations: classpath:*Mapper.xml #加载dao类映射文件,这里为UserMapper.xml
#sharding-jdbc相关配置
spring:
profiles:
#active: db_shard #库分片,导入application-db_shard.yml
#active: db_table_shard #库分片+表分片,导入application-db_table_shard.yml
#active: read_write #读写分离,导入application-read_write.yml
active: db_shard_read_write #库分片+读写分离,导入application-db_shard_read_write.yml
6.创建Controller类(用于测试效果,可用junit替换):
@Controller
@RequestMapping("/order")
public class OrderController {
@Autowired //自动创建Dao实现类
private OrderMapper orderMapper;
@RequestMapping("/insert")
@ResponseBody
public String insert(int id, int uid) {
Order order = new Order();
order.setId(id);
order.setName("服务订单" + id);
order.setUid(uid);
orderMapper.insert(order);
return "插入成功, id=" + id + " uid=" + uid;
}
@RequestMapping("/query")
@ResponseBody
public String query(int id) {
Order order = orderMapper.query(id);
if (order == null) {
return "此订单不存在";
}
return order.toString();
}
}
三、Sharding-Jdbc配置(添加maven依赖在第二章):
1.数据库分片:
(1)创建数据库+表:
MySQL0主机:ds0数据库,t_order表(字段:id,name,uid)
MySQL1主机:ds1数据库,t_order表(字段:id,name,uid)
(2)创建application-db_shard.yml,按数据库分片配置如下:
#sharding-jdbc 库分片配置
spring:
shardingsphere:
#配置数据源
datasource:
names: ds0,ds1 #mysql节点名称列表,多个时以逗号隔开
#mysql节点0
ds0:
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.141:3306/ds0 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
#mysql节点1
ds1:
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.142:3306/ds1 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
#配置分片规则
sharding:
default-database-strategy: #配置默认
inline:
sharding-column: id
algorithm-expression: ds$->{id % 2}
#配置表列表
tables:
t_order: #配置t_order表(自定义表名,为mysql中的表名)的分片规则
actual-data-nodes: ds$->{0..1}.t_order #配置mysql节点与表,对应ds0和ds1
database-strategy: #库分片策略
inline:
shardingColumn: id #库分片健
algorithmExpression: ds$->{id % 2} #库分片算法
props:
sql:
show: true #打印sql语句
(3)浏览器访问URL,插入数据:
http://localhost:9998/order/insert?id=0&uid=0
http://localhost:9998/order/insert?id=1&uid=1
http://localhost:9998/order/insert?id=2&uid=2
(4)使用Navicat查看MySQL0主机与MySQL1主机ds0/ds1数据库的t_order表中数据,如图所示:
2.数据库分片+表分片:
(1)创建数据库+表:
MySQL0主机:ds0数据库,t_order0表(字段:id,name,uid)
MySQL0主机:ds0数据库,t_order1表(字段:id,name,uid)
MySQL0主机:ds1数据库,t_order0表(字段:id,name,uid)
MySQL0主机:ds1数据库,t_order1表(字段:id,name,uid)
(2)创建application-db_table_shard.yml,按库分片+表分片配置如下:
#sharding-jdbc 库分片+表分片配置
spring:
shardingsphere:
mode:
type: Standalone
repository:
type: File
overwrite: true
#配置数据源
datasource:
names: ds0,ds1 #mysql节点名称列表,多个时以逗号隔开
#mysql节点0
ds0:
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.141:3306/ds0 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
#mysql节点1
ds1:
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.141:3306/ds1 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
#配置分片规则
sharding:
default-database-strategy:
inline:
sharding-column: uid
algorithm-expression: ds$->{uid % 2}
#配置表列表
tables:
t_order: #配置t_order表(自定义表名,为mysql中的表名)的分片规则
actual-data-nodes: ds$->{0..1}.t_order$->{0..1} #配置mysql节点与表,对应ds0和ds1,t_order0和t_order1
database-strategy: #库分片策略
inline:
shardingColumn: uid #库分片健
algorithmExpression: ds$->{uid % 2} #库分片规则
table-strategy: #表分片策略
inline:
sharding-column: id #表分片健,id为t_order表中的id字段
algorithm-expression: t_order$->{id % 2} #表分片规则
#主键生成器
# key-generator:
# #自增主键字段
# column: id
# type: SNOWFLAKE
#打印sql语句
props:
sql:
show: true
(3)浏览器访问URL,插入数据:
http://localhost:9998/order/insert?id=0&uid=1000
http://localhost:9998/order/insert?id=1&uid=1000
http://localhost:9998/order/insert?id=2&uid=1000
http://localhost:9998/order/insert?id=0&uid=1001
http://localhost:9998/order/insert?id=1&uid=1001
http://localhost:9998/order/insert?id=2&uid=1001
(4)使用Navicat查看MySQL0主机ds0/ds1数据库的t_order0/t_order1表中数据,如图所示:
3.读写分离:
(1)创建数据库+表:
MySQL0主机(master写节点):ds0数据库,t_order表(字段:id,name,uid)
MySQL1主机(slave读节点): ds0数据库,t_order表(字段:id,name,uid)
(2)MySQL主从配置:
https://blog.csdn.net/a526001650a/article/details/121641167
(3)创建application-read_write.yml,读写分离配置如下:
#sharding-jdbc 读写分离配置
spring:
shardingsphere:
#配置数据源
datasource:
names: master,slave #mysql节点名称列表,多个时以逗号隔开
#mysql写节点
master:
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.141:3306/ds0 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
#mysql读节点
slave:
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.142:3306/ds0 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
#配置读写分离
masterslave:
load-balance-algorithm-type: round_robin
name: ms
master-data-source-name: master #配置写节点
slave-data-source-names: #配置读节点列表,每个节点占一行,以- 开头
- slave
props:
sql:
show: true #打印sql语句
(4)浏览器访问URL,插入数据:
http://localhost:9998/order/insert?id=0&uid=0
(5)使用Navicat查看MySQL0主机与MySQL1主机ds0/ds1数据库的t_order表中数据,如图所示:
4.数据库分片+读写分离:
(1)创建数据库+表:
MySQL0主机(master0写节点): ds0数据库,t_order表(字段:id,name,uid)
MySQL0主机(master1写节点): ds1数据库,t_order表(字段:id,name,uid)
MySQL1主机(master0slave读节点): ds0数据库,t_order表(字段:id,name,uid)
MySQL1主机(master1slave读节点): ds1数据库,t_order表(字段:id,name,uid)
(2)MySQL主从配置:
https://blog.csdn.net/a526001650a/article/details/121641167
(3)创建application-db_shard_read_write.yml,读写分离配置如下:
#sharding-jdbc 库分片配置
spring:
shardingsphere:
#配置数据源
datasource:
names: master0,master1,master0slave,master1slave #mysql节点名称列表,多个时以逗号隔开,master0与master1为库分片,master0与master0slave为主从关系(读写分离)
master0: #master0写节点,与master1为库分片
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.141:3306/ds0 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
master0slave: #master0对应的读节点
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.142:3306/ds0 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
master1: #master1写节点,与master0为库分片
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.141:3306/ds1 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
master1slave: #master1对应的读节点
type: com.zaxxer.hikari.HikariDataSource #连接池
driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
jdbc-url: jdbc:mysql://192.168.233.142:3306/ds1 #mysql的url
username: root #mysql登录账号
password: root123456 #mysql登录密码
sharding:
#配置分片规则
default-database-strategy: #配置默认
inline:
sharding-column: id
algorithm-expression: ds$->{id % 2}
tables: #配置表列表
t_order: #配置t_order表(自定义表名,为mysql中的表名)的分片规则
actual-data-nodes: ds$->{0..1}.t_order #配置mysql节点与表,对应ds0和ds1
database-strategy: #库分片策略
inline:
shardingColumn: id #库分片健
algorithmExpression: ds$->{id % 2} #库分片规则
#配置读写分离
master-slave-rules:
ds0:
masterDataSourceName: master0 #写节点
slaveDataSourceNames: #读节点列表,每个读节点占一行,以-开头
- master0slave
ds1:
masterDataSourceName: master1 #写节点
slaveDataSourceNames: #读节点列表,每个读节点占一行,以-开头
- master1slave
props:
sql:
show: true #打印sql语句
(4)使用Navicat查看MySQL0主机与MySQL1主机的表中数据,如图所示:
http://localhost:9998/order/insert?id=0&uid=0
http://localhost:9998/order/insert?id=1&uid=1
http://localhost:9998/order/insert?id=2&uid=2
(5)使用Navicat查看MySQL0主机ds0/ds1与MySQL1主机ds0/ds1的表中数据,如图所示:
四、其他配置:
1.全局表配置(广播表,存一张表,不分片),以下配置需转换成yml格式:
spring.shardingsphere.sharding.broadcast-tables=表1,表2... #多个表以逗号隔开
2.子表配置(绑定表,将父表与子表绑定,如order表与order_item表),以下配置需转换成yml格式:
spring.shardingsphere.sharding.binding-tables=父表,子表