mysql高级

mysql基础

三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

一般说来，数据库只需满足第三范式(3NF）就行了。

第一范式（1NF）：要求数据库表的每一列都是不可分割的最小数据单元。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（非主属性列不存在对主码的部分依赖）。

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）：在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关。

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

2、关系型数据库

顾名思义，关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一列就存放着一条数据（比如一个用户的信息）。

有哪些常见的关系型数据库呢？

MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite） …。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。

MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。

mysql高级

一、MYSQL体系结构和存储引擎

Mysql被设计为一个单进程多线程架构的数据库,Mysql数据库实例在系统上的表现就是一个进程.

mysql体系结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1wfOsUtj-1620617182567)(mysql高级.assets/image-20210331102124675-0616898.png)]

1、连接层：连接池组件

2、服务层：管理服务和工具组件、sql 接口组件、查询分析器组件、优化器组件、缓冲组件

3、存储引擎层：插件式存储引擎

4、存储层：物理文件

mysql数据库区别于其他数据库的最重要的特点：插件式的表存储结构。

存储引擎是基于表的，而不是数据库。

1、mysql存储引擎

存储引擎好处：每个存储引擎都有各自的特点，能够根据具体的应用建立不同的存储引擎表。

1、MyISAM存储引擎

5.5.8之前，是默认的存储引擎（windows版本除外）

它的缓冲池只存储索引文件，不缓冲数据文件。数据文件的缓存交由操作系统本身完成。

MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。支持全文索引，支持表锁。

2、InnoDB存储引擎

支持事务，行锁设计，支持外健，mysql5.5.8开始，是默认的存储引擎。

通过使用多版本并发控制MVCC来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为repeatable read。

高可用、高性能、高可扩展性。

2、MyISAM与InnoDB对比

1.是否支持行级锁

MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下

性能不好！这也是为什么 InnoDB 在并发写的时候，性能更好的原因！

2.是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。

3.是否支持外键

MyISAM 不支持，而 InnoDB 支持。

一般我们也是不建议在数据库层面使用外键的，应用层面可以解决。不过，这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定。

4.是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

🌈 拓展一下：

• MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
• MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。
• 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

5.是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

讲真，这个对比有点废话，毕竟 MyISAM 连行级锁都不支持。

MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能。

关于 MyISAM 和 InnoDB 的选择问题

大多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意！）。

《MySQL 高性能》上面有一句话这样写到:

不要轻易相信“MyISAM 比 InnoDB 快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。

二、InnoDB存储引擎

支持ACID、行锁设计、MVCC。

事务具有4个特征，分别是原子性、一致性、隔离性和持久性，简称事务的ACID特性；

InnoDB1.2继承了上述版本所有功能，增加了全文索引支持、在线索引添加。

2.3 InnoDB体系结构

InnoDB存储引擎有多个内存块，可以认为这些内存快组成了一个大的内存池，它们负责：

1、维护多有进程/线程需要访问的多个内部数据结构

2、缓存磁盘上的数据，方便快速读取，同时在对磁盘文件的数据修改之前在这里做缓存。

3、重做日志（redo log) 缓冲。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ayzHlMGp-1620617182569)(mysql高级.assets/image-20210331105257656.png)]

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下innodb能恢复到正常运行状态。

后台线程

1. Master Thread:核心的后台线程，主要负责将缓存池中的数据异步刷新到磁盘，

内存

innodb存储引擎是基于磁盘存储的，按照页的方式进行管理。由于CPU速度与磁盘速度之间的差异，使用缓冲池技术提高数据库的整体性能。

缓冲池就是一块内存区域，通过内存的速度来弥补磁盘速度较慢。

数据库读取页----缓冲池—磁盘

数据库修改页—缓冲池–磁盘 。页从缓冲池刷新回磁盘的操作不是在每次发生页刷新时触发，而是通过Checkpoint 机制刷新回磁盘。 ？

Innodb_buffer_pool_size

数据库中的缓冲池是通过 LRU（最近最少使用）算法进行管理的。

2.4 Checkpoint 技术

问题？

1）每次页发生变化，都刷新到磁盘，开销大

2）在缓冲池将页的新版本刷新到磁盘时发生宕机，数据就不能恢复了。数据丢失。

所以，采用write ahead log。预写日志。

预写日志：当事务提交时，先写重做日志，再修改页。

当发生宕机导致数据丢失，通过重做日志完成数据恢复。这也是ACID中持久性的要求。

Checkpoint（检查点）技术：解决：

1、缩短数据库的恢复时间；

2、缓冲池不够用时，将脏页刷新到磁盘；

3、重做日志不可用时，刷新脏页。

五、索引与算法

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。索引的作用就相当于目录的作用。

1、索引的优缺点

优点 ：

• 使用索引可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

2、索引的底层数据结构

1.Hash & B+树

Hash 索引指的就是 Hash 表，最大的优点就是能够在很短的时间内，根据 Hash 函数定位到数据所在的位置，也就是说 Hash 索引检索指定数据的时间复杂度可以接近 0（1）。

但是，MySQL 并没有使用 Hash 索引而是使用 B+树作为索引的数据结构。为什么呢？

1.Hash 冲突问题 ：知道 HashMap 或 HashTable 的同学，相信都知道它们最大的缺点就是 Hash 冲突了。不过对于数据库来说这还不算最大的缺点。

2.Hash 索引不支持 顺序 和 范围查询 (Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

试想一种情况:

SELECT * FROM tb1 WHERE id < 500;Copy to clipboardErrorCopied

在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了。

2.B 树、B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的; B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

InnoDB存储引擎支持以下几种常见的索引:

1. B+树索引
2. 全文索引
3. 哈希索引

InnoDB存储引擎 支持的哈希索引 是自适应的,InnoDB存储引擎会根据表的使用自动生成哈希索引,不能人为的干预,

B+树索引类似于二叉树,根据键值对快速找到数据.B+树能找到的只是被查找数据行所在的页,数据库读取该页到内存,在再内存中查找,最后得到数据.

二分查找法–二叉查找数和平衡二叉树—B+树。

1、B+树

B+ 树将所有数据记录节点按照键值的大小顺序存放在同一层叶子节点上，而非叶子节点只存储 key 值信息，这样可以大大增加每个节点存储的 key 值的数量，降低 B+ 树的高度。

b+数的插入操作：

必须保证插入叶子结点中的记录依然排序，同时需要考虑插入到b+树的3种情况。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RYMCnxfo-1620617182570)(mysql高级.assets/image-20210331151356660.png)]

1、都没满，直接插入到叶子结点；

2、叶子结点满，索引结点没有满。

3、都满：

3、索引类型

1、主键索引

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

2、二级索引（辅助索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

4、聚集索引与非聚集索引

1.聚集索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，通过主键聚集数据。而 Myisam 并不支持聚簇索引。

B+树中，聚集索引存储不是在物理上连续的，但是在逻辑上是连续的。

聚集索引的优点

聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点

1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

2.非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

二级索引属于非聚集索引。

MYISAM 引擎的表的.MYI 文件包含了表的索引， 该表的索引(B+树)的每个叶子非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD 文件的数据。

非聚集索引的叶子节点并不一定存放数据的指针， 因为==二级索引的叶子节点就存放的是主键，根据主键再回表查数据。==这里做了重复的工作：两次B+树查找而不是一次。对于 Innodb ，自适应哈希索引能够减少这样的重复工作。

优点：

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的。

缺点：

1. 跟聚集索引一样，非聚集索引也依赖于有序的数据。
2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

总结聚集索引、非聚集索引：

如果为聚集索引，那么叶子结点的data存放数据；

如果为非聚集索引，那么叶子结点的data存放指向数据的指针。

非聚集索引一定回表查询吗(覆盖索引)?

非聚集索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表， 因为它的主键索引的叶子节点存放的是指针。但是如果 SQL 查的就是主键呢?

SELECT id FROM table WHERE id=1;

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

5、覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4kYQq4MI-1620617182570)(mysql高级.assets/20210420165341868.png)]

2. 辅助索引

辅助索引（非聚集索引），叶子记录并不包涵行记录的全部数据。叶子节点除了包含键值外，每个叶子除了包含键值还包含了一个书签（bookmark）,该书签用来是相应行数据的聚集索引键（这里是索引键，所以仍然需要在查询一次聚集索引，得到最终的数据）。

只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高 ，并发度最低。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

六、锁

【为什么要锁】

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性(脏读，不可重复读，幻读等)，可能产生死锁。为了解决这个问题，加锁是一个非常重要的技术，对实现数据库并发控制是一个好的方案。

难点？

开发多用户、数据库驱动的应用时，1、要最大程度地利用数据库的并发访问；2、同时确保每个用户能以一致的方式读取和修改数据。

6.1锁

锁是数据库系统区别于文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问。

Innodb存储引擎会在行级别上对表数据上锁。也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访问。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

Innodb存储引擎的实现和Oracle数据库非常类似，提供一致性的非锁定读 MVCC、行级锁支持。行级锁没有相关额外的开销，并可以同时得到兵法性和一致性。

6.2 lock与latch

latch分为mutex(互斥量)和rwlock（读写锁）。用来保证并发现成操作临界资源的正确性，并且通常没有死锁检测的机制。

lock的对象是事务，用来锁定数据库中的对象，比如表、页、行。

本章关注的是lock。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rZaC2c10-1620617182571)(/Users/wangziyuan/Library/Application Support/typora-user-images/image-20210331154242563.png)]

6.3 innodb存储引擎中的锁

MyISAM 和 InnoDB 存储引擎使用的锁：

• MyISAM 采用表级锁(table-level locking)。
• InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

6.3.1锁的类型

InnoDB 存储引擎实现了如下两种标准的行级锁：

1、共享锁：允许事务读一行数据。S Lock

begin;/begin work;/start transaction; (三者选一就可以)
#(lock in share mode 共享锁)
SELECT * from TABLE where id = 1 lock in share mode;

2、排他锁：允许事务删除或更新一行数据。 X Lock。

begin;

Select * from test_innodb_lock where a=8 for update; 为某一行上锁

独占。排他。其他操作会被阻塞，直到锁定行的会话提交commit。

3、Commit; 其实set autocommit=1;会自动提交。因为set autocommit=0;所以需要手动提交commit;

数据库中的层次结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vSYuwMlY-1620617182572)(/Users/wangziyuan/Library/Application Support/typora-user-images/image-20210331161050638.png)]

意向锁：（Intention Lock):将锁定的对象分为多个层次，意味着事务希望在更细粒度上加锁。

比如。想在记录上加X锁，需要先对数据库A、表、页上加意向锁 IX lock 。如果已经有事务对表1 加了S表锁，则不兼容，需等待。

意向排他锁：事务想要获取一张表中某几行的共享锁

意向排他锁：事务想要获取一张表中某几行的排他锁

6.3.2 一致性非锁定读–>MVCC

• 一致性非锁定读是指 InnoDB存储引擎通过 行多版本控制 的方式来读取当前执行时间数据库中行的数据。

如果读取的行正在努执行delete 或update操作，这时读取操作不会因此去等待行上锁的释放。相反的，InnoDB存储引擎回去读取行的一个快照数据。

之所以称其为非锁定读，因为不需要等待访问的行上X锁(排他锁）的释放。快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

​ 快照数据是当前行数据之前的版本的数据，每行记录可能有多个版本，一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称为多版本并发控制 MVCC。

​ 非锁定机制极大提高了数据库的并发性。在 InnoDB 存储引擎的默认级别下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也不相同。

	在事务隔离级别read commited 和 repeatable read 下，InnoDB存储疫情使用非锁定的一致性读。然而对于快照数据的定义却不相同。

read commited 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。

repeatable read 事务隔离级别下，于快照数据，非一致性读总是读取事务开始时的行数据版本。

InnoDB存储引擎的默认事务隔离级别是：可重复读。所以此时，对于快照数据，非一致读总是读取事务开始时的行数据版本。

6.3.3 一致性锁定读

事务的隔离级别为 repeatable read 模式下，InnoDB 存储引擎的select 操作使用一致性非锁定读。但某些情况下，用户需要显示对数据库读取操作 进行加锁 以保证 数据逻辑的一致性。这要求数据库支持加锁语句，即使是对于select 的只读操作。

• Select * from test_innodb_lock where a=8 for update;

  为读取的行记录加一个X锁（排它锁），其他事务不能对已锁定的行加上任何锁。

• Select * from test_innodb_lock where a=8 lock in share mode;

  对读取的行记录加一个S锁（共享锁），其他事务可以向被锁定的行加S锁，但是如果加 X锁，则会被阻塞。

对于 一致性非锁定读，即使读取的行已经被执行了 Select … for update; ，也是可以进行读取的，这和之前讨论的情况一样。此外，

Select … for update， Select … lock in share mode 必须是写在事务中，当事务提交了，锁也就释放了。因此：

在使用上诉两个锁定语句时，务必加上：begin; 或者 start transaction;

begin; 或者 start transaction;
Select  ...  for update;

如何分析行锁定？

通过检查’innodb_row_lock状态量来分析系统上的行锁的争夺情况。

show status like ‘innodb_row_lock%’;

SELECT * from TABLE where id = 1 lock in share mode;

6.3.4 自增长与锁

6.4锁的算法

6.4.1行锁的3种算法

• Record Lock : 单个行记录上的锁。

mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;

• Gap Lock : 间隙锁，锁定一个范围，但不包含记录本身

• Select * from  emp where empid > 100 for update;
  

• Next-Key Lock : Record Lock + Gap Lock。锁定一个范围，并且锁定记录本身

​ Record Lock总会去锁住索引，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

​ Next-Key Lock是结合了 Record Lock 和 Gap Lock的一种锁定算法，在 Next-Key Lock算法下，InnoDB对于行的查询都是采用这种Next-Key Lock锁定算法。例如一个索引有10，11，13，20这四个值

，那么该索引可能被Next-Key Locking 的区间为：

(-∞ ，10] ，（10，11]，（11，13]，（13，120]，（20，+∞）。

使用 Next-Key Lock的锁定技术称为 Next-Key Locking。其设计目的是为了解决Phantom Problem幻读 。利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁（predict lock）的一种改进。

Phantom Problem是指在同一事务下，连续两次执行相同的SQL语句可能会导致不同的结果，第二次的SQL语句可能会返回之前不存在的行（重点在记录数不一样）。也就是在随后的查询中，多了一些原本不存在的记录，就好像发生了幻觉一样。幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

当查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key Lock进行优化，降级为Record Lock，即仅锁住索引本身，而不是范围。

若是辅助索引，还是会用到Next-Key Lock。

所以：对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么索引其实是range类型查询，而不是point类型查询，故InnoDB存储引擎依然使用 Next-Key Lock。

6.4.2 解决 Phantom Problem幻读

在默认的事务隔离级别下，即 repeatable read下，InnoDB存储引擎采用 Next-Key Locking机制来避免幻读问题。这点可能不同于其他的数据库，如oracle数据库，因为其可能需要在 selializable 的事务隔离级别下才能解决幻读。

Phantom Problem是指在同一事务下，连续两次执行相同的SQL语句可能会导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

例如表由1、2、5这三个值组成，若事务T1执行如下SQL语句：

SELECT * FROM t WHERE p > 2 FOR UPDATE;

此时事务T1并没有进行提交操作，上述应该返回 5这个结果。

若此时，另一个事物T2插入了4这个值，并且数据库运行该操作，那么事务T1再次执行上述语句，返回结果是4和5。这与第一次得到的结果不同，违反了事务的隔离型，即当前事务能够看到其他事务的结果。

InnoDB存储引擎采用 Next-Key Locking的算法来避免幻读问题。对于上述语句，其锁住的不是5这单个值，而是对（2，+∞）这个范围加了X锁（排它锁）。因此任何对于这个范围的插入额都是不被允许的，从而避免幻读。

6.5锁问题

6.5.1脏读

脏页指的是在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页数据不一致的。

脏数据指事务对缓冲池中行记录的修改，还没有被提交。

脏页是非常正常的。脏页是因为数据库实例内存和磁盘的异步造成的，并不影响两者最终的一致性。

脏数据却截然不同。脏数据指：未提交的数据，如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的数据，违反了数据库的隔离性。

脏读：指的就是在不同的事务中，当前事务可以读到另外事务为提交的数据。就是可以读到脏数据。

胀肚现象只在未提交读 隔离级别下会发生。脏读隔离基本上没有用，只有在一些特殊情况下还是可以将隔离级别设置为 未提交读 。例如replication环境中的slave节点，并且在该slave上的查询并不需要特别精准的返回值。

6.5.2不可重复读

不可重复读指：一个事务内多次读取同一数据集合，在事务没有结束时，另外一个事务也会访问该同一数据集合，并做了一些DML操作（表中的数据进行增、删、改的操作）。导致在同一事务内，读取到的数据不一样，这称之为不可重复读。

脏读、不可重复读区别：脏读是读到未提交的数据；不可重复读诗读到已经提交的数据，但是违反了数据库事务的一致性的要求。

6.5.3丢失信息

丢失信息是另一个锁导致的问题，简单来说其就是一个事物的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。

6.6阻塞

因为不同锁之间的兼容性关系，在有些时刻一个事物中的锁需要等待另一个事物中的锁释放它所占有的资源。这就是阻塞。阻塞并不是一件坏事，阻塞是为了确保事务可以并发且正常地运行。

6.7死锁

ＭyISAM表锁是deadlock free的，这是因为ＭyISAM总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但是在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。

​ 1、解决死锁问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事物重新开始。但这会导致并发性能下降，浪费资源。

​ 2、超时机制：当一个事务等待超时，则对它进行回滚，另一个事物就能继续进行了。超时时间可以通过参数innodb_lock_wait_timeout来设置（默认50s）。但是有可能超时回滚的这个事务所占权重比较大，这个事务的时间要比另一个事务要多。（就是还不如回滚另一个没超时的）。

​ 3、所以当前数据库都普遍采用wait-for graph（等待图）的方式来进行死锁检测。比超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB采用这种方式。在每个事务请求锁并发生等待时都会判断是否存在贿赂，若存在则有死锁，通常InnoDB存储引擎选择回滚undo量最小的事务。

系统判定死锁的方法：

• 超时法：如果某个事物的等待时间超过指定时限，则判定为出现死锁；

• 等待图法：如果事务等待图中出现了回路，则判断出现了死锁。

  对于解决死锁的方法，只能是撤销一个处理死锁代价最小的事务，释放此事务持有的所有锁，同时对撤销的事务所执行的数据修改操作必须加以恢复。

6.8锁升级

指将当前锁的粒度降低，比如1000个行锁升级为一个页锁，或者将页锁升级为表锁。

InnoDB不存在锁升级的问题。因为它不是根据每个记录来产生行锁的，是根据每个事务访问的每个页对锁进行管理，采用的是位图的方式。因此不管一个事务锁住页种一个记录还是多个记录，开销都一样。

补充6.3.2MVCC：

简介：MVCC使得大部分支持行锁的事务引擎不再单纯的使用行锁来进行数据库的并发控制，而是把数据库的行锁和行的多个版本结合起来，只需要很小的开销，就可以实现非锁定读，从而提高数据库的并发性能。MVCC（非锁定一致性读）主要是在REPEATABLE READ和READ COMMITED两个隔离级别下工作。因为READ UNCOMMITED总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE则会对所有读取的行都加锁。

MVCC实现原理

1.写任务时候，会克隆一份数据，用版本号来区分；

2.写任务操作会新的克隆数据，直至提交

3.并发的读任务可以继续读旧版本的数据，不至于阻塞

七、事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

7.1什么是数据库事务？

数据库事务在我们日常开发中接触的最多了。如果你的项目属于单体架构的话，你接触到的往往就是数据库事务了。

平时，我们在谈论事务的时候，如果没有特指分布式事务，往往指的就是数据库事务。

那数据库事务有什么作用呢？

简单来说：数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 特性：

原子性：A atomic。事务是一个不可再分割的工作单元，事务中的操作要么都发生，要么都不发生。A向B转账

一致性：C consistency。 指事务将数据从一种状态转变为下一种一致状态。执行事务前后，数据保持一致。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

隔离性：I isolation。每个读写事务对其它事务能相互分离。

持久性：D durablity。 事务一旦提交，其结果是的。即使数据库发生故障也不应该对其有任何影响。

数据事务的实现原理呢？

以 MySQL 的 InnoDB 引擎为例：

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

7.2事务的实现

事务隔离型由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log 来完成。

redo log 重做日志。保证事务的原子性、持久性

undo log保证一致性。

都可以认为是一种恢复操作。redo log 恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的好似页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

7.2.1.redo log

1. 基本概念

redo log重做日志用来实现事务的持久性。

​ 由两部分组成。一部分是：内存中的重做日志缓冲redo log buffer；二：重做日志文件 redo log file。

InnoDB是事务的存储引擎，通过Force Log at Commit机制实现事务的持久性。即当事务提交commit时，必须先将该事务的所有日志写入到重做日志文件进行持久化。待事务的commit操作完成才算完成。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做必须进行一次fsync操作。为了确保重做日志写入磁盘，必须进行一次fsync操作。

二进制日志（binlog)和redo log ?

​ 在mysql 数据库中还有一种二进制日志（binlog)，其用来进行point-in-time（PIT)的恢复 及 主从复制（replication) 环境的建立。从表面上看其和重做日志很相似，都是记录对于数据哭操作的日志。然而，本质上很大不同。

1、首先，重做日志是在InoDB存储引擎层产生，而二进制日志是在MySQL数据库的上层产生的，并且二进制日志不仅仅针对于InoDB存储引擎，MySQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制 日志。
2、其次，两种日志记录的内容形式不同。MySQL数据库上层的二进制日志是一一种逻辑日志，其记录的是对应的SQL语句。而InnoDB存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。
3、此外，两种日志记录写人磁盘的时间点不同。二进制日志只在事务提交完成后进行一次写人。 而InoDBD存储引擎的重做日志在事务进行中不断地被写人，这表现为日志并不是随事务提交的顺序进行写人的。

2. log block

重做日志都是以512字节进行存储的。这意味着重做日志缓冲，重做日志文件都是以块（block）的方式进行保存的。称为重做日志块。每块大小为512字节。

3. log group

4. 重做日志格式

不同的数据库操作会有对应的重做日志格式。此外，由于InnoDB存储引擎管理是基于页的，所以重做日志管理的日志也是基于页的。

5.LSN 日志序列号

LSN是Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。LSN代表的含义有：

• 重做日志写入的总量

• checkpoint的位置

• 页的版本

  LSN表示事务写入重做日志的字节总量。例如，当前重做日志的LSN是1000，事务T1写入了100字节的重做日志，LSN就变成1100，又有事务T2写入200字节的重做日志，那么LSN变成：1300。可见LSN记录的是重做日志的总量，其单位是字节。

每个页的头部也有一个LSN，记录的是该页最后刷新时LSN的大小。重做日志记录的是每个页的物理更改日志，因此页中的LSN用来判断是否需要进行恢复操作。例如：页的LSN为10000，数据库启动时，写入重做日志的LSN为13000，表明该事务已经提交，数据库需要恢复；重做日志中的LSN小于页中的LSN，不需要进行重做，因为页中的LSN表示已经刷新到该位置。

6.恢复

InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。

因为redo log是物理日志，所以恢复速度比逻辑日志快很多。

仅需恢复checkpoint开始的后面的日志部分。

7.2.2.undo log

1.基本概念

undo log保证一致性。

1、在事务需要回滚rollback时，这就进行了undo。

​ undo的回滚不是将数据库物理的恢复到了原来的样子，而是逻辑的恢复到了原来的样子。也就是说，当Innodb存储引擎回滚时，它实际上做的是与先前相反的工作。例如，对于每个insert，InnoDB存储引擎会完成一个delete；delete->insert；update->相反的undate。

2、undo另一个作用是**MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。**当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现 一致性非锁定读。

3、最重要的一点，undo log会产生redo log，也就是undo log 的产生会伴随着redo log的产生，这是因为undo log 也需要持久性的保护。

2.undo存储管理

InnoDB对undo的管理是采用段的方式。

当事务提交时，InnoDB会做以下两件事：

• 将undo log 放入列表，以供之后purge操作
• 判断undo log 所在的也是否可以重用，若可以 分配给下个事务使用。

3. undo log格式

InnoDB 中，undo log分两种：

• insert undo log
• update undo log

1. insert undo log 是指在insert 操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)，故该undo log可以在事务提交后直接删除。不需要进行purge操作。
2. update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放人undo log 链表，等待purge线程进行最后的删除。

7.2.3 purge

delete和update操作 可能并不是直接删除原来的数据。undo log只是对节点做一个标记。例如，对于deleete操作，仅是将主键列的记录delete flag设置为1。记录并没有被删除，即记录还是存在于B+树中。

purge用于最终完成delete和update操作。因为InnoDB存储引擎支持MVCC操作。所以记录不能在事务提交时立即进行处理。这时其他事务可能正在引用该行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。

7.2.4.group commit

​ 若事务为非只读事务，则每次事务提交时，需要进行一次fsync操作，以此保证重做日志都已经写入磁盘。当数据库发生宕机时，可以通过重做日志进行恢复。虽然固态硬盘的出现提高了磁盘的性能，然而磁盘的fsyns性能是有限的。

​ 为了减少fsync操作的次数，提高效率，当前主流的数据库都提供了group commit 功能，一次fsync可以刷新确保多个事务日志被写入文件。

​ 对于InnoDB存储引擎，事务提交时会进入两个阶段的操作：

1：修改内存中事务对应的消息，并且将日志写入重做日志缓冲；

2：调用fsync将确保 日志 都从 重做日志缓冲 写入磁盘。

​ 在InnoDB 1.2 版本之前，在开启二进制日志后，InnoDB存储引擎的group commit功能会失效，从而导致性能的下降。并且在线环境多使用 replication环境，因此二进制日志的选项基本都为开启状态，因此这个问题尤为显著。

​ 导致这个问题的原因是在开启二进制日志后，为了保证存储引擎层中的事务和二进制日志的一致性，二者之前使用了两阶段事务，其步骤：

1、当事务提交时 InnoDB存储引擎进行 prepare操作。

2、mysql数据库上层写入二进制文件binlog。

3、InnoDB存储引擎层将日志写入重做日志文件。

​ 1）修改内存中事务对应的消息，并且将日志写入重做日志缓冲；

​ 2）调用fsync将确保 日志 都从 重做日志缓冲 写入磁盘。

事务控制语句:

START TRANSACTION/BEGIN：开启一个事务。
COMMIT：提交一个事务。
ROLLBACK：回滚一个事务。
SAVEPOINT xxx：创建一个保存点。
RELEASE SAVEPOINT xxx：删除指定保存点(未存在该保存点抛异常)。
ROLLBACK TO xxx：回滚到指定保存点的状态。
SET TRANSACTION：设置事务的隔离级别。

7.3并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 还没提交就读到了。当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

• 不可重复读（Unrepeatableread）: 修改。指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

• 幻读（Phantom read）: 新增/删除。幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，==接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，==所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改，比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

7.4事务的隔离级别：

脏读、不可重复读、幻读，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

隔离级别：4个 。级别由低到高。

1、Read Uncommitted 未提交读：最低级别，只能保证不读取物理上损坏的数据；可能导致脏读、不可重复读、幻读。

2、Read committed 已提交读：允许读取并发事务已经提交的数据。可能导致不可重复读、幻读。

3、Repeatable Read 可重复度： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。可能导致幻读。

4、Serializable 可串行化。 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

mysql默认隔离级别：可重复读。

查看当前数据库的事务隔离级别：show variables like ‘tx_isolation’;

SELECT @@tx_isolation;

**MySQL InnoDB 的 REPEATABLE-READ（可重复读）并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是 Next-Key Locks。**p331

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(已提交读) ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读） 并不会有任何性能损失。

7.7分布式事务

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

XA 是一个分布式事务协议，由Tuxedo 提出，所以分布式事务也称为XA 事务。

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

7.8不好的事务习惯

mysq数据库l默认配置下，总是自动提交， autocommit=1。

在应用程序中，最好的做法是把事务的START TRANSACTION、COMMIT、ROLLBACK操作交给程序端来完成，而不是在存储过程内完成。

7.9 长事务

长事务就是执行时间较长的事务。比如，对于银行系统的数据库，每过一个阶段可能需要更新对应账户的利息。如果对应账户的数量很大，这个事务就需要很长时间来完成。在执行过程中，当数据库或操作系统、硬件等发生问题时，重新开始事务的代价变得不可接受。
数据库需要回滚所有已经发生的变化，而这个过程可能比产生这些变化的时间还要长。因此，对于长事务的问题，有时可以通过转化为小批量(minibatch)的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次已完成的事务继续进行。

八、备份和恢复

备份类型

根据备份的方法不同：

• Hot Backup（热备）：在数据库运行中直接备份
• Cold Backup（冷备）：在数据库停止的情况下备份
• Warm Backup（温备）：同样是在数据库运行中进行，但会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性

根据备份后文件的内容：

逻辑备份：备份出的文件内容是可读的，一般是文本文件，内容一般是由一条条SQL语句，或者是表内实际数据组成。这类方法的好处是可以观察导出的文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复所需要的时间往往较长。
裸文件备份：复制数据库的物理文件。这类备份的恢复时间往往较逻辑备份短很多。
按照备份数据库的内容来分：

完全备份：对数据库进行一个完整的备份;
增量备份：在上次完全备份的基础上，对于更改的数据进行备份;
日志备份：对MySQL数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做来完成数据库的point-in-time的恢复工作。

MySQL数据库**复制（replication）**的原理就是异步实时地将二进制日志传送并应用到从（slave/standby）数据库。

8.6 快照备份

8.7复制

8.7.1复制的工作原理

复制(replication) 是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication 的工作原理分为以下3个步骤:

1)主服务器(master) 把数据更改记录到二进制日志(binlog) 中。

2)从服务器(slave) 把主服务器的二进制日志复制到自己的中继日志(relay log)中。

3)从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性

复制的工作原理其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。复制的工作原理如图。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wPRf8tzW-1620617182572)(mysql高级.assets/image-20210505160351255-0616958.png)]

• 从服务器有2个线程。

  1）一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志;

​ 2）另一个是SQL线程，复制执行中继日志。

• 主服务器上一个现场负责发送二进制日志。

之前已经说过MySQL的复制是异步实时的，并非完全的主从同步。若用户要想得知当前的延迟，可以通过命令SHOW SLAVE STATUS和SHOW MASTER STATUS得知。

补充 主从同步：

1. 主从集群

MySQL 主从集群带来的作用是：

1、提高数据库负载能力，主库执行读写任务（增删改），备库仅做查询。

2、提高系统读写性能、可扩展性和高可用性。

3、数据备份与容灾，备库在异地，主库不存在了，备库可以立即接管，无须恢复时间。

2. 主从同步

9.2.1 binlog是什么？有什么作用？

全名binarylog，一种日志文件，逻辑日志。保存了Mysql服务器实例上数据修改的日志信息，包含全量的mysql增删改查数据。

用于主从复制，在主从结构中，binlog 作为操作记录从 master 被发送到 slave，slave服务器从 master 接收到的日志保存到 relay log 中。

用于数据备份，在数据库备份文件生成后，binlog保存了数据库备份后的详细信息，以便下一次备份能从备份点开始。

3. 主从复制

mysql主从复制需要三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）

binlog dump线程：主库中有数据更新时，根据设置的binlog格式，将更新的事件类型写入到主库的binlog文件中，并创建log dump线程通知slave有数据更新。当I/O线程请求日志内容时，将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。

I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中。

SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hiUyvoom-1620617182572)(mysql高级.assets/image-20210505162349072-0616962.png)]

过程解析:

主库写入数据并且生成binlog文件。该过程中MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。

在事件写入二进制日志完成后，master通知存储引擎提交事务。

从库服务器上的IO线程连接Master服务器，请求从执行binlog日志文件中的指定位置开始读取binlog至从库。

主库接收到从库的IO线程请求后，其上复制的IO线程会根据Slave的请求信息分批读取binlog文件然后返回给从库的IO线程。

Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容。

从库服务器的SQL线程会实时监测到本地Relay Log中新增了日志内容，然后把RelayLog中的日志翻译成SQL并且按照顺序执行SQL来更新从库的数据。

从库在relay-log.info中记录当前应用中继日志的文件名和位置点以便下一次数据复制。

4.怎么减少主从延迟

• 降低多线程大事务并发的概率，优化业务逻辑
• 优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。
• 提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。
• 尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。
• 实时性要求的业务读强制走主库，从库只做灾备，备份。

8.7.2 快照+复制的备份架构

复制可以用来作为备份，但功能不仅限于备份，其主要功能如下:

1、数据分布。由于MySQL数据库提供的复制并不需要很大的带宽要求，因此可以在不同的数据中心之间实现数据的复制。

2、读取的负载平衡。通过建立多个从服务器，可将读取平均地分布到这些从服务器中，并且减少了主服务器的压力。一般通过DNS的Round-Robin和Linux的LVS功能都可以实现负载平衡。

3、数据库备份。复制对备份很有帮助，但是从服务器不是备份，不能完全代替备份。

4、高可用性和故障转移。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。

可见，复制的设计不是简简单单用来备份的，并且只是用复制来进行备份是远远不够的。假设当前应用采用了主从的复制架构，从服务器作为备份。这时，一个执行了误操作，如DROP DATABASE或DROP TABLE，这时从服务器也跟着运行了。
这时用户怎样从服务器进行恢复呢?
因此，一个比较好的方法是通过对从服务器上的数据库所在分区做快照，以此来避免误操作对复制造成影响。当发生主服务器上的误操作时，只需要将从服务器上的快照进行恢复，然后再根据二进制日志进行point-in-time的恢复即可。因此快照+复制的备份架构如图。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x6r4QYdS-1620617182573)(mysql高级.assets/image-20210505161216706-0616966.png)]

此外，建议在从服务器上启用read-only 选项，这样能保证从服务器上的数据仅与主服务器进行同步，避免其他线程修改数据。

数据库优化

1、读写分离

流程图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-66NSCI5O-1620617182573)(mysql高级.assets/image-20210505170141754-0616969.png)]

优点：

可以避免单点故障

负载均衡，对于数据库读能力的拓展

挑战：

1.对sql类型判断，是读（select）走从库，是写（insert、update、delete）走主库

2.主从数据的同步延时问题：对于强一致的业务，读写都强制走主库

3.事务问题：如果同一事务中，因读写分离，导致跨了多个库，那么jdbc将无法控制，属于分布式事务（效率低）范畴。所以，现在对于事务中夸库的情景，都统一走主库。

2、分库分表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NBlWroFf-1620617182573)(mysql高级.assets/image-20210505170223720-0616977.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7A4dK7Lz-1620617182574)(mysql高级.assets/image-20210505170254452-0616973.png)]

分库的优点：

降低单台机器的负载压力，提升写入性能。（提供了多机器实现写的能力）。

分表的好处：

提高数据操作数据的效率，无论是读写。

挑战：

1.基本数据库的增删查改：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nQWRB1N4-1620617182574)(mysql高级.assets/image-20210505170405965-0616980.png)]

sql解析：

sql路由：sql路由包括库路由和表路由，确定插入到那个库或者那个表

sql改写：

sql执行：

结果集的合并：每个sql执行之后，都会有一个执行结果，并对每一个分库分表结果集进行合并，得到一个完整的结果

2.分布式的ID

在分库分表后，我们不能再使用mysql的自增主键。因为在插入记录的时候，不同的库生成的记录的自增id可能会出现冲突。现在用了一个全局的ID生成器

3.动态扩容

指增加分库分表的数量

这种方式可以在扩容时，可以不用数据迁移

4.数据迁移

对于老的数据库，已经有存量的数据，单表已经很大，可以先进行数据迁移

其中的全量同步：

因为mysql的binlog只会保存近几天的更改的数据

1.刷新一遍binlog。问题在于，如果表的数据量过大，例如上亿条数据，这种方式可能会造成很大的主从延迟，大量的binlog也会占用大量的磁盘空间。

2.流式读取历史数据。流式读取之前，记录下当前的binlog位置，之后流式分批读取，读取完成之后，把之前记录的binlog位置提供给binlog订阅服务如(puma、databus、canal)等，进行增量同步。

其中的全量同步：

拉取binlog数据，转化成SQL到目标库上面。

补充 ：创建高性能的索引、慢查询