MySQL存储索引简单介绍

一.存储引擎简介

MySQL的存储引擎（Storage Engine）是数据库管理系统中负责数据存储、索引管理、事务处理等核心功能的底层组件。它决定了数据如何存储、如何被访问，以及支持哪些特性（如事务、锁机制等）。MySQL采用插件式架构，允许用户根据需求选择不同的存储引擎，这使得它在灵活性上具有显著优势。

二.InnoDB存储引擎

1.简介

InnoDB是一款兼顾高可靠性和高性能的通用存储引擎。在MySQL8.0中默认的存储引擎是 InnoDB ，
使用CREATE TABLE 语句创建表时，在没有修改默认存储引擎或明确指定其他存储引擎时，将创建
一个 InnoDB 的表。

2.InnoDB的特性

1)特性、优势和最佳实践

优势：

• DML操作遵循ACID模型，事务具有提交、回滚和崩溃恢复功能，以保护用户数据。
• 如果发生意外而崩溃，无论当时数据库发生了什么，都不需要在重启数据库后执行任何特殊操作。
  InnoDB 的崩溃恢复功能会自动完成崩溃之前提交的更改，并撤消崩溃前正在进行但未提交的更改，从而允许我们从中断的地放继续执行。
• 支持行级锁，提高了多用户的读取并发性和性能。
• InnoDB 存储引擎维护了一个自己的缓冲池，访问数据时在内存中缓存表和索引数据，对于经常使⽤的数据直接从内存中处理，大幅提升了效率。在专用数据库服务器上，通常会将高达80%的物理内存分配给缓冲池。
• InnoDB表优化了基于主键的查询，每个InnoDB表都有一个称为聚簇索引的主键索引，实现通过最
  少的磁盘I/O完成对主键的查找。
• 为了保持数据完整性， InnoDB 支持 FOREIGN KEY (外键)约束。在进行插⼊、更新和删除数据
  时确保相关表之间的一致性
• 当从表中反复查询相同的行时，自适应哈希索引会自动接管这些查询，此时查询效率和哈希表相
  同

最佳实践

• 为表中最频率查询的列(或多个列)指定主键(或复合主键)，如果没有明显的主键，则创建一个自增的
  列做为主键。
• 从多个表中根据相同的ID查询数据，建议使用表连接。可以在连接的列上定义外键，并在每个表中
  使用相同的数据类型声明这些列。添加外键可以确保被引用的列使用索引，从而提高性能。
• 在每秒提交数百次事务的服务器上，结合存储设备的写入速度，关闭事务的自动提交，通过系统变
  量 autocommit=OFF 设置。
• 把相关的DML操作用 START TRANSACTION 和 COMMIT 语句括在一起，分组为事务一起提交或
  回滚。
• 不要使用 LOCK TABLES 语句，InnoDB可以在不牺牲可靠性和⾼性能的情况下处理多个会话同时
  对⼀个表进行读写操作

2)验证InnoDB是否为默认存储引擎

通过show engines来查看当前的InnoDB是否为默认的搜索引擎（support为default则是默认的）：

如果此时InnoDB不是默认的搜索引擎可以按照下面的操作进行：

通过在命令行指定选项 –default-storageengine=InnoDB 或者在选项文件的 [mysqld] 节点定义 default-storageengine=InnoDB 并重新启动服务器来设置 InnoDB 存储引擎

由于业务实际需要，服务器默认存储引擎不是InnoDB时，想要创建一个InnoDB表，可以在使用CREATE TABLE 语句创建表时明确指定InnoDB存储引擎，当然这样方式也可以指定其他任何支持的存储引擎

如果想测试使用其他存储引擎表中的数据在InnoDB表中的工作情况，在确保不影响原始表的情况下，使用以下方式创建⼀张InnoDB表

创建InnoDB表

在MySQL8.0中表结构的信息也保存在 .ibd ⽂件中，可以使用 ibd2sdi 工具提取表定义的具体信息，使用方法： ibd2sdi --dump-file=t_innodb.txt t_innodb.ibd ，生成的t_innodb.txt 文件中有对应表的具体描述

• sid = Serialized Dictionary Information 序列化字典信息
• 和8.0有所不同的是，在MySQL5.X及以前的版本中使用一个后缀为 .frm 的二进制文件来记录和描述表定义的信息

三.MyISAM存储引擎

使用MyISAM存储引擎的表占用空间很小，但是由于使用表级锁定，所以限制了读/写操作的性能，通
常用于中小型的Web应⽤和数据仓库配置中的只读或主要是读的场景。

1.特性和优势

主要优势：

• MyISAM表的最大行数为 (2³²⁾2 及 (1.844E+19) 行
• 每个MyISAM表最多可以创建64个索引，每个索引最多可以包含16个列；
• ⽀持并发插入
• 通过 CREATE table 创建表时，指定 DATA DIRECTORY=PATH 和 INDEX DIRECTORY=PATH 将数据文件和索引文件放在不同设备的不同目录中，从而提高访问速度
• BLOB 和 TEXT 数据类型的列也可以被索引
• 在索引列中允许使用NULL值
• 如果mysqld启动时设置了 myisam_recover_options 系统变量，那么MyISAM表在打开时进行会自查，如果上一次表没有正确关闭将会修复；
• 表中 VARCHAR 和 CHAR 列的长度总和最多可达64KB。
• UNIQUE 约束的长度不受限制

2.创建MyISAM表

在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=MyISAM：

创建 MyISAM 表会根据表名生成三个不同后缀名文件，分别是以 .MYD ( MYData )为后缀的数据
文件，以 .MYI ( MYIndex )为后缀的索引文件，以 .sdi 为后缀的表信息描述⽂件(JSON格式)

3.MyISAM 表存储格式

• MyISAM 表支持三种不同的存储格式，其中 FIXED 静态(固定)格式和 DYNAMIC 动态格式，根据使用的列类型自动选择，第三种是压缩格式，只能使用 myisampack 实用程序生成并且是只读格式。
• 当表中没有 BLOB 或 TEXT 数据类型的列，在使用 CREATE TABLE 或 ALTER TABLE 语句创建或修改表时，可以结合 ROW_FORMAT 表选项将表格式设置为 FIXED 或 DYNAMIC 。
• 使用myisamchk 实用工具对已压缩的MyISAM进行解压操作， myisamchk --unpack 。

1)静态格式(Fixed-Length)表

静态格式是 MyISAM 表的默认格式，当表不包含可变长度的列 ( VARCHAR、VARBINARY 、
BLOB 或 TEXT ) 时使用，每行都使用固定数量的字节存储。

• MyISAM 的三种存储格式中，静态格式是最简单和最安全的（最不容易损坏），同时也是最快的磁盘格式，因为每行的长度固定，根据索引中的行号乘以行长度就可以计算出行位置，此外，每次读取固定数量的行也非常的高效。
• 静态格式表具有以下特点：
  • CHAR 和 VARCHAR 类型的列用空格填充到指定的列宽； BINARY 和 VARBINARY 类型的列
    用 0x00 字节填充到列宽
  • 每个允许为NULL的列，都用一个 1 BIT 的额外空间记录当前列是否为空
  • 速度非常快，且易于缓存
  • 崩溃后易于重建，因为行都位于固定位置
  • 通常需要比动态格式表更多的磁盘空间

2)动态格式表

当表中包含可变长度列( VARCHAR 、 VARBINARY 、 BLOB 或 TEXT )或者在创建表时使用
ROW_FORMAT=DYNAMIC 选项，则表格式为动态存储格式。

动态格式表具有以下特点：
列类型是字符串，且长度大于等于4，长度都是动态的

• 每一行都有一个标志来指示有多长，当因更新操作而变得更长时，数据可能存储在不连续的空间，可以使用 OPTIMIZE TABLE table_name 语句或 myisamchk -r 对表进行碎骗整理
• 每个允许为NULL的列，都用一个 1 BIT 的额外空间记录当前列是否为空
• 每行前面都有一个 bitmap (位图)，⽤来记录包含空字符串或0的列，如果字符串类型的列长度为零，或者数字列的值0，则在位图中标记并且不会保存到磁盘
• 通常磁盘空间占用比固定长度表要少很多；
• 每行都单独压缩，每列都可能用单独的方式进行压缩

常用的压缩方式：

• 如果数值列的值为0，无论原始数据类型是哪种都用一个 BIT 类型存储

• 如果整数列中的值范围较小，则尽可能使小的类型存储该列，比如：列中的值范围在 -128 到 127 之间，即使原始类型为 bigint (8bytes)，也使用TINYINT (1 byte) 类型存储
  

• 如果列中只有一小组可能出现的值，则数据类型转换为 ENUM

3) 压缩格式表

压缩存储格式是使用myisampack工具生成的只读格式数据表，压缩表可以用myisamchk解压缩。

压缩格式表具有以下特点：

• 压缩表占用很少的磁盘空间，最大限度地减少了磁盘使用
• 可以用于固定长度或动态长度行
• 压缩表是只读的，因此不能在表中更新或添加数据

四.MEMORY存储引擎

使用MEMORY存储引擎(以前称为HEAP)创建的表，内容存储在内存中。当服务器由于硬件问题、
断电或其他原因崩溃时数据会丢失，因此这些表仅用作临时工作区或从其他表中提取数据的只读缓
存

使用场景

1. 涉及瞬时、非关键数据的操作，例如会话管理或需要缓存的数据，当服务器停止或重新启动时，
   MEMORY 表中的数据会丢失；
2. 用于快速访问和低延时，数据量可以完全放在物理内存中，不使用虚拟内存；
3. 只读或以读为主的数据访问场景(有限的更新)

1.特性

使用固定长度的存储格式，可变长度类型，例如不能包含 BLOB 或 TEXT 列，支AUTO_INCREMENT 的列VARCHAR 使用固定长度存储；非 TEMPORARY MEMORY 表在所有客⼾端之间共享；

支持 HASH 索引(默认)和 BTREE 索引，不支持表分区，由于使用单线程，在高负载的场景下可能会涉及严重的锁竞争，特别是在多个客户端并发执 行更新操作的情况下，性能并不一定会比 InnoDB 更快。

创建MEMORY表


由于数据在内存中保存，所以 MEMORY 表不会在磁盘上⽣成数据⽂件，表结构保存数据字典和 .sdi 文件中，所以创建表的时候可以添加数据：

启动时填充 MEMORY 表的内容，可以使用 init_file 系统变量指定一个SQL文件，文件中可以编写用于初始化数据的SQL语句，例如： INSERT INTO … SELECT 或 LOAD DATA

2. 内存管理

删除单行数据，不会回收内存，只有删除整个表时才会回收内存。当不需要内存表的内容时，要释放该表所使用的所有内存，可以执行 DELETE 或 TRUNCATE table 删除所有行，或者使用DROP table 删除表。如果要释放被删除行所使用的内存，使⽤ ALTER TABLE ENGINE=MEMORY 命令强制重建表。

表中一行数据所需的内存使用以下表达式计算：

ALIGN() 函数的作用：使行长度为 char 类型大小的精确倍数。 sizeof (char*) 在32位
机器上是4，在64位机器上是8。

max_heap_table_size 系统变量设置了内存表的最大的大小限制，默认为16MB，要控制单个表的最大的大小，在创建每个表之前设置该变量的 session 值。(不要改变全局的max_heap_table_size 值，除非要明确设置所有客户端创建的内存表)，下面的示例创建了两个内存表，最大的大小分别为 1MB 和 2MB ：

每次重启服务器内存表中的数据将被清空，内存表中的数据永远不会写入磁盘。

五.CSV存储引擎

CSV是逗号分隔值（Comma-Separated Values)的缩写，以纯文本形式存储表格数据。

1.创建CSV表

在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=CSV ，创建 CSV 表时，服务器会创建三个文件，其中以 .CSV 为扩展名的文件用于以逗号分隔值的格式保存数据；扩展名为 .CSM 的文件，用于存储表的状态和表中的行数；以 .sdi 为后缀的表信息描述文件(JSON格式)

2.CSV表中的数据

向表中插入数据：

由于 .CSV 是文件格式的文件，我们在命令行查看文件内容如下：

3.CSV 表的修复和检查

CSV 存储引擎支持使用 CHECK TABLE 和 REPAIR TABLE 语句来验证或修复损坏的 CSV表：

当用文本编辑器打开t_csv.CSV文件，并写入一条新数据，如下所示：

再次执行查询语句发现没有第三条数据，这是由于.CSM文件中并没有记录新增的行，可以使用
REPAIR TABLE 语句修改表内容和CSM文件

检查损坏的表时会返回错误，例如把t_csv.CSV文件的内容进行修改:

运行检查语句提示错误：

运行修复语句后，错误的数据被删除：

注意：
在修复期时，只有从 CSV 文件第一行到第一个损坏行的行被复制到新表中，从第一个损坏的行到表
末尾的所有其他行都会被删除，即使是有效数据

CSV表限制

• CSV 存储引擎不支持索引；
• CSV 存储引擎不支持分区；
• 使用 CSV 存储引擎创建的表中的所有列都必须为 NOT NULL 。

六.ARCHIVE存储引擎

使用ARCHIVE 存储引擎创建的表，存储大量不被索引的数据且占用空间很小，一般用于归档数据的
存储

1.特性

支持 INSERT , REPLACE 和 SELECT ，但不支持 DELETE 和 UPDATE ； 支持列的 AUTO_INCREMENT 属性，该列可以有唯一约束，且手动指定的值不能小于该列的最大值；

不支持索引，在任何列上尝试建立索引都会报错； 插入时，数据将被压缩， ARCHIVE 引擎使用 zlib 无损数据压缩； INSERT 语句只是将数据写入压缩缓冲区并且根据需要刷新到磁盘，当执行 SELECT 时会强制刷新缓冲区；

检索时，按需要进行解压缩，不支持行缓存； SELECT操作执行全表扫描，找出当前查询的行，并读取行数；使用行级锁定 不支持表分区。

2.创建ARCHIVE表

在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=ARCHIVE

创建 ARCHIVE 表会根据表名生成两个不同后缀名文件，分别是以 .ARZ 为后缀的数据文件，以 .sdi 为后缀的表信息描述文件(JSON格式)， .ARN 文件在优化操作期间可能会出现

七.BLACKHOLE存储引擎

BLACKHOLE 存储引擎就像⼀个"黑洞"，接受数据，但不存储数据，检索时总是返回一个空结果

1.BLACKHOLE存储引擎的特性

1. BLACKHOLE 表不会存储任何数据，但如果启用了基于语句的⼆进制日志记录，则会记录 SQL 语句并将其复制到副本服务器
2. 支持索引
3. 不支持分区

BLACKHOLE存储引擎的用途

1. 验证转储文件语法
2. 通过比较启用和不启用二进制日志记录的性能，测量二进制日志记录的开销
3. 本质上是一个 "无操作"的存储引擎，可用于查找与存储引擎本⾝无关的性能瓶颈

2.创建BLACKHOLE表

在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=BLACKHOLE ，创建 BLACKHOLE 表时，服务器会在全局数据字典中创建表定义并生成 .sdi 为后缀的表信息描述文件。

.

八.MERGE存储引擎

MERGE存储引擎，也称为MRG_MyISAM引擎，允许MySQL DBA或开发人员在逻辑上将一系列相同的MyISAM表分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Bases)环境，如数据仓库。这里的相同表示所有表中的列都有相同的数据类型和索引信息。示意图如下：

1.创建MERGE表

在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=MERGE 。

创建MERGE表必须指定 UNION=(list-of-tables) 选项，表示要使用哪些MyISAM表；还可以通过指定 INSERT_METHOD 选项来控制如何对MERGE表进行插入操作， FIRST 或 LAST 值分别表示在第一个或最后一个基础表中进行插入，如果没有指定 INSERT_METHOD 选项，或者指定它的值为 NO ，那么在 MERGE 表中执行插入将会报错；

基础表：

插入数据

创建 MERGE 表时，会在磁盘上创建一个 .mrg 文件，其中包含了基础MyISAM表的名称。MERGE
的表格式存储在MySQL数据字典中； .sdi 为后缀的表信息描述文件；

2.操作MERGE表

础表中的 id 列作为 PRIMARY KEY 索引，但在MERGE表中并不作为主键，但是可以被索引。

因为 MERGE 表不能对基础表集强制唯一性，类似的，基础表中具有 UNIQUE 索引的列可以在MERGE 表中被索引，但不能作为唯一约束，查询示例：

要将MERGE表重新映射到不同的MyISAM基础表集合，您可以使用以下方法之一:

1. 删除MERGE表并重新创建；
2. 使用 ALTER TABLE tbl_name UNION=(…) 修改基础表的集合；ALTER TABLE…UNION=() 列表为空时，表示删除所有基础表
3. 使用 DROP TABLE 只会删除MERGE表定义，基础MyISAM表不受影响。

九.FEDERATED存储引擎

默认不支持，可以在启动时通过命令行选项 --federated 或选项文件的配置来启用

允许访问远程MySQL数据库中的数据，在不使用复制或集群技术的情况下， FEDERATED 存储引
擎可以实现对远程MySQL数据库中数据的访问，以多个物理服务器为基础创建一个逻辑数据库，当
查询 FEDERATED 表时，将会从远程数据库获取数据，非常适合分布式或数据集市环境。如图所
示：

1.创建FEDERATED表

本地配置文件中的 [mysqld] 节点下加入 federated=1 来启用 FEDERATED 引擎，之后重启MySQL服务

重启本地数据库服务并查看 FEDERATED 引擎是否启用

• 在远程服务器上为用户加入远程访问权限。
• 在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=FEDERATED
• 创建 FEDERATED 表时，本地的表定义与远程服务器的表定义相同，但数据存储在远程服务器上
• 本地表定义中使用 CONNECTION 连接字符串指向远程表的连接字符串

连接字符串的格式：
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
scheme ：连接协议，目前只支持mysql
user_name：用于连接远程服务器的用户名，注意：这个用户在远程服务器已创建，并授予了相应
的操作权限；
password ：用户的密码
host_name ：远程服务器的IP地址
port_num ：远程服务器MySQL服务的端口号
db_name ：远程表所在的数据库名
tbl_name ：远程表名，本地表名与远程表名可以不同，但建议保持⼀致。

• 不会生成数据文件，表定义在数据字典中，⽣成 .sdi 为后缀的表信息描述⽂件(JSON格式)
• 建表成功后，对本地表的增删改查和操作远程表⼀样

FEDERATED表注意事项：

1. 远程服务器必须是MySQL服务器
2. 使⽤ CONNECTION 字符串时，密码中不能使⽤"@"字符
3. DROP TABLE 只删除本地表，不删除远程表
4. 不支持事务

十.其他索引

1.EXAMPLE存储引擎

EXAMPLE 存储引擎什么也不做，它的存在目的是为开发人员说明如何开始编写⼀个新的存储引
擎，是MySQL源代码中的一个示例。

不支持索引和表分区，当创建一个 EXAMPLE 表时，不会在磁盘上创建任何⽂件，表中不能存储任何数据，查询时始终返回一个空结果。

2.其他存储引擎

其他存储引擎或自定义存储引擎可以从实现了Custom storage Engine接口的第三方和社区获取Custom storage Engine接口是MySQL提供⼀的套AP。

不同存储引擎的特性

创建⼀个数据库，并分别创建使用InnoDB引擎和MyISAM引擎的表