【数据库】MySQL有几种存储引擎（表类型）？各自有什么区别？

FixPng

已于 2024-02-24 15:20:54 修改

阅读量1.1k

点赞数 19

分类专栏：数据库文章标签：数据库 mysql

于 2024-02-22 18:02:47 首次发布

本文链接：https://blog.csdn.net/qq_45277554/article/details/136240351

版权

数据库专栏收录该内容

13 篇文章 2 订阅

订阅专栏

本文详细介绍了MySQL的几种存储引擎（如MyISAM、InnoDB、MEMORY）的特点、区别，以及它们在事务支持、并发性能和适用场景上的差异。重点突出了MyISAM和InnoDB引擎的优缺点，以及如何针对不同业务场景进行优化。

摘要由CSDN通过智能技术生成

MySQL有几种存储引擎（表类型）？各自有什么区别？

MySQL官方有多种存储引擎：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。第三方存储引擎中比较有名的有：TokuDB、Infobright、InnfiniDB、XtraDB(InnoDB增强版)。其中，最常见的两种存储引擎是MyISAM和InnoDB。MyISAM是MySQL关系型数据库管理系统的默认存储引擎（MySQL 5.5 以前）。这种MySQL表存储结构从旧ISAM代码扩展出许多有用的功能。从MySQL 5.5 开始，InnoDB引擎由于其对事务和数据完整性，以及更高的并发性等优点开始逐步地取代MyISAM，作为MySQL数据库的默认存储引擎。

很多人刚接触MySQL的时候，可能会有些惊讶，他竟然有不支持事务的存储引擎，学过关系型数据库理论的人都知道，事务是关系型数据库的核心。但是在现实应用中（特别是互联网），为了提高性能，在某些场景下可以摒弃事务。

MyISAM

ISAM是最原始的存储引擎，它管理这非事务性表，后来它就被MyISAM代替了，而且MyISAM是向后兼容的，因此，可以忘记这个ISAM存储引擎。

MyISAM 存储引擎管理非事务表，提供高速存储和检索，以及全文搜索的能力。该引擎插入数据快，空间和存储使用比较低。

（1）存储组成

每个 MyISAM 在磁盘上存储成三个文件。每一个文件的名字就是表的名字，文件名都和表名相同，扩展名指出了文件类型。这里特别要注意的是，MyISAM不缓存数据文件，只缓存索引文件。

表定义的扩展名为 .frm (frame，存储表定义)。
数据文件的扩展名为 .MYD (MYData，存储数据)。
索引文件的扩展名是 .MYI (MYIndex，存储索引)。

数据文件和索引文件可以放置在不同的目录，平均分布 I/O，获得更快的速度，而且其索引是压缩的，能加载更多索引，这样内存使用率就对应提高了不少，压缩后的索引也能节约一些磁盘空间。

（2）MyISAM具有的特点

不支持事务，不支持外键约束，但支持全文检索，这可以极大地优化 LIKE 查询的效率。
表级锁定（更新时锁定整个表）：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。MyISAM不支持行级锁，只支持并发插入的表锁，主要用于高负载的查询。
读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读取本身并不会阻塞另外的读取。
不缓存数据，只缓存索引：MyISAM可以通过 key_buffer 缓存以大大提高访问性能、减少磁盘 I/O，但是这个缓存区指挥缓存索引，而不会缓存数据。

[root@mysql]# grep key_buffer my.cnf
key_buffer_size = 16M

读取速度较快，占用资源相对少。
MyISAM 引擎是 MySQL 5.5 之前版本默认的存储引擎。
并发量较少，不适合大量 UPDATE。

（3）MyISAM 引擎适用的生产业务场景

不需要事务支持的业务，一般为读数据表较多的网站应用。
并发相对较低的业务（纯读纯写高并发也可以）。
数据修改相对较少的业务。
以读为主的业务。
对数据一致性要求不是非常高的业务。
中小型网站的部分业务。

单一对数据库的操作都可以使用 MyISAM，所谓单一就是尽量纯读或纯写（INSERT、UPDATE、DELETE）等。
生产建议：没有特别需求时，一律用InnoDB。

（4）MyISAM 引擎调优精要

尽量使用索引，优先使用MySQL缓存机制。
调整读写优先级，根据实际需要确保重要操作更有先。
启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）。
尽量顺序操作让 INSERT 数据都能写入到尾部，减少阻塞。
分解大的操作，降低单个操作的阻塞时间。
降低并发数，某些高并发场景通过应用进行排队机制。
对相对静态的数据，充分利用 Query Cache 可以极大地提高访问效率。

#这几个参数都是MySQL自身缓存设置
[root@mysql]# grep query my.cnf
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k

MyISAM 的 COUNT 只有在全表扫描的时候特别高效，带有其他条件的 COUNT 都需要进行实际的数据访问。
把主从同步的主库使用 InnoDB, 从库使用 MyISAM 引擎。

MyISAM 类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

静态型：就是定义的表列大小是固定的（即不含有 XBLOB、XTEXT、VARCHAR 等长度可变的数据类型），这样 MySQL 就会自动使用静态 MyISAM 格式。该格式的表性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。但是，高性能是由空间作为代价换来的，因为在定义的时候是固定的，所以，不管列中的值有多大，都会以最大值为准，占据了整个空间。
动态型：如果列（即使只有一列）定义为动态的（XBLOB、XTEXT、VARCHAR 等数据类型），那么这时 MyISAM 就会自动使用动态那个，对然动态性的表占用了比静态型较少的空间，但性能降低，因为如果某个字段的内容发生改变那么其位置很可能需要移动，这样就会导致碎片的产生。随着数据变化的增多，碎片就会增加，数据访问性能就会相应地降低。对于因为碎片的原因而降低数据访问性，有两种解决办法：
- 尽可能使用静态数据类型。
- 经常使用 OPTIMIZE TABLE 语句，它会整理表的碎片，恢复由于表的更新和删除导致的空间丢失。
压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，那么这种情况就是用 MyISAM 的压缩型表来减少空间的占用。

InnoDB

InnoDB 用于事务处理应用程序，主要面向 OLTP 方面的应用。该引擎由 InnoDB 公司开发，其特点是行锁设置，并支持类似于 Oracle 的非锁定读，即默认情况下读不产生锁。InnoDB 将数据放在一个逻辑表空间中。InnoDB通过多版本并发控制来获得高并发行，实现了 ANSI 标准的4中隔离级别，默认为 Repeatable，使用一种被称为 next-key locking 的策略避免幻读。对于表中数据的存储， InnoDB采用类似 Oracle 索引组织表 Clustered 的方式进行存储。如果对事务的完整性要求比较高，要求实现并发控制，那么选择 InnoDB引擎有很大的优势。需要频繁地进行更新，删除操作的数据库，也可以选择 InnoDB 存储引擎。因为，InnoDB 存储引擎提供了具有提交（COMMIT）、回滚（ROLLBACK）和崩溃恢复能力的事务安全。

InnoDB 类型的表只有 idb 文件，分为数据区和索引区，有较好的读写并发能力。物理文件有日志文件、数据文件和索引文件。其中，索引文件和数据文件放在一个目录下，可以设置共享文件，独享文件两种格式。

（1）InnoDB 引擎特点

支持事务：包括 ACID 事务支持，支持4个事务隔离级别，支持多版本读取。
行级锁定（更新时一般时锁定当前行）：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
支持崩溃修复能力和 MVCC（多版本并发控制）。
读写阻塞与事务隔离级别相关。
具有非常高效的缓存特性：能缓存索引，也能缓存数据。
整个表和逐渐以 CLUSTER 方式存储，组成一颗平衡树。
所有 SECONDARY INDEX 都会保存主键信息。
支持分区、表空间，类似 Oracle 数据库。
支持外键约束（Foreign Key），外键所在的表称为子表，而所依赖的表称为父表。
InnoDB 支持自增长列（AUTO_INCREMENT）,自增长的列不能为空。
InnoDB 的索引和数据是紧密捆绑的，没有使用压缩从而使 InnoDB 比 MyISAM 体积大不少。

（2）优点

支持事务，用于事务处理应用程序，具有众多特性，包括 ACID 事务支持，支持外键，同时支持崩溃修复能力和并发控制。并发量较大，适合大量 UPDATE。

（3）缺点

对比 MyISAM ，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。相比 MyISAM ，InnoDB 更消耗资源，速度没有 MyISAM 快。

（4）InnoDB 引擎适用的生产业务场景

如果对事务的完整性要求比较高，要求实现并发控制，那么选择 InnoDB 有很大的优势。需要频繁地进行更新，删除操作的数据库时，也可以选择 InnoDB 。具体分类如下：

需要事务支持（具有较好的事务特性）。
行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。
数据更新较为频繁地场景。例如：电子公告牌系统、社交网等。
数据一致性要求较高的业务。例如：充值、银行转账等。
硬件设备内存较大，可以利用 InnoDB 较好的缓存能力来提高内存利用率，尽可能减少磁盘 I/O。

[root@mysql]# grep -i innodb my.cnf
#default_table_type = InnoDB
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
#物理数据文件：
[root@mysql]# ll data/ibdata1
-rw-rw---- 1 mysql mysql 79691776 Feb 14 09:52 data/ibdata1

（5）InnoDB 引擎调优精要

主键尽可能小，避免给 SECONDARY INDEX 带来过大的空间负担。
避免全表扫描，因为会使用表锁。
尽可能缓存所有的索引和数据，提高相应速度，减少磁盘 I/O 消耗。
在执行大量插入操作的时候，尽量自己控制事务而不要使用 AUTOCOMMIT 自动提交。有开关可以控制提交方式。
合理设置 innodb_flush_log_at_trx_commit 参数值，不要过度追求安全性。
避免主键更新，因为这回带来大量的数据移动。

MEMORY（HEAP）

MEMORY：也称为HEAP引擎，将数据存储在内存中，读写速度非常快，适合于临时数据存储和高速缓存

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表（以前也叫做HEAP表）是非常有用的。Memory 至少比 MyISAM 要快一个数量级，因为每个基于 MEMORY 存储引擎地表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为 frm 类型。该文件只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率，不需要进行磁盘 I/O。所以 Memory 表的结构在重启以后还会保留，但数据会丢失。

Memory 表支持 Hash 索引，因此查找操作非常快。虽然 Memory 表的速度非常快，但还是无法取代传统的基于磁盘的表。Memory 表是表级锁，因此并发写入的性能较低。它不支持 BLOB 或 TEXT 类型的列，并且每行的长度是固定的，所以即使指定了 VARCHAR 列，实际存储时也会转换成 CHAR，这可能导致部分内存的浪费。

特点：
内存存放
hash索引（默认）

文件：
存储表结构信息：.sdi

其他存储引擎

NDB Cluster：也称为NDB引擎或者MySQL Cluster，是一种基于共享存储的集群存储引擎，支持高可用性和高性能的数据存储。
Archive：用于存储归档数据，压缩存储空间，但不支持索引和事务。
CSV：用于存储CSV格式的数据，适合于导入和导出数据。
Blackhole：接收数据但不存储任何内容，适用于复制和日志记录等场景。
Federated：允许将数据存储在远程MySQL服务器上，适合于分布式数据库应用。
…

详情参考MySQL官方文档：
https://dev.mysql.com/doc/refman/8.3/en/storage-engines.html

MySQL显示及修改可用存储引擎

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

上面这个查询结果显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎。

可以使用多种方法指定一个要使用的存储引擎。如果想用一种能满足大多数数据库需求的存储引擎，那么可以在 MySQL 的配置文件（my.cnf）中设置一个默认的引擎类型（在[mysqld]组下，使用 default-storage-engine=InnoDB）,或者在启动数据库服务器时，在命令行后面加上“–default-storage-engine”选项。

最直接的使用存储引擎的方式是在创建表时指定存储引擎的类型，例如：

mysql> CREATE TABLE mytable(id int,title char(20)) ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLE STATUS LIKE 'mytable'\G;
*************************** 1. row ***************************
           Name: mytable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-02-22 16:37:06
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

还可以改变现有的表使用的存储引擎，用以下语句：

mysql> ALTER TABLE mytable ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS LIKE 'mytable'\G;
*************************** 1. row ***************************
           Name: mytable
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 23925373020405759
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-02-22 16:39:16
    Update_time: 2024-02-22 16:39:16
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

然而，使用这种方式修改表类型的时候需要非常仔细，因为对不支持同样的索引、字段类型或者表大小的一个类型进行修改可能导致数据丢失。

Oracle是否有存储引擎

oracle中不存在引擎的概念，数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。