* 6台
* 主master1负责写,从slave1 slave2负责读
* 主master2负责写,从slave3 slave4负责读
表结构:c_order0和c_order1相同
CREATE TABLE `c_order0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删除', `user_id` int(11) NOT NULL COMMENT '用户id', `company_id` int(11) NOT NULL COMMENT '公司id', `publish_user_id` int(11) NOT NULL COMMENT 'B端用户id', `position_id` int(11) NOT NULL COMMENT '职位ID', `resume_type` int(2) NOT NULL DEFAULT '0' COMMENT '简历类型:0 附件 1 在线', `status` varchar(256) NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试', `create_time` datetime NOT NULL COMMENT '创建时间', `update_time` datetime NOT NULL COMMENT '处理时间', PRIMARY KEY (`id`) USING BTREE, KEY `index_userId_positionId` (`user_id`,`position_id`) USING BTREE, KEY `idx_userId_operateTime` (`user_id`,`update_time`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
配置如下:
# 命名数据源 这个是自定义的 spring.shardingsphere.datasource.names=master0,slave0,slave1,master1,slave2,slave3 # 配置数据源master0 spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.master0.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master0.url=jdbc:mysql://192.168.77.100:3306/test_m_s?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.shardingsphere.datasource.master0.username=root spring.shardingsphere.datasource.master0.password=123456 # 配置数据源slave0 spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave0.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave0.url=jdbc:mysql://192.168.77.110:3306/test_m_s?useSSL=false spring.shardingsphere.datasource.slave0.username=root spring.shardingsphere.datasource.slave0.password=123456 # 配置数据源slave1 spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave1.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave1.url=jdbc:mysql://192.168.77.120:3306/test_m_s?useSSL=false spring.shardingsphere.datasource.slave1.username=root spring.shardingsphere.datasource.slave1.password=123456 # 配置数据源master1 spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.master1.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master1.url=jdbc:mysql://192.168.77.130:3306/test_m_s?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.shardingsphere.datasource.master1.username=root spring.shardingsphere.datasource.master1.password=123456 # 配置数据源slave2 spring.shardingsphere.datasource.slave2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave2.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave2.url=jdbc:mysql://192.168.77.140:3306/test_m_s?useSSL=false spring.shardingsphere.datasource.slave2.username=root spring.shardingsphere.datasource.slave2.password=123456 # 配置数据源slave3 spring.shardingsphere.datasource.slave3.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave3.driverClassName=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave3.url=jdbc:mysql://192.168.77.150:3306/test_m_s?useSSL=false spring.shardingsphere.datasource.slave3.username=root spring.shardingsphere.datasource.slave3.password=123456 # 配置分库分表 spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=company_id spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{company_id % 2} spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master$->{0..1}.c_order$->{0..1} spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order$->{id % 2} # 配置读写分离 spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0 spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0,slave1 spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1 spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave2,slave3 # 配置主键生成规则 spring.shardingsphere.sharding.tables.c_order.key-generator.column=id # 使用雪花算法 spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
测试代码如下
package com.qjc; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.qjc.entity.COrder; import com.qjc.entity.TCity; import com.qjc.mapper.COrderMapper; import com.qjc.mapper.TCityMapper; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.annotation.Repeat; import org.springframework.test.context.junit4.SpringRunner; import javax.annotation.Resource; import java.util.Date; import java.util.List; import java.util.Random; @RunWith(SpringRunner.class) @SpringBootTest(classes = ShardingSphereDemoApplication.class) public class TestShardingMasterSlaves { @Resource private COrderMapper orderMapper; @Test @Repeat(10) public void testShardingCOrder() { Random random = new Random(); int companyId = random.nextInt(10); COrder order = new COrder(); order.setIsDel(false); order.setCompanyId(companyId); order.setPositionId(3242342L); order.setUserId(2222); order.setPublishUserId(1111); order.setResumeType(1); order.setStatus("AUTO"); order.setCreateTime(new Date()); order.setUpdateTime(new Date()); orderMapper.insert(order); } @Test public void testFind() { List<COrder> list = orderMapper.selectList(new QueryWrapper<>()); list.forEach(city -> { System.out.println(city.getId() + " " + city.getCompanyId()); }); } }
测试插入结果如下:
可以再控制台多翻一下日志,插入的时候只有master0和master1
测试查询结果如下:
分库分表参考
完整代码及sql脚本在:
https://gitee.com/xiaorenwu_dashije/sharding-sphere-demo.git