获取配置的shardingsphere分库分表的数据库链接，并连接每个分库分表执行数据归档逻辑

原创

已于 2024-10-10 17:21:36 修改 · 821 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2024-10-10 16:39:12 首次发布

前言

随着分库分表的数据量越来越大，一些无用的数据，比如取消的订单需要删除以减少数据库成本(特殊场景，95%的订单都是已取消的无效订单)。但是shardingsphere自带的查询需要分片键才能出查询对应的分库分表。做数据归档删除的时候没有分片键，直接查询会查询所有的分库分表，性能较差。

shardingsphere配置信息

spring:
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql.show: true
    # 配置数据源
    datasource:
      names: ds1,ds2,ds3,ds4,ds5,ds6,ds7,ds8,ds9,ds10,ds11,ds12,ds13,ds14,ds15,ds16
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_3?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds4:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_4?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds5:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_5?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlobalDataSourceStat: true
          timeBetweenEvictionRunsMillis: 60000
          validationQuery: select 'x'
          testOnBorrow: true
          testOnReturn: true
          testWhileIdle: true
      ds6:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.0.2.4:63306/order_6?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: xxx
        password: xxxxxxx
        druid:
          initialSize: 30
          maxActive: 100
          minIdle: 5
          maxWait: 1000
          minEvictableIdleTimeMillis: 300000
          useGlo