说明
环境: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:
- 官网的数据分片,同时包括了分库分表,考虑的有的公司可能只分库,不分表,所以笔者单独出了一个例子
分库不分表(读写分离)
步骤总结
- 配置主从数据源的连接信息,配置了3个master,每个master有两个slave
- 配置表的分片规则(参考本篇上半部分)
- 配置主数据源的读写分离规则,参考:https://developer.aliyun.com/article/791761?spm=a2c6h.13148508.0.0.454e4f0eC2QRzE
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