MySQL中列数量和长度的限制

MySQL中列数量及长度各是多少

由于MySQL设计的一些核心限制。答案不是单一的数字，而是分层和复杂的，取决于多个因素，包括存储引擎、行格式以及MySQL版本。

简单来说，最主要的限制来自于行的最大大小，而不是单纯的列数量。

以下是详细的分解说明：

1. 硬限制：绝对最大列数

无论使用何种存储引擎和行格式，MySQL每个表的绝对最大列数是4096。

但是，这只是一个理论上的上限。在实际中，几乎永远无法达到这个数字，因为会受到下面“行大小限制”的约束。

2. 实际限制：行大小限制

这才是最关键的限制。一行的所有列的数据加起来，不能超过一个特定的值。

• 默认最大行大小：65,535字节。

这个限制是共享给所有列的。这意味着，即使有100个列，每个列只占用100字节，总长度10,000字节，这也是可以的。但如果有一个VARCHAR(30000)的列，它自己就可能占用30,000字节，那么剩下的空间就很少了，无法再创建很多其他大字段。

示例：

创建两个VARCHAR(30000)的列，理论上需要60,000字节，这小于65,535字节。

CREATE TABLE test (    col1 VARCHAR(30000),    col2 VARCHAR(30000)) ENGINE=InnoDB;

但会遇到一个错误：

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535...

为什么？

因为VARCHAR使用额外的一到两个字节来存储字符串的实际长度。所以两个VARCHAR(30000)列，最大可能占用 30000 * 2 + 2 * 2 = 60004 字节，这看起来是够的。但实际上，MySQL的计算方式更为复杂，还会考虑字符集（如utf8mb4一个字符最多占4字节）等因素，导致实际计算出的最大可能长度超过65,535字节。

3. 存储引擎和行格式的影响

不同的存储引擎和行格式对行大小的限制有不同的处理方式，这尤其影响可变长度数据（如VARCHAR, TEXT, BLOB） 和大对象数据。

InnoDB 引擎

InnoDB是MySQL最常用、默认的存储引擎。它的行为如下：

• 默认行格式（DYNAMIC 或 COMPACT）：
  • 这意味着，只要有很多TEXT或BLOB列，或者非常长的VARCHAR列，实际上可以拥有远超65,535字节的总数据，因为大部分数据被“卸载”到了别处。但是，仍然受限于每行最多4096列的绝对限制。
  • 对于可变长度列（如VARCHAR, VARBINARY），如果单个列的长度超过 768 字节，InnoDB会将其前768字节存储在页面中，而将剩余部分存储在溢出页中。
• BLOB和TEXT类型 几乎总是将其主要内容存储在溢出页中，只在主行中保留一个20字节的指针。
  • 关键点：存储在溢出页中的数据不计入 65,535 字节的行大小限制。只有存储在主行中的数据（768字节的前缀+指针+固定长度列）才受限于另一个更小的限制（约为8,000字节，具体取决于页面大小）。
  • 冗余行格式（REDUNDANT）：
  • 处理方式较老，所有内容都更倾向于存储在主行中，更容易达到行大小限制。

MyISAM 引擎

• MyISAM也有65,535字节的行大小限制。
• 但与InnoDB不同，它没有溢出页的概念。所有数据都必须放在一行内（但BLOB和TEXT类型仍然只计部分长度到行限制中）。
• 因此，对于MyISAM，65,535字节的限制更加严格和直接。

总结表格

实践建议

1. 不要试图接近极限：即使理论上允许4096列，设计拥有成百上千列的表通常是一个糟糕的数据库设计（“宽表”），会导致性能问题和管理困难。考虑使用关系规范化（拆分成多个表）。
2. 使用正确的数据类型：选择最合适、最小的数据类型。例如，用INT而不是BIGINT（如果值足够小），用VARCHAR(n)而不是总是VARCHAR(255)。
3. 考虑溢出行为：如果必须存储大量文本或二进制数据，了解InnoDB的溢出页机制。这有助于理解为什么查询这些大字段可能会更慢（需要额外的磁盘读取）。
4. 检查行格式：使用 SHOW TABLE STATUS LIKE ‘table_name’; 来查看表的行格式。现代MySQL版本默认使用DYNAMIC，这是处理宽表或有大字段表的最佳选择。

总而言之，能创建的列数量主要取决于这些列的类型和长度，因为它们共同决定了是否会突破“行大小”这个最主要的限制。

行格式

好的，这是一个非常核心的MySQL（特别是InnoDB）概念。理解行格式对于数据库设计、性能优化和存储效率至关重要。

什么是行格式？

行格式（Row Format） 指的是数据库表中每一行数据在磁盘上存储时的物理组织结构。可以把它想象成一个数据结构或模板，定义了如何将一行中的各个列值、元数据（如事务ID、回滚指针等）以及索引信息打包并写入磁盘页。

不同的行格式采用不同的策略来处理：

1. 数据存储：如何紧凑地存放数据以节省空间。
2. 溢出处理：当遇到非常长的可变长度列（如VARCHAR, TEXT, BLOB）时，如何处理超出页面大小的部分。
3. 压缩：是否支持数据压缩以进一步减少磁盘占用。
4. 性能影响：如何影响查询（特别是SELECT *）和DML（INSERT, UPDATE, DELETE）操作的性能。

InnoDB的行格式

InnoDB是MySQL最常用的存储引擎，它提供了四种主要的行格式。从MySQL 5.7及以后版本，默认的行格式是 DYNAMIC。

以下是这四种行格式的详细说明：

1.REDUNDANT（冗余）

• 时代：这是InnoDB最早支持的行格式，为了向后兼容而保留。
• 特点：
  • 存储效率低：它会存储一些冗余信息（故名“冗余”），例如字段偏移量列表会存储两次。
  • NULL处理：NULL值会在NULL位图和数据区域中都占用空间（对于可变长度列），效率不高。
  • 溢出处理：对于长于768字节的可变长度列，它会将前768字节存储在数据页中，其余部分存储在溢出页中。
  • 没有压缩：不支持表压缩。
• 使用场景：基本不再使用，除非是为了兼容非常老的版本。

2.COMPACT（紧凑）

• 改进：相较于REDUNDANT，它优化了存储结构，减少了大约20%的行空间消耗。
• 特点：
  • 更高效：更紧凑地存储行数据，尤其是对NULL值和可变长度字段的处理更高效。
  • 溢出处理：和REDUNDANT一样，长可变长度列的前768字节存储在数据页中。
  • 没有压缩：同样不支持表压缩。
• 使用场景：在MySQL 5.6之前是默认格式，现在已被更先进的格式取代。

3.DYNAMIC（动态）（默认）

• 现代默认选择：从MySQL 5.7.9及以后版本开始，成为默认的行格式。
• 核心特点：
  • 先进的溢出处理：这是它与COMPACT最大的区别。对于长可变长度列（如VARCHAR, TEXT, BLOB），DYNAMIC格式几乎将整个值都存储在溢出页中，在主行数据中只存储一个20字节的指针。这极大地提高了数据页的利用率，避免了因为少数几个大字段就填满整个数据页的情况。
  • 更高效的大字段处理：非常适合包含TEXT、BLOB或超长VARCHAR列的表。
  • 支持压缩：支持COMPRESSED压缩（但DYNAMIC本身不压缩数据）。
• 使用场景：绝大多数现代应用的默认推荐选择，特别是那些可能包含大对象数据的表。

4.COMPRESSED（压缩）

• 特点：
  • 包含所有DYNAMIC特性：同样使用指针方式处理大字段溢出。
  • 额外数据压缩：使用zlib算法对表和索引数据进行压缩，可以显著减少磁盘空间占用（通常能减少50%或更多）。
  • CPU开销：压缩和解压操作会带来额外的CPU计算开销。
  • 缓冲池效率：因为数据在磁盘上是压缩的，读入内存（缓冲池）后也是压缩状态，这意味着同样大小的缓冲池可以缓存更多的数据页，可能会提高缓存命中率。
• 使用场景：
  • 磁盘空间非常宝贵（例如SSD虽然快但贵）。
  • 表非常大，且数据读多写少，CPU资源不是瓶颈。
  • 典型的例子：归档数据、历史日志表、只读或读多写少的业务数据。

如何查看和设置行格式

查看表的行格式

-- 查看某个表的行格式等信息SHOW TABLE STATUS LIKE 'your_table_name';-- 或从信息模式中查询SELECT TABLE_NAME, ROW_FORMATFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

设置行格式

可以在创建表时指定：

CREATE TABLE your_table (    ...) ROW_FORMAT=DYNAMIC;

也可以在修改表时变更：

ALTER TABLE your_table ROW_FORMAT=COMPRESSED;

注意：修改现有表的行格式是一个代价较高的操作（会重建表），请在业务低峰期进行。

总结与选择建议

简单决策流程：

1. 如果没有特殊需求，坚持使用默认的 DYNAMIC。
2. 如果表中有很多TEXT或BLOB列，强烈推荐使用 DYNAMIC。
3. 如果需要节省磁盘空间，并且愿意用CPU资源来交换，同时读写压力不大，可以考虑 COMPRESSED。