IDEA操作Sharding-JDBC实战1

一、分库实战

1.环境搭建

• 通过docker启动一个新的MySQL服务：docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name=mysql1 mysql:latest

• 进入容器：docker exec -it mysql1 bash

• 只显示bash-4.4，在容器中输入：cp /etc/skel/.bash* /root/
  然后exit退出，再次进入容器

• 进入到mysql命令行：mysql -uroot -p123456

• 进入mysql数据库，查看用户信息：use mysql，update user set host="%" where user ="root";

• 如果root用户的host字段不是%，则修改为任意主机都可以连接root用户：update user set host="%" where user ="root";

• 如果mysql为8.0.4以上版本，则修改plugin密码机制为mysql_native_password并指定密码：ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

• 使用SQLyog连接该MySQL服务，其他可视化工具也可以：
  

• 创建两个数据库：
  

• 在两个数据库中都创建position和position_detail表：

  CREATE TABLE `position` (
    `id` bigint(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) DEFAULT NULL,
    `salary` varchar(50) DEFAULT NULL,
    `city` varchar(256) DEFAULT NULL,
    PRIMARY KEY (`Id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  CREATE TABLE `position_detail` (
    `id` bigint(11) NOT NULL AUTO_INCREMENT,
    `pid` bigint(11) NOT NULL DEFAULT '0',
    `description` text DEFAULT NULL,
    PRIMARY KEY (`Id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

• 创建一个空Maven项目，如下：
  

• 在pom.xml中添加依赖：

  <dependencies>
          <!-- springboot的对象映射框架 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-jdbc</artifactId>
              <version>2.7.3</version>
          </dependency>
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-data-jpa</artifactId>
              <version>2.7.3</version>
          </dependency>
          <!-- springboot测试依赖 -->
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
              <version>2.7.3</version>
          </dependency>
          <!-- mysql驱动 -->
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>8.0.30</version>
          </dependency>
          <!-- springboot整合sharding-jdbc依赖 -->
          <dependency>
              <groupId>org.apache.shardingsphere</groupId>
              <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
              <version>4.1.1</version>
          </dependency>
          <dependency>
          <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
              <version>1.18.24</version>
     		</dependency>
      </dependencies>
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36

2.分库业务的实现

• 在java根目录下，创建包com.zzx.entity，在该包下创建实体类Position：

  @Data
  @Entity
  @Table(name="position")
  public class Position implements Serializable {
      @Id
      @Column(name="id")
      private Long id;
      @Column(name="name")
      private String name;
      @Column(name="salary")
      private Double salary;
      @Column(name="city")
      private String city;
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

  在字段id上加@GeneratedValue(strategy = GenerationType.IDENTITY) ，表示主键自增

• 在java根目录下，创建包com.zzx.repository，在该包下创建接口PositionRepository：

  public interface PositionRepository extends JpaRepository<Position,Long> {
  }
  1
  2

• 在com.zzx包下创建Springboot主启动类RunBoot（直接创建springboot项目的话，名字是项目名加Application）：

  @SpringBootApplication
  public class RunBoot {
      public static void main(String[] args) {
          SpringApplication.run(RunBoot.class,args);
      }
  }
  1
  2
  3
  4
  5
  6

• 在resource目录下创建主配置文件application.properties：

  #指定Sharding-JDBC配置文件的名称
  spring.profiles.active=sharding-database
  #将sharding-jdbc执行的sql文件打印出来
  spring.shardingsphere.props.sql.show=true
  1
  2
  3
  4

• 在resource目录下创建sharding-jdbc的配置文件application-sharding-database.properties：

  #datasource
  spring.shardingsphere.datasource.names=ds0,ds1
  #ds0
  spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
  spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
  spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.126.21:3306/zzx1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
  spring.shardingsphere.datasource.ds0.username=root
  spring.shardingsphere.datasource.ds0.password=123456
  #ds1
  spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
  spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
  spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.126.21:3306/zzx2?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
  spring.shardingsphere.datasource.ds1.username=root
  spring.shardingsphere.datasource.ds1.password=123456
  
  #sharding-database
  spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
  spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds${id%2}
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18

3.分库测试

• 在Test根目录下，创建Test测试类TestPosition：

  @SpringBootTest(classes = RunBoot.class)
  public class TestPosition {
      @Resource
      private PositionRepository positionRepository;
      
      @Test
      public void testAdd()
      {
          for (int i = 1; i <= 20; i++) {
              Position position = new Position();
              position.setId((long)i);
              position.setName("zzx"+i);
              position.setSalary(2000d);
              position.setCity("shenzhen");
              positionRepository.save(position);
          }
      }
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18

• 执行后第二个数据库的position表，如下
  
  即完成了分库操作

4.主键生成器（雪花算法）

• 将position实体类的id字段修改为如下，即加上主键自增的策略：

  @Id
  @Column(name="id")
  @GeneratedValue(strategy = GenerationType.IDENTITY) //自增策略
  private Long id;
  1
  2
  3
  4

• 在application-sharding-database.properties配置文件中，添加如下配置：

  #主键生成器
  spring.shardingsphere.sharding.tables.position.key-generator.column=id
  spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
  1
  2
  3

  即使用雪花算法生成主键，字段为id。

雪花算法：使用一个 64 bit 的 long 型的数字作为全局唯一 ID。在分布式系统中的应用十分广泛，且 ID 引入了时间戳，基本上保持自增的。

• 在PositionTest测试类中，修改Test类：

  @Test
      public void testAdd()
      {
          for (int i = 1; i <= 20; i++) {
              Position position = new Position();
              //position.setId((long)i);
              position.setName("zzx"+i);
              position.setSalary(2000d);
              position.setCity("shenzhen");
              positionRepository.save(position);
          }
      }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

• 此时第二个数据库为
  

5.拆表与分库

• 配置从表position_detail跟随主表position：

  #position_detail
  spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pid
  spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds${pid%2}
  #主键生成器
  spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
  spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE
  1
  2
  3
  4
  5
  6

  即配置从表的分库策略，以及主键生成

• 在com.zzx.entity包下，创建实体类PositionDetail：

  @Data
  @Entity
  @Table(name ="position_detail")
  public class PositionDetail implements Serializable {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
      
      @Column(name ="pid")
      private Long pid;
      
      @Column(name="description")
      private String description;
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

• 在com.zzx.repository包下，创建接口PositionDetailRepository：

  public interface PositionDetailRepository extends JpaRepository<PositionDetail,Long> {
  }
  1
  2

  继承JpaRepository，并传入两个参数，第一个是实体类对象，另一个是主键类型。

• 在PositionTest测试类中，创建一个新的测试方法：

  @Resource
  private PositionDetailRepository positionDetailRepository;
  @Test
  public void testAdd2()
  {
      for (int i = 1; i <= 20; i++) {
          PositionDetail positionDetail = new PositionDetail();
          positionDetail.setPid((long)i);
          positionDetail.setDescription("description"+i);
          positionDetailRepository.save(positionDetail);
      }
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

  此时一个数据库中的position.id与与postion_detail.pid相同时，从表跟随主表的配置就成功了。

• 接下来进行一个关联查询测试，在PositionDetailRepository接口中添加：

  @Query(nativeQuery = true,value = "SELECT p.id,p.name,p.salary,p.city,pd.description FROM position p JOIN position_detail pd ON p.id=pd.pid WHERE p.id= :id")
  Object findPositionById(@Param("id")long id);
  1
  2

• 在PositionTest测试类中，创建一个新的测试方法：

  @Test
  public void testQuery()
  {
      Object o = positionDetailRepository.findPositionById(2);
      Object[] position = (Object[]) o;
      for (int i = 0; i < 5; i++) {
          System.out.println(position[i]);
      }
  
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  如果不用Object，则需要创建一个类来映射查询到的数据。

6.广播表

• 使用SQLyog在两个数据库中创建city表

  CREATE TABLE `city` (
    `id` bigint(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) DEFAULT NULL,
    `province` varchar(256) DEFAULT NULL,
    PRIMARY KEY (`Id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1
  2
  3
  4
  5
  6

• 在com.zzx.entity包下，创建实体类City：

  @Entity
  @Data
  @Table(name="city")
  public class City implements Serializable {
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
  
      @Column(name = "name")
      private String name;
  
      @Column(name = "province")
      private String province; 
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

• 在com.zzx.repository包下，创建一个接口CityRepository：

  public interface CityRepository extends JpaRepository<City,Long> {
  }
  1
  2

• 在application-sharding-database.properties配置文件中，添加如下配置（即广播表city的配置）：

  # Broadcast(广播表) 相当于mycat的全局表
  spring.shardingsphere.sharding.broadcast-tables=city
  spring.shardingsphere.sharding.tables.city.key-generator.column=id
  spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
  1
  2
  3
  4

• 在PositionTest测试类中，创建一个新的测试方法：

  @Resource
  private CityRepository cityRepository;
  @Test
  public void testBroadcast()
  {
      City city = new City();
      city.setName("shenzhen");
      city.setProvince("guangdong");
      cityRepository.save(city);
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  此时两个数据库都有这条数据时，即配置成功。

二、读写分离实战

1.读写分离与数据分片概念

• 读写分离是通过主从配置的方式，将查询请求均匀的分散到多个数据副本，进一步的提升系统的处理能力。
• 主库：添加、更新以及删除数据操作所使用的的数据库。
• 从库：查询数据操作所使用的数据库。
• 主从架构：读写分离的目的是高可用、读写扩展。主从库内容相同，根据SQL语义进行路由。
• 分库分表架构：数据分片，目的是读写扩展、数据扩容，库和表内容不同，根据分片配置进行路由。将水平分片和读写分离联合使用，能够更加有效的提升系统性能。
• 读写分离虽然可以提升系统的吞吐量和可用性，但同时也带来了数据不一致问题，包括多个主库之间的数据一致性，以及主库与从库的数据一致性问题。并且会使得应用开发和对数据库的操作与运维变得更加复杂。

2.配置主从架构

• 配置mysql主节点：

  server-id=21
  log_bin=mysql_bin_log
  character_set_server=utf8
  binlog_do_db=sxt_his
  binlog-ignore-db=performance_schema
  binlog-ignore-db=information_schema
  1
  2
  3
  4
  5
  6

  此时mysql得先有这些具体数据库才能配置

• 配置mysql从节点：

  server-id=22
  log_bin=mysql_bin_log
  character_set_server=utf8
  default_time_zone=+8:00
  symbolic-links=0
  log-error=/var/log/mysqld.log
  1
  2
  3
  4
  5
  6

• 修改密码机制：ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

• 查看主库状态：show master status;

• 进入MySQL从库，指定主库master：change master to master_host='192.168.126.21',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql_bin_log.000002',master_log_pos=157;

• 启动从库的主从复制：start slave;

• 查看从库主从复制的状态：show slave status \G;

3.读写分离架构剖析

读写分离方案：

1. 分库+读写分离
   在数据量不多的情况下，可以将数据库进行读写分离，以应对高并发的需求，通过水平扩展从库，来缓解查询的压力。
2. 分表+读写分离
   在数据量达到500W时，这时数据量预估千万级别，可以将数据进行分表存储。
3. 分库分表+读写分离
   在数据量继续扩大，这时可以考虑分库分表，将数据存储在不同数据库的不同表中。
   透明化读写分离所带来的影响，让使用方尽量像使用一个数据库一样使用使用主从数据库集群，是ShardingSphere读写分离模块的主要设计目标。

核心功能，提供一主多从的读写分离配置。仅支持单主库，可以支持独立使用，也可以配置分库分表使用。

• 独立使用读写分离，支持SQL透传。即不需要SQL改写流程。

对于分布式实例，会对SQL进行语法解析，有一定的限制，如果用户想在某个set中获取单个节点数据，或在指定节点执行SQL，可以使用SQL透传的功能。即后面加-c。
SQL透传，即语法不解析，直接透传。

• 同一线程且同一数据库连接内，能保证数据一致性。如果有写入操作，后续的读操作均从主库读取。基于Hint的强制主库路由。可以强制路由走主库查询实时数据，避免主从同步数据延迟。

不支持项

• 主库和从库的数据同步
• 主库和从库的数据同步延迟
• 主库双写或多写
• 跨主库和从库之间的事务的数据不一致。建议在主从架构中，事务中的读写均用主库操作。

4.读写分离实战

• 在resources目录下创建一个主从配置文件application-master-slave.properties：

  # datasource
  spring.shardingsphere.datasource.names=master,slave0
  # master
  spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
  spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
  spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.126.21:3306/zzx1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
  spring.shardingsphere.datasource.master.username=root
  spring.shardingsphere.datasource.master.password=123456
  # slave0
  spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
  spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
  spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.126.22:3306/zzx1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
  spring.shardingsphere.datasource.slave0.username=root
  spring.shardingsphere.datasource.slave0.password=123456
  
  # master-slave
  spring.shardingsphere.masterslave.name=datasource
  spring.shardingsphere.masterslave.master-data-source-name=master
  spring.shardingsphere.masterslave.slave-data-source-names=slave0
  spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN
  
  # 主键生成器
  # city
  spring.shardingsphere.sharding.tables.city.key-generator.column=id
  spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25

  配置读写分离，查询时使用轮询的负载均衡算法；配置使用读写分离的表，并对表配置主键生成器及算法。

• 在主配置文件application.properties中指定配置文件的名称：

  #指定Sharding-JDBC配置文件的名称
  spring.profiles.active=master-slave
  1
  2

• 在Test目录下创建测试类TestMasterSlave，添加如下代码：

  @SpringBootTest(classes = RunBoot.class)
  public class TestMasterSlave {
      @Resource
      private CityRepository cityRepository;
  
      @Test
      public void testAdd()
      {
          City city = new City();
          city.setName("shenzhen");
          city.setProvince("guangdong");
          cityRepository.save(city);
      }
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

  因为配置时，设置仅复制master指定数据库的记录binlog_do_db=sxt_his。所以此时主从复制对其他数据库没有作用，只有主库存在数据，需要去配置指定数据库。

总结：

1. Sharding-JDBC分库，首先需要引入Sharding-JDBC相关依赖，其次是在配置文件中配置Sharding-JDBC。
   因为使用JPA的ORM框架，所以采用在repository接口继承JpaRepository类，并传入实体类以及主键类型。在使用JPA时，只需要@Resouce注入对应的接口类，直接引用即可。
2. sharding-jdbc读写分离方案有三种，分库+读写分离、分表+读写分离和分库分表+读写分离。
   sharding-jdbc的核心功能有，提供一主多从的读写分离配置，仅支持单主库；当独立使用读写分离时，支持SQL透传；同一线程且同一数据库连接内，可以保证数据一致性。
   事务中的读写均用主库操作。