目录
前提条件
- 准备三个数据库, 其中两个需要MySQL主从搭建。
- MySQL、SpringBoot构建
- Github 代码下载
DDL
与之前的文章,稍有修改,请按如下:
CREATE TABLE `area` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_1` (
`order_id` int NOT NULL,
`order_amount` decimal(10,2) NOT NULL,
`order_status` int NOT NULL,
`user_id` int NOT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_2` (
`order_id` int NOT NULL,
`order_amount` decimal(10,2) NOT NULL,
`order_status` int NOT NULL,
`user_id` int NOT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_item_1` (
`id` int NOT NULL,
`order_id` int NOT NULL,
`pruduct_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`num` int NOT NULL,
`user_id` int NOT NULL COMMENT '用户id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_item_2` (
`id` int NOT NULL,
`order_id` int NOT NULL,
`pruduct_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`num` int NOT NULL,
`user_id` int NOT NULL COMMENT '用户id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
全部库的DDL一模一样
编码部分
一、Spring命名空间配置 - 读写分离
Maven 依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.0.0-RC2</version>
</dependency>
sharding-jdbc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://shardingsphere.apache.org/schema/shardingsphere/sharding
http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
http://shardingsphere.apache.org/schema/shardingsphere/masterslave
http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd
">
<!-- MySQL数据源 -->
<bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="username" value="eddie" />
<property name="password" value="Abc@123456" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.8.246/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/>
</bean>
<!-- MySQL已经搭建好主从关系, 并非ShardingJdbc做的 -->
<bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="username" value="eddie" />
<property name="password" value="Abc@123456" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.8.245/sharding_order?serverTimezone=Asia/Shanghai&useSSL=false"/>
</bean>
<bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="username" value="eddie" />
<property name="password" value="Abc@123456" />
<property name="jdbcUrl" value="jdbc:mysql://192.168.8.247/shard_order?serverTimezone=Asia/Shanghai&useSSL=false"/>
</bean>
<!-- 主从负载均衡, 策略:随机 -->
<master-slave:load-balance-algorithm id="msStrategy" type="random" />
<!-- sharding-jdbc数据源 -->
<sharding:data-source id="sharding-data-source">
<!-- 多个数据源用逗号分割 -->
<sharding:sharding-rule data-source-names="ds0,slave0,ms1">
<!-- 主从关系 -->
<sharding:master-slave-rules>
<sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0" strategy-ref=""/>
</sharding:master-slave-rules>
<sharding:table-rules>
<!-- 逻辑表名称 logic-table="t_order" 对应Mapper.xml里面的表名 -->
<!-- 数据节点 actual-data-nodes="ds$->{0..1}.t_order_$->{1..2}" -->
<!-- 数据库分片策略 database-strategy-ref="databaseStrategy" -->
<!-- 数据表分片策略 table-strategy-ref="tableStrategy" -->
<sharding:table-rule logic-table="t_order"
actual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"
database-strategy-ref="databaseStrategy"
table-strategy-ref="tableStrategy"
/>
</sharding:table-rules>
<!-- 广播表规则列表 -->
<sharding:broadcast-table-rules>
<sharding:broadcast-table-rule table="area"/>
</sharding:broadcast-table-rules>
</sharding:sharding-rule>
</sharding:data-source>
<!-- 数据库分片规则 -->
<sharding:inline-strategy id="databaseStrategy"
sharding-column="user_id"
algorithm-expression="ms$->{user_id % 2}"
/>
<!-- 数据表分片规则 -->
<!-- id取模,可能会出现寻找到 t_order_0 的表,但实际上是没有的,所以 +1 -->
<sharding:inline-strategy id="tableStrategy"
sharding-column="id"
algorithm-expression="t_order_$->{id % 2 + 1}"
/>
<!-- 设置 mybatis 数据源 -->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="sharding-data-source"/>
<property name="mapperLocations" value="classpath*:/mybatis/*.xml" />
</bean>
</beans>
二、SpringBoot Start 配置- 读写分离
三者关系:
- ds0, slave0 读写分离
- ds0,ms1 数据分片
Maven 依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
application.properties
####################################################
#
# 博客:blog.eddilee.cn
# 备注:为了方便查看,使用 properties 而不是 yml 格式
#
####################################################
# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,slave0,ms1
# 配置第 1 个数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://192.168.8.246/sharding_order
spring.shardingsphere.datasource.ds0.username=eddie
spring.shardingsphere.datasource.ds0.password=Abc@123456
# 配置第 2 个数据源
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.8.245/sharding_order
spring.shardingsphere.datasource.slave0.username=eddie
spring.shardingsphere.datasource.slave0.password=Abc@123456
# 配置第 3 个数据源
spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.8.247/shard_order
spring.shardingsphere.datasource.ms1.username=eddie
spring.shardingsphere.datasource.ms1.password=Abc@123456
# 配置 主从关系
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0
# 配置 负载均衡, 策略:随机
spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance-algorithm-type=RANDOM
# 配置 t_order 表规则
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{1..2}
# 配置 t_order 分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}
# 配置 t_order 分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2 + 1}
# 配置 广播表规则列表
spring.shardingsphere.sharding.broadcast-tables=area
# 配置 mybatis
mybatis.mapper-locations=/mybatis/*.xml
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# 设置日志日期格式
logging.pattern.dateformat=yyyy-MM-dd HH:mm:ss.SSS
三、单元测试
无论哪种方式,都使用如下测试方式
@Test
public void testMsOrder() {
OrderExample orderExample = new OrderExample();
orderExample.createCriteria().andUserIdEqualTo(20).andOrderIdEqualTo(4);
List<Order> orders = orderMapper.selectByExample(orderExample);
for (int i = 0; i < 10; i++) {
orders.forEach(item->{
System.out.println("================");
System.out.println("userId = " + item.getUserId());
System.out.println("orderId = " + item.getOrderId());
// 通过修改从库的金额,在遍历出来,发现全部是100.00.证明都是在从库读取数据的,并非是随机
System.out.println("amount = " + item.getOrderAmount());
});
}
}