概述

存储引擎是MySQL数据库底层软件组件，负责执行数据的存储和检索操作，是MySQL区别于其他数据库的核心特性之一。MySQL采用插件式存储引擎架构，不同存储引擎提供不同的存储机制、索引技术、锁定水平等功能，用户可以根据业务需求灵活选择。

通俗点讲：存储引擎决定了数据在磁盘上的存储方式和访问方式，不同的存储引擎实现了不同的存储和检索算法
存储引擎就是数据库服务中的文件系统，用户可以根据应用的需要选择存储和索引数据。

存储引擎的架构：

+-------------------------+
|    连接/会话层          |
+-------------------------+
|    SQL解析/优化层       |
+-------------------------+
|    存储引擎接口层       |
+-------------------------+
| InnoDB | MyISAM | Memory | ... |
+-------------------------+

• 上层SQL层与存储引擎层通过标准接口交互，上层不需要关心存储引擎的具体实现
• 不同存储引擎之间相互独立，各自实现自己的特性
• 支持同一数据库中不同表使用不同存储引擎

存储引擎的简单运维操作

查看数据库中所有引擎种类：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看数据库默认引擎

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

查看某张表的存储引擎

show create table 表名;

如果你想修改存储引擎

• 全局修改

# 修改配置文件，然后重启mysql即可
vim /etc/my.cnf

# 修改下面的配置 
[mysqld]
default_storage_engine=InnoDB

• 针对某一张表修改

#创建表时设置
create table xxx (id int) engine=innodb charset=utf8mb4;

#创建表之后设置
alter table xxx engine=myisam;
alter table world.xxx engine=innodb;

存储引擎分类

目前mysql有很多种存储引擎，但是99%的场景直接选择InnoDB：现在InnoDB已经非常成熟，性能比MyISAM更高，功能更全，除非有非常明确的理由，否则都用InnoDB。
但是为了学习，我们在这里还是简单说一下所有的存储引擎：

InnoDB

这是mysql5.5+版本中默认的存储引擎，其核心特性如下：

• 事务支持：完全支持ACID特性，支持4种事务隔离级别，是需要事务支持的业务首选
• 行级锁定：粒度更小的行锁，并发性能远高于表锁，适合高并发读写场景
• MVCC多版本并发控制：读写不阻塞，大幅提升高并发场景下的性能
• 外键约束：唯一支持外键的存储引擎，保证数据的一致性和完整性
• 崩溃恢复：通过redo log和undo log实现 crash-safe，异常重启后数据不会丢失
• 聚簇索引：数据和主键索引存储在一起，主键查询性能极高
• 支持热备份：可以在服务运行时进行备份，不影响业务

其优缺点：

优点	缺点
事务支持，数据安全性高	空间占用较高，存储成本略高
行级锁，并发性能好	不支持全文索引（MySQL 5.6+开始支持，但功能较弱）
崩溃恢复能力强	批量插入性能略低于MyISAM
支持外键、MVCC	维护成本略高

其适用场景

• 绝大多数OLTP（在线事务处理）场景
• 需要事务支持、数据一致性要求高的业务
• 高并发读写的场景（电商、金融、社交等）
• 对数据可靠性要求高的核心业务

MyISAM

这是MySQL 5.5之前默认引擎，现在版本已经很新了，使用这个存储引擎的公司应该已经很少了。核心特性如下：

• 表级锁定：读共享、写独占，写操作会锁全表，并发写入性能差
• 全文索引：原生支持全文索引，适合全文搜索场景
• 压缩存储：支持压缩表，占用空间小，适合只读数据
• 查询性能高：在数据量不大、读写比很高的场景下，查询性能优于InnoDB
• 不支持事务、不支持行锁和外键、崩溃后无法安全恢复，可能丢失数据

优缺点如下：

优点	缺点
占用空间小，存储成本低	不支持事务，数据安全性差
只读场景下性能极高	表级锁，并发写入性能差
全文索引支持	崩溃后数据易丢失
维护简单，资源消耗低	不支持外键、行锁

其适用场景

• 非核心的只读或者读多写极少的场景
• 数据可以通过其他方式恢复的业务（比如统计报表、日志归档）
• 需要全文搜索的场景（现在更推荐用Elasticsearch替代）
• 对性能要求极高、数据可靠性要求低的非核心场景

Memory（HEAP）

这是一个将数据存储在内存中的引擎，现在基本都是使用redis做缓存，所以这个存储引擎使用极低，了解即可，其核心特性：

• 数据存储在内存中：访问速度极快，响应时间微秒级
• 默认哈希索引：等值查询性能极高
• 表级锁定：并发写入性能差
• 服务重启后数据全部丢失
• 不支持TEXT、BLOB等大字段，varchar最大长度65535
• 内存资源有限，不适合存储大量数据

适用场景

• 临时数据存储（比如 session 数据、一次性统计数据）
• 缓存场景（热点数据缓存，替代Redis的轻量级方案）
• 高频读、低频写的小数据量场景

Archive

这款引擎适合存储海量的归档数据，例如日志数据等，但是这种类型的数据基本都是用elasticsearch存储，所以实际工作中，这款引擎使用率也极低，了解即可。
核心特性：

• 高压缩比存储：采用zlib压缩，空间占用仅为InnoDB的1/5~1/10
• 高写入性能：支持批量插入，写入性能极高
• 仅支持INSERT和SELECT操作，不支持UPDATE、DELETE
• 不支持索引（除了自增ID索引）
• 不支持事务

适用场景

• 日志数据归档、历史数据冷存储
• 不需要修改的海量数据存储场景
• 写入后只做批量查询的场景

CSV

这款存储引擎适合小数据量，适合将外部CSV格式数据到MySQL，现在一般不使用这款引擎，了解即可。
核心特性如下：

• 数据以CSV文本格式存储，可以直接用Excel等工具打开编辑
• 支持导入导出CSV格式数据
• 不支持索引
• 不支持NULL值
• 不支持事务

适用场景

• 数据交换、导入导出中间格式
• 快速导入外部CSV格式数据到MySQL
• 简单的小数据量存储场景

Blackhole

这款引擎简称黑洞引擎，正如名字一样，写入的数据会被吞掉（丢弃），不实际存储，读取数据永远返回空值，但是binlog日志会正常记录写入的SQL，一般作为主从复制的中继节点，很少使用，了解即可。

适用场景

• 作为主从复制的中继节点，过滤不需要同步的数据
• 性能测试时的压测目标，验证写入性能
• 测试二进制日志功能

Merge（MRG_MyISAM）

这款引擎会把多个结构相同的MyISAM表合并成一个逻辑表，透明查询和写入，不需要关心底层分表，仅支持MyISAM表，功能十分有限，不支持事务

适用场景

• 简单的分表场景，合并多个历史数据表
• 日志分表后的统一查询场景

Federated

这款引擎可以访问远程MySQL数据库中的表，本地不存储数据，所有操作转发到远程节点，因为网络开销较大，所以性能较低，稳定性一般，很多特性不支持

适用场景

• 跨库访问、数据联邦查询场景
• 临时的跨实例数据访问需求

NDB Cluster

这款引擎主要用于电信、金融等需要极高可用性的核心场景，但是在此类场景下一般不使用mysql，所以这个引擎使用极低，知道即可。

核心存储引擎对比（InnoDB vs MyISAM）

此问题一般都是面试的时候会问，在实际工作中一般都是无脑选择InnoDB，当然一些极特殊情况例外。

对比维度	InnoDB	MyISAM
事务支持	✅ 支持ACID	❌ 不支持
锁机制	✅ 行级锁、gap锁、next-key lock	❌ 仅表级锁
MVCC	✅ 支持	❌ 不支持
外键	✅ 支持	❌ 不支持
索引结构	✅ 聚簇索引	❌ 非聚簇索引
崩溃恢复	✅ 支持crash-safe	❌ 易丢失数据
全文索引	⚠️ 5.6+支持，功能弱	✅ 原生支持，功能强
存储空间	较高，支持压缩	较低，压缩比更高
内存占用	较高	较低
批量写入性能	一般	更高
并发性能	极高（支持高并发读写）	低（写操作阻塞所有操作）
适用场景	绝大多数OLTP核心场景	只读、非核心场景

InnoDB详解

磁盘结构组成

在磁盘存储结构中，会使用表空间模式进行数据信息的管理，经常提到的段，区，页概念也是属于表空间的逻辑结构。
表空间的概念源于Oracle数据库，最初的目的是为了能够更好的做存储的扩容，因此数据库的表空间技术类似磁盘管理的lvm技术

共享表空间

共享表空间是ibdata1这个文件

作用：

• MySQL8.0.20之前存储Double write buffer信息、changer buffer信息
• MySQL8.0.20之后只存储changer buffer信息，Double write buffer信息被独立出来了
• MySQL5.7版本存储全局数据字典信息、undo回滚日志、Double write buffer信息、changer buffer以及系统数据

共享表空间的运维操作命令：

• 查看共享表空间

#查看共享表空间
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

#查看共享表空间默认扩展大小，默认每次扩展64M
mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)

• 共享表空间的扩容操作

# 编写数据库配置文件信息
vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend

-- 需要注意的是ibdata1文件大小必须和实际数据库要存储的数据相匹配，否则会出现如下报错信息
 [ERROR] [MY-012264] [InnoDB] The innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than the 4864 pages specified in the .cnf file!
-- 表示ibdate1指定大小超过了原有ibdata1实际的大小尺寸

• 共享表空间的初始设置方式

# 初始化配置文件
root@master:~# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend

 
# 模拟初始化操作命令
root@master:~# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

# 模拟初始化重启服务
root@master:~# /etc/init.d/mysqld start

生产环境下，共享表空间容量推荐：

MySQL版本	共享表空间数量	初始推荐大小	额外配置
5.7	2~3 个	512M / 1G	最后一个开启自动扩展
8.0	1 个	512M / 1G	无需多文件，默认管理

独立表空间

独立表空间是以ibd结尾的文件，例如：

其作用主要是存储表中的数据信息（索引信息 表结构 表中行数据）。

独立表空间操作

#表示每个表就是一个独立文件，进行数据信息的独立存储，不建议进行修改，如果改为0就是所有数据统一存储在共享表空间
select @@innodb_file_per_table;
+---------------------------------+
| @@innodb_file_per_table |
+---------------------------------+
|                                 1 |
+---------------------------------+

#设置为0表示利用共享表空间存储用户数据 1表示利用独立表空间存储用户数据
set global innodb_file_per_table=0

利用独立表空间进行数据快速迁移

源端 3306/test/t100w --> 目标端 3307/test/t100w

# 步骤一：锁定源端t100w表
# 给t100w表加写数据锁
mysql > lock tables test.t100w write;

# 获取创建表结构数据信息
mysql > show create table test.t100w;
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx` (`k1`,`k2`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

#步骤二：目标端创建oldboy库和t100w空表
# 创建新的数据库
mysql> create database test;

 
# 创建新的数据表
mysql > CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx` (`k1`,`k2`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


#步骤三：删除目标端空的表空间文件
# 删除t100w表的ibd数据文件信息，但是保留t100w的frm，ibdata1中关于t100w的系统数据
mysql> alter table test.t100w discard tablespace;


#操作步骤四：拷贝源端ibd文件到目标端目录，并设置权限
root@master: ~# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
root@master: ~# chown -R mysql.mysql /data/*

#操作步骤五：导入表空间
# 在目标端加载识别迁移过来的数据文件信息
mysql> alter table test.t100w import tablespace;
# 查看数据表中是否有迁移过来的数据信息
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.04 sec)

#步骤六：解锁源端数据库
mysql> unlock tables;

undo表空间

在数据库5.7版本中，默认存储在共享表空间中（ibdata）；在数据库8.0版本后，默认就是独立存储了(undo_001-undo_002)

undo表空间的作用：
在数据库5.7版本中，默认存储在共享表空间中（ibdata）；在数据库8.0版本后，默认就是独立存储了(undo_001-undo_002)，在实际生产环境中，建议在5.7版本之后，都将undo表空间进行独立文件存储。

查看临时表空间相关命令

# 设置undo表空间文件数量（默认2个文件）
mysql> select @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

# 设置undo表空间文件大小（字节）
mysql> select @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+
1 row in set (0.00 sec)

# 设置undo表空间回收机制（默认开启）
mysql> select @@innodb_undo_log_truncate;
+----------------------------+
| @@innodb_undo_log_truncate |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

# 设置undo表空间信息检测次数 （128次）
mysql> select @@innodb_purge_rseg_truncate_frequency; 
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+
1 row in set (0.00 sec)

修改undo表空间配置

# 关闭数据库服务程序，清理数据库服务数据目录

root@master:~# vim /etc/my.cnf
[mysqld]
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32

临时表空间

临时表空间可以存储在内存和磁盘上，主要用于存储临时的表信息，主要是在使用group by，order by，having，union all，子查询等情况都会使用临时表；
重新加载临时数据时，可以从临时表空间文件中直接读取（顺序IO读取数据）

临时表空间查看操作命令

临时表空间默认初始化大小12M，不够用会自动扩容，每次扩容64M

mysql> select @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)

mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)

建议初始化时，设定好临时表空间，mysql5.7版本建议设置2~3个临时表空间，大小建议512M或1G，最后一个定制为自动扩展，mysql8.0版本建议设置1个即可，大小建议512M或者1G。

vim /etc/my.cnf

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:120M:autoextend:max:500M

redo事物日志

redo log属于事务重做日志文件，主要用于记录内存数据页的变化（记录在内存中对数据页的操作信息），都会以日志文件方式记录；

查看redo事物日志

mysql> show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.01 sec)

在实际生产中，文件大小建议512M-1G，应用组数设置2-4

# 编写数据库配置文件信息
vim /etc/my.cnf
[mysqld]
innodb_log_file_size=100M
innodb_log_files_in_group=3

ib_buffer_pool预热文件

ib_buffer_pool预热文件可用于缓冲和缓存，可以存储‘热’数据页，减少物理IO性能损耗。

内存结构组成

其内存架构主要由缓冲池和日志缓冲组成，其结构如下：

+-------------------------+
| Buffer Pool（缓冲池）   |
|  - 数据页缓存           |
|  - 索引页缓存           |
|  - 自适应哈希索引       |
|  - 插入缓冲             |
|  - 锁信息、数据字典等   |
+-------------------------+
| Log Buffer（日志缓冲）  |
+-------------------------+

Buffer Pool

Buffer Pool是InnoDB最重要的内存组件，默认占用物理内存的50%~70%，缓存热点数据和索引，减少磁盘IO。采用LRU算法管理缓存页。是MySQL中最大的、最重要的内存区域。

查看buffer pool

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                1073741824 |
+---------------------------+
1 row in set (0.00 sec)

设置buffer pool

# buffer pool默认内存空间大小为128M，生产建议大小可以设置为物理内存总量的50%~80%
方式一：
set global innodb_buffer_pool_size=268435456;

方式二：
vim /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=256M

Log Buffer

缓存redo log日志，定期刷入磁盘，减少日志写入IO。Log Buffer建议设置64M

查看log buffer

mysql> select @@innodb_log_buffer_size;
+--------------------------+
| @@innodb_log_buffer_size |
+--------------------------+
|                 16777216 |
+--------------------------+
1 row in set (0.00 sec)

修改log buffer

方式一：
set global innodb_log_buffer_size=33554432;

方式二：
vim /etc/my.cnf
[mysqld]
innodb_log_buffer_size=32M

InnoDB性能优化

1. 缓冲池配置：innodb_buffer_pool_size设置为物理内存的50%~70%，越大越好
2. 日志配置：innodb_log_file_size设置为1GB~4GB，innodb_log_buffer_size设置为64MB
3. 刷新策略：innodb_flush_log_at_trx_commit=1（最高安全性），或2（最高性能，最多丢失1秒数据）
4. IO优化：使用SSD存储，innodb_io_capacity设置为磁盘的IOPS能力
5. 索引优化：合理设计索引，避免回表，使用覆盖索引