MySQL技术内幕::InnoDB存储引擎

MySQL 体系结构和存储引擎

定义数据库和实例

• 数据库
  • 物理操作系统文件和其他形式文件类型的集合
  • frm、MYD、MYI、ibd 结尾的文件
• 实例
  • MySQL 数据库由后台线程和一个共享内存组成
  • 数据库实例才是真正用来操作数据库的
• 实例与数据库一一对应
• MySQL 数据库实例在系统上的表现就是一个进程
• 配置加载顺序
  • /etc/my.cnf => /etc/mysql/my.cnf => /usr/local/mysql/etc/my.cnf => $HOME/.my.cnf

MySQL 体系结构

• 组成
  • 连接池组建
  • 管理服务和工具组建
  • SQL 接口组件
  • 查询分析器组件
  • 优化器组件
  • 缓冲组件
  • 插件式存储引擎
  • 物理文件
• 存储引擎是基于表的，而不是数据库

存储引擎

• InnoDB 存储引擎
  • 支持事务，面向 OLTP 应用
  • 行锁，外键
  • 支持裸设备
  • 多版本并发控制，四种隔离级别
  • next-key locking 避免幻读
  • 插入缓冲、二次写、自适应哈希索引、预读
  • 采用索引组织表，每张表的存储都是按照转的顺序存放的
• MyISAM 存储引擎
  • 不支持事务
  • 表锁设计
  • 支持全文索引
  • 面向 OLAP
  • 只缓存索引文件
  • MyISAM 存储引擎表由 MYD 和 MYI 组成
    • MYD 存放数据文件
    • MYI 存放索引文件
• NDB 存储引擎
  • 集群存储引擎
  • share nothing 架构
• Memory 存储引擎
  • 表数据放在内存
  • 使用哈希索引
  • 用于存放临时结果集
• Archive 存储引擎
  • 只支持 INSERT 和 SELECT
  • 使用 zlib 压缩数据，有较好的压缩率
  • 适合归档数据
• Federated 存储引擎
  • 指向一台远程 MySQL 数据库服务器上的表
• Maria 存储引擎
  • 目标取代 MyISAM
  • 特性同 InnoDB 存储引擎
• 其他存储引擎
  • Merge
  • CSV
  • Sphinx
  • Infobright

存储引擎比较

• MySQL :: 16 Alternative Storage Engines

连接 MySQL

• TCP/IP
  • 不同机器之间
  • 在客户端和服务器端连接
• 命名管道和共享内存
  • 在同一台机器上
  • 通过 –enable-named-pipe 启用
• UNIX 域套接字
  • 同一台机器上使用
  • 通过 –socket=/etc/mysql.sock 指定套接字文件

InnoDB 存储引擎

InnoDB 存储引擎概述

• 是第一个支持完整 ACID 事务的 MySQL 存储引擎
• 其特点是行锁设计、支持 MVCC、支持外键、提供一致性非锁定读
• 高效利用内存和 CPU

InnoDB 体系架构

• 后台线程
  • 描述
    • 负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据
    • 此外将以修改的数据文件刷新到磁盘文件
    • 同时保证在数据库发生异常的情况下 InnoDB 能恢复到正常运行状态
  • 不同的线程
    • Master Thread
      • 核心后台线程，负责将缓存池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲，UNDO 页的回收等
    • IO Thread
      • InnoDB 中大量使用 AIO 处理请求
      • IO Thread 的工作是负责这些 IO 请求的回调处理
    • Purge Thread
      • 回收已经使用并分配的 undo 页
    • Page Cleaner Thread
      • 将之前版本中脏页的刷新操作放入到单独的线程中完成
• 内存
  • 缓冲池
    • 通过内存的速度弥补磁盘速度较慢对数据库性能的影响
    • 对内容的读取先判断是否在缓冲池中，否则从磁盘读取到缓冲池中，再从缓冲池中读取
    • 对内容的更性操作先在缓冲池中进行，然后通过 checkpoint 机制写入磁盘
    • 缓冲池的大小直接影响数据库的性能
    • 数据页类型
      • 索引页
      • 数据页
      • undo 页
      • 插入缓冲
      • 自适应哈希索引
      • InnoDB 存储的锁信息
      • 数据字典信息
      • 等
  • LRU List、Free List 和 Flush List
    • 数据库中的缓冲池通过 LRU 算法来管理
    • 最频繁使用的页面在最前面，最少使用的在 LRU 列表的尾端
    • 当缓冲池不能存放新读取到的页面时，将首先释放 LRU 列表中尾端的页
    • 优化，最新读区到的页放到列表中间位置，避免新页导致热点页被刷到磁盘
    • LRU 列表从 Free 列表分配页面
    • 可以使用`show engine innodb status 来观察 LRU 列表和 Free 列表的使用情况和状态
    • 非 16KB 的压缩页面通过伙伴算法分配
    • Flush List 用来管理需要被刷回磁盘的脏页
  • redo 日志缓存
    • InnoDB 会把 redo 日志放入这个缓冲区，然后刷到磁盘上
    • 时机
      • Master Thread 每一秒将重做日志缓存刷新到重做日志文件
      • 每个事务提交前会将重做日志缓存刷新到重做日志文件
      • 当重做日志缓冲池小于一半时，重做日志缓存刷新到重做日志文件
  • 额外的内存池
    • 在对一些数据结构本身的内存进行分配时，会从额外的内存池申请。

Checkpoint 技术

• WAL 策略
  • 当事务提交时，先写重做日式，再修改页
  • 当发生宕机事故时，通过重做日志来完成对数据的恢复
• 目标
  • 缩短数据库的恢复时间
    • 当数据库发生宕机时，数据库只需对 Checkpoint 后的重做日志进行恢复
  • 缓冲池日志不够用时，将脏页刷新到磁盘
    • 根据 LRU 算法会移除最近最少使用的页，若此页为脏页，需要强制 Checkpoint，将脏页刷回磁盘
  • 重做日志不够用时，刷新脏页
    • 重做日志被设计成可以循环使用，重做日志可以覆盖已经刷到磁盘的部分
• LSN
  • InnoDB 通过 LSN 来管理版本
  • 每个页有 LSN，重做日志有 LSN，Checkpoint 也有 LSN
  • InnoDB 内部有两种 Checkpoint
    • Sharp Checkpoint
      • 当数据库关闭时，把所有脏页刷新到磁盘
    • Fuzzy Checkpoint
      • Master Thread Checkpoint
      • FLUSH_LRU_LIST Checkpoint
      • Async/Sync Flush Point
      • Dirty Page too mush Checkpoint

Master Thread 工作

• 最高级别线程
• 主循环
  • 每一秒
    • 日志缓冲刷新到磁盘，即使这个事务没有提交
    • 合并插入缓冲
    • 之多刷新 100 个 InnoDB 的缓冲池中的脏页到磁盘
    • 如果没有互动切换到后台循环
  • 每十秒
    • 刷新 100 个脏页到磁盘
    • 合并至多 5 个插入缓冲
    • 将日志缓冲刷新到磁盘
    • 删除无用的 Undo 页
    • 刷新 10 个或 100 个脏页到磁磁盘
• 后台循环
  • 删除无用的 Undo 页
  • 合并 20 个插入缓冲
  • 跳到主循环
  • 不断刷新 100 个页直到符合条件
• 刷新循环
  • 刷新页面到磁盘
• 暂停循环
  • 将 Master Thread 挂起
• Master Thread 根据数据库状态在几个循环之间切换

InnoDB 关键特性

• 插入缓冲
  • InnoDB 存储引擎开创性地设计了 Insert Buffer，对于非聚集索引的插人或更新操作，不是每一次直接插人到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接捅人：若不在，则先放人到一个 Insert Buffer 对象中，好似欺骗。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以-定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，这时通常能将多个插人合并到一个操作中 （因为在一个素引页中），这就大大提高了对于非聚集索引插人的性能。
• 两次写
  • 当发生数据库宕机时，可能 InnoDB 存储引擎正在写人某个页到表中，而这个页只写了一部分，比如 16KB 的页，只写了前 4KB，之后就发生了容机，这种情况被称为部分写失效 〈partial page write)。在 InnoDB 存储引擎未使用 doublewrite 技术前，曾经出现过因为部分写失效而导致数据丢失的情况。
  • doublewrite 由两部分组成，一部分是内存中的 doublewrite buffer，大小为 2MB，另一部分是物理磁盘上共享表空间中连续的 128 个页，即 2 个区 (extent)，大小同样为 2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次，每次 1MB 顺序地写人共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免缓冲写带来的问题。
• 自适应哈希索引
  • InnoDB 存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引 (Adaptive Hash Index，AHI)。AHI 是通过缓冲池的 B+ 树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。
• 异步 IO
  • AIO 不会阻塞读写请求
  • AIO 可以合并多个 IO 为 1 个 IO，提高 IOPS 的性能
• 刷新邻接页
  • InnoDB 存储引擎还提供了 Flush Neighbor Page （刷新邻接页）的特性。其工作原理为：当刷新一个脏页时，InnoDB 存储引擎会检测该页所在区 （extent）的所有页，如果是脏页，那么一起进行刷新。这样做的好处品而易见，通过 AIO 可以将多个 IO 操作合并为一个 IO 操作，故该工作机制在传统机械磁舟下有著显著的优势。

启动、关闭与恢复

• 正常关闭时，下次启动也会很正常
• 非正常退出，下次启动时会进行数据恢复

文件

参数文件

• 告诉 MySQL 实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
• 非必需，可以通过启动参数来配置
• 参数类型
  • 动态参数：可以在运行中进行修改
  • 静态参数：启动时读取，不可修改

日志文件

• 用来记录 MySQL 实例对某种条件做出响应时写人的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
• 错误日志
  • 对 MySQL 的启动、运行、关闭过程进行了记录，也记录一些告警信息和正确的信息，用来排查问题
  • SHOW VARIABLES LIKE 'log_error'
• 慢查询日志
  • 可以在 MySQL 启动时设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询日志中。
  • SHOW VARIABLES LIKE 'log_slow_queries'
  • log_queries_not_using_indexes
    • 如果查询没有使用索引也会被记录
  • mysqldumpslow 可以导出慢 sql
• 二进制日志
  • 记录了堆 MySQL 数据库执行更改的所有操作
  • 作用
    • 恢复(recovery):某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复。
    • 复制(replication):其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库(一般称为 slave 或 standby) 与一台 MySQL 数据库(一般称为 master 或 primary) 进行实时同步。
    • 审计 (audit):用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击
  • 当使用事务的表存储引擎时，所有未提交的二进制日志会被记录到一个缓存中，等到事务提交的时候将缓存中的二进制日志写入二进制日志文件
  • 当写入二进制文件一半时，数据库奔溃，写入的部分不能撤销，可以使用 innodb_support_xa 设置为 1 解决
  • binlog_format 参数
    • STATEMENT 格式和之前的 MySQL 版本一样，二进制日志文件记录的是日志的逻辑 SQL 语句。
    • 在 ROW 格式下，二进制日志记录的不再是简单的 SQL 语句了，而是记录表的行更改情况。基于 ROW 格式的复制类似于 Oracle 的物理 Standby (当然，还是有些区别)。同时，对上述提及的 Statement 格式下复制的问题予以解决。从 MySQL 5.1 版本开始，如果设置了 binlog_format 为 ROW，可以将 InnoDB 的事务隔离基本设为 READ COMMITTED，以获得更好的并发性。
    • 在 MIXED 格式下，MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下会使用 ROW 格式，可能的情况有:
      • 表的存储引擎为 NDB，这时对表的 DML 操作都会以 ROW 格式记录。
      • 使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不确定函数。
      • 使用了 INSERT DELAY 语句。
      • 使用了用户定义函数 (UDF)。
      • 使用了临时表(temporary table)。
    • ROW 格式可以为数据库的恢复和复制带来更好的可靠性，但是会增加二进制的大小，复制的网络开销也会变大。
  • 采用 mysqlbinlog 工具查看

socket 文件

• 当用 UNIX 域套接字方式进行连接时需要的文件。

pid 文件

• MySQL 实例的进程 ID 文件。

MySQL 表结构文件

• 用来存放 MySQL 表结构定义文件。

InnoDB 存储引擎文件

• 因为 MySQL 表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。
• 表空间文件
  • InnoDB 采用将存储的数据按表空间进行存放的设计，每张表都有独立的表空间可以分担负载。
• 重做日志
  • 每个 InnoDB 存储引擎至少有 1 个重做日志文件组，每个文件组下至少有 2 个重做日志文件。
  • 重做日志设置太大恢复需要很长时间，设置太小导致一个事务的日志需要多次切换重做日志文件，还会频繁发生 async checkpoint，造成性能的抖动。
  • 目录结构
    • redo_log_type | space | page_no | redo_log_type
      • redo_log_type 占用 1 字节，表示重做日志的类型
      • space 表示表空间的 ID，但采用压缩的方式，因此占用的空间可能小于 4 字节
      • page_no 表示页的偏移量，同样采用压缩的方式
      • redo_log_body 表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析
  • 为了保证事务 ACID 的持久性，每次提交事务都要保证重做日志已经刷入磁盘

表

索引组织表

• 在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表
• 在 InnoDB 存储引擎表中，每张表都有个主键，如果在创建表时没有显式地定义主键，InnoDB 会优先使用第一个定义的非空唯一索引，否则自动添加一个 6 字节大小的指针。

InnoDB 逻辑存储结构

• 所有数据被存放在一个空间内，称之为表空间
• 表空间又由段、区、页、行组成
• 表空间
  • InnoDB 存储引擎结构的最高层
  • 每张表的表空间只存放数据，其他信息存放在共享表空间
• 段
  • 表空间是由各个段组成的
  • 常见的段有数据段、索引段、回滚段、等
  • 数据段为 B+ 树的非子节点
  • 数据段即为 B+ 树的子节点
• 区
  • 由连续的页组成，在任何情况下每个区的大小都是 1MB
  • InnoDB 一次性申请 4 到 5 个区
  • 一般 InnoDB 引擎存储页的大小为 16KB，一个区有 64 个页，启用压缩页或者调小默认页大小也保持 1MB 总大小
• 页\块
  • 页是 InnoDB 磁盘管理的最小单位
  • 默认 16KB，可以调整为 4KB、8KB、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)
• 行
  • InnoDB 数据是按行存放的

InnoDB 行记录格式

InnoDB 数据页结构

Named File Formats 机制

约束

视图

分区表

索引与算法

InnoDB 存储引擎索引概述

• InnoDB 存储引擎支持以下几种常见的索引
  • B+ 树索引
  • 全文索引
  • 哈希索引
• 人为不能干预存储引擎是否生成哈希索引
• InnoDB 存储引擎以页为单位查找数据

数据结构与算法

• 二分查找法
  • 基本思想：将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将待杳序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。
• 二叉查找树与平衡二叉树
  • 二叉查找树：在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。
  • 平衡二叉树：首先符合二叉树的定义，其次必须满足任何节点的两个子树的高度最大差为 1。

B+ 树

B+ 树索引

Cardinality 值

哈希算法

全文检索

锁

什么是锁

• 锁机制用于管理对共享资源的并发访问
• InnoDB 在数据库内部多处使用锁，从而允许对多种不同资源提供并发访问，提供数据的完整性和一致性。
• 不同的数据库系统实现锁的机制不同

lock 与 latch

• lock 的对象时事务，用来锁定的是数据库中的对象，如表、页、行。lock 多在事务 commit 或 rollback 后释放，有死锁检测机制。
• latch 一般称为闩锁，持续时间非常短，在 InnoDB 引擎中，latch 又可分为 mutex 和 rwlock。其目的是用来保证并发线程操作临界资源的正确性，无死锁检测。

InnoDB 存储引擎中的锁

• 锁的分类
  • 共享锁（S 锁），允许事务读一行数据
  • 排他锁（X 锁），允许事务删除或更新一行数据
  • 只有 SS 是兼容的，兼容是指对同一行记录的锁的兼容性问题
  • InnoDB 支持多粒度锁定，允许事务在行级和表级上的锁同时存在
  • 意向锁
    • 在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁(表级锁)
    • 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
    • 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁
    • 意向锁是表级锁，不会与行级锁发生冲突
• 一致性非锁定读
  • InnoDB 存储引擎通过多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的数据正在执行 DELETE 或者 UPDATE 操作，这时候 InnoDB 不会因此区等待行上的锁释放。相反，InnoDB 存储引擎会去读取行的一个快照数据。
  • 快照数据读取通过 undo 段来实现。
  • 一个行记录可能有多个版本
  • 在 RC 和 RR 的隔离级别下，InnoDB 使用非锁定的一致性读。在 RR 下，读取历史快照，在 RC 下，读取最新的一份快照。
• 一致性锁定读
  • 用户需要显式地对数据库中的读取操作加锁保证数据逻辑的一致性，这需要数据库支持加锁语句
    • SELECT…FOR UPDATE，其他事务不能对该行加锁
    • SELECT…LOCK IN SHARE MODE，可以加 S 锁，不能加 X 锁
• 自增长与锁
  • 自增操作会在执行语句后立即释放锁
  • 自增的列必须是索引，必须是索引的第一列
• 外键与锁
  • InnoDB 会自动给外键列加索引，避免锁表
  • 执行 SELECT 操作时，先给父表加 S 锁，避免数据不一致

锁的算法

• 行锁的三种算法
  • Record Lock：单个行记录上的锁
    • Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会使用隐式的主键来进行锁定
  • Gap Lock：间隙锁，锁定一个范围，但不包括记录本身
  • Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身
    • 唯一索引，命中数据，Next-Key Lock 降级为 Record Lock
    • 唯一索引，未命中数据，Next-Key Lock 退化成间隙锁
• 解决 Phantom Problem
  • Phantom Problem 是指在同一个事务下，连续执行两次同样的 SQL 语句可能导致不同的结果，第二次的 SQL 语句可能返回之前不存在的行。
  • InnoDB 存储引擎采用 Next-Key Locking 算法避免 Phantom Porblem

锁问题

• 脏读
  • 一个事务读到了另一个事务未提交的数据
• 不可重复读
  • 不可重复是一个事务在事务期间读到了其他事物提交的数据
  • InnoDB 中通过 Next-Key Lock 算法来避免不可重复读的问题
• 丢失更新
  • 通过 SELECT…FOR UPDATE 避免该问题
  • 加入版本号，通过乐观锁

阻塞

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

死锁

• 死锁的概念
  • 死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象
  • 解决死锁最简单的方式是超时回滚
  • 用等待图的方式来进行死锁检测，在每个事务请求锁并发生等待时都会判断是否存在回路，如果有死锁，InnoDB 通常选择回滚代价最小的事务进行回滚操作
• 死锁概率
  • 系统中事务的数量越多，发生死锁的概率越大
  • 每个事务操作的数量越多，发生死锁的概率越大
  • 操作数据的集合，越小则发生死锁的概率越大

事务

认识事务

• 概述
  • 事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别与文件系统的重要特征之一
  • ACID
    • 原子性(Atomicity)
      • 原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个 SQL 语句执行失败，已经执行成功的 SQL 语句也必须撤销，数据库状态应该退回到执行事务前的状态
    • 一致性(Consistency)
      • 一致性指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
    • 隔离性(Isolation)
      • 事务的隔离性要求诶个读写事务的对象对其他事物的操作对象能相互分离，即该事务提交前对其他事务都不可见。
    • 持久性(Durability)
      • 事务一旦提交，其结果就是永久的。即使发生宕机等故障，数据库也能将数据恢复。
  • 分类
    • 扁平事务
      • 最简单的一种，其操作是原子性的，要么都执行要么都会滚
      • 扁平事务时应用程序成为原子操作的基本组成模块
      • 主要限制是不能提交和会滚事务的某一部分，或者分几个步骤提交
    • 带有保存点的扁平事务
      • 在扁平事务的基础上，允许事务执行过程中会滚到同一个事务较早的一个状态
      • 保存点在事务内部是递增的，ROLLBACK 不影响保存点的递增
    • 嵌套事务
      • 是一个层次结构框架，由一个顶层事务控制着各个层次的事务，顶层事务之下嵌套的事务被称为子事务，其控制每一个局部的变换
      • 可以用支持保存点的事务模拟
      • 子事务可以时扁平事务也可以是嵌套事务
      • 子事务既可以回滚也可以提交，但是它的提交要到父事务提交后才生效
      • 任何一个事务回滚会引起它所有子事务一起回滚
    • 链事务
      • 在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传递给下一个要开始的事务
      • 提交事务操作和开始下一个事务操作将合并成为一个院子操作
    • 分布式事务
      • 通常是一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

事务的实现

• 概述
  • 原子性、一致性、持久性通过数据库的 redo log 和 undo log 来完成。redo log 称为重做日志，用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。
• redo
  • 基本概念
    • 用来实现事务的持久性
    • 由内存中的重做日志缓冲（易失）和重做日志文件（持久）组成
    • 当事务提交时，必须先将事务的所有日志写入到重做日志文件进行持久化，待事务的 commit 操作完成才算完成
    • 顺序写，使用 fsync 操作保证数据落盘，fsync 效率取决于磁盘性能，间接影响数据库的性能
    • 物理格式日志，记录针对每个页的修改
    • binlog 在提交时写入，redo log 在整个事务执行过程中都在写入
  • log block
    • 以 512 字节的块进行保存，与磁盘扇区大小一致，不需要 doublewrite
    • 由日志块头（12 字节），日志块内容（492 字节），日志块尾（8 字节）组成
  • log group
    • 重做日志组，其中有多个重做日志文件
    • 存储的就是之前的 log buffer 中保存的 log block，块大小为 512 字节
    • 写盘时机
      • 事务提交时
      • 当 log buffer 中有一半的内存空间已经被使用
      • 当 log checkpoint 时
    • 通过 round-robin 的方式写入文件
    • 每个 log group 的第一个文件的前 2kb 保存文件的元信息，不写入内容
    • log file header 的后面部分是 InnoDB 保存的 checkpoint 值，其设计是交替写入的，避免因为介质失败找不到可用的值
  • redo log 格式
    • redo_log_type | space | page_no | redo_log_type
      • redo_log_type 占用 1 字节，表示重做日志的类型
      • space 表示表空间的 ID，但采用压缩的方式，因此占用的空间可能小于 4 字节
      • page_no 表示页的偏移量，同样采用压缩的方式
      • redo_log_body 表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析，根据不同的类型会有不同的内容
  • LSN
    • Log Sequence Number 的缩写，代表日志序列号，单调递增
    • 含义
      • 重做日志写入的总量
        • 例如当前重做日志的 LSN 为 1000，有一个事务 T1 写入了 100 字节的重做日志，那么 LSN 就变为了 1100，若又有事务 7 写人入 200 字节的重做日志，那么 LSN 就变为了 1300。可见 LSN 记录的是重做日志的总量，其单位为字节。
      • checkpoint 的位置
      • 页的版本
        • 页头部的 FIL_PAGE_LSN 记录了该页的 LSN
        • 用来判断是否需要进行恢复操作
        • 如果重做日志日志中的 LSN 大于页的 LSN，并且事务已经提交则需要进行恢复操作
  • 恢复
    • 由于 checkpoint 表示已经被刷到磁盘上的 LSN，因此在恢复过程中仅需恢复 checkpoint 开始的日志部分
    • 当数据库在 checkpoint 为 LSN 10000 时宕机器，只需恢复到 10000 到 13000 的数据
• undo
  • 基本概念
    • 记录了事务的行为，用来把数据恢复回去
    • 当 InnoDB 存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个 INSERT， InnoDB 存储引擎会完成一个 DELETE；对于每个 DELETE, InnoDB 都会执行一个 INSERT；对于每一个 UPDATE InnoDB 引擎会执行一个相反的 UPDATE，将修改前的行放回去
    • undo 的另一个作用时 MVCC，InnoDB 存储引擎中的 MVCC 通过 undo 实现的
    • undo log 也会产生 redo log，undo log 也需要持久化的保护
  • undo 存储管理

事务控制语句

• STARTTRANSACTION |BEGIN: 显式地开启一个事务。
• COMMIT : 要想使用这个语句的最简形式，只需发出 COMMIT。也可以更详细一些，写为 COMMIT WORK，不过这二者几乎是等价的。COMMIT 会提交事务，并使得已对数据库做的所有修改成为永久性的。
• ROLLBACK : 要想使用这个语句的最简形式，只需发出 ROLLBACK。同样地，也可以写为 ROLLBACK WORK，但是二者几乎是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier : SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT。
• RELEASE SAVEPOINT identitier : 删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个异常。
• ROLLBACK TO [SAVEPOINT] identifier: 这个语句与 SAVEPOINT 命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。例如可以发出两条 UPDATE 语句，后面跟一个 SAVEPOINT，然后又是两条 DELETE 语句。如果执行 DELETE 语句期间出现了某种异常情况，并且捕获到这个异常，同时发出了 ROLLBACK TO SAVEPOINT 命令，事务就会回滚到指定的 SAVEPOINT，撤销 DELETE 完成的所有工作，而 UPDATE 语句完成的工作不受影响。
• SET TRANSACTION : 这个语句用来设置事务的隔离级别。InnoDB 存储引你提供的事务隔离级别有: READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

隐式提交的事务

• 以下这些 SQL 语句会产生一个隐式的提交操作，即执行完这些语名后，会有一个隐式的 COMMIT 操作。
  • DDL 语句 : ALTER DATABASE…UPGRADE DATA DIRECTORY NAME，ALTER EVENT，ALTER PROCEDURE，ALTER TABLE，ALTER VIEW，CREATE DATABASE，CREATE EVENT，CREATE INDEX，CREATE PROCEDURE，CREATE TABLE，CREATE TRIGGER，CREATE VIEW，DROP DATABASE，DROP EVENT，DROP INDEX，DROP PROCEDURE，DROP TABLE，DROP TRIGGER，DROP VIEW，RENAME TABLE，TRUNCATE TABLE。
  • 用来隐式地修改 MySQL 架构的操作 : CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。
  • 管理语句，ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE。

对于事务操作的统计

• TPS = (com_commit + com_rollback) / time

事务的隔离级别

• 四个隔离级别
  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

分布式事务

• MySQL 分布式事务
  • XA 事务由一个或多个资源管理器、一个事务管理器以及一个应用程序组成。
    • 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
    • 事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
    • 应用程序：定义事务的边界，指定全局事务中的操作。
  • 分布式事务使用两段式提交 （two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备 （PREPARE)，告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行 ROLLBACK 还是 COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的 PREPARE 操作，待收到所有节点的同意信息后，再进行 COMMIT 或是 ROLLBACK 操作。
• 内部 XA 事务
  • 最为常见的内部 XA 事务存在于 binlog 与 InnoDB 存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了 binlog 功能。在事务提交时，先写二进制日志，再写 InnoDB 存储引擎的重做日志。对上述两个操作的要求也是原子的，即二进制日志和重做日志必须同时写人。若二进制日志先写了，而在写人 InnoDB 存储引擎时发生了宕机，那么 slave 可能会接收到 master 传过去的二进制日志并执行，最终导致了主从不一致的情况
  • MysQL 数据库在 binlog 与 InnoDB 存储引擎之间采用 XA 事务。当事务提交时，InnoDB 存储引攀会先做一个 PREPARE 操作，将事务的 xid 写人，接着进行二进制日志的写人.如果在 InnoDB 存储引擎提交前，MysQL 数据库宕机了，那么 MySQL 数据库在重启后会先检查准备的 UxID 事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。

不好的事务习惯

• 在循环中提交
• 使用自动提交
• 使用自动回滚

长事务

• 对于长事务的问题，有时候可以转换为小批量的事务来进行