sharding-jdbc 数据库连接池配置(有点小坑)，读写分离，分表策略，主键自增

 

• 常规spring boot druid 数据库连接配置

spring:
  datasource:
    name: druidDataSource
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
      username: root
      password: 123456
      filters: stat,wall,log4j,config
      max-active: 100
      initial-size: 1
      max-wait: 60000
      min-idle: 1
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: select 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-open-prepared-statements: 50
      max-pool-prepared-statement-per-connection-size: 20

• sharding-jdbc 数据库连接池 ，直接配置，不需增加父节点druid

 

spring:
  #shardingsphere
  shardingsphere:
    datasource:
      #主库
      m0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.88.22:3306/test?characterEncoding=utf8&serverTimezone=Asia/Shanghai&connectTimeout=15000&socketTimeout=8000&autoReconnect=true&useSSL=false&failOverReadOnly=false
        username: root
        password: root
        maximum-pool-size: 50
        pool-name: m0-pool
      # 从库
      s0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.88.22:3306/test?characterEncoding=utf8&serverTimezone=Asia/Shanghai&autoReconnect=true&useSSL=false
        username: root
        password: root
        initial-size: 20
        max-active: 200
        max-wait: 25000
        min-evictable-idle-time-millis: 1814400
        min-idle: 20
        test-on-borrow: true
        test-on-return: true
        test-while-idle: true
        validation-query: SELECT 1
        validation-query-timeout: 2000
        time-between-eviction-runs-millis: 1814400
        time-between-connect-error-millis: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20


      names: m0,s0
    props:
      sql:
        #打印sql
        show: true
      max:
        connections:
          size:
            per:
              # 开启内存模式
              query: 50
    sharding:
      master-slave-rules:
        # 读写分离
        ds0:
          master-data-source-name: m0
          slave-data-source-names[0]: s0 # 从库

      # 分表策略
      tables:
        test_account_log:
          table-strategy:
            standard:
              precise-algorithm-class-name: com.test.algorithem.DatePreciseShardingAlgorithm
              range-algorithm-class-name: com.test.algorithem.DateRangeShardingAlgorithm
              shardingColumn: create_time # 分表键

          actual-data-nodes: ds0.test_account_log_$->{2020..2021}_$->{01..12}
          #主键策略
          key-generator:
            column: id
            props:
              worker:
                id: 1
            type: SNOWFLAKE