ShardingSphere数据库中间件【详解大数据量下分库分表，读写分离，主从同步，以及ShardingSph

文章目录 五.ShardingSphere 数据库中间件5.1分库分表概述5.2 数据库拆分策略5.3本机安装多个mysql5.4 MYSQL实现读写分离（主从同步）5.5 ShardingSphere简介5.6 ShardingSphere-JDBC5.7 ShardingSphere-JDBC+mybatisplus读写分离（水平拆分库）分库分表（水平拆分）分表分库分表 分库分表（垂直拆分）

五.ShardingSphere 数据库中间件

数据库中的数据量猛增，访问性能也变慢了，优化迫在眉睫 ?

1.关系型数据库本身比较容易成为系统瓶颈：单机存储容量、数据库连接数、处理能力都有限。

2.当单表的数据量达到1000W或100G以后，由于查询维度较多，即使做了优化索引等操作， 查询性能仍下降严重。

方案1：

通过提升服务器硬件能力来提高数据处理能力，比如增加存储容量 、CPU等，这种方案成本很高，并且如果瓶颈在MySQL本身那么提高硬件也是有很的。

方案2：

把数据分散在不同的数据库中，使得单一数据库的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的；

5.1分库分表概述

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题;

1.将原来独立的数据库拆分成若干数据库组成;

2.将原来的大表(存储近千万数据的表)拆分成若干个小表；

目的：使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

5.2 数据库拆分策略

根据业务场景拆分：

垂直拆分

业务维度

水平切分

数据维度

混合切分

业务+数据

1. 垂直分割 垂直分割是将一个表按照列的方式拆分成多个表，减少单个表的记录数和列数，提高查询性能。垂直分割一般 分为两种： 基于功能分割和基于范式分割。例如，将一个用户表拆分为登录信息表、用户信息表和账户信息表。 2. 水平分割 水平分割是将一个表按照行的方式拆分成多个表，将数据存储到多个服务器上，提高查询性能。水平分割一般按照主键或按照特定的列进行分割。例如，将一个订单表按照订单号拆分成多个表。 4. 读写分离 读写分离是将对数据库的读操作和写操作拆分到不同的服务器上，减轻单个数据库的负载压力，提高查询性能。例如，将一个电商网站的读取操作分配到从数据库上，将写操作分配到主数据库上。 5. 分片 分片是将一个大型数据表按照某个维度拆分成多个小的数据表，并将数据存储到多个服务器上。分片一般按照分片键进行分割，例如，将一个电商网站的订单表按照某个地理位置分割成多个子表 5.3本机安装多个mysql

【点击这里✔】

5.4 MYSQL实现读写分离（主从同步）

设定mysql3391为主，mysql3392为从

修改mysql3391中my.ini文件信息

在[mysqld]最后添加如下代码（手动新建mysql4072log文件夹）

log-bin=D:\mysql8.0log\mysql-bin binlog-do-db=smbms

重启服务并输入命令查看日志信息

000001最新的版本信息

位置默认到157

注意：可以另外在创建一个新的用户以供连接使用，此处我使用root

修改3392中的my.ini文件,并重启服务

在[mysqld]最后添加如下代码

log-bin=E:\\software\mysql8\mysql4072log\mysql-bin replicate-do-db=smbms log-slave-updates

在Navicat中依次执行，进行信息绑定：

stop slave; change master to master_host='127.0.0.1',master_port=3391,master_user='root',master_password='sa123456',master_log_file='mysql-bin.000001',master_log_pos=157; start slave; show slave status;

此时会发现有个错误，如图：

Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

如果失败，尝试修改uuid

随便修改一个数字即可，保存重启mysql3392服务。

重启启动mysql3392，在把上Navicat中命令依次执行，

此时读写分离设置完成。

在mysql3391数据库中修改信息，可以同步到mysql3392中。

5.5 ShardingSphere简介

Apache ShardingSphere是一款开源的分布式数据库中间件组成的生态圈，它由ShardingJDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立的产品组成。ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的 计算和存储能力，而并非实现一个全新的关系型数据库。

Sharding-JDBC：被定位为轻量级Java框架，在Java的JDBC层提供的额外服务，以jar包形式使用。Sharding-Proxy：被定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版 本，用于完成对异构语言的支持。Sharding-Sidecar：被定位为Kubernetes或Mesos的云原生数据库代理，以DaemonSet的形式代理所有对数据库的访问。 5.6 ShardingSphere-JDBC

ShardingSphere-JDBC 是 ShardingSphere 的第一个产品，也是 ShardingSphere 的前身， 我们经常简称之为：sharding-jdbc 。 它定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。

基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL。

sharding-jdbc 的本质上就是实现 JDBC 的核心接口。

5.7 ShardingSphere-JDBC+mybatisplus 读写分离（水平拆分库）

(前提是mysql3391,和mysql3392实现主从同步。)

mysql3391中smbms为主库，mysql3392中smbms为从库

2个库中都需同时存在smbms_bill表，要求结构相同

【引入jar包】

<!--驱动包--> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> <version>8.0.33</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId> sharding-jdbc-spring-boot-starter </artifactId> <version>4.0.0-RC1</version> </dependency> <!-- mybatis-plus --> <dependency> <groupId>com.baomidou</groupId> <artifactId> mybatis-plus-boot-starter </artifactId> <version>3.4.0</version> </dependency> <!--阿里数据库连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId> druid-spring-boot-starter </artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency>

【编写yml文件(注意缩进)】

【application.yml】

server: port: 8080 mybatis-plus: type-aliases-package: com.hz.pojo.Bill configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl map-underscore-to-camel-case: false #驼峰映射 spring: profiles: active: rwSplit #读写分离

【application-rwSplit.yml】

#file: noinspection SpringBootApplicationYaml spring: shardingsphere: props: sql: show: true masterslave: name: masterSlave master-data-source-name: db1 #主库 slave-data-source-names: db2 #从库 从库有多个可以用逗号隔开 # load-balance-algorithm-type: round-robin #从库负载均衡轮询 datasource: names: db1,db2 db1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3391/smbms?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: sa123456 db2: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3392/smbms?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: sa123456 main: allow-bean-definition-overriding: true #允许覆盖注册 bean

【实体类】

@Data @TableName("smbms_bill") public class Bill { @TableId(type = IdType.AUTO) private Integer id; //id private String billCode; //账单编码 private String productName; //商品名称 private String productDesc; //商品描述 private String productUnit; //商品单位 private BigDecimal productCount; //商品数量 private BigDecimal totalPrice; //总金额 private Integer isPayment; //是否支付 private Integer providerId; //供应商ID private Integer createdBy; //创建者 private Date creationDate; //创建时间 private Integer modifyBy; //更新者 private Date modifyDate;//更新时间 } @Mapper public interface BillMapper extends BaseMapper<Bill> { /** * @description: * @author: weiyi * @date: 2025/2/19 15:52 * @param: [] * @return: int **/ @Select("select count(1) from smbms_bill") public int selectBillCount(); } @SpringBootTest class ShardingJdbc01ApplicationTests { @Autowired private BillMapper billMapper; @Test void insert(){ Bill bill = new Bill(); bill.setBillCode("123"); bill.setProductName("测试"); bill.setProductDesc("测试"); billMapper.insert(bill); } @Test void contextLoads() { billMapper.selectBillCount(); } }

可以看到查询读取数据时，是从从库db2中读取的，插入数据时是向db1中插入。此时db2也同步新增的数据。

分库分表（水平拆分）

分表：在同一数据库下创建两张订单表，要求结构相同，让id为奇数数据放入smbms_bill_2中,id为偶数数据放入smbms_bill_1中，分开存储数据，查询是合并在一起。

分库分表：在3391与3392下创建smbms02数据库，在3391下创建smbms_bill_1，在3392下创建smbms_bill_2

分表

分表：在同一数据库下创建两张订单表，要求结构相同，让id为奇数数据放入smbms_bill_2中,id为偶数数据放入smbms_bill_1中，分开存储数据，查询是合并在一起。

【application.yml】

server: port: 8080 mybatis-plus: type-aliases-package: com.hz.pojo.Bill configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl map-underscore-to-camel-case: false #驼峰映射 #spring: # profiles: # active: rwSplit #读写分离 spring: profiles: active: 分库分表 #读写分离

【application-分库分表.yml】

#file: noinspection SpringBootApplicationYaml spring: shardingsphere: props: sql: show: true #打印sql datasource: names: db1 db1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3391/smbms?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: sa123456 sharding: tables: smbms_bill: #逻辑表名字 actual-data-nodes: db1.smbms_bill_${1..2} # 创建了两个表，下标1和2 如果在同一数据库下，只做分表 db1.smbms_bill_1,db1.smbms_bill_2 key-generator: type: SNOWFLAKE #雪花算法 column: id databaseStrategy: #如果只分表，可不设置，也就是往的数据库插 inline: # 指定表的分片策略 shardingColumn: id #参与分片运算的列名 algorithmExpression: db1 #分片算法 table-strategy: inline: #指定表的分片策略 sharding-column: id algorithm-expression: smbms_bill_${id % 2 +1} #分片规则 main: allow-bean-definition-overriding: true #允许覆盖注册 bean

【实体类】

@Data @TableName("smbms_bill") public class Bill { // @TableId(type = IdType.AUTO) private Long id; //id private String billCode; //账单编码 private String productName; //商品名称 private String productDesc; //商品描述 private String productUnit; //商品单位 private BigDecimal productCount; //商品数量 private BigDecimal totalPrice; //总金额 private Integer isPayment; //是否支付 private Integer providerId; //供应商ID private Integer createdBy; //创建者 private Date creationDate; //创建时间 private Integer modifyBy; //更新者 private Date modifyDate;//更新时间 } @Test void insert(){ Bill bill = new Bill(); bill.setBillCode("123"); bill.setProductName("测试1"); bill.setProductDesc("测试1"); billMapper.insert(bill); } @Test void contextLoads() { billMapper.selectBillCount(); }

可以看到根据id的奇偶性分开插入不同的数据库，在查询时还是查询的总记录数。

分库分表

（将主从同步设置关闭）

分库分表：在3391与3392下创建smbms02数据库，在3391下创建smbms_bill_1，在3392下创建smbms_bill_2，表中数据清空

【application.yml】

server: port: 8080 mybatis-plus: type-aliases-package: com.hz.pojo.Bill configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl map-underscore-to-camel-case: false #驼峰映射 #spring: # profiles: # active: rwSplit #读写分离 spring: profiles: active: 分库分表 #读写分离

【application-分库分表.yml】

#file: noinspection SpringBootApplicationYaml spring: shardingsphere: props: sql: show: true #打印sql datasource: names: db1,db2 db1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3391/smbms?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: sa123456 db2: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3392/smbms?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: sa123456 sharding: tables: smbms_bill: #逻辑表名字 actual-data-nodes: db$->{1..2}.smbms_bill_${1..2} #db1.smbms_bill_1,db2.smbms_bill_2 #db$->{1..2}.smbms_bill_${1..2} # 创建了两个表，下标1和2 如果在同一数据库下，只做分表 db1.smbms_bill_1,db1.smbms_bill_2 key-generator: type: SNOWFLAKE #雪花算法 column: id databaseStrategy: #如果只分表，可不设置，也就是往的数据库插 inline: # 指定表的分片策略 shardingColumn: id #参与分片运算的列名 algorithmExpression: db$->{id % 2 +1} #分片算法 table-strategy: inline: #指定表的分片策略 sharding-column: id algorithm-expression: smbms_bill_$->{id % 2 +1} #分片规则 main: allow-bean-definition-overriding: true #允许覆盖注册 bean @SpringBootTest class ShardingJdbc01ApplicationTests { @Autowired private BillMapper billMapper; @Test void insert(){ Bill bill = new Bill(); bill.setBillCode("123"); bill.setProductName("测试1"); bill.setProductDesc("测试1"); billMapper.insert(bill); } @Test void contextLoads() { billMapper.selectBillCount(); } }

可以看到数据根据id的奇偶性不同插入不同的数据库中的表里面，在查询时，可以查询所有记录。

分库分表（垂直拆分）

分别创建数据库smbms01与smbms02

smbms01中创建表smbms_bill

smbms02中创建表smbms_user

【application.yml】

server: port: 8080 mybatis-plus: type-aliases-package: com.hz.pojo.Bill configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl map-underscore-to-camel-case: false #驼峰映射 #spring: # profiles: # active: rwSplit #读写分离 #spring: # profiles: # active: 分库分表 #读写分离 spring: profiles: active: 垂直拆分 #读写分离

【application-垂直拆分.yml】

#file: noinspection SpringBootApplicationYaml spring: shardingsphere: props: sql: show: true #打印sql datasource: names: db1,db2 db1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3391/smbms01?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: sa123456 db2: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3392/smbms02?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC username: root password: sa123456 sharding: tables: smbms_bill: #逻辑表名字 actual-data-nodes: db1.smbms_bill smbms_user: #逻辑表名字 actual-data-nodes: db2.smbms_user main: allow-bean-definition-overriding: true #允许覆盖注册 bean @Mapper public interface UserMapper extends BaseMapper<SmbmsUser> { } @Data @TableName("smbms_user") @ToString public class SmbmsUser { @TableId(type = IdType.AUTO) private Long id; //id private String userCode; //用户编码 private String userName; //用户名称 private String userPassword; //用户密码 private Integer gender; //性别 private Date birthday; //出生日期 private String phone; //电话 private String address; //地址 private Integer userRole; //用户角色 private Integer createdBy; //创建者 private Date creationDate; //创建时间 private Integer modifyBy; //更新者 private Date modifyDate; //更新时间 } @SpringBootTest class ShardingJdbc01ApplicationTests { @Autowired private UserMapper userMapper ; @Test void insertUser(){ SmbmsUser smbmsUser = new SmbmsUser(); smbmsUser.setUserCode("123"); smbmsUser.setUserName("测试6666"); smbmsUser.setUserPassword("123"); smbmsUser.setUserRole(1); userMapper.insert(smbmsUser); } @Test void contextLoads02() { billMapper.selectBillCount02(2L); } }

查询成功：

插入成功：

但是，不能表连接。