ShardingSphere + Mysql，實現分庫分表、讀寫分離，並整合 SpringBoot

Yfeil發表於2024-06-07

軟體版本

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 編寫 docker-compose.yml 檔案

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 編寫 docker-compose.yml 檔案

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-readwrite-splitting.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)

使用ShardingSphere-JDBC完成Mysql的分庫分表和讀寫分離
2021-10-20
JDBCMySql
Sharding-JDBC基本使用，整合Springboot實現分庫分表，讀寫分離
2021-10-29
JDBCSpring Boot
Docker實現Mariadb分庫分表、讀寫分離
2019-04-21
Docker
MyCat分庫分表、讀寫分離
2019-02-25
Mycat 讀寫分離+分庫分表
2017-07-18
讀寫分離 & 分庫分表 & 深度分頁
2024-03-09
MySQL-SpringBoot整合JPA實現資料讀寫分離
2018-10-21
MySqlSpring Boot
springboot實現讀寫分離
2017-07-28
Spring Boot
ProxySQL實現MySQL讀寫分離
2019-03-04
MySql
Amoeba 實現 MySQL 讀寫分離
2016-06-28
MySql
大資料資料庫讀寫分離分庫分表
2021-01-01
大資料資料庫
Amoeba+Mysql 實現讀寫分離
2017-03-18
MySql
mysql-proxy實現讀寫分離
2016-01-25
MySql
ShardingSphere-proxy +PostgreSQL實現讀寫分離（靜態策略）
2022-08-03
SQL
docker+atlas+mysql實現讀寫分離
2020-11-08
DockerMySql
Amoeba實現讀寫分離
2015-05-13
ShardingSphere-proxy-5.0.0企業級分庫分表、讀寫分離、負載均衡、雪花演算法、取模演算法整合(八)
2022-07-01
負載演算法
MySQL Amoeba讀寫分離
2015-01-29
MySql
Amoeba for mysql讀寫分離
2013-10-12
MySql
MySQL讀寫分離Atlas
2015-09-07
MySql
搭建MySQL主從實現Django讀寫分離
2021-07-30
MySqlDjango
ProxySQL實現Mysql讀寫分離 - 部署手冊
2018-07-02
MySql
MySQL 中介軟體Atlas 實現讀寫分離
2016-08-05
MySql
CQRS如何實現讀寫分離
2012-12-20
Spring實現資料庫讀寫分離
2015-04-15
Spring資料庫
【ShardingSphere技術專題】「ShardingJDBC」SpringBoot之整合ShardingJDBC實現分庫分表（JavaConfig方式）
2021-08-20
JDBCSpring BootJava
SpringBoot 專案優雅實現讀寫分離
2023-11-22
Spring Boot
Mycat讀寫分離、主從切換、分庫分表的操作記錄
2018-07-25
MySQL + Atlas --- 部署讀寫分離
2017-09-18
MySql
Amoeba for MySQL讀寫分離配置
2014-11-12
MySql
mysql讀寫分離(PHP類)
2015-07-06
MySqlPHP
【Mysql】mysql-proxy讀寫分離
2016-03-07
MySql
ShardingSphere-proxy-5.0.0建立mysql讀寫分離的連線(六)
2022-06-27
MySql
Mycat實現mysql的負載均衡讀寫分離
2020-12-26
MySql負載
Spring配置RoutingDataSource實現mysql讀寫分離
2017-12-14
SpringMySql
mysql proxy 安裝及配置實現讀寫分離
2015-06-22
MySql
springboot+mybatis+druid實現mysql主從讀寫分離(五)
2020-11-18
Spring BootMyBatisUIMySql
資料庫讀寫分離
2024-03-28
資料庫