ShardingSphere 数据库中间件

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

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

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

方案 1 ：

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

方案 2 ：

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

一、分库分表概述

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

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

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

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

二、数据库拆分策略

1. 垂直分割

垂直分割是将一个表按照列的方式拆分成多个表，减少单个表的记录数和列数，提高查询性能。垂直分割一般

分为两种：基于功能分割和基于范式分割。例如，将一个用户表拆分为登录信息表、用户信息表和账户信息表。

2. 水平分割

水平分割是将一个表按照行的方式拆分成多个表，将数据存储到多个服务器上，提高查询性能。水平分割一般按照主键或按照特定的列进行分割。例如，将一个订单表按照订单号拆分成多个表。

4. 读写分离

读写分离是将对数据库的读操作和写操作拆分到不同的服务器上，减轻单个数据库的负载压力，提高查询性能。例如，将一个电商网站的读取操作分配到从数据库上，将写操作分配到主数据库上。

5. 分片

分片是将一个大型数据表按照某个维度拆分成多个小的数据表，并将数据存储到多个服务器上。分片一般按照分片键进行分割，例如，将一个电商网站的订单表按照某个地理位置分割成多个子表。

三、本机安装多个mysql

复制 mysql 安装目录下文件夹到某盘

修改my.ini文件

[mysqld]
# 设置3306端口
port=3381
# 设置mysql的安装目录
basedir=D:\\mysql-8.0.39-winx64-3381
# 设置mysql数据库的数据的存放目录
datadir=D:\\mysql-8.0.39-winx64-3381\mysqldata
#开启慢日志
slow_query_log=1
long_query_time=2
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_
ZERO,NO_ENGINE_SUBSTITUTION
server-id=101
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=caching_sha2_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3381
# 设置默认字符集为UTF8MB4
default-character-set=utf8mb4

注意修改文件路径： basedir 与 datadir ，端口号， server-id= 唯一

cmd 打开 dos 命令，以管理员身分运行

执行 mysqld --defaults-file=D:\mysql-8.0.39-winx64-3381\my.ini --initialize --console 目录改为自己

的，初始化数据库存储目录

注意修改路径

记住临时生成的密码，例如： 2O/2ARj9mv)7

使用命令创建服务， mysqld install MySQL3381 --defaults-file=D:\mysql-8.0.39-winx64-3381\my.ini

注意修改端口号和路径

如果服务安装有问题 , 删除服务命令：

mysqld - remove 服务名

使用命令进入 mysql -uroot -p -P 端口号 , 进入控制台修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; 修改密码

刷新 flush privileges; 即可使用

安装完成后启动服务 , 使用工具连接测试 :

四、MYSQL实现读写分离

3381 为主， 3382 为从

修改 3381 中 my.ini 文件信息

添加如下代码

log-bin=D:\mysql8.0log\mysql-bin

binlog-do-db=smbms =后是数据库名

[mysqld]
# 设置3306端口
port=3381
# 设置mysql的安装目录
basedir=D:\\mysql-8.0.39-winx64-3381
# 设置mysql数据库的数据的存放目录
datadir=D:\\mysql-8.0.39-winx64-3381\mysqldata
#开启慢日志
slow_query_log=1
long_query_time=2
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_
ZERO,NO_ENGINE_SUBSTITUTION
server-id=101
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=caching_sha2_password
#log-bin需要在D盘下创建文件夹mysql8.0log用于存储日志文件信息
log-bin=D:\\mysql8.0log\mysql-bin
#binlog-do-db 为要主从的数据库名
binlog-do-db=smbms
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3381
# 设置默认字符集为UTF8MB4
default-character-set=utf8mb4

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

show master status;

000002 最新的版本信息

位置默认到 563

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

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

添加如下代码

log-bin = D : \\mysql8.0log\mysql-bin

replicate-do-db = smbms

log-slave-updates

使用dos命令进入3382数据库，并进行信息绑定

mysql -uroot -p -P3382
stop slave;
# 127.0.0.1为主数据库IP 3381为主数据库端口 用户，密码为主数据库 000001要与主数据库log版本
相同 157也一样
change master to
master_host='127.0.0.1',master_port=3381,master_user='root',master_password='roo
t',master_log_file='mysql-bin.000001',master_log_pos=157;
start slave;
show slave status\G; #查看是否连接 若使用Navicat 使用show slave status;

如果失败，尝试修改uuid

成功后进行测试

修改主库 3381 数据信息 ,3382 也会改变

五、ShardingSphere简介

Apache ShardingSphere 是一款开源的分布式数据库中间件组成的生态圈，它由 Sharding

JDBC 、 Sharding-Proxy 和 Sharding-Sidecar （规划中）这 3 款相互独立的产品组成。

ShardingSphere 定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。

1 ） Sharding-JDBC ：被定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务，以 jar 包形式使用。

2 ） Sharding-Proxy ：被定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。

3 ） Sharding-Sidecar ：被定位为 Kubernetes 或 Mesos 的云原生数据库代理，以 DaemonSet 的形式代理所有对数据库的访问。

六、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 的核心接口。

七、 ShardingSphere-JDBC+mybatisplus

1.读写分离（水平拆分库）

3381 中 smbms 为主库， 3382 中 smbms 为从库

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

引入 jar 包

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--驱动包-->
<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>

编写 yml 文件 ( 注意缩进 )

server:port: 8080
mybatis-plus:type-aliases-package: com.hz.pojoconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: false
spring:shardingsphere:masterslave:load-balance-algorithm-type: round_robin #从库负载均衡轮询name: dsmaster-data-source-name: db1 #主库slave-data-source-names: db2 #从库，多个可以使用，隔开datasource:names: db1,db2db1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3381/smbmsusername: rootpassword: rootdb2:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3382/smbmsusername: rootpassword: rootprops:sql:show: true #打印SQL语句main:allow-bean-definition-overriding: true #允许覆盖bean

编写代码，新增与查询进行测试

2.分库分表（水平拆分）

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

分库分表：在 3381 与 3382 下创建 smbms02 数据库，在 3381 下创建 smbms_bill_1 ，在 3382 下创建

smbms_bill_2

spring:main:allow-bean-definition-overriding: true #就是允许定义相同的bean对象 去覆盖原有的shardingsphere:datasource:names: ds0,ds1 # 一主一从ds0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3381/smbms02username: rootpassword: root# 从数据源ds1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3382/smbms02username: rootpassword: rootsharding:tables:smbms_bill: # 指定表名，此名必须和model中 @TableName(value = "my_table") 一致actual-data-nodes: ds$->{0..1}.smbms_bill_$->{1..2} # 创建了两个表，下标0和1 如果在同一数据库下，只做分表 ds0.smbms_bill_$->{1..2}key-generator:column: id # 主键IDtype: SNOWFLAKE # 生成策略雪花iddatabaseStrategy: #如果只分表，可不设置inline: # 指定表的分片策略shardingColumn: id #参与分片运算的列名algorithmExpression: ds$->{id % 2} #分片算法# 分表策略table-strategy:inline: # 指定表的分片策略sharding-column: idalgorithm-expression: smbms_bill_$->{id % 2 + 1} #分片规则props:sql:show: true
mybatis-plus:type-aliases-package: com.hz.pojoconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: false #驼峰映射

3.读写分离+分库分表（水平拆分）

创建数据库smbms01与smbms02

分别在smbms01与02中创建表smbms_user,smbms_bill_1,smbms_bill_2

spring:main:allow-bean-definition-overriding: true #就是允许定义相同的bean对象 去覆盖原有的shardingsphere:datasource:names: ds0,ds1 # 一主一从ds0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3381/smbms01username: rootpassword: root# 从数据源ds1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3382/smbms02username: rootpassword: rootsharding:tables:smbms_bill: # 指定表名，此名必须和model中 @TableName(value = "my_table") 一致actual-data-nodes: ds0.smbms_bill_$->{1..2} # 创建了两个表，下标0和1key-generator:column: id # 主键IDtype: SNOWFLAKE # 生成策略雪花id分库分表（垂直拆分）# 分表策略table-strategy:inline: # 指定表的分片策略sharding-column: idalgorithm-expression: smbms_bill_$->{id % 2 + 1} #分片规则master-slave-rules:ds0:master-data-source-name: ds0slave-data-source-names: ds2load-balance-algorithm-type: round_robinprops:sql:show: true
mybatis-plus:type-aliases-package: com.hz.pojoconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: false #驼峰映射

4.分库分表（垂直拆分）

分别创建数据库 smbms01 与 smbms02

smbms01 中创建表 smbms_user

smbms02 中创建表 smbms_bill( 订单表）

spring:main:allow-bean-definition-overriding: true #就是允许定义相同的bean对象 去覆盖原有的shardingsphere:datasource:names: ds0,ds1ds0:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3381/smbms01username: rootpassword: rootds1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3381/smbms02username: rootpassword: rootsharding:tables:smbms_user:actual-data-nodes: ds0.smbms_user #指定关联的数据库smbms_bill:actual-data-nodes: ds1.smbms_bill #指定关联的数据库props:sql:show: true #打印SQL
mybatis-plus:type-aliases-package: com.hz.pojoconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: false #驼峰映射