MySQL2 | SkySea-GaoMing

参考

《MySQL技术内幕》

MySQL体系结构

MySQL是一个单进程多线程架构的数据库，Oracle是一个多进程数据库，MySQL
实例在系统上的表现就是一个进程。注意数据库与数据库实例不是一个概念

数据库物理操作系统文件或其他形式文件类型的集合
数据库实例是程序，位于用户与操作系统之间的一层数据管理软件，用户对
数据库的任何操作都是在数据库实例下进行的

下图是MySQL的体系结构

MySQL 数据库区别与其他数据库的最重要特点就是插件式表存储引擎，注意存储
引擎是基于表而不是基于数据库，不同的表可以使用不同的存储引擎。而Oracle
只有一种存储引擎，所有数据存储管理机制都是一样的

存储引擎

MySQL数据库是开源的，用户可以编写自己的存储引擎也可使使用官方存储引擎
，数据库存储引擎是数据库底层软件组织，不同的存储引擎提供不同的存储机
制，索引技巧、锁定水平等功能。接下来看一下Mysql支持的存储引擎

InnoDB

InnoDB是事务型存储引擎，支持事务安全表（ACID）、行锁定和外键。
实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）在可重
复读隔离级别下，通过多版本并发控制(MVCC)+Next-Key Locking防止幻影读。
主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询
性能有很大的提升。
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并
且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要
停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引
以加快数据的获取
支持外键完整性约束。存储表中的数据时，每张表的存储都按主键顺序存放
，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的
ROWID，并以此作为主键

MyISAM

MyISAM 基于 ISAM 的存储引擎，并对其进行扩展。它是在Web、数据存储和其
他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，
但不支持事务，不支持行级锁只有表级锁

支持全文索引
使用表级锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往
表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）
主机宕机后，MyISAM表易损坏，灾难恢复性不佳
可以配合锁，实现操作系统下的复制备份、迁移
只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的
系统调用且效率不佳
数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
可以把数据文件和索引文件放在不同目录

使用MyISAM创建数据库时将产生三个文件，文件的名字以表的名字开始，扩展
名指出文件类型：frm文件存储表定义，数据文件的扩展名为.MYD，索引文件
的扩展名为.MYI

InnoDB和MyISAM的比较

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁
外键：InnoDB 支持外键
备份：InnoDB 支持在线热备份
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复
的速度也更慢
其它特性：MyISAM 支持压缩表和空间数据索引

MEMORY

MEMORY 存储引擎将表中的数据存储在内存中，为查询和引用其他表数据提供快
速访问。默认使用哈希索引

使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈
只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪
费空间
不支持 TEXT 、 BLOB 字段。当有些查询需要使用到临时表（使用的也是
MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁
盘的MyISAM表，严重降低性能
由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，
可通过清除数据或调整内存表参数来避免报错
服务器重启后数据会丢失，复制维护时需要小心

存储引擎的选择

如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要
求实现并发控制，InnoDB 是个很好的选择。
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可
以将数据保存在内存中的 Memory 引擎。MySQL 中使用该引擎作为临时表，
存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive存储引
擎支持高并发的插入操作，但是本身并不是事务安全的。Archive引擎非常适
合存储归档数据，如记录日志信息可以使用 Archive引擎

InnoDB

InnoDB存储引擎是OLTP（在线事务处理）应用中核心表的首选存储引擎

体系结构

InnoDB存储引擎有多个内存块，这些内存块组成了一个大的内存池。负责如下
工作

维护所有进程/线程需要访问的多个内部数据结构
缓存磁盘上的数据，方便快速读取，同时在对磁盘文件的数据修改之前在这
里缓存
重做日志缓存

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

后台线程

Master Thread 将缓冲池中的数据异步刷新到磁盘，保证数据的一致性
，包括脏页的刷新、合并插入缓冲、UNDO页的回收等，异步刷新是刷新部分
内容，同步刷新是刷新全部内容。内存数据页与磁盘数据页不一致时内存
数据页就叫脏页，如果一致就是干净页
IO Thread 在InnoDB存储引擎中大量使用AIO(Async IO)来处理写IO请
求。IO Thead的工作就是负责这些IO请求的回调(call back)处理
Purge Thread 事务被提交后，其所使用的undolog可能不再需要，因此
需要Purge Thread来回收已经使用并分配的undo页
Page Cleaner Thread 将之前版本中脏页的刷新操作都放入单独的线程
中完成，减轻Master Thread的工作以及用户查询线程的阻塞

内存

InnoDB存储引擎是基于磁盘存储的，其中的记录会按照页的方式进行管理。缓
冲池是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影
响，在数据库中进行读取页的操作，首先将磁盘读取到的页放在缓冲池中，这
个过程称为将页”FIX”在缓冲池中，下一次读取页时先判断缓冲池中是否有页
，如果有则直接读取否则从磁盘读取。对于数据库中页的修改操作，首先修改
在缓冲池中的页，然后再以一定频率刷新到磁盘上，注意页从缓冲池刷新到
磁盘并不是每次修改都刷新，而是通过Checkpoint机制刷新回磁盘。
由此可知缓冲池的大小直接影响数据库的整体性能，缓冲池的配置可以通过
参数innodb_buffer_pool_size来设置

1	show variables like 'innodb_buffer_pool_size'\G

InnoDB内存数据对象如下图
从InnoDB 1.0x版本开始允许有多个缓冲池实例，每个页根据哈希值平均分配
到不同缓冲池实例中，这样做是为了减少数据库内部的资源竞争，增加数据库
的并发处理能力

LRU

数据库的缓冲池是通过LRU算法进行管理，即最频繁的页在LRU列表的前端，而
最少使用的页在LRU列表的尾端，当缓冲池不能存放新读到的页时，首先释放
LRU列表中尾端的页。页的大小默认16KB，InnoDB的存储引擎对LRU算法进行
了优化，在LRU列表中加入midpoint装置，新读取到的页虽然是最新访问的
，但并不是直接放到LRU的首部，而是放到LRU列表的midpoint位置。这个
算法在InnoDB存储引擎下称为midpoint insertion strategy。

1 2	#默认情况下这个位置在LRU列表长度的5/8处 show variables like 'innodb_old_blocks_pct'\G

把midpoint之后的列表称为old列表，之前的列表称为new列表。优化的目的是
防止某些操作可能使缓冲池中的页被刷新出。InnoDB还引入了另一个参数管理
LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位
置后需要等待多久才会被加入到LRU列表的热端

1	set global innodb_old_blocks_time=1000;

LRU列表用来管理已经读取到的页，但当数据库刚启动时LRU列表是空的，即
没有任何页。这时页都存放在Free 列表中，当需要从缓冲池中分页时，首先
从Free 列表中查找是否有可用的空闲页，若有则将该也从Free列表中删除，
放入LRU列表中。LRU列表的页被修改后就是脏页，Flush list即为脏页列表
分页的意思是每页显示多少条数据

重做日志缓冲

InnoDB 存储引擎的内存区域除了缓冲池还有重做日志缓冲，InnoDB存储引擎
首先将重做日志信息放入缓冲区中，然后按照一定频率刷新到重做日志文件，
以下操作会将重做日志缓冲的内容刷新到外部磁盘的重做日志文件中

Master Thread每一秒将重做日志缓冲刷新到重做日志文件
每个事务提交时
重做日志缓冲池空间小于1/2

额外的内存池

当对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，如
果该区域内存不够会从缓冲池中申请

Checkpoint

每次执行 update、delete 等语句更改记录时，缓冲池中的页与磁盘不一致，但
是缓冲池的页不能频繁刷新到磁盘中，而且当刷新时发生宕机那么数据就不能恢
复，因此增加了write ahead log策略。当事务提交时先写重做日志，再修改内
存页。当发生宕机时通过重做日志来恢复，这也是ACID中的D的要求。如果缓冲
池和重做日志足够大，那么宕机时完全可以通过重做日志恢复数据，但这需要
两个前提

缓冲池可以缓存数据库中所有数据
重做日志可以无限大

这两个前提都不现实，Checkpoint技术的目的是解决如下问题

缩短数据库恢复时间
缓冲池不够用时，将脏页刷新
重做日志不可用时，刷新脏页

当宕机时不需要重做所有日志，因为checkpoint之前的页都已刷新到磁盘，只
需要对checkpoint后的重做日志进行恢复。在InnoDB存储引擎内部，有两种
Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘
Fuzzy Checkpoint 数据库在运行时只刷新一部分脏页，而不是刷新所有
的脏页回磁盘

Master Thread以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例
的页回磁盘，这个过程是异步的。FLUSH_LRU_LIST Checkpoint检查LRU列
表中是否有足够的可用空间发送在用户查询线程中，如果没有100个可用空
闲页那么会将LRU列表尾端的页溢出，如果有脏页则会进行Checkpoint。
Async Flush Checkpoint指重做日志文件不可用时需要强制将一些页
刷新回磁盘。最后一种Checkpoint的情况是Dirty Page too much，
即脏页的数量太多，将强制Checkpoint

Master Thread

InnoDB存储引擎的主要工作都是在一个单独的后台线程Master Thread中完成
的，内部由多个循环组成：主循环、后台循环、刷新循环、暂停循环。主循环有
两大部分操作–每秒操作和每10秒操作，每秒操作包括

日志缓冲刷新到磁盘，即使这个日志还没有提交（总是）
合并插入缓冲（可能）
至多刷新100个脏页（可能）
如果当前没有用户活动则切换到background loop（可能）

每10秒的操作如下

日志缓冲刷新到磁盘
合并至多5个插入缓冲（总是）
刷新100个脏页（可能）
删除无用的Undo页（总是）

如果当前没有用户活动或者数据库关闭，就会切换到这个循环，background loop
会执行如下操作

删除无用的undo页
合并20个插入缓冲
跳回主循环
不断刷新100个页直到符合条件

InnoDB关键特性

插入缓冲
两次写
自适应哈希索引
异步IO
刷新邻接页

插入缓冲

若插入按照聚集索引primary key插入，页中的行记录按照primary存放，一般情
况下不需要读取另一个页记录，插入速度很快。一张表中会存在多个非聚集的辅助
索引，插入操作时依然按照主键的顺序存放，但是对于非聚集索引叶子节点的插
入不再是顺序的，这时就需要离散访问非聚集索引页。InnoDB存储引擎开创性设
计了Insert Buffer，对于非聚集索引的插入或更新，并不是每一次直接插入
到索引页中，而是先判断缓冲池中是否有要插入的非聚集索引页，如果有则直
接插入，否则放入Insert Buffer对象中，然后再以一定的频率和情况进行和
辅助索引页子节点的merge，这时通常能将多个插入合并到一个操作中，这样
就提高非聚集索引插入的性能，使用Insert Buffer需要两个条件

索引是辅助索引
索引不是unique

两次写

double write带给InnoDB存储引擎的是数据页的可靠性。当数据库宕机时，若
InnoDB将页写到表中只写了一部分，称为部分写失效，这种情况无法通过重做
日志恢复，重做日志记录的是对页的物理操作，如果页本身已经损坏那么重做
无意义，在重做之前用户需要一个页的副本，当写入失效时，先通过页的副本
还原该页，然后重做，这就是double write

自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立索引可以带来
速度上的提升，则建立哈希索引，称之为自适应哈希索引AHI。AHI是根据缓冲
池的B+树页构造而来，建立速度很快，而且不需要对整张表构建哈希索引。
Innodb根据访问频率对热点页建立哈希索引，AHI的要求是对页面的访问模式必
须一样，如连续使用where a=’xxx’访问了100次。建立热点哈希后读取速度可
能能提升两倍，辅助索引连接性能提升5倍

异步IO

同步IO的意思是每进行一次IO操作，需要等待此次操作结束才能继续接下来的
操作，如果用户进行索引扫描的查询，可能需要扫描多个页，那么同步IO只能
等一个页扫描完后再进行下一次扫描，异步IO就是说用户在发送一个IO请求后
能立即发送下一个IO请求，这就是AIO。AIO的另一个优势是可以进行IO merge
操作

刷新邻接页

当刷新一脏页时，同时检测所在区(extent)的所有页，如果有脏页则一并刷新

文件

接下来分析一下构成MySQL数据库和InnoDB存储引擎表的各种类型文件

参数文件告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某
些初始化参数，这些参数定义了某种内存结构的大小等设置
日志文件用来记录MySQL实例对某种条件做出相应时写入的文件
socket文件当用UNIX域套接字方式进行连接时需要的文件
pid文件 MySQL实例的进程ID文件
MySQL表结构文件用来存放MySQL表结构定义文件
存储引擎文件每个存储引擎都有自己的文件来保存各种数据，存储记录
和索引等数据

参数文件

MySQL实例启动时，数据库会先读一个配置参数文件，用来寻找数据库的各种
文件所在位置以及指定某些初始化参数

日志文件

日志文件记录了影响MySQL数据库的各种类型活动

错误日志
二进制日志
慢查询日志
查询日志
查询日志

错误日志

该文件记录所有的错误信息，也包括一些警告信息或正确的信息

慢查询日志

慢查询日志用来帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化

查询日志

记录所有对MySQL数据库请求的信息，默认文件名为主机名.log

二进制日志

二进制文件记录了对MySQL数据库执行的更改操作，但是不包括SELECT和 SHOW
这类操作。因为这类操作对数据本身没有修改，然而，若操作本身并没有导致数
据库发生变化，那么该操作也会写入二进制

套接字文件

本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字文件

pid文件

MySQL实例启动时会将自己的进程ID写入pid文件

表结构定义文件

每个表都有与之对应的文件，与存储引擎无关，后缀名为frm，该文件是文本文件

存储引擎文件

每个表存储引擎都有自己独有的文件，包括表空间文件和重做日志文件

表空间文件

InnoDB采用将存储的数据按表空间进行存放的设计，在默认配置下会有一个
10MB大小的文件，名为ibdata1文件，该文件就是默认的表空间文件，单独
的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息
还是存放在默认的表空间中

重做日志文件

MySQL默认初始化ib_logfile0、ib_logfile1两个重做日志文件，当实例
或介质失败，重做日志文件就能派上用场。如果数据库主机掉电介质失败，
InnoDB存储引擎会使用重做日志恢复到掉电前的水平，以此来保证数据的
准确性。也正是因为重做日志的存在，才使得InnoDB存储引擎可以提供可
靠的事务。每个InnoDB存储引擎都至少有1个重做日志文件组，每个文件
组至少有两个重做日志文件
重做日志缓冲写入磁盘时是按512个字节也就是一个扇区的大小进行写入，
因为扇区是写入的最小单位，因此可以保证写入必定是成功的。所以不需要
两次写

二进制文件和重做日志文件的不同

二进制文件记录的是所有与MySQL数据库有关的日志记录，包括各种存储引擎
，而重做日志文件记录的是与该存储引擎本身的事务记录。其次记录的内容不
同，二进制文件记录的是关于一个事务的具体操作内容，即逻辑日志，而重
做日志记录的是每个页更改的物理情况。写入的时间也不同，二进制日志文
件仅在事务条件前进行提交，只写磁盘一次。而在事务进行中不断有重做日
志被写入磁盘

MySQL索引

参考 https://www.cnblogs.com/boothsun/p/8970952.html#autoid-7-1-0
http://blog.codinglabs.org/articles/theory-of-mysql-index.html

索引的选择

这个样例展示了一种可能的索引形式，左边是数据表，最左边是数据记录的物理
地址，右边是一个二叉查找树，每个节点都包含一个索引键值和一个指向对应数
据记录物理地址的指针，这样就可以在O(logN)的时间复杂度内查找数据，但是
数据库系统并没有使用二叉树或红黑树来实现

B树

B树也称为B-Tree，是一种平衡的多叉查找树，最多可以开m个叉，也称为m阶b树

一棵B树一般需要满足以下条件

每个节点至多可以拥有m棵子树
根节点至少有2个节点或者只有一个根节点
非根非叶的节点至少有的Ceil(m/2)个子树（向上取整）
非叶节点中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，其中n表示该节点中
保存的关键字个数，K为关键字且Ki<Ki+1，A为指向子树根节点的指针
从根到叶子的每一条路径都有相同的长度，也就是说，叶子节在相同的层
，并且这些节点不带信息，实际上这些节点就表示找不到指定的值，也就是
指向这些节点的指针为空

B树和查找过程和二叉查找树类似，从根节点依次比较每个节点，因为每个节点的
关键字和左右子树都是有序的，所以可以快速找到要查找的关键字，比如现在要
查询k

从根节点P开始，K的位置在P之前，进入左侧指针
左子树中，依次比较C、F、J、M，发现K在J和M之间
沿着J和M之间的指针，继续访问子树，并依次进行比较，发现第一个关键字K
即为指定

伪算法代码如下

BTree_Search(node, key) {
    if(node == null) return null;
    foreach(node.key)
    {
        if(node.key[i] == key) return node.data[i];
            if(node.key[i] > key) return BTree_Search(point[i]->node);
    }
    return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

B+树

B+树是B树的加强版

有n棵子树的节点有n个关键字
所有的关键字都是存储在叶子节点上，且叶子节点本身根据关键字自小而
大顺序连接，这里并不是说只有叶子节点才有关键字，而是说所有叶子节点
的关键字包含了所有关键字
非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大
（或最小）关键字

B+树的查找过程与B树类似，但是查找时当在非叶子节点的关键字等于给定值
并不终止查找，而是沿着指针直到叶子节点的位置，因此B+树不管查找成功
与否都会有一条从根节点到叶子节点的路径

带有顺序访问的B+树

一般在数据库系统或文件系统都使用的B+树结构都在经典的B+树基础上进行
了优化，增加了顺序访问指针
在B+树的每个叶子节点增加了一个指向相邻叶子节点的指针，就形成了带有
顺序访问指针的B+Tree。这个优化的目的是提高区间访问的性能，比如要查
找18到49的数据，当找到18后只需要通过指针访问相邻叶子节点就可以一
性得到所有数据

索引底层原理

之前已经讲过数据库系统和文件系统使用B树或B+树而不是红黑树作为索引，
接下来从计算机组成原理来讲解为什么使用B树或B+树作为索引的理论基础。
一般来说索引本身也很大不可能全部存储在内存中，因此索引常常以索引文
件的形式存储在磁盘上，学过计算机系统都知道索引查找过程通过磁盘I/O
比通过内存读取数据要慢几个数量级，所以评价一个数据结构作为索引的
优劣最重要的指标是在查找过程中磁盘I/O操作次数的渐进复杂度，也就
是说要想优化的话就必须尽量减少查找过程中磁盘I/O的存取次数

主存存储原理

目前计算机使用的主存都是随机读写存储器（RAM）
从抽象角度看，主存就是一系列存储单元组成的矩阵，每个存储单元都储存
固定大小的数据并有唯一的地址，这里简化为一个二维地址：通过一个行地
址和一个列地址可以确定一个唯一的存储单元

主存的读取过程

当系统需要读取主存时会将地址信号放到地址总线上传给主存，主存读取到
地址信号后，解析信号并读取到指定存储单元，然后将存储单元数据放到数
据总线上，写主存的过程与读取主存类似，系统将要写入单元地址和数据分
别放到地址总线和数据总线上，主存读取两个主线的内容并进行写操作，由
此可知主存存取时间仅与存取次数呈线性关系，数据的位置不影响时间

磁盘存取原理

之前已经说过索引以文件的形式一般存储在磁盘上，索引检索需要磁盘I/O
的操作，与主存不同磁盘存在机械运动耗费因此时间耗费巨大
关于磁盘的知识我在计算机系统中讲解过，这里就不讲解了。重点要说明的
是当从磁盘读取数据时系统会将数据逻辑地址传给磁盘，磁盘的控制电路
按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道
、哪个扇区。为了读取这个扇区的数据，需要将磁头放在这个扇区的上方，
磁头需要移动对准相应磁道，这个过程叫做寻道，耗费时间就是寻道时间
然后磁盘旋转将目标扇区转移到磁头下，这个过程耗费的时间是旋转时间

局部性原理与磁盘预读

之前的讲解已经知道磁盘比内存要慢很多，要想提高效率必须减少磁盘的
I/O ，为了达到这个目的磁盘往往不是严格按需读取，而是每次都会预读
，即使只需要一个字节，磁盘也会从这个位置开始顺序向后读取一定长度
的数据放入内存，这个理论基础就是局部性原理：当一个数据被用到其附
近的数据也通常会被马上使用。所以程序运行期间需要的数据通常应当比
较集中。预读的长度一般为页的整数倍，页是计算机管理存储器的逻辑块
，硬件和操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，
每个存储块称为一页（通常是4k），主存和磁盘以页为单位交换数据，
当程序要读取的数据不在主存中，会触发一个缺页异常，此时系统会向
磁盘发出读盘信号，磁盘会找到数据的存储位置并向后连续读取多个页
载入内存中

索引的性能分析

先从B树分析，根据B树的定义，检索一次最多需要访问h个节点，h 就是这棵
B树的高度，数据库系统的设计者巧妙利用磁盘预读原理，将一个节点的大小
设为等于一个页，这样每个节点只需要一次I/O 就可以完全载入，为此还需
要进行如下操作：每次新建节点时直接申请一个页的空间，这样就保证一个
节点物理上也存储在一个页里，而且计算机存储分配都是按页对齐的，就实
现了一个node只需要一次I/O。B树中一个检索最多只需要h-1次 I/O，根节
点是存储在内存中的，渐进时间复杂度是 O(h)=O(logdN)，d是树的度。如
果使用红黑树的话h会深很多，而且逻辑上很近的节点（父子）物理上可能
很远，无法利用局部性

索引实现

在MySQL中索引是存储引擎级别的，不同的存储引擎对索引的实现方式不同，
接下来讨论MyISAM和InnoDB两个存储引擎的索引实现方式

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放数据记录的
地址
这里设表一共有三列，假设以Col1为主键，则上图是一个MyISAM表的主索引
示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中主
索引和辅助索引结构上没有区别，区别在于主索引要求key是唯一的，而辅
助索引的key可以重复，如果在Col2建立一个辅助索引，则此索引的结构
如下
MyISAM索引方式也叫非聚集的，这是为了与InnoDB的聚集索引区分

InnoDB索引实现

InnoDB也使用 B+树作为索引结构，当具体实现方式却与MyISAM截然不同。第
一个区别是InnoDB的数据文件本身就是索引文件，MyISAM的索引文件和数据
文件是分离的，索引文件仅仅保存数据的地址，而在InnoDB中表数据文件本
身就是按照B+树组织的索引结构，这棵树的叶子节点的data域保存了完整的
数据记录，这个索引的key就是数据表的主键，因此InnoDB表的主键本身就
是主索引
由上图可知叶子节点包含了完整的数据记录，这种索引叫做聚集索引。因为
InnoDB的数据文件本身要求按照主键聚集，所以InnoDB要求必须有主键，
如果没有显示指定则MySQL会自动选择一个可以唯一标识数据记录的列作为
主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作
为主键，这个字段长度为6个字节，类型为长整型。第二个区别是InnoDB
的辅助索引data域存储相应记录主键的值而不是地址，有就是说InnoDB
的所有辅助索引都引用主键作为data域
这里是使用ASCLL比较key，聚集索引这种实现方式使得主键的搜索十分高效
，如果使用辅助索引的话需要检索两遍：第一遍就是通过辅助索引找到获取
主键，第二遍就是通过主键到主索引中检索获得记录。了解InnoDB的索引实
现后就知道不应该使用过长的字段作为主键，因为所有辅助索引都使用主索
引，过长的主索引会令辅助索引变得过大，另外最好不要用非单调的字段作
为主键，单调的意思就是严格递增或递减，因为InnoDB 数据文件本身就是
一棵B+树，非单调的主键插入时数据文件会为了维持B+树的特性而频繁地
分裂调整，十分低效，所以推荐使用自增字段

聚集索引和非聚集索引

是否是聚集索引取决于数据跟索引是否放在一起。innodb只能有一个聚集
索引，向innodb插入数据时必须要有包含一个索引的key值。innodb存储
引擎中数据是与索引放在ibd文件中，如果一个表有多个聚集索引那么就
会存在数据冗余的问题，可以有多个非聚集索引。MyISAM没有聚集索引。
MyISAM存储引擎中数据放在MYD文件，索引放在MYI文件中，索引对应处
存放的是另一个数据文件中对应数据的地址。
先根据普通索引查询到聚集索引的key值，然后根据key值在聚集索引中
查询到对应的行记录，这就是回表。举个栗子

id是聚集索引，name是普通索引，gender没有索引
select * from stu where name=zhangsan
这条语句需要查两个B+树，先在name所在的B+树中找到聚集索引的值，
然后在聚集索引所在的B+树中找到整行数据
select id,name from stu where name=zhangsan
这条语句只需要查一个B+树，通过name可以直接查到id和name两个列的
值，不需要从聚集索引中查任何数据，这就叫做索引覆盖

还有一个问题是为什么主键应该自增，事实上向表中插入数据时B+树的
节点会存在分裂或合并，比较消耗性能，如果主键自增插入B+树的话只
需要在叶子节点进行累积即可，不涉及节点的分裂合并。

索引使用及优化

MySQL的优化主要分为结构优化和查询优化，下图是一个官方给出的数据库

最左前缀原理及优化

高效使用索引的前提是知道什么样的查询会使用索引，这与最左前缀原理
有关，一般来说索引只使用一个列实际上也可以按照一定顺序使用多个列
，这种索引叫做联合索引，主键也可以由多个列组成，叫做联合主键。
以employees.titles表为例
titles表的主索引为<\emp_no,title,from_date>，辅助索引为emp_no

全列匹配
当按照索引中的所有列进行精确匹配(= in)时，索引可以被用到，MySQL的
查询优化器会自动调整where子句的条件顺序以使用适合的索引
最左前缀匹配

以上的例子有点复杂，举个简单的例子

id是主键，name和age是组合索引
select * from stu where name='ff' and age=11
符合最左匹配原则
select * from stu where name='ff'
符合最左匹配原则
select * from stu where age=11
不符合最左匹配原则
select * from stu where age=11 and name='ff'
符合最左匹配原则，优化器会改变列的顺序

索引下推

MySQL可以分为三层架构，第一层架构是client，第二层架构是server
，第三层是存储引擎。client 就是可视化的一些工具，server 是程序
开始运行后所提供的服务，数据是放在存储引擎中。所以会涉及server
与磁盘之间的交互

select * from stu where name='ff' and age=11
没有索引下推之前先根据name从存储引擎中获取符合规则的数据，
然后在server层对age进行过滤
有索引下推之后根据name,age两个条件从存储引擎获取对应的数据，
也就是说原来需要在server层进行的操作全部放到存储引擎中完成，
减少了server层和存储引擎层的交互量，这样可以提高性能

一条查询语句的背后

参考 https://www.infoq.cn/article/PKzT75BPcryCYJ_VuWrR
https://my.oschina.net/u/4391973/blog/3237509
当输入一条sql查询语句MySQL内部的执行过程是怎样的

1	select * from tableName where id=1;

客户端通过连接器连接到MySQL服务器
连接器通过数据库权限身份验证后会先查询数据库缓存是否存在（之前执
行过相同条件的SQL查询），如果有就直接返回缓存中的数据，没有的话就会
进入分析器
进入分析器后会对查询语句进行词法语法的分析，判断该SQL语句是否存
在语法错误，如果存在查询语法词法错误会直接返回给客户端错误，不存在
的话就进入优化器
优化器会对查询语句进行优化处理，如果一条语句用了多个索引就会判断
那个索引性能最好
最终会进入执行器，开始执行查询语句直到查询出满足条件的所有数据

大体可以把MySQL分为Server层和存储引擎两部分，可以用一张图来展示这
个过程

存储引擎

负责数据的存储和提取，目前的版本MySQL默认使用InnoDB，创建table的
时候可以通过 engine=memory来指定存储引擎

Server层

包括连接器、分析器、查询缓存、优化器、执行器等

连接器

连接器的主要工作就是跟客户端建立连接、获取权限、维持和管理连接

-- $ip: 服务器IP
-- $port: MySQL端口号
-- $user: 用户名
mysql -h$ip -P$port -u$user -p

如果用户名或密码不对则会收到一个 Access denied for user错误，客
户端程序结束执行
如果用户名和密码正确，连接器会从权限表查询用户拥有的权限，这个链
接里面的权限逻辑判断全都依赖此时读到的权限，也就是说当建立连接后即
使管理员对权限做修改也不会立刻生效，只有重新建立链接才会使用新的权
限设置

查询缓存

建立连接成功后执行SQL前会先查询缓存，查看是否之前执行过这条语句，之
前的执行语句很可能以key-value的形式直接缓存在内存中，key是查询语句
value 是查询结果，如果能在缓存中找到相应的 key，则直接返回对应的
value，如果语句不再缓存中就会继续后面的执行操作，然后把结果存入
查询缓存中

分析器

分析器首先进行词法分析，比如从输入的select可知这是一个查询语句，然
后还要识别出表名和列字段。词法分析结束后还要进行语法分析，如果不符
合MySQL的语法规则会提示 You have an error in your SQL syntax

优化器

优化器是在表里有多个索引的时候，决定使用哪个索引。或者在一个语句有
多个表关联的时候决定各个表的连接顺序，比如以下这条语句

1	select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

可以先从t1中取出c=10的ID值然后再根据ID值关联t2，也可以先从t2取值，
不同的顺序执行效率不同，优化器会决定选择哪种方案

执行器

开始执行的时候首先判断是否有操作这个表的权限，如果没有权限就会返回
没有权限的错误，如果有权限就会继续执行，根据这个表的引擎定义使用这
个引擎提供的接口，比如这个例子中ID是没有索引的，那么执行流程如下

调用这个引擎的接口取这个表的第一行，判断ID是否为10，如果不是则
跳过否则将结果存入结果集中
调用引擎接口取下一行，重复上述操作
将结果集返回给客户端

事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务
，也可以使用 Rollback 进行回滚。事务会把数据库从一种一致性状态转
换到另一种一致性状态。ACID四个特性如下

原子性 Atomicity 原子性是指事务是一个不可分割的工作单位，事务
中的操作要么全部成功，要么全部失败。事务在执行过程中出现错误时，就
会回滚到事务开始前的状态，好像事务从来没有执行过一样，通过回滚日
志实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些
修改操作即可
一致性 Consistency 事务必须使数据库从一个一致性状态变换到另外
一个一致性状态。以转账为例子，A向B转账，假设转账之前这两个用户的钱
加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的
钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性，一致性是
通过原子性、隔离性和持久性一起实现的。一致性是事务的根本追求，数据
库系统通过并发控制技术和日志恢复技术来避免错误情况
隔离性 Isolation 事务的隔离性是多个用户并发访问数据库时，数据
库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并
发事务之间要相互隔离，通过锁和mvvc实现
持久性 Durability 持久性是指一个事务一旦被提交，它对数据库中
数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任
何影响，系统发生崩溃可以用重做日志（Redo Log）进行恢复，从而实
现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数
据页的物理修改

读问题

脏读脏读指一个事务读取了另外一个事务未提交的数据
如果第一个事务回滚会发现第二个事务读的数据又是最开始的数据，如果
不回滚而是修改之后提交的话那么第二个事务就会永远读heheda
不可重复读一个事务读取数据，多次读取结果不同。与脏读的区别在
于脏读是读取前一事务未提交的脏数据，而不可重复读是读取前一事务
已提交的数据
修改隔离级别为读提交以后可以避免脏读
但是不能够避免不可重复读
隔离级别设置为可重复读后可以解决不可重复读问题
幻读一个事务读取数据，另一个事务插入新的数据，前后数据不一样，
幻读本质上也属于不可重复读的情况，T1读取某个范围的数据，T2在这个范
围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和和第一
次读取的结果不同。
幻读与不可重复读的差别在于不可重复读对应的是update，可以通过可重复
读防止别的事务update数据，但是幻读对应的是insert操作，所以可重复
读无法阻止幻读，需要通过mvvc+next-key lock实现
第一个事务插入一条数据后提交，第二个事务却不能插入数据，但是可以插
入除了id为4的数据
产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是
记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就
是间隙锁(Gap Lock)

产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过
并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要
用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户
以一种更轻松的方式处理并发一致性问题

封锁协议

一级封锁协议

事务T要修改数据A时必须加X锁，直到T结束才释放锁。可以解决丢失修改问题，
因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖

二级封锁协议

在一级的基础上，要求读取数据A时必须加S锁，读取完马上释放S锁。可以解决
读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议
，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据

三级封锁协议

在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放
S 锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X锁
，从而避免了在读的期间数据发生改变

两段锁协议

加锁和解锁分为两个阶段进行。可串行化调度是指，通过并发控制，使得并发
执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，
不会出现并发一致性问题。事务遵循两段锁协议是保证可串行化调度的充分条
件。例如以下操作满足两段锁协议，它是可串行化调度

1	lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但它还是可串行化调度

1	lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

MySQL隐式与显示锁定

MySQL 的 InnoDB 存储引擎采用两段锁协议，会根据隔离级别在需要的时候自
动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。
InnoDB 也可以使用特定的语句进行显示锁定：

1 2	SELECT ... LOCK In SHARE MODE; SELECT ... FOR UPDATE;

隔离级别

MySQL有4种隔离级别，隔离性本质是通过锁来实现的

set session transaction isolation level read committed; #设置隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

read uncommitted 事务中的修改，即使没有提交，对其它事务也是可
见的
read committed 一个事务只能读取已经提交的事务所做的修改。换句
话说，一个事务所做的修改在提交之前对其它事务是不可见的
repeatable read 保证在同一个事务中多次读取同一数据的结果是一
样的
serializable 强制事务串行执行，这样多个事务互不干扰，不会出现
并发一致性问题。该隔离级别需要加锁实现，因为要使用加锁机制保证同一
时间只有一个事务执行，也就是保证事务串行执行

隔离级别越高效率越低，根据情况选择隔离级别

事务的实现

参考 https://www.cnblogs.com/wyc1994666/p/11367051.html
事务隔离性由锁实现，原子性、一致性、持久性通过数据库的redo log和undo
log来实现，redo log为重做日志，用来保证事务的原子性和持久性，undo
log用来保证事务的一致性。redo和undo并不是逆过程，redo 恢复提交事务
修改的页操作，而undo回滚行记录到特定版本。redo通常是物理日志，记录
页的物理修改操作，undo是逻辑操作根据每行记录进行记录。
事务的最终目的就是做到可靠性和并发处理，在企业级开发中数据库是一定
不能出错的

可靠性数据库要保证insert或update操作时抛异常或者数据库crash的
时候需要保证数据的操作前后一致，要想做到这个必须知道修改之前和之后
的状态，于是就有了undo log和redo log
并发性有多个请求操作数据库时需要对事务之间的读写进行隔离，隔离
程度要看具体业务系统

三种日志

redo log 重做日志，用来保证事务的持久性，基本是按顺序写的。在事务
开始时就会产生redo log，逐步写入日志文件，归属于存储引擎
undo log 回滚日志，用来帮助事务回滚及MVV C功能，需要进行随机读写
，保存了事务发生之前的一个版本，在事务提交之后 undo log 并不会马上
删除，而是放入待清理的链表，purge线程判断是否由其他事务在使用undo
段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志
空间，归属于存储引擎
binlog 二进制日志归属于Server层，error log 和realy log 也是在
Server层。用于PIT POINT-IN-TIME的恢复以及主从复制环境的建立

二进制日志和重做日志的区别

产生位置不同二进制文件是在数据库的上层产生，重做日志是在InnoDB
存储引擎中产生，不管是什么存储引擎，对数据库进行了修改都会产生二进
制日志
内容形式不同两种日志记录的内容形式不同，二进制日志是一种逻辑日
志，记录的是对应的SQL语句，InnoDB存储引擎层面的重做日志是物理格式
日志，记录的是对于每个页的修改
写入磁盘的时间不同二进制日志只在事务提交完成后进行一次写入，而
InnoDB存储引擎的重做日志在事务进行中不断地被写入，也就是说并不是按
照事务提交的顺序进行写入。而且二进制日志只包含对应事务的一个日志，
重做日志包含每个事务的多个物理操作日志

log block

在InnoDB存储引擎中，重做日志都是以512个字节进行存储的，意味着重做
日志缓冲、重做日志文件都是以块(block)的方式进行保存，称为重做日志
块。若一个页中重做日志数量大于512个字节，那么需要分割为多个块进行
存储，此外重做日志块的大小和磁盘扇区大小一样，都是512个字节，所
以重做日志的写入可以保证原子性，不需要doublewrite技术。注意重做
日志块除了日志本身外，还包括日志快头12个字节和日志快尾8个字节

接下来会讲解实现事务的三个技术：日志文件、锁技术和MVCC，然后讲解事
务的实现原理

redo log

redo log 叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组
成：重做日志缓冲(redo log buffer)和重做日志文件(redo log)，前者
是在内存中后者是在磁盘中，当事务提交之后会把所有信息都存到该日志中
。假设有个表要插入数据

start transaction;
select balance from bank where name="zhangsan";
# 生成 重做日志 balance=600
update bank set balance = balance - 400; 
# 生成 重做日志 amount=400
update finance set amount = amount + 400;
commit;

所有的数据在落盘之前一定先经过内存，redo log中记录的是实际物理页
的修改

Buffer Pool先写到Log Buffer，然后每秒写入OS Buffer并调fsync
方法刷新到磁盘
Buffer Pool每次提交写入OS Buffer并调fsync方法刷新到磁盘
Buffer Pool每次提交写入OS Buffer，每秒调fsync方法刷新到磁盘

MySQL为了提升性能不会把每次的修改都实时同步到磁盘，而是先会存到 Buffer
Pool 中，然后使用后台线程去做缓存池和磁盘之间的同步。如果还没来的同步的
时候宕机或断电了，也就是上图还未执行红色部分的操作，那么这样就会丢失已
提交事务的修改信息，所以引入redo log来记录已经提交事务的修改信息，并
且会把redo log持久化到磁盘，系统重启后再读取redo log恢复最新数据。
总结一句话redo log是用来恢复数据的，用于保障已提交事务的持久化特性

LSN

日志序列号，在InnoDB存储引擎中占8个字节，并且单调递增，含义如下

重做日志写入的总量
checkpoint的位置
页的版本

LSN代表事务写入重做日志的字节总量，例如当前重做日志的LSN为1000，有一个
事务T1写入100字节的重做日志，那么LSN就变为1100。可见LSN中记录的是重做
日志的总量，单位是字节。LSN 不仅记录在重做日志中也记录在每个页中，每个
页的头部都记录了该页的LSN，在页中LSN表示最后刷新时LSN的大小。因为重做
日志记录的是每个页的日志，所以可以通过页LSN判断也是否需要被恢复

恢复

InnoDB存储引擎在启动时不管上次数据库运行是否正常关闭，都会尝试进行恢复
操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日
志（如二进制日志）要快很多。重启 InnoDB 时，checkpoint表示已经完整刷
到磁盘上data page上的 LSN，因此恢复时仅需要恢复从checkpoint开始的日
志部分。例如，当数据库在上一次checkpoint的 LSN 为 10000 时宕机，且事
务是已经提交过的状态。启动数据库时会检查磁盘中数据页的 LSN，如果数据页
的LSN小于日志中的 LSN，则会从检查点开始恢复

undo

undo log叫做回滚日志，用于记录数据被修改前的信息，正好与重做日志要记录
的信息相反，重做日志是记录被修改后的数据。undo log的存在就是为了在发生
错误时回滚之前的操作，需要将之前的数据都记录下来，然后发生错误时回滚

每次写入数据或修改数据之前都会把修改前的信息记录到undo log中，总结一句
话undo log是用来回滚数据的，用于保障未提交事务的原子性。
undo log存放在数据库内部的一个特殊字段中，这个字段称为undo段，位于共
享表空间中。undo是逻辑日志，只是将数据库逻辑地恢复到原来的样子，所有
修改都被逻辑取消

当delete一条记录时会在undo log中记录一条对应的insert记录
当insert一条记录时会在undo log中记录一条对应的delete记录
当update一条记录时会在undo log中记录一条对应相反的update记录

除了回滚操作undo的另一个作用就是MVVC，当用户读取一行记录时，若该行记录
已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息以实现非锁
定读取。
最后注意undo log会产生redo log，也就是undo log的产生会伴随redo log
的产生，因为undo log 也需要持久性的保护

undo存储管理

事务在undo log segment分配页并写入undo log的这个过程同样需要写入重做
日志，当事务提交时

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

事务提交后并不可以马上删除undo log及undo log所在的页，因为其他事务
需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入
一个链表中，最终是否删除undo log及undo log所在页由purge线程判断

purge

delete和updaet操作可能并不直接删除原有的数据。purge 用于最终完成
delete和update，这样设计是因为InnoDB支持MVVC，所以记录不能在事
务提交时立即处理，有可能其他事务也在引用这行，若改行已经不被其他
事务引用那么可以真正删除

MVVC

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL
的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复
读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，
无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用
MVCC 无法实现。
InnoDB的MVVC是通过在每行记录的后面保存了两个隐藏的列来实现的，这两个
列一个保存了行的创建时间一个保存了行的过期时间，这个时间并不是实际的
时间而是系统版本号，主要实现思想是通过数据多版本来做到读写分离，从而
实现不加锁而做到读写并行。MVVC在MySQL中的实现依赖是undo log和read
review

undo log 记录某行数据的多个版本的数据
read review 用来判断当前版本数据的可见性

基本思想

在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加
锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，MVCC
利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照
，没有互斥关系，这一点和 CopyOnWrite 类似。
在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个
版本快照。
脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进
行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交
的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读

MVVC的好处

主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有
读写冲突时，也能做到不加锁，非阻塞并发读。解决读-写冲突的无锁并发控制
，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事
务时间戳关联，读操作只读该事务开始前的数据库的快照。所以MVCC可以为
数据库解决以下问题

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻
塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢
失问题

MVVC实现原理

参考 https://www.jianshu.com/p/8845ddca3b23
实现原理主要是依赖记录中的 3个隐式字段，undo日志，Read View来实现的

隐藏字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID
DB_ROLL_PTR DB_ROW_ID等字段

DB_TRX_ID 6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后
一次修改该记录的事务ID
DB_ROLL_PTR 7byte，回滚指针，指向这条记录的上一个版本（存储于
rollback segment里）
DB_ROW_ID 6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，
InnoDB会自动以DB_ROW_ID产生一个聚簇索引

如上图所示，DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，
DB_TRX_ID是当前操作该记录的事务ID，而DB_ROLL_PTR是一个回滚指针
，用于配合undo日志，指向上一个旧版本

版本号

系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号
就会自动递增
事务版本号 TRX_ID ：事务开始时的系统版本号

undo日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志
通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。在InnoDB存
储引擎中，undo log分为

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

例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个
数据行，然后对该数据行执行两次更新操作

1
2
3

INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据
MySQL 的 AUTOCOMMIT 机制，每个操作都会被当成一个事务来执行，所以上
面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID和操作之
外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除
INSERT、UPDATE、DELETE 操作会创建一个日志，并将事务版本号 TRX_ID 写
入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为1

undo log执行流程

比如一个有个事务插入person表插入了一条新记录，记录如下，name为
Jerry, age为24岁，隐式主键是1，事务ID和回滚指针，我们假设为NULL
在来了一个事务1对该记录的name做出了修改，改为Tom

在事务1修改该行(记录)数据时，数据库会先对该行加排他锁
然后把该行数据拷贝到undo log中，作为旧记录，即在undo log中有当前
行的拷贝副本
拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务ID为当前事务
1的ID, 我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本
记录，即表示我的上一个版本就是它
事务提交后，释放锁

又来了个事务2修改person表的同一个记录，将age修改为30岁

在事务2修改该行数据时，数据库也先为该行加锁
然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo
log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
修改该行age为30岁，并且修改隐藏字段的事务ID为当前事务2的ID, 那就
是2，回滚指针指向刚刚拷贝到undo log的副本记录
事务提交，释放锁

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导
致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就
是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的
节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实
在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

Read View

Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该
事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护
系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递
增的，所以最新的事务，ID值越大)

Read View的作用

Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候
，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够
看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo
log里面的某个版本的数据

Read View的实现

Read View 遵循一个可见性算法，主要是将要被修改的数据的最新记录中的
DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比
（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较
，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出 Undo Log 中的
DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近
的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个
DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本。实际上维
护的就是一个系统未提交的事务列表
在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和
TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之
前进行更改的，因此可以使用，则当前事务能看到TRX_ID 所在的记录。这
个意思就是说TRX_ID_1 TRX_ID_2等还未提交的事务在开始时都是读取这
个TRX_ID的快照数据，是可以使用的
TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的
，因此不可使用，也就是说其他还未提交的事务已经更改了快照数据，之前
已经说过修改操作会为数据行新增一个版本快照
TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行
判断

提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事
务还未提交，则该快照不可使用。否则表示已经提交，可以使用
可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读
到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就
会发生改变，也就是出现了不可重复读问题

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR
找到下一个快照，再进行上面的判断。综上以我自己的理解就是，比如一个事务
1已经修改了数据并提交，这是这个数据已经是最新的数据。接下来无论开启多
少个事务，使用快照读读取到的都是事务1更新的数据，也就是满足条件1

RC,RR级别下的InnoDB快照读的区别

正是Read View 生成时机的不同，从而造成RC，RR级别下快照读的结果的不同

在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read
View，将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是
使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用
过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修
改不可见
即RR级别下，快照读生成Read View 时，Read View会记录此时所有其他活
动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View
创建的事务所做的修改均是可见
而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View，
这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因

总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View，而在RR
隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快
照读获取的都是同一个Read View

事务的实现

事务的原子性是通过undo log来实现的
事务的持久性是通过redo log来实现的
事务的隔离性是通过读写锁+MVVC来实现的
事务的一致性是通过原子性、持久性和隔离性来实现

原子性

假设有两个表bank和finance，当进行插入、删除和更新操作时生成undo log

由上图可知数据的变更都伴随着回滚日志的产生

产生了被修改前数据(zhangsan,1000) 的回滚日志
产生了被修改前数据(zhangsan,0) 的回滚日志

由以上的流程可知

每条数据变更(insert/update/delete)操作都伴随一条undo log的生成，
并且回滚日志必须先于数据持久化到磁盘上
所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert
，insert的逆向操作为delete，update的逆向为update等，在操作数据前会
将数据记录在回滚日志，然后每条操作的逆向操作会记录在回滚日志

当系统发生错误或执行rollback时需要根据undo log进行回滚
回滚操作就是要还原到原来的状态，undo log记录了数据被修改前的信息以及
新增和被删除的数据信息，根据undo log生成回滚语句

如果在回滚日志里有新增数据记录，则生成删除该条的语句
如果在回滚日志里有删除数据记录，则生成生成该条的语句
如果在回滚日志里有修改数据记录，则生成修改到原先数据的语句

持久性

事务一旦提交，其所做的修改会永久保存在数据库中，就算系统崩溃也不会丢
失，MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘IO
，然而即使是使用SSD磁盘IO也是非常消耗性能的，为了提升性能InnoDB提
供了缓冲池，Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用

读数据会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入
缓冲池
写数据会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中

缓冲池能够极大提升性能，但是当计算机宕机或者断电的时候数据就会丢失，
虽然事务已经提交但是数据依然在缓存中，还未磁盘持久化，所以需要一种
机制保存已提交的事务数据，为恢复数据使用
事务的持久性是通过redo log来实现的，在操作数据时并不是把结果直接写
到磁盘，而是先写到内存，但是内存中的数据可能会丢失，所以undo log就
是保证数据丢失时依然能够将修改写入磁盘