深入理解MySQL事务以及存储引擎

隔离性（Isolation）：事务并发执行时，彼此互不影响，如同串行执行。MySQL提供了多种事务隔离级别，包括读未提交（READ UNCOMMITTED）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ，MySQL的默认隔离级别）和串行化（SERIALIZABLE）。

持久性（Durability）：一旦事务提交，对数据库的修改将被永久保存，即使在系统崩溃的情况下也不会丢失。

（二）事务的隔离

在数据库中，多个事务同时执行时，它们之间可能存在相互影响。事务的隔离性通过事务隔离级别来控制，不同的隔离级别对事务间的影响有不同的管理策略。

1.事务之间的影响

以下是四种主要的并发问题：

脏读（Dirty Read）：在较低的隔离级别（如读未提交Read Uncommitted）下，一个事务可以读取到另一个事务尚未提交的数据，如果这个事务随后被回滚，那么读取的数据就是无效的。

不可重复读（Non-repeatable Read）：即在一个事务中，对同一行数据进行两次读取时，结果不一致，因为在这两次读取之间，另一个事务可能对此行数据进行了修改并提交了事务。

幻读（Phantom Read）：在可重复读（Repeatable Read）隔离级别下，同一个事务在执行相同的查询时，两次查询结果集中包含了不同的行（新增的行像幽灵一样突然出现），这是因为另一个事务在这两次查询期间插入了新的行并提交了事务。

丢失更新（Lost Update）：当两个事务同时修改同一行数据且都未进行锁操作时，可能会导致其中一个事务的更新丢失。例如，两个事务先后将某行的值从A更新为B和C，最终的结果可能是A直接变为C，B的更新被忽略。

注意：不可重复读与幻读之间看起来区别不大，都是在数据库并发控制中讨论的事务隔离级别不足时可能出现的问题，它们都发生在同一个事务内两次读取数据时结果不一致的现象，但侧重点有所不同

不可重复读：指的是在同一个事务内，同一个查询在不同时刻读取到了不同的数据。比如事务A在开始时读取了一行数据，但是在事务A还未结束时，事务B修改了这行数据并提交了事务。当事务A再次读取同一行数据时，发现数据内容与之前读取的结果不同，这就发生了不可重复读。

幻读：则是在同一个事务内，同一个查询在不同时刻读取到了不同的行数。同样是事务A在开始时执行了一个范围查询（如：查询年龄大于20岁的人数），并在结果集基础上进行了操作。之后事务B插入了一些新的满足条件（年龄大于20岁）的数据并提交了事务。当事务A再次执行同样的范围查询时，发现结果集中出现了新的行（即“幽灵行”），尽管它先前读取过的数据行并未发生变化，但行数增加了，这就是幻读现象。

2.事务隔离级别

为了解决这些问题，数据库系统提供了不同的事务隔离级别：

读未提交（Read Uncommitted）：最低隔离级别，允许脏读、不可重复读和幻读。

读已提交（Read Committed）：解决了脏读问题，但仍可能出现不可重复读和幻读。

可重复读（Repeatable Read）：MySQL默认的隔离级别，解决了脏读和不可重复读问题，但幻读仍有可能发生（在MySQL中通过Next-Key Locks解决一部分幻读问题）。

串行化（Serializable）：最高隔离级别，可以避免所有并发问题，但可能会导致性能下降，因为它几乎等同于事务串行执行。在串行化级别下，事务冲突会被严格禁止，以确保事务间不会互相影响。

事务隔离级别的作用范围分为两种

全局级：对所有的会话有效

会话级：只对当前的会话有效

3.查询与设置事务隔离级别

3.1 查询全局事务隔离级别

show global variables like '%isolation%'; 执行该命令是为了显示与事务隔离级别相关的全局系统变量。模糊查询

select @@global.tx_isolation; 用来查询当前MySQL服务器的全局事务隔离级别

transaction_isolation 和 tx_isolation 展示的是当前服务器的全局事务隔离级别

可以看到，显示的值都为repeatable read，即可重复读

3.2 查询会话事务隔离级别

show session variables like '%isolation%';
select @@session.tx_isolation;

同样的，在进行会话事务，即交互时的隔离级别同样为repeatable read，即可重复读

3.3设置全局事务隔离级别

使用set指令设置
set global transaction isolation level 事务隔离级别;

3.4 设置会话事务隔离级别
set session transaction isolation level 事务隔离级别;

（三）事务控制语句

BEGIN 或 START TRANSACTION	显式地开启一个事务。
COMMIT 或 COMMIT WORK	提交事务，并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK	回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT S1	使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称。
ROLLBACK TO [SAVEPOINT] S1	把事务回滚到标记点。

1.提交事务

使用begin开始事务，而后开始处理、修改数据，最后使用commit提交事务，算一次完整的事务，则在事务中的所有操作生效，否则不生效。

2.回滚事务

使用rollback指令回滚未提交的事务，使数据回到事务开始前的状态

3.多点回滚

开始事务后，使用savepoint s1 进行节点标记，而后使用 rollback to savepoint 节点名称进行回滚

而后进行回滚，可以回到你需要的节点

此操作不可逆，如果回滚到s1之后，不能回到之后的节点，只能往前

使用rollback指令直接回滚到事务开始之前的值

4.设置控制事务

使用set指令设置控制事务

注释

• 如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束，当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。

• 如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。

• 当然无论开启与否，begin; commit|rollback; 都是独立的事务。

二、MySQL存储引擎

在MySQL数据库的世界里，存储引擎作为数据库的核心组件，负责数据的存储、检索和索引管理等关键任务。MySQL支持多种存储引擎，每种引擎都有其独特的优劣和适用场景，使得MySQL能够灵活适应各种各样的业务需求。

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

（一）存储引擎的种类

使用show engines;指令查当前mysql支持哪些存储引擎

字段含义

Engine	存储引擎
Support	系统是否支持
Comment	注释信息
Transactions	是否支持事务
XA	是否支持XA协议
Savepoints	是否支持节点

InnoDB存储引擎

InnoDB是MySQL的默认存储引擎。InnoDB引擎支持行级锁定，能够有效减少并发操作时的锁争用，提高并发性能。同时，它实现了ACID（原子性、一致性、隔离性、持久性）事务特性，确保了数据的一致性和安全性。此外，InnoDB引擎支持外键约束，提供了良好的数据完整性保障。在现代MySQL版本中，InnoDB还支持分区表、聚集索引和自适应哈希索引等特性，进一步优化了性能。

MRG_MyISAM存储引擎

基于MERGE，将多个MyISAM表合并成一个逻辑表。相同MyISAM表的集合

MEMORY存储引擎

MEMORY（以前称为 HEAP）存储引擎将数据存储在内存中，因此它的查询速度极快，尤其适用于临时表或者需要快速读写的场景。但由于数据仅存在于内存中，一旦MySQL服务器关闭，表中的数据就会丢失。此外，MEMORY引擎也不支持事务和外键约束。

BLACKHOLE黑洞引擎

所有写入都被丢弃，用于记录binlog但不实际存储数据

MyISAM存储引擎

MyISAM曾是MySQL早期的默认存储引擎，虽然在事务处理和行级锁定方面不如InnoDB强大，但因其索引文件和数据文件分离、全表扫描速度快等特点，在读密集型场景中表现出色。MyISAM不支持事务和外键，基于哈希，存储在内存中，对临时表很有用

ARCHIVE存储引擎

ARCHIVE存储引擎主要用于大量历史数据归档，它压缩率极高，适合长期存储且很少需要更新的大容量静态数据。ARCHIVE引擎只支持INSERT和SELECT操作，不支持UPDATE和DELETE，也不支持索引。

CSV存储引擎

以CSV格式存储数据，易于与其他程序交换数据

目前MySQL常用的存储引擎有InnoDB存储引擎与MyISAM存储引擎

（二）InnoDB存储引擎

MySQL从5.5之后版本开始，默认的存储引擎为 InnoDB

在建表时，不需要指定，默认就是此类

1.特点

DML操作遵循ACID模型，支持事务；

行级锁，如果事务A正在操作某行，其它事务不能操作此行，可以操作此表的其他行，提高并发访问性能；

支持外键 FOREIGN KEY约束，保证数据的完整性和正确性

2.文件存储格式

xxx.ibd:xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。

（三）MyISAM存储引擎

1.特点

不支持事务，不支持外键

支持表锁，操作或者修改一张表的数据时，整张表会被锁起来，不允许其他用户操作，等到操作结束退出后释放，不支持行锁

访问速度快，对事物的完整性没有要求

2.文件存储格式

MyISAM 表支持 3 种不同的存储格式

静态表（Fixed-length Table）：

在MySQL的MyISAM存储引擎中，静态表也称为固定长度表。这类表中的每一行数据占用的空间是固定的，即所有列的长度总和。例如，如果一个列定义为CHAR(10)，无论实际存储的内容长短，都将占用10个字符的固定空间。静态表的优点在于查询速度快，因为行数据是连续存储且大小恒定，易于缓存和快速定位。缺点是如果数据长度小于定义长度时，会浪费存储空间。

动态表（Variable-length Table）：

动态表（也称变长表）中的行长度是可以变化的。例如，在VARCHAR类型的列中，实际存储的数据长度与列定义的最大长度有关。动态表能够更有效地利用存储空间，尤其是对于包含大量可变长度数据的列时。但这也可能导致数据在硬盘上的存储位置不连续，影响到某些查询操作的性能。
压缩表（Compressed Table）：

压缩表

是对已有表的数据进行压缩存储，以节省磁盘空间。在MySQL中，可以使用MyISAM的myisampack工具对MyISAM表进行压缩，创建出来的表为只读表。InnoDB存储引擎也支持表压缩，通过设置相应的表选项可以在表级别进行压缩，这样既能节省存储空间，又能保持表的读写能力。压缩表在读取时需要解压，这会增加CPU的消耗，但在I/O密集型环境中，由于减少了磁盘读写，总体上可能提高性能。

MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：

文件存储表结构的定义的扩展名：.frm

数据文件的扩展名为 .MYD

索引文件的扩展名是 .MYI

（四）修改存储引擎

修改存储引擎的方式有三种

1.修改已存在的表

使用alter指令修改表的存储引擎
alter table 表名 engine = 存储引擎种类；

2.建表时指定引擎

建表时，在字段外指定存储引擎

create table 表名 (字段1 字段1类型[comment 注释信息]，字段2 字段2类型) engine = 存储引擎种类；

3.修改配置文件

在/etc/my.cnf文件加添加指定信息，来确定建表时的默认存储引擎类型

default-storage-engine=存储引擎种类

修改后重新启动文件

总结

（一）事务

1.事务的作用

保证数据的完整性与准确性

2.事务的ACID特性

#原子性（Atomicity）
事务中的所有操作作为一个整体提交，要么全部执行成功，要么全部回滚到事务开始前的状态，
确保了事务的不可分割性。

#一致性（Consistency）
事务执行前后，数据库都必须处于一致状态。

#隔离性（Isolation）
事务并发执行时，彼此互不影响

#持久性（Durability）
一旦事务提交，对数据库的修改将被永久保存，即使在系统崩溃的情况下也不会丢失。

3.事务隔离级别

读未提交（Read Uncommitted）	最低隔离级别，允许脏读、不可重复读和幻读。
读已提交（Read Committed）	解决了脏读问题，但仍可能出现不可重复读和幻读。
可重复读（Repeatable Read）	MySQL默认的隔离级别，解决了脏读和不可重复读问题，但幻读仍有可能发生
串行化（Serializable）	最高隔离级别，可以避免所有并发问题，但可能会导致性能下降，几乎等同于事务串行执行

（二）存储引擎

1.innodb与myisam的区别

特点	InnoDB	MYISAM
存储限制	64TB	有
事务安全	支持	不支持
锁机制	行锁	表锁
B+tree索引	支持	支持
全文索引	支持(5.5版本之后)	支持
空间使用	高	低
内存使用	高	低
批量插入速度	低	高
支持外键	支持	不支持

2.适用场景

InnoDB存储引擎适用场景：

适用于高并发、事务处理和数据完整性要求较高的场景，如电子商务、银行交易系统等。

当数据库中有大量的增删改操作，并且需要支持事务时，InnoDB是首选。

对于需要外键约束来确保数据参照完整性的应用，InnoDB是唯一支持外键的存储引擎。

对于需要行级锁定以提高并发性能的场合，InnoDB的行锁特性使其成为理想选择。

MyISAM存储引擎适用场景：

对于读多写少、数据仓库和数据分析类应用，尤其是全表扫描操作较多的场景。

当不需要事务处理和外键约束时，MyISAM可以提供较好的读取性能。

对于硬件资源有限，尤其是内存较小的环境，MyISAM由于其数据和索引分开存储的特性，可能在某些情况下占用更少的内存。

智能运维探索者

关注

12
点赞
踩
27

收藏

觉得还不错? 一键收藏
0
评论
深入理解MySQL事务以及存储引擎

在MySQL数据库中，事务与存储引擎是两个密切相关的概念，它们共同决定着数据的一致性、可靠性和性能。事务处理是保证数据库操作原子性、一致性、隔离性和持久性的关键机制，而存储引擎则是MySQL用来管理数据存放和访问的具体实现。
复制链接

扫一扫