目录

想要良好的使用MySQL，对底层原理一定要有一定的掌握。本文致力于讲解实用的底层技术。

概述

  1. 架构关键词：
    • 客户端层面：连接池、shell
    • 服务器层面：服务器进程、NoSQL接口、SQL接口、解析器Parser、优化器Optimizer、缓存和缓冲区、存储引擎、文件系统
  2. 适用场景：需要事务支持、高并发需求、数据一致性要求较高、时延和稳定性有一定要求
  3. 由于目前（截止2023年2月）MySQL仍以InnoDB为默认引擎，因此底层篇未经说明，一律以InnoDB实现为主。

客户端和服务器端

  1. 连接
    1. 连接池：降低连接延迟、连接复用。每个连接是一个线程。 连接池的作用是未超过连接上限时直接捞一个连接并复用，不用每一次都建立连接断开连接、超过上限会创建新的并自动进行断开

log

推荐阅读博客MySQL 日志：undo log、redo log、binlog 有什么用？

出现三种log是一个历史原因。实际上binlog在很早就有了，MyISAM时期就有，但是binlog无法用于保证持久性。因此在InooDB中额外使用了redo log。

Binlog

binlog更像是归档日志，用于数据备份和主从同步。

  1. 参考：
    1. 研发应该懂的binlog知识（上）

Undo Log

undo log的核心目标是支持回滚，支持MVVC。因此也常被称为回滚日志。

Redo Log

重放日志，用于保证数据的持久性。

由于对数据库的修改是先对内存进行的，虽然会对MySQL的内存页数据结构进行标记，标记为脏页，但是回写并不一定立刻执行，如果掉电，则会发生数据丢失。

因此对于数据的任何修改的提交，必须保证redo log写磁盘完成。redo log由于是磁盘追加写，因此性能较好，至少比写回脏页的随机写要好一些。

事务

  1. 事务特性：ACID
    • A（Atomic）：原子性，事务内的所有操作要么全部完成，要么全部失败
    • C（Consistency）：一致性，事务结束后，数据库的完整性不会被破坏
    • I（Isolation）：隔离性，允许多个并发事务同时对其数据进行读写和修改的能力，不会因为多个事务并发执行时由于交叉执行而导致数据的不一致
    • D（Durability）：持久性：对数据的修改就是永久的，即便系统故障也不会丢失
  2. 事务隔离级别：
    • 事务的不同隔离级别中，主要有三种情况不同，见下表
      隔离级别 脏读 不可重复读 幻读
      读未提交（Read Uncommitted） √ √ √
      读已提交（Read committed） × √ √
      可重复读（Repeatable read） × × √
      可串行化（Serializable） × × ×

    注：不可重复读侧重于对单行数据的修改，幻读则是说明了此时有行增加删除，不仅需要行锁，还需要区间锁甚至表级锁才能避免

  3. 锁机制
    • 从共享性看锁类型：
      锁名称 缩写 （最小）粒度 互斥性 含义
      共享锁 S 行锁 和X、IX互斥 读
      排他锁 X 行锁 和全部互斥 写
      意向共享锁 IS 表锁 和X互斥
      意向排他锁 IX 表锁 和X、S互斥
      自增锁 表锁 自增列专用的表级锁

      意向锁是为表锁服务的，InnoDB会在数据操作之前自动添加、判断，如果一个进程想要启用表锁，就可以看一下意向锁是否存在，如果存在，则该表就不能启用表锁（否则需要逐行判断是否有人上锁）

    • 行级锁的具体类型
      • 记录锁：锁定指定记录
      • 间隙锁：不包含记录本身，锁定两个记录的中间范围
      • 临键锁：记录锁+间隙锁，能一定程度上解决幻读问题
    • 加锁机制：乐观锁、悲观锁
    • 锁的粒度：表锁、行锁、页锁
    • 元数据锁：分成读和写两种，所有的DDL、DML都需要进行申请
    • 封锁协议：
      • 一级封锁协议：修改前加X锁
      • 二级封锁协议：读取前加S锁，读完释放
      • 三级封锁协议：读取前加S锁，事务结束后释放
      • 一次性封锁协议：要求事务一次性获取所有需要的锁，或者失败全部不获取（避免死锁）
      • 二阶段封锁协议：事务分为获取锁的阶段和释放锁的阶段，且一旦开始释放就不再申请（仍不能避免死锁）
    • 锁的使用：
      • 和当前的事务隔离级别、当前查询语句的索引使用情况、索引是否唯一、是否为等值查询等情况均有关，必要时再进行优化
      • 表锁并非完全不可取，当事务需要更新大部分数据时，表又比较大，如果使用默认的行锁，不仅效率低，而且还容易造成其他事务长时间等待和锁冲突。且如果事务比较复杂，使用行锁很可能引起死锁导致回滚
    • 不同引擎对比：
      • MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。
      • 在InnoDB中，锁是逐步获得的，就造成了死锁的可能。发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

存储引擎

  1. 引擎对比：
    • InnoDB：InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。适合处理经常更新的高并发的表。使用B+Tree索引结构。Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引（聚簇索引）。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。
    • MyISAM：曾经的默认引擎。没有提供对数据库事务的支持，也不支持行级锁和外键。也是使用的B+Tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。
  2. 表空间（TableSpace）：InnoDB用于存储一个数据的一个逻辑容器，从内容管理上分为系统表空间、用户表空间、撤销表空间、临时表空间；从关系上又可分为共享表空间（多表共享一个表空间）、独立表空间
    • 段（Segment）：由若干个区构成，区之间不一定连续，是数据库的基本分配单位，不通类型的数据会创建不同的段，如表段、索引段
    • 区（Extent）：由连续的页构成（默认64个），实际上为了性能，一般也会连续申请多个连续的区。
    • 页（Page）：磁盘管理的最小单位（默认16KB），页也对应着所属段的一个数据节点，常见类型有：
      • 数据页（B-tree Node）
      • undo页（undo Log Page）
      • 系统页 （System Page）
      • 事物数据页 （Transaction System Page）
      • 插入缓冲位图页（Insert Buffer Bitmap）
      • 插入缓冲空闲列表页（Insert Buffer Free List）
      • 未压缩的二进制大对象页（Uncompressed BLOB Page）
      • 压缩的二进制大对象页 （compressed BLOB Page）
    • 行（Row）：表数据以行的形式进行存储。
      • 以数据页为例，其结构为：文件头（描述页信息）、页头（描述页状态信息）、最大最小记录（虚拟）、用户记录（若干条）、空闲空间、页目录、文件尾。
        • 文件头：包含数据页的前驱、后继指针、以组成B+树结构。
        • 页目录：用户记录实际上是以有序链表的形式存放在页中，页目录存储了用于二分查找的索引值，当查找时，先和页目录中的各个索引值进行比较（超出范围的会被映射到最大最小记录），先确定索引值，最后去该索引值所映射的区间上遍历查找。
  3. 索引：
    1. 优点：减少扫描量、避免表锁、随机IO变顺序IO，可以做到预排序
    2. 缺点：占用存储、单个存储元素修改时间变长
    3. 索引类型：主键索引、唯一索引、联合索引（联合索引的多个键值都会出现在B+树的非叶子节点中）、普通索引、全文索引（InnoDB后期也支持了，常用于大文本like等模糊查询）
    4. 数据结构：B+树、Hash索引
    5. 实现：聚簇索引、非聚簇索引
      • 聚簇索引的实现下，二级索引的数据域为二级索引key+主键key，如果是联合索引，就是所有的联合列+主键Key
      • InnoDB默认PK（主键）是聚簇索引。如果表没有定义PK，则第一个not NULL unique列是聚集索引。否则，InnoDB会创建一个隐藏的row-id作为聚集索引。实际上考虑到主键对B+树节点分裂的性能影响，推荐使用自增的id作为主键。
  4. 索引和页的配合：使用索引最终的目的时找到数据所在的数据页，然后系统会将数据页加载进内存，最终通过对数据页的页目录进行搜索，定位到具体的记录行。
    1. 这里要注意，B+树的非叶子节点中是需要存储主键/索引，以及对应的页号的。
  5. 其他关键词：
    1. 多引擎支持

查询计划

快照读和当前读

这是SQL在产生查询计划时的一个重要区别。参考全网最全一篇数据库MVCC详解

当前读，它读取的数据库记录，都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据。是悲观锁的一种操作。例如以下操作

select lock in share mode -- 共享锁
select for update -- 排他锁
update -- 排他锁
insert -- 排他锁
delete -- 排他锁
-- 串行化事务隔离级别

快照读，快照读的实现是基于多版本并发控制，即MVCC，既然是多版本，那么快照读读到的数据不一定是当前最新的数据，有可能是之前历史版本的数据。事务级别不是串行化时，所有其他的不加锁的select操作。

MVCC

  • 多版本并发控制机制（Mutil-Version Concurrency Control MVCC），用于实现事务隔离级别的底层机制，以更好的支持对数据库的并发访问
  • InooDB的实现：
    • 基本原理：每行添加两个值，修改该行的事务id（trx_id）、指向上一个版本的指针（roll_pointer），再结合事务执行时创建的ReadView（主要包含当前事务id、活跃事务id列表），通过不同的ReadView生成策略，去读最新数据或者是Undo Log中记录的旧版本数据，完成对读已提交和可重复读的区分支持
    • MVCC和锁：MVCC实现了高性能的读写。读不加任何锁，读写不冲突。默认RR级别隔离，并且不显式指定lock，都将会使用MVCC支持的快照读。否则会使用当前读，运用锁机制保证读写。

      换言之，MVCC并不能保证不出现幻读。它只能保证快照读情况下不出现幻读，对于当前读，必须使用锁机制才行。

    • ReadView策略
      • 读已提交RC：在每一次进行快照读的时候生成ReadView，因此在事务内每次快照读，都有可能有其他事务提交新的修改（单行数据在前后的读取过程中可能变动）
      • 可重复读RR：在第一次进行快照读的时候生成ReadView，在事务未提交之前的所有快照读都会使用这个ReadView，因此事务执行期间其他事务的提交不可见

难点：为什么只用MVCC不能解决当前读的幻读问题。参考事务隔离级别中的可重复读能防幻读吗? 。

简单来说，考虑这样一个例子，如果A事务读取某一个范围，并对这个范围内的值设置统一的修改。此时在A事务的中间，事务B，插入了一个在这个范围内的新的值。那么A事务在最后的修改时，就会多修改一行。

更糟糕的情况是，如果事务A也想插入一个相同的新的值，此时数据库甚至会报告主键冲突。

多机

  1. 分片（sharding）：
    1. 出现原因：
      1. 单一实例主db，扛不住写入了，拆分成多个主写入（多个机器）。
      2. 主从同步，虽然主可以并发写，但是同步一般是单线程，拆分之后可以提高同步效率。
    2. 实现方式：
      1. 在代理层做路由、将库拆分成多个部分，分散在多个机器上。（分片键修改困难、扩缩容困难、数据迁移困难）
  2. 同步
  3. 其他关键词：
    1. 多区域写入同步：
      • 建立双工链路
      • 用UTC时间戳解决一致性问题，选最新的
    2. 同城多机房（强一致性）：由代理层负责，写入只允许在主库，并同步到其他机房。读取一定在本机房。

高可用

  1. High availability（HA）：高可用是MySQL进入生产应用的基本要求。
  2. Orchestrator：一种高可用复制管理工具。

参考

  1. Mysql中MVCC的使用及原理详解
  2. 对MySQL中MVCC理解
  3. MVCC原理之ReadView
  4. Java全栈知识体系：InnoDB的MVCC实现机制
  5. MySQL的InnoDB索引原理详解
  6. 一文理解 MySQL 中的 page 页
  7. 锁的分类
  8. 深入理解MySQL锁类型和加锁原理
  9. MySQL（九）：InnoDB 表空间（Tables）
  10. MySQL(InnoDB)锁详解