MySQL存储引擎

Cactus li2025年3月21日...大约 3 分钟

1、查看存储引擎

查看MySQL提供什么存储引擎

SHOW ENGINES;

下面的结果表示MySQL中默认使用的存储引擎是InnoDB，支持事务，行锁，外键，支持分布式事务(XA)，支持保存点(回滚)

也可以通过以下语句查看默认的存储引擎：

SHOW VARIABLES LIKE '%default_storage_engine%';

2、设置存储引擎

**方法1：**临时

设置默认存储引擎：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

**方法2：**永久

#或者修改 my.cnf 文件
vim /atguigu/mysql/mysql8/conf/my.cnf

#添加配置
[mysqld]
default-storage-engine=MyISAM 
#保存并退出

#重启MySQL
docker restart atguigu-mysql8

方法3：

我们可以为不同的表设置不同的存储引擎

CREATE TABLE 表名( 建表语句 ) ENGINE = 存储引擎名称;
ALTER TABLE 表名 ENGINE = 存储引擎名称;

-- 例如：
CREATE TABLE student(id INT, `name` VARCHAR(16),age INT,dept INT) ENGINE = MyISAM;
ALTER TABLE student ENGINE = InnoDB;

3、MyISAM和InnoDB的区别

事务支持：InnoDB是支持事务的存储引擎，而MyISAM不支持事务。
外键约束：InnoDB支持外键约束（foreign key constraints），可以保证数据的完整性和一致性。MyISAM不支持外键约束。
锁级别：InnoDB采用行级锁定（row-level locking），可以更好地支持并发操作和并发控制。而MyISAM采用表级锁定（table-level locking），这意味着在对表进行写操作时需要锁定整个表，可能导致并发性能下降。
崩溃恢复：InnoDB具有崩溃恢复的能力，可以在数据库发生异常情况或崩溃后自动恢复数据。MyISAM没有崩溃恢复机制，如果发生故障，可能会导致数据丢失或损坏。
并发性能：由于InnoDB采用了行级锁定和多版本并发控制（MVCC）技术，它在高并发环境下通常具有更好的性能。MyISAM适用于读操作较多、写操作较少的场景。

3.1、事务控制

InnoDB支持事务，MyISAM不支持事务：

要证明 MyISAM 不支持事务，可以通过以下步骤来验证：

创建一个 MyISAM 表：

CREATE TABLE myisam_table (
  id INT PRIMARY KEY,
  data VARCHAR(255)
) ENGINE=MyISAM;

开启一个事务，插入一条记录并未提交事务：

START TRANSACTION;
INSERT INTO myisam_table (id, data) VALUES (1, 'Data');

打开另一个连接或会话，尝试读取插入的记录：
```
SELECT * FROM myisam_table WHERE id = 1;
```
如果 MyISAM 支持事务，则在事务未提交之前，第二个连接是无法看到未提交的数据的。然而，如果你使用的是 MyISAM 存储引擎，你会发现第二个连接可以读取到该未提交的记录。
回到第一个连接或会话，回滚事务：
```
ROLLBACK;
```
再次在第二个连接或会话中尝试读取记录：
```
SELECT * FROM myisam_table WHERE id = 1;
```
在 MyISAM 中，即使回滚了事务，第二个连接仍然可以读取到被回滚的记录。

这个实验表明，MyISAM 存储引擎不支持事务。在 MyISAM 中，每个操作（插入、更新、删除）都被立即写入磁盘，没有事务日志来支持回滚操作。因此，MyISAM 不适合处理需要 ACID（原子性、一致性、隔离性和持久性）特性的事务操作。

3.2、外键约束

InnoDB支持外键约束，MyISAM不支持外键约束：

要证明 MyISAM 不支持外键约束，可以按照以下步骤进行验证：

创建两个 MyISAM 表，并定义一个表之间的外键关系。假设我们有两个表：parent_table 和 child_table。

CREATE TABLE parent_table (
  id INT PRIMARY KEY
) ENGINE=MyISAM;

CREATE TABLE child_table (
  id INT PRIMARY KEY,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
) ENGINE=MyISAM;

使用 SHOW CREATE TABLE 命令来查看表的定义信息，确认 MyISAM 表中没有外键约束。
```
SHOW CREATE TABLE child_table;
```