sharding-proxy5.X 分库分表和根据时间分表

config-sharding.yaml

schemaName: edu_yjx


dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.99:3306/test_0?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://192.168.1.100:3306/test_1?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
rules:
- !SHARDING
  tables:
    t_user:
      actualDataNodes: ds_${0..1}.t_user_${0..31}
      databaseStrategy:
        standard:
          shardingColumn: openId
          shardingAlgorithmName: user_database_inline
      tableStrategy:
        standard:
          shardingColumn: openId
          shardingAlgorithmName: user_table_inline
    t_msg:
      actualDataNodes: ds_${0..1}.t_msg_${(0..6).collect{java.time.LocalDate.now().minusDays(it).format(java.time.format.DateTimeFormatter.ofPattern("yyyy_MM_dd"))}}
      databaseStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: msg_database_inline
      tableStrategy:
        standard:
          shardingColumn: openId
          shardingAlgorithmName: msg_table_inline
  bindingTables:
    - t_user

  shardingAlgorithms:
    user_database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${(int)(Math.abs(openId.hashCode()) % 64/32)}
    user_table_inline:
      type: INLINE
      props:
        algorithm-expression: t_user_${Math.abs(openId.hashCode())%32}
    msg_database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${(id>>24)%2}
    msg_table_inline:
      type: INLINE
      props:
        algorithm-expression: t_msg_${new Date((id >> 24) + 1484754361114L).format("yyyy_MM_dd")}

　　server.yaml

rules:
  - !AUTHORITY
    users:
      - root@%:123456  # 启动该服务的用户密码
      - sharding@:123456 # 启动该服务的用户密码  意思就是用这个密码来登录shardingshare生成的模拟mysql
    provider:
      type: NATIVE
props:
  max-connections-size-per-query: 1 # 一次查询请求在每个数据库实例中所能使用的最大连接数。
  kernel-executor-size: 16 # 线程池大小  默认值: CPU核数
  kernel-acceptor-size: 16  # 用于设置接收客户端请求的工作线程个数，默认为CPU核数*2
  proxy-frontend-flush-threshold: 128  # 设置传输数据条数的 IO 刷新阈值
  proxy-opentracing-enabled: false #是否开启链路追踪功能，默认为不开启
  proxy-hint-enabled: false #是否启用hint算法强制路由 默认false
  sql-show: ture #是否打印sql 默认falsefalse
#  show-process-list-enabled: false
    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
    # The default value is -1, which means set the minimum value for different JDBC drivers.
  proxy-backend-query-fetch-size: -1
  check-duplicate-table-enabled: false
  sql-comment-parse-enabled: false
  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
    # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
  proxy-backend-executor-suitable: OLAP
#  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
#  sql-federation-enabled: false