导图社区 MySQL高阶知识，看看你都知道多少个

MySQL高阶知识，看看你都知道多少个

MySQL高阶知识，如在MySQL数据库中，视图（View）是一个命名的虚表，它由一个SQL查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有实际的物理存储。看看你都知道多少个~

提示: 本内容由社区用户上传并分享。平台不对内容的真实性、合法性、知识产权归属及是否侵害第三方权利进行事前审核或保证。本内容可能包含受版权保护的图片、字体或其他第三方素材，使用前请自行确认授权范围。

编辑于2023-10-11 11:02:32

mysql
高阶

遥遥知识库

他的近期作品查看更多>>

MySQL高阶知识，看看你都知道多少个

社区模板帮助中心，点此进入>>

遥遥知识库

他的近期作品查看更多>>

相似推荐
大纲

互联网9大思维
- 42.1k
- 987
- 2.5k
- 402
- 0
MindMaster
安全教育的重要性
- 9.1k
- 955
- 100
- 18
- 0
issen
组织架构-单商户商城webAPP 思维导图。
- 20.3k
- 3
- 187
- 8
- 1
Kacyun
个人日常活动安排思维导图
- 10.5k
- 0
- 85
- 0
- 0
少儿栏目外景策划波波老师
域控上线
- 5.7k
- 173
- 11
- 4
- 0
jackrao
西游记主要人物性格分析
- 21.0k
- 1.5k
- 648
- 102
- 0
issen
17种头脑风暴法
- 217.3k
- 4.3k
- 12.0k
- 4.1k
- 1
MindMaster
python思维导图
- 11.6k
- 560
- 243
- 6
- 0
(*^▽^*)
css
- 5.1k
- 1
- 43
- 3
- 0
A张舫
CSS
- 7.9k
- 276
- 189
- 33
- 0
journey

mySQL

MySQL 架构

架构图

分层结构

网络连接层

Connectors

该层一般基于 C/S 架构组件，对外提供交互的组件

SQL 处理层

Management Service & Utilities

管理服务组件和工具组件，提供对MySQL 集成管理，备份、恢复、安全管理等

SQL Interface

SQL 接口组件，接收用户 SQL 命令，如 DML，DDL 和存储过程等，并将最终结果返回给用户

Parser

查询分析器组件，首先分析 SQL 命令语法的合法性（是否符合 SQL 92 标准），并尝试将 SQL 命令分解成数据结构，若分解失败，则提示 SQL 语句不合理

Limit 属于 MySQL 自己的语法，不属于 SQL 92 标准

Optimizer

优化器组件，对 SQL 命令按照标准流程进行优化分析

针对索引，选择最优索引。

多表关联，将数据较小的表放在左边（嵌套循环，外层循环小提高性能）。

通过执行计划 Explain 执行 Where 从左到右寻找过滤力度最大的先执行（如有主键，先找主键）。

Caches & Buffers

缓存和缓冲组件

Map 结构： Key 存储 SQL 语句 hash 值，Value 存储 SQL 返回的结果。当该数据更新时，缓存层会删除。

MySQL 8.0 版本已经不再使用该功能

插件式存储引擎层

MyISAM

高速引擎，查询和插入顺序较高。5.6 版本支持了事务（与 InnoDB 事务不同），但不能同时与带有事务引擎使用（GTID 全局事务ID，可了解），但是仍然不支持行锁。

B+Tree 索引

InnoDB

5.5 版本以后默认引擎，支持事务处理、回滚、修复及 MVCC（多版本并发控制）和行锁及外键。

B+Tree 索引

Memory

内存存储引擎，无需磁盘 I/O。重启表结构会保留，数据会丢失。

Hash 索引

CSV

可以将 Excel 等彪哥数据存储为 CSV 文件。

不支持索引

NDB

集群存储引擎，类似 Oracle 的 RAC 集群，不同的是结构采用 share noting 集群架构。

NDB 数据全部放在内存中，但是 JOIN 查询速度慢。

只支持 READ COMMITTED 隔离级别

没有 MVCC

相关知识

Tablespace

表空间可以设置未固定大小，也可以设置未自动增长。 已经占用的空间，不会再主动释放，所以一般情况下，表空间文件大小只会增长，而不会减小。 且表空间增长，是

System tablespace

https://dev.mysql.com/doc/refman/5.7/en/innodb-system-tablespace.html 一定会存以下数据： InnoDB data dictionary InnoDB数据字典 double write buffer 双写缓冲 change buffer 修改缓冲 undo logs undo日志 如果未开启innodb_file_per_table时，以下数据也会存放在这里： table data: transaction 事务相关数据 row 行数据...

General tablespace

https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html 一般表空间是被所有表共享的表空间，可以使用CREATE TABLESPACE语句创建。 类似系统表空间，是一个共享表空间，保存着多张表的数据。 和file-per-table表空间相比，具有潜藏的内存优势。服务器会在表空间的生命周期内，在内存中保存着表空间的元数据。多张表公用相同的表空间，消耗的内存比相同file数量的表使用file-per-table表空间所占用的内存更少。 一般表空间数据文件，可以放在与mysql数据相关或者独立的目录中，拥有对数据文件和针对file-per-table表空间的存储管理能力。对于file-per-table表空间而言，将数据文件放置在独立于mysql数据的目录，可以获取针对关键表的性能管理能力，比如针对特定表安装RAID或者DRBD，又或者将特定表绑定到特定磁盘上。 一般表空间支持Antelope和Barracuda文件格式，并且支持所有行数据格式，以及相关特性。支持这些文件格式，一般表空间文件并不依赖与innodb_file_format和innodb_file_per_table配置，而且这些配置对一般表空间也不会有任何影响。...

file-per-table tablespace

主要包含表数据和索引，保存在文件系统中的专属于该表的数据文件。 InnoDB会默认会将为表数据创建file-per-table表空间，不过这个行为可以通过innodb_file_per_table变量来设置开启或者关闭。 该变量既可以在配置文件中设置，也可以在运行期间动态修改。 从MySQL5.6才开始支持该变量。

advantages

在truncate或者drop一个表创建的file-per-table表空间时，这些磁盘空间将会返还给操作系统。如果这个表在其他表空间中，那么这些空间将继续被InnoDB数据占用。 一个表复制操作或者alter table操作，如果表在共享表空间中，表空间会持续增大，增加磁盘空间的占用。这些操作可能需要很多额外的空间保存表中添加的索引数据。即使是file-per-table表空间，这种方式占用的磁盘空间也不会返回给操作系统。 TRUNCATE的性能相对更好。 可以在单独的存储设备上创建每个表空间文件的表空间数据文件，更方便I/O优化、空间管理或备份。 可以直接通过file-per-table表空间导入其他mysql实例中的表数据。 可以使用BYNAMIC和COMPRESSED行数据格式 存储在单个表空间数据文件中的表在恢复时可以更节省时间，并在备份或者二进制文件损坏的时候，或者MySQL实例无法重启时，提高数据损坏发生时成功恢复的几率...

disdvantages

Page（页）

标准页

InnoDB 中，数据管理的最小单位为页，默认 16KB，页中除了存储用户数据，还可以存储控制信息的数据。 InnoDB IO子系统的读写最小单位也是页。

压缩页

通过相关参数配置，可以将页压缩，压缩最小单位是2KB。 页最大暂时是16K，且无法修改，页压缩之后，无法在恢复。

Buffer Chunks（缓冲块）

包括两部分：数据页和数据页对应的控制体，控制体中有指针指向数据页。Buffer Chunks是最低层的物理块，在启动阶段从操作系统申请，直到数据库关闭才释放。通过遍历 chunks 可以访问几乎所有的数据页，有两种状态的数据页除外：没有被解压的压缩页(BUF_BLOCK_ZIP_PAGE)以及被修改过且解压页已经被驱逐的压缩页(BUF_BLOCK_ZIP_DIRTY)。此外数据页里面不一定都存的是用户数据，开始是控制信息，比如行锁，自适应哈希等。

Buffer Pool 内存常用三大逻辑链表

Free List

该链表所有节点都是未被使用的节点，如果需要从数据库中分配新的数据页，直接从上获取即可。InnoDB 需要保证Free List 有足够的节点，提供给用户线程用，否则需要从 FLU List 或者 LRU List 淘汰一定的节点。InnoDB 初始化后，Buffer Chunks 中的所有数据页都被加入到 Free List，表示所有节点都可用。

LRU List

近期最少使用链表(Least Recently Used)，这个是 InnoDB 中最重要的链表。所有新读取进来的数据页都被放在上面。链表按照最近最少使用算法排序，最近最少使用的节点被放在链表末尾，如果 Free List里面没有节点了，就会从中淘汰末尾的节点。LRU List 还包含没有被解压的压缩页，这些压缩页刚从磁盘读取出来，还没来得及被解压。 最大限度地减少进入缓冲池并从未被再次访问的页的数量，这样可以确保热点页保持在缓冲池中。

LRU 实现原理

当缓冲池的 Free List 没有可用的空闲页时，InnoDB 会回收 LRU 列表中最近最少使用的页，并将新读取到的页添加到LRU列表的 midpoint 位置，称之为 “midpoint insertion strategy”。LRU List 被分为两部分，默认前 5/8 为 young list，存储经常被使用的热点 page，后 3/8 为old list。新读入的 page 默认被加在 old list 头，只有满足一定条件后，才被移到young list 上，主要是为了预读的数据页和全表扫描污染 buffer pool。 最初，新添加到缓冲池的页位于 old 子列表的头部。当在缓冲池中第一次访问这些页时，会将它们移到 young 子列表的头部，此时发生的操作称为 page made young。随着数据库的运行，缓冲池中没有被访问到的页由于移到 LRU 列表的尾部而变老，最终会回收 LRU 列表尾部长时间未被访问的页。 为什么要将新读取到的页放在 midpoint 位置而不是 LRU列表的头部？若直接将读取到的页插入到 LRU 列表的头部，当出现全表扫描或索引扫描的时候，需要将大量的新页读入到缓冲池中，导致热点页从缓冲池刷出，而这些新页可能仅在这次查询中用到，并不是热点数据，这样就会...额

特性

缓存了所有读入内存的数据页

1. 未修改的页面，可以从该列表中摘除，然后移到free列表中。 2. 已修改还未刷新到磁盘的页面。 3. 已修改且已经刷新到磁盘的页面，可并为第一类。

热点数据、新数据、旧数据处理

FLU List（Flush List）

这个链表中的所有节点都是脏页，也就是说这些数据页都被修改过，但是还没来得及被刷新到磁盘上。 在 FLU List 上的页面一定在 LRU List 上，但是反之则不成立。 一个数据页可能会在不同的时刻被修改多次，在数据页上记录了最老(也就是第一次)的一次修改的 lsn，即 oldest_modification。不同数据页有不同的 oldest_modification，FLU List 中的节点按照oldest_modification 排序，链表尾是最小的，也就是最早被修改的数据页，当需要从 FLU List 中淘汰页面时候，从链表尾部开始淘汰。加入FLU List，需要使用 flush_list_mutex 保护，所以能保证 FLU List 中节点的顺序。

Buffer Pool 数据

Buffer Pool 预热

MySQL重启与数据加载

Buffer Pool 里面没有什么数据，这个时候业务上对数据库的数据操作，MySQL 就只能从磁盘中读取数据到内存中，这个过程可能需要很久才能是内存中的数据是业务频繁使用的。 Buffer Pool 中数据从无到业务频繁使用热数据的过程称之为预热。 在预热这个过程中，MySQL 数据库的性能不会特别好，并且 Buffer Pool 越大，预热过程越长。

提高预热效率

为了减短这个预热过程，在 MySQL 关闭前，把 Buffer Pool 中的页面信息保存到磁盘。 等到 MySQL 启动时，再根据之前保存的信息把磁盘中的数据加载到 Buffer Pool 中即可。 通过 innodb_buffer_pool_dump_at_shutdown（服务器关闭前设置）来设置，当设置这个参数以后MySQL就会在机器关闭时保存 InnoDB 当前的状态信息到磁盘上。

Buffer Pool 预读

I/O过程

InnoDB 在 I/O 的优化上有个比较重要的特性为预读，预读请求是一个 I/O 请求，它会异步地在缓冲池中预先回迁多个页面，预计很快就会需要这些页面，这些请求在一个范围内引入所有页面。

读取过程

数据库请求数据的时候，会将读请求交给文件系统，放入请求队列中； 相关进程从请求队列中将读请求取出，根据需求到相关数据区(内存、磁盘)读取数据； 取出的数据，放入响应队列中； 最后数据库就会从响应队列中将数据取走； 完成一次数据读操作过程。

读请求合并

接着进程继续处理请求队列，(如果数据库是全表扫描的话，数据读请求将会占满请求队列)， 判断后面几个数据读请求的数据是否相邻，再根据自身系统 IO 带宽处理量， 进行预读，进行读请求的合并处理，一次性读取多块数据放入响应队列中，再被数据库取走。 (如此，一次物理读操作，实现多页数据读取，rrqm>0（ # iostat -x ），假设是 4 个读请求合并，则 rrqm 参数显示的就是 4)

InnoDB 使用两种预读算法来提高 I/O 性能

Linear Read-Ahead（线性预读）

以 Extent 为单位，64 个 Page 为一个 Extent，将下一个 Extent 提前读取到 Buffer Pool 中。

RandomRead-Ahead（随机预读）

以 Page 为单位，将当前 Extent 中剩余的 Page 提前读取到 Buffer Pool 中。

Buffer Pool 工作机制

实现加速度与加速写的同时，需要考虑客观条件的限制，因为机器的内存大小是有限的，所以 MySQL 的 Innodb Buffer Pool 的大小同样是有限的。在通常的情况下，当数据库的数据量比较大的时候，缓存池并不能缓存所有的数据页，所以也就可能会出现，当需要访问的某个页面时，该页面却不在缓存池中的情况，这个时候就需要从磁盘中将这个页面读出来，加载到缓存池中，然后再去访问。这样就涉及到随机的物理 I/O，也就延长了访问页面所消耗的时间。 这样的情况是一个 Bad Case，是我们期望尽量避免的——因此需要想办法来提高缓存的命中率。Innodb Buffer Pool 采用经典的 LRU 列表算法来进行页面淘汰，以提高缓存命中率。

加速读

当需要访问一个数据页面的时候，如果这个页面已经在缓存池中。 那么就不再需要访问磁盘，直接从缓冲池中就能获取这个页面的内容。

加速写

当需要修改一个页面的时候，先将这个页面在缓冲池中进行修改，记下相关的 Redo Log，这个页面的修改就算已经完成了。 至于这个被修改的页面什么时候真正刷新到磁盘，这个是 Buffer Pool 后台刷新线程来完成的。

Buffer Pool（缓冲池）

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。但是由于 CPU 速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能。

Data Page

当数据进行 Insert、Update、Delete 时， 因为内存的 Data Page 跟磁盘数据页不一致，该页为 Dirty Page。 Dirty Page会被加入到FLU List等待被刷入磁盘中。

数据页，架构图上未标识

Index Page（索引页）

主要保存着聚簇索引与非聚簇索引信息

Change Buffer Page

Change Buffer Page , Insert Buffer Page 的升级 用来缓存不在缓冲池中的辅助索引页（非唯一索引）的变更。这些缓存的的变更，可能由 Insert、Update 或 Delete 操作产生， 当读操作将这些变更的页从磁盘载入缓冲池时，InnoDB 会将 Change Buffer Page 中缓存的变更跟载入的辅助索引页合并。 不像聚簇索引，辅助索引通常不是唯一的，并且辅助索引的插入顺序是相对随机的。若不用 Change Buffer Page，那么每有一个页产生变更，都要进行 I/O 操作来合并变更。使用 Change Buffer 可以先将辅助索引页的变更缓存起来，当这些变更的页被其他操作载入缓冲池时再执行 merge 操作，这样可以减少大量的随机 I/O。Change Buffer Page 可能缓存了一个页内的多条记录的变更，这样可以将多次I/O操作减少至一次。 在内存中， Change Buffer Page 占据缓冲池的一部分。在磁盘上， Change Buffer Page 是系统表空间的一部分，以便数据库重启后缓存的索引变更可以继续被缓存。

Adaptive Hash Index(自适应哈希)

自适应哈希索引是 InnoDB 表通过在内存中构造一个哈希索引来加速查询的优化技术，此优化只针对使用 '=' 和 'IN' 运算符的查询。MySQL会监视 InnoDB 表的索引查找，若能通过构造哈希索引来提高效率，那么 InnoDB 会自动为经常访问的辅助索引页建立哈希索引。 这个哈希索引总是基于辅助索引(B+树结构)来构造。MySQL 通过索引键的任意长度的前缀和索引的访问模式来构造哈希索引。InnoDB 只为某些热点页构建哈希索引。 通过 innodb_adaptive_hash_index 参数开启或禁用此功能，默认是开启状态。开启此功能后，InnoDB 会根据需要自动创建这个哈希索引，而不用人为干预创建，这就是叫自适应的原因。此功能并不是在所有情况下都适用，且 AHI 需要的内存都是从缓冲池申请的，所以此功能的开启或关闭需要通过测试来具体确定。可以通过 SHOW ENGINE INNODB STATUS 命令查看 AHI 的使用状况。

Lock Info(锁信息)

元数据、表结构

Data Dictionary(数据字典)

行锁、表锁

write Ahead Log

预写式日志，WAL机制，关系型数据库实现事务日志的标准方案

Redo Log Buffer（重做日志缓冲）

构成结构

InnoDB 存储引擎内存区域的一部分，存储数据修改引起的事务缓存，大小为 8M 在硬盘中，由 innodb_log_group_home_dir 参数指定文件目录：id_logfile0，id_logfile1（InnoDB 至少有 1 个重做日志文件组，且每组至少包含 2 个 id_logfile*），该文件被称为 Redo Log File，记录 InnoDB 存储引擎的事务日志，服务器宕机可以使用 Redo Log File 恢复数据到宕机前状态，保证数据的完整性。以循环方式写入，当写满文件1时，再写入文件2，同时确定文件1的数据不会再使用清除。当文件2写满时切换到文件1。

innodb_log_group_home_dir

可指定重做日志保存目录

innodb_log_group

每个innodb至少有一个重做日志组，每个重做日志组至少包含两个重做文件

Redo Log File

记录InnoDB存储引擎的事务日志，mysql服务器宕机时，可以使用redo log用来恢复服务器状态，保证数据的完整性。 由于是按照重做日志组，且每个组至少两个文件，重做日志会再写完一个之后进行切换。以循环方式写入，当写满文件1时，再写入文件2，同时确定文件1的数据不会再使用清除。 当第二个将要写完时，将会进入mysql的checkPoint，进行脏页刷盘，将重做日志中的数据刷入磁盘当中。 由于这个机制存在，所以当redo log设置的太小时，部分事务再执行过程中会触发多次redo log file写入切换的事件，且清理redo log file时，还需要确保redo log file中的数据已经无效，这中间就涉及到脏页刷盘的操作。 所以redo log 的大小需要设置到一个合理的大小，且redo log如果设置的过大，恢复mysql的时间又增长。

写入过程

此具体过程可以参考日志 -> Redo日志 -> LSN解析具体过程

Redo Log Info

Redo Log Buffer

Redo Log File

触发落盘几种场景

Redo Log Buffer 空间不足

后台线程

建立 CheckPoint

实例 ShutDown

Bin Log 切换

事务提交

通过 Force Log at Commit 机制来保证事务的持久性。 通过变量 innodb_flush_log_at_trx_commit 决定 commit 动作是否刷新 log buffer 到磁盘。 在事务提交时，必须先将该事务的所有日志写入到 Redo Log File 中，待事务的 commit 操作完成才算整个事务操作完成。

设置为 0

事务提交每秒写入 Log Buffer的日志,不写入 OS Buffer，并调用 fsync() 写入 Log Files 到磁盘。 当mysql崩溃时，将丢失最后1秒的数据。

设置为 1

事务每次提交都会将 Log Buffer 中的日志写入 OS Buffer，并调用 fsync() 直接写入 Log Files 到磁盘。 此值是默认设置，大部分场景下，都比较安全，但是I/O性能最差，丢失数据更少。

设置为 2

事务每次提交仅写入 OS Buffer，然后每秒调用 fsync() 将 OS Buffer 中的日志写入 Log Files 到磁盘。 当系统崩溃时，会丢失最后 1 秒的数据。

示意图

Doublewrite Buffer（双写缓冲）

时序图

产生原因

双写缓冲技术是为了解决 Partial Page Write 问题而开发的。DoubleWrite Buffer 是系统表空间上的连续的 128 个页(两个区)，大小为2M。 当发生数据库宕机时，可能 InnoDB 存储引擎正在写入某个页到表中，而这个页只写了一部分，比如 16KB 的页，只写了前 4KB，之后就发生了宕机，这种情况被称为部分写失效（Partial Page Write）。

Partial Page Write

InnoDB 的 Page Size 默认为 16KB，数据校验也是针对 16KB 来计算 Page 是否完整。操作系统写文件是以 4KB 作为单位，则每写入一个 InnoDB 的 Page 需要系统持续写入 4个 Block。 计算机硬件和操作系统，在发生极端情况（例如断电）时，不能保证此次写入操作的原子性。16KB 的数据，可能会在写入第 1 个或者第 2 个 Block 时发生操作中断，此时整个 Page 只有一部分是写入成功，这种情况就是 Partial Page Write （部分页写入）问题。此时 Page 数据出现不一致情况，形成一个断裂的 Page。InnoDB 对这种错误是无法处理的。 有人认为系统恢复，MySQL 可以根据 Redo Log 进行恢复，而 MySQL 在恢复过程中会检查 Page 的 CheckSum，CheckSum 就是 Page 的最后事务编号，发生 Partial Page Write 问题时，Page 已经损坏，MySQL 无法找到该 Page 的事务号，所以无法恢复。

工作原理

DoubleWrite 由两部分组成，一部分为内存中的 DoubleWrite Buffer，其大小为 2MB，另一部分是磁盘上共享表空间(ibdata x)中连续的 128 个页，即 2个区(extent)，大小也是 2M。 1. 拷贝到缓冲 当一系列机制触发数据缓冲池中的脏页刷新时，并不直接写入磁盘数据文件中，而是先拷贝至内存中的 DoubleWrite Buffer中； 2. 写入共享表空间 接着从两次写缓冲区分两次写入磁盘共享表空间中(连续存储，顺序写，性能很高)，每次写 1MB； 3. 写入innodb_per_file_tablespace...

DoubleWrite Buffer

share table space

innodb-file-per-tablespace

崩溃恢复

如果操作系统在将页写入磁盘的过程中发生崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间的 DoubleWrite 中找到该页的一个最近的副本，将其复制到表空间文件，再应用 Redo Log，就完成了恢复过程。 因为有副本所以也不担心表空间中数据页是否损坏。

副作用

写负载

Double Write 是一个 Buffer，但其实它是开在物理文件上的一个 Buffer，其实也就是 File，所以它会导致系统有更多的fsync 操作, 而硬盘的 fsync 性能是很慢的, 所以它会降低 MySQL 的整体性能。 但是，DoubleWrite Buffer 写入磁盘共享表空间这个过程是连续存储，是顺序写，性能非常高，(约占写的10%)，牺牲一点写性能来保证数据页的完整还是很有必要的。

监控负载

开启 DoubleWrite 后，每次脏页刷新必须要先写 DoubleWrite，而 DoubleWrite 存在于磁盘上的是两个连续的区，每个区由连续的页组成，一般情况下一个区最多有 64 个页，所以一次 IO 写入应该可以最多写64个页。 而根据以上系统 Innodb_dblwr_pages_written 与 Innodb_dblwr_writes 的比例来看，大概在 3 左右，远远还没到 64 (如果约等于64，那么说明系统的写压力非常大，有大量的脏页要往磁盘上写)，所以从这个角度也可以看出，系统写入压力并不高。

示例

采用Double Write Buffer的原因

写入连续性

1. 如果直接写入Page Data，而Page Data分散磁盘的各个位置上，可以是视为每次刷新脏页，都是一次随机写操作。 而Double Write Buffer是磁盘上一片连续的区域，再写入数据时，所有数据的顺序写，性能比随机写绝对占优，提高数据刷盘性能。

Buffer溢出

如果Double Write Buffer中的数据没办法及时清理，那么Double Write Buffer的性能会下降，更严重时，会导致Double Write Buffer溢出。

Additional Memory Pool

用来存储数据字典信息和其他内部数据结构的字节。应用程序中的表越多，需要在此处分配的内存就越多。如果 InnoDB 在该池中的内存不足，它将开始从操作系统分配内存，并将警告消息写入 MySQL 错误日志。

Disk（硬盘）

Tablespace（表空间）

Tablespace

表空间可以设置未固定大小，也可以设置未自动增长。 已经占用的空间，不会再主动释放，所以一般情况下，表空间文件大小只会增长，而不会减小。 且表空间增长，需要用户创建时定义，且增长大小一般是2M每次。

System tablespace

系统表空间 https://dev.mysql.com/doc/refman/5.7/en/innodb-system-tablespace.html 如果没有修改配置的话，一般是Mysql 数据目录中的ibdata1文件。 一定会存以下数据： InnoDB data dictionary InnoDB数据字典 double write buffer 双写缓冲 change buffer 修改缓冲 undo logs undo日志 如果未开启innodb_file_per_table时，以下数据也会存放在这里： table data:...

General tablespace

常规表空间 https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html 一般表空间是被所有表共享的表空间，可以使用CREATE TABLESPACE语句创建。 类似系统表空间，是一个共享表空间，保存着多张表的数据。 和file-per-table表空间相比，具有潜藏的内存优势。服务器会在表空间的生命周期内，在内存中保存着表空间的元数据。多张表公用相同的表空间，消耗的内存比相同file数量的表使用file-per-table表空间所占用的内存更少。 一般表空间数据文件，可以放在与mysql数据相关或者独立的目录中，拥有对数据文件和针对file-per-table表空间的存储管理能力。对于file-per-table表空间而言，将数据文件放置在独立于mysql数据的目录，可以获取针对关键表的性能管理能力，比如针对特定表安装RAID或者DRBD，又或者将特定表绑定到特定磁盘上。 一般表空间支持Antelope和Barracuda文件格式，并且支持所有行数据格式，以及相关特性。支持这些文件格式，一般表空间文件并不依赖与innodb_f...i

temp tablespace

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。 当临时表空间不足时，表现为运算速度异常的慢

file-per-table tablespace

advantages

disdvantages

undo tablespace

Undo 日志表空间 可以存储再系统表空间，也可以专门指定一个位置存储Undo表空间

逻辑存储结构

Tablespace（表空间）

表空间是 InnoDB中的最高抽象层次，所有的数据都放在表空间中。里面包含了数据、ChangeBuffer、Undo、事务、Double Write Buffer 等等信息。我们知道 InnoDB 采用的是聚集索引来组织数据结构，数据结构为B+ Tree

Segment（段）

表空间又是由段组成的，段通常又分为数据段、索引段、回滚段等等。 对于数据段它就对应于 B+ 树上的的叶子节点， 索引段对应于 B+ Tree 的非叶子节点。 回滚段，这部分特别特殊，由InnoDB自行管理，不受外界控制。

数据段

索引段

回滚段

Extent（区）

区是由连续页组成的空间，任何情况下区的大小均为 1MB，默认情况下 InnDB 引擎页的大小为16KB，因此一个区默认情况下一共有 64 个页。 1 Segment = 256 Extent，大小为 256 M

Page（页）

页是 InnDB 磁盘管理的最小单位，默认为 16KB 可以进行页压缩，最小可压缩至2KB大小，其他大小分别是8K,4K 且压缩页，一般是从一整页进行压缩，且一定是从大往小压缩。 一般情况下 1 Extent = 64 连续 Page，大小为 1M。 页压缩后，页数目会相应变化。 碎片页(Fregment Page)

Page、System、Disk 关系

该结构图可以看出造成 Partial Page Write 原因

常见页类型

B-Tree Node

undo log Page

System page

Transaction System Page

Insert Buffer Bitmap

Insert Buffer Tree List

Uncompressed BLOB Page

compressed BLOB Page

页数据

File Header

FIL_PAGE_SPACE_OR_CHKSUM 4 当MySQL为MySQL4.0.14之前的版本时，该值为0。在之后的版本中，代表页的checksum值(一种新的checksum值) FIL_PAGE_OFFSET 4 表空间中页的偏移值，如果某独立表空间a.ibd的大小为1GB，如果页大小为16KB，那么总共有65536页。FIL_PAGE_OFFSET表示该页在所有页中的位置。若该表空间的ID为10，搜索页（10，1）就表示查找表a中的第二页。 FIL_PAGE_PREV 4 当前页的上一页，B+Tree特性决定了叶子节点必须是双向列表。 FILE_PAGE_NEXT 4 当前页的下一页 FIL_PAGE_LSN 8 代表该页最后修改的日志序列位置LSN FIL_PAGE_TYPE 2 InnoDB存储引擎页的类型。常见的类型见上个分支。0x45BF代表存放的是数据页，即实际行记录的存储空间 FIL_PAGE_FILE_FLUSH_LSN 8 该值仅在系统表空间的一个页中定义，代表文件至少被更新到了该LSN值。对于独立表空间，该值都为0 FILE_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4 从MySQL4.1开始，代表页归属于哪个表空间

FILE_PAGE_TYPE

Page Header

该部分用来记录数据页的状态信息，由14个部分组成，共占用56字节。

虚拟行数据

在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。 这两个值会在页创建时创建，并且任何时候都不会被删除。 在Compact行格式和Redundant行格式下，两者占用的字节数各不相同。

Infimum

记录比该页中实际数据任何值都要小的数据。

Supremum

记录比该页中实际数据任何值都要大的数据。

User Record

实际行数据存储部分。

Free Space

Free Space指的是空闲空间，同样也是个链表数据结构。 在一条记录被删除后，该空间会被加入到空闲链表中。

Page Dictionary

Page Directory（页目录）中存放了记录的相对位置（注意，这里存放的是页相对位置，而不是偏移量），有些时候这些记录指针称为Slots（槽）或目录槽（Directory Slots）。 与其他数据库系统不同的是，在InnoDB中并不是每个记录拥有一个槽，InnoDB存储引擎的槽是一个稀疏目录（sparse directory），即一个槽中可能包含多个记录。 伪记录Infimum的n_owned值总是为1，记录Supremum的n_owned的取值范围为[1，8]，其他用户记录n_owned的取值范围为[4，8]。 当记录被插入或删除时需要对槽进行分裂或平衡的维护操作。 在Slots中记录按照索引键值顺序存放，这样可以利用二叉查找迅速找到记录的指针。假设有（'i'，'d'，'c'，'b'，'e'，'g'，'l'，'h'，'f'，'j'，'k'，'a'），同时假设一个槽中包含4条记录，则Slots中的记录可能是（'a'，'e'，'i'）。...

File Trailer

为了检测页是否已经完整地写入磁盘（如可能发生的写入过程中磁盘损坏、机器关机等），InnoDB存储引擎的页中设置了File Trailer部分。File Trailer只有一个FIL_PAGE_END_LSN部分，占用8字节。前4字节代表该页的checksum值，最后4字节和File Header中的FIL_PAGE_LSN相同。 将这两个值与File Header中的FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值进行比较，看是否一致（checksum的比较需要通过InnoDB的checksum函数来进行比较，不是简单的等值比较），以此来保证页的完整性（not corrupted）。 在默认配置下，InnoDB存储引擎每次从磁盘读取一个页就会检测该页的完整性，即页是否发生Corrupt，这就是通过File Trailer部分进行检测，而该部分的检测会有一定的开销。用户可以通过参数innodb_checksums来开启或关闭对这个页完整性的检查。...

Row（行）

InnoDB是面向行的(row-oriented)，数据按照行存放，每页最多存放的数据按照页大小而定。 但至少是2条。 在 msyql 5.7.9 及以后版本，默认行格式由innodb_default_row_format变量决定，它的默认值是DYNAMIC，也可以在 create table 的时候指定ROW_FORMAT=DYNAMIC。 用户可以通过命令SHOW TABLE STATUS LIKE 'table_name'来查看当前表使用的行格式，其中row_format属性表示当前所使用的行记录结构类型。 如果要修改现有表的行模式为compressed或dynamic，必须先将文件格式设置成Barracuda：...

格式为 Compact，事务 Id、回滚指针（指向 undo log record）、表定义的字段

CHAR

CHAR是定长类型，当未达到长度时，空数据填充0x20。

行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、LOB这类的大对象列类型的存储会把数据存放在数据页面之外。 但是，这个理解有点偏差，BLOB可以不将数据放在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据。 首先对VARCHAR数据类型进行研究。很多DBA喜欢MySQL数据库提供的VARCHAR类型，因为相对于Oracle VARCHAR2最大存放4000字节，SQL Server最大存放8000字节，MySQL数据库的VARCHAR类型可以存放65535字节。 但实际上InnoDB存储引擎并不支持65535长度的VARCHAR。这是因为还有别的开销，通过实际测试发现能存放VARCHAR类型的最大长度为65532。 并且MySQL官方手册中定义的65535长度是指所有VARCHAR列的长度总和，如果列的长度总和超出这个长度，依然无法创建。 在一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中。但是当发生行溢出时，数据存放在页类型为Uncompress BLOB页中。

存放位置

VARCHAR字段并不总是放在普通数据页。 TEXT、BLOB数据也不总是会放到Uncompressed BLOB Page。与VARCHAR的规则一致，如果一页数据无法存放两条行数据，就会对这些字段数据做溢出处理。但是一般TEXT、BLOB的长度会很大，所以一般都会溢出，且行数据保留前768字节的数据。

何时溢出

InnoDB存储引擎表是索引组织的，即B+Tree的结构，这样每个页中至少应该有两条行记录（否则失去了B+Tree的意义，变成链表了）。 如果页中只能存放一条数据，那么就会将数据做溢出处理。

Antelope文件格式

Compact 行记录格式

目前使用最大的行记录格式 再5.1时时默认格式，1.0.x内核引擎后支持。 优点： 支持变长字段，且变长字段最少只需占用1字节。 Null字段，每个Null字段需要一位用来标识，但是至少1字节存储，如果Null字段越来越多，Null字段标识所需会越来越大，且大小根据表中字段多少与位置相关。

分支主题

变长字段列表

一个存放可变长字段的实际长度，按照列顺序逆序放置 列长度小于等于255，则使用1字节表示。 列长度大于255小于等于65535，则使用2字节表示。 长度最多使用2字节。

Null 值字段标志位

如果字段创建是允许NULL值字段 是否有 NULL，按照顺序，哪个字段为空置，哪个字段的位上就是1，其余位是0， 至少1字节。 如果字段较多，且正好两个空值字段分别位于首、尾，Null值标识将会让用更多字节标识Null值字段。

record header（记录头）

固定占用5字节，40位 () 1 未知 () 1 未知 deleted_flag 1 改行是否删除 min_rec_flag 1 为1，如果该记录是预先被定义为最小的记录 n_owned 4 该记录拥有的记录数 heap_no 13 索引堆中该条记录的排序记录 record_type 3 记录类型，000普通，001 B+树节点指针，010 Infimum，011 Supremum，1xx 保留 next_record 16 页中吓一条记录相对位置...

隐藏列

DB_ROW_ID

此隐藏列不是必有的，当建表时，未主动指定主键列， 且表中没有建立唯一约束索引时，将会自动创建使用此列作为该表的主键 大小为6字节。

DB_TRX_ID

6字节 标识最后更新或者插入该记录的事务ID（删除操作在InnoDB中也被视为是一个更新操作）。在特定位上修改，即可标记记录为删除状态。 事务之间的数据隔离就是通过此字段实现的。

DB_ROLL_PTR

7字节 回滚指针，指向被写入回滚段中的undo日志记录。

列数据...

Redundant行记录格式

分支主题

字段长度偏移列表

字段长度偏移列表，同样是按照列的顺序逆序放置的。 若列的长度小于255字节，用1字节表示；若大于255字节，用2字节表示。

记录头信息

() 1 未知 () 1 未知 deleted_flag 1 改行是否删除 min_rec_flag 1 为1，如果该记录是预先被定义为最小的记录 n_owned 4 该记录拥有的记录数 heap_no 13 索引堆中该条记录的排序记录 n_fields 10 记录中列的数量 1byte_offs_flag 1 偏移列表为1字节还是2字节 next_record 16 页中下一条记录相对位置 Total 48

隐藏列

DB_ROW_ID

此隐藏列不是必有的，当建表时，未主动指定主键列， 且表中没有建立唯一约束索引时，将会自动创建使用此列作为该表的主键 大小为6字节。

DB_TRX_ID

DB_ROLL_PTR

7字节 回滚指针，指向被写入回滚段中的undo日志记录。

Barracuda文件格式

此文件格式支持InnoDB的所有文件格式，可兼容Antelope文件格式，但此文件格式有两种新的行数据格式 新的两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在Off Page中。 Compressed行记录格式，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。

Compressed行记录格式

数据格式与Dynamic行记录格式相同。 但是与Dynamic行记录格式相比，会更节省页空间

Dynamic行记录格式

支持3072字节的键前缀。且此长度可以通过innodb_large_prefix参数启动或者关闭，且默认开启。 与COMPACT行记录格式的差别在对待超长字段的溢出处理上，dynamic会把所有的TEXT和BLOB字段都存储到off-page中。 且定长字段如果超过768字节，也会被当作变长字段处理。 innodb_large_prefix可以增加索引的最大长度。

Redo log

存储的 Redo Log buffer 刷到磁盘的数据。

主要线程

Master Thread

包含了几种循环，分别是： loop（主循环）、 background loop（后台循环）、 flush loop（刷新循环） suspend loop（暂停循环） 且该线程优先级很高，最核心的一个后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。 包括脏页刷新（Page Cleaner）、合并插入缓冲、Undo 页的回收等。

Loop

主循环的 每秒操作与每10秒操作并不是绝对精准的， 而是每10次小循环后，会有一个特殊操作 每次小循环会尽量在1秒内完成操作。 然后开始执行特殊操作。

每秒钟操作

为了保证每次日志都会写入 redo log file ，InnoDB 引擎通过 Force Log At Commit 机制实现事务持久性， 通过 innodb_flush_log_at_commit 参数控制，默认参数为 1，具体参考 Redo Log 部分详解。

1. 处理redo log

将 redo log file 从 redo log buffer 刷新到磁盘。每秒都会执行，无论事务是否提交。

2. 合并insert buffer

合并插入缓存，并不保证每秒都执行。InnodDB 存储引擎会检查前一秒的 IO << 5%innodb_io_capacity，若是则执行，反之不执行。

3. 刷新 dirty page

刷新 dirty page 到磁盘，并不保证每秒都执行。 InnoDB 存储引擎会去检查当前 dirty page 比例并标记为 buf_get_modified_ratio_pct，当 buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct 时，会刷新一定数量 dirty page 到磁盘。 具体刷新数量不同，以前版本是 100。 在 1.0 之后，可以通过 innodb_adaptive_flushing 参数为 ON 打开状态， InnoDB 存储引擎会调用 buf_flush_get_desired_flush_rate 函数根据生产 redo 日志速度来决定最合适的数量。

4. 如果当前没有活动用户，切换到 background loop。

每 10 秒操作

与小循环相比， 回收undo页将会在大循环中进行

每秒操作的前三步骤

4. 回收 undo 页

回收undo页 每 10 秒都会执行，InnoDB 会检查 undo page 是否还需要，如果所有事务都不需要 undo page则回收。回收数量由 innodb_purge_batch_size 参数决定。

5. 刷新 innodb_io_capacity 的 100% 或 10% 到磁盘，每 10 秒都会执行。InnoDB 存储引擎会检查

background loop

flush loop

suspend loop

IO Thread

IO Thread主要负责IO请求的回调处理。 在InnoDB存储引擎中大量使用AIO处理IO请求。 InnoDB1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO Thread。 Linux平台下，IO Thread数量无法调整，但是Window平台可以通过innodb_file_io_threads参数来增大IO Thread数量。 在InnoDB1.0.x版本后，read、write线程分别增加到了4个，并且不再使用innodb_file_io_threads参数设置。 分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置。 ...

使用AIO

read IO Thread

write IO Thread

insert buffer IO Thread

log IO Thread

Purge Thread

事务被提交后，就不再需要该事务的undo log，由Purge Thread来回收已经使用并分配的undo页。再InnoDB1.1版本之前，Purge操作尽在InnoDB存储殷勤的Master Thread 中完成。从InnoDB1.1版本开始，Purge操作由单独的线程完成，减轻Master Thread的工作，提高CPU使用率，以及提升存储引擎的性能。可以在配置文件中添加命令启动独立的Purge Thread：innodb_purge_threads=1 而InnoDB1.1版本时，将此参数设置为大于1时，启动时仍然会将其设置为1，并给与错误提示。之后的版本才开始支持多个Purge Thread，加快undo页的回收。且由于Purge Thread需要离散读取undo页,这样能更进一步利用磁盘的随即读取性能。

Page Cleaner Thread

Page Cleaner Thread时InnoDB1.2.x版本引入的。将之前版本中脏页刷新操作放入单独线程中处理，依旧为了减轻Master Thread的工作即对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

流程图

日志

Error Log

1. 记录启动、运行以及停止mysqld时出现的问题 2. 错误信息 3. 警告和正确信息，可以通过警告信息日志优化或者排查数据库出现的问题。 log_error可以指定错误日志保存地址。

Slow Query Log

慢查询日志 默认情况下关闭，需要手动配置slow_query_log变量开启。 slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。 log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log...

General Query Log

记录了所有对MySQL的请求信息，无论是否正确执行。 记录建立的客户端连接和执行的所有语句，信息量很大，导致 IO 开销很大。 生产环境在高并发情况建议关闭。 与慢查询日志相似，可以将此日志记录到表中，可切换存储引擎，。

Slave Relay Log

记录的是主从关系中，从主库同步过来的数据。属于一种二进制日志。

Binary Log

记录所有更改数据（DDL，DML）的语句，但不包括 DQL 语句，常用于主从复制或数据库恢复。 二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。 修改操作如未对数据库有修改，也会被记录下来。 二进制日志文件默认不启动，启动后性能会有损耗，但是可以加快启动时数据库的恢复速度。 二进制文件通过bin_log.index进行索引，主要保存二进制文件序号。 bin_cache...

恢复

审计

可以通过二进制文件中记录的数据修改情况，或者statement格式的逻辑语句进行分析，统计，判断是否存在注入攻击。

InnoDB存储引擎层

相关知识点

MVCC

Multi-Version Concurrency Control 多版本并发控制 同一份数据临时保留多版本的一种方式，进而实现并发控制。 普通 select 都是使用 MVCC 机制读取的 Undo Log 中已经提交的数据，非阻塞快照读。（？） MVCC 行记录的隐藏字段参考快照读的 ID 字段 与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control) 读不加锁，读写不冲突

Current Read

读取的是记录的最新版本，加锁保证事务隔离性，阻塞其他事务同时改动相同记录，避免出现数据安全问题。

SQL 语句

select .. for update

select ... lock in share mode

insert

update

delete

实现方式

Next-Key 临键锁

行记录锁 + Gap 间隙锁 可以理解为一种特殊的间隙锁，用来解决幻读。

Gap间隙锁

Gap 间隙锁只有在 ReadRepeatable、Serializable 隔离级别时才有，就是锁定范围空间内的数据，防止新数据插入，避免出现幻读。 假如锁定 id > 3 的数据，id 此时有 3，4，5。那么4，5 和后边的数字都会被锁定。像6，7...... 。如果不锁定，当加入 id= 6 的数据，就会出现幻读，此时间隙锁的出现避免了此类问题。

行记录锁

对主键或唯一索引，如果 select 查询时 where 条件全部精确命中（=或者in），这种场景本身就不会出现幻读，所以只会加 Record Locks（行记录锁）。

Snapshot Read

又称一致性读，读取的是 Undo Log 中已提交的数据（MVCC 的版本记录），可能是数据的历史版本。No-Locking 非阻塞读取操作。

SQL语句

基本的 select ... 语句

快照生成

Read Commited

每次 select 都生成一个快照读。

Read Repeatable

开启事务后第一个 select 语句才是快照读的地方，而不是一开启事务就快照读。 http://www.zsythink.net/archives/1436

实现方式

行数据隐藏列

Undo Log 包含每行的数据和 DB_TRX_ID，DB_ROLL_PRT，DB_ROW_ID insert undo log: 只在事务回滚时需要, 事务提交就可以删掉了。 update undo log: 包括 update 和 delete，回滚和快照读都需要。 DB_TRX_ID: 6字节 DB_TRX_ID 字段，表示最后更新的事务 ID（update，delete，insert）。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。 DB_ROLL_PTR: 7字节回滚指针，指向前一个版本的 Undo Log 记录，组成 Undo 链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。...

示意图

表锁

表锁由 MySQL 服务器实现，无论使用什么存储引擎，都可以使用。 表锁不仅实现和使用都很简单，而且占用的系统资源少，所以在很多存储引擎中使用，如 MyISAM、MEMORY、MERGE 等，MyISAM 存储引擎几乎完全依赖 MySQL 服务器提供的表锁机制，查询自动加表级读锁，更新自动加表级写锁，以此来解决可能的并发问题。但是表锁的粒度太粗，导致数据库的并发性能降低。 关于表锁，我们要了解它的加锁和解锁原则，要注意的是它使用的是一次封锁技术，也就是说，我们会在会话开始的地方使用 lock 命令将后面所有要用到的表加上锁，在锁释放之前，我们只能访问这些加锁的表，不能访问其他的表，最后通过 unlock tables 释放所有表锁。这样的好处是，不会发生死锁！所以我们在 MyISAM 存储引擎中，是不可能看到死锁场景的。

加表锁

读锁

lock table <<table_name> read; 规则与java中JUC包中实现的读写锁特性相似： 1. 线程获取读锁后，不影响其他线程获取读锁，即其他会话都可以持有读锁。 2. 读锁未释放前，不会有线程可以获取到写锁，读写操作双方在此场景下互斥。 3. 只有读锁完全释放，才可以申请写锁。

允许多个会话同时持有读锁

持有读锁的会话可以读表，但不能写表

其他会话就算没有给表加读锁，也是可以读表的，但是不能写表

其他会话申请该表写锁时会阻塞，直到锁释放。

写锁

lock table <<table_name> write;

持有写锁的会话既可以读表，也可以写表

只有持有写锁的会话才可以访问该表，其他会话访问该表会被阻塞，直到锁释放

其他会话无论申请该表的读锁或写锁，都会阻塞，直到锁释放

释放表锁

unlock tables;

使用 UNLOCK TABLES 语句可以显示释放表锁

如果会话在持有表锁的情况下执行 LOCK TABLES 语句，将会释放该会话之前持有的锁

如果会话在持有表锁的情况下执行 START TRANSACTION 或 BEGIN 开启一个事务，将会释放该会话之前持有的锁

如果会话连接断开，将会释放该会话所有的锁

示例

行锁

读锁（S锁，共享锁）

SELECT ... LOCK IN SHARE MODE

写锁（X 锁，排他锁）

SELECT ... FOR UPDATE

常见的 INSERT、UPDATE、DELETE 会自动对操作的数据行加写锁

实现

innoDB索引

简单加锁流程

加锁语句

主键加 X 锁，id 为 students 表的主键，执行语句为： mysql> update students set score = 100 where id = 49;

具体过程

id 为 students 表的主键，name 为 students 表的二级索引，执行语句为： mysql> update students set score = 100 where name = 'Tom'; 当执行以上语句时，InnoDB 会先定位到 name 二级索引，在 name= 'Tom' 这个索引上加上一把 X 锁， 同时通过 name = 'Tom' 二级索引定位到 id = 49 的主键索引，并在 id = 49 的主键索引再加上一把 X 锁。

复杂加锁流程

加锁语句

当 score 为二级索引时，执行语句为： mysql> update students set level = 3 where score >= 60;

具体过程

从图中可以看到当 update 语句被发给 MySQL 后，MySQL Server 会根据 where 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁（Current Read），待 MySQL Server 收到这条加锁的记录之后，会再发起一个 update 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。 因此，MySQL 在操作多条记录时 InnoDB 与 MySQL Server 的交互是一条一条进行的，加锁也是一条一条依次进行的，先对一条满足条件的记录加锁，返回给 MySQL Server，做一些 DML 操作，然后在读取下一条加锁，直至读取完毕。理解这一点，对我们后面分析复杂 SQL 语句的加锁过程将很有帮助。

对比

总结

开销

表锁：开销小，加锁速度快。 行锁：开销大，加锁速度慢。

粒度

表锁：粒度为整个表，并发高时，冲突较大，并发度较低 行锁：粒度为索引页，并发高时，冲突较小，并发度较高

发生死锁

表锁：由于粒度较大，基本不会出现死锁 行锁：粒度较小，如果业务处理不当，出现死锁的概率会很大

Lock概念

lock_mode

表级锁

意向锁

表锁锁定了整张表，而行锁是锁定表中的某条记录，它们俩锁定的范围有交集，因此表锁和行锁之间是有冲突的。 譬如某个表有 10000 条记录，其中有一条记录加了锁，如果这个时候系统需要对该表加表锁，为了判断是否能加这个表锁，系统需要遍历表中的所有 10000 条记录，看看是不是某条记录被加锁，如果有锁，则不允许加表锁，显然这是很低效的一种方法，为了方便检测表锁和行锁的冲突，从而引入了意向锁。 意向锁为表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。 当事务试图读或写某一条记录时，会先在表上加上意向锁，然后才在要操作的记录上加上读锁或写锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。 意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

LOCK_IS

IS(读意向锁)

当前事务视图读取一条数据，现在该表上加上读意向锁

LOCK_IX

IX(写意向锁)

当事务视图读一条记录时，现在该表上加上写意向锁

LOCK_AUTO_INC

也成为AI(自增所，一种特殊的表级锁) 当插入的表中有自增列（AUTO_INCREMENT）的时候可能会遇到。 当插入表中有自增列时，数据库需要自动生成自增值，在生成之前，它会先为该表加 AUTO_INC 表锁，其他事务的插入操作阻塞，这样保证生成的自增值肯定是唯一的。

innodb_autoinc_lock_mode

AUTO_INC 表锁会导致并发插入的效率降低。 为了提高插入的并发性，MySQL 从 5.1.22 版本开始，引入了一种可选的轻量级锁（mutex）机制来代替 AUTO_INC 锁。 通过参数 innodb_autoinc_lock_mode 控制分配自增值时的并发策略。

traditional lock mode 使用传统的AUTO_INC表锁，并发性能差

consecutive lock mode MySQL默认采用此方式，这种方式

interleaved lock mode 全部都用轻量级锁mutex，并发性能最高，缺点时复制(replication) 主从可能不一致。

AUTO_INC互斥

AUTO_INC锁互不兼容，同一张表只允许一个AUTO_INC锁存在。

解锁

自增锁不遵循二段锁协议，它并不是事务结束时释放，而是在 INSERT 语句执行结束时释放，提高并发插入性能。

自增中断

自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

行级锁

/* Basic lock modes */ enum lock_mode { LOCK_IS = 0, /* intention shared */ LOCK_IX, /* intention exclusive */ LOCK_S, /* shared */ LOCK_X, /* exclusive */ LOCK_AUTO_INC, /* locks the auto-inc counter of a table in an exclusive mode*/ ... };

读锁

加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。

写锁

对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。（注意：这里说的读都是当前读，快照读是无需加锁的，记录上无论有没有锁，都可以快照读）

兼容性

意向锁之间互不冲突

S 锁只和 S/IS 锁兼容，和其他锁都冲突

X 锁和其他所有锁都冲突

AI 锁只和意向锁兼容

lock_type

Record Lock

记录锁，行锁 只有是唯一索引或者主键且精准匹配时才会加记录锁。

示例

id 为主键，SQL 语句如下： mysql> UPDATE accounts SET level = 100 WHERE id = 5; SQL 语句就会在 id = 5 这条记录上加上记录锁，防止其他事务对 id = 5 这条记录进行修改或删除。记录锁永远都是加在索引上的，就算一个表没有建索引，数据库也会隐式的创建一个索引。 如果 WHERE 条件中指定的列是个二级索引，那么记录锁不仅会加在这个二级索引上，还会加在这个二级索引所对应的聚簇索引上（参考简单加锁流程）

注意事项

1. 如无索引或可用索引，会针对所有数据添加记录锁 如果 SQL 语句无法使用索引时会走主索引实现全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁。 2. 无法通过索引快速过滤时，符合条件记录加锁，不符合条件记录会加锁释放锁 如果一个 WHERE 条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由 MySQL Server 层进行过滤。不过在实际使用过程中，MySQL 做了一些改进，在 MySQL Server 层进行过滤的时候，如果发现不满足，会调用 unlock_row 方法，把不满足条件的记录释放锁（显然这违背了二段锁协议）。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。 3. 更新操作需使用索引 在没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，而且极大的降低了数据库的并发性能，所以说，更新操作一定要记得走索引。

模拟过程

1. 开启事务A 2. 精准查询指定主键5的数据，并加写锁 3. 开启事务B 4. 精准查询指定主键5的数据，并加读锁，执行语句后进入阻塞状态 5. 查询InnoDB锁信息，即可查看锁的持有情况

Precise Modes

LOCK_ORDINARY

Next-Key Lock

邻键锁，锁一条记录及其之间的间隙，Repeatable Read隔离级别使用最多的锁，Read Committed不使用。

改善幻读问题

通过临建锁可以改善幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间 (~ ∞,∞] 的数据。 需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

示例

该表id为主键，age为非唯一索引列。潜在临键锁： (-∞, 10] (10, 24] (24, 32] (32, 45] (43, +∞] 开启事务执行SQL： 1. 开启事务A； 2. 对非唯一索引列添加写锁：...

模拟

根据兼容矩阵，Next-Key 锁和记录锁、Next-Key 锁或插入意向锁冲突，但是貌似很难制造 Next-Key 锁和记录锁冲突的场景，也很难制造 Next-Key 锁和 Next-Key 锁冲突的场景。 所以还是用 Next-Key 锁和插入意向锁冲突的例子，和间隙锁的例子几乎一样。

LOCK_GAP

Gap Lock

间隙锁，基于非唯一索引。 锁定一段范围的非唯一索引记录。 Repeatable Read 隔离级别使用较多的锁，Read Committed 一般不使用。 间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。 有时候又称为范围锁（Range Locks），这个范围可以跨一个索引记录，多个索引记录，甚至是空的。 使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。

示例

表accounts中id为主键 mysql> UPDATE accounts SET level = 100 WHERE id = 5; Repeatable Read 隔离级别下，在 id = 5 的前后两个索引加上间隙锁，防止其他事务插入，造成幻读。 Read Committed 隔离级别，只加记录锁，防止别的事务修改 id = 5 的行数。 若id = 5的记录不存在： Read Committed 级别，不加任何锁。 Repeatable Read 级别加间隙锁。 ...

模拟

间隙锁只和插入意向锁冲突，而且是先加间隙锁，然后加插入意向锁时才会冲突

innodb_locks_unsafe_for_binlog

切换到 Read Committed 隔离级别，或者开启选项 innodb_locks_unsafe_for_binlog 来避免GAP锁。这时候只有在检查外键约束或者 duplicate key 检查时才会使用到 Gap Lock。

LOCK_INSERT_INTENSION

是一种特殊的间隙锁（所以有的地方把它简写成 II GAP），这个锁表示插入的意向，只有在 INSERT 的时候才会有这个锁，属于 Lock_GAP 的一种特例。 这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混淆了。插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在上面的例子中，id = 1 和 id = 5 之间如果有两个事务要同时分别插入 id = 2 和 id = 3 是没问题的，虽然两个事务都会在 id = 1 和 id = 5 之间加上插入意向锁，但是不会冲突。 插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，那么间隙锁是如何防止幻读的呢？正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。...

LOCK_REC_NOT_GAP

之所记录，不锁间隙

兼容矩阵

在已有任意锁时，可以再申请插入意向锁

已有插入意向锁时，不兼容间隙锁与临键锁

移除插入意向锁

间隙锁兼容除插入意向锁以外的任意锁

记录锁与临键锁互斥，且与自身互斥

悲观锁与乐观锁

Optimistic Lock

乐观锁 总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。 适合读多写少的业务。

实现方式

版本号

1. 获取version信息 取出记录，并获取当前 version 版本号（也可是使用时间戳） 2. 更新时比较version 更新时附带取出的 version ，执行更新，如果 version 不对，更新失败 3. 更新时也更新version 核心 SQL update table set name = '****', version = version + 1 where id = #{id} and version = #{version}; ...

CAS

CAS 即 Compare And Swap 的缩写， 翻译成中文就是比较并交换，其作用是让 CPU 比较内存中某个值是否和预期的值相同，如果相同则将这个值更新为新值，不相同则不做更新，也就是 CAS 是原子性的操作(读和写两者同时具有原子性)，其实现方式是通过借助 C/C++ 调用 CPU 指令完成的，所以效率很高。 public boolean compareAndSwap(int value, int expect, int update) { // 如果内存中的值value和期望值expect一样则将值更新为新值update if (value == expect) { value = update; return true; } else {...

1. 读取内存位置 V

2. 进行比较原预期值 A

3. 拟写入新值 B

ABA问题

如果另一个线程修改 V 值假设原来是 A，先修改成 B，再修改回成 A。当前线程的CAS操作无法分辨当前 V 值是否发生过变化。 小明在提款机，提取了50元，因为提款机问题，有两个线程，同时把余额从100变为50 线程1（提款机）：获取当前值100，期望更新为50， 线程2（提款机）：获取当前值100，期望更新为50， 线程1成功执行，线程2某种原因block了，这时，某人给小明汇款50 线程3（默认）：获取当前值50，期望更新为100， 这时候线程3成功执行，余额变为100， 线程2从Block中恢复，获取到的也是100，compare之后，继续更新余额为50！！！ 此时可以看到，实际余额应该为100（100-50+50），但是实际上变为了50（100-50+50-50）这就是ABA问题带来的成功提交。

解决方式

java中juc并发包中提供了一系列的Atomic相关类型。 可以保证每次操作都是原子性的，但是并不能保证其不会出现ABA的问题。 如果需要保证不会出现ABA问题，就需要使用AtomicStampedReference，将相关的引用使用AtomicStampedReference包裹起来。 它的实现是额外维护了一个单调递增的stamp字段，相当于为引用创建了一个版本号，引用每次更新，都会更新这个stamp字段。

自旋问题

Java 的 java.util.concurrent.atomic 相关类是 CAS 实现，并通过自旋 CAS 尝试获得锁。 CAS 自旋锁适用于锁使用者保持锁时间比较短的情况中，因为自旋锁使用者一般保持锁的时间很短，所以才选择自旋而不是睡眠。 而如果竞争比较激烈，那么势必会一直占用CPU试图更新Atomic的值，让CPU占用高，但实际执行效率并不高。

只能保证一个共享变量的原子操作

解决办法是：把多个共享变量合并成一个共享变量。AtomicReference 类来保证引用对象之间的原子性。

Pessimistic Lock

悲观锁 总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。 适合写多读少，或者数据安全性较高的业务。

实现方式

Java 的 synchronized 和 ReentrantLock

MySQL 的锁

事务

事务隔离特性

Atomic

原子性 一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。 要么全部完成，要么全部不完成。

Consistency

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。 一个事务单元需要提交之后才会被其他事务可见

Isolution

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括未提交读（Read uncommitted）、提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。 并发事务之间不会互相影响，设立了不同程度的隔离级别，通过适度的破坏一致性，得以提高性能

Durability

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。 事务提交后即持久化到磁盘不会丢失

事务隔离级别标准

MySQL支持ANSI标准提出的四种事务级别。 分别是Read Uncommitted、Read Committed、Repeatable Read、Serialized。 Oracle则只实现量ANSI种的两个级别Read Committed、Serialized。

Read Uncommitted

简称RU， 事务 1 更改数据，在未提交时，事务 2 可以看到数据修改后的状态。 不允许第一类更新丢失。允许脏读，不隔离事务 事务读不阻塞其他事务读和写，事务写阻塞其他事务写但不阻塞读。 读不加锁，写事务必须加 "持续 X 锁"，否则会出现第一类丢失更新问题

Read Committed

简称RC 事务 1 未提交时，事务 2 无法看到事务 1 的修改。当事务 1 提交时，不管事务 2是否提交，都可以查看到事务 1 已提交的状态。 事务读不会阻塞其他事务读和写，事务写会阻塞其他事务读和写 不允许脏读，允许不可重复读 通过对写操作加 "持续 X 锁"，对读操作加 "临时 S 锁" 实现，不会出现脏读。 每次SELECT时生成快照。SELECT 可以看到其它已提交事务的修改

Repeatable Read

MySQL 在 RR 隔离级别下，普通的 SELECT 语句只是快照读，没有任何的加锁，和标准的 RR 是不一样的。 所以MySQL下是会出现幻读的，但是什么能够保证每次读取数据是一致的？ MVCC，检查隐藏列DB_TRX_ID，Undo Log，一致性非锁定读，数据可见性检查。 DB_TRX_ID是在什么时候生成的。 1. 干扰事务B比事务A先启动， 事务A查询数据，得到列表1； 事务B插入数据后，提交事务；（此时事务B提交的数据的TRX_ID是事务B的事务ID，TRX_ID > 事务A的ID） 事务A查询数据，得到列表2，此时得到的列表1和列表2是一致的。 事务A更新事务B插入的数据后，查询数据，得到列表3。...

Serialized

所有的增删改查串行执行 默认会为所有数据隐式加上锁语句，防止其他事务干扰查询结果。 SERIALIZABLE 正是对所有事务都加 X锁才杜绝了幻读，但很多场景下我们的业务sql并不会存在幻读的风险。 SERIALIZABLE 的一刀切虽然事务绝对安全，但性能会有很多不必要的损失（可能没有想象中的那么严重）。

加锁协议

一级封锁协议

事务 T 在修改数据 R 之前必须先对其加 X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。 可防止丢失更新，并保证事务T是可恢复的。

二级封锁协议

一级封锁协议加上事务 T 在读取数据 R 之前必须先对其加 S 锁，读完后即可释放 S 锁（瞬间 S 锁）。 二级封锁协议除防止了丢失更新，还可进一步防止读 "脏" 数据。

三级封锁协议

一级封锁协议加上事务 T 在读取数据 R 之前必须先对其加 S 锁，直到事务结束才释放。 三级封锁协议除防止了丢失更新和不读 "脏" 数据外，还进一步防止了不可重复读和覆盖更新。

四级封锁协议

四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对事务中所读取或者更改的数据所在的表加表锁，也就是说，其他事务不能读写该表中的任何数据。

隔离级别配置

查询当前会话 select @@tx_isolution; 查询全局 select @@global.tx_isolution; 设置会话隔离级别 set transaction isolation level << read uncommitted | read committed | repeatable read | serializable>; ...

查询

修改

隔离级别加锁示例

表名 students，id 为主键，no 为二级唯一索引，name 和 age 为二级非唯一索引，score 无索引。

分支主题

聚簇索引

命中场景 SQL: UPDATE students SET score = 100 WHERE id = 15; 在RC和RR隔离级别下，都会对id=15这个聚簇索引加X锁。 查询未命中场景 SQL: UPDATE students SET score = 100 WHERE id = 16; 在RC隔离级别下，不加锁；在RR隔离级别下，加GAP锁

二级唯一索引

查询命中 SQL: UPDATE students SET score = 100 WHERE no='S0003'; 在 RC 和 RR 隔离级别下，先给命中的二级唯一索引加 X 锁，找到二级索引保存的主键索引位置，给主键索引再加X锁。 如果不给主键加锁，会出现并发问题。 查询未命中 SQL:UPDATE students SET score = 100 WHERE no = 'S0008'; RC隔离级别下，不加锁...

二级非唯一索引

查询命中 SQL:UPDATE students SET score = 100 WHERE name = 'Tom'; RC隔离级别下，先给二级索引添加X锁，然后给对应主键添加X锁。 RR隔离级别下，先给二级非唯一索引加 X 锁，并在按照『左开右闭』原则依次加上 Gap 锁，然后在对应的主键加 X 锁。 右图(Tom,37)和前面Gap锁合起来是Next-Key锁，该锁加在(Tom,37)索引上，同理(Tom,49)索引上也右一把Next-Key锁。 共五把锁，最有一Gap锁，两个Tom索引上Next-Key锁，两个主键上的X锁(也可成为Record Lock)。 ps: X锁基于lock_mode，Record Lock基于lock_type。...

无索引

SQL:UPDATE students SET score = 100 WHERE score = 22; 在 RC 隔离级别下，WHERE 条件不能指定索引只能走聚簇索引，对表中所有记录进行扫描，然后全部加上 X 锁 在 RR 隔离级别下，主键索引加 X 锁和 Gap 锁。 在实际过程中，在 RC 隔离级别下，MySQL Server 过滤条件发现不满足后，会调用 unlock_row 方法，将不满足条件的记录锁释放掉。但是每条记录加锁操作还是存在。在 RR隔离级别下，需要设置 innodb_locks_unsafe_for_binlog 参数，这样会提前释放锁，并且不加 GAP 锁。这就是 Semi-Consistent Read（半一致读）。

范围查询

聚簇索引

SQL:UPDATE students SET score = 100 WHERE id <<= 20; RC隔离级别下，在范围内给id加X锁。 在RR级别下，对范围查询， 如果WHERE条件是id <<= N，那么N后一条记录也会被加上Next-Key锁； 如果条件是id << N，那么N条记录会被加上Next-Key锁。 另外，如果WHERE条件是id >=N，只会给N加上记录锁，以及给比N大的记录加锁，不会给N前一条记录加锁； 如果条件是id > N，也不会锁前一条记录，N这条记录也不会锁。

二级非唯一索引

SQL:UPDATE students SET score = 100 WHERE age <<=23; 在 RC 隔离级别下，首先对范围内的二级非唯一索引加 X 锁，然后根据二级索引叶子节点存储的主键索引位置，找到范围内的所有主键索引并加 X 锁。 在 RR 隔离级别下，加锁规则和聚簇索引一样。唯一不同的是，二级非唯一索引因为可能会存在多个，多以加 Next-Key 锁时，如果边界有两个相同的，只在第一个加锁。 ps: 注意 RR 级别 24 的 Next-Key 锁只在第一条记录加锁

使用事务隔离级别带来的问题

性能

四种隔离级别，读未提交、读已提交、可重复读、序列化 性能上由于并发性的关系，依次从高到低。 但是可重复读与序列化级别之间的性能可能分界点没有那么明显，甚至可能出现Serialized性能高于Repeatable的情况。 序列化性能低于可重复读的结论现在看来并不严谨。

数据

公共条件

分支主题

Dirty Read

仅仅从程序运行的角度来看： 事务 1 执行成功之后，A 成功转了 100 元到 B 账户，这时 A 余额还剩 900 元，B 余额剩 1100，总和为 2000； 事务 2 的统计求和算出来的结果是 A + B = 1900。 这个就是上面说的，没有提交事务的数据被其他事务读取到了，这叫做脏读。 可以使用Read Committed级别解决。

Unrepeatable Read

『不可重复读』是不一定可重读，意味着前后两次读取的数据可能会发生变化。不是一定不可重读。 事务 2 第一次获取 A 的账户余额为 1000 元，这个时候事务 1 对 A 的账户余额进行操作减去了 100 元，事务 2 再去查询 A 的账户余额发现变成了 900 元，这样就出现了同一个事务对同一条记录读取两遍，两次读出来的结果竟然不一样。 不可重复读和脏读的区别在于，脏读是读取了另一个事务未提交的修改，而不可重复读是读取了另一个事务提交之后的修改，本质上都是其他事务的修改影响了本事务的读取。 因为其他事务 Update 造成的数据变化。 使用Repeatable Read即可解决不可重复读的问题。

Phantom Read

幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。 更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。 或者也可以使用update更新一个原本结果集中不存在的数据，结果成功了。 并且重新查询之后，可以读取到这条更新之后的数据。 幻读有两种解决方式： 1. RR下，使用加锁语句防止幻读发生 SELECT ... FOR UPDATE 这也是 SERIALIZABLE 隔离级别下会隐式为你做的事情 2. 使用SERIALIZABLE隔离级别。...

Lost Update

提交覆盖(一类丢失更新)

假设两个事务同时对 A 的余额进行修改，他们都查出 A 的当前余额为 1000，然后事务 2 修改 A 的余额，将 A 的余额加 100 变成 1100 并提交，这个时候 A 的余额应该是 1100，但是这个时候事务 1 并不知道 A 的余额已经变动，而是继续在 1000 的基础上进行减 100 的操作并提交事务，就这样事务 2 的提交被覆盖掉了，事务 1 提交之后 A 的余额变成了 900 元。这就是说事务 1 的提交覆盖了事务 2 的提交，事务 2 的 UPDATE 操作完全丢失了 上图是经典的丢失更新问题，由于最后一步是提交操作，所以又叫提交覆盖，也叫 Read-Modify-Write 问题

回滚覆盖(二类丢失更新)

该图事务1，缺失了一个 Update account SET balance = 1000 - 100 WHERE name = 'A', 此时 balance = 900 操作和提交覆盖情景基本上一样，只是最后一步事务 1 的提交变成了回滚，这样 A 的余额恢复成原始值 1000，事务 2 的 UPDATE 操作也完全没有生效，想一想这真的是非常可怕，一个事务的回滚操作竟然影响了另一个正常提交的事务。 回滚覆盖问题可以说是程序 bug 了，因此几乎所有的数据库都不允许回滚覆盖。

隔离级别关系

幻读在 RR 隔离级别，通过 Gap Lock 锁机制解决了幻读现象，但并没有完全解决，对于 Update 操作依然存在幻读问题。具体参考：https://juejin.im/post/5c9040e95188252d92095a9e http://www.zsythink.net/archives/1216 因为 MySQL 的实现和 ANSI-SQL 标准之间的差异，在标准的传统实现中，RR 隔离级别是使用持续的 X 锁和持续的 S 锁来实现的，由于是持续的 S 锁，所以避免了其他事务有写操作，也就不存在提交覆盖问题。 但是 MySQL 在 RR 隔离级别下，普通的 SELECT 语句只是快照读，没有任何的加锁，和标准的 RR 是不一样的。 如果要让 MySQL 在 RR 隔离级别下不发生提交覆盖，可以使用 SELECT ... LOCK IN SHARE MODE 或者 SELECT ... FOR UPDATE 。 ...

分支主题

一致性非锁定读

如果被读的数据行被加了排他锁，在读取这行数据的时候并不会等待锁释放，而是读取该行的一个快照数据。 之所以称为非锁定读，因为不需要等待被访问行的X锁的释放。快照数据是指修改行之前的数据版本，该实现通过 Undo 段来完成。非锁定读的方式极大提高了数据库的并发性。在 InnoDB 存储引擎中，这是默认的读取方式。

性能优化

索引层优化

类型

普通索引

创建 CREATE INDEX <<index_name> ON <<table_name> (<<column_names>); 修改 ALTER TABLE <<table_name> ADD INDEX <<index_name> (<<column_names>); 删除 ALTER TABLE <<table_name> DROP INDEX <<index_name>;

唯一索引

创建 CREATE UNIQUE INDEX <<index_name> ON <<table_name> (<<column_names>); 修改 ALTER TABLE <<table_name> ADD UNIQUE INDEX <<index_name> (<<column_names>); 删除 ALTER TABLE <<table_name> DROP INDEX <<index_name>;

全文索引

创建 CREATE FULLTEXT INDEX <<index_name> ON <<table_name> (<<column_names>); 修改 ALTER TABLE <<table_name> ADD FULLTEXT INDEX <<index_name> (<<column_names>); 删除 ALTER TABLE <<table_name> DROP INDEX <<index_name>;

组合索引

创建 CREATE INDEX <<index_name> ON <<table_name> (<<column_name1>,<<column_name2>, ...); 修改 ALTER TABLE <<table_name> ADD INDEX <<index_name> (<<column_name1>,<<column_name2>, ...); 删除 ALTER TABLE <<table_name> DROP INDEX <<index_name>; 组合索引的匹配原则：...

索引条件图示

index key

MySQL 用来确定扫描的数据范围，就是可以利用的索引范围，因此只在索引第一次搜索时使用，一次判断即可。 Index Key 用来确定范围，分为上边界和下边界。

Last Key

上边界 MySQL 利用=、=<<、<< 来确定上边界，利用最左前缀匹配原则，判断第一个索引键值在 WHERE 条件中是否存在，如果存在则判断比较符合，如果是（=、=<<）其中一种，加入上边界的界定，继续判断下一个索引键值，如果存在且是（<<），则将该索引键值加入上边界的界定，停止匹配。如果不存在，直接停止上边界匹配。 示例 索引条件：idx_c1_c2_c3(c1,c2,c3) WHERE 条件：where c1<<=1 and c2=2 and c3<<3 ...

First Key

下边界 MySQL 利用=、>=、> 来确定下边界，利用最左前缀匹配原则，判断第一个索引键值在 WHERE 条件中是否存在，如果存在则判断比较符合，如果是（=、>=）其中一种，加入下边界的界定，继续判断下一个索引键值，如果存在且是（>），则将该索引键值加入下边界的界定，停止匹配。如果不存在，直接停止下边界匹配。 示例 索引条件：idx_c1_c2_c3(c1,c2,c3) WHERE 条件：where c1>=1 and c2>2 and c3=1 ...

index filter

MySQL 用于过滤索引查询范围中不满足查询条件的记录，因此对于索引范围中的每一条记录，均需要与 Index Filter 进行对比，若不满足 Index Filter 则直接丢弃，继续读取索引下一条记录。 MySQL会把这个过滤条件下推到存储引擎（ICP），不管是Index Key还是Index Filter对应的列必须添加了索引。 示例 索引条件： idex_c1_c2_c3 WHERE 条件： where c1>=1 and c2<<=2 and c3 =1 index key --> c1 index filter --> c2,c3

table filter

Table Filter 是 WHERE 条件最后一道防线，用于过滤通过前面索引的层层考验的记录，此时的记录已经满足了 Index Key 的范围，并且符合 Index Filter 的条件，存储引擎通过回表读取了完整的记录，判断整条记录是否满足 Table Filter 中的查询条件，同样的，若不满足，跳过当前记录，继续读取索引的下一条记录，若满足，则返回记录，此记录满足了 WHERE 的所有条件，可以返回数据。

ICP过程

1. 根据 Index Key 确定数据扫描范围 2. 符合 Index Filter 的索引条件的下推到存储引擎 3. 存储引擎根据索引来过滤掉不符合 WHERE 条件的记录，然后把最终数据返回给 MySQL Server 4. MySQL Server 再根据 WHERE 条件来做最后的过滤，最后把数据返回给用户，这个过程减少了二次回表的次数，有效减少了磁盘IO

相关博客

http://www.fordba.com/spend-10-min-to-understand-how-mysql-use-index.html https://www.ezlippi.com/blog/2018/08/mysql-tips.html

EXPALIN命令

SQL 语句前加 EXPLAIN 命令，分析 SQL 语句输出 SELECT 执行的详细信息。 字段具体参考官方解释 https://dev.mysql.com/doc/refman/8.0/en/explain-output.html 具体 SQL 例子可以参考 https://segmentfault.com/a/1190000008131735

SELECT查询标识符，每个SELECT语句都会自动分配一个唯一标识符，id越大，越先查询。 当id相同时，倒序执行。

select_type

表示查询类型 SIMPLE 表示此查询不包含 UNION 查询或子查询 最常见的查询类别 PRIMARY 表示此查询为最外层查询 UNION 表示此查询是 UNION 的第二或随后的查询 DEPENDENT UNION UNION 中的第二个或后面的查询语句，取决于外面的查询...

table

表示查询涉及的表或衍生表，如果查询时对表有别名 这里展示的表是别名后的表名称。

partitions

匹配的分区。

type

表示JOIN类型，判断SQL查询是否高效的重要指标之一，可以判断是全表扫描还是索引。 性能排序从低到高，ALL << index << range ~ index_merge << ref << eq_ref << const << system ALL 表示扫描全表，这个类型的查询是性能最差的查询 index 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。当是这种情况时，Extra 字段会显示 Using index range 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =、<<>、>、>=、<<、<<=、IS...

possible_keys

表示可能选用的索引，虽然再possible_keys中出现，但是并不代表一定会使用该字段中展示的索引。 真正展示使用索引的字段是key字段展示的索引名称。

key

真正使用的索引名称。 如果此字段是空，也代表没有使用任何索引。

key_len

表示查询优化器使用了索引的字节数。 这个字段可以评估组合索引是否完全被使用，或只有最左部分字段被使用到

ref

表示哪个字段或这常数与key一起使用

rows

表示该查询一共扫描了多少行，并不是确定值，是估算出来的。 Musql查询优化器根据统计信息，估算SQL要查找到结果需要扫描读取的数据行数。 仍然可以从此字段判断索引的执行效率情况。

filtered

5.7时引入此字段。 此查询条件锁过滤的数据占比。

extra

额外信息 Using index 表示使用索引，如果只有 Using index，说明没有查询到数据表，只用索引表就完成了这个查询，这个叫索引覆盖。 Using index condition 表示没有达到索引覆盖，查询语句通过索引过滤出一部分记录，但是查询内容超出了索引范围，需要读取完整的数据行。 ICP（Index Condition Pushdown）索引下推，MySQL 5.6 新功能 原理 查询过程中，直接在查询引擎层的API获取数据的时候实现"非直接索引"过滤条件的筛选，而不是查询引擎层查询出来之后在Server层筛选。...

语法细节优化

SELECT

SELECT 后边要查询的字段尽量都建立索引，如果有一个字段未建立索引，就会需要使用主键查询对应的数据行。 如果查询的字段全部都在使用的索引中，引擎会直接在索引表查询而不访问原始数据。

LIKE 以通配符开头的，会造成全表扫描。例如：LIKE '%mysql%'。如果第一个 % 没有，该字段又建立索引，则会使用索引。

JOIN ON

对于JOIN语句匹配关系，ON设计到的字段都应在同一个索引中。

ORDER BY

ORDER BY 将查询结果按照某个字段排序时，如果字段没有索引，执行计划将会查询数据后使用外部排序 (将数据分批读取到内存中，使用内部排序，最后合并排序结果)

WHERE

符合索引遵循最左前缀匹配原则。 常用查询的字段可以考虑建索引。

OR条件必须两边字段都有索引时，才会使用索引，单边索引会造成全表扫描。 MySQL只能使用一个二级索引。 如果OR条件涉及到两个字段时，几乎无法避免全表扫描； 如果只有设计到一个字段，可以使用IN代替这个OR语句； 应尽量避免使用OR语句。

COUNT

关于COUNT(*)与COUNT(1)的选择 https://juejin.cn/post/6844903637307621383

慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句。 具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。 long_query_time 的默认值为 10，意思是运行 10S 以上的语句。 默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

配置方法

只对当前数据库生效，重启失效 查询慢日志开启状态 SHOW VARIABLES LIKE '%slow_query_log%'; 开启慢日志 SET GLOBAL slow_query_log = 1; 永久生效 修改 /etc/my.cnf 配置文件 # 开启慢查询日志 slow_query_log = 1 # 配置慢查询日志文件存储路径 slow_query_log_file = /var/lib/mysql/slow.log ...

分析工具

MySQL 自带分析工具 mysqldumpslow

mysqlsla

Query Profiler

在5.7之后逐渐废弃 MySQL 自带的分析器，可以获取一条 Query 语句在整个执行过程中多种资源的消耗情况，如: CPU、IO、IPC 等。 # 查询 profiling 开启状态 SHOW VARIABLES LIKE '%profiling%'; # 开启 profiling SET profiling = 1;

Performance Schema

用于监视MySQL服务器，且运行时消耗很少的性能。 Performance Schema 是通过内存表的方式来记录的，不使用硬盘存储。 Performance Schema 表被视为本地服务器，对其更改不会被复制或写入二进制文件。 此处只做简单介绍，具体使用请查阅 https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

MySQL 5.7 四个基本的库

mysql 包含权限配置、时间、存储引擎状态、主从信息、日志、时区信息、用户权限配置等 sys 记录临时表使用，请求最多 IO，线程占用最多内存，无用索引等一系列数据 information_schema 数据库元数据抽象分析 performance_schema 内存型数据库，使用 performance_schema 存储引擎，通过事件机制将 MySQL 服务运行状态采集并存储

服务器层优化

Buffer Pool

提高Buffer容量

Buffer Pool 默认为 128 M，理论上扩展为内存的 3/4 或 4/5 显示相关参数 SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%' MySQL 5.7 可以直接修改参数 SET GLOBAL innodb_buffer_pool_size=****** MySQL 5.7 以前需要i修改 my.cnf 文件 innodb_buffer_pool_size = *M

手动内存预热

SELECT count(id) from table_name; 可以编写 init.sql 文件查询表数据，然后配置在 my.cnf 文件 init-file = /mysql/init.sql

自动内存预热

MySQL 5.7 以后默认开启 设置参数 #启动是加载数据恢复MySQL内存数据 SET GLOBAL innodb_buffer_pool_load_at_startup = ON #关闭是dump内存数据用于下次启动时恢复 SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON #终止恢复操作 SET GLOBAL innodb_buffer_pool_load_abort=ON

降低磁盘IO

设置 Redo Log 大小

innodb_log_ﬁle_size 设置成 innodb_buﬀer_pool_size * 0.25

关闭部分与数据不相关的日志

慢查询日志、通用查询日志等

更改 Force Log at Commit 机制

此选项需要慎重选择，宕机时丢失数据量大小和此参数密切相关

提高磁盘读写能力

增加磁盘

比如使用innodb_per_file_tablespace，将不同的表数据放到不同的磁盘上，在通体上增加磁盘带宽。

使用SSD

顺序写的数据速度可以达到N GB/s。

参数优化

内存参数

线程独享

sort_buffer_size

join_buffer_size

read_buffer_size

read_rnd_buffer_size

线程共享

读写线程数参数

innodb_buffer_pool_size

key_buffer_size

tmp_table_size

max_head_table_size

事务日志

innodb_log_file_size

innodb_log_files_in_group

innodb_log_buffer_size

innodb_flush_log_at_trx_commit

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_doublewrite = 1

delay_key_write

innodb_read_io_threads

innodb_io_capacity

innodb_flush_neighbors

sync_binlog

其他参数

expire_logs_days

max_allowed_packet

skip_name_resolve

read_only

skip_slave_start

sql_mode

max_connections

高可用

分布式理论

Consistency

线性一致性

1. 线性一致性也叫做强一致性或原子一致性，除此以外，所有其他的一致性都是弱一致性的特殊情况。所谓强一致性，即复制是同步的，弱一致性，即复制是异步的。 2. 当更新操作完成之后，任何多个后续进程或者线程的访问都会返回最新的更新过的值。这种是对用户最友好的，就是用户上一次写什么，下一次就保证能读到什么。

顺序一致性

单个节点的事件历史在全局历史上看符合程序顺序，且事件历史在各个节点上看全局一致。即不需要和全局时钟下的顺序一致，错的话一起错，对的话一起对。

ZooKeeper实现的一致性

Zookeeper 的所有写操作都通过主节点进行。 从节点复制修改操作，这样所有节点的更新顺序都和主节点相同。 不会出现某个节点的更新顺序与其它节点不同的情况。 但是 Zookeeper 允许客户端从从节点读取数据。 因此如果客户端在读取过程中连接了不同的节点，此时不保证顺序一致性。

弱一致性

系统并不保证进程或者线程的访问都会返回最新的更新过的值。 系统在数据写入成功之后，不承诺立即可以读到最新写入的值，也不会具体的承诺多久之后可以读到。 但会尽可能保证在某个时间级别（比如秒级别）之后，可以让数据达到一致性状态。

最终一致性

弱一致性的特定形式。系统保证在没有后续更新的前提下，系统最终返回上一次更新操作的值。 在没有故障发生的前提下，不一致窗口的时间主要受通信延迟，系统负载和复制副本的个数影响。 在实际的实践中，这5种系统往往会结合使用，以构建一个具有最终一致性的分布式系统。

因果一致性

如果A进程在更新之后向B进程通知更新的完成，那么B的访问操作将会返回更新的值。如果没有因果关系的C进程将会遵循最终一致性的规则。

读己所写一致性

因果一致性的特定形式。一个进程总可以读到自己更新的数据。

会话一致性

读己所写一致性的特定形式。进程在访问存储系统同一个会话内，系统保证该进程读己之所写。

单调读一致性

如果一个进程已经读取到一个特定值，那么该进程不会读取到该值以前的任何值。

单调写一致性

系统保证对同一个进程的写操作串行化。

Consenus

关注的是多个提议者达成共识的过程。比如 Paxos，Raft 共识算法本质上解决的是如何在分布式系统下保证所有节点对某个结果达成共识，其中需要考虑节点宕机，网络时延，网络分区等分布式中各种问题。共识算法通常应用在复制状态机中，比如 etcd，zookeeper，用于构建高可用容错系统。在这种应用情景下，Raft/Paxos 共识算法被用来确保各个复制状态机(节点)的日志是一致的。类似的，区块链中非常重要的一部分也是共识算法，但通常使用是 POW(Proof of Work) 或 POS(Proof of Stake)，这类共识算法通常适合用在公网，去中心化的情形下。 不存在一致性强弱之分!

分布式协议

分布一致性

在分布式系统中，为了保证数据的高可用，通常会降数据保存多个副本，这些副本会放置在不同的物理机上。为了向用户操作提正确的数据结果，我们需要保证不同物理机上的副本是一致的。

分布式事务

分布式事务是指涉及到操作多个数据库的事务。 将单数据库的单事务扩大到了多个库的事务。 为了保证数据的一致性，分布式事务处理的关键是必须有一种方法可以知道事务在所有库中所做的所有操作，提交或回滚事务的决定必须产生统一的结果。 在分布式系统中，各个节点之间在物理上相互独立，通过网络进行沟通和协调。 由于存在事务机制，可以保证每个独立节点上的数据操作可以满足 ACID。 但是，相互独立的节点之间无法准确的知道其他节点中的事务执行情况。 从理论上讲，两台机器理论上无法达到一致的状态。如果想让分布式部署的多台机器中的数据保持一致性，那么就要保证在所有节点的数据写操作，要不全部都执行，要么全部的都不执行。 但是，一台机器在执行本地事务的时候无法知道其他机器中的本地事务的执行结果。所以他也就不知道本次事务到底应该 commit 还是...

推荐框架

JTA

基于XA规范的2PC Java框架

seata

蚂蚁金福开源框架，分为AT\TCC\SAFA\XA，共四种模式。

RocketMQ

通过消息事务机制解耦。

X/Open Specification(XA规范)

XA 就是 X/Open DTP 定义的交易中间件与数据库之间的接口规范（即接口函数），交易中间件用它来通知数据库事务的开始、结束以及提交、回滚等。 XA 接口函数由数据库厂商提供。 2PC与3PC协议都是根据XA协议衍生出来的。

本地事务

一个数据库内部的事务处理，如对多个表的操作，作为本地事务看待。数据库的事务处理对象是本地事务。

全局事务

全局事务，是指分布式事务处理环境中，多个数据库可能需要共同完成一个工作，这个工作即是一个全局事务。 例如，一个事务中可能更新几个不同的数据库。对数据库的操作发生在系统的各处但必须全部被提交或回滚。此时一个数据库对自己内部所做操作的提交不仅依赖本身操作是否成功，还要依赖与全局事务相关的其它数据库的操作是否成功，如果任一数据库的任一操作失败，则参与此事务的所有数据库所做的所有操作都必须回滚。

二阶段提交协议

Two-phase Commitment Protocol(2PC) 二阶段提交协议（Two-phase Commit，即2PC）是常用的分布式事务解决方案，它可以保证在分布式事务中，要么所有参与进程都提交事务，要么都取消事务，即实现 ACID 的原子性（A）。在数据一致性中，它的含义是：要么所有副本（备份数据）同时修改某个数值，要么都不更改，以此来保证数据的强一致性。 在分布式系统中，每个节点虽然可以知道自己的操作是成功还是失败，却是无法知道其他节点的操作状态。当一个事务需要跨越多个节点时，为了保持事务的 ACID 特性，需要引入一个作为协调者（Coordinator）的组件来统一掌控所有节点（参与者 Participant）的操作结果并最终指示这些节点是否要把操作结果进行真正的提交（比如将更新后的数据写入磁盘等等）。因此，二阶段提交的算法思路可以概括为：参与者将操作结果通知协调者，再由协调者根据所有参与者的反馈情报决定各参与者是否要提交操作还是中止操作。

图示

正常状态

失败状态

Fail-stop Failures模型

角色

Coordinator（协调者）

分布式系统中，其中一个节点为事务发起者

Participant（参与者，也叫 Voter 投票者）

分布式系统中，其他节点都是参与者

阶段

1. Commit request phase

2. Commit phase

细节描述

1. 提交请求阶段

当前阶段主要会做以下准备： 1. 协调者发送事务提交请求 协调者向所有参与者发送事务内容，询问是否可以执行事务提交操作，并等待每个参与者响应 2. 参与者执行事务 参与者执行事务，将Undo Log和Redo Log写入事务日志当中 3. 参与者向协调者反馈响应 参与者执行成功返回agree，没有执行成功返回disagree。

2. 提交阶段

提交事务

提交事务 每个参与者都返回agree时 相关节点会做以下操作： 1. 协调者发送Commit请求 协调者会向所有参与者发送Commit请求 2. 参与者提交事务 参与者收到Commit请求之后，正式执行事务提交操作，并在完成后释放整个事务期间占用的事务资源。 3. 参与者返回提交事务结果 参与者完成事务提交之后，向协调者发送ACK(Commited)响应。 4. 事务完成...

中断事务

任何一个参与者返回disagree或者等待超时时，协调者未能收到参与者反馈和响应。 将会执行以下操作： 1. 协调者发送aborted请求 超时或者收到disagree时，将会向所有参与者发送aborted请求 2. 参与者事务回滚 收到协调者发送的aborted请求后，根据第一阶段的Undo Log信息执行回滚操作，并释放事务执行过程中占用的资源。 3. 参与者反馈事务回滚结果 参与者执行完事务回滚操作之后，还需要向协调者发送回滚情况的ACK响应。 4. 事务中断 协调者收到所有参与者的aborted响应后，完成中断事务操作。

Crash模型

协调者Crash，参与者正常

选举一个新的协调者。当他成为新的协调者的时候，询问所有参与者的最后一条事务的执行情况，协调者就可以知道是应该做什么样的操作了。所以，这种情况不会导致数据不一致。

协调者正常，参与者Crash

参与者无法恢复

放弃该参与者，并在集群中重新添加一个新的节点

参与者可恢复

如果参与者恢复后发现未执行完的事务操作，直接取消当前事务。 重新向协调者请求新的任务。 等待协调者下发任务，并执行下发任务。 重新加入到集群中参与分布式事务。

协调者Crash，参与者Crash

协调者与参与者在第一阶段Crash

由于这时还没有执行Commit操作，可以重新选举协调者。 之后可以按照协调者正常，参与者Crash 的流程执行，完成事务的执行。

协调者在第二阶段Crash

参与者在agree阶段Crash

当新的协调者被选举出后，询问所有参与者的事务执行情况。 如果有机器disagree或者aborted，就通知参与者执行aborted操作。 如果没参与者执行aborted操作，且有参与者执行了Commit操作，就继续Commit操作。 事务可以正常执行，且数据可保证安全。

参与者在commit阶段Crash

如所有节点仍在执行事务状态，那么同步状态后可以判断事务是否需要继续执行。 但是如果节点在Crash之前执行了Commit操作，当协调者下发Commit请求，此时数据仍可保证数据一致性。 如果其他节点有aborted，那么协调者会下发aborted请求，而此时由于事务已提交，数据不一致，只能通过其他手段，重新同步数据。 有或者如果当前节点是aborted，但是发出aborted前Crash了，而其他节点已经执行完了Commit，并且发出了ACK，但是协调者也在这时Crash了，数据也无法保证一致性。

Fail-stop Failures模型

此模型是2PC的致命缺陷！ 该阶段如果部分参与者收到 commit 之后 crash 会造成事务无法提交，而接收到命令的会完成事务提交，导致数据不一致。 假设协调者和参与者3都在 commit 这个阶段 crash 了，而参与者1和参与者2没有收到 commit 消息. 这时候参与者1和参与者2就陷入了一个困境. 因为他们并不能判断现在是两个场景中的哪一种： 1.上轮全票通过然后参与者3第一个收到了 commit 的消息并在 commit 操作之后 crash了。 2.上轮参与者3反对所以干脆没有通过 ...

三阶段提交协议

Three-phase Commitment Protocol 最关键要解决的就是协调者和参与者同时挂掉导致数据不一致的问题，所以 3PC 把 2PC 的 Commit 阶段拆成了 PreCommit 和 Commit 两个阶段，这样三阶段提交就有：CanCommit、PreCommit 和 DoCommit 三个阶段。通过进入增加的这一个PreCommit阶段，参与者可以得到 Prepare 阶段的投票结果, 但不会 commit；而通过进入 Commit 阶段，参与者可以盘出其他每个参与者也都打算 commit 了，从而可以放心的 commit。 CanCommit 相当于 3PC 在 2PC 的 Commit 阶段里增加了一个 Barrier（即相当于告诉其他所有参与者，协调者收到了 Prepare 的结果）。 在这个 Barrier 之前协调者 Crash，其他参与者可以得出结论不是每个参与者都收到 Prepare phase 的结果，从而放弃或选出新的协调者; ...

图片示例

正常状态

异常状态

细节描述

CanCommit

1. 协调者发送事务请求 协调者发送CanCommit请求，询问是否可以执行事务提交操作，并开始等待各参与者响应。 2. 参与者反馈请求响应 参与者收到CanCommit请求之后，如果可以顺利执行返回agree，进入预备状态。 否则返回disagree。

PreCommit

所有参与者反馈agree

1. 协调者发送预提交请求 协调者向各参与者发送PreCommit请求，并进入Prepared阶段。 2. 参与者执行事务预提交 参与者收到PreCommit请求后，执行事务操作，记录Undo Log和Redo Log到事务日志中。 3. 参与者反馈事务预提交 将事务执行情况的通过ACK反馈给协调者。等待协调者下一步命令。

有参与者响应超时，或者反馈aborted

1. 协调者向所有参与者发送aborted请求 2. 协调者中断事务

DoCommit

所有参与者反馈agree

1. 协调者发送提交请求 协调者收到所有参与者的agree响应后，从PreCommit -> DoCommit阶段，向所有参与者发送DoCommit请求 2. 参与者执行事务提交 参与者收到DoCommit请求后，正式提交事务，并将执行事务期间占用的资源释放。 3. 参与者反馈提交结果响应、 参与者完成事务提交后，向协调者发送ACK响应 4. 事务完成 协调者收到所有参与者的ACK信息，完成事务。

有参与者超时，或者反馈aborted

1. 协调者向所有参与者发送abort请求 协调整向所有参与者发送abort请求后，等待参与者的ACK响应。 2. 参与者执行Rollback事务回滚 参与者接收到协调者的abort请求后，执行回滚操作，并释放所有事务中占用的资源。 3. 参与者反馈事务回滚结果响应 参与者将回滚情况通过ACK返回给协调者。 4. 中断事务 协调者接受到所有参与者反馈的ACK，终端事务

Network Partition（网络分区情况）

3PC 可以有效的处理 Fail-stop 的模式，但不能处理网络划分（Network Partition）的情况---节点互相不能通信。 假设在 PreCommi t阶段所有节点被一分为二，收到 PreCommit 消息的参与者在一边，而没有收到这个消息的参与者在另外一边。 在这种情况下，两边就可能会选出新的协调者而做出不同的决定。 除了网络分区以外， 3PC 也不能处理 Fail-recover 的错误情况。简单说来当协调者收到 PreCommit 的确认前 Crash，于是其他某一个参与者接替了原协调者的任务而开始组织所有参与者 commit。而与此同时原协调者重启后又回到了网络中，开始继续之前的回合---发送 abort 给各位参与者因为它并没有收到 PreCommit。此时有可能会出现原协调者和继任的协调者给不同节点发送相矛盾的 commit 和 abort 指令，从而出现个节点的状态分歧。

TCC提交协议

目前没有正式商用的案例 关于 TCC（Try-Confirm-Cancel）的概念，最早是由 Pat Helland 于 2007 年发表的一篇名为《Life beyond Distributed Transactions:an Apostate’s Opinion》的论文提出。在该论文中，TCC 还是以 Tentative-Confirmation-Cancellation 作为名称；正式以 Try-Confirm-Cancel 作为名称的，可能是Atomikos（Gregor Hohpe所著书籍《Enterprise Integration Patterns》中收录了关于 TCC 的介绍，提到了 Atomikos 的 Try-Confirm-Cancel，并认为二者是相似的概念） TCC 是对 2PC 的一个改进，Try 阶段通过预留资源的方式避免了同步阻塞资源的情况，但是TCC编程需要业务自己实现 Try、Confirm、Cancel 方法，对业务入侵太大，实现起来也比较复杂。 ...

Try

完成业务检查、预留业务资源

Confirm

使用预留的资源执行业务操作（需要保证幂等性）

Cancle

取消执行业务操作，释放预留的资源（需要保证幂等性）

Paxos算法

基于消息传递且具有高度容错性的一致性算法。 Paxos 算法要解决的问题就是如何在可能发生几起宕机或网络异常的分布式系统中，快速且正确地在集群内部对某个数据的值达成一致，并且保证不论发生以上任何异常，都不会破坏整个系统的一致性。 Paxos 的目的是让整个集群的结点对某个值的变更达成一致。Paxos 算法基本上来说是个民主选举的算法——大多数的决定会成个整个集群的统一决定。 任何一个点都可以提出要修改某个数据的提案，是否通过这个提案取决于这个集群中是否有超过半数的结点同意（所以 Paxos 算法需要集群中的结点是奇数）。

假设A、B、C三个节点

1. Prepare 阶段

A 把申请修改的请求 Prepare Request 发给所有的节点A，B，C。Paxos 算法会有一个 Sequence Number（可以认为是一个提案号，这个数不断递增，而且是唯一的，也就是说 A 和 B 不可能有相同的提案号），这个提案号会和修改请求一同发出，任何节点在「Prepare阶段」时都会拒绝其值小于当前提案号的请求。所以，节点A在向所有节点申请修改请求的时候，需要带一个提案号，越新的提案，这个提案号就越是是最大的。 如果接收节点收到的提案号 N 大于其它节点发过来的提案号，这个节点会回应 Yes（本节点上最新的被批准提案号），并保证不接收其它 << N 的提案。这样一来，节点上在 Prepare 阶段里总是会对最新的提案做承诺。 优化：在上述 Prepare 过程中，如果任何一个节点发现存在一个更高编号的提案，则需要通知提案人，提醒其中断这次提案。

2. Accept 阶段

如果提案者 A 收到了超过半数的节点返回的 Yes，然后他就会向所有的节点发布 Accept Request（同样，需要带上提案号 N），如果没有超过半数的话，那就返回失败。 当节点们收到了 Accept Request 后，如果对于接收的节点来说，n是最大的了，那么，它就会通过 Request（修改这个值），如果发现自己有一个更大的提案号，那么，节点就会拒绝 Request（拒绝修改）。 我们可以看以，这似乎就是一个“两段提交”的优化。其实，2PC/3PC都是分布式一致性算法的残次版本，Google Chubby的作者 Mike Burrows 说过这个世界上只有一种一致性算法，那就是 Paxos，其它的算法都是残次品。 我们还可以看到：对于同一个值的在不同节点的修改提案就算是在接收方被乱序收到也是没有问题的。

ACID

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。 ACID是传统数据库常用的设计理念, 追求强一致性模型。 ACID特性与分布式理论追求的目的存在分歧。 不属于分布式理论，与 CAP、BASE 的可用性截然相反

CAP

在理论计算机科学中，CAP定理（CAP theorem），又被称作布鲁尔定理（Brewer’s theorem）由加州大学柏克莱分校的计算机科学家埃里克·布鲁尔在2000年提出猜想并在在2002年由麻省理工学院（MIT）的赛斯·吉尔伯特和南希·林奇发表了布鲁尔猜想的证明，使之成为一个定理。 CAP 理论认为：一致性（Consistency），可用性（Availability）以及分区容忍性（Partition tolerance）三者不能同时满足。 论述CAP 详细情阅读：请不要再称数据库是CP或者AP (Please stop calling databases CP or AP) https://blog.the-pans.com/cap/ ...

Consistence（一致性）

在CAP中是可线性化的意思（Linearizability）。而这个是非常特殊(而且非常强)的一致性。尤其是虽然ACID中的C也是一致性(Consistency)，但是和这里的一致性没有任何关系。我会在后面解释可线性化是什么意思。

Availability（可用性）

在CAP中是定义为"每一个请求(request)如果被一个工作中的[数据库]节点收到，那一定要返回[非错误]的结果"。 注意，这里一部分节点可以处理这个请求是不充分的。任意一个工作中的节点都要可以处理这个请求。所以很多自称"高度可用"的系统通常并没有满足这里的可用性的定义。

Partition Tolerance（分区容错性）

基本上就是说通信是在异步的网络中。信息是可能延迟送达或者被丢失的。互联网还有我们所有的数据中心都有这个属性。所以我们在这件事上并没有选择。 分区 一个分布式系统里面，节点组成的网络本来应该是连通的。然而可能因为一些故障，使得有些节点之间不连通了，整个网络就分成了几块区域。数据就散布在了这些不连通的区域中。这就叫分区。

CAP 伪二分法

原因

很多系统在CAP的定义下，既不是一致也不可用。然而我从来没有听到别人称这些系统为"P"，可能是因为这样不太好看。但这并不差，他很可能是完全合理的设计，他只是不在CP/AP这两个分类中。 Eric Brewer（CAP 理论提出人）承认 CAP 是一个容易误导人的而且过于简化的模型。 在 2000年，CAP 的意义在于让大家开始讨论关于分布式系统的取舍。 他在这方面做得很好。但是他不是用来作为一个正式的突破性的结果，也不是一个严格的数据系统的分类方式。 15年之后，我们已经有了多得多的有不一样一致性和容错模型的系统。CAP 已经完成了他自己的使命，现在是时候不要在纠结了。 例如Zookeeper、NoSQL 等等都是在 CAP 范围之外的优秀软件

重CA轻P

如果希望能够避免系统出现分区容错性问题，一种较为简单的做法是将所有的数据（或者仅仅是哪些与事务相关的数据）都放在一个分布式节点上。这样做虽然无法 100 %保证系统不会出错，但至少不会碰到由于网络分区带来的负面影响。但同时需要注意的是，放弃P的同时也就意味着放弃了系统的可扩展性。 场景示例 7*24小时金融服务可能会选择 CA，改为牺牲部分P（单节点主从备份，故障切换）

重CP轻A

一旦系统遇到网络分区或其他故障或为了保证一致性时，放弃可用性，那么受到影响的服务需要等待一定的时间，因此在等待期间系统无法对外提供正常的服务，即不可用 场景 大部分金融选择 CP

重AP轻C

这里所说的放弃一致性，实际上指的是放弃数据的强一致性，而保留数据的最终一致性。这样的系统无法保证数据保持实时的一致性，但是能够承诺的是，数据最终会达到一个一致的状态。 场景 大部分行业选择AP，通过日志等保证最终一致性

ACID与CAP一致性区别

ACID

内部一致性注重于事务前后数据的完整性

节点服务器的数据完整性

本质区别

ACID 的一致性只是目的，通过 AID 特性来保证。 是指系统从一个正确的状态，迁移到另一个正确的状态。 就是当前的状态满足预定的约束就叫做正确的状态。 而事务具备 ACID 里 C 的特性是说通过事务的AID来保证我们的一致性。

CAP

外部一致性则注重于读写数据或主从数据的一致性

分布式多服务器之间复制数据以取得这些服务器拥有同样的数据，这是一种分布式领域的一致性概念

本质区别

CAP 的 C，指的是可线性化一致性，即强一致性。

可线性化

如果B操作在成功完成A操作之后，那么整个系统对B操作来说必须表现为A操作已经完成了或者更新的状态。 这张图展示了Alice还有Bob,他们在同一个房间，都在看他们的手机查2014年世界杯的决赛结果。就在最终结果刚发布之后，Alice刷新了页面，看到了宣布冠军，而且很兴奋地告诉了Bob。Bob马上也重新加载了他手机上的页面，但是他的请求被送到了一个数据库的拷贝，还没有拿到最新的数据，结果他的手机上显示决赛还正在进行。 如果Alice和Bob同时刷新，拿到了不一样的结果，并不会太让人意外。因为他们不知道具体服务器到底是先处理了他们中哪一个请求。但是Bob知道他刷新页面是在Alice告诉了他最终结果之后的。所以他预期他查询的结果一定比Alice的更新。事实是，他却拿到了旧的结果。这就违反了可线性化。 ...

BASE

BASE 理论是由 eBay 架构师提出的。 BASE 是对 CAP 中一致性和可用性权衡的结果，其来源于对大规模互联网分布式系统实践的总结，是基于C AP 定律逐步演化而来。 其核心思想是即使无法做到强一致性，但每个应用都可以根据自身业务特点，采用适当的方式来使系统达到最终一致性。 BASE 解决了 CAP 中理论没有网络延迟，在 BASE 中用软状态和最终一致，保证了延迟后的一致性。 BASE 和 ACID 是相反的，它完全不同于 ACID 的强一致性模型，而是通过牺牲强一致性来获得可用性，并允许数据在一段时间内是不一致的，但最终达到一致状态。

Basically Available（基本可用）

基本可用是指分布式系统在出现故障的时候，允许损失部分可用性，即保证核心可用。 比如：响应时间上的损失，功能上的损失（降级页面）

Soft State（软状态）

弱状态也称为软状态，和硬状态相对，是指允许系统中的数据存在中间状态，并认为该中间状态的存在不会影响系统的整体可用性，即允许系统在不同节点的数据副本之间进行数据同步的过程存在延时。

Eventually Consistent（最终一致性）

最终一致性强调的是系统中所有的数据副本，在经过一段时间的同步后，最终能够达到一个一致的状态。 因此，最终一致性的本质是需要系统保证最终数据能够达到一致，而不需要实时保证系统数据的强一致性。 最终一致性分类详见上文一致性概念，在实际的实践中，这5种系统往往会结合使用，以构建一个具有最终一致性的分布式系统。 哪五种？ ACID、CA、AP、CP、BASE五种相互结合。

分库分表

水平分割

通过建立结构相同的表结构，分割为多份数据共同储存一份完整的数据。 假如一张表，有1000万条数据。使用水平分割，分别在10张表分别存储 100万数据，共同组成1000万条数据。 id、时间按照区间直接分割，一般历史记录表会选择按照此种方式，做周表、月表、年表。 userId 一般采取取模的方式分割，保证相同用户尽量落到一张表中 也可以两种结合使用。 水平分割优点：...

垂直分割

通过拆分表结构，将一份完整的记录分成多个表储存一份完整的数据。 假如一张表，有5个字段。其中3个字段数据常用，将3个字段建立一张表，与原来表建立关系，使用两张表共同存储一份完整的数据。 优点： 1. 表数据结构更加内聚，查询单条完整数据相对更快 缺点： 1. 相同业务，在拆分之后很可能需要join关联查询，性能下降。

第三方插件

Shareding JDBC

MyCat

节点复制

服务器间外部复制

主从模式

Asynchronous Replication

异步复制，默认方式

逻辑原理

主库执行客户端提交的事务，立即返给客户端。不关心从库是否都已经接收并处理。

技术实现

主库将事务的 Bin Log 事件写入 binlog 中，主库通知 Dump 线程发送新的 binlog，然后主库继续后续操作。 至于从库是否收到，主库并不关心。

问题

如果主机 Crash，此时主机上的已提交的事务有可能并没有传递到从库上，如果强行将从库提升为主库，会导致数据不一致

Fully synchronous Replication

全同步复制

逻辑实现

主库执行完事务，所有从库必须都执行了该事务才会返给客户端。

技术实现

主库提交事务后，所有从库节点必须收到、接收并提交该事务。主库才能继续后续操作。

存在问题

因为主库必须等待从库完成事务才能返给客户端，主库完成时间变长，性能降低

Semisynchronous Replication

半同步复制

逻辑实现

介于异步复制和全同步复制之间。 master 在写入binlog 后通知 Slave 写入，master 此时 commit 并且一直等待 ACK。 只需等待至少一个从库节点收到并且刷新 binlog 到 relay log 文件然后反馈给 master 一个 ACK。 不需要等待所有从库的反馈。 master 在写入 binlog 后通知 slave 写入，由于 master 是在最后 commit 等待的，此时 master 其他 session 可以看到该提交事务，所以 master 和 slave 数据一致，master 如果 crash，slave 数据会丢失。详见半同步与无损复制区别

技术实现

主库执行完事务后，等待至少一个从库接收并写入 relay log 然后返给客户端。

半同步退化机制

半同步之所以叫做半同步，是因为有一个退化机制。当达到触发条件时，同步复制会退化为异步复制，所以叫半同步复制。 半同步复制需要设置一个超时时间，在超时时间范围内，数据复制采用同步复制模式，只需要至少一台设备接收了 binlog，数据即可提交成功。假如在超时时间范围内没有响应，会退化为异步复制，并继续提供服务。 这就是半同步复制可能会出现数据不一致的原因。

存在问题

虽然相比异步和全同步提高了性能，保证的数据的安全性，但是最少有一个 TCP/IP 的延迟时间。适合在低延时网络中使用。 半同步复制在特殊条件会退化为异步复制

Lossless Replication

无损复制半同步复制升级版

逻辑实现

半同步增强版（优化了 ACK 等待和 commit 机制）。master 在写入binlog 后通知 Slave 写入，master 并且一直等待 ACK（此时不会 commit 该事务）。只需等待至少一个从库节点收到并且刷新 binlog 到 relay log 文件然后反馈给 master 一个 ACK 然后完成该事务的 commit。不需要等待所有从库的反馈。 master 写入 binlog 并通知 slave 同步 binlog 时等待，此时 master 其他 session 看不到该提交事务，所以 master 和 slave 数据一致。master 如果 crash ，slave 不会丢失数据。 主从数据一致，因为 binlog 持久化到 Slave 才会给 Master 返回 ACK，不存在数据丢失问题。

技术实现

半同步复制中，master 写数据到 binlog 且sync，然后一直等待 ACK。当至少一个 slave 接收 binlog 后写入到 relay‐log 并 flush disk，就返回 ack（不需要回放完日志）

存在问题

会阻塞 master session，非常依赖网络由于master 是在三段提交的第二阶段 sync binlog 完成后才等待, 所以master 的其他session 是看不见这个提交事务的，所以这时候master 上的数据和slave 一致，master crash 后，slave 没有丢失数据

半同步复制与无损复制区别

ACK 和 commit 等待阶段不一样

半同步复制

After commit 之后等待 ACK 相比异步复制，数据丢失风险很低 重点 由于 master 是在三段提交的最后 commit 阶段完成后才等待，所以 master 的其他 session 是可以看到这个提交事务的，所以这时候 master 上的数据和 slave 不一致，master crash 后，slave 数据丢失。

无损复制

After sync 之后等待 ACK 最后 commit 数据零丢失（前提一直都是 lossless replication） 由于 master 是在三段提交的第二阶段 sync binlog 完成后才等待, 所以 master 的其他 session 是看不见这个提交事务的，所以这时候 master 上的数据和slave一致，master crash 后，slave 没有丢失数据

MGR模式

MySQL Group Replication（MGR）是 MySQL 5.7.17版本引入的一个服务器插件，可用于创建高可用、可扩展、容错的复制拓扑结构。MGR由组通信系统（Group Communication System，GCS）协议支持。该系统提供故障检测机制、组成员服务以及安全且有序的消息传递。所有这些属性都是创建系统的关键，可确保在服务器组中一致地复制数据。该技术的核心是 Paxos 算法的实现，它充当组通信引擎。 基于组的复制（Group-based Replication）是一种被使用在容错系统中的技术。 Replication-group（复制组）是由能够相互通信的多个服务器（节点）组成的。在通信层，Group Replication 实现了一系列的机制：比如原子消息（Atomicmessage Delivery）和全序化消息（Totalorderingof messages）。 这些原子化，抽象化的机制，为实现更先进的数据库复制方案提供了强有力的支持。 ...

特性

数据一致性

对于只读（RO）事务，组间实例无需进行通讯，就可以处理事务；但是对于读写（RW）事务，需要经过组内大多数节点决议，来决定该事务是否可以提交。 一个 Replication-group 就是一组节点，每个节点都可以独立执行事务，而读写事务则会在于 Group 内的其他节点进行协调之后再 commit。 因此，当一个事务准备提交时，会自动在 Group 内进行原子性的广播，告知其他节点变更了什么内容/执行了什么事务。 这种原子广播的方式，使得这个事务在每一个节点上都保持着同样顺序。

事务并发冲突处理

在高并发的多写模式（MGR 的一种运行模式）下，节点间事务的提交可能会产生冲突，比如，两个不同的事务在两个节点上操作了同一行数据，这个时候就会产生冲突。 首先，Group Replication（GR）能够识别到这个冲突，然后对此的处理采用乐观策略：依赖事务提交的时间先后顺序，先发起提交的节点能够正确提交，而后面的提交，会失败。 假设在不同的节点上有两个并发事务，更新了同一行数据，那么就会发生资源争用。 面对这种情况，GroupReplication 判定先提交的事务为有效事务，会在整个 Group 里面重演，后提交的事务会直接中断，或者回滚，最后丢弃掉。 因此，这也是一个无共享的复制方案，每一个节点都保存了完整的数据副本。

节点故障自动检测

MGR自带故障检测机制，可以识别组内成员是否挂掉(组内节点心跳检测)。 当一个节点失效，将由其他节点决定是否将这个失效的节点从 Group 里面剔除。当然，这是建立在满足大多数节点存活并且可以进行决议的前提上的。

组成员自动管理

MGR 自动维护组内节点的状态(在线？存活？挂掉？)，对于失效的节点，由其他节点决定是否剔除。 对于新加入的节点，MGR 会自动维护它的视图与其他节点的视图保持一致。

容错能力

MGR 基于分布式一致性算法实现，一个组允许部分节点挂掉，只要保证大多数节点仍然存活并且之间的通讯是没有问题的，那么这个组对外仍然能够提供服务。 假设一个 MGR 由 2n + 1 个节点（建议节点为奇数），那么允许 n 个节点失效，这个 MGR 仍然能够对外提供服务。比如有 3 个节点组成的一个 MGR，可允许1个节点失效，这个 MGR 仍然能够提供服务。

基于Paxos多主更新

1. 组复制由多个 Server 成员构成，并且组中的每个 Server 成员可以独立地执行事务。但所有读写（RW）事务只有在冲突检测成功后才会提交。只读（RO）事务不需要在冲突检测，可以立即提交。 2. 对于任何 RW 事务，提交操作并不是由始发 Server 单向决定的，而是由组来决定是否提交。准确地说，在始发 Server 上，当事务准备好提交时，该 Server 会广播写入值（已改变的行）和对应的写入集（已更新的行的唯一标识符）。然后会为该事务建立一个全局的顺序。最终，这意味着所有 Server 成员以相同的顺序接收同一组事务。因此，所有 Server 成员以相同的顺序应用相同的更改，以确保组内一致。 3. 组复制能够根据在一组 Server 中复制系统的状态来创建具有冗余的容错系统。因此，只要它不是全部或多数 Server 发生故障，即使有一些 Server 故障，系统仍然可用，最多只是性能和可伸缩性降低，但它仍然可用。Server...

示例

由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才能得以提交。 如图所示，由 3 个节点组成一个复制组，Consensus 层为一致性协议层，在事务提交过程中，发生组间通讯，由2个节点决议（certify）通过这个事务，事务才能够最终得以提交并响应

分支主题

Certify

Certify 模块主要负责检查事务是否允许提交，是否与其它事务存在冲突，如两个事务可能修改同一行数据。 单主模式 由 3 个节点形成一个 Group，当在节点 s1 上发起一个更新事务 UPDATE，此时数据库版本 dbv = 1，更新数据行之后，准备提交之前，将其修改的数据集（write set）及事务日志相关信息发送到 Group，Write set 中包含更新行的主键和此事务执行时的快照（由 gtid_executed 组成）。组内的每个节点收到 Certification 请求后，进入 Certification 环节，每个节点的当前版本 cv = 1，与 write set 相关的版本 dbv = 1，因为 dbv 不小于 cv，也就是说事务在这个write set 上没有冲突，所以可以继续提交。 并发冲突解决...

冲突解决方式

单主模式

两个事务的冲突可以通过封锁来避免。 悲观锁思想解决。

多主模式

乐观锁思想解决 不同节点间没有分布式锁，所以无法使用封锁来避免。 为提高性能，Group Replication 乐观地来对待不同事务间的冲突，乐观的认为多数事务在执行时是没有并发冲突的。 事务分别在不同节点上执行，直到准备提交时才去判断事务之间是否存在冲突。

MGR使用

前置条件

详见：https://dev.mysql.com/doc/refman/8.0/en/group-replication-requirements.html

仅支持 InnoDB 引擎

仅支持IPv4

必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set

每张表必须有主键，用于做write set冲突检测

限制条件

详见：https://dev.mysql.com/doc/refman/8.0/en/group-replication-limitations.html

一个MGR集群最多支持9个节点

官方建议 READ COMMITTED 隔离级别

因为 REPEATABLE READ 级别在 Certfy 过程中没有考虑到 Gap Lock。 而RC级别不会使用Gap Lock

SELECT ... FOR UPDATE 可能造成死锁

不支持外键：多主不支持，单主模式不存在此问题

不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚

二进制日志不支持 binlog event checksum

不支持 SERIALIZABLE 事务隔离级别

COMMIT可能会失败，类似于快照事务隔离级别的失败场景

DDL 无法作为事务处理，存在一些问题

不支持 Replication event checksums，需要在 my.cnf 里面配置

Single-Primary Mode

Single-Primary Mode，组内只有一个节点负责写入，读可以从任意一个节点读取，组内数据保持最终一致。 这个模式下，Group 内只有一台节点可写可读，其他节点只读。对于 Group 的部署，需要先跑起 Primary 节点（read_only = 0），然后再跑起其他的节点，并把这些节点一一加进 Group。其他的节点就会自动同步 Primary 节点上面的变化，然后将自己设置为只读模式（read_only = 1）。 当 Primary 节点意外宕机或者下线，在满足大多数节点存活的情况下，Group 内部发起选举，选出下一个可用的读节点，提升为 Primary 节点。 在切换 Primary 期间，Group 不会处理应用重连接到新的主，这需要应用层自己或者由另外的中间件层（proxy or router）去保证。 Primary 选举根据 Group 内剩下存活节点的 server_uuid 按字典序升序来选择，即剩余存活的节点按server_uuid...

示图

https://www.93bok.com/MySQL%E9%9B%86%E7%BE%A4MGR%E6%9E%B6%E6%9E%84for%E5%8D%95%E4%B8%BB%E6%A8%A1%E5%BC%8F/ 单主 Primary Election

Multi-Primary Mode

Multi-Primary Mode 即为多写方案，即写操作会下发到组内所有节点，组内所有节点同时可读可写，该模式也是能够保证组内数据最终一致性。

示图

https://www.93bok.com/MySQL%E9%9B%86%E7%BE%A4MGR%E6%9E%B6%E6%9E%84for%E5%A4%9A%E4%B8%BB%E6%A8%A1%E5%BC%8F/ 多主 Client Failover

服务器间内部复制

异步复制、全同步复制、半同步复制、无损复制都可以根据配置选择适合的复制模式。组复制只能选择基于 GTID 的 row 复制方式

基于Binlog复制

statement-based replication

SBR 每一条修改数据的 SQL 语句都记录到 binlog 中，Salve 在复制的时候，会解析成和 Master 端相同的 SQL并执行。

优势

binlog文件较小

binlog 文件较小，节约 IO 资源，提高性能

主从数据库版本可以不一样

缺点

不定函数或者功能会让复制出问题

在 Statement 模式下，由于是记录的执行语句，为了 SQL 语句在 Salve 端也可以正确执行，那么记录每条语句时，必须额外记录上下文信息，保证 Salve 端执行能够得到和 Master 一样的结果。因此修改数据时，如果使用了特定函数或功能时，复制就会出现问题。比如： sleep() 函数在有些版本不能正确复制。

复制过程可能会有全表扫描

复制需要进行全表扫描（WHERE 语句中没有使用到索引）的 UPDATE 时，需要比 RBR 请求更多的行级锁。 导致复制效率在某些情况下不高。

有AUTO_INCREMENT字段，会有阻塞

对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句

复杂语句如果执行出错会消耗更多资源

对于一些复杂的语句，在从服务器上的耗资源情况会更严重，如果出错的话，会消耗更多资源

row-based replication

简称RBR，不记录每条 SQL 语句的上下文信息，仅需记录数据改变,哪条数据被修改了，修改成什么样。

优点

对函数、存储过程、触发器等机制兼容

不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题。

更小锁粒度

INSERT、UPDATE、DELETE 语句，Slave 都需要更少的行级锁。 并发度相对较高。

支持多线程复制

缺点

binlog日志量大

对大事务执行效率不高

UDF产生的大BLOB值影响复制效率

不能直接查看binlog日志

mixed-based replication

MBR SBR、RBR两种模式的混合使用，一般的复制使用 Statement 模式保存 binlog，对于Statement 模式无法复制的操作使用 Row 模式保存 binlog，MySQL 会根据执行的SQL语句选择日志保存方式。

基于GTID复制

Global Transaction ID复制 MySQL 5.6 引入 Global Transaction Identifiers（全局事务标识）主要用来解决主从复制（Replication）场景问题，GTID 只存在于 binlog 中，数据库中不存在。GTID是一个事务一一对应，并且全局唯一ID。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

GTID

GTID = source_id:transaction_id

source_id

用于鉴别原服务器，即 MySQL 服务器唯一的的 server_uuid，由于 GTID 会传递到 Slave，所以也可以理解为源ID。

transaction_id

为当前服务器上已提交事务的一个序列号，通常从1开始自增长的序列，一个数值对应一个事务。

示例

3E11FA47-71CA-11E1-9E33-C80AA9429562:23 前面的一串为服务器的 server_uuid，即 3E11FA47-71CA-11E1-9E33-C80AA9429562，后面的 23 为 transaction_id

工作原理

同一 GTID 只会被执行一次，相同的会 Skip 1. 当一个事务在主库端执行并提交时，产生 GTID，一同记录到 binlog 日志中。 2. binlog 传输到 Slave，并存储到 Slave 的relaylog 后，读取这个 GTID 的这个值设置 gtid_next 变量，即告诉 Slave 下一个要执行的 GTID 值。 3. sql 线程从 relaylog 中获取 GTID，然后对比 Slave 端的 binlog 是否有该 GTID。 4. 如果有记录，说明该 GTID 的事务已经执行，Slave 会忽略。 5. 如果没有记录，Slave 就会执行该 GTID 事务，并记录该 GTID 到自身的 binlog，在读取执行事务前会先检查其他 session 持有该 GTID，确保不被重复执行。 6. 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

GTID在binlog中的结构

GTID event 结构

优点

更简单的实现 failover 做到主从切换自动化

更简单的搭建主从复制。

数据比传统的复制更加安全。

GTID 是连续的没有空洞的，保证数据的一致性，零丢失。

缺点

只支持 InnoDB 引擎

必须全 GTID 才可以复制

故障处理比较复杂，需要注入空事务

不支持 CREATE TABLE ... SELECT 语句，因为该语句会被拆成 CREATE TABLE 和 INSERT 两个事务，并且两个事务被分配同一个 GTID，导致 INSERT 操作直接 Skip

不支持CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE 临时表操作

Errant transaction 问题：即从库不能进行任何事物型操作，会引入新的 GTID，当 binlog 被清除后，再进行主从切换，会导致其他从库找不到此 GTID，从而挂载不上

GTID配合SBR、RBR、MBR三者之一混合使用，保证数据一致性

Master配置

log-bin=master-bin # 配置复制方式 statement（默认）、row、mixed binlog_format=row # 开启 GTID 模式 gtid_mode=on # 开启 GTIC 强一致 enforce-gtid-consistency=on # 高可用切换 log-slave-updates=on

Slave配置

log-bin=slave-bin # 配置复制方式 statement（默认）、row、mixed binlog_format=row # 开启 GTID 模式 gtid_mode=on # 开启 GTIC 强一致 enforce-gtid-consistency=on # 高可用切换 log-slave-updates=on # 设置为只读...

高可用选型

基于 MySQL 原生异步或半同步复制协议

MHA

MHA（Master High Availability）是一套 MySQL 高可用管理软件，除了检测 Master 宕机后，提升候选 Slave 为 New Master 之外（漂虚拟IP），还会自动让其他 Slave 与 New Master 建立复制关系。 MHA Manager 可以单独部署在一台独立的机器上，并管理多个 master-slave 集群。

架构

分支主题

MHA Manager

MHA 的管理节点，负责检测 MySQL 的状态，以及管理 MySQL 的复制关系

Master

MySQL 对外提供的服务（通过 VIP ）的主节点

Slave(M)

MySQL 候选主节点，本质上为一个 Slave 节点，只是在 Master 宕机后，会被提升为 New Master

Slave(N)

MySQL从机节点，对外可以提供只读服务

工作流程

1.1 Slave(M) 是通过对比 Slave 之间 I/O 线程读取 Master binlog 位置，选取最接近的 Slave 作为 latestslave。其它 Slave 通过与 latest slave 对比生成差异中继日志。在 latest slave 上应用从 Master 保存的 binlog，同时将 latest slave 提升为 Master。最后在其它 Slave 上应用相应的差异中继日志并开始从新的 Master 开始复制。 1.1 MHA Manager 会定时探测集群中的 Master 节点，当 Master 宕机后，MHA Manager 会让 Slave(M) 提升为 New Master ，然后修改 Slave N 的复制关系（ CHANGE MASTER ），指向 New Master 1.2 Slave(M) 是通过对比 Slave 之间 I/O 线程读取 Master binlog 位置，选取最接近的 Slave 作为 latestslave。其它 Slave 通过与 latest slave 对比生成差异中继日志。在 latest slave 上应用从 Master 保存的 binlog，同时将 latest slave 提升为 Master。最后在其它 Slave 上应用相应的差异中继日志并开始从新的 Master 开始复制。...

Failover过程

此过程中，数据无法保证强一致性 1. 从宕机崩溃的 Master 节点保存二进制日志事件（binlog events），此种情况为 MySQL 挂了，但是服务器没挂，还是可以通过 SSH 连接过去，如果服务器彻底宕机，该步骤略过 2. 识别含有最新的更新的 Slave节点 3. 应用差异的中继日志（relay-log）到其他的 Slave 4. 应用从Master保存的二进制日志事件（binlog events），如果之前 Master 彻底宕机了，就没有保存的 binlog，该步骤略过...

无法保证数据不会丢失

MySQL 5.7 之前数据不丢的前提是 Master 服务器还可以被 MHA Manager进行SSH连接，通过应用保存的 binlog 的方式来保证。 MySQL 5.7 之后通过无损复制，仅仅是减少了丢数据的可能性，假如此时的状态为切成异步的状态，那就和之前一样了（可以设置超时的时间很大） 当 Master 恢复的时候，最后一部分数据是否需要 Flashback，MHA 也是不负责这个事情，需要人工介入

优点

可以进行故障的自动检测和转移

可扩展性较好，可以根据需要扩展MySQL的节点数量和结构

相比于双节点的 MySQL 复制，三节点/多节点的 MySQL 发生不可用的概率更低

支持基于日志点、GTID的复制方式

缺点

至少需要三节点，相对于双节点需要更多的资源

逻辑较为复杂，发生故障后排查问题，定位问题更加困难

数据一致性仍然靠原生半同步复制保证，仍然存在数据不一致的风险

可能因为网络分区发生脑裂现象

需要基于 SSH 免认证配置，存在一定的安全隐患

只监控 master，未监控 slave 状态

MMM

MMM（Master-Master replication manager for MySQL）是一套支持双主故障切换和双主日常管理的脚本程序。MMM 使用 Perl 语言开发，主要用来监控和管理 MySQL Master-Master（双主）复制，虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热，可以说 MMM 这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个 Slave的 read 负载均衡。 MMM 提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟 ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于 MMM 无法完全的保证数据一致性，所以 MMM 适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用 MMM 这种高可用架构。

优点

高可用，主主同步，虽然是 2 个 master，但只有一个对外提供服务（主备模式）

缺点

由于架构里只有一个写入点，所以扩展性是有限的，但是对一般中型企业够用了。解决方案：对于大应用可以采取垂直拆分到多个 MMM 架构的方式，使用 MMM Cluster 来管理

对于读写分离和读负载均衡还是要程序来开发或者使用其他工具完成。

数据不能保证抢一致性，但是保证了高可用

可能因为网络分区发生脑裂现象

存在单点故障

不支持基于 GTID 复制

Zookeeper + Proxy

Zookeeper 使用分布式算法保证集群数据的一致性，可以有效的保证 Proxy 的高可用性，可以较好的避免网络分区现象的产生。

优点

较好保证了整个系统的高可用

扩展性好，可以扩展为大规模集群

缺点

依然依赖于原生的半同步复制

引入 zk，使系统变得更加复杂

基于分布一致性协议

MGR

MySQL Innodb Cluster(基于MGR方案)

MySQL InnoDB Cluster 解决方案其实是由 MySQL 的几个不同产品和技术组成的，比如 MySQL Shell，MySQL Router，Group Replication。一组MySQL服务器可以配置为一个MySQL集群。在默认的单主节点模式下，集群服务器具有一个读写主节点和多个只读辅节点。辅助服务器是主服务器的副本。客户端应用程序通过 MySQL Router 连接到主服务程序。如果主服务连接失败，则次要的节点自动提升为主节点，MySQL Router 请求到新的主节点。InnoDB Cluster 不提供 NDB Cluster 支持。

优点

支持多主写入

无脑裂问题

自动故障转移，自动添加、剔除节点，不依赖任何第三方工具

缺点

同 MGR

MySQL NDB Cluster

官方集群的部署方案，通过使用NDB存储引擎实时备份冗余数据，实现数据库的高可用性和数据一致性。

优点

全部使用官方组件，不依赖于第三方

当 NDB Cluster 关闭时，NDB Cluster 数据节点在 Memory 中保存的数据将写入磁盘，并在 Cluster 启动的下一个 time 重新加载到 Memory。

同步复制，在 NDB Cluster 所有数据节点都保持同步，可实现数据强一致性

缺点

基于内存，数据库集群规模受内存大小限制

配置复杂，必须使用 NDB 存储引擎，与常规存储引擎存在差异，只支持 READ COMMITTED 隔离级别，没有 MVCC

联表 JOIN 性能很差

MariaDB Galera Cluster

MariaDB 集群是 MariaDB 同步多主机集群。它仅支持 XtraDB/ InnoDB 存储引擎（虽然有对 MyISAM 实验支持 - 看wsrep_replicate_myisam 系统变量）。

功能

同步复制

真正的 multi-master，即所有节点可以同时读写数据库

自动的节点成员控制，失效节点自动被清除

新节点加入数据自动复制

真正的并行复制，基于行级

用户可以直接连接集群，使用感受上与 MySQL 完全一致

优点

因为是多主，所以不存在 Slavelag（延迟）

不存在丢失事务的情况

同时具有读和写的扩展能力

节点间数据是同步的,而 Master/Slave 模式是异步的，不同 Slave 上的 binlog 可能是不同的

缺点

需要打 wsrep 补丁

只支持 InnoDB 存储引擎

不同 Slave 的 binlog 可能不同