十六、MySQL基础系列笔记之数据表存储引擎的介绍

MySQL存储引擎概述

MySQL默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率。

MySQL支持的存储引擎包括：InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、ARCHIVE、MERGE、FEDERATED、EXAMPLE、NDB等，其中InnoDB和NDB提供失误安全表，其他存储引擎都是非失误安全表。

创建表时若不选择存储引擎，那么系统就会使用默认的存储引擎。MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为InnoDB.

查看当前默认存储引擎

show variables like 'default_storage_engine';

查看当前数据库支持的存储引擎

show engines;

各存储引擎特性

说明：留空则为不支持

MySQL常用的存储引擎有MyISAM、InnoDB、MEMORY、Archive。下面也将记录这4种引擎。

常用的存储引擎

MyISAM

MyISAM不支持事务也不支持外键。MyISAM在某些场景中相对InnoDB的访问速度有明显的优势；对事务完整性没有要求或者以SELECT、INSERT为主的应用可以使用InnnoDB引擎。

**每个MyISAM在磁盘上存储成3个文件，其文件名与表名相同，但扩展名不同，**如下：

• .frm（存储表定义）
• .MYD（MYData，存储数据）
• .MYI（MYIndex，存储索引）

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

MyISAM的表还支持3种不同的存储格式，如下：

• 静态（固定长度）表
• 动态表
• 压缩表

静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是：存储非常迅速、容易缓存、出现故障容易恢复；缺点是：占用的空间通常比动态表多。

动态表中包含包含变长字段，记录不是固定长度的，优点是：占用的空间相对较少；缺点是：频繁的更新和记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，且在出现故障时恢复相对比较困难。

压缩表由myisampack工具创建，优点是占用磁盘空间非常小。

InnoDB

InnoDB具有提交、回滚和崩溃恢复能力的事务安全保障，同时提供了更小的锁粒度和更强的并发能力，拥有自己独立的缓存和日志。

对比MyISAM存储引擎，InnoDB会占用更多的磁盘空间以保留数据和索引。

自动增长列

InnoDB表的自动增长列可以手工插入，若插入的值为空，则实际插入的将是自动增长后的值。

外键约束

Mysql支持外键的常用存储引擎只有InnoDB，在创建外键时，要求父表必须有对应的索引，子表在创建时也会自动创建对应的索引。

主键和索引

InnoDB的数据文件是以聚簇索引的形式保存，这个聚簇索引也被称为主索引且也是InnoDB的主键，InnoDB表第行数据都保存在主索引的叶子节点上。

存储方式

InnoDB存储表和索引有2种存储方式：使用共享表空间存储和使用多表空间存储。

MEMORY

MEMORY存储引擎使用存在于内存中的内容来创建表。

每个MEMORY表对应一个磁盘文件，格式为.frm；由于存储在内存中，MEMORY类型的表访问速度非常快，一旦服务关闭，表中数据则丢失。

MERGE

MERGE存储引擎被称为MRG_MyISAM，是一组MyISAM表的组合。这些MyISAM表的结构必须完全相同，Merge表本身并没有数据，对MERGE类型的表可能进行查、改、删操作。

2020-10-19