服务端持久化层MySQL零基础入门实战3-阿里云开发者社区

9.MySQL主从复制

9.1.单个MySQL问题分析

在企业网站中，后端MySQL数据库只有一台时，会出现以下问题：单点故障，服务不可用，无法处理大量的并发数据请求，数据丢失将造成大灾难。

改造方法：

增加MySQL数据库服务器，对数据进行备份，形成主备。
确保准备MySQL数据库服务器是一样的。
主服务器宕机了备份服务器继续工作，数据有保障。

9.2.主从复制原理

MySQL从服务器开启I/O线程，向主服务器请求数据同步（获取二进制文件）
MySQL主服务器开启I/O线程回应从服务器
从服务器获得主的二进制日志写入中继日志
从服务器开启SQL线程将日志内容执行，实现数据同步

9.3.主从复制的模式

1、异步模式

异步模式，主节点不会主动push bin log到从节点，这样有可能导致failover情况下，也许从节点没有及时的将最新的bin log同步到本地。

2、半同步模式

这种模式下主节点只需要接受到其中一台从节点的返回信息，就会commit；否则需要等待知道超过时间然后切换成异步模式在提交；这样做的目的可以使只从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交以后，binlog至少传输到一个节点上，不能保证从节点将此事务更新到db中。性能上会有一定的降低，响应时间会边长。

3、全同步模式

全同步模式是指主节点和从节点全部执行了commit并确认才会向客户端返回成功。

9.4.MySQL主从复制服务搭建

1、环境准备

192.168.159.100(主节点)
192.168.159.101(从节点)
192.168.159.102(从节点)
关闭所有机器的防火墙以及selinux

每台机器上安装配置NTP时间同步服务器：yum -y install ntp

每台服务器与阿里云服务器时间同步：ntpdate ntp1.aliyun.com

2、主库100节点创建开启bin-log日志，并修改server-id重启数据库

vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
log-slave-updates=true #手动添加，开启从日志
server-id=1

重启mysql服务：
/usr/local/mysql/support-files/mysql.server restart

登入mysql服务器给从服务器授权：
mysql -uroot -p123456
grant replication slave on *.* to 'myslave'@'%' identified by '123456';
刷新：
flush privileges;

3、配置从服务器

（1）101节点配置

修改配置文件：
vi /etc/my.cnf
[mysqld]
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
server-id=2 #注意主从不能一致，每个mysql服务独有一个server-id，不能重复

（2）102节点配置

修改配置文件：
vi /etc/my.cnf
[mysqld]
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
server-id=3 #注意主从不能一致，每个mysql服务独有一个server-id，不能重复

重启从节点的mysql服务
/usr/local/mysql/support-files/mysql.server restart

查看主节点的bin-log日志位置： mysql -uroot -p123456 -e ‘show master status’（主节点机器执行）

登入从的mysql服务与主节点进行绑定：

change master to master_host='192.168.159.100',master_user='myslave',master_password='123456',master_log_file= 'mysql-bin.000001',master_log_pos=154;

在从库开启主从复制：

start slave;

查看从库的状态：

show slave status\G;

注意：在生产环境钟数据库是有初始数据的，在做主从复制之前，做一次全备

全备之前要锁表:
锁表命令  flush table with read lock;

然后全备主库
mysqldump -uroot -p123456 -A |gzip -9 >all.sql.gz
将初始数据导入从库

解锁
unlock tables;

9.5.GTID方式搭建主从复制

1、GTID简介

（1）GTID作用方式

最开始的时候，MySQL只⽀持⼀种binlog dump⽅式，也就是指定binlog filename + position，向master发送COM_BINLOG_DUMP命令。

可以指定flag为BINLOG_DUMP_NON_BLOCK，这样master在没有可发送的binlog event之后，就会返回⼀个EOFpackage。不过通常对于slave来说，⼀直把连接挂着可能更好，这样能更及时收到新产⽣的binlog event。

在MySQL 5.6之后，⽀持了另⼀种dump⽅式，也就是GTIDdump，通过发送COM_BINLOG_DUMP_GTID命令实现，需要带上的是相应的GTID信息。

（2）GTID工作原理

master更新数据时，会在事务前产⽣GTID，⼀同记录到binlog⽇志中

slave端的i/o 线程将变更的binlog，写⼊到本地的relay log中sql线程从relay log中获取GTID，然后对⽐slave端的binlog是否有记录 2 3 4 5

如果有记录，说明该GTID的事务已经执行，slave会忽略

如果有记录，slave就会从relay log中执⾏该GTID的事务，并记录到binlog

在解析过程中会判断是否有主键，如果没有就⽤⼆级索引，如果没有就⽤全部扫描

（3）GTID优势

⼀个事务对应⼀个唯⼀ID，⼀个GTID在⼀个服务器上只会执行一次;

GTID是⽤来代替传统复制的方法，GTID复制与普通复制模式的最⼤不同就是不需要指定⼆进制⽂件名和位置;

减少手工干预和降低服务故障时间，当主机挂了之后通过软件从众多的备机中提升⼀台备机为主机;

（4）GTID的劣势

不⽀持⾮事务引擎;

不⽀持create table … select 语句复制(主库直接报错);(原理:会⽣成两个sql, ⼀个是DDL创建表SQL, ⼀个是insert into 插⼊数据的 sql; 由于DDL会导致⾃动提交, 所以这个sql⾄少需要两个GTID, 但是GTID模式下, 只能给这个sql⽣成⼀个GTID)

不允许⼀个SQL同时更新⼀个事务引擎表和非事务引擎表

在⼀个复制组中，必须要求统⼀开启GTID或者是关闭GTID

2、基于GTID方式搭建主从模式

（1）首先开启GTID方式，vi /etc/my.cnf

gtid_mode=on #(必选)
enforce-gtid-consistency=1 #（必选）
log_bin=mysql-bin #（可选） #⾼可⽤切换，最好开启该功能
log-slave-updates=1 #（可选） #⾼可⽤切换，最好打开该功能

（2）重启mysql服务

systemctl restart mysql

（3）主节点中授权用户，myslave

grant replication slave on *.* to 'myslave'@'%' identified by '123456';

（4）从库执行

change master to master_host='192.168.159.100',master_user='myslave',master_password='123456',master_auto_position=1;

10.MySQL读写分离

10.1.读写分离场景背景

一个项目中数据库最基础同时也是最主流的是单机数据库，读写都在一个库中。当用户逐渐增多，单机数据库无法满足性能要求时，就会进行读写分离改造（适用于读多写少），写操作一个库，读操作多个库，通常会做一个数据库集群，开启主从备份，一主多从，以提高读取性能。当用户更多读写分离也无法满足时，就需要分布式数据库了

10.2.SpringBoot+MySQL实现读写分离

1、环境准备

SpringBoot2.x+MySQL5.7+Druid数据源

2、pom.xml文件导入maven依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--SpringBoot集成Aop起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!--SpringBoot集成Jdbc起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!--SpringBoot集成WEB起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mybatis集成SpringBoot起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!--MySQL驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--alibaba数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--lombok插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>

3、配置application.yml配置文件

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://192.168.159.100:3306/school?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave1:
      jdbc-url: jdbc:mysql://192.168.159.101:3306/school?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&useSSL=false&serverTimezone=Asia/Shanghai
      username: root   # 只读账户
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
#    slave2:
#      jdbc-url: jdbc:mysql://192.168.159.102:3306/school?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&useSSL=false&serverTimezone=Asia/Shanghai
#      username: root   # 只读账户
#      password: 123456
#      driver-class-name: com.mysql.cj.jdbc.Driver

4、编写DBTypeEnum枚举类

/**
 * 数据源枚举类
 */
public enum DBTypeEnum {
    MASTER,SLAVE1;
}

5、编写DBContextHolder配置类

/**
 * Description 这里做读写模式切换
 * 原理是用ThreadLocal保存当前线程处于那种模式进行切换
 * 操作结束后要清除该数据，避免内存泄露
 */
@Slf4j
public class DBContextHolder {
    private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
    //private static final AtomicInteger counter = new AtomicInteger(-1);
    public static void setDBType(DBTypeEnum dbTypeEnum){
        contextHolder.set(dbTypeEnum);
    }
    public static DBTypeEnum getDBType(){
       return contextHolder.get();
    }
    public static void master(){
        try {
            setDBType(DBTypeEnum.MASTER);
            log.info("切换到主数据源-master节点");
        }finally {
            //最后一定要释放ThreadLocal，防止内存泄漏
            clearDBType();
        }
    }
    public static void slave(){
        try {
            setDBType(DBTypeEnum.SLAVE1);
            log.info("切换到从数据源-slave1节点");
        }finally {
            //最后一定要释放ThreadLocal，防止内存泄漏
            clearDBType();
        }
        //负载均衡时，放开下面代码
        /*int index = counter.getAndIncrement()%2;
        if (counter.get()>9999){
            counter.set(-1);
        }
        if(index == 0){
            setDBType(DBTypeEnum.SLAVE1);
            log.info("切换到从数据源-slave1节点");
        }else{
            setDBType(DBTypeEnum.SLAVE2);
            log.info("切换到从数据源-slave2节点");
        }*/
    }
    public static void clearDBType() {
        contextHolder.remove();
    }
}

6、编写MyRoutingDataSource数据源选择配置类

/**
 * 获取路由key，确定当前数据源
 * spring在开始进行数据库操作时会通过这个方法决定使用那个数据库，因此在determineCurrentLookupKey
 * 方法里调用获取数据源的操作getDBType
 */
public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.getDBType();
    }
}

7、编写DataSourceConfig数据源配置类

/**
 * 数据源配置类
 */
@Slf4j
@Configuration
public class DataSourceConfig {
    /**
     * 设置主数据源
     * @return
     */
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource(){
        log.info("初始化master主节点，载入配置");
        return DataSourceBuilder.create().build();
    }
    /**
     * 设置从数据源
     * @return
     */
    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource(){
        log.info("初始化slave从节点，载入配置");
        return DataSourceBuilder.create().build();
    }
    /**
     * 路由数据源，通过MyRoutingDataSource中的determineCurrentLookupKey确定使用那个数据源
     * @param masterDataSource
     * @param slave1DataSource
     * @return
     */
    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource){
        Map<Object,Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER,masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1,slave1DataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        log.info("初始化路由配置数据源");
        return myRoutingDataSource;
    }
}

8、编写MyBatisConfig配置类

/**
 * 多数据源要自己配置sqlSessionFactory
 * mybatis配置类，现在spring容器内有三个数据源，所以我们需要为事物管理器和MyBatis手动执行一个明确的数据源
 */
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {
    @Resource(name = "myRoutingDataSource")
    private DataSource myRoutingDataSource;
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        //配置数据源
        sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
        //设置mapper位置
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
    /**
     * 手动设置事务，需要知道当前数据源才可以进行设置事务
     * @return
     */
    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(myRoutingDataSource);
    }
}

9、配置方法切面类DataSourceAop

@Aspect
@Component
public class DataSourceAop {
    @Pointcut("execution(* com.lixiang.service.impl.*.select*(..))"+
    "|| execution(* com.lixiang.service.impl.*.find*(..)))")
    public void readPointcut(){}
    @Pointcut("execution(* com.lixiang.service.impl.*.save*(..))"+
    "|| execution(* com.lixiang.service.impl.*.insert*(..))"+
    "|| execution(* com.lixiang.service.impl.*.add*(..))"+
    "|| execution(* com.lixiang.service.impl.*.update*(..))"+
    "|| execution(* com.lixiang.service.impl.*.edit*(..))"+
    "|| execution(* com.lixiang.service.impl.*.delete*(..))"+
    "|| execution(* com.lixiang.service.impl.*.remove*(..))")
    public void writePointcut(){}
    @Before("readPointcut()")
    public void read(){
        DBContextHolder.slave();
    }
    @Before("writePointcut()")
    public void write(){
        DBContextHolder.master();
    }
}

10、编写实体，Mapper

//student实体
public class StudentPO{
    private Integer id;
    private String name;
    private Integer age;
}
//StudentMapper.java
public interface StudentMapper{
    List<Map<String,Object>> findAllStudent();
    void addStudent(StudentPO studentPO);
}
//StudentMapper.xml
    <select id="findAllStudent" resultType="java.util.Map">
        select * from student;
    </select>
    <insert id="addStudent" parameterType="com.lixiang.domain.StudentPO">
        insert into student (`name`,age) values (#{name},#{age})
    </insert>

11、service业务层

//IStudentService
public interface IStudentService {
    List<Map<String,Object>> findAllStudent();
    void addStudent();
}
//StudentServiceImpl
@Service
public class StudentServiceImpl implements IStudentService {
    @Resource
    private StudentMapper studentMapper;
    /**
     * 查询全部学生信息
     * @return
     */
    @Override
    public List<Map<String, Object>> findAllStudent() {
        return studentMapper.findAllStudent();
    }
    /**
     * 插入新的学生信息
     */
    @Override
    public void addStudent() {
        StudentPO studentPO = new StudentPO();
        studentPO.setName("测试");
        studentPO.setAge(18);
        studentMapper.addStudent(studentPO);
    }
}

12、controller层

@Slf4j
@RequestMapping("api/v1/student")
@RestController
public class StudentController {
    @Resource
    private IStudentService iStudentService;
    /**
     * 查询所有学生信息接口
     * @return
     */
    @GetMapping("/findAllStudent")
    public List<Map<String,Object>> findAllStudent(){
        log.info("调用查询所有学生信息接口--api/v1/student/findAllStudent");
        return iStudentService.findAllStudent();
    }
    /**
     * 保存学生信息
     * @return
     */
    @GetMapping("/saveStudent")
    public Map<String,Object> saveStudent(){
        log.info("调用保存学生信息接口--api/v1/student/saveStudent");
        iStudentService.addStudent();
        Map<String,Object> map = new HashMap<>();
        map.put("code","0000");
        map.put("desc","操作成功");
        return map;
    }
}

13、调用结果

读写分离只是数据库扩展的一个临时解决办法，并不能一劳永逸，随着负载进一步增大，只有一个库用于写入肯定是不够的，而且单表的数据库是有上限的，mysql 最多千万级别的数据能保持较好的查询性能。最终还是会变成–分库分表架构的。

11.MySQL性能优化分析

11.1.基于Durid分析MySQL执行性能

配置Durid打开SQL执行监控

@Configuration
public class DruidConfig {
    /**
     * 注入数据源的username，为登录用户
     */
    @Value("${spring.datasource.druid.username}")
    private String username;
    /**
     * 注入数据源的密码，为登录密码
     */
    @Value("${spring.datasource.druid.password}")
    private String password;
    /**
     * 配置数据源，prefix截止到druid之前，spring会自动获取到druid及它下面的内容
     * @return
     */
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        Map<String, String> initParameters = new HashMap<>();
        initParameters.put("loginUsername", username);// ⽤户名
        initParameters.put("loginPassword", password);// 密码
        initParameters.put("resetEnable", "false");// 禁⽤HTML⻚⾯上的“Reset All”功能
        servletRegistrationBean.setInitParameters(initParameters);
        return servletRegistrationBean;
    }
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

在项目运行成功之后访问地址：127.0.0.1:8080/druid/login,输入账号名称和密码（数据库连接的账号名和密码）

数据源：项目中管理的所有数据源配置的详细信息

SQL监控：锁执行sql语句数量、时长、执行时间分布

SQL防火墙：druid提供了黑白名单的访问，可以清楚的看到sql防护情况

Web应用：目前运行的web程序的详细信息

URI监控：监控到所有的请求路径的请求次数、请求时间等其他参数

Session监控：当前的session状况，创建时间、最后活跃时间、请求次数、请求时间等详细参数

11.2.MySQL性能优化分析

SQL优化可以分为两个部分，一个是设计阶段，另一个是查询阶段

设计阶段运用到的优化

使用适当的数据库列类型和大小

尽量从设计上采用单表查询解决业务问题

在适当字段加入索引，能用唯一索引引用唯一索引

查询阶段设计的优化

尽可能不用select * ：让优化器无法完成索引覆盖扫描这类的优化，而且还会增加额外的I/O、内存和CPU的消耗

慎用join操作：单张表查询可以减少锁的竞争，更容易应对业务的发展，方便对数据库进行拆分

慎用子查询和临时表：未带索引的字段上的group by 操作，union查询，部分order by操作，例如distinct函数和order by一起使用且distinct和prder用一个字段

尽量不要使用limit，部分场景可以改用between and

1、索引优化查询

（1）无索引的情况下千万级别数据查询时间

（2）name字段加上索引查询

2、千万级别数据量分页查询优化

（1）分页查询耗时一览，发现越到后面的查询的时间越长，真正到达亿级查询耗时可想而知

select * from school.student limit 10,20    //耗时4ms
select * from school.student limit 100,20   //耗时4ms
select * from school.student limit 1000,20  //耗时4ms
select * from school.student limit 10000,20  //耗时9ms
select * from school.student limit 100000,20  //耗时73ms
select * from school.student limit 1000000,20 //耗时542ms
select * from school.student limit 10000000,20 //耗时3.377s

（2）对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询

利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：

select id from school.student limit 10000000,20 //耗时1.857s

相对于查询了所有列的大概一半的查询的速度。

那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

select * from school.student s where id >= (select id from school.student limit 10000000,1 ) limit 20 //耗时1.808s
select * from school.student a join (select id from school.student limit 10000000,20 ) b on a.id = b.id //耗时1.818s

12.MySQL分库分表

12.1.分库分表的原因

数据库是最容易产生性能瓶颈的服务组件。数据库因为表多、数据多造成的性能问题。

单一服务中心的数据访问压力也必然会达到单机数据库的承载上限，所以在进行服务化改造的同一时间段内，需要对数据库能力做扩展的工作。

单台数据库mysql，当访问连接数过多时，就会出现“too many connections”的错误，一般来说时访问量太大或者数据库设置的最大连接数太小的原因。

MySQL默认的最大连接数为100，这个连接数可以修改，而mysql服务允许的最大连接数为16384。

什么是分区？

分表是将一张表分成N多张小表，分区时把一张表的数据分成N多个区块，这些区块可以在同一磁盘上，也可以在不同的磁盘上。

物理上多表存储，但是逻辑上单表操作。

12.2.分库分表的方式

一般会有两种分库分表方向，分别是垂直方向和水平方向，第一种方案时直接对现有的数据库进行垂直拆分，可以缓解目前写峰值QPS过大、DB主从延迟的问题。第二种方案时对现有数据库大表进行分库分表。

根据不同规模对垂直方向和水平方向的选择

单个库太大，这时我们要看时因为表多而倒是数据多，还是因为单张表里的数据多，还是因为单张表里面的数据多。如果是因为表多而数据多，使用垂直切分，根据业务切分成不同的库。

单张表的数据量太大，甚至多个库上的多张表。分库分表的顺序应该是先垂直分，后水平分。因为垂直分更简单，更符合我们处理现实世界问题的方式。

垂直拆分

垂直分表：也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用，数据较大，长度较长的拆分到扩展表

垂直分库：垂直分库针对的是一个系统中的不同业务进行拆分，数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数以及单机硬件资源的瓶颈。

水平拆分

水平分表：针对数据量巨⼤的单张表（⽐如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表⾥⾯去。但是这些表还是在同⼀个库中，所以库级别的数据库操作还是有IO瓶颈。

水平分库：将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。⽔平分库分表能够有效的缓解单机和单库的性能瓶颈和压⼒，突破IO、连接数、硬件资源等的瓶颈。

12.3.Sharding-JDBC实现分库分表读写分离

1、Sharding-JDBC简介

Sharding定位为轻量级的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标准的数据库。

2、主从复制、分库分表架构图

3、创建maven项目加入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--MySQL驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--alibaba数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>
        <!--lombok插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
        <!--mybatis集成SpringBoot起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!--SpringBoot集成Jdbc起步依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
    </dependencies>

4、配置yml文件

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: masterdb0,masterdb1,slave1db0,slave1db1,slave2db0,slave2db1 #配置的数据源
      masterdb0: #主product0库
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.159.91:3306/product0?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
      masterdb1: #主product1库
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.159.91:3306/product1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
      slave1db0: #从1product0库
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.159.91:3307/product0?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
      slave1db1: #从1product1库
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.159.91:3307/product1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
      slave2db0: #从2product0库
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.159.91:3308/product0?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
      slave2db1: #从2product1库
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://192.168.159.91:3308/product1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
    sharding:
      default-database-strategy:
        inline:
          #做分片的字段是store_id
          sharding-column: store_id
          #分片的算法是store_id对2取余，product$为对库的选择
          algorithm-expression: product$->{store_id%2}
      tables:
        product:
          #数据节点，product{0，1}.product{0，1，2，3，4，5，6，7}
          actual-data-nodes: product$->{0..1}.product$->{0..7}
          table-strategy:
            inline:
              #表的分区策略也是根据store_id
              sharding-column: store_id
              #根据store_id对7取余+1来区分存在那个product表中，product_$表示对表的选择
              algorithm-expression: product_$->{(store_id%7)+1}
          key-generator:
            column: store_id #自动生成主键
            type: SNOWFLAKE #生成主键的策略是雪花算法
      master-slave-rules:
        product0: #分区product0
          master-data-source-name:  masterdb0 #分区product0的主数据源
          slave-data-source-names: slave1db0,slave2db0 #分区product0的从数据源
        product1: #分区product1
          master-data-source-name:  masterdb1 #分区product1的主数据源
          slave-data-source-names: slave1db1,slave2db1 #分区product1的从数据源
sharding:
  jdbc:
    config:
      masterslave:
        load-balance-algorithm-type: random #轮询策略
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
  mapper-locations: classpath:/mapper/*.xml

5、测试代码

控制层

    @RequestMapping(value = "/addProduct", method = RequestMethod.GET)
    public String add() {
        for (int i=10000;i<11000;i++){
            Product product = new Product();
            product.setProductSeq("productSeq" + i);
            product.setCreateTime(LocalDateTime.now());
            product.setSkuId("skuId" + i);
            product.setSpuId("spuId" + i);
            product.setSaleId("saleId" + i);
            product.setStoreId((long) i);
            product.setUpdateTime(LocalDateTime.now());
            productService.addProduct(product);
        }
        return "success";
    }

业务层

@Service
public class ProductServiceImpl implements ProductService {
    @Resource
    private ProductMapper productMapper;
    @Override
    public void addProduct(Product product){
        productMapper.insertSelective(product);
    }
    @Override
    public List<Map<String,Object>> findAllProduct() {
        return productMapper.findAllProduct();
    }
    @Override
    public Product findProductById(Long id) {
        return productMapper.selectByPrimaryKey(id);
    }
}

数据库存储

mmm_mond：监控进程，负责所有的监控工作，决定和处理所有节点角色活动。因此脚本需要在监管机上运行。

mmm_agentd：运行在每个mysql服务器上的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上与运行。

mmm_control：一个简单的脚本，提供管理mmm_mond进程的命令。

13.2.MMM实现MySQL高可用

1、环境准备

mysql-master1：192.168.159.151

mysql-master2：192.168.159.152

mysql-slave1：192.168.159.153

mysql-slave2：192.168.159.154

mysql-monitor：192.168.159.155

Writer-VIP：192.168.159.201

Reader-VIP：192.168.159.202，192.168.159.203

2、主主复制，master1与master2互为主从

（1）编辑配置文件

[client] 
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
lower_case_table_names = 1 
default-storage-engine = InnoDB 
port = 3306 
datadir = /usr/local/mysql/data 
character-set-server = utf8 
socket = /usr/local/mysql/data/mysql.sock 
log_bin = mysql-bin 
log-error=/usr/local/mysql/logs
server_id = 1 #注意server_id每个节点都要保证不同
innodb_buffer_pool_size = 200M 
slave-parallel-workers = 8 
thread_cache_size = 600 
back_log = 600 
slave_net_timeout = 60 
max_binlog_size = 512M 
key_buffer_size = 8M 
query_cache_size = 64M
join_buffer_size = 2M 
sort_buffer_size = 2M 
query_cache_type = 1 
thread_stack = 192K
gtid_mode = ON
log_slave_updates
enforce_gtid_consistency

（2）互相授权，两个主中都执行

grant replication slave on *.* to 'slave'@'192.168.159.%' identified by '123456'

（3）设置互为主从

151节点执行

change master to master_host='192.168.159.152',master_user='slave',master_password='123456',master_auto_position=1;

152节点执行

change master to master_host='192.168.159.151',master_user='slave',master_password='123456',master_auto_position=1;

3、配置mysql-slave1、mysql-slave2为mysql-master1的主节点

change master to master_host='192.168.159.151',master_user='slave',master_password='123456',master_auto_position=1;

目前数据同步为在master1（151）上或者master2（152）上创建数据在四个库中应该都可以看到

4、在mysql-monitor上安装mysql-mmm-monitor服务

（1）5台机器机器安装epel源

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
wget https://mirrors.aliyun.com/repo/Centos-7.repo

（2）master1、master2、slave1、slave2安装mysql-mmm-agent，monitor安装mysql-mmm-monitor

151,152,153,154节点：
yum -y install mysql-mmm-agent

155节点：
yum -y install mysql-mmm-monitor

（3）四台服务器配置151、152、153、154

cd /etc/mysql-mmm/
cp -p mmm_common.conf mmm_common.conf.bak #备份mysql-mmm的配置文件 
vi mmm_common.conf

配置如下：

active_master_role      writer
<host default>
    cluster_interface       ens33 #网卡名称
    pid_path                /run/mysql-mmm-agent.pid
    bin_path                /usr/libexec/mysql-mmm/
    replication_user        slave #授权主从复制的用户
    replication_password    123456  #主从复制用户的密码
    agent_user              mmm_agent #agent代理的用户名
    agent_password          123456    #代理的密码
</host>
<host db1>
    ip      192.168.159.151 #ip
    mode    master #角色
    peer    db2 #peer表示同级，db1与db2互为主从
</host>
<host db2>
    ip      192.168.159.152
    mode    master 
    peer    db1
</host>
<host db3>
    ip      192.168.159.153
    mode    slave
</host>
<host db4>
    ip      192.168.159.154
    mode    slave
</host>
<role writer>
    hosts   db1, db2 #配置写库，db1、db2  
    ips     192.168.159.201 #写库的vip
    mode    exclusive
</role>
<role reader>
    hosts   db3, db4 #配置读库，db3、db4  
    ips     192.168.159.202, 192.168.159.203 #读库的vip
    mode    balanced
</role>

（3）登入MySQL给mmm_agent授权

#这个用户为上方agent_user的代理用户
grant super,replication client,process on  *.* to 'mmm_agent'@'192.168.159.%'  identified by '123456';
flush privileges;

（4）修改/etc/mysql-mmm/mmm_agent.conf为 db1、db2、db3、db4

（5）在mysql-monitor（155）节点编辑 mmm_mon.conf

vi /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
    ip                  127.0.0.1   #不用修改，默认本机
    pid_path            /run/mysql-mmm-monitor.pid
    bin_path            /usr/libexec/mysql-mmm
    status_path         /var/lib/mysql-mmm/mmm_mond.status
    ping_ips            192.168.159.151,192.168.159.152,192.168.159.153,192.168.159.154 #管理的ip节点
    auto_set_online     10 #间隔10s进行健康检测
    # The kill_host_bin does not exist by default, though the monitor will
    # throw a warning about it missing.  See the section 5.10 "Kill Host
    # Functionality" in the PDF documentation.
    #
    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
    #
</monitor>
<host default>
    monitor_user        mmm_agent #之前授权的代理用户
    monitor_password    123456 #密码
</host>
debug 0

（6）151、152、153、154启动mysql-mmm-agent，155启动mysql-mmm-monitor

#151、152、153、154节点执行
systemctl start mysql-mmm-agent
#155节点执行
systemctl start mysql-mmm-monitor

（7）在mysql-monitor上查看监控状态

mmm_control show   #查看监控状态

检查vip绑定情况

（8）测试高可用

停掉master1测试,查看监管

停掉slave1测试，查看监控

服务端持久化层MySQL零基础入门实战3

9.MySQL主从复制

9.1.单个MySQL问题分析

9.2.主从复制原理

9.3.主从复制的模式

9.4.MySQL主从复制服务搭建

9.5.GTID方式搭建主从复制

（2）GTID工作原理

（3）GTID优势

（4）GTID的劣势

2、基于GTID方式搭建主从模式

（1）首先开启GTID方式，vi /etc/my.cnf

10.MySQL读写分离

10.1.读写分离场景背景

10.2.SpringBoot+MySQL实现读写分离

11.MySQL性能优化分析

11.1.基于Durid分析MySQL执行性能

11.2.MySQL性能优化分析

12.MySQL分库分表

12.1.分库分表的原因

什么是分区？

12.2.分库分表的方式

12.3.Sharding-JDBC实现分库分表读写分离

13.2.MMM实现MySQL高可用

1、环境准备

2、主主复制，master1与master2互为主从

（1）编辑配置文件

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

服务端持久化层MySQL零基础入门实战3

9.MySQL主从复制

9.1.单个MySQL问题分析

9.2.主从复制原理

9.3.主从复制的模式

9.4.MySQL主从复制服务搭建

9.5.GTID方式搭建主从复制

（2）GTID工作原理

（3）GTID优势

（4）GTID的劣势

2、基于GTID方式搭建主从模式

（1）首先开启GTID方式，vi /etc/my.cnf

10.MySQL读写分离

10.1.读写分离场景背景

10.2.SpringBoot+MySQL实现读写分离

11.MySQL性能优化分析

11.1.基于Durid分析MySQL执行性能

11.2.MySQL性能优化分析

12.MySQL分库分表

12.1.分库分表的原因

什么是分区？

12.2.分库分表的方式

12.3.Sharding-JDBC实现分库分表读写分离

13.2.MMM实现MySQL高可用

1、环境准备

2、主主复制，master1与master2互为主从

（1）编辑配置文件

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像