MySQL

MySQL
数据库
发布日期:   2023-03-17
更新日期:   2023-03-18
文章字数:   10k
阅读次数:  

MySQL 基础架构

下面就是 MySQL 执行一条 SQL 查询语句的流程，也从图中可以看到 MySQL 内部架构里的各个功能模块。

image-20220910143828625

MySQL 的架构共分为两层：Server 层和存储引擎层:

  • server 层负责建立连接、分析和执行 SQL。所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。

    包含大多数的核心功能模：

    • 连接器： 身份认证和权限相关

      登录 MySQL 的时候，与客户端进行 TCP 三次握手建立连接；校验客户端的用户名和密码；若用户密码正确，连接器会读取该用户的权限并保存，然后后面的权限逻辑判断都基于此时读取到的权限。

    • 查询缓存： 执行查询语句的时候，会先去查询缓存（ Query Cache ）里查找缓存数据

      查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

      MySQL 8.0 版本后移除，因为这个功能不太实用：表更新时会清空查询缓存。

    • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，对sql语句做解析。

      词法分析：MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

      语法分析：根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

    • 优化器：基于查询成本，选择索引， 按照 MySQL 认为最优的方案去执行。

    • 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。

MySQL 存储引擎

MySQL 支持多种存储引擎，你可以通过 show engines 命令来查看 MySQL 支持的所有存储引擎。

image-20220910160112498

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

MyISAM 和 InnoDB 的区别

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

  • MyISAM 不提供事务支持，InnoDB 提供事务支持

  • MyISAM 不支持外键，而 InnoDB 支持外键

  • MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持，依赖于 redo log

MySQL索引

索引是一种用于快速查询和检索数据的数据结构，形象的说就是索引是数据的目录。

索引类型

按数据结构分类

从数据结构的角度来看，MySQL 常见索引有： B 树， B+树和 Hash。

B+树索引类型是 MySQL 存储引擎采用最多的索引类型。

B+树 vs B树

B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

B+树 vs Hash

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。但是 Hash 表不支持顺序和范围查询。

按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

聚簇索引

聚簇索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

  • 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

主键索引 B+Tree

二级索引

二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

二级索引 B+Tree

回表

如果用二级索引查询，会先检索二级索引中的 B+Tree 的索引值（商品编码，product_no），找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据

覆盖索引

当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到（如二级索引自身），这时就不用再查主键索引查。这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引

唯一索引、普通索引、前缀索引都属于二级索引

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

普通索引的唯一作用就是为了快速查询数据

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

  • 建立在单列上的索引称为单列索引，比如主键索引；
  • 建立在多列上的索引称为联合索引；
联合索引

通过将多个字段组合成一个索引，该索引就被称为联合索引，联合索引(product_no, name) 的 B+Tree 示意图如下：

联合索引

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配，即先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。

联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like ‘林%’这种）的时候，就会停止匹配，也就是范围列可以用到联合索引，但是范围列后面的列无法用到联合索引。

对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值（ID 为 2）后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回主键索引去判断呢？

  • 在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
  • 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引优缺

优点 ：

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

索引适用

  • 字段有唯一性限制的，比如商品编码；
  • 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
  • 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

缺点 ：

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

索引不适用

  • WHERE 条件，GROUP BY，ORDER BY 里用不到的字段

  • 字段中存在大量重复数据

  • 表数据太少

  • 经常更新的字段

索引优化

前缀索引优化

使用某个字段中字符串的前几个字符建立索引，可以减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。

局限：

  • order by 就无法使用前缀索引；
  • 无法把前缀索引用作覆盖索引；

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设只需要查询商品的名称、价格，我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

主键索引自增

在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键，从而使每次插入一条新记录，都是追加操作，不需要重新移动数据。

索引NOT NUL

索引列要设置为 NOT NULL 约束，因为对于数据为 NULL 的字段，数据库较难优化

如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

防止索引失效

避免写出索引失效的查询语句，常见的索引失效场景：

  • 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
  • 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
  • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

EXPLAIN

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1            
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列的含义如下:

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: SELECT 查询的类型.
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息

type

  • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据
  • eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高
  • ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
  • range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
  • index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
  • ALL: 表示全表扫描,

Extra

  • Using filesort
    当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
  • Using index
    “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary
    查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

总结

img

MySQL事务

事务隔离级别是怎么实现的

事务是逻辑上的一组操作，要么都执行，要么都不执行，由MySQL的InnoDB 引擎实现。

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

事务特性

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

  1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。一个事务中的所有操作，要么全部完成，要么全部不完成；
  2. 一致性（Consistency）： 执行事务前后，数据满足完整性约束，保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
  3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

  • 持久性是通过 redo log （重做日志）来保证的；
  • 原子性是通过 undo log（回滚日志） 来保证的；
  • 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
  • 一致性则是通过持久性+原子性+隔离性来保证；

并发事务问题

读-写问题

有线程安全问题，可能会造成事务隔离性问题

脏读

一个事务「==读到==」了另一个「==未提交事务修改过的数据==」

图片
不可重复读

在一个事务内多次读取同一个数据，出现前后两次读到的数据不一样的情况

图片
幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，出现前后两次查询到的记录数量不一样的情况

图片

写-写问题

有线程安全问题，可能会存在更新丢失问题

第一类丢失更新

A事务撤销时, 把已经提交的B事务的更新数据覆盖了

clipboard.png

InnoDB存储引擎的隔离级别都使用了排他锁，这类更新丢失问题是不会出现的

第二类丢失更新

A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失

clipboard.png

此类更新丢失问题, 无法依靠前三种隔离级别来解决, 只能用最高隔离级别 Serializable 或者手动使用乐观锁, 悲观锁来解决。

事务隔离级别

SQL 标准提出了四种隔离级别来规避这些读-写问题，隔离级别越高，性能效率就越低，这四个隔离级别如下：

  • 读取未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
  • 读取已提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
  • 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
  • 串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

针对不同的隔离级别，并发事务时可能发生的现象也会不同

图片

  • 解决脏读现象，就要升级到「读提交」以上的隔离级别；

  • 要解决不可重复读现象，就要升级到「可重复读」的隔离级别。

  • 解决幻读现象不建议将隔离级别升级到「串行化」，因为这样会导致数据库在并发事务时性能很差。

    InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

这四种隔离级别具体是如何实现的呢？

  • 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
  • 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
  • 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过Read View 来实现的，它们的区别在于创建 Read View 的时机不同， Read View 可以理解成一个数据快照。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制，是通过「版本链」来控制并发事务访问同一个记录时的行为，从而实现读（快照读）-写冲突不加锁。

  • 当前读

    当前读实际上是一种加锁的操作，是悲观锁的实现

    像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

  • 快照读

    像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

实现原理

MVCC 通过隐式字段、undo log 和 Read View实现

隐式字段

聚簇索引记录除了我们自己定义的字段外，还有数据库隐式定义的字段

image-20220913153654174

  • DB_TRX_ID：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除

  • DB_ROLL_PTR，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

  • DB_ROW_ID：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

undo log

undo log 称为回滚日志，用于保存数据更新之前版本的数据，在事务进行 rollback 时可以直接进行数据恢复。同时由于多版本历史数据的存在，我们可以去读取旧版本的数据，快照读就是通过读取旧版本的数据实现的。

在 InnoDB 存储引擎中 undo log 分为两种： insert undo log 和 update undo log：

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

Read View 主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

img

Read View 有四个重要的字段：

  • m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，“活跃事务”指的就是，启动了但还没提交的事务。
  • min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
  • max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
  • creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

在创建 Read View 后，我们可以将记录中的 trx_id 划分这三种情况：

img

工作机制

事务每次读取记录时，会查看这条记录的 trx_id 值，

  • 如果在事务的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内，如果在，那么说明这条记录是被还未提交的事务修改的，这时事务 并不会读取这个版本的记录。而是沿着 undo log 链条往下找旧版本的记录，直到找到 trx_id 「小于」事务 的 Read View 中的 min_trx_id 值的第一条记录

  • 如果 trx_id事务的 Read View 中的 min_trx_id 值小，这意味着修改这条记录的事务早就在事务启动前提交过了，所以该版本的记录对事务 可见的，也就是事务可以获取到这条记录。

可重复读实现机制

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

读取已提交实现机制

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。

MySQL锁

InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁

表级锁

MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

行级锁

MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

MySQL InnoDB 支持三种行锁定方式：

  • 记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
  • 间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
  • 临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类

  • 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

S 锁 X 锁
S 锁 不冲突 冲突
X 锁 冲突 冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。可以通过以下语句显式加共享锁或排他锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁

意向锁是表级锁，用来快速判断是否可以对某个表使用表锁，共有两种：

  • 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  • 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手 动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

IS 锁 IX 锁
IS 锁 兼容 兼容
IX 锁 兼容 兼容

MySQL日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）

  • undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
  • redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
  • binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

undo log

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

  • 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
  • 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
  • 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

undo log 有两大作用：

  • 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
  • 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

redo log

redo log 是物理日志，记录了某个数据页做了什么修改，每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

  • 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
  • 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能

Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

img
  • 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
  • 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

Buffer Pool 是基于内存的，如果断电重启，还没来得及落盘的脏页数据就会丢失。

因此，InnoDB 引擎使用了WAL （Write-Ahead Logging）技术： MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

img

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）

redo log 和 undo log 区别

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

  • redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
  • undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

redo log 刷盘策略

redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘

image-20220915172033879

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

  • 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
  • 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
  • 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

下面是不同刷盘策略的流程图。

  • innodb_flush_log_at_trx_commit=0

    image-20220915172736501

    为0时，如果MySQL挂了或宕机可能会有1秒数据的丢失。

  • innodb_flush_log_at_trx_commit=1

    image-20220915172514520

    为1时， 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。

    如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失

  • innodb_flush_log_at_trx_commit=2

    image-20220915172820371

    为2时， 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。

    如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写

img

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

  • write pos 是当前记录的位置，一边写一边后移
  • checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

img

binlog

redo log 是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写（不会覆盖以前的日志）， 用于备份恢复、主从复制。

主从复制的实现

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

MySQL 主从复制过程
  • MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
  • 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
  • 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主）

MySQL 主从复制模型：

  • 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。实际不可用。
  • 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
  • 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。
binlog写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

img

两阶段提交

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样

但redo log与binlog两份日志之间的逻辑可能不一致，例如执行过程中写完redo log日志后，binlog日志写期间发生了异常。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案：将redo log的写入拆成了两个步骤prepare和commit

img

文章作者: liuzhangjie
文章链接: https://liuzhangjie1713.github.io/2023/03/17/mysql/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 liuzhangjie !
MySQL
  目录