ShardingSphere 数据库中间件

一、分库分表概述

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

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

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

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

[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

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

replicate-do-db = smbms

log-slave-updates

<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>

server:
  port: 8080
mybatis-plus:
  type-aliases-package: com.hz.pojo
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: false
spring:
  shardingsphere:
    masterslave:
      load-balance-algorithm-type: round_robin #从库负载均衡轮询
      name: ds
      master-data-source-name: db1 #主库
      slave-data-source-names: db2 #从库，多个可以使用，隔开
    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:3381/smbms
        username: root
        password: root
      db2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3382/smbms
        username: root
        password: root
    props:
      sql:
        show: true #打印SQL语句
  main:
    allow-bean-definition-overriding: true #允许覆盖bean

创建数据库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.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3381/smbms01
        username: root
        password: root
      # 从数据源
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3382/smbms02
        username: root
        password: root
    sharding:
      tables:
        smbms_bill: # 指定表名，此名必须和model中 @TableName(value = "my_table") 一致
          actual-data-nodes: ds0.smbms_bill_$->{1..2} # 创建了两个表，下标0和1
          key-generator:
            column: id # 主键ID
            type: SNOWFLAKE # 生成策略雪花id
            分库分表（垂直拆分）
            # 分表策略
          table-strategy:
            inline: # 指定表的分片策略
              sharding-column: id
              algorithm-expression: smbms_bill_$->{id % 2 + 1} #分片规则
  master-slave-rules:
      ds0:
        master-data-source-name: ds0
        slave-data-source-names: ds2
        load-balance-algorithm-type: round_robin
  props:
    sql:
      show: true
mybatis-plus:
  type-aliases-package: com.hz.pojo
  configuration:
     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
     map-underscore-to-camel-case: false #驼峰映射

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

spring:
  main:
    allow-bean-definition-overriding: true #就是允许定义相同的bean对象 去覆盖原有的
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3381/smbms01
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3381/smbms02
        username: root
        password: root
    sharding:
      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.pojo
   configuration:
     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
     map-underscore-to-camel-case: false #驼峰映射