ShardingSphere + Mysql，实现分库分表、读写分离，并整合 SpringBoot

2024-06-07 23:02 由 Yfeil 发表于 #后端开发

软件版本

Docker：26.1.3

Mysql：8.4.0

ShardingSphere：5.5.0

分库分表

1.Docker创建两个Mysql

services:
  mysql:
    image: mysql:8.4.0
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: abc123
    volumes:
      - ./data:/var/lib/mysql
      - ./config:/etc/mysql/conf.d
    restart: always

2.两个Mysql创建测试库 demo 并导入表

-- demo.t_address definition

CREATE TABLE `t_address` (
  `user_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- demo.t_order_0 definition

CREATE TABLE `t_order_0` (
  `order_id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- demo.t_order_item_0 definition

CREATE TABLE `t_order_item_0` (
  `order_item_id` bigint NOT NULL,
  `order_id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- demo.t_order_1 definition

CREATE TABLE `t_order_1` (
  `order_id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- demo.t_order_item_1 definition

CREATE TABLE `t_order_item_1` (
  `order_item_id` bigint NOT NULL,
  `order_id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

3.Docker创建ShardingSphere-Proxy

services:
  shardingsphere:
    image: apache/shardingsphere-proxy:5.5.0
    ports:
      - "3307:3307"
    volumes:
      - ./conf:/opt/shardingsphere-proxy/conf
      - ./ext-lib:/opt/shardingsphere-proxy/ext-lib
    restart: always

4.导入Mysql驱动

请下载 mysql-connector-java-5.1.49.jar 或者 mysql-connector-java-8.0.11.jar，并将其放入 ext-lib 目录。

5.导入分库分表配置到 conf/database-sharding.yaml

databaseName: demo

dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.111:3306/demo?allowPublicKeyRetrieval=true
    username: root
    password: abc123
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://192.168.1.112:3306/demo?allowPublicKeyRetrieval=true
    username: root
    password: abc123
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
  - !SHARDING
    # 分片配置
    tables:
      # 逻辑表名
      t_order:
        # 数据源
        actualDataNodes: ds_${0..1}.t_order_${0..1}
        # 分库策略，下面默认分库策略替代
        # databaseStrategy:
        #   standard:
        #     shardingColumn: user_id
        #     shardingAlgorithmName: database_inline
        # 分表策略
        tableStrategy:
          standard:
            # 分片列
            shardingColumn: order_id
            # 分片算法
            shardingAlgorithmName: t_order_inline
        # 主键生成策略
        keyGenerateStrategy:
          # 主键列
          column: order_id
          # 主键生成算法
          keyGeneratorName: snowflake
      # 第二个逻辑表名，配置方式如上
      t_order_item:
        actualDataNodes: ds_${0..1}.t_order_item_${0..1}
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: t_order_item_inline
        keyGenerateStrategy:
          column: order_item_id
          keyGeneratorName: snowflake
    # 绑定表
    bindingTables:
      - t_order,t_order_item
    # 默认分库策略
    defaultDatabaseStrategy:
      standard:
        shardingColumn: user_id
        shardingAlgorithmName: database_inline
    # 默认分表策略
    defaultTableStrategy:
      none:

    # 定义分片算法
    shardingAlgorithms:
      # 算法名
      database_inline:
        # 算法类型
        type: INLINE
        # 算法属性
        props:
          algorithm-expression: ds_${user_id % 2}
          allow-range-query-with-inline-sharding: true
      t_order_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_${order_id % 2}
          allow-range-query-with-inline-sharding: true
      t_order_item_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_item_${order_id % 2}
          allow-range-query-with-inline-sharding: true

    # 定义主键生成算法
    keyGenerators:
      # 算法名
      snowflake:
        # 算法类型
        type: SNOWFLAKE

  - !BROADCAST
    # 广播表配置
    tables:
      - t_address

6.启动 ShardingSphere-Proxy 即可完成

默认URL：jdbc:mysql://127.0.0.1:3307/demo

默认端口：3307

默认账号：root

默认密码：root

可以直接像连接普通Mysql数据库一样使用了。

读写分离

1.先准备两个Mysql服务器，并配好主从复制

Mysql 8.4.0 结合 Docker 搭建GTID主从复制，以及传统主从复制 - Yfeil - 博客园 (cnblogs.com)

2.Docker创建ShardingSphere-Proxy

services:
  shardingsphere:
    image: apache/shardingsphere-proxy:5.5.0
    ports:
      - "3307:3307"
    volumes:
      - ./conf:/opt/shardingsphere-proxy/conf
      - ./ext-lib:/opt/shardingsphere-proxy/ext-lib
    restart: always

3.导入Mysql驱动

请下载 mysql-connector-java-5.1.49.jar 或者 mysql-connector-java-8.0.11.jar，并将其放入 ext-lib 目录。

4.导入分库分表配置到 conf/database-sharding.yaml

databaseName: demo

dataSources:
  write_ds:
    url: jdbc:mysql://192.168.1.113:3306/demo?allowPublicKeyRetrieval=true
    username: root
    password: abc123
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  read_ds:
    url: jdbc:mysql://192.168.1.114:3306/demo?allowPublicKeyRetrieval=true
    username: root
    password: abc123
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
  - !READWRITE_SPLITTING
    dataSources:
      # 逻辑数据源名
      readwrite_ds:
        # 写库数据源
        writeDataSourceName: write_ds
        # 读库数据源
        readDataSourceNames:
          - read_ds
        loadBalancerName: random
    
    # 定义负载均衡算法
    loadBalancers:
      # 算法名
      random:
        #算法类型
        type: RANDOM

  - !SINGLE
    # 单表规则
    tables:
      - readwrite_ds.*

5.启动 ShardingSphere-Proxy 即可完成

默认URL：jdbc:mysql://127.0.0.1:3307/demo

默认端口：3307

默认账号：root

默认密码：root

可以直接像连接普通Mysql数据库一样使用了。

JDBC 整合 SpringBoot 2.x

ShardingSphere-JDBC 对 SpringBoot 3.x 支持的不好，硬要使用先去官网看看注意事项。

这里只演示分库分表的整合，读写分离同理。

1.引入依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>5.5.0</version>
    <exclusions>
        <exclusion>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-test-util</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>2.2</version>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

2.添加配置

# 配置 DataSource Driver
spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
# 指定 YAML 配置文件
spring.datasource.url=jdbc:shardingsphere:classpath:database-sharding.yaml

3.创建 src/main/resources/database-sharding.yaml

rules 配置和之前一样，主要区别在于 dataSources 下的配置变了。

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.1.111:3306/demo?allowPublicKeyRetrieval=true
    username: root
    password: abc123
  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.1.112:3306/demo?allowPublicKeyRetrieval=true
    username: root
    password: abc123

rules:
  - !SHARDING
    # 分片配置
    tables:
      # 逻辑表名
      t_order:
        # 数据源
        actualDataNodes: ds_${0..1}.t_order_${0..1}
        # 分库策略，下面默认分库策略替代
        # databaseStrategy:
        #   standard:
        #     shardingColumn: user_id
        #     shardingAlgorithmName: database_inline
        # 分表策略
        tableStrategy:
          standard:
            # 分片列
            shardingColumn: order_id
            # 分片算法
            shardingAlgorithmName: t_order_inline
        # 主键生成策略
        keyGenerateStrategy:
          # 主键列
          column: order_id
          # 主键生成算法
          keyGeneratorName: snowflake
      # 第二个逻辑表名，配置方式如上
      t_order_item:
        actualDataNodes: ds_${0..1}.t_order_item_${0..1}
        tableStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: t_order_item_inline
        keyGenerateStrategy:
          column: order_item_id
          keyGeneratorName: snowflake
    # 绑定表
    bindingTables:
      - t_order,t_order_item
    # 默认分库策略
    defaultDatabaseStrategy:
      standard:
        shardingColumn: user_id
        shardingAlgorithmName: database_inline
    # 默认分表策略
    defaultTableStrategy:
      none:

    # 定义分片算法
    shardingAlgorithms:
      # 算法名
      database_inline:
        # 算法类型
        type: INLINE
        # 算法属性
        props:
          algorithm-expression: ds_${user_id % 2}
          allow-range-query-with-inline-sharding: true
      t_order_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_${order_id % 2}
          allow-range-query-with-inline-sharding: true
      t_order_item_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_item_${order_id % 2}
          allow-range-query-with-inline-sharding: true

    # 定义主键生成算法
    keyGenerators:
      # 算法名
      snowflake:
        # 算法类型
        type: SNOWFLAKE

  - !BROADCAST
    # 广播表配置
    tables:
      - t_address

4.完成

平时咋操作数据库现在也一样，也可以引入 Mybatis，可以无缝连接。

参考

数据分片 :: ShardingSphere (apache.org)

读写分离 :: ShardingSphere (apache.org)

分片算法 :: ShardingSphere (apache.org)

负载均衡算法 :: ShardingSphere (apache.org)

使用限制 :: ShardingSphere (apache.org)