MySQL的主从复制+读写分离+分库分表，看这一篇文章就够了

小学生波波

已于 2022-10-20 08:53:11 修改

阅读量1.9k

点赞数 6

分类专栏： Mysql 面试宝典文章标签：数据库 mysql 服务器

于 2022-08-01 12:17:32 首次发布

本文链接：https://blog.csdn.net/wujiangbo520/article/details/126098651

版权

面试宝典同时被 2 个专栏收录

17 篇文章 1 订阅

订阅专栏

Mysql

4 篇文章 1 订阅

订阅专栏

文章目录

1、为什么需要主从复制
2、MySQL主从复制概念
3、主从复制主要用途
4、MySQL主从形式
5、主从复制原理
6、主从复制-实战
7、中间件框架
- 7.1、Sharding-JDBC介绍
- 7.2、ShardingSphere
8、读写分离
9、分库分表
10、什么时候考虑切分
总结

1、为什么需要主从复制

在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
数据热备
架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

总而言之一句话，就是为了提高数据库的并发性能

你想，假设是单机，读写都在一台MySQL上面完成，性能肯定不高。如果有三台MySQL，一台主机只负责写操作，两台备机只负责读操作，性能不就能大大提高了吗？

主机：一般叫做 master，备机：一般叫做 slave

2、MySQL主从复制概念

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。
MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

3、主从复制主要用途

读写分离

在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

数据实时备份，当系统中某个节点出现故障的时候，方便切换
高可用HA
架构扩展

随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

HA是Highly Available缩写，是双机集群系统简称，提高可用性集群，是保证业务连续性的有效解决方案，一般有两个或两个以上的节点，且分为活动节点及备用节点

4、MySQL主从形式

MySQL主从形式主要分为如下几种：

一主一从
一主多从，提高系统读性能
多主一从（从5.7开始支持）
双主复制
级联复制

说明：

1、一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

2、多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

3、双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

4、级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

5、主从复制原理

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示：

在这里插入图片描述

流程描述：

当 master 节点进行insert、update、delete操作时，会按顺序写入到二进制日志 binary log 中。
只要有一个 salve 连接 master，master 就会创建一个 binary log dump thread。
当 master 节点的 binary log 发生变化时， binary log dump thread 会通知所有salve节点，并将相应的 binary log 内容推送给slave节点。
I/O thread 接收到 binary log 内容后，将内容写入到本地的 relay log 中。
SQL thread读取I/O thread 写入的 relay log，并且根据 relay log 的内容对从数据库做对应的操作。

三个线程作用如下：

1、主节点 binary log dump thread 作用
当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成发动给从节点之前，锁会被释放。

2、从节点I/O thread 作用
当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。

3、从节点SQL thread 作用
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

解读：

对于每一个主从连接，都需要三个进程来完成
当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 线程，而每个从节点都有自己的I/O线程，SQL线程。
从节点用两个线程将从主库拉取更新和执行更新，分成独立的任务，这样在执行同步数据任务的时候，就不会降低读操作的性能。

6、主从复制-实战

6.1、环境如下

主库：119.45.157.94

从库：124.222.59.129

两台服务器上都已安装了MySQL数据库，版本都是5.5.20

6.2、主库配置

1、主库中新建测试数据库，名为：testdb

2、进入MySQL安装目录，找到my.ini配置文件，在mysqld下面新增如下配置

# 主从复制
#[必须]启用二进制日志
log-bin=mysql-bin
#[必须]服务器唯一ID，默认是1，最好取服务器IP的后3位
server-id=1
#只保留7天的二进制日志，以防磁盘被日志占满
expire-logs-days=7
#需要做复制的数据库名，如果有多个，复制多份指定数据库名即可
binlog_do_db=testdb
#不备份的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=sys

3、重启MySQL服务

登录mysql，测试log_bin是否成功开启

$ mysql -u root -p
  123456

// log_bin ON表示开启成功，OFF表示开启失败
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)

4、新增备份账户

先登录MySQL，以管理员身份打开cmd窗口后，运行mysql -uroot -p，然后回车，输入数据库密码，然后再依次输入下面命令：

CREATE USER 'myslave'@'124.222.59.129' IDENTIFIED BY '258369';
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'124.222.59.129';
FLUSH PRIVILEGES;

5、查看 master 状态，记录二进制文件名和位置，执行下面命令：

show master status;

在这里插入图片描述

二进制文件为mysql-bin.000003，位置为107，待会需要用到这个数据的，先记录下来

6.3、从库配置

1、进入MySQL安装目录，找到my.ini配置文件，在mysqld下面新增如下配置

server-id=2
log-bin=mysql-bin

2、重启MySQL服务

3、以管理员身份打开cmd窗口后，运行mysql -uroot -p，然后回车，输入数据库密码，然后再输入下面命令

change master to master_host='119.45.157.94',master_port=3306,master_user='myslave',master_password='258369',master_log_file='mysql-bin.000003',master_log_pos=8126;

在这里插入图片描述

4、启动salve同步进程，继续输入下面命令：

start slave;

在这里插入图片描述

5、查看slave状态，继续输入下面命令：

show slave status\G;

在这里插入图片描述

Slave_IO_Running: Yes，Slave_SQL_Running: Yes，说明两个线程已启动，主从复制配置成功

6.4、测试

刷新从库，就会有testdb数据库了，然后从库可以新增表，然后往表里新增数据，或者删除数据，再刷新从库，都会有一样的数据，说明测试成功，到此，我们主从复制就设置好了

7、中间件框架

站在巨人的肩膀上能省力很多，目前分库分表已经有一些较为成熟的开源解决方案：

sharding-jdbc（当当）
TSharding（蘑菇街）
Atlas（奇虎360）
Cobar（阿里巴巴）
MyCAT（基于Cobar）
Oceanus（58同城）
Vitess（谷歌）

注意：以上工具的利弊，请自行调研，官网和社区优先参考，我们这里就不全部介绍了，我们这里重点介绍Sharding-JDBC

7.1、Sharding-JDBC介绍

Sharding-JDBC 最早是当当网内部使用的一款分库分表框架，到2017年的时候才开始对外开源，这几年在大量社区贡献者的不断迭代下，功能也逐渐完善，现已更名为 ShardingSphere，2020年4⽉16日正式成为 Apache 软件基金会的顶级项目
Sharding-JDBC 定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

我的理解：Sharding-JDBC 就是增强版的JDBC驱动，客户端使用的时候，就像正常使用JDBC驱动一样，引入Sharding-JDBC依赖包，连接好数据库，配置好分库分表规则，读写分离配置，然后客户端的sql 操作 Sharding-JDBC会自动根据配置完成分库分表和读写分离操作。

7.2、ShardingSphere

随着版本的不断更迭 ShardingSphere 的核心功能也变得多元化起来

从最开始 Sharding-JDBC 1.0 版本只有数据分片，到 Sharding-JDBC 2.0 版本开始支持数据库治理（注册中心、配置中心等等），再到 Sharding-JDBC 3.0版本又加分布式事务（支持 Atomikos、Narayana、Bitronix、Seata），如今已经迭代到了 Sharding-JDBC 4.0 版本

在这里插入图片描述

现在的 ShardingSphere 不单单是指某个框架而是一个生态圈，这个生态圈 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar 这三款开源的分布式数据库中间件解决方案所构成。

ShardingSphere 的前身就是 Sharding-JDBC，所以它是整个框架中最为经典、成熟的组件，先从 Sharding-JDBC 框架开始学习分库分表和读写分离

8、读写分离

上面我们已经准备好了主库和从库的环境了：

主库：119.45.157.94

从库：124.222.59.129

那么我们接下来需要做的事情就是，数据往主库里写，然后查询时，数据是来自从库，这样主库和从库的压力就可以分摊了，这是我们需要达到的目的

1、表准备

在我们上面提到的主库中新增一张t_user表，建表语句如下：

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(30) DEFAULT NULL COMMENT '姓名',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `sex` int(1) DEFAULT NULL COMMENT '性别（0：男；1：女）',
  `birthday` date DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

注意：主库建完表后，刷新从库，会自动同步一张一模一样的表，上面我们已经设置好了主从复制，所以接下来我们都不需要管从库了，都会自动复制主库数据了

2、新建项目

新建项目，名为：springboot-sharding-jdbc，然后pom导入下面依赖：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.wujiangbo</groupId>
    <artifactId>springboot-sharding-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.8</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <dependencies>
        <!-- web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

        <!-- sharding-jdbc -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3、启动类

package cn.itsource;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @description: 启动类
 * @auth: wujiangbo
 * @date: 2022-07-20 10:25
 */
@SpringBootApplication
public class APP {

    public static void main(String[] args) {
        SpringApplication.run(APP.class, args);
    }
}

4、yml配置

spring:
  main:
    #设置为true时，后定义的bean会覆盖之前定义的相同名称的bean
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      # master-ds1数据库连接信息
      ds1:
        driver-class-name: com.mysql.jdbc.Driver
        maxPoolSize: 100
        minPoolSize: 5
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://119.45.157.94:3306/testdb?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      # slave-ds2数据库连接信息
      ds2:
        driver-class-name: com.mysql.jdbc.Driver
        maxPoolSize: 100
        minPoolSize: 5
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://124.222.59.129:3306/testdb?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      # 配置数据源
      names: ds1,ds2
    masterslave:
      # 配置slave节点的负载均衡均衡策略,采用轮询机制
      load-balance-algorithm-type: round_robin
      # 配置主库master,负责数据的写入
      master-data-source-name: ds1
      # 配置主从名称
      name: ms
      # 配置从库slave节点,负责数据的读取
      slave-data-source-names: ds2
      #slave-data-source-names: ds2,ds3
    # 显示sql
    props:
      sql:
        show: true
    # 配置默认数据源ds1 默认数据源,主要用于写
    sharding:
      default-data-source-name: ds1

# 整合mybatis的配置
mybatis:
  type-aliases-package: cn.wujiangbo.entity

5、实体类

package cn.itsource.entity;

import lombok.Data;
import java.util.Date;

/**
 * @description: t_user表对应实体类
 * @auth: wujiangbo
 * @date: 2022-07-20 10:28
 */
@Data
public class User {

    private Long id;//主键ID
    private String username;//账号
    private String password;//密码
    private Integer sex;//性别（0：男；1：女；）
    private Date birthday;//生日
}

6、mapper

package cn.itsource.mapper;

import cn.itsource.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;

/**
 * @description: Mapper
 * @auth: wujiangbo
 * @date: 2022-07-20 10:33
 */
@Mapper
public interface UserMapper {

    @Insert("insert into t_user(username, password, sex, birthday) values(#{username}, #{password}, #{sex}, #{birthday})")
    void addUser(User user);

    @Select("select * from t_user")
    List<User> findUsers();
}

7、controller

package cn.itsource.controller;

import cn.itsource.entity.User;
import cn.itsource.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * @description: 用户API接口
 * @auth: wujiangbo
 * @date: 2022-07-20 10:32
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserMapper userMapper;

    //添加用户信息入库
    @GetMapping("/save")
    public String addUser() {
        User user = new User();
        user.setUsername("zhangsan" + new Random().nextInt());
        user.setPassword("123456");
        user.setSex(1);
        user.setBirthday(new Date());
        userMapper.addUser(user);
        return "success";
    }

    /**
     * 查询所有用户信息
     * @return
     */
    @GetMapping("/findUsers")
    public List<User> findUsers() {
        return userMapper.findUsers();
    }
}

8、测试

浏览器先访问：http://localhost:8080/user/save，提示：success，查询主库表，数据添加进去了，再看从库表，数据也同步过去了，完美

然后再访问：http://localhost:8080/user/findUsers，页面展示查询结果

观察控制台打印数据：

在这里插入图片描述

从打印信息来看，可以充分的说明，数据确实添加到我们的主库中了，然后查询用户信息时，也确实是从从库中查询到的数据

至此，读写分离，就测试成功了

9、分库分表

9.1、为什么需要分库分表

分库分表，是企业里面毕竟常见的针对高并发、数据量大的场景下的一种技术优化方案，也是一个非常高频的面试题。但是，因为很多人其实并没有非常丰富的分库分表的经验，所以能把这个问题回答的比较好的人其实还挺少的
如果不分表也不分库的话，那么数据了上来的时候，稍微一个不注意，MySQL单机磁盘容量会撑爆，但是如果拆成多个数据库，磁盘使用率大大降低。

所以分库分表是解决表数据量超大导致查询缓慢的有效途径之一

分库分表实际上是指三件事：“只分库不分表”、“只分表不分库”、以及"既分库又分表"

分库：主要解决的是并发量大的问题。通过增加数据库实例的方式来提供更多的可用数据库链接，从而提升系统的并发度
分表：主要解决的是数据量大的问题。通过将数据拆分到多张表中，来减少单表的数据量，从而提升查询速度
既分库又分表：当你的数据库链接也不够了，并且单表数据量也很大导致查询比较慢的时候，就需要做既分库又分表了

9.2、分库分表原理

1、水平拆分

同一个表的数据拆到不同的库不同的表中。可以根据时间、地区或某个业务键维度，也可以通过hash进行拆分，最后通过路由访问到具体的数据。
拆分后的每个表结构保持一致

2、垂直拆分

就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。
每个库表的结构都不一样，每个库表都包含部分字段。
一般来说，可以根据业务维度进行拆分，如订单表可以拆分为订单、订单支持、订单地址、订单商品、订单扩展等表；
也可以根据数据冷热程度拆分，20%的热点字段拆到一个表，80%的冷字段拆到另外一个表

9.3、分表

我们这里介绍的是水平分表

在这里插入图片描述

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
结果：
- 每个表的结构都一样；
- 每个表数据都不一样，没有交集；
- 所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

1、建库

我们新建数据库叫做order，然后在里面新建两张表，叫做order_1和order_2，表结构都是一样的，如下：

CREATE TABLE `order_1` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `count` int(3) DEFAULT NULL COMMENT '订单数量',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE `order_2` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `count` int(3) DEFAULT NULL COMMENT '订单数量',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

这两张表是订单表水平拆分后的表，通过Sharding-Jdbc向订单表插入数据，按照一定的分片规则，主键为偶数的落入order_1表，为奇数的落入order_2表，再通过Sharding-Jdbc 进行查询

2、yml配置

spring:
  main:
    #设置为true时，后定义的bean会覆盖之前定义的相同名称的bean
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      # 配置数据源
      names: ds1
      # master-ds1数据库连接信息
      ds1:
        driver-class-name: com.mysql.jdbc.Driver
        maxPoolSize: 100
        minPoolSize: 5
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/order?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    # 显示sql
    props:
      sql:
        show: true
    sharding:
      tables:
        #指定表
        order:
          #数据节点
          actual-data-nodes: ds1.order_$->{1..2}
          #主键生成器
          key-generator:
            column: order_id #指定主键字段是哪一个
            type: SNOWFLAKE #雪花算法，指定主键ID值的生成策略（即使数据库主键字段指定了自增，也会使用雪花算法生成的值）
          # 分表策略
          table-strategy:
            inline:
              #以order_id为分片键
              sharding-column: order_id
              #直接通过 order_id 的奇偶性，来判断到底是用哪个表
              algorithm-expression: order_$->{order_id % 2 + 1}

# 整合mybatis的配置
mybatis:
  type-aliases-package: cn.wujiangbo.entity

3、实体类

package cn.itsource.entity;

import lombok.Data;

/**
 * @description: order_info表对应的实体类
 * @auth: wujiangbo
 * @date: 2022-07-20 12:31
 */
@Data
public class OrderInfo {

    private Long orderId;
    private Long userId;
    private String productName;
    private Integer count;
}

4、mapper

//新增订单
@Insert("INSERT INTO order(user_id, product_name, COUNT) VALUES(#{user_id}, #{product_name}, #{count})")
int insertOrder(@Param("user_id") int user_id, @Param("product_name") String product_name, @Param("count") int count);

5、controller

//添加订单信息入库
@GetMapping("/saveOrder")
public String saveFk() {
    for (int i = 0; i < 10; i++) {
        orderInfoMapper.insertOrder(i, "空调" + i, 1);
    }
    return "success";
}

6、测试

浏览器访问：http://localhost:8080/order/saveOrder，页面显示：success，然后打开order数据库中的两张表：

在这里插入图片描述

从结果看，是没问题的，测试成功，数据确实按照我们指定的规则存入不同的表中去了

7、查询

mapper新增接口：

//根据ID集合查询订单数据
@Select({"<script>"+
    "select * from order p where p.order_id in " +
    "<foreach collection='orderIds' item='id' open='(' separator = ',' close=')'>#{id}</foreach>"
    +"</script>"})
List<Map> findOrderByIds(@Param("orderIds") List<Long> orderIds);

controller新增接口：

//查询订单信息
@GetMapping("queryOrders")
public List<Map> queryOrders() {
    List<Long> ids = new ArrayList<>();
    ids.add(756509151646973952L);
    ids.add(756509151416287233L);

    List<Map> orderList = orderInfoMapper.findOrderByIds(ids);
    return orderList;
}

注意：

这里的两个ID，分别是order_1和order_2 这两张表的主键ID值，就是要测试，看能不能从不同的表中查到需要的数据

测试：

浏览器访问：http://localhost:8080/order/queryOrders，结果如下：

在这里插入图片描述

成功，确实从不同表中查询到了数据

再看IDEA控制台：

在这里插入图片描述

9.4、分库

我们这里介绍的是水平分库

在这里插入图片描述

概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
结果：
- 每个库的结构都一样；
- 每个库的数据都不一样，没有交集；
- 所有库的并集是全量数据；
场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
分析：库多了，io和cpu的压力自然可以成倍缓解。

1、建库

把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上

注意：

我们现在做分库的测试，是可以单独存在的，也就是说我这个项目中可以不做主从复制和读写分离也是可以做分库的，当然也可以一起做，都是可以的，只是我这里测试，防止大家混淆，就单独只做分库的测试

所以我这里单独再新建两个数据库 order1 和 order2，而这两个数据库我是没有设置主从复制的，然后再这两个库中分别都新建order_info表，表结果一样：

CREATE TABLE `order_info` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `product_name` varchar(128) DEFAULT NULL COMMENT '商品名称',
  `count` int(3) DEFAULT NULL COMMENT '订单数量',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

2、yml配置

spring:
  main:
    #设置为true时，后定义的bean会覆盖之前定义的相同名称的bean
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      # master-ds1数据库连接信息
      ds1:
        driver-class-name: com.mysql.jdbc.Driver
        maxPoolSize: 100
        minPoolSize: 5
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/order1?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      # slave-ds2数据库连接信息
      ds2:
        driver-class-name: com.mysql.jdbc.Driver
        maxPoolSize: 100
        minPoolSize: 5
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://127.0.0.1:3306/order2?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      # 配置数据源
      names: ds1,ds2
    # 显示sql
    props:
      sql:
        show: true
    sharding:
      tables:
        #指定表
        order_info:
          #数据数据节点
          actual-data-nodes: ds$->{1..2}.order_info
          #主键生成器
          key-generator:
            column: order_id #指定主键字段是哪一个
            type: SNOWFLAKE #雪花算法，指定主键ID值的生成策略（即使数据库主键字段指定了自增，也会使用雪花算法生成的值）
          # 分库策略
          database-strategy:
            inline:
              sharding-column: user_id #以user_id为分片键
              #直接通过 user_id 的奇偶性，来判断到底是用哪个数据源，用哪个数据库和表数据
              algorithm-expression: ds$->{user_id % 2 + 1} #分片策略，user_id为偶数操作ds1数据源，否则操作ds2

# 整合mybatis的配置
mybatis:
  type-aliases-package: cn.wujiangbo.entity

解读：user_id % 2，对2取模，结果只有可能是0或者1，然后在家1，结果就只可能是1或者2了，所以要么选择ds1，要么选择ds2

3、实体类

package cn.itsource.entity;

import lombok.Data;

/**
 * @description: order_info表对应的实体类
 * @auth: wujiangbo
 * @date: 2022-07-20 12:31
 */
@Data
public class OrderInfo {

    private Long orderId;
    private Long userId;
    private String productName;
    private Integer count;
}

4、mapper

package cn.itsource.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface OrderInfoMapper {

    @Insert("INSERT INTO order_info(user_id, product_name, COUNT) VALUES(#{user_id},#{product_name},#{count})")
    int insertOrderFk(@Param("user_id") int user_id, @Param("product_name") String product_name, @Param("count") int count);
}

5、controller

package cn.itsource.controller;

import cn.itsource.mapper.OrderInfoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @description: 订单API接口
 * @auth: wujiangbo
 * @date: 2022-07-20 12:33
 */
@RestController
@RequestMapping("/order")
public class OrderController {

    @Autowired
    private OrderInfoMapper orderInfoMapper;

    /**
     * 添加订单信息入库
     * @return
     */
    @GetMapping("/saveOrder")
    public String saveFk() {
        for (int i = 0; i < 10; i++) {
            orderInfoMapper.insertOrderFk(i, "空调" + i, 1);
        }
        return "success";
    }
}

6、测试

浏览器访问：http://localhost:8080/order/saveOrder，页面显示：success

然后观察order1和order2数据库中的表：

在这里插入图片描述

从结果来看，已经完成了数据的水平切分，成功保存到不同库中了

7、查询

mapper新增接口：

//根据ID集合查询订单数据
@Select({"<script>"+
    "select * from order_info p where p.order_id in " +
    "<foreach collection='orderIds' item='id' open='(' separator = ',' close=')'>#{id}</foreach>"
    +"</script>"})
List<Map> findOrderByIds(@Param("orderIds") List<Long> orderIds);

controller新增接口：

//查询订单信息
@GetMapping("queryOrders")
public List<Map> queryOrders() {
    List<Long> ids = new ArrayList<>();
    ids.add(756494106573668353L);
    ids.add(756494106774994944L);

    List<Map> orderList = orderInfoMapper.findOrderByIds(ids);
    return orderList;
}

注意：

这里的两个ID，分别是order1和order2数据库中的主键ID值，就是要测试，看能不能从不同的库中查到需要的数据

测试：

浏览器访问：http://localhost:8080/order/queryOrders，结果如下：

在这里插入图片描述

成功，确实从不同数据库中查询到了数据

再看IDEA控制台：

在这里插入图片描述

10、什么时候考虑切分

1、能不切分尽量不要切分

并不是所有表都需要进行切分，主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要工作之一。

不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。

2、数据量过大，正常运维影响业务访问

这里说的运维，指：

1）对数据库备份，如果单表太大，备份时需要大量的磁盘IO和网络IO。例如1T的数据，网络传输占50MB时候，需要20000秒才能传输完毕，整个过程的风险都是比较高的

2）对一个很大的表进行DDL修改时，MySQL会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。如果使用pt-online-schema-change，使用过程中会创建触发器和影子表，也需要很长的时间。在此操作过程中，都算为风险时间。将数据表拆分，总量减少，有助于降低这个风险。

3）大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压力

3、随着业务发展，需要对某些字段垂直拆分

举个例子，假如项目一开始设计的用户表如下：

id                   bigint             #用户的ID
name                 varchar            #用户的名字
last_login_time      datetime           #最近登录时间
personal_info        text               #私人信息
.....                                   #其他信息字段

在项目初始阶段，这种设计是满足简单的业务需求的，也方便快速迭代开发。而当业务快速发展时，用户量从10w激增到10亿，用户非常的活跃，每次登录会更新 last_login_time 字段，使得 user 表被不断update，压力很大。而其他字段：id, name, personal_info 是不变的或很少更新的，此时在业务角度，就要将 last_login_time 拆分出去，新建一个 user_time 表。