MySQL 8 存储引擎详解

在上一章中，我们学习了如何设置新系统、数据字典和系统数据库。提供了有关缓存技术、全球化、不同类型的组件和插件配置的详细信息，以及对管理非常重要的几种类型的日志文件

本章提供了有关 MySQL 8 存储引擎的详细信息。它详细解释了InnoDB存储引擎及其功能，还提供了定制存储引擎创建的实用指南，以及如何使其可插入，以便安装在 MySQL 8 中。我们将在本章中介绍的主题如下：

存储引擎是 MySQL 组件，用于处理不同类型表中使用的 SQL 操作。MySQL 存储引擎设计用于在不同类型的环境中管理不同类型的任务。了解并选择最适合系统或应用程序需求的存储引擎非常重要。在以下部分中，我们将详细了解存储引擎的类型、默认存储引擎以及自定义存储引擎的创建。

让我们来看一下，为什么存储引擎是数据库（包括 MySQL 8）中非常重要的组件。存储引擎与数据库引擎一起在不同的环境中执行各种类型的任务。它们以语句的形式对数据库中的数据执行创建、读取、更新和删除操作。当您在 CREATETABLE 语句中提供ENGINE参数时，它看起来非常简单，但是对于通过 SQL 语句发送的每个请求，都有大量操作要对数据执行的配置。它不仅仅是持久化数据——该引擎负责存储限制、事务、锁定粒度/级别、多版本并发控制、地理空间数据类型、地理空间索引、B 树索引、T 树索引、Hash索引、全文搜索索引、，聚集索引、数据缓存、索引缓存、压缩数据、加密数据、群集数据库、复制、外键、备份、查询缓存以及数据字典的更新统计信息。

MySQL 存储引擎的可插拔体系结构允许数据库专业人员为任何特定应用程序所需的专门化选择任何存储引擎。MySQL 存储引擎体系结构提供了一个简单的应用程序模型和 API，其一致性将数据库管理员和应用程序程序员与存储级别的所有底层实现细节隔离开来。因此，应用程序总是在不同存储引擎的不同功能之上工作。它提供了所有底层存储引擎通用的标准管理和支持服务。

存储引擎对在物理服务器级别保留的数据执行活动。这种模块化和高效的体系结构为任何特定应用程序的特定需求提供了解决方案，如事务处理、高可用性情况或数据仓库，同时具有来自底层存储引擎的独立接口和服务的优势。

数据库管理员和应用程序程序员通过存储引擎上的连接器 API 和服务与 MySQL 数据库交互。MySQL 服务器体系结构通过提供一致且适用于所有存储引擎的易于使用的 API，将应用程序与存储引擎的详细级别复杂性隔离开来。如果应用程序需要对底层存储引擎进行更改，或者如果添加了一个或多个存储引擎以支持应用程序的需要，则无需进行重大编码或流程更改即可使其正常工作。

现在，我们知道了存储引擎的重要性，并从 MySQL 8 可用的大量存储引擎中确定要使用哪些存储引擎。让我们看看有什么可用的，有哪些规格。InnoDB 是在您开始考虑存储引擎时第一个进入您脑海的名字，对吗？

InnoDB 是 MySQL 8 中默认且最通用的存储引擎，Oracle 建议将其用于表以及特殊用例。MySQL 服务器有一个可插拔的存储引擎体系结构，支持从已经运行的 MySQL 服务器加载和卸载存储引擎。

在 MySQL 8 中，识别服务器支持哪些存储引擎非常容易。我们只需要转到 MySQL shell 或提示符并使用SHOW ENGINES语句。当出现提示时点击该语句，结果将是引擎列表，其中包含一些列，如引擎、支持、事务、保存点和注释

“支持”列中的值“默认”、“是”和“否”表示存储引擎可用且当前设置为默认存储

InnoDB是 MySQL 8 中默认的、最通用的存储引擎，提供高可靠性和高性能。

如果您没有配置不同的默认存储引擎，那么发出不带ENGINE = clause的 SQL 语句CREATE TABLE将创建一个表，其中存储引擎InnoDB作为 MySQL 8 中的默认引擎。

InnoDB存储引擎提供的特性和优势将在InnoDB 存储引擎一节稍后进行解释。

MySQL 5.1 及所有更高版本和 MySQL 8 中的存储引擎体系结构都利用了灵活的存储引擎体系结构。

存储引擎可插拔体系结构提供了创建和添加新存储引擎的能力，而无需重新编译服务器，直接添加到正在运行的 MySQL 服务器。该体系结构使开发和部署新的存储引擎到 MySQL 8 变得非常容易。

在即将到来的创建自定义存储引擎一节中，我们将使用 MySQL 存储引擎架构的可插拔特性开发一个新的存储引擎

在本节中，我们将更深入地了解 MySQL 8 支持的广泛使用的存储引擎。但在检查它们之前，让我们先看看存储引擎体系结构是如何使其可插拔的，并提供了在同一架构或服务器中使用多个存储引擎的灵活性。

以下是 MySQL 8 支持的存储引擎列表：

  • InnoDB：MySQL 8 的默认存储引擎。它是一个兼容ACID的（事务安全的）存储引擎，具有提交、回滚和崩溃恢复功能，用于保护用户数据，并具有维护数据完整性的referential-integrity约束，等等。
  • MyISAM：具有占用空间小的表的存储引擎。它具有表级锁定，因此主要用于只读或以读为主的数据工作负载，如数据仓库和 web 配置。
  • Memory：以前称为HEAP引擎的存储引擎。它将数据保存在 RAM 中，从而提供更快的数据访问，主要用于非关键数据环境的快速查找
  • CSV：在文本文件和表格中，表格作为逗号分隔值的存储引擎。它们没有索引，主要用于导入和转储CSV格式的数据
  • Archive：存储引擎包含紧凑的未索引表，用于存储和检索大量历史、归档或安全审计数据。
  • Blackhole：具有可用于复制配置的表的存储引擎。查询总是返回一个空集。DMLSQL 语句被发送到从属服务器。它接受数据，但不存储数据，例如在 Unix/dev/null设备中使用。
  • Merge：存储引擎提供对一系列类似MyISAM表进行逻辑分组的能力，并将它们作为一个对象而不是单独的表引用。
  • Federated：可以将多个单独的物理 MySQL 服务器链接到一个逻辑数据库的存储引擎。它非常适合数据集市或分布式环境
  • Example：只作为stub工作的存储引擎。它主要用于说明如何开始在 MySQL 源代码中编写新的存储引擎的开发人员。

MySQL does not restrict using the same storage engine for an entire server or schema; instead, specifying the engine at table level makes it flexible based on the type of data and the use case of the application.

MySQL 服务器使用可插拔存储引擎体系结构，支持从已经运行的 MySQL 服务器加载和卸载存储引擎：

  • 插入存储引擎：在服务器中使用存储引擎之前，必须使用INSTALL PLUGINSQL 语句将存储引擎插件共享库加载到 MySQL 中。如果您创建了一个名为MyExample的MYEXAMPLE引擎插件，而共享库名为MyExample.so，则需要使用以下语句加载它们：
 mysql> INSTALL PLUGIN MyExample SONAME 'MyExample.so';

要安装存储引擎，发出上述语句的用户必须拥有mysql.plugin表的INSERT权限，并且插件文件必须存在于 MySQL 插件目录中。共享库也必须存在于plugin_dir变量中给定的 MySQL 服务器插件目录中。

  • 拔下存储引擎：拔下存储引擎之前，请确保没有表正在使用存储引擎。如果已拔下存储引擎的插头并且任何现有表都需要该引擎，则这些表将无法访问，并且仅在适用时存在于磁盘上。如果拔掉名为MyExample的MYEXAMPLE引擎插件，则执行以下语句拔掉存储引擎：
 mysql> UNINSTALL PLUGIN MyExample ;

MySQL 可插拔存储引擎负责对实际数据执行 I/O 操作，并满足特定的应用程序需求，包括在需要时启用和强制实施所需的功能。使用特定或单个存储引擎更有可能提高效率和数据库性能，因为该引擎只启用特定应用程序所需的功能，并减少数据库的系统开销。

存储引擎支持以下独特的基础结构组件或密钥：

  • 并发：一些应用程序比其他应用程序更需要细粒度锁级别（如行级锁）。选择正确/错误的锁定策略可能会影响锁的总体性能和开销，这还包括多版本并发控制或快照读取功能。
  • 事务支持：存在定义非常明确的需求，例如ACID合规性，如果应用程序需要事务，则更多。
  • 引用完整性：如果需要，服务器可以使用DDL定义的外键强制关系数据库引用完整性。。
  • 物理存储：包括表和索引的页面大小，也包括用于在物理磁盘上存储数据的格式。
  • 索引支持：这包括基于应用需求的索引策略，因为每个存储引擎都有自己的索引方法。
  • 内存缓存：基于应用需求的缓存策略，因为每个存储引擎都有自己的缓存方法以及所有存储引擎的通用内存缓存。
  • 性能帮助：这涉及批量插入处理、数据库检查指向、用于并行操作的多个 I/O 线程、线程并发等
  • 其他目标功能：这可能包括对某些数据操作、地理空间操作和其他类似功能的安全限制的支持。

前面的每个基础架构组件都是为支持特定应用程序需要的一组特定功能而设计的，因此非常仔细地了解应用程序需求并选择正确的存储引擎非常重要，因为它可能会影响整个系统的效率和性能。

当您使用CREATE TABLE语句创建新表时，您可以使用ENGINEtable 选项指定要用于该表的引擎。如果未指定ENGINE表选项，则将使用默认存储引擎。InnoDB是 MySQL 8.0 的默认引擎。您还可以使用ALTER TABLE语句将表从一个存储引擎转换为另一个存储引擎，如下例所示：

CREATE TABLE table1 (i1 INT) ENGINE = INNODB;
CREATE TABLE table3 (i3 INT) ENGINE = MEMORY;
ALTER TABLE table3 ENGINE = InnoDB;

通过设置default_storage_engine变量，可以为当前会话设置默认存储引擎，如下例所示：

SET default_storage_engine=MEMORY;

使用CREATE TEMPORARY TABLE的TEMPORARY表的默认存储引擎可以通过在启动或运行时设置default_tmp_storage_engine变量来单独设置。

MyISAM存储引擎使用占用空间小的表。它实现了表级锁定，因此主要用于只读或以读为主的数据工作负载，如数据仓库和 web 配置中。每个MyISAM表在磁盘上存储有两个文件。文件名以表名及其扩展名类型开头，一个以.MYD扩展名表示数据文件，另一个以.MYI扩展名表示索引文件。

对于MyISAM发动机，有几个mysqld指定的启动选项可以改变MyISAM表的行为；例如：

--myisam-recover-options=mode

此选项将在MyISAM中设置崩溃表的自动恢复模式

MyISAM、B-Tree索引中键所需的空格由MyISAM表使用，String索引中使用空格压缩。如果字符串是索引的第一部分，那么也会进行前缀压缩，这总体上会减小索引文件的大小。如果许多字符串具有相似的前缀，则前缀压缩会有所帮助。通过使用MyISAM表格中的表格选项PACK_KEYS=1，如果有许多具有类似前缀的数字，也可以对这些数字应用前缀压缩。

MySQL 8.0 中的MyISAM表不支持分区。

MyISAM表格的一些重要表格特征如下：

  • 所有存储的数据值都具有低字节一阶，这使得数据独立于机器和操作系统
  • 所有数字键值都以高字节第一顺序存储，这允许更好的索引压缩
  • MyISAM表中有（2322（1.844E+19）行限制
  • MyISAM表中每个表最多只能有 64 个索引
  • MyISAM表列限制为每个索引最多 16 列
  • 如果表在数据文件中间没有任何空闲块，则在 SytT0*中支持并发插入。
  • TEXT和BLOB类型的列也可以在MyISAM中索引
  • 在索引列中，允许使用NULL值
  • 每列可以有不同的字符集
  • 它还支持起始长度为 1 或 2 字节的真正的VARCHAR类型列、具有固定或动态行长度的VARCHAR列的表以及具有任意长度的UNIQUE约束
  • MyISAM表存储格式：MyISAM支持以下三种不同类型的存储格式：
    • Static表：MyISAM存储引擎中表的默认格式，列大小固定
    • Dynamic表：顾名思义，包含可变大小列的格式，包括VARCHAR、BLOB或TEXT
    • Compressed表：保存MyISAM存储引擎表中只读数据和压缩格式的表格式

前两种格式（固定格式和动态格式）是根据所使用的列类型自动选择的。可使用myisampack工具创建压缩格式

  • MyISAM表问题：文件格式已经过广泛测试，但出现了一些导致数据库表损坏的情况。让我们看看这种情况以及恢复这些表的方法。

如果发生以下任何事件，我们可能会得到损坏的表：

  • 如果在写的中间被杀死
  • 如果出现意外的计算机关闭
  • 如果有任何硬件故障
  • 如果 MySQL 服务器和外部程序同时修改表，如myisamchk
  • MySQL 或MyISAM代码有软件缺陷

使用CHECK TABLE语句检查表的运行状况，并尝试使用REPAIR TABLE语句修复任何损坏的MyISAM表

MyISAM表也可能存在一个问题，即未正确关闭表。为了确定表是否正确关闭，每个MyISAM索引文件在标题中保留一个计数器。在以下情况下，计数器可能不正确：

  • 如果复制表格时未发出LOCK TABLES和FLUSH TABLES
  • MySQL 在更新过程中最终关闭前崩溃
  • mysqld正在使用该表，同时被另一程序修改：myisamcheck --recover或myisamchk --update-state

MEMORY存储引擎，以前也称为HEAP引擎，将数据保存在RAM中，从而提供更快的数据访问。它主要用于快速查找非关键数据环境。它使用存储在内存中的内容创建特殊用途的表，但这些数据容易发生崩溃、断电和硬件问题。因此，这些表用于临时工作区，或者可能使用从其他表中提取数据后缓存的只读数据。

您应该选择是否使用 AutoT0}或 Oracle T1}，您应该检查应用程序是否需要重要的、高度可用的或经常更新的数据，并考虑 AUT2 T2 是否更好的选择。NDB Cluster提供与MEMORY发动机相同的功能，但具有更高的性能水平和MEMORY发动机未提供的附加功能。这些措施包括：

  • 通过多线程操作和行级锁定降低客户端之间的争用
  • 具有语句混合的可伸缩性，包括写入
  • 数据持久性；它支持可选的磁盘备份操作
  • 无共享体系结构，提供多个主机操作而无单点故障，使应用程序的可用性达到 99.999%
  • 跨节点的自动数据分发
  • 支持可变长度数据类型，包括BLOB和TEXT

MEMORY表中不支持分区。

性能取决于服务器的繁忙程度，以及在更新处理期间单线程执行和表锁开销的影响。更新处理期间的表锁定导致MEMORY表上多个会话的并发使用速度减慢。

内存表特性：表定义存储在 MySQL 数据字典中，不在磁盘上创建任何文件。以下是表格功能的突出显示：

  • 插入和空间的 100%动态哈希在小块中分配。
  • 不需要额外的密钥空间或溢出区域，也不需要额外的空闲列表空间。通过将行放入链表插入新记录时重用已删除的行。
  • 固定长度行存储格式VARCHAR以固定长度存储。无法存储BLOB或TEXT列。
  • AUTO_INCREMENT支撑立柱。

HASH和BTREE类型中的索引由MEMORY存储引擎支持。MEMORY表每个表最多有 64 个索引，每个索引最多有 16 列，最大键长度为 3072 字节。MEMORY表也可以有non-unique键。

用户创建和临时表：内部临时表由服务器在处理查询时动态创建。两种表的存储转换不同，MEMORY表不进行转换：

  • 当内部临时表变得太大时，服务器会自动将其转换为磁盘存储
  • 服务器从不转换用户创建的MEMORY表

可以使用--init-file选项执行数据加载，如果需要，可以使用来自任何持久性数据源的INSERT INTO ... SELECT或LOAD DATA INFILE语句。

此存储引擎以逗号分隔值的形式在文本文件中存储数据。引擎总是编译到 MySQL 服务器中，可以从 MySQL 发行版的storage/csv目录检查源代码

服务器创建的数据文件以给定的表和扩展名.CSV开始。数据文件是纯文本文件，包含逗号分隔值格式的数据。

MySQL 服务器创建一个对应的元文件和一个CSV表，该表存储关于表的状态和表中存在的行数的信息。图元文件也以表名开头的.CSM扩展名存储。

  • 修复和检查CSV表：存储引擎支持CHECK和REPAIR语句，以验证并可能修复损坏的CSV表。您可以使用CHECK TABLE语句验证或验证该表，并使用REPAIR TABLE语句修复从现有CSV数据文件复制有效行并用新复制/恢复的行替换现有文件的表。

During repair, only rows from the CSV data file to the first damaged row gets copied to the new table or copied data file. The rest of the rows after the damaged row gets removed from the table, including valid rows, so I suggest that you take enough back up of the data file prior to proceeding with the repair.

CSV存储引擎不支持索引或分区，使用CSV存储引擎创建的所有表在所有列上都必须具有NOT NULL属性。

ARCHIVE存储引擎创建专用表，用于以非常小的占用空间存储大量未编制索引的数据。

创建ARCHIVE表时，它以表名开始，以.ARZ扩展名结束。在优化操作期间，可能会出现扩展名为.ARN的文件。

引擎支持The AUTO_INCREMENT列属性。它还支持INSERT、REPLACE、SELECT和BLOB列（空间数据类型除外），但不支持DELETE,、UPDATE、ORDER或BY操作。

ARCHIVE存储引擎不支持分区：

  • 存储：引擎使用zlib进行无损数据压缩，插入时对行进行压缩。支持CHECK TABLE操作。发动机中使用了几种插入方式：
    • INSERT语句将行发送到压缩缓冲区，并根据需要刷新缓冲区。压缩缓冲区中的插入受锁保护，只有在请求SELECT时才会进行刷新。
    • 一旦完成，就可以看到批量缓冲区。只有在同时出现任何其他插入时才能看到。此处在SELECT时不会发生冲洗，除非在加载任何正常插件时
  • 取数：取数后，行按要求解压，不使用任何行缓存。对SELECT操作执行完整的表格扫描：
    • SELECT检查当前可用的行数，并仅读取该行数。它作为一致的读取操作执行。
    • 对于ARCHIVE表，SHOW TABLE STATUS报告的行数始终是准确的。
    • 使用OPTIMIZE TABLE或REPAIR TABLE操作实现更好的压缩。

BLACKHOLE存储引擎就像一个黑洞。它接受数据但不存储数据，查询总是返回空结果。

当您创建BLACKHOLE表时，服务器仅在全局数据字典中添加表定义，并且没有与该表关联的文件。

BLACKHOLE存储引擎支持各种索引，因此可以在表定义中包含。

BLACKHOLE存储引擎不支持分区。

插入到表中不会存储任何数据，但如果对语句启用了二进制日志记录，则会记录语句并将其复制到从属服务器。这种机制可用作过滤器或中继器。

BLACKHOLE存储引擎有以下可能的用途：

  • 转储文件语法验证
  • 通过BLACKHOLE性能比较启用或禁用二进制日志记录的开销测量
  • 它还可用于查找除存储引擎本身之外的任何性能瓶颈

自动递增列：由于引擎是一个不可操作的引擎，它不会递增任何字段值，但它在复制中有影响，这可能非常重要。考虑具有以下条件的场景：

  1. 主服务器有一个带有主键的自动递增字段的BLOCKHOLE表
  2. 从属服务器上存在相同的表，但使用MyISAM引擎
  3. 插入被执行到主服务器的表中，而无需在INSERT语句中设置任何自动增量值或使用SET INSERT_ID语句

在前面的场景中，复制将在具有重复项的主键列上失败。

MERGE存储引擎，也称为MRG_MyISAM引擎，是类似表的集合，可以用作一个表。这里，“相似”表示所有表都具有相似的列数据类型和索引信息。不可能将表与按不同顺序列出的列合并，也不可能在各个列中具有相同的数据类型或按不同顺序进行索引。

以下是不限制合并的表中的差异列表：

  • 各个列和索引的名称可以不同。
  • 表、列和索引之间的注释可以不同。
  • AVG_ROW_LENGTH、MAX_ROWS或PACK_KEYS表格选项可以不同。

当创建一个MERGE表时，MySQL 还会在磁盘上创建一个.MRG文件，并将底层MyISAM表的名称作为一个文件使用。表的格式存储在 MySQL 数据字典中，底层表不需要与MERGE表在同一数据库中。

与MERGE表映射的MyISAM表上的SELECT、UPDATE和DELETE必须具有权限，因此可以使用MERGE表上的SELECT、INSERT、UPDATE和DELETE语句

在MERGE表上执行DROP TABLE语句只会删除MERGE的规范，不会对基础表产生任何影响

Using MERGE tables has the following security issues. If the user has access to the MyISAM table t1, then the user can create the MERGE table m1 that can access t1. Now, if the user's privileges on the table t1 are revoked, the user can still continue accessing table t1 by using table m1.

FEDERATED存储引擎可以将多个单独的物理 MySQL 服务器链接到一个逻辑数据库中，因此它可以让您从远程 MySQL 服务器访问数据，而无需使用复制或群集技术。

当我们查询本地FEDERATED表时，它会自动从远程联邦表中提取数据，并且数据不需要存储在本地表中。

MySQL 服务器默认不支持FEDERATED存储引擎，但使用--federated选项启动服务器将启用FEDERATED引擎选项。

创建FEDERATED表时，表定义与其他表相同，但相关数据的物理存储在远程服务器上处理。FEDERATED表由以下两个要素组成：

  • 一个远程服务器，其数据库表由表定义和相关联的表数据组成。这种类型的表可以是远程服务器支持的任何表，包括MyISAM或InnoDB。
  • 一个本地服务器，其数据库表由与远程服务器上相应表相同的表定义组成。表定义存储在数据字典中，本地服务器上不存储任何关联的数据文件。相反，除了表定义之外，它还保留一个指向远程表本身的连接字符串。

以下是在FEDERATED表上执行 SQL 语句时，本地和远程服务器之间的信息流：

  1. 引擎检查表中的每一列，并生成引用远程表的适当 SQL 语句。
  2. MySQL 客户端 API 用于将 SQL 语句发送到远程服务器。
  3. 该语句由远程服务器处理，相应的结果由本地服务器检索。

EXAMPLE存储引擎只是一个存根引擎，其目的是在 MySQL 源代码中提供示例，帮助开发人员编写新的存储引擎。

要使用EXAMPLE引擎源代码，请查看 MySQL 源代码分发下载的storage/example目录。

如果使用EXAMPLE引擎创建表，则不会创建任何文件。数据不能存储在EXAMPLE引擎中，返回空结果。

EXAMPLE存储引擎不支持索引和分区。

InnoDB是最通用的存储引擎，是 MySQL 8 中的默认引擎，提供高可靠性和高性能

以下是InnoDB存储引擎提供的主要优势：

  • 其DML操作遵循ACID模型，事务具有提交、回滚和崩溃恢复功能，以保护用户数据
  • Oracle-style提供一致读取和行级锁定，提高多用户并发性能
  • 每个InnoDB表都有一个主键索引，称为聚集索引，它排列磁盘上的数据，以优化基于主键的查询，并在主键查找期间最小化 I/O
  • 通过支持外键，可以检查插入、删除和更新，确保不同表之间的一致性，以保持数据完整性

以下是使用InnoDB表的主要好处：

  • 如果服务器因任何硬件或软件问题而崩溃，无论当时服务器中正在处理哪些更改，重新启动服务器后都不需要执行任何特殊操作。它有一个崩溃恢复系统，负责在服务器崩溃期间提交的更改。它将转到这些更改并从停止处理的位置开始。
  • 引擎有自己的缓冲池，用于缓存表和根据访问的数据将数据索引到内存。经常使用的数据直接从高速缓存中提取，因此可以加快处理速度。在专用服务器中，它占用分配给缓冲池使用的物理内存的 80%。
  • 使用外键设置将相关数据拆分到表中会强制执行引用完整性，从而防止在主表中没有相应数据的情况下将任何不相关的数据插入到辅助表中。
  • 如果内存或磁盘中的数据损坏，校验和机制会在我们开始使用之前发出有关损坏数据的警报。
  • 更改缓冲自动优化了Insert、Update和Delete。InnoDB还允许对同一个表进行并发读写访问，并缓存数据更改以优化磁盘 I/O。
  • 当从表中重复访问相同的数据行时，自适应哈希索引功能可以加快查找速度并提高性能。
  • 允许对表和关联索引进行压缩。
  • 通过查询INFORMATION_SCHEMA或Performance Schema表，可以轻松监控存储引擎的内部工作和性能细节。

现在，让我们看看存储引擎的每个方面，其中InnoDB都经过了增强或优化，以提供非常高效和增强的性能

ACID模型是一组强调可靠性的数据库设计原则，对于任务关键型应用程序和业务数据来说，可靠性是最重要的。

MySQL 拥有与ACID模型紧密相连的InnoDB存储引擎等组件。因此，即使在硬件故障或软件崩溃的特殊情况下，数据也是安全的，不会损坏。

使用 MySQL 8，InnoDB支持原子DDL，确保DDL操作完全提交或回滚，即使服务器在执行操作时停止。现在可以将DDL日志写入数据字典表的mysql.innodb_ddl_log配置，启用innodb_print_ddl_logs配置选项将DDL恢复日志打印到stderr。

InnoDB 是一个多版本存储引擎。这意味着它能够保留已更改行数据信息的旧版本，并支持跨国界特性，如并发性和回滚。信息存储在表空间、数据结构和命名回滚段中。

在内部，对于存储在数据库中的每一行，InnoDB创建三个字段：6 字节DB_TRX_ID、7 字节DB_ROLL_PTR（称为滚动指针）和 6 字节DB_ROW_ID。通过这些字段，InnoDB创建聚集索引，将更改行数据的信息保存在数据库中

在本节中，我们将简要介绍InnoDB架构的主要组件：

  • 缓冲池：主存中缓存表和索引数据以加快处理速度的区域
  • 变更缓冲区：缓存二级索引页变更的特殊数据结构
  • 自适应散列索引：启用内存内数据库，如在缓冲池内存和工作负载均衡、适当组合的系统上进行查找、操作
  • 重做日志缓冲区：保存数据以写入重做日志的存储区
  • 系统表空间：存储 MySQL 8 数据字典信息之前的doublewrite缓冲区、撤销日志、变更缓冲区的存储区域
  • 双写缓冲区：系统表空间中的存储区域，用于写入从缓冲池刷新的页面
  • 撤销日志：与任何单笔交易关联的撤销日志记录的集合
  • 每个表表空间的文件：将单个表表空间添加到自己的数据文件中
  • 通用表空间：通过CREATE TABLESPACE语法创建的共享表空间
  • 撤销表空间：一个或多个具有撤销日志的文件
  • 临时表空间：用于非压缩临时表及其相关对象
  • 重做日志：基于磁盘的数据结构，用于在崩溃恢复期间更正不完整的事务数据

**对于 MySQL 8，InnoDB存储引擎使用全局 MySQL 数据字典，而不是自己的存储引擎特定数据字典

本节简要介绍InnoDB使用的锁和InnoDB实现的事务模型。InnoDB使用以下不同的锁类型：

  • 共享锁和独占锁：实现两种标准行级锁。共享锁允许您读取不同事务的行；独占锁用于更新或删除行，甚至不允许您将该行读取到任何不同的事务。
  • 意向锁：表级锁，支持多粒度锁，InnoDB实际维护行级锁与整表级锁共存。
  • 记录锁：索引记录锁，防止任何其他事务插入、更新或删除记录。
  • 间隙锁定：锁定应用于索引记录之间的间隙（范围）。
  • 下一键锁：索引记录锁加上前一索引记录的间隙锁的组合。
  • 插入意向锁：插入行前INSERT操作设置的间隙锁类型。
  • AUTO-INC 锁：用于插入AUTO_INCREMENT列记录的特殊表级锁。
  • 空间索引谓词锁：锁定空间索引，支持具有空间索引的表中的隔离级别。

遵循事务模型的目标是将传统的两阶段锁定与最佳的多版本数据库属性结合起来。执行行级锁定，并使用非锁定一致读取运行查询。InnoDB负责事务隔离级别、自动提交、回滚和提交以及锁定读取。如果适用，它允许非锁定一致读取。InnoDB还使用一种机制来避免虚行，并使用一种配置来支持自动死锁检测。

本节简要介绍不同InnoDB组件InnoDB初始化启动中使用的配置和程序：

  • InnoDB启动配置：这包括指定启动选项、日志文件配置、存储注意事项、系统表空间数据文件、撤消表空间、临时表空间、页面大小和内存配置
  • InnoDB用于只读操作：启用 MySQL 实例进行只读操作，使用--innodb-read-only=1选项，这在使用CD或DVD等只读媒体时非常有用
  • InnoDB缓冲池配置：配置缓冲池大小、多实例、刷新、监控
  • InnoDB变更缓冲：配置二级索引缓存的变更缓冲选项
  • InnoDB的线程并发：并发线程数限制配置
  • 后台InnoDBI/O 线程数：配置在数据页上进行 I/O 读写操作的后台线程数
  • 在 Linux 上使用异步 I/O：在 Linux 上使用本机异步 I/O 子系统的配置
  • InnoDB主线程 I/O 速率：为后台工作的主线程配置整体 I/O 容量，负责多个任务 自旋锁轮询：配置自旋等待延迟周期，控制请求获取mutexes或rw-locks的多个线程之间频繁轮询的最大延迟 InnoDB清除调度：配置清除线程以实现适用的可扩展性* InnoDB的*优化器统计：配置持久和非持久优化器统计参数 索引页的合并阈值：配置MERGE_THRESHOLD以减少合并拆分行为* 启用**专用 MySQL 服务器的自动配置：配置专用服务器选项--innodb_dedicated_server，自动配置缓冲池大小和日志文件大小

**# 表空间

本节简要介绍表空间以及在InnoDB中执行的与表空间相关的操作：

  • 调整InnoDB系统表空间大小：在启动/重启 MySQL 服务器的同时，通过配置增减系统表空间的大小。
  • C挂起InnoDB重做日志文件的数量或大小：在启动/重启 MySQL 服务器之前，分别在my.cnf中配置innodb_log_files_in_group和innodb_log_file_size值。
  • 为系统表空间使用原始磁盘分区：将原始磁盘分区配置为系统表空间中的数据文件。
  • InnoDB每个表的文件表空间：默认启用的功能innodb_file_per_table，确保每个表和相关索引存储在单独的.idb数据文件中。
  • 配置撤销表空间：设置撤销日志所在撤销表空间数量的配置。
  • 截断撤销表空间：将innodb_undo_log_truncate配置为允许截断超过innodb_max_undo_log_size中定义的最大限制的撤销表空间文件。
  • InnoDB通用表空间：使用CREATE TABLESPACE语句创建的共享表空间。它类似于系统表空间。
  • InnoDB表空间加密：支持对使用AES基于块加密算法的表空间中以文件形式存储的表进行数据加密。

本节简要介绍InnoDB表和索引及其相关操作：

  • 创建InnoDB表：使用CREATE TABLE语句创建表。
  • 一张InnoDB表的物理行结构：在创建表的过程中，取决于指定的行格式。如果未指定，则使用默认值DYNAMIC。
  • 移动或复制InnoDB表：将部分或全部InnoDB表移动或复制到不同实例或服务器的不同技术。
  • 将表从MyISAM转换为InnoDB：在将MyISAM表转换为InnoDB表时考虑指导原则和技巧，但分区表除外，MySQL 8 不支持分区表
  • InnoDB中的AUTO_INCREMENT处理：将AUTO_INCREMENT的模式配置为innodb_autoinc_lock_mode参数分别为 0、1、2，用于传统模式、连续模式或交错模式，其中交错模式是 MySQL 8 的默认模式。
  • 对InnoDB表的限制：一个表最多可以包含 1017 列，最多可以包含 64 个二级索引，以及根据页面大小、表大小和数据行格式定义的其他几个限制。
  • 聚集索引和二级索引：InnoDB使用称为聚集索引的特殊索引。其余的索引称为二级索引。
  • InnoDB索引的物理结构：对于空间索引，InnoDB使用R-tree数据结构，这是一种专门的数据结构。其余索引使用B-tree数据结构。
  • 已排序索引生成：为插入创建或重建索引时批量加载。它们称为排序索引构建，在空间索引中不受支持。
  • InnoDB``FULLTEXT索引：为基于文本的列创建—char、varchar或text类型。它们有助于加快查询和搜索操作。

本节提供了InnoDB``INFORMATION_SCHEMA表的使用示例及相关信息。 提供InnoDB存储引擎不同方面的元数据、统计信息和状态信息。

通过在INFORMATION_SCHEMA数据库上执行SHOW TABLES语句，可以获取InnoDB``INFORMATION_SCHEMA表的列表：

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB%';
  • 压缩表：在INNODB_CMP和INNODB_CMP_RESET表中提供的压缩操作次数和压缩相关信息所花费的时间。INNODB_CMPMEM和INNODB_CMPMEM_RESET表中提供了压缩期间的内存分配。
  • 交易和锁定信息：INNODB_TRX包含当前正在执行的交易的信息，Performance Schema表中的data_locks和data_lock_waits表给出了关于锁定的信息
  • 模式对象表：提供InnoDB模式对象的元数据信息。
  • FULLTEXT索引表：提供FULLTEXT索引的元数据信息。
  • 缓冲池表：提供缓冲池中页面的状态信息和元数据。
  • 指标表：提供性能和资源相关信息。
  • 临时表信息表：提供InnoDB实例中当前活动的所有用户和系统创建的临时表的元数据信息。
  • 检索InnoDB表空间元数据：提供InnoDB实例中所有表空间类型的元数据信息。

添加了一个新视图INNODB_TABLESPACES_BRIEF，以提供名称、路径、标志、空间和空间类型数据。

添加了一个新表INNODB_CACHED_INDEXES，以提供每个索引在缓冲池中缓存的索引页数。

MySQL 8 为您提供了名为daemon_memcached的InnoDBmemcached 插件，可以帮助我们轻松管理数据。它将自动存储和检索InnoDB表中的数据，并提供get、set和incr操作，通过跳过 SQL 解析消除性能开销，从而加快数据操作。memcached插件使用集成的memcached守护进程，自动从InnoDB表检索和存储数据，使 MySQL 服务器能够将数据快速发送到key-value存储。

以下是使用InnoDB memcached插件的主要好处：

  • 直接访问InnoDB存储引擎，减少解析和规划 SQL 开销
  • memcached使用与 MySQL 服务器相同的进程空间，减少网络开销
  • 在memcached协议中写入或请求的数据从InnoDB表中透明地写入或查询，减少了必须通过 SQL 层的开销
  • 通过在磁盘和内存之间自动传输简化应用程序逻辑
  • MySQL 数据库存储数据，以防止数据损坏、崩溃或中断
  • 结合使用主服务器上的daemon_memcached插件和 MySQL 复制，确保高可用性
  • 重复的数据请求使用InnoDB缓冲池进行缓存，提供高速处理
  • 由于数据存储在InnoDB表中，数据一致性将自动执行

InnoDB memcached插件支持多个 get 操作（在一个memcached查询中获取多个键/值对）和范围查询。

MySQL AB 在 MySQL 5.1 中引入了可插拔存储引擎体系结构，包括 MySQL 8 在内的所有更高版本都利用了灵活的存储引擎体系结构。

存储引擎可插拔体系结构提供了创建和添加新存储引擎的能力，而无需重新编译服务器，直接添加到正在运行的 MySQL 服务器。该体系结构使开发和部署新的存储引擎到 MySQL 8 变得非常容易。

在开发新的存储引擎时，需要注意存储引擎的所有组件。这些包括安装处理程序、表上的操作，如创建、打开和关闭、DML、索引等。

在本节中，我们将参考开发社区中提供的 MySQL 文档，介绍如何在高层基础上开始开发新的存储引擎。创建自定义存储引擎需要使用C和CPP进行开发，并使用cmake和Visual Studio进行编译。

实现新存储引擎的最简单方法是从复制和修改EXAMPLE存储引擎开始。文件ha_example.cc和ha_example.h可以在 MySQL 源代码发行版的storage/example目录中找到。

复制文件时，将名称从ha_example.cc和ha_example.h更改为适合您的存储引擎的名称，例如ha_foo.cc和ha_foo.h。

复制并重命名文件后，必须用存储引擎的名称替换EXAMPLE和example的所有实例。

插件可以实现状态和系统变量，在本节中，我们已经介绍了使用适当的值和数据类型对变量和参数所做的更改。

服务器插件接口允许插件使用通用插件描述符的status_vars和system_vars成员公开状态和系统变量。

status_vars是通用插件描述符的成员。如果该值不是 0，则它指向一个st_mysql_show_var结构的数组，其中每个结构描述一个状态变量，后跟一个所有成员都设置为 0 的结构。st_mysql_show_var结构的定义如下：

struct st_mysql_show_var {   
  const char *name;   
  char *value;   
  enum enum_mysql_show_type type; 
};

安装插件时，插件名称和名称值用下划线连接，形成SHOW STATUS语句显示的名称。

下表显示了允许的状态变量类型值以及相应的变量应该是什么：

  • SHOW_BOOL：这是指向boolean变量的指针
  • SHOW_INT：这是指向integer变量的指针
  • SHOW_LONG：这是指向长integer变量的指针
  • SHOW_LONGLONG：这是指向longlong integer变量的指针
  • SHOW_CHAR：这是一个String索引
  • SHOW_CHAR_PTR：这是指向String索引的指针
  • SHOW_ARRAY：这是指向另一个st_mysql_show_var array的指针
  • SHOW_FUNC：这是指向函数的指针
  • SHOW_DOUBLE：这是指向double的指针

所有会话和全局系统变量在使用前必须发布到mysqld。这正是通过构造一个以NULL结尾的变量数组并在插件公共接口中链接到它来实现的。

所有可变和插件系统变量都存储在HASH结构内部。

服务器命令行帮助文本的显示是通过编译所有相关变量的DYNAMIC_ARRAY，对它们进行排序和迭代以显示每个选项而生成的。

在插件安装过程中，服务器会在插件成功加载后立即处理命令行选项，但插件初始化功能尚未调用。

在runtime加载的插件不受益于任何配置选项，必须具有可用的默认值。一旦安装，在mysqld初始化时加载，可在命令行或my.cnf内设置配置选项。

thd参数在插件中应视为只读。

handlerton（handler singleton 的缩写形式）定义了存储引擎。它包含指向应用于整个存储引擎的方法的方法指针，而不是基于每个表的方法。此类方法的示例包括处理提交和回滚操作的事务方法。

EXAMPLE存储引擎的示例如下：

handlerton example_hton= {
 "EXAMPLE", /* Name of the storage engine */
 SHOW_OPTION_YES, /* It should be displayed in options or not */
 "Example storage engine", /* Description of the storage engine */
 DB_TYPE_EXAMPLE_DB, /* Type of storage engine it should refer to */
 NULL, /* Initialize handlerton */
 0, /* slot  available */
 0, /* define savepoint size. */
 NULL, /* handle close_connection */
 NULL, /* handle savepoint */
 NULL, /* handle rollback to savepoint */
 NULL, /* handle release savepoint */
 NULL, /* handle commit */
 NULL, /* handle rollback */
 NULL, /* handle prepare */
 NULL, /* handle recover */
 NULL, /* handle commit_by_xid */
 NULL, /* handle rollback_by_xid */
 NULL, /* handle create_cursor_read_view */
 NULL, /* handle set_cursor_read_view */
 NULL, /* handle close_cursor_read_view */
 example_create_handler, /* Create a new handler instance */
 NULL, /* handle drop database */
 NULL, /* handle panic call */
 NULL, /* handle release temporary latches */
 NULL, /* Update relevant Statistics */
 NULL, /* Start Consistent Snapshot for reference */
 NULL, /* handle flush logs */
 NULL, /* handle show status */
 NULL, /* handle replication Report Sent to Binlog */
 HTON_CAN_RECREATE
};

共有 30 个handlerton要素，其中只有少数是强制性的。

这是存储引擎中创建新处理程序实例所需的第一个方法调用。

在源文件中定义handlerton之前，必须在方法头中定义实例化方法，下面是CSV引擎显示实例化方法的示例：

static handler* tina_create_handler(TABLE *table);

正如您在前面的示例中所看到的，该方法接受指向该表的指针。handler 负责管理并返回 handler 对象，方法头定义后，在create()``handlerton元素中用方法指针命名方法。这将该方法标识为负责在请求时生成新的处理程序实例。

MyISAM存储引擎的实例化方法如下图所示：

static handler *myisam_create_handler(TABLE *table)
 {
 return new ha_myisam(table);
 }

存储引擎必须提供存储引擎使用的扩展列表，这些扩展与给定表、表的数据和 MySQL 服务器的索引关联。

扩展应该以空终止字符串数组的形式给出，并且在调用[custom-engine.html#custom-engine-api-reference-bas_ext bas_ext()方法时返回相同的扩展，如下面的块所示：

const char **ha_tina::bas_ext() const
{
 return ha_tina_exts;
}

通过提供扩展信息，您还可以跳过实现DROP TABLE功能，因为 MySQL 服务器将通过关闭表并删除所有具有指定扩展名的文件来实现相同的功能。

在处理程序实例化之后，应该遵循 table 方法的创建。存储引擎必须执行[custom-engine.html#custom-engine-api-reference-create create()方法，如下方框所示：

virtual int create(const char *name, TABLE *form, HA_CREATE_INFO *info)=0;

前面显示的方法应该创建所有必要的文件，但它不会打开表。MySQL 服务器将单独调用以打开表。

*name参数用于传递表名，*form参数用于传递TABLE结构。表结构定义了表，并与tablename.frm的内容相匹配。存储引擎不得修改tablename.frm文件，因为这将导致错误或不可预测的问题。

*info参数是包含CREATE TABLE语句信息的结构。用于创建表，结构在handler.h文件中定义。以下是供参考的结构：

typedef struct st_ha_create_information
{
 CHARSET_INFO *table_charset, *default_table_charset; /* charset in table */
 LEX_STRING connect_string; /* connection string */
 const char *comment,*password; /* storing comments and password values */
 const char *data_file_name, *index_file_name; /* data and index file names */
 const char *alias; /* value pointer for alias */
 ulonglong max_rows,min_rows;
 ulonglong auto_increment_value;
 ulong table_options;
 ulong avg_row_length;
 ulong raid_chunksize;
 ulong used_fields;
 SQL_LIST merge_list;
 enum db_type db_type; /* value for db_type */
 enum row_type row_type; /* value for row_type */
 uint null_bits; /* NULL bits specified at start of record */
 uint options; /* OR of HA_CREATE_ options specification */
 uint raid_type,raid_chunks; /* raid type and chunks info */
 uint merge_insert_method;
 uint extra_size; /* length of extra data segments */
 bool table_existed; /* 1 in create if table existed */
 bool frm_only; /* 1 if no ha_create_table() */
 bool varchar; /* 1 if table has a VARCHAR */
} HA_CREATE_INFO;

存储引擎可以忽略*info和*form的内容，因为只有在存储引擎使用时才真正需要创建和初始化数据文件。

在对任何表执行任何读写操作之前，MySQL 服务器调用[custom-engine.html#custom-engine-api-reference-open handler::open()方法打开表索引和数据文件：

int open(const char *name, int mode, int test_if_locked);

第一个参数是要打开的表的名称。第二个参数用于执行文件操作。数值在handler.h：O_RDONLY - Open read only、O_RDWR - Open read/write中定义。

最后一个选项指示处理程序是否应在打开前检查表上的锁。以下选项可供选择：

#define HA_OPEN_ABORT_IF_LOCKED 0 /* default */
#define HA_OPEN_WAIT_IF_LOCKED 1 /* wait if table is locked */
#define HA_OPEN_IGNORE_IF_LOCKED 2 /* ignore if locked */
#define HA_OPEN_TMP_TABLE 4 /* Table is a temp table */
#define HA_OPEN_DELAY_KEY_WRITE 8 /* Don't update index */
#define HA_OPEN_ABORT_IF_CRASHED 16
#define HA_OPEN_FOR_REPAIR 32 /* open even if crashed with repair */

典型的存储引擎将实现某种形式的共享访问控制，以防止多线程环境中的文件损坏。例如，实现文件锁定请参见sql/example/ha_tina.cc的get_share()和free_share()方法。

最基本的存储引擎实现了只读级别的表扫描，它们可能用于支持 SQL 查询，以便从 MySQL 之外填充的日志和其他数据文件请求信息。

实现这些方法是创建高级存储引擎的第一步。下面显示了在CSV引擎的九行表扫描期间进行的方法调用：

ha_tina::store_lock
ha_tina::external_lock
ha_tina::info
ha_tina::rnd_init
ha_tina::extra - ENUM HA_EXTRA_CACHE Cache record in HA_rrnd()
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::rnd_next
ha_tina::extra - ENUM HA_EXTRA_NO_CACHE End caching of records (def)
ha_tina::external_lock
ha_tina::extra - ENUM HA_EXTRA_RESET Reset database to after open

可以采用以下方法来处理特定操作：

  • 实现store_lock()：此方法可以修改锁级别，忽略或添加多个表的锁
  • 实现external_lock()：在LOCK TABLES语句发出时调用此方法
  • 实现rnd_init()：此方法用于表扫描，用于重置表开始处的计数器和指针
  • 实现info(uinf flag)：此方法用于向优化器提供额外的表信息
  • 实现extra()：此方法用于向存储引擎提供额外的提示信息
  • 执行rnd_next()：每行扫描调用此方法，直到达到EOF或满足搜索条件为止

当 MySQL 服务器对该表完成所有请求的操作后，将调用custom-engine.html#custom-engine-api-reference-close close()方法。它将关闭文件指针并释放所有相关资源。

使用共享访问方法的存储引擎见CSV引擎。其他示例引擎必须将其从共享结构中删除，如下所示：

int ha_tina::close(void)
 {
 DBUG_ENTER("ha_tina::close");
 DBUG_RETURN(free_share(share));
 }

存储引擎使用自己的共享管理系统。他们应该使用所需的方法，以便从共享中删除在其处理程序中打开的相应表的处理程序实例。

If your storage engine is compiled as a shared object, during loading if you get an error such as undefined symbol: _ZTI7handler, then make sure you compile and link your extension using the same flags as the server uses. The usual reason for this error is that LDFLAGS are missing the -fno-rtti option.

我们已经详细阅读了前面的章节，给出了定制存储引擎组件的高级信息和所需的更改。要在定制存储引擎中实现INSERT、UPDATE、DELETE、索引等，需要具备使用C/CPP进行开发和使用C/CPP进行编译的工作知识cmake和Visual Studio。有关定制存储引擎的高级开发，请参阅中给出的详细信息 https://dev.mysql.com/doc/internals/en/custom-engine.html

到目前为止，您已经了解了 MySQL 8 中可用的不同数据库引擎，我们也了解了为什么我们应该关注 MySQL 8 中的存储引擎和可用存储引擎选项。我们详细介绍了InnoDB存储引擎以及InnoDB存储引擎中已经提供的相关重要功能。现在，您实际上可以根据系统需求创建一个自定义存储引擎，并使其可插入 MySQL 8。一个重要的方面是为您的系统选择合适的存储引擎，下面将详细介绍。

在下一章中，您将了解 MySQL 8 中索引的工作原理、引入的与索引相关的新功能、不同类型的索引以及如何在表上使用索引。除此之外，还将进行比较，并深入了解索引实现的各种方式。****

教程来源于Github，感谢apachecn大佬的无私奉献，致敬！

技术教程推荐

玩转Git三剑客 -〔苏玲〕

TensorFlow快速入门与实战 -〔彭靖田〕

重学前端 -〔程劭非（winter）〕

乔新亮的CTO成长复盘 -〔乔新亮〕

Python自动化办公实战课 -〔尹会生〕

深入剖析Java新特性 -〔范学雷〕

业务开发算法50讲 -〔黄清昊〕

反爬虫兵法演绎20讲 -〔DS Hunter〕

给程序员的写作课 -〔高磊〕

休闲君评测 |  TOOLFK工具网 |  chrome插件网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  豪评测 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630