MySQL 中的事务和存储引擎

目录

事务的 ACID 特性

MySQL 的四种隔离机制和问题

MySQL 的四种隔离机制：

MySQL 的存储引擎

InnoDB 存储引擎

MyISAM 存储引擎

Memory 存储引擎

通过 ALTER TABLE 语句更改存储引擎

在创建表时指定存储引擎

通过修改配置文件设置默认存储引擎

---

在数据库系统中，事务是指一组操作被当作一个单元来执行，要么全部成功提交，要么全部失败回滚。而存储引擎则是数据库管理系统中负责数据存储和管理的核心组件。在 MySQL 中，了解事务的 ACID 特性、隔离级别以及不同的存储引擎对于设计和优化数据库系统非常重要。

事务的 ACID 特性

在 MySQL 中，事务遵循 ACID 特性，确保了数据的一致性和完整性：

• 原子性：事务中的所有操作要么全部执行成功，要么全部失败回滚，不会出现部分执行的情况。
• 一致性：事务将数据库从一种一致状态转换为另一种一致状态，即使在事务执行过程中出现错误也会回滚到初始状态。
• 隔离性：事务的执行不受其他事务的影响，每个事务看到的数据是一致的，防止了数据的交叉干扰。
• 持久性：一旦事务提交，对数据的修改将永久保存在数据库中，即使发生系统故障也不会丢失。

MySQL 的四种隔离机制和问题

MySQL 提供了四种事务隔离级别，分别是：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。每种隔离级别都有不同的特点，也会导致不同的问题：

• 脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据。
• 不可重复读（Non-Repeatable Read）：一个事务在读取同一数据行时，由于其他事务的修改导致了多次读取结果不一致。
• 幻读（Phantom Read）：一个事务读取了另一个事务提交的新数据，导致前后两次查询的结果集不一致。
• 丢失更新（Lost Update）：两个事务同时读取同一数据行，并且都修改了这一行，但只有一个事务的修改生效了。

MySQL 的四种隔离机制：

• 读未提交（Read Uncommitted）：允许事务读取未提交的数据，可能出现脏读、不可重复读、幻读、丢失更新问题。
• 读已提交（Read Committed）：保证一个事务不会读取到另一个事务未提交的数据，解决了脏读问题，但仍可能出现不可重复读、幻读、丢失更新问题。
• 可重复读（Repeatable Read）：保证一个事务在执行过程中多次读取同一数据行的结果是一致的，解决了不可重复读问题，但仍可能出现幻读、丢失更新问题。
• 串行化（Serializable）：最高级别的隔离级别，完全解决了脏读、不可重复读、幻读、丢失更新问题，但性能较低。

MySQL 的存储引擎

MySQL 的存储引擎是指数据库管理系统底层的组件，负责数据的存储、索引和管理。不同的存储引擎具有不同的特点和功能，适用于不同的场景和需求。 MySQL 中常见的存储引擎，包括 InnoDB、MyISAM、Memory 等。

InnoDB 存储引擎

• 特点：

  • 支持事务（ACID）：提供了事务的支持，可以实现数据的一致性和完整性。
  • 行级锁定：支持行级锁定，可以提高并发性能，减少锁冲突。
  • 外键约束：支持外键约束，保证数据的完整性。
  • 支持全文索引：可以对文本类型的数据进行全文搜索。
  • 自动崩溃恢复：具有自动崩溃恢复功能，可以在 MySQL 重启后自动恢复数据。

• 优点：

  • 适合大量写入和读取的应用：对于需要高并发读写、数据完整性要求高的应用非常适用。
  • 支持事务：可以实现数据的一致性，适合需要事务支持的应用。
  • 外键约束：对于有复杂关系的数据库设计，外键约束非常有用。

• 缺点：

  • 内存占用较大：相比于其他存储引擎，InnoDB 的内存占用较大。
  • 性能损失：由于支持事务和行级锁定，性能可能相对较低。

• 适用场景：

  • 需要事务支持和数据完整性的应用：如电子商务网站、金融系统等。
  • 高并发读写的应用：对于需要大量的并发读写操作的应用非常适用。
  • 复杂关系的数据库设计：对于需要建立复杂关系的数据库结构，外键约束非常有用。

MyISAM 存储引擎

• 特点：

  • 不支持事务：不提供事务的支持，对于简单的读取操作较为适用。
  • 表级锁定：锁定的粒度是整个表，不能实现行级锁定。
  • 全文索引：支持全文索引，可以对文本类型的数据进行全文搜索。
  • 性能较高：在读取操作上性能较高，适合读取频繁的应用。

• 优点：

  • 内存占用较小：相比于 InnoDB，MyISAM 的内存占用较小。
  • 性能较高：在读取操作上性能较高，适合读取频繁的应用。

• 缺点：

  • 不支持事务：对于需要事务支持的应用不适用。
  • 表级锁定：锁定的整个表，可能导致并发性能下降。
  • 不支持外键约束：对于需要外键约束的数据库设计不适用。

• 适用场景：

  • 只读的数据仓库：对于只读的数据仓库或者读取操作频繁的应用非常适用。
  • 日志、存档等：对于需要高性能的写入操作，但不需要事务和复杂查询的应用。
  • 简单的数据库设计：对于简单的数据库结构，不需要复杂关系和外键约束的应用。

Memory 存储引擎

• 特点：

  • 将表存储在内存中：数据存储在内存中，读写速度非常快。
  • 不支持事务和外键约束：不支持事务和外键约束，主要用于临时表、缓存等场景。
  • 表级锁定：锁定的粒度是整个表，不支持行级锁定。

• 优点：

  • 读写速度快：由于数据存储在内存中，读写速度非常快。
  • 适用于临时数据：对于临时数据、缓存等场景非常适用。

• 缺点：

  • 数据丢失：数据存储在内存中，MySQL 重启后数据丢失。
  • 不支持事务和外键约束：对于需要事务和外键约束的应用不适用。
  • 表级锁定：锁定的粒度是整个表，可能导致并发性能下降。

• 适用场景：

  • 临时数据和缓存：对于临时数据、缓存等场景非常适用。
  • 测试和开发环境：对于测试和开发环境中的临时数据存储非常方便。

通过 ALTER TABLE 语句更改存储引擎

假设 school 数据库中有一个表名为 class，我们想将其存储引擎从默认的 InnoDB 更改为MyISAM：

ALTER TABLE class ENGINE = MyISAM;

在创建表时指定存储引擎

假设要创建一个新的 class 表，并将存储引擎设置为 MyISAM：

CREATE TABLE class (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50)) ENGINE = MyISAM;

通过修改配置文件设置默认存储引擎

在 my.cnf中设置默认存储引擎：

[mysqld]
default_storage_engine = MyISAM

设置完成后，重启 MySQL 服务，新创建的表就会默认使用 MyISAM 存储引擎。已存在的表存储引擎不会被更改，在创建表时如果手动指定使用的存储引擎，则使用手动指定的存储引擎。