第34讲：MySQL中常用的几种存储引擎以及如何选择

文章目录

1.MySQL中常用的存储引擎

在MySQL数据库中常用的存储引擎有InnoDB、MyISAM和Memory。

2.InnoDB存储引擎

2.1.InnoDB存储引擎的特点

InnoDB是一种兼顾可靠性和高性能的通用存储引擎，使用的最为广泛，在MySQL 5.5版本之后，InnoDB是默认的MySQL数据库存储引擎。

InnoDB存储引擎的特点如下：

• 任何增删改查的语句都支持事务操作，都遵循ACID模型，也就是事务的四种模型。
• 支持行级锁，提高并发访问的性能。
• 支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

InnoDB存储引擎数据文件的特点：

• 使用InnoDB存储引擎后，默认情况下，每张表都会有一张对应的表空间文件。
• 表空间的命名格式是{表名}.ibd，在表空间中会记录该张表的表结构、数据以及索引。
• 在早期版本的InnoDB引擎中，表结构是存放在frm文件中的，在MySQL8.0后，表结构存放在sdi数据字典里，但是数据字典也集成在了ibd文件中，因此一张表空间文件会记录该表的表结构、数据和索引。

 innodb_file_per_table

参数可以设置是否开启每张表都拥有一个表空间文件，默认开启，通过show variables like指令就可以搜索数据库的配置参数。

mysql>show variables like'%innodb_file%';+-----------------------+-------+| Variable_name         |Value|+-----------------------+-------+| innodb_file_per_table |ON|+-----------------------+-------+1rowinset(0.00 sec)

2.2.观察InnoDB存储引擎的数据持久化文件

我们可以进入到数据库的文件目录，可以看到每张表都有一个表空间文件。

ibd文件是无法直接打开的，可以通过

ibd2sdi

工具将ibd文件转换成sdi文件，看到表的表结构、数据和索引等信息。

[root@mysql~]# ibd2sdi /var/lib/mysql/db_1/bmxxb.ibd 

2.3.InnoDB存储引擎的逻辑存储结构

InnoDB存储引擎的逻辑结构分为以下几层：

• TableSpace：表空间- 表空间是InnoDB存储引擎逻辑结构中的最高层，ibd文件就是表空间文件，在表空间中可以包含多个Segment段。
• Segment：段- 表空间就是由很多个段组成的，常见的段有数据段、索引段、回滚段等等，InnoDB中对于段的管理都是引擎自身完成的，不需要人为控制，一个段中可以包含很多个区。
• Extent：区- 区是表空间中的单元结构，每个区的固定大小为1M，一个区中有很多个页，默认情况中，InnoDB存储引擎页的大小为16KB，因此一个区中一共有64个连接的页。
• Page：页- 页是组成区的最小单元，页也是InnoDB存储引擎中磁盘管理的最小单元，每个页的大小为16KB，为了保证页的连续性，InnoDB存储引擎每次会从磁盘中申请4-5个区，每个页下又拥有很多个行。
• Row：行- InnoDB存储引擎是面向行的，也就是说数据时安装行进行存放的，在每一行中除了定义表的所有字段外，还包含Trxid和roll pointer两个隐藏的字段。

3.MyISAM存储引擎

MyISAM存储引擎是MySQL早期的默认存储引擎。

MyISAM存储引擎的特点：

• 不支持事务、不支持外键。
• 只支持表级锁，不支持行级锁。
• 访问速度很快。

MyISAM存储引擎的数据文件：

• xxx.sdi：存储表结构的信息。
• xxx.MYD：存储表中的数据。
• xxx.MYI：存储表中的索引。

4.Memory存储引擎

Memory存储引擎下表数据是存储在内存中的，可能会由于硬件的问题或者是断电的影响，就会导致数据丢失，因此只有当表是临时表或者缓存表的时候可以使用Memory存储引擎。

Memory存储引擎的特点：

• 在内存中存放数据。
• 支持hash索引。

Memory存储引擎的数据文件只有一个.sdi的文件。

5.各个存储引擎的对比表

特点InnoDBMyISAMMemory存储限制64TB有有事务安全支持--锁机制行级锁表级锁表级锁B+tree索引支持支持支持Hash索引--支持全文索引支持（5.6版本后）支持-空间使用高低不使用硬盘空间，只消耗内存内存使用高低中等批量插入速度低高高支持外键支持--
InnoDB和MyISAM的区别：

• InnoDB支持行级锁，MyISAM支持表级锁。
• InnoDB支持事务，MyISAM不支持事务。
• InnoDB支持外键，MyISAM不支持外键。

6.各场景下应该如何选择存储引擎

在选择存储引擎时，应该根据应用系统的特点去选择合适的存储引擎，存储引擎没有好坏之分，最合适的就是最好用的，对于复杂的应系统，还可以根据实际情况选择多种的存储引擎组合使用。

• InnoDB的适用场景：- 默认存储引擎，支持事务、外键等，如果对事务的完整性要求比较高，在并发事务的条件下要求数据的一致性，数据除了插入和查询外，还有很多时候会用到更新和删除的操作，此时InnoDB引擎是最合适的。
• MyISAM的适用场景：- 如果应用都是以读取和插入数据为主，只有很少的删除和更新操作，对于事务的完整性、并发性要求不高，可以选择MyISAM引擎。- MyISAM引擎使用的很少，几乎被MongoDB替代。
• Memory的适用场景：- 将所有的数据保存在内存中，访问的速度很快，通常应用于临时表或者缓存表，Memory引擎对表的大小有限制，太大的表无法缓存到内存中，也无法保障数据的安全性。- Memory应用的也很少，几乎被Redis所替代。