sharding-jdbc实现mysql分库不分表

说明

环境:Spring boot:2.1.11.RELEASE,mybatis plus3.4.1,sharing-jdeb:4.1.1

说明:

  • 配置了三个数据源,数据根据一定的分片规则,分别分布在不同的数据库进行存储,读写都会在同一个数据库
  • yourdburl:项目的实际数据库地址,yourdbname:项目的实际数据库名称
  • yourdbusername:项目的数据库登录用户名称,yuordbpassword:项目的数据库登录密码
  • your-table-name:项目的表名称
  • id:用于确定数据分片的表的字段,根据该字段对数据库的数量进行取模,选择数据实际读写的数据库,可根据实际情况指定该字段

分库不分表

application.properties

# mybatis plus配置
mybatis-plus.mapper-locations=classpath:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.spring.db.springdb.entity
mybatis-plus.global-config.db-config.id-type=auto
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.cache-enabled=false
#返回map时,true:当查询数据为空时字段返回为null;false:不加这个查询数据为空时,字段将被隐藏
mybatis-plus.configuration.call-setters-on-nulls=false
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

# sharding jdbc 读写分离
spring.shardingsphere.datasource.names=ds0,ds1,ds2

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds0.username=yourdbusername
spring.shardingsphere.datasource.ds0.password=yuordbpassword

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds1.username=yourdbusername
spring.shardingsphere.datasource.ds1.password=yuordbpassword

spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds2.username=yourdbusername
spring.shardingsphere.datasource.ds2.password=yuordbpassword

# 配置一个表的分库规则
spring.shardingsphere.sharding.tables.your-table-name.actual-data-nodes=ds$->{0..2}.your-table-name

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 3}

# 指定具体的表的数据库分片策略,未指定的使用默认的分库规则
# 行表达式分片策略
spring.shardingsphere.sharding.tables.your-table-name.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.your-table-name.database-strategy.inline.algorithm-expression=ds$->{id % 3}

# 打印sharding的数据库信息,可以看出每次执行的sql语句是选用的哪个数据库
spring.shardingsphere.props.sql.show=true

参考资料

ps:

  • 官网的数据分片,同时包括了分库分表,考虑的有的公司可能只分库,不分表,所以笔者单独出了一个例子

分库不分表(读写分离)

步骤总结

application.properties

# mybatis plus配置
mybatis-plus.mapper-locations=classpath:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.spring.db.springdb.entity
mybatis-plus.global-config.db-config.id-type=auto
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.cache-enabled=false
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

# 分库不分表,主从复制读写分离
spring.shardingsphere.datasource.names=master0,master0slave0,master0slave1,master1,master1slave0,master1slave1,master2,master2slave0,master2slave1

# 数据源连接信息
spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master0.username=yourdbusername
spring.shardingsphere.datasource.master0.password=yuordbpassword

spring.shardingsphere.datasource.master0slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0slave0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master0slave0.username=yourdbusername
spring.shardingsphere.datasource.master0slave0.password=yuordbpassword

spring.shardingsphere.datasource.master0slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0slave1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master0slave1.username=yourdbusername
spring.shardingsphere.datasource.master0slave1.password=yuordbpassword

spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master1.username=yourdbusername
spring.shardingsphere.datasource.master1.password=yuordbpassword

spring.shardingsphere.datasource.master1slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1slave0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master1slave0.username=yourdbusername
spring.shardingsphere.datasource.master1slave0.password=yuordbpassword

spring.shardingsphere.datasource.master1slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1slave1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master1slave1.username=yourdbusername
spring.shardingsphere.datasource.master1slave1.password=yuordbpassword

spring.shardingsphere.datasource.master2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master2.username=yourdbusername
spring.shardingsphere.datasource.master2.password=yuordbpassword

spring.shardingsphere.datasource.master2slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master2slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2slave0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master2slave0.username=yourdbusername
spring.shardingsphere.datasource.master2slave0.password=yuordbpassword

spring.shardingsphere.datasource.master2slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master2slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2slave1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master2slave1.username=yourdbusername
spring.shardingsphere.datasource.master2slave1.password=yuordbpassword

# 数据库主从规则配置
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0,master0slave1
spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robin

spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0,master1slave1
spring.shardingsphere.sharding.master-slave-rules.ds1.load-balance-algorithm-type=round_robin

spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=master2slave0,master2slave1
spring.shardingsphere.sharding.master-slave-rules.ds2.load-balance-algorithm-type=round_robin

# 每个表的实际数据节点信息,这里的数据节点就是用主从规则中的数据库实例名称代替
spring.shardingsphere.sharding.tables.your-table-name-a.actual-data-nodes=ds$->{0..2}.your-table-name-a
spring.shardingsphere.sharding.tables.your-table-name-b.actual-data-nodes=ds$->{0..2}.your-table-name-b
spring.shardingsphere.sharding.tables.your-table-name-b.actual-data-nodes=ds$->{0..2}.your-table-name-c

# 默认的分库规则
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 3}

# 为某个表指定自己的分库规则,没有单独指定,采用默认规则
spring.shardingsphere.sharding.tables.your-table-name-a.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.your-table-name-a.database-strategy.inline.algorithm-expression=ds$->{id % 3}

spring.shardingsphere.props.sql.show=true

参考资料

上一篇:Qt界面UI之QML初见(学习笔记四)


下一篇:STAR-H1208M集线器不支持同时挂载多个nfs