【Mysql上分之路】第九篇：Mysql存储引擎

MySQL存储引擎

数据库管理系统对数据的操作是通过存储引擎实现的，存储引擎就是表的类型，决定了表在计算机中存储方式，不同的存储引擎提供了不同的存储机制、索引技巧，不同的存储引擎还有特定的功能

存储引擎分类

MySQL提供了多个不同的存储引擎针对不同的需要进行选择，MySQL8.0支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等

SHOW ENGINES

可以通过SHOW ENGINES语句来查看系统所支持的存储引擎类型

存储引擎物理存储

InnoDB

InnoDB支持事务、行级锁和外键，使用InnoDB存储引擎的数据表，一个数据表对应着两个文件，分别为：.frm、.ibd

• .frm：存储表结构信息
• .ibd：存储表中数据

MERGE

MERGE存储引擎的数据表其实是一个逻辑结构，代表着由一组结构完全相同的MyISAM数据表所构成的集合

MERGE存储引擎的数据表除了拥有存储表结构定义的.frm文件以外，还有一个扩展名为.mgr的文件，这个文件里不保存数据，保存的是数据的来源地，即是由一份由多个MyISAM数据表的名单构成的MERGE数据表

MyISAM

MyISAM 存储引擎的数据表在数据库目录里使用 3 个文件来代表，这些文件的基本名与数据表的名字相同，扩展名则表明了文件的具体用途

• .frm：表结构定义文件，存放着该数据表的结构定义
• .MYD：MY Data的缩写 ，数据文件，存放着该数据表中各个行的数据
• .MYI：MY Index的缩写 ，索引文件，存放着该数据表的全部索引信息

MEMORY

Memory 存储引擎的数据表是创建在内存中的数据表，MySQL 服务器把 Memory 数据表的数据和索引都存放在了内存中而不是硬盘上；除了相应的.frm表结构文件之外，Memory引擎表在文件系统里没有相应的代表文件

Blackhole

由于在 BLACKHOLE 存储引擎的数据表中写入任何数据都会消失，所以除了 .frm 文件，BLACKHOLE 引擎表没有其他相应的代表文件

Archive

Archive 存储引擎的数据表除了拥有 .frm 表结构定义文件外，还有一个扩展名为 .arz 的数据文件，用来存储历史归档数据。执行优化操作时可能还会出现一个扩展名为 .arn 的文件

CSV

CSV 引擎表也会包含一个 .frm 表结构定义文件，此外还会创建一个扩展名为 .CSV 的数据文件。这个文件是 CSV 格式的文本文件，用来保存表中的实际数据

• .CSV 文件可以直接在 Excel 中打开，或者是使用其它文件编辑工具查看。另外，还有一个同名的元信息文件，文件扩展名为 .CSM，用来保存表的状态及表中保存的数据量
• CSV 存储引擎基于 CSV 格式文件存储数据，由于自身文件格式的原因，所有列必须强制指定 NOT NULL

InnoDB存储引擎

InnoDB是目前最重要、使用最广泛的存储引擎，是因为提供外键约束以及事务处理能力的存储引擎

InnoDB存储引擎的优势

支持事务安装

InnoDB支持事务是InnoDB成为主流存储引擎的主要原因；InnoDB实现了SQL标准的4个隔离级别（Read uncommitted、Read commited、Repeatable Read、Serialiable）

在innodb存储引擎中，repeatable read是默认的事务隔离级别，同时该引擎的实现基于多版本的并发控制协议-MVCC(Multi-version Concurrency Control)，解决了幻读问题，脏读和不可重复读也是不存在

灾难恢复性好

InnoDB通过commit、rollback、crash-recovery来保障数据的安全

crash-recovery 就是指如果服务器因为硬件或软件的问题而崩溃，不管当时数据是怎样的状态，在重启 MySQL 后，InnoDB 都会自动恢复到发生崩溃之前的状态，并回到用户离开的地方

使用行级锁

InnoDB存储引擎实现了行锁，行锁机制是通过索引来完成的，行锁定机制也为 InnoDB 在承受高并发压力的环境下增强了不小的竞争力

实现缓冲处理

InnoDB存储引擎提供了专门的缓存池，实现了缓冲管理，不仅能缓冲索引也能缓冲数据，重用的数据直接从内存中处理，比磁盘获取数据处理更快

InnoDB的表和索引在一个逻辑空间中，表空间可以包含数个文件

支持外键

InnoDB支持外键约束，检查外键、插入、更新和删除、以确保数据的完整性

在存储表中数据时每张表的存储都按主键顺序存放，如果没有显示地在定义表时指定主键，InnoDB会为每一行生成一个6个字节的ROWID并作为主键

适合需要大型数据库的网站

InnoDB 是为处理巨大数据量时的最大性能设计，它的 CPU 效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的

物理存储

InnoDB的物理存储结构分为两部分：数据文件、日志文件

使用InnoDB是，MySQL会在数据目录Data下创建一个名为ibdata1的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的日志文件

数据文件

数据文件用来存放数据表中的数据和所有的索引数据，包括主键和其他普通索引

InnoDB存储的数据采用表空间（Tablespace）进行存放设计，表空间是用来存放MySQL系统相关信息的一个特殊共享表空间

InnoDB的表空间可以两种形式：共享表空间、独立表空间

• 共享表空间：表数据和索引都在同一个表空间，默认的表空间文件就是上面提到的ibdata1文件
• 独立表空间：每个表的数据和索引被存放在一个单独的.ibd文件中

可以通过命令show variables like ‘innodb_file_per_table%’查看mysql是否使用独立表空间

innodb_file_per_table：该字段值为ON时表示开启独立文件，InnoDB表的数据和索引都会以单独的形式存放，值为OFF时，InnoDB表的数据和索引都存放在一个表空间中，可以通过设置该参数的值来决定是否使用独立表空间

共享表空间

共享表空间的数据文件可以设置为固定大小和可自动扩展大小两种形式

• 自动扩展形式的文件可以设置文件的最大大小和每次扩展量
• 在创建自动扩展的数据文件时，最好加上最大尺寸，一个原因是文件系统本身有一定的大小规则，还有一个原因是方便自身维护
• 只有共享表空间快用完的时候，必须要为其增减数据文件

可以通过show variables like ‘innodb_data_file_path%’查看当前共享表空间文件的路径、大小和自动化策略

innodb_data_file_path：该参数负责定义共享表空间的路径、初始化大小、自动扩展策略

用户也可以通过该参数innodb_data_file_path指定共享表空间文件

innodb_data_file_path=datafile_spec1[;datafile_spec2]...
datafile_spec1 格式为表空间文件路径:大小:属性，还可以指定多个文件组成一个表空间，同时指定文件的属性
如：innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

这里将 /db/ibdata1 和 /dr2/db/ibdata2 两个文件用来组成表空间。若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能

注意：innodb在创建新数据文件时不会创建目录，若指定目录存在，新数据文件也无法启动并且报错，除此之外，给innodb的共享表空间增减数据文件之后，必须要重启数据库之后才能生效

独立表空间

独立表空间的命名规则为：表名.ibd

• 通过设置参数innodb_file_per_table参数，可以将每个基于innodb存储引擎的表产生一个独立表空间
• SET GLOBAL innodb_file_per_table=1，表示使用独立表空间

注意：单独的表空间文件只存储该表的数据、索引和缓冲等信息，无论是使用共享表空间还是独立表空间来存放表，共享表空间都是必须存在的

日志文件

InnoDB存储引擎的数据目录下两个ib_logfile0和ib_logfile1的文件，称为存储引擎的重做日志文件

InnoDB可以通过重做日志文件将数据库宕机时已经完成但还没有来得及将数据写入磁盘的事务恢复，也能将所有完成并写入磁盘的未完成事务回滚，并且将数据还原，以此来保证数据的完整性

每个InnoDB引擎至少有一个重做日志文件组（Group），每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1

如果数据库中InnoDB的表，不能删除InnoDB的日志文件，会造成数据库的Crash

• 数据库Crash：指的是数据库不工作或停止响应、进程中断等情况

MySQL 启动参数文件设置中，InnoDB 的所有参数基本上都带有前缀“innodb_”，不论是 InnoDB 数据还是和日志相关，或者是其他一些性能，事务等等相关的参数都是一样

下面是影响重做日志文件的参数：

• innodb_log_file_size：指定每个重做日志的大小。
• innodb_log_files_in_group：指定日志文件组中重做日志文件的数量，默认为 1。
• innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为 1。
• innodb_log_group_home_dir：指定日志文件组所在路径，默认为./

MyISAM存储引擎

MyISAM是基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一；MyISAM拥有较高的插入、查询速度，但是不支持事务、也不支持外键；对事务完整性没有要求或者以插入、查询为主的应用都可以MyISAM引擎来创建

MySQL的特性

• ①大文件在支持大文件的文件系统和操作系统上被支持
• ②每个MyISAM表最大索引数是64，这可以通过重新编译来改变，每个索引最大的列数是16个。
• ③数据文件和索引文件可以放在不同的目录
• ④NULL值被允许在索引的列中，这个值占每个键的0~1字节
• ⑤BLOB和TEXT列可以被索引

物理存储

每个基于MyISAM引擎创建的表在磁盘上存储成3个文件，其文件名和表名相同，但是扩展名不一样分别为：

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

数据文件和索引文件可以分布在不同的目录，平均分布I/O，获得更快的速度；要指定索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，也就是说不同MyISAM表的索引文件和数据文件可以放置到不同路径下。文件路径需要绝对路径，并且具有访问权限

MyISAM 的每一张表数据都存放在后缀名为 .MYD 的文件中，但是每个文件的存放格式可能并不完全一样；MyISAM的表支持3中不同的存储格式：静态(固定长度)表、动态表、压缩表

静态表

静态表是默认的存储格式，静态表中字段都是非变长字段，这样每个记录都是固定长度，这种存储方式的优点是存储非常快，容易缓存，出现故障容易恢复；缺点是占用的空间比动态表多；静态表的数据在存储时会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉

*如果需要保存的内存后面本来就带有空格，那么返回的时候也会被去掉，因为静态表是默认的存储格式。

动态表

动态表中包含变长字段，记录不是固定长度的，这种存储方式的优点就是占用的空间较少，但是频繁的更新和删除记录会产生碎片，需要定义执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，并且在出现故障时恢复相对吧比较困难

压缩表

压缩表由myisampack工具是基于静态或动态格式表上创建，占据非常小的磁盘空间，因为每个记录是被单独压缩的，所以只有非常小的访问开支

默认存储引擎

查看默认存储引擎

查看默认存储引擎，之前可以通过SHOW ENGINES来查看系统支持的存储引擎，其中有Default关键字的表示为默认存储引擎，但是更快的可以通过查看默认存储引擎的语句来查看

SHOW VARIABLES LIKE 'default_storage_engine%';

修改默认存储引擎

配置文件修改（永久修改）

若将系统默认存储引擎进行永久修改，则需要对my.cnf配置文件修改，修改语句为

default-storage-engine=存储引擎名称

修改完成之后保存配置文件，就对默认存储引擎永久修改

临时修改

临时修改默认的存储引擎通过SET语句

SET default_storage_engine=< 存储引擎名 >

SET只能临时修改默认存储引擎，当客户端重启时默认存储引擎依然是InnoDB

存储引擎的选择

在使用 MySQL 数据库管理系统时，选择一个合适的存储引擎是一个非常复杂的问题。不同的存储引擎都有各自的特性、优势和使用的场合，正确的选择存储引擎可以提高应用的使用效率

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

MyISAM

MyISAM 存储引擎不支持事务和外键，所以访问速度比较快。如果应用主要以读取和写入为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的

MyISAM 是在 Web 数据仓储和其他应用环境下最常使用的存储引擎之一

InnoDB

InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比 MyISAM 存储引擎占用更多的磁盘空间

对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择

InnoDB 存储引擎除了可以有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择

Memory

MEMORY 存储引擎将所有数据保存在 RAM 中，所以该存储引擎的数据访问速度快，但是安全上没有保障

MEMORY 对表的大小有限制，太大的表无法缓存在内存中。由于使用 MEMORY 存储引擎没有安全保障，所以要确保数据库异常终止后表中的数据可以恢复

如果应用中涉及数据比较少，且需要进行快速访问，则适合使用 MEMORY 存储引擎

总结

数据库管理系统对数据的操作是通过存储引擎实现的，存储引擎就是表的类型，决定了表在计算机中存储方式，不同的存储引擎提供了不同的存储机制、索引技巧，不同的存储引擎还有特定的功能；MySQL提供了多个不同的存储引擎针对不同的需要进行选择，MySQL8.0支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等

赶紧学习起来吧！我是一个正在努力找回自我的人，希望能和一起学习的人成长，有错误的地方请各位大佬帮忙指正，如果觉得有帮助就点个赞当作对我的一个小肯定❤👍，peace&love