全方位解析 MySQL 及相关面试题二（收藏点赞系列）

coderxdh
282 阅读29分钟

写在前面

  • 文章是在前人的基础上进行总结整理再加上自己的一点理解，仅作为自己学习的记录，不作任何商业用途！
  • 如果在文章中发现错误或者侵权问题，欢迎指出，谢谢！

image.png

前置内容

事务

什么是事务
  • 事务是逻辑上的一组操作要么都执行，要么都不执行
为什么需要事务
  • 设想一个场景：
    • A 向 B 转账 1000 元，那么应该是 A 减少 1000 元，B 增加 1000 元，如果在这减少和增加的操作之间转账系统崩溃了，导致 A 余额减少而 B 余额没有增加，明显这是不合理的
    • 那么事务就是保证这两个操作要么都完成，要么都失败的
事务的四大特性
  • 原子性(Atomicity)
    • 是事务最小的执行单元，不可再分割，原子性保证了事务要么都执行，要么都不执行
  • 一致性(Consistency)
    • 在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
  • 隔离性(Isolation)
    • 在并发访问数据库的时候，一个事务在执行操作不会被其他事务所打扰，相互之间是隔离独立的
  • 永久性(Durability)
    • 当执行完事务后，对数据的影响是永久性的。即使数据库发生故障也不会有影响
并发事务带来的问题
  • 为什么会产生问题
    • 多个事务并发执行，可能会对同一个数据进行操作而产生了下面几个问题
  • 脏读
    • 当事务 A 对数据库中的某一个记录进行了更新，但是还没提交到数据库中去，此时事务 B 对该数据进行了访问，那么得到的就是 事务 A 更新后的数据。若事务 A 进行了回滚，那么事务 B 得到的数据就是一个脏数据
  • 不可重复读
    • 事务 A 多次访问同一个数据，事务 B 在事务 A 访问的时候对该数据进行了更新并提交了，那么就导致事务 A 多次访问的结果不一致
  • 幻读
    • 幻读和不可重复读是类似的，都是在同一个事务中两次访问数据得到的结果不一致，但是不同点在于：幻读的重点在数据(行数)的新增或删除，不可重复读的重点在数据(内部)的修改
事务的隔离级别
  • 读未提交(READ_NUCOMMITTED)
    • 允许读未提交的数据，有可能造成脏读、不可重复读、幻读
  • 读已提交(READ_COMMITTED)
    • 允许读已提交的数据，可以解决脏读问题，仍然存在不可重复读和幻读问题
  • 可重复读(REPETABLE_READ)
    • 一个事务多次读取结果是一样的，即事务执行期间禁止其他事务对该数据进行操作，可解决脏读和不可重复读问题，仍然存在幻读问题
    • MySQL 数据库 InnoDB 存储引擎默认的隔离级别
  • 序列化(SERIALIZABLE)
    • 一个事务多次从一个表中读取到相同的行，即执行期间禁止其他事务对这个表进行新增、更新和删除，可解决脏读、不可重复读、幻读问题
  • 注意：
    • 事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差
    • InnoDB 存储引擎在 REPEATABLE-READ 事务隔离级别下使用的是 Next-Key Lock(临建锁，在锁机制中会讲) 算法，可以避免幻读的产生，因此在完全保证事务的隔离性要求的同时保留了比较好的并发性能
并发问题的解决方案
  • 脏读、不可重复读和幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，有以下两种解决方案
    • 一种是加锁：在读取数据前对其加锁，阻止其他事务对数据进行修改，比如：共享锁和排它锁
    • 一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库
MVCC(TODO：更深入的理解)

锁机制

  • 数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则
锁的分类

image.png

  • 并发控制一般采用三种方法，分别是乐观锁和悲观锁以及上面讲解过的 MVCC
乐观锁
  • 乐观锁会认为自己在使用数据的时候，不会有其他线程来修改，所以不加锁，只是在更新数据之前去判断这个数据有没有被其他线程修改过。如果没有被修改，则该线程将自己修改的数据成功写入；如果被修改了，则根据不同的实现方式进行不同的操作
    • 再次强调一下：乐观锁只是一种思想，CAS 是它的一种实现方式，但是 CAS 同样存在问题，就引出了下面两种实现方式
    • 关于 CAS 的内容可以看我的另一篇文章CAS
  • 实现方式：乐观锁有以下两种
    • 借助数据库表增加一个版本号的字段 version
      • 即为数据增加一个版本标识，一般是为数据库表增加一个数字类型的 version 字段来实现
      • 当读取数据的时候，将 version 字段值读取出来并记录下来，当我们进行提交的时候再次读取 version 字段值，和上一次读取的值进行比较，如果值相等则可以进行更新，否则不予更新
      • 数据每更新一次则 version+1
    • 借助行更新时间时间戳 timestamp
      • 与上面的检测方式类似，即更新操作执行前先获取记录当前的更新时间 A，在提交更新时，再次获取记录更新时间 B，并判断此时 B 是否与上次获取的更新时间 A 相等
  • 优点
    • 乐观并发控制没有实际加锁，不通过数据库的锁机制实现，所以没有额外开销，也不错出现死锁问题，适用于读多写少的并发场景，因为没有额外开销，所以能极大提高数据库的性能
  • 缺点
    • 乐观并发控制不适合于写多读少的并发场景下，因为会出现很多 version 字段的写冲突，导致数据写入要多次等待重试，在这种情况下，其开销实际上是比悲观锁更高的
悲观锁

  • 对同一个数据的并发操作，悲观锁会认为自己在使用数据的时候一定会有其他线程来修改该数据，因此在获取的时候会加一把锁，确保数据不会被其他线程修改

  • 实现方式：在数据库中，悲观锁的流程如下

    • 在对任意记录进行修改前，先尝试为该记录加上排他锁(后文会讲)
    • 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常(具体响应方式由开发者根据实际需要决定)
    • 如果加锁成功，那么就可以对记录做修改，事务完成后就会解锁了
    • 在这期间，如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常

  • 注意

    • 在 InnoDB 存储引擎中使用悲观锁必须关闭 MySQL 数据库的自动提交属性(set autocommit = 0)
    • SQL 语句要怎么写？ 
      //0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;
    • select…for update 会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB 默认行级锁，行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住

  • 优点

    • 悲观锁都是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。因此适合写多读少的情况

  • 缺点

    • 由于需要加锁，而且可能面临锁冲突甚至死锁的问题，增加了系统的额外开销，降低了系统的效率，同时也会降低了系统的并行性

  • 乐观锁和悲观锁的适用场景

    • 乐观锁
      • 适用于读多写少，因为如果出现大量的写操作，写冲突的可能性就会增大，业务层需要不断重试，这会大大降低系统性能
      • 数据一致性要求不高，但要求非常高的响应速度
      • 无法解决脏读，幻读，不可重复读，但是可以解决更新丢失问题
    • 悲观锁
      • 适用于写多读少，因为悲观锁是在读取数据的时候就加锁的，读多的场景会需要频繁的加锁和很多的的等待时间，而在写冲突严重的情况下使用悲观锁可以保证数据的一致性
      • 数据一致性要求高
      • 可以解决脏读，幻读，不可重复读，第一类更新丢失，第二类更新丢失的问题
共享锁
  • 共享锁也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源
  • 性质
    • 多个事务可以封锁同一个共享页
    • 任何事务都不能修改该页，如果事务对共享锁进行修改操作，很可能会造成死锁
    • 通常是该页读取完毕，共享锁就释放掉了
    • 如果事务 T 对数据 A 加上共享锁后，则其他事务只能对 A 再加共享锁，不能加排他锁
  • 说明
    • 在查询语句后面增加 LOCK IN SHARE MODE ，MySQL 会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞
  • 案例
    • 事务 A 在关闭自动提交的前提下进行查询，在查询语句尾部添加了 LOCK IN SHARE MODE
    -- 使用悲观锁需要先关闭自动提交
SET AUTOCOMMIT = 0  
SELECT * FROM customers WHERE customer_id = 163 LOCK IN SHARE MODE
-- 注意没有执行 COMMIT 提交命令
    • 事务 B 现在来对事务 A 的查询结果进行 UPDATE 操作
    UPDATE customers SET city = 'HangZhou1' WHERE customer_id = 163
    • 执行结果如下：超时
    image.png
    • 事务 C 现在对事务 A 的查询结果进行 SELECT 操作
    SELECT * FROM customers WHERE customer_id = 163
    • 执行结果如下：查询成功
    image.png
    • 上述案例说明了：加了共享锁的数据，后面只能再加共享锁，而不能加排它锁。多个事务可以封锁同一个共享页
排它锁
  • 排它锁也叫写锁，表示对数据进行写操作，如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了
  • 性质
    • 仅允许一个事务封锁此页，该事务可以对该页进行读写操作
    • 其他事务可以对该页进行读，不能写
    • 排它锁会阻塞所有的排它锁和共享锁
  • 说明
    • 在查询语句后面增加 FOR UPDATE ，MySQL 会对查询结果中的每行都加排它锁
  • 案例
    • 事务 A 在关闭自动提交的前提下进行查询，在查询语句尾部添加了 FOR UPDATE
    SET AUTOCOMMIT = 0  
SELECT * FROM customers WHERE customer_id = '163' FOR UPDATE
-- 没有执行 COMMIT 提交命令
    • 事务 B 现在来对事务 A 的查询结果进行 UPDATE 操作
    UPDATE customers SET city = 'HangZhou' WHERE customer_id = 163
    • 执行结果如下：超时
    image.png
    • 事务 C 现在对事务 A 的查询结果进行 SELECT 操作，并 添加上 LOCK IN SHARE MODE
    SELECT * FROM customers WHERE customer_id = '163' LOCK IN SHARE MODE
    • 执行结果如下：查询失败，超时
    image.png
表级索、行级锁和页面锁
  • 表级索
    • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）
  • 行级锁
    • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）
  • 页面锁
    • 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
  • 适用场景
    • 表级锁：更适合于以查询为主，只有少量按索引条件更新数据的应用
    • 行级锁：更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用
MyISAM 表锁
  • MyISAM 的表锁有两种模式
    • 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
    • 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作
  • MyISAM 表的读操作与写操作之间，以及写操作之间是串行的
    • 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放为止
  • 默认情况下，写锁比读锁具有更高的优先级
    • 当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求
  • MyISAM 加锁解锁机制
    • 在执行查询语句（SELECT—前，会自动给涉及的所有表加读锁
    • 在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预
InnoDB 行锁
  • InnoDB 实现了以下两种类型的行锁
    • 共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
    • 排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
  • 注意
    • InnoDB 存储引擎的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则都会从行锁升级为表锁
    • 行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁
InnoDB 加锁机制
  • InnoDB 有三种行锁模式的算法
  • 记录锁（Record Locks）
    • 单个记录上的锁。对符合条件的带有索引的行加锁，其他事务不能修改和删除加锁项
    SELECT * FROM table WHERE id = 1 FOR UPDATE;
    • 会在 id=1 的记录上加上记录锁，以阻止其他事务更新，删除 id=1 这一行
    -- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;
    • 在通过主键索引与唯一索引对数据行进行 UPDATE 操作时，也会对该行数据加记录锁
  • 间隙锁（Gap Locks）
    • 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙（GAP)，InnoDB 也会对这个间隙加锁
    • 比如：我们总共有 100 条记录，id 从 1-100
    Select * from  emp where empid >= 100 for update;
    • 是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid=100 的记录加锁，也会对empid>100（这些记录并不存在）的间隙加锁
    • 使用间隙锁的目的是为了解决幻读问题，以满足 REPETABLE_READ 这个隔离级别的要求，比如：
      • 对于上面的例子要是不使用间隙锁，如果其他事务插入了 empid>100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读
    • 注意
      • 如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁
      • 间隙锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件
  • 临键锁（Next-Key Locks）
    • 临键锁是记录锁与间隙锁组成的，它的封锁范围，既包含索引记录，又包含索引区间，可以理解为一种特殊的间隙锁，也可以理解为是一种算法
    • 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据
    • InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁
    • 临键锁的主要目的，也是为了避免幻读，如果把事务的隔离级别降级为READ-COMMITED，临键锁则也会失效
死锁(TODO：更深入的理解)
  • 什么是死锁
    • 死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状
  • 产生死锁的四个条件
    • 互斥条件：同一个资源只能被一个进程使用
    • 请求与保持条件：一个进程因请求资源而阻塞，但是对已经占有的资源不会释放
    • 不可剥夺条件：一个进程已经占用的资源在使用完之前不会被剥夺
    • 循环等待条件：若干个进程形成环形等待资源关系
  • 解除当前死锁状态
    • 总的来说就是找到造成死锁的进程ID 并 kill 进程ID
  • 如何避免死锁
    • 按同一顺序访问对象
    • 避免事务中的用户交互
  • 注意
    • 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁
    • 死锁会影响性能而不是会产生严重错误，因为InnoDB 会自动检测死锁状况并回滚其中一个受影响的事务
相关面试题
  • Q：数据库的乐观锁和悲观锁？
    • 乐观锁是数据库认为事务在其操作过程中不会有其他事务
    • 悲观锁则认为在其操作过程中会有其他事务来干扰，则会在进行操作之前加上锁
  • Q：MySQL 中有哪几种锁，列举一下？
    • 乐观锁和悲观锁
    • 共享锁和排它锁
    • 记录锁、间隙锁和临键锁
  • Q：MySQL 中 InnoDB 引擎的行锁是怎么实现的？
    • InnoDB 存储引擎的行锁有三种实现机制：记录锁、间隙锁、和临键锁
    • 记录锁是对带有索引的查询记录加锁，加锁后不能对其进行更新和删除
    • 当我们的查询条件是一个范围而不是等值的时候，并且对查询记录加排它锁或者是共享锁的时候，存储引擎则会给符合的索引记录加上间隙锁
    • 临键锁是记录锁和间隙锁组成的，其作用和间隙锁一样都是避免 InnoDB 引擎在 REPETABLE_READ 的隔离级别下发生幻读
  • Q：MySQL 死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁
    • 死锁是指多个事务在占有各自资源的情况下，又互相申请被其他事务所占用的资源而导致的一种死循环状态
    -- 执行顺序1
BEGIN;
DELETE FROM user_table WHERE id = 10;    --理论上此时 user_id区间 (-∞, 10] 和 (10, 20)都被锁住了

-- 执行顺序3
INSERT INTO user_table (id) VALUES (24); --事务 A 尝试插入数据，但是该区间已经被事务 B 锁住，因此阻塞等待事务 B 释放锁

    -- 执行顺序2
BEGIN;
DELETE FROM user_table WHERE id = 30;    --理论上此时 user_id 区间 [20, 30 ) 和 [30, +∞) 被事务 B 锁住了

-- 执行顺序4
INSERT INTO user_table(id) VALUES (13);  --事务 B 尝试插入数据，但是该区间已经被事务 A 锁住，因此阻塞等待事务 A 释放锁
    • 事务 A 和事务 B 互相等待对方释放锁，导致死锁
    • MySQL 提供了一套 InnoDB 的监控机制，用于周期性（每隔 15 秒）输出 InnoDB 的运行状态日志中，默认是关闭的
    • InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚

性能优化(TODO：更深入的理解)

SQL 优化
  • 总体来说 SQL 语句的优化有以下几个方面
    1. 避免不走索引的场景
    2. SELECT 语句其他优化
    3. 增删改 DML 语句优化
    4. 查询条件优化
    5. 建表优化
  • 参考

分区分表分库

分区
  • 概念
    • 分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表
  • 何时考虑分区
    • 当一张表的查询速度已经慢到影响使用的时候
    • 数据库中数据是分段的
    • 往往只是操作数据库中一部分数据
  • 分区策略
    • RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区
    • LIST 分区：类似于按 RANGE 分区，每个分区必须明确定义。它们的主要区别在于，LIST 分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而 RANGE 分区是从属于一个连续区间值的集合
    • HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式
    • KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数，必须有一列或多列包含整数值
  • 实现方式（RANGE 分区） 
    CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL,
    PRIMARY KEY(id, order_day)
) ENGINE=Innodb 
PARTITION BY RANGE(YEAR(order_day)) (
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE);
  • 优点
    • 相对于单个文件系统或是硬盘，分区可以存储更多的数据
    • 分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，减少数据库频繁操作 IO
  • 缺点
    • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
分表
  • 概念
    • 就是把一张表按一定的规则分解成 N 个具有独立存储空间的实体表，系统读写时需要根据定义好的规则得到对应的子表名，在对该子表进行操作
  • 何时考虑分表
    • 一张表的查询速度已经慢到影响使用的时候。
    • SQL 经过优化
    • 数据量大
    • 当频繁插入或者联合查询时，速度变慢
  • 分表策略

    • 水平分表：是把一个表复制成同样表结构的另一张表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能，这些相同结构的表可以放在同一个或不同的数据库

    • 垂直分表：通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表，然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中

    • 图片来源于网络，侵权请联系删除 image.png

  • 解决的问题
    • 分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了
    • 查询一次的时间短了
    • 数据分布在不同的文件，磁盘 I/O 性能提高
    • 读写锁影响的数据量变小
    • 插入数据库需要重新建立索引的数据减少
  • 实现方式
    • 需要业务系统配合迁移升级，工作量较大
分库
  • 概念
    • 一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上
  • 何时考虑分库
    • 单台数据库的存储空间不够
    • 随着查询量的增加单台数据库服务器已经没办法支撑
  • 分库策略
    • 水平分库
    • 垂直分库
    • 读写分离
  • 解决的问题
    • 其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题
分表分库带来的问题

  • join 操作

    • 水平分表后，数据分散到多个表中，如果需要与其他表进行join 查询，需要在业务代码或者数据库中间件中进行多次 join 查询，然后将结果合并

  • count 操作     

    • 水平分表后，虽然物理上数据分散到多个表中，但是某些业务逻辑上还是会将这些表当作一个表进行处理，例如，获取记录总数用于分页或展示，水平分表之前用一个 count() 就能完成的操作，在分表之后就没有那么简单了，常见的处理方式有如下两种
      • count() 相加
      • 记录数表，新建一张表专门记录 count 相关的数据

  • order by 操作     

    • 水平分表后，数据分散到多个子表中，排序操作无法在数据库中完成，只能由业务代码或数据库中间件分表查询每个子表中的数据，然后汇总进行排序

  • 事务的支持，分库分表就变成了分布式事务

  • 分库分表，读写分离使用了分布式，分布式为了保证强一致性，必然带来延迟，导致性能降低、系统的复杂度变高

  • 解决方案：通常配合使用第三方数据库中间件（Atlas，Mycat，TDDL，DRDS）来解决相应的问题

主从复制

  • 概念
    • MySQL 主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中
    • 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行
  • 用途
    • 读写分离：在开发工作中，有时候会遇见某个 SQL 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读
    • 数据备份：当系统中某个节点数据库发生故障时，可以方便的恢复
    • 架构扩展：当单体数据库不能适应海量的数据的时候，可以使用主从复制来缓解单体 DB 的压力，降低单体磁盘 IO 操作频率，提高单个机器的性能
主从复制形式
  • 一主一从
  • 一主多从
    • 一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现高可用，而且还能读写分离，进而提升集群的并发能力
  • 多主一从
    • 多主一从可以将多个 MySQL 数据库备份到一台存储性能比较好的服务器上
  • 双主复制
    • 双主复制每个主节点既是主节点又是另外一台服务器的从节点。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中
  • 级联复制
    • 部分从节点的数据同步不连接主节点，而是连接从节点
    • 因为如果主节点有太多的从节点，就会损耗一部分性能用于 replication，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响
主从复制原理
  • MySQL 主从复制涉及到三个线程，一个运行在主节点（Log Dump Thread），其余两个运行在从节点（I/O Thread、SQL Thread），如下图所示

image.png

  • 主节点 Log Dump Thread

    • 当从节点连接主节点时，主节点会创建一个Log Dump Thread，用于发送 bin-log 的内容。在读取 bin-log 中的操作时，此线程会对主节点上的 bin-log 加锁，当读取完成，甚至在发动给从节点之前，锁才会被释放
    • bin-log 是主库中的更新事件类型

  • 从节点 I/O Thread

    • 当从节点上执行 start slave 命令之后，从节点会创建一个 I/O Thread 用来连接主节点，请求主库中更新的 bin-log，I/O Thread 接收到主节点 Log Dump Thread 发来的更新之后，保存在本地 relay-log 中

  • 从节点 SQL Thread

    • SQL Thread 负责读取 relay-log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性

  • 说明

    • 对于每一个主从复制，都需要三个线程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 Log Dump Thread，而每个从节点都有自己的 I/O Thread 和 SQL Thread
    • 要实施复制必须打开 Master 端的 binary log（bin-log）功能，否则无法实现，因为整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作

  • 复制过程

    image.png

    1. 从节点上的 I/O 线程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
    2. 主节点接收到来自从节点的 I/O 请求后，负责复制的 I/O 线程会根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 bin-log file 以及 bin-log position
    3. 从节点的 I/O 进程接收到内容后，将接收到的日志内容更新到本机的 relay-log 中，并将读取到的 binary log 文件名和位置保存到 master-info 文件中，以便确定下次去主库中读取的位置
    4. 从节点的 SQL 线程检测到 relay-log 中新增加了内容后，会将 relay-log 的内容解析成在主节点上实际执行过的操作，并在本数据库中执行，达到主从一致性
主从复制策略
  • 同步策略：Master 会等待所有的 Slave 都回应后才会提交，这个主从的同步的性能会严重的影响
  • 半同步策略：Master 至少会等待一个 Slave 回应后提交
  • 异步策略（默认）：Master 不用等待 Slave 回应就可以提交
  • 延迟策略：Slave 要落后于 Master 指定的时间
    • 对于不同的业务需求，有不同的策略方案，但是一般都会采用最终一致性，不会要求强一致性，毕竟强一致性会严重影响性能
主从复制实现（TODO：还未实现）

数据库连接池

参考与感谢

avatar
文章
阅读
粉丝
相关推荐
【面试题精讲】MySQL-事务隔离-幻读
138阅读
 · 
1点赞
【面试题精讲】MySQL-事务隔离-不可重复读
120阅读
 · 
0点赞
【面试题精讲】MySQL-事务隔离-脏读
113阅读
 · 
0点赞
【面试题精讲】MySQL-事务隔离-ACID
133阅读
 · 
0点赞
【面试题精讲】MySQL连接器
163阅读
 · 
0点赞