「学」 MySQL

本文最后更新于：8 个月前

MySQL 是最流行的关系型数据库管理系统，在 web 应用方面 MySQL 是最好的 RDBMS（Relational Database Management System，关系数据库管理系统）应用软件之一。

1 基本架构

MySQL 基本架构

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

  • Server 层负责建立连接、分析和执行 SQL。所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

  • 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

1.1 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的：

1
mysql -h$ip -P$port -u$user -p

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

数据库里长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。但是使用长连接后占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。可以使用定期断开长连接或者客户端主动重置连接的方式释放内存。

1.2 查询缓存

MySQL 拿到一个查询请求（select 语句）后，会先查询缓存。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

1.3 分析器

MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

  • 词法分析：MySQL 会根据你输入的字符串识别出关键字，构建 SQL 语法树。

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

1.4 优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

1.5 执行器

确定了执行方案后，接下来 MySQL 就真正开始执行语句了，这个工作是由执行器和存储引擎交互完成的。

1.6 存储

一张数据库表的数据是保存在 表名字.ibd 的文件里的，这个文件也称为独占表空间文件。表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB 存储引擎的逻辑存储结构大致如下图：

  • 行（row）

    数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

  • 页（page）

    记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

    InnoDB 的数据是按页为单位来读写的，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。默认每个页的大小为 16 KB，也就是最多能保证 16 KB 的连续存储空间。

  • 区（extent）

    InnoDB 存储引擎是用 B+ 树来组织数据的。

    B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置可能并不是连续的，磁盘查询时就会有大量的随机 I/O，随机 I/O 是非常慢的。

    解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

    在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1 MB，对于 16 KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

  • 段（segment）

    表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

    • 索引段：存放 B+ 树的非叶子节点的区的集合
    • 数据段：存放 B+ 树的叶子节点的区的集合
    • 回滚段：存放的是回滚数据的区的集合

1.7 Buffer Pool

MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。InnoDB 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

  • 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取其中的数据，否则再去磁盘中读取。

  • 当修改数据时，首先修改 Buffer Pool 中数据所在的页，然后将其设置为脏页，最后由后台线程将脏页写入到磁盘。

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16 KB。因此，Buffer Pool 同样需要按「页」来划分。

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的 16 KB 的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。Buffer Pool 除了缓存索引页和数据页，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。

如何提高缓存命中率？

LRU（Least recently used）算法。该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。

  • 当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。

  • 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。

Buffer Pool 里有三种页和链表来管理数据。

  • Free Page（空闲页），表示此页未被使用，位于 Free 链表。
  • Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于 LRU 链表。
  • Dirty Page（脏页），表示此页已被使用且已经被修改，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

脏页什么时候会被刷入磁盘？

引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。

在脏页还没有刷入磁盘时，MySQL 宕机了也不会丢失数据。InnoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

  • 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘
  • Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘
  • MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘
  • MySQL 正常关闭之前，会把所有的脏页刷入到磁盘

MySQL 抖⼀下是什么意思？

针对 innoDB 导致 MySQL 抖的原因，主要是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。所以，⽆论是查询语句在需要内存的时候可能要求淘汰⼀个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了更新语句，都可能是造成业务端感知 MySQL 抖了⼀下的原因。

在开启了慢 SQL 监控后，如果发现偶尔会出现一些用时稍长的 SQL，如果间断出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。

2 日志模块

更新语句的流程会涉及到 undo log、redo log 、binlog 这三种日志：

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

2.1 undo log

一个事务在执行过程中，在提交事务之前，如果 MySQL 发生了崩溃，可以通过 undo log 回滚到事务之前的数据。

undo log 是一种用于撤销回退的日志，MySQL 会记录更新前的数据到 undo log 日志文件里面。当事务回滚时，可以利用 undo log 来进行回滚，做与原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据进行 insert 操作。

另外，undo log 还有一个作用，通过 Read View + undo log 实现 MVCC（多版本并发控制）。

undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

2.2 redo log

如果 MySQL 每一次的更新操作都需要写进磁盘，找到对应的记录更新，那么整个过程 IO 成本、查找成本都很高。WAL（Write-Ahead Logging）技术解决了这个问题，它的关键点就是先写日志，再写磁盘。

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存。InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候。重做日志文件是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。

redo log 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

redo log 可以保证即使数据库发生异常重启，之前提交的记录也不会丢失，这个能力称为 crash-safe。

redo log 与 undo log 的区别：

  • redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值
  • undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值
  • 事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务
  • 事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务

2.3 binlog

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

redo log 与 binlog 的区别：

  • redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  • redo log 是物理日志，记录的是”在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 update T set c=c+1 where ID=2。
  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。因为 redo log 文件是循环写，会边写边擦除日志，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复。

MySQL 的主从复制依赖于 binlog，复制的过程就是将 binlog 中的数据从主库传输到从库上：

  1. 写入 binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
  2. 同步 binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
  3. 回放 binlog：回放 binlog，并更新存储引擎中的数据。

在完成主从复制之后，就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

MySQL 主从复制模型

  • 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

  • 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。提交事务和复制这两个流程在不同的线程中独立执行，互不等待。这种模式一旦主库宕机，数据就会发生丢失。

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

2.4 执行一条更新

具体更新一条记录 update T set c=c+1 where ID=2; 的流程如下：

  1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id=2 这一行记录：
    • 如果 id=2 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
    • 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器；
  2. 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
    • 如果一样的话就不进行后续更新流程；
    • 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
  3. 开启事务，InnoDB 层更新记录前，首先要记录相应的 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。
  4. InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。
  5. 至此，一条记录更新完了。
  6. 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
  7. 事务两阶段提交。

2.5 两阶段提交

update T set c=c+1 where ID=2

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。

两阶段提交是为了让两份日志之间的逻辑一致。

binlog 会采用追加写记录所有的逻辑操作，如果 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。当需要恢复到指定的某一秒时，你可以这么做：

  1. 找到最近的一次全量备份，将这个备份恢复到临时库。
  2. 从备份的时间点开始，将备份的 binlog 依次取出来，重放到指定的那个时刻。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash：

  • 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

  • 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了”把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

3 索引

数据库索引是数据库管理系统中一个排序的数据结构，包含着对数据表里所有记录的引用指针。以协助快速查询、更新数据库表中数据。如果没有索引，数据库引擎需要扫描整个数据表来找到匹配的记录。而有了索引，数据库引擎可以直接跳到索引中存储的位置，快速定位到匹配的记录，从而大大加快查询速度。

  • 按「数据结构」分类：B+Tree 索引、Hash 索引、Full-text 索引
  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）
  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引
  • 按「字段个数」分类：单列索引、联合索引

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

  • 需要占用物理空间，数量越大，占用空间越大
  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大
  • 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护

什么时候适合用索引：

  • 字段有唯一性限制的，比如商品编码
  • 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引
  • 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了

3.1 B+Tree

B+Tree 是一种多叉树，叶子节点存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。

InnoDB 里的 B+ 树中的每个节点都是一个数据页。

B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O，所以 B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高。

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

如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。

  • B+Tree vs B-Tree

    B+Tree 只在叶子节点存储数据，而 B-Tree 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，磁盘 I/O 次数更少。

    另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

  • B+Tree vs 二叉树

    对于有 N 个叶子节点的 B+Tree，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

    在实际的应用当中，d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3-4 层左右，也就是说一次数据查询操作只需要做 3-4 次的磁盘 I/O 操作就能查询到目标数据。而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

  • B+Tree vs Hash

    Hash 在做等值查询的时候效率很快，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

3.2 索引优化

  1. 前缀索引优化

    前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引。使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

  2. 覆盖索引优化

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

    假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

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

  3. 主键索引自增

    InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

    如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

    如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

  4. 索引 NOT NULL

    索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂。

    NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式（opens new window）中至少会用 1 字节空间存储 NULL 值列表，如下图的紫色部分：

  5. 防止索引失效

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

4 事务

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是其被 InnoDB 取代的重要原因之一。

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

4.1 并行事务的问题

  • 脏读（dirty read）

    如果一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象。

    假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取余额数据，然后再执行更新操作，如果此时事务 A 还没有提交事务，而此时正好事务 B 也从数据库中读取余额数据，那么事务 B 读取到的余额数据是刚才事务 A 更新后的数据。如果在上面这种情况事务 A 发生了回滚，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。

  • 不可重复读（non-repeatable read）

    在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了不可重复读现象。

    事务 A 先开始从数据库中读取余额数据，然后继续执行代码逻辑处理，在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的。

  • 幻读（phantom read）

    在一个事务内多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象。

    事务 B 先从数据库查询账户余额大于 100 万的记录，发现共有 5 条。事务 A 插入了一条余额超过 100 万的账号，并提交了事务。然后事务 B 再次查询账户余额大于 100 万的记录，此时查询到的记录数量有 6 条。

4.2 事务的隔离级别

  • 读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到。

  • 读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到。

  • 可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，InnoDB 引擎的默认隔离级别。

  • 串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

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

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；

对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

4.3 MVCC

MVCC 即多版本并发控制，为每个事务创建多个数据版本，每个版本对应一个特定时间点的数据库状态，不同事务可以基于各自的时间点来进行读取和写入操作，而不会相互干扰。

当前读和快照读是 MVCC 机制下的两种数据读取方法，各自适用于各种不同的应用场景。

  • 当前读（Current Read）

    • 当前读是指事务在读取数据时，总是读取最新提交的数据版本。

    • 当前读能够读取其他事务已经提交的数据，同时在当前事务有未提交的修改时，也会读取自己所做的修改，可能会读取到未提交的数据。

    • 当前读适用于需要获取最新数据状态的场景，比如，实时查询账户余额。然而，需要注意的是，在并发环境下，当前读可能会引发一致性问题。

  • 快照读（Snapshot Read）

    • 快照读，也称为一致性读，是指事务在读取数据时，会读取一个事务开始时的数据版本，即创建事务时的快照。

    • 快照读仅会读取已提交的数据版本，不会读取其他事务未提交的数据。

    • 快照读适用于需要事务隔离和数据一致性的场景。比如，在事务内部进行多次读取操作。

    • 快照读能够提供事务开始时的数据一致性视图，避免了并发冲突和未提交数据的影响，但可能不够实时。

数据库的三种并发场景：

  • 读 - 读：不存在任何问题，也不需要并发控制；

  • 读 - 写：有线程安全问题，事务可能出现隔离性问题，例如脏读、幻读、不可重复读；

  • 写 - 写：有线程安全问题，可能存在更新丢失问题。

如果没有 MVCC，读-写之间就必须加锁。锁是一种性能低下的组件。MVCC 就是一种不使用锁，去解决读写冲突问题。

MVCC 的实现主要依赖于：三个隐藏字段（trx_id、roll_pointer、row_id）、Undo log 及 ReadView。

不同事务或者相同事务的对同一记录的修改，会导致该记录的 undo log 生成一条记录版本线性表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录。所以，一个记录会被一堆事务进行修改，一个记录中就会存在很多 undo log。

那对某个事务来说，这么多 undo log，到底应该选择哪些 undo log 执行回滚呢？ReadView 机制 就是用来为事务做可见性判断的，它可以判断版本链中的哪个版本是当前事务可见的。

ReadView 仅仅记录一个事务开始的时候，系统的事务 id 列表，和相关的事务信息。

将要被修改的数据的最新记录中的 DB_TRX_ID（当前事务id）取出来，与系统当前其他活跃事务的 id 去对比，如果 DB_TRX_ID 跟 Read View 的属性做了比较，不符合可见性，那么就通过 DB_ROLL_PTR 回滚指针去取出 undolog 中的 DB_TRX_ID 做比较，即遍历链表中的 DB_TRX_ID，直到找到满足条件的 DB_TRX_ID，这个 DB_TRX_ID 所在的旧记录，就是当前事务能看到的最新老版本数据。

5 锁

5.1 全局锁

1
flush tables with read lock;

执行 FTWRL 后，整个数据库就处于只读状态了。如果要释放全局锁，则要执行 unlock tables。

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。如果数据库里有很多数据，备份就会花费很多的时间，期间业务只能读数据，而不能更新数据，这样会造成业务停滞。

如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

5.2 表级锁

  • 表锁

    1
    2
    3
    4
    5
    //表级别的共享锁，也就是读锁；
    lock tables t_student read;

    //表级别的独占锁，也就是写锁；
    lock tables t_stuent write;

    尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大会影响并发性能，InnoDB 实现了颗粒度更细的行级锁。

  • 元数据锁（MDL）

    MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做变更。不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

    • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
    • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

    当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

    反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

  • 意向锁

    意向锁的目的是为了快速判断表里是否有记录被加锁。如果没有意向锁，那么加独占表锁时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

    • 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
    • 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

  • AUTO-INC 锁

    表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

    AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

    但是，AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

5.3 行级锁

行级锁的类型主要有三类：

  • Record Lock，记录锁，也就是仅仅把一条记录锁上
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身。只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

5.4 死锁

死锁的四个必要条件：互斥、占有且等待、不可抢占、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过打破循环等待条件来解除死锁状态：

  1. 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

  2. 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

6 SQL

SQL（Structured Query Language）指结构化查询语言，可以访问和处理数据库，包括数据插入、查询、更新和删除。

  • DDL：Data Definition Languages 语句，即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，如 CREATE，ALTER，DROP，TRUNCATE，COMMENT，RENAME。

  • DML：Data Manipulation Language 语句，即数据操作语句，用来查询、添加、更新、删除等，如 SELECT，INSERT，UPDATE，DELETE，MERGE，CALL，LOCK TABLE。

命令

  • SELECT - 从数据库中提取数据
  • UPDATE - 更新数据库中的数据
  • DELETE - 从数据库中删除数据
  • INSERT INTO - 向数据库中插入新数据
  • CREATE DATABASE - 创建新数据库
  • ALTER DATABASE - 修改数据库
  • CREATE TABLE - 创建新表
  • ALTER TABLE - 变更（改变）数据库表
  • DROP TABLE - 删除表
  • CREATE INDEX - 创建索引（搜索键）
  • DROP INDEX - 删除索引

语法

  • SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。
  • SELECT DISTINCT 语句用于返回唯一不同的值。
1
2
SELECT column1, column2, ...
FROM table_name;
  • WHERE 子句用于提取那些满足指定条件的记录。
  • LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
  • 通配符（%，_）可用于替代字符串中的任何其他字符。
  • IN 操作符允许您在 WHERE 子句中规定多个值。
  • BETWEEN/NOT BETWEEN 操作符用于选取介于/不介于两个值之间的数据范围内的值。
  • WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
  • WHERE column IS NULL 在 column 值为空时使用。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

SELECT * FROM Websites
WHERE url LIKE 'https%';

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
  • AND & OR 运算符用于基于一个以上的条件对记录进行过滤。
1
2
3
SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');
  • ORDER BY 关键字用于对结果集进行排序。
  • ASC 表示按升序排序；DESC 表示按降序排序。
  • GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
1
2
3
4
5
6
7
8
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
  • INSERT INTO 语句用于向表中插入新记录，column 可省略。
  • SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。
  • MySQL 数据库不支持 SELECT INTO 语句，但支持 INSERT INTO ... SELECT。
1
2
3
4
5
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

INSERT INTO table2
SELECT * FROM table1;
  • UPDATE 语句用于更新表中已存在的记录。
1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • DELETE 语句用于删除表中的记录。
  • DROP 语句可以删除索引、表和数据库。
1
2
DELETE FROM table_name
WHERE condition;
  • SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
  • INNER JOIN：如果表中有至少一个匹配，则返回行
  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN：只要其中一个表中存在匹配，则返回行
1
2
3
4
SELECT column1, column2, ...
FROM table1
JOIN table2
ON condition;

函数

  • AVG() - 返回平均值
  • COUNT() - 返回行数
  • FIRST() - 返回第一个记录的值
  • LAST() - 返回最后一个记录的值
  • MAX() - 返回最大值
  • MIN() - 返回最小值
  • SUM() - 返回总和
  • LEN() - 返回文本字段中值的长度（MySQL 中使用 LENGTH）
学习笔记
「学」 MySQL
https://qanlyma.github.io/Note-MySQL/
作者
Qanly
发布于
2023年8月7日