MySQL存储引擎详解（新手必看）

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 支持的存储引擎有 InnoDB、MyISAM、Memory、NDB、Merge、Archive、Federated、CSV、BLACKHOLE 等。

可以使用 SHOW ENGINES 语句查看系统所支持的引擎类型，结果如下：

mysql> SHOW ENGINES \G
*** 1. row ***
        Engine: FEDERATED
      Support: NO
  Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
Savepoints: NULL

*** 2. row ***
        Engine: MRG_MYISAM
      Support: YES
  Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
Savepoints: NO

*** 3. row ***
        Engine: MyISAM
      Support: YES
  Comment: MyISAM storage engine
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: CSV
      Support: YES
  Comment: CSV storage engine
Transactions: NO
          XA: NO
Savepoints: NO

*** 6. row ***
        Engine: MEMORY
      Support: YES
  Comment: Hash based, stored in memory, useful for temporary tables
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: InnoDB
      Support: DEFAULT
  Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
Savepoints: YES

*** 9. row ***
        Engine: PERFORMANCE_SCHEMA
      Support: YES
  Comment: Performance Schema
Transactions: NO
          XA: NO
Savepoints: NO

Support 列的值表示某种引擎是否能使用：YES 表示可以使用，NO 表示不能使用，DEFAULT 表示该引擎为当前默认的存储引擎。

查看当前默认的存储引擎，可以使用下面的命令：

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+

更改数据表的存储引擎

MySQL 数据库在创建表的时候，可以添加默认的存储引擎。例如下面的例子：

create table books(
id int,
name varchar(20) not null
)engine=MyISAM default charset=utf8mb4;

更改表的存储引擎的语法格式如下：

ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

下面示例将数据表 books 的存储引擎修改为 InnoDB。在修改存储引擎之前，先使用SHOW create table查看表 books 当前的存储引擎，结果如下：

mysql> SHOW create table books \G
*************************** 1. row ***************************
     Table: books
Create Table: create table `books` (
  `id` int DEFAULT NULL,
  `name` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

可以看到，表 books 当前的存储引擎为 ENGINE=MyISAM，接下来修改存储引擎类型，输入如下 SQL 语句并执行：

mysql> ALTER TABLE books ENGINE=InnoDB;

使用 SHOW create table 再次查看表 books 的存储引擎，发现表 books 的存储引擎变成了“InnoDB”，结果如下：

mysql> SHOW create table books \G
*************************** 1. row ***************************
     Table: books
Create Table: create table `books` (
  `id` int DEFAULT NULL,
  `name` varchar(20) NOT NULL
) ENGINE= InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

选择合适的存储引擎

不同存储引擎都有各自的特点，以适应不同的需求。为了做出选择，首先需要考虑每一个存储引擎提供了哪些不同的功能。

下表分析了常用存储引擎的对比情况。

表：常用存储引擎的对比情况
特点	InnoDB	MyISAM	MEMORY	MERGE
存储限制	64TB	有	有	没有
事务安全	支持	-	-	-
锁机制	行锁	表锁	表锁	表锁
B 数索引	支持	支持	支持	支持
哈希索引	-	-	支持	-
全文索引	-	支持	-	-
集群索引	支持	-	-	-
数据缓存	支持	-	支持	-
索引缓存	支持	支持	支持	支持
数据可压缩	-	支持	-	-
空间使用	高	低	N/A	低
内存使用	高	低	中等	低
批量插入速度	低	高	高	高
支持外键	支持	-	-	-

如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是个很好的选择；
如果数据表主要用来插入和查询记录，则 MyISAM 引擎能提供较高的处理效率；
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的 Memory 引擎，MySQL 中使用该引擎作为临时表，存放查询的中间结果；
如果只有 INSERT 和 SELECT 操作，可以选择 Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的；
Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同引擎，以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

MySQL存储引擎详解（新手必看）

更改数据表的存储引擎

选择合适的存储引擎

相关文章