MySQL数据库存储引擎MyISAM与InnoDB

前言

MySQL存储引擎是MySQL数据库中负责管理数据存储和检索的组件，不同的存储引擎提供了不同的功能和特性，可以根据实际需求选择合适的存储引擎来优化数据库性能和功能。以下是一些常见的MySQL存储引擎：InnoDB、MyISAM、MEMORY、NDB Cluster、CSV、Archive等。这里介绍其中两种常用的引擎：MyISAM 和 InnoDB。

目录

一、存储引擎概念

二、数据写入流程 

三、MyISAM

1. 特点介绍

2. 适用的环境

四、InnoDB

1. 特点介绍

2. 适用的环境

五、管理存储引擎

1. 查看表使用的存储引擎

2. 修改存储引擎

六、MyISAM 和 InnoDB 区别

---

一、存储引擎概念

• MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
• 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
• MySQL数据库中的组件，负责执行实际的数据I/O操作
• MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

二、数据写入流程 

① 首先，通过执行INSERT语句将数据插入到MySQL数据库中的表中。这个INSERT语句包含要插入的数据值以及目标表的名称。

② 在创建表时，需要选择适合的存储引擎。

③ 当数据被插入到表中时，MySQL通常会将数据首先存储在内存中的数据缓存中。

④ MySQL根据存储引擎的不同，会有不同的策略将数据从内存缓存刷新到磁盘上的数据文件中。

⑤ 一旦数据被成功写入到磁盘中的数据文件，数据就被持久化存储了。 

三、MyISAM

1. 特点介绍

① MyISAM不支持事务，也不支持外键

② 访问速度快

③ 对事务完整性没有要求

④ 在磁盘上存储成三个文件

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

⑤ 表级锁定形式，数据更新时锁整表 

• 表级锁是一种用于控制对整个表的并发访问的锁机制
• 数据库在读写过程中相互阻塞，串行操作，按照顺序操作，每次读写锁全表
• 会在数据写入的过程阻塞用户数据的读取
• 也会在数据读取的过程中阻塞用户的数据写入

⑥ 数据单独写入或读取，速度过程较快且占用资源相对少

⑦ MyIAM支持的存储格式 

• 静态表：默认的存储格式，字段都是非可变字段（char）、固定长度；存储块，故障易恢复，占用空间多
• 动态表：包含可变字段（varchar），记录不是固定长度的，占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 optimize table 语句或 myisamchk -r 命令来改善性能，故障恢复相对比较困难
• 压缩表：由 myisamchk 工具创建，占据非常小的空间

2. 适用的环境

① 公司业务不需要事务的支持 

② 单方面读取或写入数据比较多的业务

③ 不适合数据读写都比较频繁场景

④ 使用读写并发访问相对较低的业务

⑤ 数据修改相对较少的业务

⑥ 对数据业务一致性要求不是非常高的业务

⑦ 服务器硬件资源相对比较差

简而言之：适合于单方向的任务场景、同时并发量不高、对于事务要求不高的场景

四、InnoDB

1. 特点介绍

① 支持4个事务隔离级别

② 行级锁定，但是全表扫描仍然会是表级锁定

• 行级锁是一种用于控制对数据库表中单行记录的并发访问的锁机制
• 行级锁类型有：共享锁和排他锁
• 在需要高并发访问的情况下，行级锁可以提供更好的并发控制，减少锁冲突
• 在事务中需要对特定行进行读写操作时，可以使用行级锁确保数据的一致性

③ 读写阻塞与事务隔离级别相关

④ 高效的缓存素引和数据

⑤ 表与主键以簇的方式存储

• 数据行在物理上是按照主键的顺序排列的，而不是按照它们被插入的顺序 

⑥ 支持分区、表空间，类似oracle数据库

⑦ 支持外键约束，5.5后支持全文索引

⑧ 对硬件资源要求较高

2. 适用的环境

① 业务需要事务的支持

② 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成

③ 业务数据更新较为频繁的场景，如：论坛，微博等

④ 业务数据一致性要求较高，如：银行业务

⑤ 硬件设备内存较大，利用Innodb较好的缓存能力来提高内存利用率，减少磁盘I/O的压力

五、管理存储引擎

1. 查看表使用的存储引擎

显示当前MySQL服务器支持的存储引擎列表：

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

方法一：

show table status from 库名 where name='表名'\G；
示例；
mysql> show table status from school where name = 'class'\G;
         Engine: InnoDB           # 存储引擎为 InnoDB

方法二：

use 库名;
show create table 表名;
示例：
mysql> use school;
mysql> show create table class;           # 显示 class 表的详细信息
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |    # 存储引擎为 InnoDB

2. 修改存储引擎

方法一：通过 alter table 修改

use 库名;
alter table 表名 engine = 存储引擎;
示例：
mysql> use school;                         # 切换库 school
mysql> alter table class engine = MyISAM;  # 修改 class 表的存储引擎为 MyISAM
mysql> show create table class;            # 显示 class 表的详细信息
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |     # 存储引擎为 MyISAM，修改前为 InnoDB

方法二：通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务

vim /etc/my.cnf
[mysqld]
default-storage-engine=存储引擎;
示例：
[root@localhost ~]# vim /etc/my.cnf
default-storage-engine=MyISAM        # 指定 MySQL 服务器默认使用的存储引擎是 MyISAM
[root@localhost ~]# systemctl restart mysqld.service  # 重启服务

[root@localhost ~]# mysql -u root -p123456
mysql> use school;
mysql> mysql> show table status from school;
+----------+--------+
| Name     | Engine |
+----------+--------+
| class    | MyISAM |    # 除了 class 表前面手动修改了存储引擎，现有其他表均为 InnoDB
| class01  | InnoDB |
| newclass | InnoDB |
| test01   | InnoDB |     
+----------+--------+
mysql> create table class02 (id int,name char(5));  # 新建 class02 表
mysql> show create table class;
 ENGINE=MyISAM DEFAULT CHARSET=utf8 |     # 新建 class02 表存储引擎已默认为 MyISAM

方法三：通过 create table 创建表时指定存储引擎

use 库名;
create table 表名(字段1 数据类型,...) engine=存储引擎;
示例：
mysql> create table class03 (id int not null,name char(5) not null)engine=CSV;
# 创建表 class03 并指定存储引擎为 CSV
mysql> show create table class03;        # 显示 class02 表的详细信息
) ENGINE=CSV DEFAULT CHARSET=utf8 |      # 存储引擎 CSV

六、MyISAM 和 InnoDB 区别

存储引擎	事务支持	行级锁	外键约束	崩溃恢复	空间占用	全文索引的支持
MyISAM	不支持事务，不具备事务的ACID特性	使用表级锁	不支持	恢复方面较弱，容易出现数据损坏	相对较小，适合存储大量只读数据	支持
InnoDB	支持事务，提供了事务的隔离级别，支持事务的回滚和提交	支持行级锁	支持	支持事务的回滚和提交	占用更多的空间，因为它支持事务和行级锁	5.6.4版本之后开始支持