分库分表-支付服务

配置

引入 ShardingSphere 的相关依赖

<properties>
	<shardingsphere.version>5.3.2</shardingsphere.version>
</properties>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>${shardingsphere.version}</version>
    <exclusions>
        <exclusion>
            <artifactId>logback-classic</artifactId>
            <groupId>ch.qos.logback</groupId>
        </exclusion>
    </exclusions>
</dependency>

支付服务相关配置：

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

shardingsphere-pay.yaml配置：

dataSources:    
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/damai_pay_0?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: root

  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/damai_pay_1?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: root
    
rules:
  # 分库分表规则
  - !SHARDING
    tables:    
      # 对d_pay_bill表进行分库分表
      d_pay_bill:
        # 库为damai_pay_0 damai_pay_1 表为d_pay_bill_0 至 d_pay_bill_1
        actualDataNodes: ds_${0..1}.d_pay_bill_${0..1}
        # 分库策略
        databaseStrategy:
          standard:
            # 使用out_order_no作为分片键
            shardingColumn: out_order_no
            # 用out_order_no列使用hash取模作为分库算法
            shardingAlgorithmName: databasePayHashModModel
        # 分表策略      
        tableStrategy:
          standard:
            # 使用out_order_no作为分片键
            shardingColumn: out_order_no
            # 用out_order_no列使用hash取模作为分表算法
            shardingAlgorithmName: tablePayHashModModel
      # 对d_refund_bill表进行分库分表
      d_refund_bill:
        # 库为damai_pay_0 damai_pay_1 表为d_refund_bill_0 至 d_refund_bill_1
        actualDataNodes: ds_${0..1}.d_refund_bill_${0..1}
        # 分库策略
        databaseStrategy:
          standard:
            # 使用out_order_no作为分片键
            shardingColumn: out_order_no
            # 用out_order_no列使用hash取模作为分库算法
            shardingAlgorithmName: databaseRefundHashModModel
        # 分表策略      
        tableStrategy:
          standard:
            # 使用out_order_no作为分片键
            shardingColumn: out_order_no
            # 用out_order_no列使用hash取模作为分表算法
            shardingAlgorithmName: tableRefundHashModModel
    # 具体的算法        
    shardingAlgorithms:
      # d_pay_bill表分库算法
      databasePayHashModModel:
        type: HASH_MOD
        props:
          # 分库数量
          sharding-count: 2
      # d_pay_bill表分表算法
      tablePayHashModModel:
        type: HASH_MOD
        props:
          # 分表数量
          sharding-count: 2
      # d_refund_bill表分库算法
      databaseRefundHashModModel:
        type: HASH_MOD
        props:
          # 分库数量
          sharding-count: 2
      # d_refund_bill表分表算法
      tableRefundHashModModel:
        type: HASH_MOD
        props:
          # 分表数量
          sharding-count: 2
props:
  # 打印真实sql
  sql-show: true

总结

• d_pay_bill表的分库分表都是用的out_order_no作为分片键，算法为HASH_MOD，先hash然后取模
• d_refund_bill表的分库分表都是用的out_order_no作为分片键，算法为HASH_MOD，先hash然后取模

支付服务的分库分表配置比较常规，基本都是用out_order_no作为分片键

企业级项目导航：⬅️ 05-技术精华-全面剖析分库分表 | 06-分库分表-支付服务 | ➡️ 07-技术精华-解锁分库分表新姿势：基因法完全解读