MySQL读书笔记

目录

第一章：mysql数据库体系结构


server+插件式存储引擎+磁盘
server有连接器、查询分析器、查询优化器、查询缓存等

mysql特别之处就是插件式的表存储引擎，存储引擎是底层物理结构的实现，存储引擎基于表
innoDB和myisam的区别

  • innodb支持事务，myisam不支持事务
  • innodb支持MVCC，myisam不支持MVCC
  • innodb支持行锁和表锁，myisam支持表锁（性能体现）
  • innodb支持外键，myisam不支持外键
  • innodb采用聚集索引（索引和行记录放在一起），myisam采用非聚集索引（索引保存行地址）
  • innodb支持哈希索引，myisam不支持哈希索引
  • innodb的数据和索引都放在IBD文件，myisam的数据和索引分别放在MYD文件和MYI文件
  • myisam可以压缩表，减少磁盘占用空间，减少io，压缩表不能修改，需解压
  • myisam不缓存数据文件，只缓存索引文件
  • innodb需要的内存比myisam多，有缓冲池

选择：

  • 是否需要事务
  • 能否接收系统崩溃无法恢复数据
  • 大多时候进行读操作选择myisam
  • 硬件资源（磁盘和内存）有限选择myisam，myisam可压缩表（innodb大的原因，个人认为因为维护事务需要redolog，和一些特性比如两次写需要磁盘空间，内存也是这个道理）
  • 并发高选择innodb，innodb支持mvcc和行锁

第二章：InnoDB存储引擎

InnoDB体系架构

多线程+缓冲池+磁盘文件

基于多线程模型

  • Master：将缓冲池的数据刷新回磁盘，保持一致性；刷新insertbuffer
  • IO：负责异步IO的回调处理
  • Purge：清除事务提交后的undolog，回收undo页
  • PageCleaner：刷新脏页

内存管理

  • innodb存储引擎是基于磁盘存储的
  • 页为单位，进行管理，磁盘还是缓冲都是以页为单位，伙伴算法管理页
  • 缓冲池：就是页缓存的性质。包括重做日志缓冲、数据页、索引页、插入缓冲、锁信息、自适应哈希索引、数据字典信息、额外内存池
  • LRU算法及优化：加入midpoint，新页不放在链表头；参数innodb_old_blocks_time，表示midpoint位置的页过多长时间可以放到链表头。（不是全部页都需要LRU，比如自适应哈希所索引、insertbuffer不需要LRU）。这两个优化可以避免全表扫描污染LRU链表

checkpoint技术

工作就是刷新脏页
当事务提交时，数据库先写redolog，再修改页，ACID中的D持久性就是利用redolog实现（redolog文件的刷新不代表页已经刷新回磁盘）
checkpoint之前的页已经刷新回磁盘了，所以checkpoint之前的redolog并不需要了

  • 出现原因：假设redolog文件无限大，缓冲池无限大，那么即使写了redolog文件，数据库崩溃，恢复时所有页都没刷新回磁盘，需要按redolog文件恢复很长时间
  • 解决的问题：redolog文件的循环使用，缓冲池不够进行刷新，缩短数据库恢复时间
  • sharp checkpoint：数据库关闭时把所有脏页都刷新回磁盘，默认工作方式。（运行时使用fuzzy，也避免刷新全部脏页）
  • fuzzy checkpoint：①master刷新；②pagecleaner刷新；③重做日志文件不可用（需要刷新脏页然后checkpoint之前的redolog可以删除）；④脏页太多，要保持缓冲池有足够的干净页。（刷新部分脏页）

InnoDB的关键特性

  • 插入缓冲：缓冲池中有，磁盘中也有（？？为什么要有），在共享表（有独立表就在独立表）

    • 对于辅助索引的插入或更新操作，并不是直接插入到索引页。如果索引页在缓冲池中就直接插入，如果不在，就先放入InsertBuffer，再定期合并
    • 出现原因：因为利用辅助索引的插入操作，不像主键插入一定是顺序的（如果辅助索引是递增的时间就是顺序的），需要离散访问非聚集索引页（多次io），导致插入性能降低
    • 使用条件：辅助索引，索引不唯一（唯一的话，插入缓冲时还是要取读索引页判断唯一性）
    • 问题：写密集情况下，插入缓冲会占用过多缓冲池内存；部分没刷新到磁盘，数据库恢复开销变大
    • 作用：提高性能
    • 内部实现：B+树，在ibdata1中
    • 合并时机：①该辅助索引页读到缓冲池（比如select时就会检查insertbuffer）；②master；③辅助索引页空间（物理）不足（insertbuffer用位图记录每个辅助索引页的可用空间）

  • 两次写：先将脏页拷贝到doublewriteBuffer，再写副本在doublewrite的物理页（共享表空间），然后写到表空间文件

    • 解决问题：重做日志记录的是对页的物理操作（比如偏移量800写'aaa'），但是如果页写一半系统就崩溃的话，页就损坏了，根据重做日志也不能修改（就是重做日志记录的是你对整一个页的操作，但你实际上对页的操作并不完全）
    • 实现：doublewrite由doublewritebuffer和doublewrite的物理页两部分组成
    • 先写到doublewrite的物理页，然后要写表空间文件时，即使崩溃了，系统恢复时将doublewrite记录的物理页先写回磁盘，页就没有损坏了，再进行重做
    • 作用：提高数据页的可靠性
    • ps：redolog不需要两次写

  • 自适应哈希索引：当innodb发现某些索引值被使用得十分频繁时，它会在内存中基于B+树索引创建哈希索引（并不是整张表），用户无法控制

    • 指向数据页
    • 缺点：占内存，只适合等值查询

  • 异步IO：避免阻塞（索引页不在内存中的话，需要多次io），提高性能。同时有IOmerge功能，即合并多个IO为一个IO，减少IO次数。需要系统支持

  • 刷新邻接页 ：利用IOmerge的机制，当刷新一个脏页时，将页所在的区的所有脏页也进行刷新

  • 预读：也是通过异步IO完成的

    • 避免离散读

第三章：文件

参数文件

mysql启动时指定初始化参数，在哪里找数据库文件
cnf文件

ini文件

日志文件

记录影响数据库的各种类型的活动。比如错误日志、查询日志、二进制日志、慢查询日志

错误日志

对MySQL的启动、运行、关闭过程进行记录

慢查询日志（默认不启动）

在MySQL启动时设定一个阈值，将运行时间超过该阈值的所有SQL语句记录在慢查询日志，可以定位可能存在问题的SQL语句（SQL优化）

查询日志

记录所有对MySQL数据库的请求信息，无论请求是否得到正确执行，比如SELECT和SHOW这类操作

二进制日志

记录对MySQL数据库（所有存储引擎）执行更改的所有操作，但不包含SELECT和SHOW这类操作

  • 作用：数据恢复，检查是否发生数据库注入，复制
  • 记录逻辑语句

套接字文件

表结构定义文件

frm文件

pid文件

记录运行MySQL的进程ID

InnoDB存储引擎文件

表空间文件

  • InnoDB采用将数据按表空间进行存放的设计，用户可以通过多个文件组成一个表空间
  • 独立表空间：一个表一个表空间，ibd后缀，放自己表的索引、数据和insertbuffer的位图
  • 共享表空间：文件名是ibdata1，放undolog、doublewrite等（没有独享表空间，全放在共享表空间）

重做日志文件redolog

  • 作用：事务的持久性依靠redolog，记录页的物理操作
  • 实现：InnoDB采用重做文件组的形式，组内有多个重做日志，一个写满切换，全满checkpoint刷新
  • 太大导致恢复时间长
  • 太小导致总是checkpoint影响性能，或者一个事务的日志需要多次切换重做日志文件
  • 文件是乱序的，因为事务并发进行
  • 不需要doublewrite，因为redolog缓冲写磁盘是以扇区为单位（512B），是最小的单位，保证写入必定成功
  • redolog缓冲刷新时机：①masterthread刷新定期刷新（这也是为什么事务提交快的原因）；②事务提交前；③redolog缓冲池空间剩余空间少于1/2

ps：undolog在事务那一章

redolog和二进制日志的区别

  1. 二进制日志记录所有存储引擎而redolog只记录自己的存储引擎
  2. 二进制日志记录逻辑日志（事务的具体操作）而redolog记录页更改的物理情况
  3. 二进制日志是会追加新文件，而redolog是循环使用的
  4. 写入时间不同，二进制在事务提交后提交而redolog事务进行时也在写入
  5. 事务提交时，先写binlog文件再写redolog文件
  • redolog的状态被拆分为两段
  • redolog写入时，状态为prepare
  • 事务提交时，先写binlog
  • 再提交redolog，状态变为commit
  • 假如先提交redolog，宕机后，binlog是不完整的
  • 假如先提交binlog，宕机后，redolog是不完整的
  • 两段，宕机后，根据binlog进行回滚或提交
  • 作用：保证两个日志的一致性

第四章：表

索引组织表

表中数据都是根据主键顺序组织存放的
每个表都有主键，没主键用非空的唯一索引（is not null + unique）做主键，没非空的唯一索引会隐式自动创建

InnoDB逻辑存储结构

所有数据都被逻辑地存放在一个空间中
行-->页-->区-->段-->表空间

表空间

和上一章的表空间文件类似，不过这里是在内存中的

段

有数据段（叶子节点），索引段（非叶子节点），回滚段

区

里面的页是连续的

页

InnoDB磁盘管理的最小单位，也叫块

行

innodb存储引擎面向列，所以数据是按行存放的
每页允许的行数也有规定，7992行

InnoDB行记录格式

compact（默认）

行溢出

行溢出时，部分数据存在当前页，剩余数据存放在页类型为uncompress blob页（溢出页），由当前页剩余空间中的偏移量指向

约束

实体完整性

保证表中有一个主键

参照完整性

外键要么为空，要么为被参照表主键的值
innodb存储引擎会自动为外键加索引，避免表锁
外键的更新或插入，需要先查被参照表的记录，即select，使用的是lock in share mode（可以设置关闭，导入时不需要这种操作）

  • 不使用MVCC的原因：假如事务A删除被参照表某个主键，事务B更新外键（过程中的select使用mvcc），最后参照表和被参照表就不一致了

视图

视图就是一个命名的虚表，和持久表不同，视图中的数据没有实际的物理存储

  • 视图抽象出需要的数据，不需要关心基表，有一定的安全性作用

分区表

分区：将一个表或索引分解成多个更小、更可管理的部分。逻辑上只有一个表或索引，但物理上这个表或索引可能由多个分区组成。每个分区可以独自处理也可以作为一个大分区的部分

  • mysql支持水平分区（同表不同行可以分），不支持垂直分区（同表不同列可以分）
  • mysql是局部分区索引，即一个分区存放索引和数据。（全局分区，数据分，所有数据的索引放在一个对象中）

第五章：索引与算法

索引是一种数据结构
为什么要索引：

  • 加快数据的检索速度
  • 避免排序和临时表
  • 随机IO变为顺序IO

索引越多越好吗：

  • 数据更新要维护索引
  • 索引会占磁盘空间

选取索引标准：

  • 主键
  • 外键
  • 经常使用

不做索引：

  • 取值范围小的，比如性别
  • 很少使用的

B+树索引

索引找到的是数据所在的对应页，而不是具体行，具体行还需要把页读入内存后再查找

聚集索引

一种数据存储方式
叶子节点存放的是整张表的行记录信息，也将聚集索引的叶子节点称为数据页，数据页是双向链表进行连接
聚集索引的特性决定了索引组织表中的数据也是索引的一部分
每个表只能拥有一个聚集索引
数据页存放的是完整的每行记录，而索引页存放的仅仅是键值和指向数据页的偏移量
聚集索引的存储在逻辑上是连续的，行记录根据主键顺序存放

  • 优点：
    • 相关数据保存在一起，双向链表，适合范围查询
    • 数据访问更快，因为数据和索引都在树中
    • 数据更新移动行时，对二级索引没有影响
  • 缺点：
    • 更新聚簇索引列（即主键）的开销大，因为索引一改，在树中的位置也改变了，因为物理页是由顺序
    • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏的时候或者页分裂导致数据存储不连续的时候
    • 插入速度严重依赖于插入顺序。按主键顺序插入是最快的
    • 二级索引可能很大，因为叶子节点包括主键值
    • 二级索引需要两次索引查找，即回表

PS：避免随机的聚簇索引（UUID），插入变得完全随机（乱序的话新行就不在索引最后了，页分裂也需要开销，也是插入缓冲的那一套原因），影响插入速度，占的空间也会变大（因为主键字段长和页分裂）
页分裂就是需要插入的当前页满了，下一页页满了，需要创建一个新页，把当前页进行分裂（选择一部分记录），把一部分记录放入新页
顺序主键也可能会有不好的结果，当高并发时，所有新行都插入到同一个位置，并发插入需要锁

非聚集索引

一种数据存储方式
叶子节点不包含行记录的所有数据
myisam叶子节点存放索引值，指向数据的地址，key可以重复
数据库表中行的物理顺序与索引顺序可以不相同

二级索引（辅助索引、非聚集索引）

innodb叶子节点保存的是行的主键值（而不是行指针），需要根据这个值再去聚簇索引中查找（回表）
myisam的二级索引和主键索引形式一样

  • 优点：减少当出现行移动或数据页分裂时二级索引的维护工作（就是不需要更新）

聚集索引和非聚集索引的区别

前缀索引

当需要索引很长的字符列，可以选择索引开始的部分字符

  • 优点：让索引更快更小
  • 缺点：无法使用前缀索引做GROUP BY，也不能做覆盖扫描

压缩（前缀压缩）索引（myisam）

默认只能压缩字符串，减少索引大小，能让更多索引放入内存，提高性能

联合索引

对表上多个列进行索引，取选择性最高的列为索引

  • 优点：①第二个键值已经排序，避免只是用一个索引时需要多一次排序

索引最左匹配原则

建立联合索引后，使用索引作为查询条件时，需要按照建立时的顺序来使用左边连续一列或多列
较多个数的索引放在左边

覆盖索引

索引包含所有需要查询的字段的值就叫做覆盖索引
innodb的二次索引（辅助索引）可以利用这个机制，避免对主键索引的二次查询
优点：

  • 非聚集索引不包含整行记录的所有信息，所以比聚集索引小，减少io操作
  • innodb中避免回表查询

哈希索引

基于哈希表实现，需要精确匹配索引所有列的查询才有效
找的是缓冲池中的页
对索引列求哈希码作为key，指向数据行的指针作为value
优点：读取速度O(1)
缺点：不利于范围查询；数据过多，哈希碰撞链表过长；需要索引列（联合索引）完全匹配；只支持等值比较查询；无法排序

  • 适合场景：查找操作

全文索引

倒排索引

倒排索引实现全文检索

主键索引

以主键为索引，非空不重复，innodb必须有主键索引（就是聚集索引），myisam的主键索引是非聚集索引

唯一索引

和主键不同，就是可以为空

冗余索引

创建(a, b)索引后，又创建(a)索引
ps：(b, a)不是冗余索引

索引失效

  • LIKE模糊查询，%在前（n个任意字符，_下划线是任意单个字符）
  • or前后都必须要是索引
  • 联合索引，没有按最左匹配原则来使用
  • 隐式转换，比如需要varchar却使用int
  • 全表扫描比使用索引快
  • 对索引字段进行运算或使用函数

第六章：锁

锁机制用于管理共享资源的并发访问

共享锁、排他锁（行锁、也可以是表锁）

获得共享锁才可以读一行数据、获得排他锁才可以删除或更新一行数据

意向锁

表级别的锁
意向锁是将锁定对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁
InnoDB自动添加
意向锁和表级锁互斥
意向锁之间不互斥，也不和行级锁互斥

  • 过程：如果想对某粒度的对象上锁，需要先对更粗粒度的对象上意向锁
  • 作用：比如本来事务A用行锁锁住一行，事务B想锁该行所在的表，如果锁上了就冲突了。事务B如何判断能否锁，就要依靠事务A对表的意向锁，不然要一行一行判断是否锁住开销太大

一致性非锁定读（默认select读取方式）

利用MVCC实现。如果读取行正在执行delete或update操作，不需要等待解X锁，innodb存储引擎会去读行的一个快照数据

  • 快照数据：该行之前版本的数据，利用undolog完成
  • MVCC：多版本并发控制，快照数据可能有多个版本，只在两种隔离级别下工作
    • 读已提交：取最新的快照数据
    • 可重复读：取事务开始时版本的快照数据
  • 作用：提高数据库并发性

MVCC多版本并发控制

用于读已提交和可重复读
实现1
实现2

  • MVCC的实现依靠隐藏字段+undolog+readview

  • 隐藏字段：每条行记录添加两个字段，db_trx_id记录最近一次修改的事务ID，db_roll_ptr指向当前行的uodolog信息

  • readview（快照）：一个结构体，主要用来做可见性判断，里面保存的是一些事务ID，详细介绍几个字段

    • low_limit_id：目前出现过最大事务ID+1，就是未来第一个新事务的ID
    • up_limit_id：活跃事务列表trx_ids中最小的事务ID
    • trx_ids：活跃事务列表，快照创建时未提交事务的ID
    • creator_trx_id：当前事务ID
    • 读已提交：事务中每次select都会创建一个快照
    • 可重复读：事务中第一次select创建一个快照，之后的每次select都使用这个快照

  • undolog：行记录更新时，将行记录复制一份作为旧版本放在undolog，新行记录的db_roll_ptr指向旧版本，形成链表（剩余介绍在事务中）

    • 链首是最新的旧记录，链尾是最旧的旧记录

  • 可见性比较算法

    1. 取当前undolog行的db_trx_id
    2. 如果大于readview中的low_limit_id，即是当前事务之后的新事务，不可见，根据db_roll_ptr去下一个undolog行
    3. 如果小于readview中的up_limit_id，即是在当前事务之前就提交了，可见
    4. 如果在两者之间
    • 如果在trx_ids找不到，即创建快照时该事务已提交，可见
    • 如果在trx_ids找到，即创建快照时该事务已存在，无论提交与否都不可见，根据db_roll_ptr去下一个undolog行

  • 解决问题：并发和幻读（个人认为不读新版本号的记录不就不会幻读了）

//下面是书本概念

  • 为数据添加两个隐藏字段，一个保存行的创建版本号，一个保存行的过期（删除）版本号
  • select：
    • 可重复读：创建版本号小于等于当前系统版本号，删除版本号未定义或大于当前系统版本号。符合两点才返回结果
  • insert：以当前系统版本号作为创建版本号
  • delete：以当前系统版本号作为删除版本号
  • update：innodb插入一行新纪录，当前系统版本号为创建版本号，原本的行以当前系统版本号作为删除版本号
  • 系统版本号：每开始一个事务，系统版本号递增

一致性锁定读

加锁语句：

  • select...for update；加X锁
  • select...lock in share mode；加S锁

注意事项：innodb采用两段锁协议。必须在事务中（begin/commit）使用，事务提交，锁就解放

  • 使用begin或者start transaction开启事务，关闭自动提交

锁算法

record lock

单个行记录上的锁
锁索引记录

gap lock

间隙锁（索引两边的间隙），锁定一个范围但不包含记录本身（锁当前记录往后的索引的记录）
解决幻读问题

next-key lock

gap+record，锁定一个范围和记录本身（当前记录record+往前的索引的记录gao），前开后闭（原因是主键有序自增）
解决幻读问题

  • 当查询列是唯一索引的情况下，next-key lock降级为record lock，提高并发性
  • 使用辅助索引进行查询操作时，会对聚集索引进行加record lock
  • 辅助索引，使用的是next-key lock，还会对索引后面的记录加gap lock
  • 上面说的都是等值查询，范围查询或者等值查询不存在的行需要next-keylock

锁问题

丢失更新

事务A的更新操作被事务B的更新操作覆盖

  • 所有隔离级别都不会出现

脏读

脏数据是未提交的数据，事务A读到事务B未提交的数据（违背隔离性）

  • 需要隔离级别为读未提交才会发生

不可重复读（也是幻读）

事务A多次读一行数据集合，中间事务B对数据进行修改，事务A两次读到数据不相同

  • 需要隔离级别为读已提交才会发生

幻读

同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能返回之前不存在的行

  • 比如事务A第一次读a=1有一个数据，第二次读有两个数据

阻塞

一个事务的锁需要等待另一个事务的锁释放资源

死锁

两个或两个以上的事务在执行过程，因争夺锁资源造成互相等待的现象

  • 例子：事务A、B都加了S锁，事务A再加X锁，事务B再加X锁就死锁了

解决方法：

  • 超时机制，回滚
  • 等待图进行死锁检测，选择回滚操作小的事务进行回滚

锁升级

数据库在特定情况下，避免开销太大，将锁进行升级，如1000个行锁升级为页锁，锁多内存占用大
情况：

  • 一句单独的SQL语句，在一个对象上持有锁的数量超过阈值（5000）
  • 锁资源占用的内存超过40%

锁升级带来问题，并发性能降低。InnoDB没有这个问题，用位图加锁

第七章：事务

事务是一条SQL语句，或一组SQL语句组成

ACID

A atomicity 原子性

要么全做要么全不做
利用undolog

C consistency 一致性

事务将数据库从一个状态转变为下一个一致的状态
利用undolog

I isolation 隔离性

一个事务的操作不会另一个事务产生影响
利用锁

D durability 持久性

事务一旦提交，结果就是永久性的。即使系统崩溃也能恢复数据
利用redolog

事务的实现

为什么需要事务：有些事情就是一套操作，不能被其他操作干扰

undolog

undolog帮助事务回滚及MVCC功能，位于共享表空间
undolog是逻辑日志，对每行记录进行记录
undolog在共享表空间，undolog的产生伴随着redolog的产生，也需要持久性保护
事务提交后也不能马上删除undolog，因为可重复读级别的MVCC需要。事务提交后把undolog放入链表，由purge线程来判断

  • 格式：
    • 链表形式
    • insert undolog：事务提交后可以删除
    • update（+delete） undolog：事务提交后不能马上删除的undolog
    • 可以根据undolog的内容（有记录主键）找到对应的行记录，mvcc的实现也需要行记录

undolog实现mvcc，用户通过undolog读取之前的行版本信息
redolog保证事务持久性，事务提交时，必须持久化redolog；
redolog记录的页的物理修改操作；

事务隔离级别

  • 读未提交：最低隔离级别，可能会有脏读、不可重复读、幻读。
    • 实现：修改加X锁，读不加S锁
  • 读已提交：允许读取并发事务已提交的数据，阻止脏读，但可能会有不可重复读、幻读。
    • 非一致性锁定读使用MVCC
    • 一致性锁定读和更新操作使用record lock
    • 除了唯一性的约束检查和外键约束会使用gap lock
  • 可重复读（mysql默认）：同一事务读取多次读取结果都是一致的，阻止脏读、不可重复读，innodb用next-key lock阻止幻读。
    • 非一致性锁定读使用MVCC
    • 一致性锁定读和更新操作使用next-key lock避免幻读
  • 可串行化（分布式事务必须选择）：事务序列化执行，锁问题全部阻止。
    • 实现：自动添加一致性锁定读的lock in shared lock
    • 多用户（分布式事务）的丢失更新问题需要可串行化隔离级别

隔离级别越低，事务请求的锁越少或保持锁的时间越短

mysql默认自动提交事务

分布式事务

  • 定义：允许多个独立事务资源参与到一个全局事务中
  • 特点：分布式事务基于两段提交协议，隔离级别必须为可串行化
    innodb存储引擎提供对XA事务的支持，XA事务支持分布式事务的实现（XA事务允许不同数据的分布式事务Oracle+Mysql+SQLServer）。例子：银行转账系统

外部XA事务

  • 实现：一个或多个资源管理器RMS+一个事务管理器TM+一个应用程序AP
    • RMS：提供访问事务资源的方法，通常就是一个数据库
    • TM：协调各个事务，和所有资源管理器通信
    • AP：定义事务的边界，即全局事务中的操作
    • 第一阶段：RMS通知TM准备完毕
    • 第二阶段：TM通知RMS进行ROLLBACK或者COMMIT

内部XA事务（以后学再补充吧）

存储引擎和插件之间，或存储引擎和存储引擎之间。例子：binlog和innodb存储引擎之间

SQL优化

  1. 尽量避免全表扫描，利用上索引
  2. or连接条件时，如果一个是索引一个不是索引，会使用全表扫描。测试
  3. in和not in也会使用全表扫描
  4. 避免函数操作，会全表扫描
  5. 使用组合索引注意遵守最左匹配原则

复制

保持主从服务器的一致性

步骤

  1. 主服务器把数据更改记录到binlog
  2. 从服务器把主服务器的binlog复制到自己的中继日志relay log
  • 从服务器启动IO线程，和主库建立连接
  • 连接后主库启动binlog dump二进制日志转储线程，发送binlog
  • PS：一个binlogdump线程对应一个从服务器
  1. 从服务器重做relay log中的日志，把更改应用到自己的数据库上，已达到数据的最终一致性
  • 从服务器启动SQL线程，实现数据库更新

模式

  • 异步模式（默认）
    主服务器完成提交后马上返回客户端，不需要等待从服务器响应；由从服务器进行请求

  • 半同步模式
    主服务器提交binlog之前会通知binlogdump线程push到从服务器，收到一台从服务器的ACK即可返回客户端；若超时则切换到异步模式。收到ACK之前就已经在存储引擎层提交事务
    ps：ACK是从服务器写到relaylog后才返回的

  • 增强半同步模式
    主服务器收到ACK后再提交事务，再返回客户端

  • 同步模式
    主服务器提交binlog之前会通知binlogdump线程push到从服务器，收到所有从服务器的ack才可以提交（所有slave都已经提交）

复制方式

  • 基于行复制
    把实际的行数据进行记录

优点：

  • 避免一些语句执行起来很慢（比如有些需要全表扫描）

缺点：

  • 不执行语句，那么在日志里就没有记录

  • 日志大

  • 基于语句复制
    逻辑复制，直接复制SQL语句，从服务器把主服务器的SQL再执行一遍

优点：

  • 复制过程就是执行SQL语句的过程，出错也容易理解
  • 日志小

缺点：

  • 存储过程、触发器、或者需要获取当前时间等语句的复制会出现bug

  • MIXED
    可以使用语句就使用语句，不可以使用语句的就是用行

读写分离

  • 同一线程同一数据库内，如有写入操作，以后读操作都从主库读取，以保证数据一致性

向后兼容：新版本服务器可以作为旧版本服务器的备库，反之不行

posted @ 2021-03-14 20:14  肥斯大只仔  阅读(164)  评论(0)  编辑  收藏  举报