ShardingSphere数据库分库分表中间件

ShardingSphere

数据库分库分表中间件

一款分布式的数据库生态系统,可以将任意的数据库转换为分布式数据库,并通过数据分片,弹性伸缩,加密等能力对原有的数据库进行增强

基于底层数据库提供分布式数据库解决方案

可以水平扩展计算和存储,使用ShardingSphere的数据分片即可实现分库分表

导入依赖

<dependency>
    <groupId>com.jzo2o</groupId>
    <artifactId>jzo2o-shardingsphere-jdbc</artifactId>
</dependency>

配置

dataSources:
	//0号库
  jzo2o-orders-0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://192.168.101.68:3306/jzo2o-orders-0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
    username: root
    password: mysql
    //1号库
  jzo2o-orders-1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://192.168.101.68:3306/jzo2o-orders-1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
    username: root
    password: mysql
    //2号库
  jzo2o-orders-2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://192.168.101.68:3306/jzo2o-orders-2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
    username: root
    password: mysql
rules:
- !TRANSACTION
  defaultType: BASE
- !SHARDING
//分表规则
  tables:
    orders:
      actualDataNodes: jzo2o-orders-${0..2}.orders_${0..2}
      tableStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: orders_table_inline
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: orders_database_inline
    orders_serve:
      actualDataNodes: jzo2o-orders-${0..2}.orders_serve_${0..2}
      tableStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: orders_serve_table_inline
      databaseStrategy:
        standard:
          shardingColumn: serve_provider_id
          shardingAlgorithmName: orders_serve_database_inline
    biz_snapshot:
      actualDataNodes: jzo2o-orders-${0..2}.biz_snapshot_${0..2}
      tableStrategy:
        standard:
          shardingColumn: biz_id
          shardingAlgorithmName: biz_snapshot_table_inline
      databaseStrategy:
        standard:
          shardingColumn: db_shard_id
          shardingAlgorithmName: biz_snapshot_database_inline
  shardingAlgorithms:
    # 订单-分库算法
    orders_database_inline:
      type: INLINE
      props:
        # 分库算法表达式
        algorithm-expression: jzo2o-orders-${user_id % 3}
        # 分库支持范围查询
        allow-range-query-with-inline-sharding: true
    # 订单-分表算法
    orders_table_inline:
      type: INLINE
      props:
        # 分表算法表达式
        algorithm-expression: orders_${(int)Math.floor(id % 10000000000 / 15000000)}
        # 允许范围查询
        allow-range-query-with-inline-sharding: true
    # 服务单-分库算法
    orders_serve_database_inline:
      type: INLINE
      props:
        # 分库算法表达式
        algorithm-expression: jzo2o-orders-${serve_provider_id % 3}
        # 允许范围查询
        allow-range-query-with-inline-sharding: true
    # 服务单-分表算法
    orders_serve_table_inline:
      type: INLINE
      props:
        # 允许范围查询
        algorithm-expression: orders_serve_${(int)Math.floor(id % 10000000000 / 15000000)}
        # 允许范围查询
        allow-range-query-with-inline-sharding: true
    # 快照-分库算法
    biz_snapshot_database_inline:
      type: INLINE
      props:
        # 分库算法表达式
        algorithm-expression: jzo2o-orders-${db_shard_id % 3}
        # 允许范围查询
        allow-range-query-with-inline-sharding: true
    # 快照-分表算法
    biz_snapshot_table_inline:
      type: INLINE
      props:
        # 允许范围查询
        algorithm-expression: biz_snapshot_${(int)Math.floor((Long.valueOf(biz_id))  % 10000000000 / 15000000)}
        # 允许范围查询
        allow-range-query-with-inline-sharding: true
  # id生成器
  keyGenerators:
    snowflake:
      type: SNOWFLAKE
- !BROADCAST
//广播,代表不做分库分表的表
  tables:
    - breach_record
    - orders_canceled
    - orders_refund
    - orders_dispatch
    - orders_seize
    - serve_provider_sync
    - state_persister
    - orders_dispatch_receive
    - undo_log
    - history_orders_sync
    - history_orders_serve_sync
props:
  sql-show: true

指定数据源

在springdatasource中指定ShardingSphereDriver作为数据源