【MySQL基础】05：表及存储引擎（Table and Storage Engine）

本文链接：https://blog.youkuaiyun.com/zyplanke/article/details/102972847

本文介绍了MySQL的存储引擎概念，强调了InnoDB作为默认存储引擎的重要性，提供了MyISAM、Memory和InnoDB的特性和对比。InnoDB支持事务和外键，而MyISAM不支持，Memory引擎数据存储在内存中，服务重启后数据丢失。存储引擎的选择应根据事务需求、性能和数据持久性来决定。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本教程以MySQL8为主版本（内容兼顾MySQL5.7）。

所有MySQL文章的目录为：总目录 【MySQL8教程】：总目录_mysql8 数据库只有个目录-优快云博客

存储引擎概述

与大多数的数据库不同，MySQL的表table有一个存储引擎（storage engine）的概念。可以对每一个表Table设置不同的存储引擎，这样我们可以表的特点和用途选择最合适的存储引擎。所谓的存储引擎，是指表的数据管理和组织的方式（比如数据以什么样的形式、格式存放，是否支持事物、是否支持加密，是否有缓存机制等等）。在其他数据库中这种方式是随某个数据库产品固定的。但MySQL是灵活的、可配置的、可定制化的。

MySQL可插拔存储引擎体系结构，是MySQL的重要特性。使DBA可以为特定的应用程序需求选择某个的存储引擎，来进行相应的数据存放、是否需要事物等。 MySQL支持多种存储引擎，甚至我们也可以定义和加载自己的存储引擎（可插入的存储引擎机制）。

MySQL5.x及MySQL8.x支持的存储引擎有：InnoDB、MyISAM、MEMORY、CSV、ARCHIVE、BLACKHOLE、MERGE、FEDERATED、NDB等存储引擎。其中InnoDB是支持事务一致性的存储引擎。使用SHOW ENGINES命令，查看当前MySQL server端已经支持的存储引擎。


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 5.5起，用户create table 默认使用的存储引擎已经从原来的MyISAM改为InnoDB。从MySQL8.0起，系统表自身的存储引擎也换成InnoDB。MySQL默认自带的系统表不再使用MyISAM。 从官方文档中拿出大量篇幅来介绍InnoDB，也从侧面反映出InnoDB的重要性，可以说没有InnoDB的事务保证，就没有MySQL如此广泛的使用。

对表Table指定存储引擎

mysql> CREATE TABLE t1 (i INT) ENGINE = INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t2 (i INT) ENGINE = MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t3 (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)

还可以修改Table的存储引擎，准确说是convert转换存储引擎，因为修改存储引擎需要锁定并复制表数据。有风险，生产环境不建议使用此命令修改存储引擎。推荐使用Percona OSC工具。

mysql> insert into t3 values (9999);
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE t3 ENGINE=INNODB;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

通过show表的定义DDL语句来查看当前表的存储引擎（及字符集）

mysql> show create table t3;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

存储引擎对比

下表是常用存储引擎特性对比，数据来源来自于MySQL官方文档Table 16.1 （特别注意，网上有些资料是错误的）

特性	MyISAM	Memory	InnoDB	Archive	NDB
B树索引	支持	支持	支持	无	无
集群Cluster支持	无	无	无	无	支持
集群索引	无	无	支持	无	无
数据压缩	支持	无	支持	支持	无
数据缓存	无	不适用	支持	无	支持
加密数据	支持	支持	支持	支持	支持
外键支持	无	无	支持	无	支持
全文搜索索引	支持	无	支持	无	无
地理空间数据类型支持	支持	无	支持	支持	支持
地理空间索引支持	支持	无	支持	无	无
哈希索引	无	支持	否	无	支持
索引缓存	支持	不适用	支持	无	支持
锁定粒度	表	表	行	行	行
MVCC	无	无	支持	无	无
储存限制	256TB	内存	64TB	无	384EB
T树索引	无	无	无	无	支持
事物安全	无	无	支持	无	支持
更新数据字典的统计信息	支持	支持	支持	支持	支持

MyISAM存储引擎

MyISAM存储引擎在MySQL5.5之前，是用户创建表的默认存储引擎。历史及现在仍有许多系统使用该引擎。MyISAM不支持事务，不支持外键。其优势是访问速度很快，对于没有事务完整性要求或者低事务要求（如基本用Select / Insert访问，几乎没有Update/Delete）的场景可以使用本引擎。

MyISAM存储引擎的表，会在服务器磁盘下（/var/lib/mysql/下，database既schema子目录）产生三个文件，文件名同表名（Linux注意大小写），扩展名分别为：

.sdi文件。 SDI是Serialized Dictionary Information的缩写，MySQL8对于非InnoDB引擎提供的一种可读的文件格式来描述表的元数据信息。（对于MySQL5.7及更老版本，此文件后缀为*.frm 文件）
*.MYD 文件。存放表的数据，MyData
*.MYI 文件。存放表的索引，MyIndex

[root@centosa test]# pwd
/var/lib/mysql/test

[root@centosa test]# ls -l *t2*
-rw-r-----. 1 mysql mysql 8554 Nov  8 15:20 t2.frm
-rw-r-----. 1 mysql mysql    0 Nov  8 15:20 t2.MYD
-rw-r-----. 1 mysql mysql 1024 Nov  8 15:20 t2.MYI

提示：可以在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句分别指定上面*.MYD和*.MYI文件的位置。为了提高性能，可以将两个文件分别放到不同的目录（不同的物理磁盘），以分散IO，加快访问速度。

在MySQL OCP认证考试中，经常会考 key_buffer_size的用途。 key_buffer_size是用于MyISAM索引块的缓冲区的大小（单位字节，默认大小8MB）。一般可通过key_read/key_read_request的比值，或者key_write/key_write_request的比值来评估，key_buffer_size是否合适。详细参见官方文档该系统变量的说明。

对于使用MyISAM存储引擎的表，因为Mysql会存储表中数据的行数（在information_schema.TABLES表中存放），因此MySQL会快速返回count数量。而InnoDB由于支持MVCC对版本事务机制，无法保存表的count数量，只能select时通过扫描索引或全表获得count数量。

Memory存储引擎

MEMORY存储引擎是将数据不存放在磁盘中，而存放在内存中。每个Memory表在磁盘中只有一个*.frm的定义文件，磁盘上没有数据文件和索引文件。由于数据在内存中，它的访问非常的快，在内存中默认使用HASH索引。但一旦MySQL服务端关闭，表中的数据就会全部丢失（表定义及结构仍然存在）

mysql> create table tab_mem ( name varchar(64)) engine=memory;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tab_mem values ('Some info');
Query OK, 1 row affected (0.00 sec)




[root@centosa test]# ls -l *tab_mem*
-rw-r-----. 1 mysql mysql 8560 Nov  8 15:49 tab_mem.frm

可以在MySQL服务端启动时，使用 --init_file选项，把INSERT INTO ... SELECT语句或 LOAD DATA语句放入文件中。就可以在MySQL服务启动时自动将指定的数据加载到表中。

每个MEMORY表可以存放的数据量大小，受到max_heap_table_size参数的限制。默认初始值是16MB。

提示：MEMORY表特别适合①数据变动不大，但又需要高频或快速访问的表（例如，代码表、应用系统参数表等）；②应用处理过程中，临时结果存放的中间表。注意在硬件损坏、断电、MySQL关闭等多种情况都会丢失数据，使用它时，要确保数据丢失是没有负面影响的（即使有负面影响，也有健全的补救措施）。

InnoDB存储引擎

InnoDB提供了事务支持，具备事务安全性。但是相对于前两种引擎，它的性能要差一些。并且会占用更多的磁盘用于保存数据和索引。

MyISAM存储引擎的表，会在服务器磁盘下（/var/lib/mysql/下，database既schema子目录）产生两个文件（Linux注意大小写），扩展名分别为：

~~*.frm 文件。存放表的定义。 MySQL8已经没有该文件。~~
*.ibd 文件。存放表的数据和索引。如果是分区表，每个分区一个文件。文件名：表名+分区名

MySQL8没有frm文件。MySQL8将原FRM文件存放的信息及更多信息，统一定义为：序列化字典信息（Serialized Dictionary Information，SDI）。在MySQL8中，SDI被写在ibd文件内部，它是数据字典包含的信息的一个冗余副本它将表结构定义文件也放入了ibd文件。为此，MySQL8提供了一个工具程序 ibd2sdi。此程序可提取解析SDI信息，并以JSON文件输出，该JSON文件便于操作提取和构建表定义。（对于分区表，分区的公共定义信息在第一个*.ibd文件中）

InnoDB存储引擎支持其他存储引擎没有的特性，具体说明如下：

自动增长列

自动增长列可以手动赋值，但如果不赋值，则会自动增长赋值。

mysql> create table autoinc_test ( id int not null auto_increment, name varchar(32), primary key (id) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into autoinc_test values (1, '1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into autoinc_test values (2, '2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into autoinc_test values (null, '3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from autoinc_test;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)

进一步查看AUTO_INCREMENT的特点：

mysql>  insert into autoinc_test values (61, '61');
Query OK, 1 row affected (0.00 sec)

mysql> insert into autoinc_test values (null, 'may be 62');
Query OK, 1 row affected (0.00 sec)

mysql> select * from autoinc_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 1         |
|  2 | 2         |
|  3 | 3         |
| 61 | 61        |
| 62 | may be 62 |
+----+-----------+
5 rows in set (0.00 sec)

mysql> delete from autoinc_test where id =61 or id =62;
Query OK, 2 row affected (0.01 sec)

mysql> insert into autoinc_test values (null, 'may be 63');
Query OK, 1 row affected (0.01 sec)

mysql> select * from autoinc_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 1         |
|  2 | 2         |
|  3 | 3         |
| 63 | may be 63 |
+----+-----------+
4 rows in set (0.00 sec)


mysql>  insert into autoinc_test values (31, '31');
Query OK, 1 row affected (0.00 sec)

mysql> insert into autoinc_test values (null, 'may be 64');
Query OK, 1 row affected (0.00 sec)

mysql> select * from autoinc_test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 1         |
|  2 | 2         |
|  3 | 3         |
| 31 | 31        |
| 63 | may be 63 |
| 64 | may be 64 |
+----+-----------+
6 rows in set (0.00 sec)

AUTO_INCREMENT结论：当将任何其他值插入到 AUTO_INCREMENT列中时，如果大于AUTO_INCREMENT的Next值，并且序列Next值会被置为更大的值，以便下一个自动生成的值从最大列的值开始依次出现。

在5.7及更低版本存在bug，当数据库重启后，AUTO_INCREMENT会被设置为当前数据最大值+1，这在历史归档或者复制环境中会发生数据冲突。 Mysql 8.0无此问题（写入到了redo log中）

外键约束

下面两个表：课程表是主表，学生表是子表。学生表需要依赖课程表表，其中的class_id是外键，对应于课程表的class_id

mysql> create table class(class_id varchar(12), class_name varchar(100), primary key(class_id) );
Query OK, 0 rows affected (0.01 sec)

mysql> create table student(
    ->   student_id varchar(10),
    ->   name varchar(64),
    ->   class_id varchar(12), 
    ->   age int,
    ->   primary key(student_id),
    ->   constraint fk_class_id FOREIGN KEY (class_id) REFERENCES class(class_id)
    ->  );
Query OK, 0 rows affected (0.01 sec)

对于外键，还可以通过cascade进行级联更新或删除，既当父表进行更新或删除时，自动同步更新或删除子表中对应的记录。

通过show create table student

mysql> show create table student ;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                      |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `student_id` varchar(10) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `class_id` varchar(12) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  KEY `fk_class_id` (`class_id`),
  CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

主键和索引

InnoDB存储引擎表的数据文件就是以聚簇索引的形式保持的，这个聚簇索引也被称为主索引，也是InnoDB表的主键。如果表没有显示的的指定主键，则InnoDB存储引擎会自动创建一个6个字节的long类型的隐藏字段作为主键。

在InnoDB中，除了主键之外，其他的索引都称为辅助索引或者二级索引。二级索引会指向主索引。

因此，建议，创建表时都应同时定义主键，这也符合三范式的原则。

存储方式

InnoDB的表存储方式分为每表独立文件，多表共享存储。每表独立文件值每个表建立一个独立的ibd文件。多表共享指所有表都放在系统表空间上存储（不单独建立文件）。两者方式通过参数innodb_file_per_table控制。默认为ON每表独立文件的方式存储。

事务

InnoDB支持事务，主要是靠redolog实现。注意redolog是InnoDB存储引擎的，不是MySQL系统中的。 binlog才是MySQL中的。而且在InnoDB中，如果双一设置（sync_binlog=1和innodb_flush_log_at_trx_commit=1）时，在commit过程中，redo log是先于binlog写入磁盘的。