目录1.pom.xml配置2.配置分库分表规则2.1.创建实体类和Mapper接口2.2.测试分库分表3.配置读写分离规则3.1.测试读写分离4.分库分表 读写分离方案一分库分表 读写分离整合配置方案二使用读写分离数据源作为分片数据源注意事项1.pom.xml配置dependency groupIdorg.apache.shardingsphere/groupId artifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactId version5.1.1/version /dependency dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.4.3.4/version /dependency2.配置分库分表规则分库分表是将一个大的数据库拆分成多个小的数据库分库并将一个大的表拆分成多个小的表分表以减轻单个数据库和表的压力提高系统的并发处理能力和数据存储能力。分库分表主要有垂直拆分和水平拆分两种方式。垂直拆分将表按照字段进行拆分不同的字段存储在不同的表中。例如将用户表拆分成用户基本信息表和用户详细信息表。水平拆分将表按照行进行拆分不同的行存储在不同的表中。例如将订单表按照订单ID的哈希值拆分成多个表。在application.yml中配置分库分表规则spring: shardingsphere: datasource: names: ds0,ds1 ds0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/db0 username: root password: root ds1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/db1 username: root password: root rules: sharding: tables: order: actual-data-nodes: ds$-{0..1}.order_$-{0..1} table-strategy: standard: sharding-column: order_id sharding-algorithm-name: table-inline database-strategy: standard: sharding-column: user_id sharding-algorithm-name: database-inline sharding-algorithms: database-inline: type: INLINE props: algorithm-expression: ds$-{user_id % 2} table-inline: type: INLINE props: algorithm-expression: order_$-{order_id % 2} props: sql-show: true2.1.创建实体类和Mapper接口创建Order实体类和OrderMapper接口import com.baomidou.mybatisplus.annotation.TableName; import lombok.Data; Data TableName(order) public class Order { private Long orderId; private Long userId; private String orderName; }import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.apache.ibatis.annotations.Mapper; Mapper public interface OrderMapper extends BaseMapperOrder { }2.2.测试分库分表在Service层编写测试代码import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; Service public class OrderService { Autowired private OrderMapper orderMapper; public void addOrder(Order order) { orderMapper.insert(order); } public ListOrder getOrders() { return orderMapper.selectList(null); } }3.配置读写分离规则读写分离是将数据库的读操作和写操作分离到不同的数据库实例上以提高系统的并发处理能力。通常情况下写操作会集中在主数据库上而读操作会分散到多个从数据库上。在application.yml中配置读写分离规则spring: shardingsphere: datasource: names: master,slave master: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/master_db username: root password: root slave: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/slave_db username: root password: root rules: readwrite-splitting: >3.1.测试读写分离在Service层编写测试代码import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; Service public class UserService { Autowired private UserMapper userMapper; public void addUser(User user) { userMapper.insert(user); } public ListUser getUsers() { return userMapper.selectList(null); } }4.分库分表 读写分离在同一个项目中同时配置分库分表和读写分离需要将两种配置进行整合。以下是完整的配置方案方案一分库分表 读写分离整合配置spring: shardingsphere: datasource: names: ds0,ds1,ds0_slave,ds1_slave # 主库配置用于写操作 ds0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/db0 username: root password: root ds1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/db1 username: root password: root # 从库配置用于读操作 ds0_slave: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3307/db0_slave username: root password: root ds1_slave: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3307/db1_slave username: root password: root rules: # 分片规则配置 sharding: tables: order: actual-data-nodes: ds$-{0..1}.order_$-{0..1} table-strategy: standard: sharding-column: order_id sharding-algorithm-name: table-inline database-strategy: standard: sharding-column: user_id sharding-algorithm-name: database-inline sharding-algorithms: database-inline: type: INLINE props: algorithm-expression: ds$-{user_id % 2} table-inline: type: INLINE props: algorithm-expression: order_$-{order_id % 2} # 读写分离规则配置 readwrite-splitting: >方案二使用读写分离数据源作为分片数据源spring: shardingsphere: datasource: names: master0,master1,slave0,slave1 # 主库 master0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/db0 username: root password: root master1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/db1 username: root password: root # 从库 slave0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3307/db0_slave username: root password: root slave1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3307/db1_slave username: root password: root rules: # 先配置读写分离 readwrite-splitting: >注意事项