MySQL事务基本概念和存储引擎

最新推荐文章于 2024-08-13 09:29:28 发布

置顶黑字Black Byte

最新推荐文章于 2024-08-13 09:29:28 发布

阅读量336

点赞数

分类专栏：数据库文章标签： mysql 数据库事务存储引擎隔离级别

本文链接：https://blog.csdn.net/qq_34218615/article/details/79835731

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

基本概念

1.事务

事务即一组原子性的数据库操作序列（SQL语句），这些操作要么全做要么全都不做。数据库引擎能成功地应用一组查询的全部语句，那么就执行该组查询，否则搜由于局都不会执行

事务的特性

原子性(atomicity)：一个事务必须背视为一组不可分割的工作单元。整个事务中的操作要么全部成功提交(commit)，要不全部失败回滚(rollback)。
一致性(consistency)：数据库总是从一个一致性状态到另一个一致性状态。因此当数据库只包含成功事务提交的结果的时候，就说数据库处于一致性状态。一致性与原子性密切相关。
隔离性(isolation)：一个事务的执行不能被其他事务干扰。通常来说，一个事物最终提交以前，对其他事务是不可见的。
持久性(durability)：一旦事务提交，其所做的修改就会永久保存到数据库中。

事务的ACID特性可以保证数据的正确性与完整性，但在应用逻辑中要实现这一点还是非常难的，一个兼容ACID的数据库系统，需要做很多复杂但可能用户没有察觉到的工作，才能确保ACID的实现。而实现了ACID的数据库比没有实现ACID的数据库，通常会需要更强的CPU处理能力，更大的内存和更多的磁盘空间,所以Mysql需要根据实际需求选择合适的存储引擎。

隔离级别

READ UNCOMMITED(未提交读): 一个事务中的修改，即使没有提交对其它事务也是可见的。事务可以读取未提交的数据，也被称为脏读。除非必要，一般实际需求中很少使用。
READ COMMITED(提交读): 一个事务提交之前的数据对其他事务不可见。一个事务中多次执行同样的查询，可能得到不同结果，所以也叫不可重复读。这也是大多数数据库系统的默认隔离级别（mysql不是）。
REPEATABLE READ(可重复读): 一个事务中多次读取相同记录的结果是一致的。可解决脏读的问题，无法解决幻读——指某个事务在读取某个范围的记录时，另一个事务又在该范围内插入了新记录，当之前的事务再次读取该范围中的记录时，会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC)解决了幻读问题。mysql默认的隔离级别。
SERIALIZABLE(可串行化): 最高的隔离级别，它通过强制事务串行执行避免幻读问题。简单来说，它会在读取的每一行都加锁，所以可能导致大量超时和锁争用问题，实际应用中也很少用到这个隔离级别。只有非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑该级别

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITED	yes	yes	yes	no
READ COMMITED	no	yes	yes	no
REPEATABLE READ	no	no	yes	no
SERIALIZABLE	no	no	no	yes

死锁

数据库中指两个或多个事务在同一资源上相互占用，并请求对方占用的资源，从而导致恶性循环的现象。

死锁实例

事务1：
start transaction;
update table1 set close=123 where id=4;
update table2 set close=456 where id=3;
commit;

事务2：
start transaction;
update table1 set close=456 where id=3;
update table2 set close=123 where id=4;
commit;

如果凑巧，两个事务都执行到了第一条update语句，更新了一行数据，同时也锁定了该行数据，接着两个事务都尝试去执行第二条update语句，却发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环，除非外部因素介入才可能解除死锁。

解决死锁的方案：

检测到死锁的循环依赖，并返回一个错误。
当查询时间到达锁等待时间的设定后放弃锁请求。这种方式可能会误判死锁。
将持有最少行级锁排他锁的事务进行回滚。InnoDB目前处理死锁的方式。

事务日志

每次修改表的数据时，只需要修改其内存的拷贝，而将该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的的数据本身持久到硬盘上。

事务日志采用的是追加的方式，因此写日志的操作时磁盘上一小块区域的顺序IO，而不像随机IO需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说快得多。事务日志持久化后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，称为预写式日志，修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久画，但数据本身还没有被写回磁盘，此时系统崩溃，存储引擎重启时能自动修复这部分数据，具体的恢复方式则视存储引擎而定。

2.多版本并发控制(MVCC)

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制(MVCC)。不仅MySQL，包括Oracle、PostgreSQL 等其他数据库系统也都实现了 MVCC，但各自的实现机制不尽相同，因为MVCC 没有一个统一的实现标准。

可以认为 MVCC 是行级锁的一个变种, 但是它在很多情况下避免了加锁操作，因此开销更低。县然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说， 不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。如果之前没有这方面的概念，这句话听起来就有点迷惑。熟悉了以后会发现，这句话其实还是很容易理解的。

InnoDB的MVCC，是通过在每行记录后加上两个隐藏的列来实现的。这两个列，一行保存了行的创建时间，一行保存行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号。每开始一个事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号做比较。

REPEATABLE READ级别下的MVCC具体操作（配合解决幻读)：

SELECT
InnoDB会根据以下两个条件检查每行记录：
- InnoDB只查找早于（小于或等于）当前事务版本号的数据行。这样可以确保事务读取的行，要么是事务开始前已经存在的，要么是事务自身修改过的。
- 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行在事务开始之前未删除。
INSERT
InnoDB为新插入的每一行保存当前的系统版本号作为行的版本号。
DELETE
InnoDB为删除的每一行保存当前的系统版本号作为删除标识。
UPDATE
InnoDB为插入的记录，保存当前的系统版本号作为行的版本号，同时保存当前的系统版本号到原来的行作为删除标识。

保存这两个额外系统版本号，使大多数操作都可以不加锁，也能保证读取到符合标准的行，避免幻读。但每行记录都需要额外的存储空间，需要做更多的行检查工作和维护工作。
MVCC只在REPEATABLE READ和READ COMMITED这两个级别下工作，与另外两个级别不兼容，因为READ UNCOMMITED总是读取最新的行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有的行都加锁。

存储引擎概要

除非万不得已，否则不要混合使用存储引擎。

InnoDB

MySQL默认存储引擎，被设计用来处理大量的短期事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃回复特性，使它在非事务存储的需求中也很流行。除非有特别的原因需要使用其他存储引擎，否则应优先考虑使用InnoDB。

InnoDB支持外键定义，支持行级锁。
InnoDB数据存储在表空间中，表空间是InnoDB管理的黑盒子，由一系列数据文件组成。
InnoDB使用MVCC来支持高并发，并且实现了四个标准的隔离级别。默认级别是REPEATABLE READ(可重复读),并且通过间隙锁策略防止幻读的产生。间隙锁使InnoDB不仅仅锁定查询设计的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB基于聚簇索引(B+树索引叶子节点存储的是实际数据，索引项顺序与表中记录的物理顺序相同)建立。
适用于频繁写入(insert,delete,update)的情况。

XtraDB

Percona XtraDB 是 InnoDB 存储引擎的增强版，被设计用来更好的使用更新计算机硬件系统的性能，同时还包含有一些在高性能环境下的新特性。

MariaDB 10.2 又重新使用了InnoDB -> 传送门
XtraDB 存储引擎是完全的向下兼容，在 MariaDB 中，XtraDB 存储引擎被标识为”ENGINE=InnoDB”，这个与 InnoDB 是一样的，可以直接用XtraDB 替换掉 InnoDB 而不会产生任何问题。
XtraDB 在多核CPU下能更有效地使用内存。
提供了比InnoDB更多的配置和性能监控参数

MyISAM

MyISAM存储引擎表由MYD(存数据)和MYI(存索引)组成，不支持事务和行级锁，不支持崩溃后的安全恢复。

使用非聚簇索引，B+树索引叶子节点存储的是实际数据的地址。也就是说它的索引和实际数据是分开的。
支持全文索引（一种基于分词创建的索引，可以支持复杂查询)
MyISAM不支持外键。将一个包含外键的InnoDB表转为MyISAM会丢失外键。
查询效率高，多用于只读或者大多数情况下只读的表，适合频繁查询几乎没有写入的情况。

Aria

MySQL创建者计划用来替代MyISAM的一款引擎，MariaDB包含了该引擎。

Aria可以说是为了解决崩溃安全恢复的MyISAM，如果发生崩溃，Aria会回滚到命令执行前的状态。
能对数据进行缓存(MyISAM只能缓存索引)。
支持对一张表的并发插入操作。

Memory

Memory存储引擎（之前称为HEAP）将表中数据存放在内存中，不需要进行磁盘IO。Memory表的结构重启后会保留，数据会丢失。

如果需要快速访问数据，并且这些数据不会被修改，重启后丢失也没关系，那么使用Memory表是非常有用的。Memory至少比MyISAM快一个数量级。
Memory在很多场景可以发挥很好的作用：

用于查找或者映射表，例如将邮编和地点名映射的表
用于缓存周期性聚合数据的结果
用于保存数据分析中产生的中间结果

Memory表支持Hash索引，因此查找非常快。但还是无法取代传统基于磁盘的表。
Memory表字段长度固定。使用VARCHAR也会转换成CHAR。
Memory是表级锁，并发写入性能低。
Memory表不支持BLOB和TEXT类型的列，如果包含BLOB和TEXT类型，则会转换成MyISAM表。
Memory表可作为临时表保存中间结果但不等同于自己创建的临时表。

CSV

CSV存储引擎可以将普通的csv文件(逗号为分隔符的文本文件)作为MySQL的表来处理，但不支持索引。

所有列必须都不能为null。
不适合大表，不适合在线处理。
可以作为一种数据交换的机制，在数据库运行时拷入或者拷出文件。
可以将Excel等电子表格软件中的数据存储为csv文件,然后复制到MySQL数据目录下，就能在MySQL中打开使用。

Cassandra

实现SQL与Cassandra之间的数据交换。Cassandra存储引擎允许MariaDB通过标准SQL语法使用Cassandra集群。未默认安装。官方说明

可以从多个MariaDB实例访问同一个Cassandra集群，它们每一个都可以运行Cassandra。
使用MariaDB的Cassandra存储引擎在Cassandra上执行查询时需要创建一个映射表。该虚表需要使用与实际Cassandra列族相同的静态列名和类型。而动态列在MariaDB中将以blob类型的单列形式存在