InnoDB的使用限制有哪些

黑风风

已于 2024-05-29 18:10:27 修改

阅读量725

点赞数 8

分类专栏： MySQL进阶学习文章标签： mysql InnoDB

于 2024-04-14 11:39:53 首次发布

本文链接：https://blog.csdn.net/kaka_buka/article/details/137740549

版权

MySQL进阶学习专栏收录该内容

52 篇文章 0 订阅

订阅专栏

InnoDB的使用限制有哪些

以下是一些使用InnoDB在使用中的限制，包含InnoDb表，索引，表空间，和InnoDB存储引擎其他方面的各种限制。

一个表最多包含1017列字段，虚拟生成的列也包含在这个限制中。

每个表的元数据需要在内存中维护其结构，过多的列会增加内存消耗和管理复杂度。同时，SQL查询处理和优化器的效率也可能因此受到影响。此外，虽然理论上允许有这么多列，但在实际应用中，拥有成百上千个列的表很少见，并且通常不推荐这样设计数据库，因为这可能会导致数据库性能问题和维护难度。

虚拟生成列（也称为计算列或派生列）是一种在MySQL中定义的特殊列，其值不是直接存储的，而是通过其他列的值计算得出。虚拟列的值在每次查询时都会动态计算，而不是在数据库中持久存储。这种类型的列特别有用于存储那些从其他列派生的结果，如执行数学运算、字符串操作或转换数据类型的结果。

在MySQL中创建虚拟生成列的语法如下：

CREATE TABLE example (
    id INT,
    price DECIMAL(10,2),
    quantity INT,
    total_cost AS (price * quantity) VIRTUAL
);

在上述示例中，total_cost是一个虚拟生成列，它通过计算price和quantity的乘积来动态生成其值。该列被标记为VIRTUAL，表示其值不会在表中持久存储，而是在每次访问时计算。

一个表最多包含64个二级索引

二级索引，也称为非聚集索引，是指不改变表中数据物理存储顺序的索引。每增加一个索引，都会占用额外的存储空间，并且更新表中数据时，相关的索引也需要被更新，这会增加写操作的负担。限制索引的数量有助于平衡查询性能与维护成本。对于大多数应用来说，64个索引已经足够使用，过多的索引可能会导致管理和性能的问题。

二级索引（也称为非聚集索引）是指不按数据的物理顺序，而是按照一个或多个列的值来排序和存储的索引。在数据库中，二级索引使得不需要按照主键排序的查询可以更快地执行，因为它提供了对表中数据的另一种查找方式。

例如，假设有一个用户表，主键是用户ID，但我们经常需要按照用户名来查找用户。在这种情况下，用户名上的二级索引将会很有帮助：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE INDEX idx_username ON users(username);

在这个例子中，idx_username是一个二级索引，它帮助数据库快速地根据用户名来查找用户，而不需要扫描整个表。

使用DYNAMIC或者COMPRESSED行格式的表，前缀索引的长度被限制在3072字节

InnoDB存储引擎提供了几种不同的行格式，用于优化表的存储和性能，其中DYNAMIC和COMPRESSED是两种常见的选择。

DYNAMIC行格式：这种格式为表中的可变长度字段（如VARCHAR、BLOB和TEXT类型）提供了更有效的存储方式。与传统的COMPACT行格式相比，DYNAMIC格式只存储变长字段的实际使用长度，而不是预分配固定空间，这有助于节省空间并提高性能。
COMPRESSED行格式：在DYNAMIC格式的基础上，COMPRESSED行格式进一步通过压缩数据来减少表的物理占用空间。它特别适用于包含大量可变长度数据的表，如大文本文件或图片数据，可以显著减少磁盘使用量和提高IO效率。

使用REDUNDANT或者COMPACT行格式的表，前缀索引的长度被限制在767字节。比如，如果TEXT或VARCHAR列的列前缀索引超过191个字符，则可能会达到此限制，假设为utf8mb4字符集，每个字符最多4个字节。

试图使用超过限制的索引键前缀长度会返回错误。

如果在创建MySQL实例时通过指定InnoDB_page_size选项将InnoDB页面大小减少到8KB或4KB，则索引键的最大长度将按比例降低，这是基于16KB页面大小3072字节的限制。也就是说，当页面大小为8KB时，最大索引密钥长度为1536字节，当页面尺寸为4KB时，最大索引号长度为768字节。

这个限制适用于前缀索引，也适用于完整列索引。

多列索引，最多16列，超过限制会报错

报错如下：

ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

多列索引（也称为复合索引）是基于表中多个列的值构建的索引。这种类型的索引可以在查询涉及多个列的条件时提高性能，特别是对于查询和排序操作。多列索引按照定义索引时列的顺序来存储数据。

例如，如果一个查询常常需要同时根据姓和名来筛选结果，可以创建一个包含这两个列的复合索引：

CREATE INDEX idx_name ON customers(last_name, first_name);

对于4KB、8KB、16KB和32KB的页面大小，最大行大小（不包括页外存储的任何可变长度列）略小于页面的一半。

例如，16KB的默认innodb_page_size的最大行大小约为8000字节。然而，对于64KB的InnoDB页面大小，最大行大小约为16000字节。LONGBLOB和LONGTEXT列必须小于4GB，并且包括BLOB和TEXT列在内的总行大小必须小于4GB。

如果一行的长度小于半页，则所有行都存储在该页的本地。如果它超过半页，则选择可变长度列用于外部页外存储，直到该行适合半页，

虽然InnoDB内部支持大于65535字节的行大小，但MySQL本身对所有列的组合大小施加了65535的行大小限制。

因为MySQL在早期设计时对行大小做了限制，以简化数据库引擎的管理和提高处理效率。这意味着如果表中包含大量大型字段（如TEXT或BLOB类型），则可能需要优化表的结构，或考虑使用页外存储来避免超过这个限制。

页外存储（也称为外部存储）是InnoDB用于存储那些不能完整地存放在数据库页中的大字段（如BLOB和TEXT类型数据）的机制。当这些列的大小超过半个页面的大小时，InnoDB会将这些数据存储在表空间的特殊部分，只在主记录中保留指向这些外部数据的指针。

下面是如何定义一个使用页外存储的表的示例：

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content LONGTEXT,
    FULLTEXT idx_content (content)
) ENGINE=InnoDB;

在这个例子中，content列可能包含大量文本。如果某些行的content数据非常大，超过了页面大小的一半，InnoDB将自动将这部分数据存储在页外。

最大的表或者表空间尺寸受服务器文件系统的影响

文件系统可能会施加小于InnoDB定义的内部64TiB大小限制的最大文件大小。例如，Linux上的ext4文件系统的最大文件大小为16TiB，因此表或表空间的最大大小变成16TiB而不是64TiB。另一个例子是FAT32文件系统，其最大文件大小为4GB。

如果您需要一个更大的系统表空间，请使用几个较小的数据文件而不是一个大的数据文件来配置它，或者按表和常规表空间数据文件跨文件分发表数据。

InnoDB日志文件的最大组合大小为512GB。

InnoDB的事务日志文件是用于记录所有未提交事务的修改操作的，以确保在系统故障后能够恢复这些事务。InnoDB允许日志文件的最大组合大小达到512GB。较大的日志文件可以在系统故障后提供更长时间的事务数据恢复能力，但同时也可能增加数据恢复的时间。配置合适大小的日志文件是优化数据库性能和恢复能力的重要考虑因素。