MySQL事务与并发控制详解-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_36629833/article/details/123407732

本文详细介绍了MySQL中的事务管理，包括ACID原则、存储引擎隔离级别、事务日志和多版本并发控制（MVCC）。同时，文章还讨论了InnoDB的MVCC实现、索引类型以及不同类型的索引在不同场景下的应用。此外，还涵盖了存储引擎InnoDB和MyISAM的特点，以及如何利用索引优化查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

事务

ACID原则

原子性：事务必须是一个最小的工作单元，要么成功要么失败
一致性：一个事务必须使数据库从一个一致性状态变换到另一个一致性状态
隔离性：一个事务所做的修改在最终提交之前，是不会被其他事务感知
持久性：一旦事务提交，则其修改会永远保存到数据库中

存储引擎隔离级别

未提交读：事务的修改即使未提交对其他的事务也是可见，会导致脏读
提交读：事务的修改未提交前对其他的事务不可见
可重复读：mysql默认事务隔离级别，保证同一事物多次读取同样的记录结果一致，无法解决幻读问题
可串行化：强制事务串行执行，给每一行数据都加锁

不同的隔离级别会导致不同的问题，例如脏读、不可重复读、幻读。

脏读：A和B两个事务同时修改同一个数据，A修改的提交在B提交之后，导致B好像没有修改，丢失修改
不可重复读：事务A查询数据之后，数据被事务B修改，导致事务A两次读取数据不一致
幻读：在一次事务里面，多次查询之后，查询的结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行，幻读是由插入或者删除引起的

事务日志

存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久化在硬盘的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。预写式日志。修改数据需要写两次磁盘。

事务日志持久化以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。

多版本并发控制

mysql大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能考虑，实现了多版本并发控制(MVCC)。不同的存储引擎的MVCC实现方式不一样，典型的有乐观并发控制和悲观并发控制。

InnoDB的MVCC是在可重复读的隔离级别下实现的，通过在每行后面记录两个隐藏列来实现，这两个列，一个保存行的创建时间，一个行的过期时间(删除时间)。当然存储的并不是实际的时间，而是系统版本号。每开始一个新的事务，系统版本号会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比价。

select（快照读），因为读的时候快照中的内容，是历史数据，所以读的时候不会有幻读。开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读。

a. InnoDB会查找早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前存在的，要么是事务自身插入或者修改过的。
b. InnoDB会查找行的删除版本要么未定义，要么大于当前事务版本号的，这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果。

insert、delete（当前读，会加锁）

保存存当前系统版本号作为行版本号

update（当前读）

InnoDB为插入一行新记录保存存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

快照读和当前读

快照读：在RC（提交读）隔离级别中每次select都生成一个快照读。在RR（可重复读）隔离级别中，开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读，事务后续的读取都是基于最开始创建的快照。
当前读：读取的是最新版本，并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。

存储引擎

InnoDB存储引擎

采用MVCC来支持高并发，默认隔离级别为可重复读，并通过next-key锁（行锁+间隙锁）来防止幻读，支持事务和行锁。

MyISAM引擎

不支持事务、只支持表锁。

索引

索引的类型

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

限制

哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。例如，在数据列(A、B)上建立哈希索引，如果查询只有数据列A,则无法使用该索引。
哈希索引只支持等值比较查询，包括=,in,<=>。也不支持任何范围查询
访问哈希索引的数据非常快，除非有很多哈希冲突。

B-Tree索引

InnoDB中使用的是B树的变种B+树，B+树数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址，而B树数据存在各个节点中。
B+树相对B树的优势

B+树更加适应磁盘的特性，非叶子节点只存储关键字，单个页中能够存储更多的关键字，这样一次性读入内存需要查找的关键字也就越多，磁盘的随机IO就相对减少。
B+树的查询效率相比B树更加稳定，由于数据只存在在叶子结点上，所以查找效率固定为O(log n)。
B+树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+树的效率更高。

B树索引适用于

全值匹配：全值匹配指的是和索引中的所有列进行匹配。
匹配最左前缀：即只使用索引的第一列。
匹配列前缀：也可以只匹配某一列的值的开头部分。例如用于查找以J开头的姓的人，这里也只使用了索引的第一列。
匹配范围值：例如查找姓在Allen和Barraymore之间的人，这里也只使用了索引的第一列。
精确匹配某一列并范围匹配另外一列：例如查找所有姓为Allen，并且名字是字母K开头的人。即第一列last_name全匹配，第二列firt_name范围匹配。

B树索引的限制

如果不是按照索引的最左列开始查找，联合索引则无法使用索引。
不能跳过索引中的列。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。
红黑树：树的高度随着数据量增加而增加，IO代价高，自旋效率低。

空间数据索引

可以用作地理数据存储。MyISAM存储引擎。

全文索引

是一种特殊类型索引，它查找的是文本中的关键字词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有很多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的where条件匹配。

聚簇索引和非聚簇索引

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，聚簇索引的实现依赖于存储引擎，并不是所有的存储引擎都支持聚簇索引，以InnoDB为例，聚簇索引是将索引和数据行都存储在一个B-Tree树中，数据行存储在叶子页中，页节点中只存放索引的主键值，聚簇就是表示数据行和相连的键值紧密的存储在一起。一个表中只能有一个聚簇索引，其他的索引都是二级索引，二级索引的叶子页中存放的是聚簇索引的主键值，因此二级索引查找数据需要查询两次，即先根据索引键值找到主键值，在根据主键值到聚簇索引中找到具体的数据行。

优点

可以把相连的数据保存在一起，减少磁盘IO，提高了IO密集型应用的性能，例如查询某个用户的邮件，可能只需要查询几个页数据就能获取用户的全部文件。
数据访问更快，聚簇索引将索引和数据保存在一个B-Tree树中，因此聚簇索引比非聚簇索引获取数据要快，因为非聚簇索引还需要到聚簇索引中查找到具体的数据行。
使用覆盖索引扫描可以直接使用页节点中的主键值
聚集索引这种主+辅索引的好处是，当发生数据行移动或者页分裂时，辅助索引树不需要更新，因为辅助索引树存储的是主索引的主键关键字，而不是数据具体的物理地址。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引要比非聚簇索引查询效率高很多

缺点

插入速度严重依赖插入顺序。
更新聚簇索引列的代价很高，以为会强制InnoDB将每个被更新的行移动到新的位置。
基于聚簇索引的表再插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂的问题，页分裂会导致表占用更多的磁盘空间。
聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据不连续。
二级索引访问需要查询两次索引。

mysql索引中和页的关系
我们知道操作系统是采用页式内存管理的，所以mysql索引将一个节点的大小设置为一个页的大小，这样一个节点只需要一次磁盘IO就可以完全载入，减少磁盘IO的次数。

非聚簇索引

非聚簇索引和聚簇索引的区别在于，叶子页中存储的是具体的数据行。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询字段的值，那么就可以称这个索引为覆盖索引。

优点

索引的大小远小于数据行的大小，所以如果只需要读索引就能获取到需要的值，那么会极大的减少数据访问量，和磁盘IO。
因为索引是按照列的值顺序存储的，对于IO密集型的范围查询会比随机从磁盘读取一行数据的IO要少的多。
对于InnoDB的聚簇索引，覆盖索引特别有用，因为二级索引中如果保存了所有需要查询的列，那么则可以避免对主键索引的二次查询。

使用索引做排序

MySQL有两种方法生成有序的结果：通过排序操作；或者通过按照索引顺序扫描；扫描索引本身是很快的，因为只需要从一条记录移动到下一条记录，但如果索引不能覆盖查询所需的列，那么不得不没扫描一条记录就回表查询一次对应的行，者这都是随机IO，因此按照索引顺序读取数据通常要比顺序的全表扫描慢。

使用索引做排序需要满足以下条件：

索引列的顺序和ORDER BY的顺序完全一直，并且所有列的排序方向完全一致。
查询关联多张表时，只有ORDER BY的字段全部都是第一张表的字段，才能做索引排序。
ORDER BY不满足最左前缀原则时，那么前导列必须为常量，才能使用索引排序。

InnoDB多版本控制

InnoDB聚簇索引会给每一行记录增加三个隐藏字段，DB_TRX_ID、DB_ROOL_PTR、DB_ROW_ID。

DB_TRX_ID（事务id），每次对某数据记录进行修改时，都会把对应的事务id赋值给DB_TRX_ID列。每次事务操作都会分配一个事务id，它是一个自增id。
DB_ROOL_PTR（回滚指针），指向回滚日志（undo log）中对应该数据的回滚日志，每次对数据进行修改，mysql都会将修改前的数据按照一定的日志格式写入回滚日志，DB_ROOL_PTR就是指向该日志的一个指针。
DB_ROW_ID（数据行id），并不是必要的，如果表中有主键或者非NULL的唯一键时就不会添加DB_ROW_ID字段。

回滚日志中分为插入回滚日志和更新回滚日志，插入回滚仅在事务回滚是需要，因此在事务提交后即可丢弃，更新回滚日志在读一致性时也需要用到，因此它只有在InnoDB没有为其分配快照的事务后才能丢弃它。

复制

复制流程概述

MySQL复制过程大致分为三步：

主库将数据的更改记录都写到二进制日志中（Binary Log），每一个条改动记录在MySQL中都叫做一个日志事件。每次在准备提交事务完成之前，都会先将数据的更新事件记录到二进制日志中。
备库通过开启一个I/O线程从主库同步二进制日志到自己本地的中继日志（Relay Log）中。
备库另外在开启一个SQL线程，执行中继日志中的事件，将其重放到备库数据库中。

复制原理

基于语句的复制
主库通过记录那些对数据进行更改的语句到二进制日志中，备库通过读取并重放这些语句即可实现主备保持同步，通过这种方法实现的复制，写入日志文件的数据更少，结构更加紧凑，当更新或删除影响许多行时，日志文件只需记录少量的SQL语句即可，这也意味着可以更快地完成备份和恢复。

**缺点：**虽然基于语句的复制，能够减少写入日志文件的数据，但是当语句中含有非确定性行为时，这时候基于语句的复制都会有问题。例如语句中包含UUID()、USER()、RAND()等函数时，才备库重放该语句时就会有问题，无法保证执行后备库和主库语句执行出来对数据的更新效果都是一致的。

基于行的复制

主库通过将实际的数据记录在二进制日志中，从而确保所有的行都能够正确的被复制，并且无需重放SQL语句即可直接更新数据。

**缺点：**会导致二进制日志中记录更多的数据，尤其是对数据进行全部更新时，这个时候需要将所有的行数据都记录到二进制日志中，使得二进制日志十分庞大。

MySQL默认采用基于语句的复制，当基于语句的复制无法正确复制时就会切换成基于行的复制。

二进制日志

二进制日志包含描述数据库更改的事件，如表创建操作或表数据更改。它还包含可能进行更改的语句的事件(例如，不匹配任何行的DELETE)，除非使用了基于行的日志记录。

二进制日志的作用

1、在复制的时候用于供副本进行数据同步。
2、某些数据恢复操作需要使用二进制日志。

二进制日志的提交时机

二进制日志记录是在一个语句或事务完成后，但在任何锁被释放或任何提交完成之前立即完成的。这确保了日志是按提交顺序记录的。对非事务性表的更新在执行后立即存储在二进制日志中。

InnoDB架构

在这里插入图片描述

InnoDB内存架构

Buffer Pool（缓冲池）

缓冲池是主存中的一个区域，InnoDB在这里缓存表和索引数据。缓冲池允许从内存中直接访问经常使用的数据，这加快了处理速度。在专用服务器上，高达80%的物理内存通常分配给缓冲池。

缓冲池采用LRU算法对缓冲池列表中的内容进行管理，当需要空间将新页面添加到缓冲池时，最近最少使用的页面将被移除，并将新页面添加到列表的中间。这个中点插入策略将列表视为两个子列表，顶部是最近访问的新(年轻)页面的子列表，在尾部，是最近访问较少的旧页面的子列表。
在这里插入图片描述

缓冲池的 3/8 专用于旧子列表
列表的中点是新子列表的尾部与旧子列表的头部的边界
访问旧子列表中的页面会使其年轻，将其移动到新子列表的头部。
在数据库操作时，缓冲池中未被访问的页面会向列表的尾部移动。新子列表和旧子列表中的页面都会随着其他页面的更新而老化。旧子列表中的页面也随着页面插入到中点而老化。最终，仍然未使用的页面到达旧子列表的尾部并被移除。

Change Buffer（更改缓冲）

更改缓冲区主要用来记录对二级索引的更改，因为二级索引通常都不是唯一的，并且以相对随机的顺序插入二级索引，所以更新二级索引通常伴随着大量的随机I/O。如果当要更新的二级索引页不在缓冲池中（内存）的时候，我们将对页的修改记录在Change Buffer中，同时记录redo log，之后在慢慢将数据加载到内存，加载到内存后再将Change Buffer里记录的修改应用到内存中（Buffer Pool），这个动作叫做merge（合并），随后内存中被修改的数据随着页面在缓冲池中的老化最终刷新到磁盘，这个动作叫做purge（清除）。这样可以减少大量的随机I/O，例如读取数据到内存，以及将更新的数据写到磁盘的I/O。

为什么对聚簇索引的更新不需要记录在Change Buffer中？

因为聚簇索引」（clustered indexes）必须是「唯一」的，也就意味着每次插入、更新，都需要检查是否已经有相同的字段存在，也就没有必要使用 Change Buffer 了；另外，「聚簇索引」操作的随机性比较小，通常在相邻的「页」进行操作，比如使用了自增主键的「聚簇索引」，那么 insert 时就是递增、有序的，不像「二级索引」，访问非常随机。