InnoDB 存储引擎

予早 2025-09-01 00:56:00
Categories： Tags：

InnoDB 架构

InnoDB-architecture

内存结构

缓冲池，Buffer Pool

缓冲池是主内存中的一个区域，其以页为存储单位，InnoDB 在访问磁盘上数据页和索引页时会对其进行缓存。缓冲池允许直接从内存访问常用数据，从而加快处理速度。在专用服务器上，通常会将高达 80% 的物理内存分配给缓冲池。

BufferPool：缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘I/O，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

缓冲池以最近最少使用（LRU）算法变体管理数据。

LRU

  1. 缓冲池被组织为一个链表，该链表由两个页链表 new sublist、old sublist 构成，其中 new sublist 占缓冲池的$/frcc{5}{8}$，old sublist 占缓冲池的$/frac{3}{8}$，列表的中点是新子列表的尾部与旧子列表的头部相交的边界。
  2. InnoDB 读取页时，首先从缓冲池中获取，若没有再从磁盘中读取并将页缓存至缓冲池。页读取有两种原因，一是用户发起操作（例如，SQL查询），二是 InnoDB 预读。
  3. InnoDB 读取页时，若缓冲池中没有，最初会将页面插入到中点（插入中点的数据会被放到旧子列表的头部），旧子链表其余节点后移（衰老化），若插入时旧子列表满，则会驱逐尾部页出链表然后再插入。插入中点后，根据页读取发生的原因，有不同行为：
    • 若页读取由用户发起操作触发，页被移动至新子链表头部（页直接年轻化，详细参见命中缓存时步骤）
    • 若页读取由InnoDB预读触发，页留在旧子链表保持不动（下一次读取该页时会命中缓存触发年轻化）
  4. InnoDB 读取页时，若缓冲池中存在，则根据页所在子链表不同有不同行为：
    • 若页在新子链表，则页会被移动至新链表头部（年轻化）
    • 若页在旧子链表，则页会被移动至新链表头部（年轻化），新链表其余节点后移（衰老化），若页移动至新子链表时新子链表满，则会将其尾部节点衰老至旧子链表然后再插入。

使用SHOW ENGINE INNODB STATUS;查看BUFFER POOL AND MEMORY。

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2347368448
Dictionary memory allocated 11854139
Buffer pool size   130432
Free buffers       3087
Database pages     127007
Old database pages 47035
Modified db pages  5260
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 773726113, not young 25367121616
6.05 youngs/s, 1169.02 non-youngs/s
Pages read 2250350690, created 154709943, written 2179642334
151.37 reads/s, 3.05 creates/s, 53.62 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 5 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 127007, unzip_LRU len: 0
I/O sum[60976]:cur[7312], unzip sum[0]:cur[0]

更改缓冲区，Change Buffer

ChangeBuffer

更改缓冲区用以优化索引页更改场景（INSERT、UPDATE、DELETE）下磁盘大量随机访问IO问题：与聚集索引不同，二级索引通常是非唯一的，并且向二级索引中插入数据的顺序相对随机。同样，删除和更新操作可能会影响索引树中不相邻的二级索引页。对二级索引页的操作容易引起大量随机访问IO。

当二级索引页不在缓冲池中时，更改缓冲区会缓存对索引页的更改。这些缓冲的更改可能源于INSERT、UPDATE或DELETE操作（数据操纵语言），稍后当其他读取操作将这些页面加载到缓冲池中时，这些更改会被合并。

当其他操作将受影响的页读入缓冲池时，在稍后合并缓存的更改，可避免将二级索引页从磁盘读入缓冲池所需的大量随机访问 I/O。

自适应哈希索引，Adaptive Hash Index

当启用时，InnoDB 会根据查询访问模式自动为经常使用的索引页创建哈希索引，从而加速等值查询（= 查询）。

自适应哈希索引使InnoDB在工作负载与缓冲池内存足够的系统上，能更像内存数据库一样运行，同时又不牺牲事务特性和可靠性。自适应哈希索引可通过innodb_adaptive_hash_index变量禁用，也可在服务器启动时通过--innodb-adaptive-hash-index启用。

根据观察到的搜索模式，会使用索引键的前缀来构建哈希索引。前缀可以是任意长度，而且可能只有B树中的部分值会出现在哈希索引中。哈希索引是根据经常被访问的索引页面按需构建的。

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index_parts';

日志缓冲区，Log Buffer

日志缓冲区是一个内存区域，用于存放将要写入磁盘上日志文件的数据。日志缓冲区的大小由变量定义。默认大小为64MB。日志缓冲区的内容会定期刷新到磁盘。较大的日志缓冲区能让大型事务在运行时，无需在提交前将重做日志数据写入磁盘。因此，如果您有更新、插入或删除大量行的事务，增大日志缓冲区的大小可以节省磁盘I/O。

磁盘结构

索引

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点：

  1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的查询速度，这也是创建索引的主要原因。
  3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。
  4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

索引的缺点：

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

按逻辑约束分类
  1. 主键（PRIMARY）：唯一且 NOT NULL
  2. 唯一（UNIQUE）：唯一但允许 NULL，若为单列唯一索引，允许多个 NULL 列，若为联合唯一索引，则？？？
  3. 普通（INDEX）
  4. 全文（FULLTEXT）
  5. 空间（SPATIAL）
按物理存储分类

聚簇索引

聚簇索引叶子节点会指向数据，每个表都会有一个聚簇索引，聚簇索引生成策略如下：

  1. 有定义主键以主键索引为聚簇索引
  2. 未定义主键，则选用第一个所有键列均为 NOT NULL 的 UNIQUE 索引
  3. 无合适唯一索引，则使用行ID生成一个名为 GEN_CLUST_INDEX 的索引，行 ID 为 6 字节

二级索引

聚集索引以外的索引称为二级索引。在 InnoDB中，二级索引中的每条记录都包含该行的主键列以及为二级索引指定的列。 InnoDB使用这个主键值在聚集索引中搜索该行。

[!TIP]

由于二级索引中包含主键索引中的列，故设计较短的主键有利于节省二级索引占用空间。

索引物理结构

InnoDB 中索引由不同物理结构所实现：

  1. 主键索引、唯一索引、普通索引基于B+树。
  2. 全文索引基于倒排索引。
  3. 空间索引基于R树。
B+Tree

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。如下图：

img

image-20250830151749829

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

索引顺序访问方法(indexed sequential access method), 索引顺序存取方法, 索引循序存取方法

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM索引的原理图如下。这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

img

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

img

InnoDB索引实现：

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

img

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

img

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

R Tree

除空间索引外，InnoDB索引均为B 树数据结构。空间索引使用R 树，这是一种专门用于索引多维数据的数据结构。索引记录存储在其 B 树或 R 树数据结构的叶页中。索引页的默认大小为 16KB。页面大小由 MySQL 实例初始化时的innodb_page_size设置决定。参见第 17.8.1 节，“InnoDB 启动配置”。

当新记录插入到
InnoDB
聚集索引中时，
InnoDB会尝试保留页面的 1/16 空间，以便将来插入和更新索引记录。如果索引记录按顺序（升序或降序）插入，生成的索引页大约会被填满 15/16。如果记录以随机顺序插入，页面的填充程度会在 1/2 到 15/16 之间。

InnoDB在创建或重建 B 树索引时会执行批量加载。这种索引创建方法被称为排序索引构建。
innodb_fill_factor变量定义了在排序索引构建过程中每个 B 树页面上被填充的空间百分比，剩余空间预留用于未来的索引增长。空间索引不支持排序索引构建。有关更多信息，请参见
第 17.6.2.3 节，“排序索引构建”。当innodb_fill_factor设置为 100 时，会为未来的索引增长在聚集索引页中留出 1/16 的空闲空间。

如果InnoDB索引页的填充因子降至MERGE_THRESHOLD以下（如果未指定，默认值为 50%），InnoDB会尝试收缩索引树以释放该页面。MERGE_THRESHOLD设置同时适用于 B 树索引和 R 树索引。有关更多信息，请参见第 17.8.11 节 “配置索引页的合并阈值”。

# 索引

优点

  1. 提高数据查询效率，减低数据库IO成本
  2. 通过索引列对数据进行排序，降低数据排序成本，减低CPU开销

缺点

  1. 索引列占用额外空间
  2. 数据更新时需要对索引也进行更新，insert、update、delete等操作效率降低

MySQL的索引实在存储引擎层实现的，不同的存储引擎对索引结构有着不同的实现

InnoDB支持B+树索引和全文索引

hash

b+tree

索引分类

二级索引查询时还要回表查询

实践

表空间

系统表空间，System Tablespace

系统表空间存储更改缓冲区，以及存储表数据（若该表被指定创建在系统表空间）。

每表文件表空间，File-Per-Table Tablespace

每表文件表空间包含单个InnoDB表的数据和索引，并以单个数据文件的形式存储在文件系统中（即一个表对应一个表空间）。

通用表空间，General Tablespace

通用表空间是一个共享的InnoDB表空间。创建表时可以zhiding

它是使用
CREATETABLESPACE语法创建的。本节的以下主题介绍了通用表空间的功能和特性：

撤销表空间，Undo Tablespace

撤消表空间包含撤消日志，这些日志是记录的集合，其中包含有关如何撤消事务对聚集索引记录的最新更改的信息。

临时表空间，Temporary Tablespace
会话临时表空间，Session Temporary Tablespaces

会话临时表空间存储用户创建的临时表以及当InnoDB被配置为磁盘内部临时表的存储引擎时，优化器创建的内部临时表。磁盘内部临时表使用InnoDB存储引擎。

全局临时表空间，Global Temporary Tablespace

全局临时表空间（ibtmp1）存储对用户创建的临时表所做更改的回滚段。

双写缓冲区，Doublewrite Buffer

双写缓冲区是一个存储区域，在将从缓冲池刷新的页面写入InnoDB数据文件中的适当位置之前，InnoDB会先将这些页面写入该区域。如果在页面写入过程中，操作系统、存储子系统出现问题，或者mysqld进程意外退出，InnoDB可以在崩溃恢复期间从双写缓冲区中找到该页面的完好副本。

尽管数据会被写入两次，但双写缓冲区并不需要两倍的 I/O 开销或两倍的 I/O 操作。数据以一个大的连续块写入双写缓冲区，只需对操作系统进行一次fsync()调用（除非innodb_flush_method被设置为O_DIRECT_NO_FSYNC）。

doublewrite 缓冲区存储区域位于 doublewrite 文件中。

SHOW VARIABLES LIKE "%innodb_doublewrite%"

重做日志

重做日志是一种基于磁盘的数据结构，在崩溃恢复期间用于更正不完整事务所写入的数据。在正常操作中，重做日志会对因 SQL 语句或低级 API 调用而产生的更改表数据的请求进行编码。在意外关闭前未能完成数据文件更新的修改，会在初始化期间以及接受连接之前自动重新执行。

撤销日志

撤销日志是与单个读写事务相关联的一组撤销日志记录。一条撤销日志记录包含有关如何撤销事务对聚簇索引记录所做的最新更改的信息。如果另一个事务在一致性读操作中需要查看原始数据，那么未修改的数据会从撤销日志记录中获取。撤销日志存在于撤销日志段中，而撤销日志段又包含在回滚段中。回滚段位于撤销表空间和全局临时表空间中。

驻留在全局临时表空间中的撤销日志用于修改用户定义的临时表中数据的事务。这些撤销日志不会进行重做日志记录，因为崩溃恢复不需要它们。它们仅在服务器运行时用于回滚操作。这种类型的撤销日志通过避免重做日志记录的输入 / 输出操作，有助于提升性能。

InnoDB 锁

数据库锁体系

一切问题的根源来自读写并发：

  1. 读读，可以
  2. 读写，读时不能写
  3. 写读，写时不能读
  4. 写写，写时不能写

为保证读写并发安全性，数据库通常会使用三类方案：

  1. 悲观锁方案：使用共享锁和排他锁解决读写并发问题
  2. 乐观锁方案：使用版本号解决并发写问题
  3. 无锁方案：允许读操作读取历史数据以避免与写锁冲突

加锁方案为兼顾并发性能，通常会将资源分为不同粒度然后进行加锁，例如，关系型数据库存储引擎中通常有，全局锁、表级锁、页级锁、行级锁

InnoDB 中采用悲观锁方案和无锁方案：

  1. InnoDB 悲观锁中，实现了全局锁、表级锁、行级锁
  2. 无锁方案基于 MVCC 实现

全局锁，global-level lock

全局锁对整个数据库加锁。

常用于全库备份场景，注意：

  1. 若在主库备份，则备份期间主库只读，不能执行写操作。
  2. 若在从库备份，则备份期间从库不能执行由主库同步过来的二进制日志，会导致从库与主库数据不一致。
-- 注意，这里的库指的是show databases命令展示出来的库

-- 对全库加读锁，库变为只读状态
FLUSH TABLES WITH READ LOCK;

-- 解除全局锁
unlock tables;

image-20241021225036100

在InnoDB引擎中，可以在备份时加上--sinigle-transaction参数完成不加锁的数据备份。

mysqldump --single-transaction -u root -p 123456 t_department > t_department.sql

表级锁，table-level lock

表级锁是悲观锁。数据库连接断开时会自动释放锁。

自增锁

事务回滚时自增字段不会回滚。

元数据锁

元数据锁，meta data lock，MDL，对表结构做更新时加锁。

元数据锁也是基于一般表锁和意向锁体系的。

image-20241021233726895

查看元数据锁

select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;

image-20241021234029600

一般表锁

共享读锁，shared read

image-20241021233455116

LOCK TABLES department READ;
unlock tables department;

排他锁，exclusive write

image-20241021233505904

LOCK TABLES department WRITE;
unlock tables department;
意向锁

意向锁，Intention Lock，MyISAM、InnoDB都支持

为避免加表锁时，与其他事务中因执行DML而持有的行锁冲突，在InnoDB中引入了意向锁，使得加表锁时无需检查每行数据是否加锁，意向锁优化了一般表级锁的加锁开销。

未引入意向锁时，当A事务更新某一行数据时，事务B期望对表加锁需要检查每一行数据加锁情况。

image-20241021235547108

引入意向锁后，当事务A更新某一行数据时，需要先对表加意向锁，然后对行加锁，事务B期望对表加锁时直接检查表的意向锁即可。

image-20241021235718563

意向共享锁，Intention Share

意向排他锁。Intention Exclusive 

select * from department lock in share mode;

begin;
-- 加意向锁锁和行锁
select * from department lock in share mode;
-- 查看意向锁
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
commit;

image-20241022001115713

supremum pseudo-record

行级锁，row-level lock

每次操作锁定对应的行数据或者某一个行范围的数据。

InnoDB支持

InnoDB行锁是通过给索引上的索引项加锁来实现的，Innodb一定存在聚簇索引，行锁最终都会落到聚簇索引上，通过非聚簇索引查询的时候，先锁非聚簇索引，然后再锁聚簇索引。

记录锁

Record Lock，将精准匹配的精准记录锁住。已提交读和可重复读隔离级别均生效。

锁定单个行记录，

间隙锁

Gap Lock，可重复读级别生效，已提交读无效。

锁定记录间隙（不含边界记录），确保记录间隙中不会被更新数据。

临键锁

Next-Key Lock，临键锁，记录锁和间隙锁组合，锁住一个闭区间的记录。已提交读级别无效，可重复读级别生效。

默认情况下，InnoDB在可重复读级别下运行，采用临键锁。注意，InnoDB的行锁是针对索引加的锁，若不通过索引操作数据，则InnoDB将全表扫描对全表加锁，此时行锁升级为表锁。

image-20241022002353004

查看行锁和意向锁

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

begin;
select * from department where did = 4101 for update;
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
select * from department where did > 4102 and did < 4103 lock in share mode;
-- 在另一个事务执行，select * from department where did > 4102 and did < 4103 for update;，也没问题，间隙锁彼此兼容
select * from department where did >= 4103 for update;
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
commit;
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

开启事务，根据主键精确查询，对表加意向排他锁，对记录加排他记录锁。

image-20241022003714254

对某个间隙加共享间隙锁。

特别注意，间隙锁的作用在于锁住某个间隙，防止该间隙中插入数据，无论是共享间隙锁还是排他间隙锁，不同事务间均是互相兼容的。

image-20241022003741799

对某个上界加锁，存在记录加记录写锁，上界加记录锁。

image-20241022003809597

image-20241022005121839

行级锁

InnoDB存储引擎有3种行锁的算法，其分别是：

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。采用Next-Key Lock的锁定技术称为Next-Key Locking，其设计的目的是为了解决Phantom Problem（幻读）。而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁（predict lock）的一种改进。

InnoDB存储引擎实现了如下两种标准的行级锁：

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容。但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁，这种情况称为锁不兼容。下图显示了共享锁和排他锁的兼容性，可以发现X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

表级锁

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下图所示。

  1. InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
  2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁 / 排他锁 互斥。
  3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
  4. 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-intention-locks

锁的实现原理

InnoDB 行锁实现方式：
InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，
别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。（更多阅读：MySQL索引总结）
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。 应用设计的时候要注意这一点。

以下以InnoDB为例介绍

锁的粒度

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
最大程度的支持并发，同时也带来了最大的锁开销。
在 InnoDB 中，除单个 SQL 组成的事务外，
锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM表级锁模式：

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

MyISAM加表锁方法：
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作
（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

InnoDB加锁方法：
意向锁是 InnoDB 自动加的， 不需用户干预。
对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB
会自动给涉及数据集加排他锁（X)；
对于普通 SELECT 语句，InnoDB 不会加任何锁；
事务可以通过以下语句显式给记录集加共享锁或排他锁：
共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

锁升级

锁升级（Lock Escalation）是指将当前锁的粒度降低。举例来说，数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

数据库死锁

关于死锁：

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。

除了超时机制，当前数据库还都普遍采用wait-for graph（等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息：

通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。这是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

死锁（Deadlock Free）
死锁产生：
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。
MyISAM避免死锁：
在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。
InnoDB避免死锁：
为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
改变事务隔离级别
如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

一些优化锁性能的建议
尽量使用较低的隔离级别；
精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
选择合理的事务大小，小事务发生锁冲突的几率也更小
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
不要申请超过实际需要的锁级别
除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

LOCK TABLES 和 UNLOCK TABLES
Mysql也支持lock tables和unlock tables，这都是在服务器层（MySQL Server层）实现的，和存储引擎无关，它们有自己的用途，并不能替代事务处理。 （除了禁用了autocommint后可以使用，其他情况不建议使用）：

LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，
或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁

（表空间、段、区、页、行）

要不要锁，能不能锁，锁什么，怎么锁

锁的是不同粒度的数据，获得锁的是事务

最基础的概念，三类

由于mysql不同的表可以对应不同存储引擎，行级锁由存储引擎实现，表级锁由server层实现

一个锁，必然是乐观悲观锁之一，必然是读写锁之一，必然是某个粒度的锁

按思想分类：乐观锁、悲观锁

按属性分类：读锁、写锁、意向锁

为了优化读写锁，引入了意向锁机制。

image-20241021224738656

若一个事务请求的锁与当前数据加的锁相互兼容，InnoDB就将请求的锁授予该事务，反之，若两者不兼容，则请求锁的事务阻塞。

按粒度：全局锁、表级锁、行级锁

粒度越小，加锁开销越大，但读写性能整体会好

页级锁，page-level lock

InnoDB没有页级锁。

BerkeleyDB引擎，https://www.mysqlzh.com/doc/218.html

事务模型

标准事务模型

事务

一串sql命令

特性

事务需遵循ACID四个特性：

类型

集中式事务

单个节点参与的事务，单节点操作执行成功集中式事务即成功，单节点操作执行失败则集中式事务回滚

扁平式事务
链式事务

在带保存点的扁平事务的基础上，将多个带保存点的扁平事务连接，前一个事务的提交和后一个事务的开始为原子操作，这样前一个事务的上下文就可以被后一个事务获取，链式事务本质是传递上下文的多个事务

嵌套事务

树状事务组织形式，根节点为顶层事务，每一棵子树都可以是嵌套事务，叶子节点为扁平事务，嵌套事务由顶层事务统一控制提交，但如果任意子事务回滚会导致整个嵌套事务回滚

自治事务

自治事务在嵌套事务的基础上，让子树管理其下事务而不受顶层事务节制，自治事务提交后即使顶层事务回滚也不会造成自治事务回滚。自治事务常用于在外层事务记录日志，无论外层成功提交还是失败回滚，自治事务都可以记录日志。MySQL原生不支持自治事务，Oracle原生支持自治事务。

分布式事务

由多个节点参与的事务，各个节点操作都成功执行则分布式事务成功，任意节点操作失败则分布式事务全部回滚

链式事务和带保存点的扁平事务区别在于，前者是一个事务，后者是多个事务，表现在：

宕机时，使用带保存点的扁平事务执行A和B操作，扁平点设置在A与B操作之间，链式事务执行A和B操作使用两个事务，在事务执行正常时，两种方案执行情况相同，在事务正在执行未提交时，假定此时数据库宕机,，恢复后数据库会回滚，带保存点的扁平事务会被完全回滚，而对于链式事务，如果执行A时宕机，那么与前者结果一样，如果执行B时宕机，A操作所在事务却是成功持久化了，同样的，带保存点的扁平事务在提交之前可以随意回滚到某个保存点，但链式事务只能在链上每个事务中回滚，已经提交的链上事务无法回滚

锁，当带有保存点的扁平事务完成保存点之前的操作时，该部分操作使用的锁并不会释放，因为是一个事务，而对于链式事务完成一个链上事务就会将该事务的锁释放

隔离级别

SQL标准定义了四种隔离级别，这四种隔离级别分别是：

事务隔离是为了解决脏读、不可重复读、幻读问题，下表展示了 4 种隔离级别对这三个问题的解决程度：

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED 可能 可能 可能
READ COMMITTED 不可能 可能 可能
REPEATABLE READ 不可能 不可能 可能
SERIALIZABLE 不可能 不可能 不可能

并发情况下，读操作可能存在的三类问题：

  1. 脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
  2. 不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
  3. 幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

InnoDB 事务模型

  1. 模型融合：InnoDB 事务模型致力于结合多版本控制数据库的优势与传统两阶段锁定机制，兼顾并发性能与数据一致性。
  2. 默认行为：采用行级锁定，并默认以非锁定的一致性读方式执行查询，风格类似 Oracle，减少锁冲突以提升并发效率。
  3. 锁存储优化：锁信息存储具有空间高效性，无需进行锁升级（将行锁升级为表锁等），因此允许多个用户锁定表中全部或任意随机子集的行，且不会导致 InnoDB 内存耗尽，保障了高并发场景下的稳定性。

类型

InnoDB存储引擎支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务，MySQL数据库并不是原生的，因此对于有并行事务需求的用户来说MySQL就无能为力了，但是用户可以通过带有保存点的事务来模拟串行的嵌套事务。

MVCC

多版本并发控制，MVCC，Multi-Version Concurrency Control

性能问题

在不使用读写锁的自然状态下：

读写、写写紊乱问题可以通过加锁解决。引入 MVCC 来优化读写并发场景的性能问题，写时允许读数据的历史版本而不是阻塞读操作从而提高读性能。

核心思想

维护数据的多个版本，保证写最新数据，但不保证读最新数据，读写并发时写加锁，读不加锁但读历史版本，读写不冲突，读写锁限制被绕开，读多写少场景下极大提高了并发性能。

实现原理

内部字段

字段名称 长度 含义
DB_ROW_ID 6 字节 聚簇索引若由InnoDB生成，则生成本列构建聚簇索引，否则本列不会存在。
DB_TRX_ID 6 字节 最后一次插入或更新（包括删除）该行的事务的标识符。删除在内部被视为一种更新，此时行中的一个特殊位会被设置，以将其标记为已删除。
DB_ROLL_PTR 7 字节 回滚指针。回滚指针指向写入回滚段的撤销日志记录。如果该行已被更新，撤销日志记录包含重建该行更新前内容所需的信息。

undo log 版本链

每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链

不同事务或者相同事务对同一行数据修改，会在修改前记录该数据到 undo log 版本链中，链表头部是最新的历史记录，链表尾部为最旧的历史记录。

两类undo log，insert时，产生的undo log只有在回滚时才会用到，只要事务提交，该日志即可删除，而update或delete时，产生的undo log除了在回滚时需要，在快照读时以版本链的形式提供读取依据，不会被立即删除

InnoDB是一种多版本存储引擎。它保留已更改行的旧版本信息，以支持并发和回滚等事务功能。这些信息存储在撤消表空间中一个名为回滚段的数据结构里。参见第 17.6.3.4 节，“撤消表空间”。
InnoDB利用回滚段中的信息执行事务回滚所需的撤消操作。它还使用这些信息构建行的早期版本，以实现一致性读取。参见第 17.7.2.3 节，“一致性非锁定读取”。

回滚段中的撤销日志分为插入撤销日志和更新撤销日志。

在
InnoDB多版本控制方案中，当您使用 SQL 语句删除一行时，该行不会立即从数据库中被物理删除。
InnoDB只有在丢弃为删除操作写入的更新撤销日志记录时，才会物理删除相应的行及其索引记录。这种删除操作称为清理，速度相当快，通常所需时间与执行删除操作的 SQL 语句大致相同。

InnoDB多版本并发控制（MVCC）处理二级索引的方式与聚集索引不同。聚集索引中的记录会就地更新，其隐藏的系统列指向撤销日志条目，通过这些条目可以重建记录的早期版本。与聚集索引记录不同，二级索引记录不包含隐藏的系统列，也不会就地更新。

当二级索引列被更新时，旧的二级索引记录会被标记为删除，新的记录会被插入，而被标记为删除的记录最终会被清除。当二级索引记录被标记为删除，或者二级索引页被较新的事务更新时，
InnoDB 会在聚集索引中查找数据库记录。在聚集索引中，会检查记录的
DB_TRX_ID，如果该记录在读取事务启动后被修改过，则会从撤销日志中检索该记录的正确版本。

如果二级索引记录被标记为删除，或者二级索引页被更新的事务所更新，那么就不会使用覆盖索引技术。InnoDB不会从索引结构中返回值，而是会在聚集索引中查找记录。

但是，如果启用了
索引条件下推（ICP）优化，并且
WHERE条件的部分内容仅使用索引中的字段就能进行评估，MySQL 服务器仍会将
WHERE条件的这部分内容下推到存储引擎，由存储引擎使用索引来进行评估。如果没有找到匹配的记录，就可以避免聚集索引查找。如果找到了匹配的记录，即便在带有删除标记的记录中，
InnoDB也会在聚集索引中查找该记录。

InnoDB 的多版本并发控制（MVCC）对二级索引和聚集索引的处理方式存在显著差异，核心区别及机制如下： InnoDB 的

  1. 存储与更新方式的差异
    • 聚集索引：记录会被就地更新，且包含隐藏系统列（如 DB_TRX_ID、DB_ROLL_PTR），这些列指向 undo 日志，可通过日志重建记录的历史版本。
    • 二级索引：不包含隐藏系统列，且不会就地更新。当二级索引列被更新时，旧记录会被标记为 “删除”，新记录会被插入，标记删除的记录最终会被清除（purge）。
  2. 版本验证与查询机制
    • 当二级索引记录被标记删除，或其所在页面被更新时，InnoDB 会通过聚集索引查找原始记录，并检查聚集索引中记录的 DB_TRX_ID，确认是否需要从 undo 日志中获取事务开始前的正确版本。
  3. 索引技术的限制与优化
    • 覆盖索引失效：若二级索引记录被标记删除或页面被更新，覆盖索引（直接从索引返回数据）无法使用，必须通过聚集索引查询完整记录。
    • 索引条件下推（ICP）仍有效：若启用 ICP，且 WHERE 条件可仅通过二级索引字段判断，MySQL 会将条件下推到存储引擎，通过索引筛选记录。若未找到匹配项，可避免聚集索引查询；若找到匹配项（即使是标记删除的记录），仍需通过聚集索引获取最终版本。

简言之，二级索引不直接参与多版本管理，而是依赖聚集索引和 undo 日志验证版本，同时通过特定机制（如 ICP）优化查询效率。

锁定读取（当前读）

读取记录的最新版本，阻塞读取，读取时对记录进行加锁，保证其他并发事务不能修改当前记录，例如：

一致性非锁定读取（快照读）

读取记录的可见版本（即不保证一定是最新版本），非阻塞读取

通过隐藏列构建的版本链，可以实现写操作执行时并行读历史数据的操作，读取时版本选择由 ReadView 确定。

readview，读视图

读视图是指事务在某时刻为整个事务系统生成快照，随后读取数据时基于该快照判断当前事务可读取的版本。

读视图记录 InnoDB 当前活跃事务（即未提交）的 ID。包含四个字段：

  1. creator_trx_id：视图创建者事务ID
  2. m_ids：当前活跃的事务ID集合
  3. min_trx_id：最小活跃事务ID
  4. max_trx_id：最大活跃事务ID，当前最大事务ID+1（事务ID自增）

db_trx_id 为行记录中最后一次插入或更新该记录的事务ID，版本链版本选择从最新数据开始遍历，选择第一个满足条件的版本：

  1. db_trx_id == creator_trx_id：当前版本数据最后一次为当前事务所操作过的，使用该版本数据

  2. db_trx_id < min_trx_id：当前版本数据最后一次为一个非活跃事务操作过的（即事务已提交），使用该版本数据

  3. min_trx_id <= db_trx_id < max_trx_id

    1. 若 db_trx_id 不在 m_ids 中，当前版本数据最后一次为一个非活跃事务操作过的（即事务已提交），使用该版本数据
    2. 若 db_trx_id 在 m_ids 中，当前版本数据最后一次为一个活跃事务操作过的（即事务还没提交），不能使用该版本数据

  4. db_trx_id >= max_trx_id，当前版本数据最后一次为一个比当前事务及当前读事务中所有事务都新的事务，不能使用该版本数据

[!IMPORTANT]

MVCC 在 RC 和 RR 两个隔离级别生效，读视图在两个级别生成时机不同：

  1. READ COMMITED：在事务中每执行一次快照读时均生成一次读视图。
  2. REPEATABLE READ：在事务中第一次执行快照读时生成读视图，随后该事务中的快照读复用该视图。

故在两次快照读中间有事务修改数据并提交，会RC 级别下会发生数据不一致问题。

读已提交

可重复读

ACID

Server 和 Engine 之间使用两阶段提交保证一致性：

崩溃恢复时就靠这两个阶段写下的信息判断：

• Redo log 是 固定大小的环形文件（ib_logfile0/1/...），后台由 log_writer/log_flusher 线程循环复用，不是“清理”而是“覆盖”。
• Undo log 是 可以无限增长、需要真正回收 的文件段（undo_001/002 或独立表空间），由专门的 purge 线程在满足条件后异步删除。

一、Redo log 的“清理”——其实是循环覆盖

  1. 结构
    Redo log 是一组固定大小（innodb_log_file_size * innodb_log_files_in_group）的文件，InnoDB 把它当做一个 逻辑上首尾相接的圆环。
  2. 复用规则
    • 每当 checkpoint 推进（log_checkpoint()）时，InnoDB 把 Buffer Pool 中已落盘的脏页对应的 LSN 之前的 redo 记录视为“无用”。
    • 这些无用记录所占的日志空间即可 被新事务覆盖；后台线程 log_writer/log_flusher/log_checkpointer 只负责把待覆盖区域写满新日志并 fsync，不会删除文件。
    • 因此，Redo log 永远不需要“清理文件”，只要 checkpoint 不落后，就能一直复用。
MVCC机制在可重复读隔离级别下对幻读问题的贡献

四个隔离级别中，已提交读和可重复读两个级别使用了MVCC。

在事务隔离级别中，可重复读级别是存在幻读问题的，而MySQL中，在实现上，一定程度解决了幻读的几个场景。

MVCC只是用来优化读写并发场景下读的性能，并不能解决幻读问题。不过InnoDB使用间隙锁在MVCC的基础上解决了某些场景下的幻读问题。

事务看到的数据不一定真的存在，没有看到数据不一定不存在。

先分析一下幻读会出现的场景。

A事务查询id=1数据发现不存在

B事务插入该条id=1数据

A事务再次查询发现

A事务快照读id=1数据发现不存在

B事务插入该条id=1数据

A事务再次快照读没有发现数据

A事务当前读id=1数据发现不存在

B事务插入该条id=1数据，B事务会阻塞

A事务再次当前读没有发现数据

A事务快照读1 <=id<=5数据发现只有id为1、2、3的三条

B事务插入该条id=4和id=5数据

A事务再次快照读还是发现只有id为1、2、3的三条

A事务当前读1 <=id<=5数据发现只有id为1、2、3的三条

B事务插入该条id=4和id=5数据，被next-key-lock阻塞

A事务再次当前读还是发现只有id为1、2、3的三条

A事务快照读1 <=id<=5数据发现只有id为1、2、3的三条

B事务插入该条id=4和id=5数据

A事务当前读还是发现只有id为1、2、3、4、5的五条

单纯的MVCC机制并不能解决幻读问题，InnoDB也是通过加间隙锁来防止幻读。

快照读和当前读是否可重复读？不可。幻读也是这个问题。

create table if not exists department( did numeric(4), dname varchar(20), dlocation varchar(20), primary key(did) );
truncate department;
insert into sqlexercise.department value(4101,'行政部','北京');

A事务

begin; -- 1
select * from department; -- 2
select * from department; -- 6
select * from department for update; -- 7
commit; -- 9

B事务

begin; -- 3
select * from department; -- 4
update department set dlocation = '上海' where did = 4101; -- 5
commit; -- 8

快照读和当前读同时读取同一条数据有可能会出现不可重复读问题。

索引

InnoDB 事务 ACID

MySQL事务ACID特性实现原理

原子性实现原理

基于undo log回滚，undo log属于逻辑日志，记录sql执行相关的信息。如果事务执行失败或调用了rollback，导致事务需要回滚，InnoDB会根据undo log的内容做与之前相反的工作。对于insert，回滚时会执行delete。对于delete，回滚时会执行insert。对于update，回滚时则会执行相反的update，将数据还原。

持久性实现原理

基于磁盘存储，Buffer Pool解决读写速率问题，redo log解决Buffer Pool在MySQL崩溃时未写入磁盘数据的问题

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool。当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题。当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作。当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

隔离性实现原理

基于锁和MVCC实现，隔离性要求并发条件下多个事务之间不相互影响

  1. 一个事务写操作对另一个事务写操作的影响，锁机制保证隔离性

    隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁。获得锁之后，事务便可以修改数据。该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差。行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

  2. 一个事务写操作对另一个事务写操作的影响，MVCC保证隔离性

    InnoDB默认的隔离级别是RR（REPEATABLE READ），RR解决脏读、不可重复读、幻读等问题，使用的是MVCC。MVCC全称Multi-Version Concurrency Control，即多版本并发控制。它最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下技术及数据结构：

    隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。

    基于undo log的版本链：每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

    ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

一致性实现原理

一致性要求事务中的数据变化规则与现实世界事物变化规则一致。。。。。

以转账为例，现实世界中，张三给李四100个贝壳，张三失去100贝和李四获取100贝是同时发生的，所以对于数字化实现而言，张三账户贝壳余额减100和李四账户贝壳加100必须位于同一个事务中以保证数字规则对现实规则的模拟

InnoDB事务实现

原子性如何保证

一致性如何保证

。。。

隔离性如何保证

在加锁的基础上使用MVCC进行优化，MVCC文末详解

持久性如何保证

redolog刷新到磁盘是事务的一部分，如果失败则事务提交不成功

redolog，追加日志，写的形式是顺序磁盘IO，而对于更改的数据，整体上是随机更改的，写的时候就是随机磁盘IO，前者效率较高，所以使用redolog保证持久化，而直接将脏页写入磁盘也可以，但由于性能较差，其持久性可靠程度并不如redolog日志

脏页写入磁盘后，相应部分的redolog便不再需要保存

当然还有binlog

事务隔离级别

InnoDB 提供 SQL:1992 标准所描述的全部四种事务隔离级别：
未提交读（READ UNCOMMITTED）、
已提交读（READ COMMITTED）、
可重复读（REPEATABLE READ） 以及
可串行化（SERIALIZABLE）

MySQL 中隔离级别默认采用 REPEATABLE READ。

脏读 不可重复读 幻读
READ UNCOMMITTED Y Y Y
READ COMMITTED N Y Y
REPEATABLE READ（默认） N N N
SERIALIZABLE N N N

这四种隔离级别的实现机制如下：

  1. READ UNCOMMITTED & READ COMMITTED：

    通过Record Lock算法实现了行锁，但READ UNCOMMITTED允许读取未提交数据，所以存在脏读问题。而READ COMMITTED允许读取提交数据，所以不存在脏读问题，但存在不可重复读问题。

  2. REPEATABLE READ：

    使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

  3. SERIALIZABLE：

    对每个SELECT语句后自动加上LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

4种隔离级别MySQL都支持，并且InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，因此避免了幻读的产生。所以，InnoDB存储引擎在默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别。

可重复读

这是InnoDB的默认隔离级别。同一事务中的一致性读取会读取由第一次读取所建立的快照。这意味着，如果在同一事务中执行多个普通（非锁定）SELECT语句，这些SELECT语句彼此之间也是一致的。参见第 17.7.2.3 节，“一致性非锁定读取”。

对于锁定读取（带FOR UPDATE或FOR SHARE的SELECT）、UPDATE和DELETE语句，其锁定范围取决于语句的查询条件：

[!TIP]

不建议在单个可重复读取事务中将锁定语句（UPDATE、INSERT、DELETE或带FOR UPDATE或FOR SHARE的SELECT）与非锁定SELECT语句混合使用，因为通常在这种情况下，您需要SERIALIZABLE。这是因为非锁定SELECT语句从读取视图显示数据库的状态，该视图由创建读取视图之前和当前事务自身写入之前提交的事务组成，而锁定语句使用数据库的最新状态来使用锁定。一般来说，这两种不同的表状态彼此不一致，难以解析。

读已提交

每一次一致性读取，即便在同一个事务内，都会设置并读取自己全新的快照。

对于锁定读取（SELECT 与 FOR UPDATE 或 FOR SHARE）、UPDATE 语句和 DELETE 语句，InnoDB 仅锁定索引 记录，而不是它们面前的空白，从而允许自由 在锁定的记录旁边插入新记录。间隙锁定 仅用于外键约束检查和 重复键检查。

由于间隙锁定被禁用，可能会出现幻影行问题，因为其他会话可以在间隙中插入新行。有关幻影行的信息，请参阅第 17.7.4 节 “幻影行”。

  1. 核心限制：当事务隔离级别设置为READ COMMITTED时，仅支持基于行的二进制日志格式（row-based logging）。这是因为该隔离级别下的锁定机制（如不使用间隙锁）可能导致语句级日志（statement-based）无法准确记录事务行为，而基于行的日志能更精确地记录每行数据的修改。
  2. 自动适配：若隔离级别为READ COMMITTED，且二进制日志格式设置为MIXED（混合模式，会根据场景自动选择语句级或行级日志），MySQL 服务器会自动切换为行级日志，以确保日志的准确性和一致性。

对于 UPDATE或 DELETE语句， InnoDB仅对其更新或删除的行持有锁。在MySQL评估完 WHERE条件后，会释放不匹配行的记录锁。这极大地降低了死锁的可能性，但死锁仍有可能发生。

对于
UPDATE语句，如果某一行已被锁定，
InnoDB会执行一次
“半一致” 读取，将最新提交的版本返回给 MySQL，以便 MySQL 能够判断该行是否符合
WHERE条件以及
UPDATE。如果该行匹配（必须更新），MySQL 会再次读取该行，此时
InnoDB要么锁定该行，要么等待获取该行的锁。

读未提交

SELECT语句以非锁定方式执行，但可能会使用行的早期版本。因此，使用此隔离级别时，此类读取是不一致的。这也称为脏读。除此之外，此隔离级别的工作方式与READ COMMITTED类似。

可串行化

从 MySQL 授权表读取数据（通过连接列表或子查询）但不修改它们的 DML 操作，无论隔离级别如何，都不会在 MySQL 授权表上获取读锁。有关更多信息，请参阅授权表并发。

README