ShardingSphere 实现分库分表

最新推荐文章于 2025-11-08 08:12:19 发布

原创最新推荐文章于 2025-11-08 08:12:19 发布 · 594 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #spring boot

Java应用专栏收录该内容

4 篇文章

订阅专栏

1、具体步骤

1.1 引入依赖

在pom.xml文件中引入ShardingSphere 的相关依赖：

<shardingsphere.version>5.3.2</shardingsphere.version>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>${shardingsphere.version}</version>
</dependency>

1.2 新建配置文件

在resource文件夹下新建 shardingsphere-config.yaml 配置文件：

1.2.1 数据源配置

dataSources:
  # 以电商订单库数据源，按编号区分
  order_db_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ecommerce_order_0?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: root
  order_db_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ecommerce_order_1?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: root

1.2.2 分片规则配置

rules:
  - !SHARDING
    tables:
      # 电商订单主表
      t_order:
        actualDataNodes: order_db_${0..1}.t_order_${0..15}
        databaseStrategy:
          standard:
            shardingColumn: user_id  # 电商场景常用用户ID作为分库键，便于查询用户的所有订单
            shardingAlgorithmName: order_db_inline
        tableStrategy:
          standard:
            shardingColumn: user_id  # 表级也按用户ID分片，确保同一用户订单在同一表
            shardingAlgorithmName: order_table_inline
      # 订单明细表（与订单主表保持一致分片规则）
      t_order_item:
        actualDataNodes: order_db_${0..1}.t_order_item_${0..15}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order_db_inline
        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order_table_inline
    shardingAlgorithms:
      # 订单分库算法
      order_db_inline:
        type: INLINE
        props:
          algorithm-expression: order_db_${user_id % 2}  # 按用户ID取模2，分配到2个库
      # 订单分表算法
      order_table_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_${user_id % 16}  # 每个库内分16张表

1.2.3 全局属性配置

props:
  sql-show: true  # 开发环境打印SQL，便于调试分库分表路由
  executor-size: 10  # 执行线程池大小，根据电商并发量调整

1.2.4 完整配置

dataSources:
  # 电商订单库数据源，按编号区分
  order_db_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ecommerce_order_0?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: root
  order_db_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/ecommerce_order_1?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: root

rules:
  - !SHARDING
    tables:
      # 电商订单主表
      t_order:
        actualDataNodes: order_db_${0..1}.t_order_${0..15}
        databaseStrategy:
          standard:
            shardingColumn: user_id  # 电商场景常用用户ID作为分库键，便于查询用户的所有订单
            shardingAlgorithmName: order_db_inline
        tableStrategy:
          standard:
            shardingColumn: user_id  # 表级也按用户ID分片，确保同一用户订单在同一表
            shardingAlgorithmName: order_table_inline
      # 订单明细表（与订单主表保持一致分片规则）
      t_order_item:
        actualDataNodes: order_db_${0..1}.t_order_item_${0..15}
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order_db_inline
        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: order_table_inline
    shardingAlgorithms:
      # 订单分库算法
      order_db_inline:
        type: INLINE
        props:
          algorithm-expression: order_db_${user_id % 2}  # 按用户ID取模2，分配到2个库
      # 订单分表算法
      order_table_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_${user_id % 16}  # 每个库内分16张表

props:
  sql-show: true  # 开发环境打印SQL，便于调试分库分表路由
  executor-size: 10  # 执行线程池大小，根据电商并发量调整
    折磨多配置能不能分块讲解一下

1.3 在application.yaml里添加配置

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:shardingsphere-config.yaml