(六)、Sharding-JDBC分库分表+读写分离

(六)、Sharding-JDBC分库分表+读写分离 首先,我们需要创建MySQL集群主从架构

*  6台

* 主master1负责写,从slave1 slave2负责读

* 主master2负责写,从slave3 slave4负责读

(六)、Sharding-JDBC分库分表+读写分离

 

 表结构: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());
        });
    }

}

测试插入结果如下:

(六)、Sharding-JDBC分库分表+读写分离

可以再控制台多翻一下日志,插入的时候只有master0和master1

测试查询结果如下:

(六)、Sharding-JDBC分库分表+读写分离

分库分表参考

SpringBoot2.0.3.RELEASE+sharding-jdbc4.1.0+mybatis-plus3.4.1+druid1.1.22 快速搭建分库分表 - 劈天造陆 - 博客园 (cnblogs.com) 

完整代码及sql脚本在:

https://gitee.com/xiaorenwu_dashije/sharding-sphere-demo.git

     

上一篇:分表分库-Sharding-JDBC-入门案例


下一篇:SCTL 涅槃重生:投入 RAL 的怀抱