1 环境介绍

软件版本

  1. 1. VMware 15 pro
  2. 2. 虚机系统 SentOS7
  3. 3. shell工具 FinalShell3.6.2
  4. 4. mysql5.7.31

虚机作用

角色 IP 主机名 server_id 作用
Master1 192.168.1.201 myql-master 1 主库-写请求
Slave1 192.168.1.202 myql-slave1 2 从库-读请求,已开启bin-log
Slave2 192.168.1.203 myql-slave2 3 从库-读请求,已开启bin-log
Master2 192.168.1.211 myql-master 1 主库-写请求
Slave3 192.168.1.212 myql-slave1 2 从库-读请求,已开启bin-log
Slave4 192.168.1.213 myql-slave2 3 从库-读请求,已开启bin-log

2 项目介绍

  1. 引入依赖

    1. <properties>
    2.      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    3.      <project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
    4.      <shardingsphere.version>4.1.0</shardingsphere.version>
    5.      <springboot.version>2.2.5.RELEASE</springboot.version>
    6. </properties>
    7. <dependencyManagement>
    8. <dependencies>
    9. <dependency>
    10.   <groupId>org.springframework.boot</groupId>
    11.   <artifactId>spring-boot-starter-jdbc</artifactId>
    12.   <version>${springboot.version}</version>
    13. </dependency>
    14. <dependency>
    15.   <groupId>org.springframework.boot</groupId>
    16.   <artifactId>spring-boot-starter-data-jpa</artifactId>
    17.   <version>${springboot.version}</version>
    18. </dependency>
    19. <dependency>
    20.   <groupId>org.springframework.boot</groupId>
    21.   <artifactId>spring-boot-starter-test</artifactId>
    22.   <version>${springboot.version}</version>
    23.   <scope>test</scope>
    24. </dependency>
    25. <dependency>
    26.   <groupId>mysql</groupId>
    27.   <artifactId>mysql-connector-java</artifactId>
    28.   <version>5.1.48</version>
    29. </dependency>
    31. <dependency>
    32.   <groupId>org.apache.shardingsphere</groupId>
    33.   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    34.   <version>${shardingsphere.version}</version>
    35. </dependency>
    36. <dependency>
    37.   <groupId>org.projectlombok</groupId>
    38.   <artifactId>lombok</artifactId>
    39.   <version>1.18.12</version>
    40. </dependency>
    41. </dependencies>
    42. </dependencyManagement>

  2. 配置实体+DAO

  1. @Data
  2. @Entity
  3. @Table(name = "c_order",
  4.   indexes = {@Index(name = "idx_userId_positionId", columnList = "user_id,position_id"),
  5.             @Index(name = "idx_userId_operateTime", columnList = "user_id,update_time")} )
  6. public class COrder implements Serializable {
  8.     @Id
  9.     @Column(name = "id")
  10.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  11.     private long id;
  13.     @Column(name = "is_del")
  14.     private Boolean isDel;
  16.     @Column(name = "user_id")
  17.     private Integer userId;
  19.     @Column(name = "company_id")
  20.     private Integer companyId;
  22.     @Column(name = "publish_user_id")
  23.     private Integer publishUserId;
  25.     @Column(name = "position_id")
  26.     private long positionId;
  27.     //简历类型： 0附件 1在线
  28.     @Column(name = "resume_type")
  29.     private Integer resumeType;
  30.     //    投递状态 投递状态
  31.     //    WAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝
  32.     //    ARRANGE_INTERVIEW-通知⾯试
  33.     @Column(name = "status")
  34.     private String status;
  36.     @Column(name = "create_time")
  37.     private Date createTime;
  39.     @Column(name = "`update_time`")
  40.     private Date updateTime;
  41. }
  43. public interface COrderRepository extends JpaRepository<COrder, Long> {
  44. }
  1. 项目启动 ```java import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication public class RunBoot { }

  2. 2. 单元测试
  4. ```java
  5. import com.dd.edu.entity.COrder;
  6. import com.dd.edu.repository.COrderRepository;
  7. import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
  8. import org.junit.Test;
  9. import org.junit.runner.RunWith;
  10. import org.springframework.boot.test.context.SpringBootTest;
  11. import org.springframework.test.annotation.Repeat;
  12. import org.springframework.test.context.junit4.SpringRunner;
  14. import javax.annotation.Resource;
  15. import java.util.Date;
  16. import java.util.List;
  17. import java.util.Random;
  19. @RunWith(SpringRunner.class)
  20. @SpringBootTest
  21. public class ShardingMasterSlaveTest {
  23.     @Resource
  24.     private COrderRepository orderRepository;
  26.     @Test
  27.     public void testShardingCOrder(){
  29.         Random random = new Random();
  30.         for (int i = 0; i < 30; i++) {
  31.              int companyId = random.nextInt(10);
  32.              int userId = random.nextInt(10);
  34.              COrder order = new COrder();
  36.              order.setIsDel(false);
  37.              order.setCompanyId(companyId);
  38.              order.setPositionId(3242342);
  39.              order.setUserId(userId);
  40.              order.setPublishUserId(1111);
  41.              order.setResumeType(1);
  42.              order.setStatus("AUTO");
  43.              order.setCreateTime(new Date());
  44.              order.setUpdateTime(new Date());
  45.              orderRepository.save(order);
  46.         }
  47.         testShardingDataQuery();
  48.     }
  49.     @Test
  50.     public void testShardingDataQuery(){
  51.         System.out.println("______________________查询所有结果数据_______________________");
  52.         List<COrder> all = orderRepository.findAll();
  53.         for (COrder cOrder : all) {
  54.              System.out.println(cOrder);
  55.         }
  56.     }
  57. }
  1. sharding-jdbc配置
  1. #数据源
  2. spring.shardingsphere.datasource.names=master0,slave1,slave2,master1,slave3,slave4
  4. spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
  5. spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://192.168.1.201:3306/master?useUnicode=true&characterEncoding=utf-8&useSSL=false
  7. spring.shardingsphere.datasource.master0.username=root
  8. spring.shardingsphere.datasource.master0.password=root
  10. spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
  11. spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.1.202:3306/master
  13. spring.shardingsphere.datasource.slave1.username=root
  14. spring.shardingsphere.datasource.slave1.password=root
  16. spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
  17. spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
  18. spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.1.203:3306/master
  19. spring.shardingsphere.datasource.slave2.username=root
  20. spring.shardingsphere.datasource.slave2.password=root
  22. spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
  23. spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
  24. spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.1.211:3306/master?useUnicode=true&characterEncoding=utf-8&useSSL=false
  25. spring.shardingsphere.datasource.master1.username=root
  26. spring.shardingsphere.datasource.master1.password=root
  28. spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
  29. spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
  30. spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.1.212:3306/master
  31. spring.shardingsphere.datasource.slave3.username=root
  32. spring.shardingsphere.datasource.slave3.password=root
  34. spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
  35. spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
  36. spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.1.213:3306/master
  37. spring.shardingsphere.datasource.slave4.username=root
  38. spring.shardingsphere.datasource.slave4.password=root
  40. #分库分表
  41. #id : 通过雪花算法自动生成主键
  42. spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
  43. spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
  44. ## 分库字段 ： 根据id取模2，id为奇数的存入master1，偶数的存入master0
  45. spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=id
  46. spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master${id % 2 }
  47. ## 这里配置实际对应的数据库节点与表，所有的库与表必须实际存在，否则会抛异常：no table route info
  48. spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${0..1}.c_order${0..1}
  49. ## 分表字段 ： 根据user_id取模2，id为奇数的存入c_order1，偶数的存入c_order0
  50. spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=user_id
  51. spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order${user_id % 2}
  54. #读写分离：物理主从数据库配置
  55. spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
  56. spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave1,slave2
  57. spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
  58. spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave3,slave4

3 项目验证

执行单元测试方法：com.dd.edu.ShardingMasterSlaveTest#testShardingCOrder()

可以看到如下日志信息：

  1. 2020-08-04 21:55:56.266  INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: insert into c_order (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
  3. 2020-08-04 21:55:56.267  INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: master0 ::: insert into c_order0 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [3, 2020-08-04 21:55:55.464, false, 3242342, 1111, 1, AUTO, 2020-08-04 21:55:55.464, 8, 497527317707358208]
  4. 2020-08-04 21:55:56.341  INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: insert into c_order (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
  6. 2020-08-04 21:55:56.342  INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: master1 ::: insert into c_order0 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [9, 2020-08-04 21:55:56.339, false, 3242342, 1111, 1, AUTO, 2020-08-04 21:55:56.339, 6, 497527318495887361]
  7. 2020-08-04 21:55:56.363  INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: insert into c_order (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
  9. 2020-08-04 21:55:56.364  INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: master0 ::: insert into c_order1 (company_id, create_time, is_del, position_id, publish_user_id, resume_type, status, `update_time`, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [7, 2020-08-04 21:55:56.361, false, 3242342, 1111, 1, AUTO, 2020-08-04 21:55:56.361, 9, 497527318588162048]

可以看出执行插入的sql，会插入到master主库中；根据id字段值的奇数偶数的不同，自动解析到 Actual SQL: master0 或者 Actual SQL: master1 ；根据user_id字段值的奇数偶数的不同，自动解析到 c_order0 或 c_order1 。

  1. ______________________查询所有结果数据_______________________
  2. 2020-08-04 21:55:56.755  INFO 37160 --- [main] ShardingSphere-SQL : Logic SQL: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order corder0_
  4. 2020-08-04 21:55:56.756  INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave1 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order0 corder0_
  6. 2020-08-04 21:55:56.756  INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave2 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order1 corder0_
  8. 2020-08-04 21:55:56.756  INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave3 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order0 corder0_
  10. 2020-08-04 21:55:56.756  INFO 37160 --- [main] ShardingSphere-SQL : Actual SQL: slave4 ::: select corder0_.id as id1_0_, corder0_.company_id as company_2_0_, corder0_.create_time as create_t3_0_, corder0_.is_del as is_del4_0_, corder0_.position_id as position5_0_, corder0_.publish_user_id as publish_6_0_, corder0_.resume_type as resume_t7_0_, corder0_.status as status8_0_, corder0_.`update_time` as update_t9_0_, corder0_.user_id as user_id10_0_ from c_order1 corder0_

在查询所有数据时，会从所有的从库中查询数据，然后组合后返回给查询客户端；以上日志将查询sql，分解为4个从库查询sql；关键点Actual SQL: slave1 Actual SQL: slave2 Actual SQL: slave3 Actual SQL: slave4