MySQL数据库引擎、事务详解

数据库引擎

innoDB

• 支持事务ACID，也就是支持事务完整性、一致性

• innoDB支持行级锁，行级锁可以在最大程度上支持并发，允许在同一时刻对表的不同行进行并发修改，而且不会相互阻塞

• InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按照主键顺序存放，如果没有显式在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

• InnoDB支持崩溃数据自修复。InnoDB存储引擎中就是依靠redo log（重做日志）来保证的。当数据库异常崩溃后，数据库重新启动时会根据redolog进行数据恢复，保证数据库恢复到崩溃前的状态

MyISAM

• 不支持事务

• 其锁定机制是表级索引，虽然可以让锁定的实现成本很小，但是也同时大大降低了其并发性能

• 不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

• 只会缓存索引：MyISAM可以通过key_buffer缓存，以大大提高访问性能减少磁盘I/O，但是这个缓冲区只会缓存索引，而不会缓存数据

• 适用于不需要事务支持（不支持）、并发相对较低（锁定机制问题）、数据修改相对较少（阻塞问题）、以读为主这类场景、

MEMORY

• 使用存储在内存中的内容来创建表，而且所有数据也放在内存中

• 每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构，数据文件则存储在内存中

• MEMORY默认使用哈希索引，速度比使用B型树索引快。如果想用B型树索引，可以在创建索引时指定

• MEMORY存储引擎是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，那么所有数据都会消失

事务

事务是指一个操作由多个步骤组成，要么全部成功，要么全部失败

数据库事务是指对数据库的一系列操作组成的逻辑工作单元

数据库管理系统在写入或更新资料的过程中，为保证事务的正确可靠，必须具备四个特性ACID

A：原子性

事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么全都不执行

C：一致性

确保事务将数据库从一种一致的状态转换为另一种一致的状态。如果事务执行后数据库不满足一致性，整个事务将会被回滚

一致性主要包括约束、触发器、级联等

I：隔离性

多个事务并发执行，一个事务的执行不影响其他事务的执行

D：持久性

已被提交的失误对数据库的修改应该永久保存在数据库中

如何保证这四个特性呢？

• 持久性是通过 redo log （重做日志）来保证。

• 原子性是通过 undo log（回滚日志） 来保证。

• 隔离性是通过 MVCC（多版本并发控制） 和锁机制来保证。

• 一致性则是通过持久性+原子性+隔离性来保证。

事务并发的问题

• 脏读：读取为提交的数据

• 不可重复读：前后多次读取，数据内容不一致

• 幻读：同一个查询在不同时间产生不同的结果集

严重性逐渐减少

事务隔离级别

为了解决上述问题，出现了事务隔离级别

• 读未提交

• 读已提交

• 可重复读

• 串行化

隔离水平逐级增加

能解决的问题逐级增加

• 读未提交

允许所有问题，事务可以读取其他事务尚未提交的数据，虽然拥有超高的并发处理能力以及很低的系统开销，但很少用于实际应用，因为可能会导致数据不一致

• 读已提交

不允许脏读，事务只能读取已经提交的数据，从而避免了脏读

是大多数数据库的默认隔离级别（如Oracle和SQL Server），但不是MySQL默认

• 可重复读

只允许幻读

事务在整个事务期间保持一致的快照，其他事务的修改不会影响正在运行的失误，从而防止不可重复读

是MySQL InnoDB的默认事务隔离级别

• 串行化

解决所有问题，强制事务排序，使之不可能相互冲突，从而防止所有并发问题

事务是串行执行，效率极低。通常我们使用乐观锁或者悲观锁来解决这些问题

四种隔离级别具体是如何实现的呢？

• 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了。

• 对于「读已提交」和「可重复读」隔离级别的事务来说，它们是通过多版本并发控制（MVCC）来实现的。

• 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问

MVCC多版本并发控制，是以乐观锁为基础的，主要包括版本链和读视图

• 版本链存储历史版本

• 读视图用于管理事务的可见性，以确保每个事务在读取数据时只看到在事务开始之前已经提交的数据版本，读已提交和可重复读的区别就在于生成读视图的策略不同

更新丢失

两个事务同时读取相同数据并分别修改后，一个事务的修改覆盖了另一个事务的修改

包括回滚丢失和提交丢失

MySQL可通过：

1. 提升隔离级别至串行化

2. 使用乐观锁或悲观锁

使用数据库事务

BEGIN开始一个事务

COMMIT提交一个事务

ROLLBACK回滚一个事务

数据库执行流程

MySQL架构分为两层：Server层和存储引擎层

• 连接器：建立连接，管理连接、校验用户身份；

• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

• 执行 SQL：执行 SQL 共有三个阶段：

• 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。

• 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；

• 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；