开发经验｜小白该如何学习MySQL存储引擎

上一篇简单介绍了下MySQL是什么、小版本之间的差异、名称的来源、安装以及连接。本篇想着重讲下MySQL的存储引擎。

1 存储引擎的历史

MySQL中的存储引擎的发展历史可谓是丰富多彩，至今为止，MySQL已经有超过20年的历史，其存储引擎不断进行着演化和发展。

2 为什么需要存储引擎？

这是一个很好的问题。存储数据后，数据使用需求场景不一样，所以要求数据的特性也就不同了。需要在调用数据时有不同的特性或者性能优势时，数据库引擎便起了作用，可以根据应用的具体需求选择最适合的存储引擎。

首先，数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据库操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存储引擎，还可以获得特定的功能。

其次，MySQL之所以需要存储引擎是因为它允许用户根据不同的需求和场景选择合适的存储引擎，从而灵活地管理和操作数据。不同的存储引擎具有不同的特性和性能优势，可以根据应用的具体需求选择最适合的存储引擎。

在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表适用不同的存储引擎。在第4小节，大家可以看下不同的存储引擎有不同的应用场景或者说不同的需求需要使用不同的存储引擎。

3 常用的存储引擎

MySQL支持多种存储引擎（登录到MySQL服务端后，使用命令show engines;可以查看到当前服务器支持的存储引擎）。

在MacOS上查看MySQL支持的存储引擎

在Windows上查看MySQL支持的存储引擎

上面的参数说明：

参数	解释
Engine	当前版本的 MySQL 所支持的引擎类型、引擎名称
Support	对应引擎是否支持，指默认引擎，可以在InnoDB查阅
Comment	存储引擎的简要说明，大家可以翻译下
Transactions	是否支持事务
XA	是否支持XA事务
Savepoints	是否支持保存点

面临这么多的存储引擎，那我们在使用时如何选择存储引擎呢？在常用的小型开发项目或者个人学习过程中，我们最常用的存储引擎就是InnoDB引擎和MyISAM引擎，其次是MEMORY引擎和CSV引擎。其中InnoDB引擎是在MySQL 5.5+默认的，在此之前MySQL的默认存储引擎是MyISAM。当然，现在面试官应该不会再问这个问题了～

4 存储引擎详细介绍

• MyISAM：是MySQL默认的、老版本常用的存储引擎之一（MySQL 5.5+已经不是默认存储引擎）。它以表为单位进行存储，适用于读密集型应用，具有快速的插入速度和高效的全文搜索功能。但不支持事务和行级锁定，容易出现表级锁定和数据不一致问题。
• InnoDB：在MySQL5.5+，MySQL默认的、最常用的存储引擎。它提供了ACID事务支持，支持行级锁定，并具有较好的并发处理能力。InnoDB适合于多用户、高并发的OLTP（联机事务处理）应用，支持自动崩溃恢复和外键约束等功能。
• Memory/Heap：也被称为Heap存储引擎，将表中的数据存储在内存中，速度非常快。但因为数据存储在内存中，所以在重启数据库或服务器崩溃时，数据会丢失。适合于临时数据存储和高速缓存等场景。
• Archive：适用于大量历史数据的存储和查询，以最小的存储空间来实现快速插入和压缩。但它不支持更新和删除操作，只能进行插入和查询。

5 如何使用存储引擎

5.1 明确指定数据表的存储引擎

作为测试，我们首先需要选择一个数据库，其次在数据库中创建数据表，最后查看当前数据表使用的存储引擎。

mysql>
mysql> use db_learn;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> CREATE TABLE `it_learn_student` (
    ->   `id` INT(11) NOT NULL COMMENT '编号',
    ->   `name` VARCHAR(128) NOT NULL COMMENT '姓名',
    -> username VARCHAR(128) NOT NULL COMMENT '姓名',
    ->   score DECIMAL(5,0) DEFAULT NULL COMMENT '分数',
    ->   passwd CHAR(48) DEFAULT '密码',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '考试分数表';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>
mysql> SHOW CREATE TABLE `it_learn_student` \G;
*************************** 1. row ***************************
       Table: it_learn_student
Create Table: CREATE TABLE `it_learn_student` (
  `id` int NOT NULL COMMENT '编号',
  `name` varchar(128) NOT NULL COMMENT '姓名',
  `username` varchar(128) NOT NULL COMMENT '姓名',
  `score` decimal(5,0) DEFAULT NULL COMMENT '分数',
  `passwd` char(48) DEFAULT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='考试分数表'
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>
mysql>

5.2 修改表的存储引擎

如果我们已经创建了表，但是我们需要修改存储引擎，如何处理呢？借鉴修改表结构的语句，我们可以按照这样结构写：ALTER TABLE [tableName] ENGINE = [engineName];例如我上面创建的表指定了存储引擎InnoDB，现在想修改为MyISAM存储引擎，转化为实际的语句则需要这样子写：

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

mysql>
mysql> SHOW CREATE TABLE `it_learn_student` \G;
*************************** 1. row ***************************
       Table: it_learn_student
Create Table: CREATE TABLE `it_learn_student` (
  `id` int NOT NULL COMMENT '编号',
  `name` varchar(128) NOT NULL COMMENT '姓名',
  `username` varchar(128) NOT NULL COMMENT '姓名',
  `score` decimal(5,0) DEFAULT NULL COMMENT '分数',
  `passwd` char(48) DEFAULT '密码',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COMMENT='考试分数表'
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

再次检查时，发现存储引擎已经指定到MyISAM。上面所讲的都是显示的指定存储引擎或者使用默认的存储引擎。

参考文献：[1] https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

本文参与了SegmentFault 思否写作挑战赛活动，欢迎正在阅读的你也加入。