sharding-jdbc实现2主4从数据分片

1. 环境软件版本

环境&软件	版本
虚拟机&VMware Workstation Pro	VMwareworkstation15.5.6
服务器&Centos	CentOS-7-x86_64-DVD-1503-01.iso
数据库&mysql	mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
远程连接&Xshell	5
远程文件传输&Xftp	5

2. 环境架构设计

机器名称	IP	角色	权限
mysql-master1	192.168.5.128	mysql主库1	读写
mysql-slave1	192.168.5.129	mysql从库1	读
mysql-slave2	192.168.5.131	mysql从库2	读
mysql-master2	192.168.5.133	mysql主库1	读写
mysql-slave3	192.168.5.134	mysql从库3	读
mysql-slave4	192.168.5.135	mysql从库4	读

三、搭建步骤

1. 对6台服务器，每台安装mysql，分成2组搭建mysql主从复制，一主2从
2. master1，master2执行sql脚本，创建数据库表c_order1,c_order2,用于分库分表操作。

CREATE TABLE `c_order1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删\r\n除',
  `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 '简历类型：\r\n0附件 1在线',
  `status` varchar(256) NOT NULL COMMENT '投递状态 投递状态\r\nWAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝\r\nARRANGE_INTERVIEW-通知⾯试',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '处理时间',
  PRIMARY KEY (`id`),
  KEY `index_userId_positionId` (`user_id`,`position_id`),
  KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=533958753645494273 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `c_order2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删\r\n除',
  `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 '简历类型：\r\n0附件 1在线',
  `status` varchar(256) NOT NULL COMMENT '投递状态 投递状态\r\nWAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝\r\nARRANGE_INTERVIEW-通知⾯试',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '处理时间',
  PRIMARY KEY (`id`),
  KEY `index_userId_positionId` (`user_id`,`position_id`),
  KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=533958753645494273 DEFAULT CHARSET=utf8mb4;

4. 创建maven项目，引入依赖

<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.complie.sourceEncoding>UTF-8</project.complie.sourceEncoding>
        <shardingsphere.version>4.1.0</shardingsphere.version>
        <springboot.version>2.2.5.RELEASE</springboot.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${springboot.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
    </dependencies>

5. 主配置信息application.properties

spring.profiles.active=sharding-database
spring.shardingsphere.props.sql.show=true

6. sharding-jdbc配置信息

#datasource
spring.shardingsphere.datasource.names=master1,slave1,slave2,master2,slave3,slave4
#master1,slave2为主库
#slave1,slave2,master3,master4为从库

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.5.128/lagou
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.5.129/lagou
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root

spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.5.131/lagou
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root

spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://192.168.5.133/lagou
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root

spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.5.134/lagou
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root

spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.5.135/lagou
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root

#sharding-database
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id % 2 + 1}

#读写分离
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1,slave2
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave3,slave4

#id生成策略，使用雪花算法生成id
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

## 实际操作节点，具体操作的库和表
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${1..2}.c_order${1..2}

### 分表规则：对id取模2，奇偶分表
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 + 1}

7. 实体类Order

@Entity
@Table(name = "c_order")
public class Order implements Serializable {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "is_del")
    private Boolean isDel;
    @Column(name = "user_id")
    private Integer userId;
    @Column(name = "company_id")
    private Integer companyId;
    @Column(name = "publish_user_id")
    private Integer publishUserId;
    @Column(name = "position_id")
    private Integer positionId;
    @Column(name = "resume_type")
    private Integer resumeType;
    @Column(name = "status")
    private String status;
    @Column(name = "create_time")
    private Date createTime;
    @Column(name = "update_time")
    private Date updateTime;
    ........
    节省部分代码，get，set方法

8. 操作数据库的jpa接口

import com.lagou.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<Order,Long> {
}

9. springboot启动类

@SpringBootApplication
public class RunBoot {
}

10. Test测试方法

@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestShardingDatabase {

    @Autowired
    private OrderRepository orderRepository;

    @Test
     public void testAdd(){
        for (int i = 0; i < 10; i++) {
            Order order = new Order();
            order.setDel(true);
            order.setUserId(order.hashCode());
            order.setCompanyId(i);
            order.setPublishUserId(i);
            order.setPositionId(i);
            order.setResumeType(i);
            order.setStatus(String.valueOf(i));
            order.setCreateTime(new Date());
            order.setUpdateTime(new Date());
            orderRepository.save(order);
        }
    }

    @Test
    public void testGet(){
        List<Order> orderList = orderRepository.findAll();
        for (Order order : orderList) {
            System.out.println(order);
        }
    }
}

11. 进行插入数据操作，使用user_id进行分库，使用id进行分表
    
12. 对从库进行查询
    
13. 数据库表内容