MySQL变长字段的庖丁解牛-育师

MySQL 中的变长字段（如VARCHAR、TEXT、BLOB、JSON等）是数据库设计中最常用也最易被误解的元素之一。

一、什么是变长字段？

变长字段是指其存储长度随实际内容变化的字段类型，与CHAR、INT等固定长度字段相对。

常见变长字段类型：

类型	最大长度	用途
`VARCHAR(N)`	65,535 字节（受行总长限制）	短文本（用户名、标题）
`TEXT`	65,535 字节	中长文本（文章、描述）
`MEDIUMTEXT`	16MB	长文本
`LONGTEXT`	4GB	超长文本（日志、文档）
`BLOB`/`MEDIUMBLOB`/`LONGBLOB`	同 TEXT	二进制数据（图片、文件）
`JSON`	1GB（实际受`max_allowed_packet`限制）	结构化数据

🔑核心特征：存储开销 = 实际内容长度 + 长度前缀 + 可能的溢出指针。

二、存储原理：长度前缀 + 内容

InnoDB 为每个变长字段存储长度前缀（Length Prefix） + 实际数据：

字段最大长度	长度前缀大小
≤ 255 字节	1 字节
> 255 字节	2 字节

示例：

nameVARCHAR(100)-- 存 "John" → 1B (len=4) + 4B (data) = 5BcontentTEXT-- 存 1000B 文本 → 2B (len=1000) + 1000B = 1002B

✅优势：节省空间（相比CHAR(100)总是占 100B）。

三、行格式（ROW_FORMAT）的决定性影响

变长字段的存储行为高度依赖 InnoDB 行格式。三种主要格式对比：

行格式	变长字段存储策略	适用场景
REDUNDANT（旧）	尽量全存主页	MySQL 5.0 之前（已淘汰）
COMPACT（默认）	前 768 字节存主页，剩余存溢出页	兼容旧版
DYNAMIC（推荐）	全部存溢出页，主页仅 20B 指针	现代应用（含大字段）

关键区别：溢出阈值

COMPACT：单字段 > 768 字节 → 溢出；
DYNAMIC：只要字段可能 > 行剩余空间，就溢出（更激进）。

💡DYNAMIC 的核心思想：“主页只存指针，内容全外置”，避免主页膨胀。

四、溢出页（Off-page Storage）机制

当变长字段无法完全放入主索引页（16KB）时，InnoDB 使用溢出页存储数据。

溢出触发条件：

单字段 > 768 字节（COMPACT）或行总长 > 8KB（DYNAMIC）；
页面剩余空间不足（即使字段 < 768B，但行已很长）。

溢出存储结构：

主页：存储20 字节指针（DYNAMIC）或768B + 20B 指针（COMPACT）；
溢出页：16KB 页，存储实际数据；
链式溢出：若单字段 > 16KB，跨多页，页间指针链接。

📌DYNAMIC 的优势：
主页更紧凑 → 更多行缓存在 Buffer Pool →减少 I/O，提升查询性能（尤其当查询不包含大字段时）。

五、行大小限制：65,535 字节的真相

MySQL 文档常说“行最大 65,535 字节”，但这是逻辑限制，非物理限制。

真实规则：

所有字段长度之和 ≤ 65,535 字节（仅计算长度前缀，不包括溢出内容）；
实际存储无硬限制（因溢出页可无限扩展）。

示例：

-- 合法！尽管总内容可能 > 65KBCREATETABLEt(aVARCHAR(30000)CHARACTERSETutf8mb4,-- 最多 120,000 字节bVARCHAR(30000)CHARACTERSETutf8mb4);-- 错误！30000*4*2 = 240,000 > 65,535-- 正确做法：用 TEXTCREATETABLEt(aTEXT,bTEXT);-- 合法！TEXT 指针仅占 20B * 2 = 40B < 65,535

✅设计建议：
VARCHAR用于 ≤ 1KB 的字段；
TEXT/BLOB用于 > 1KB 的字段（避免行大小限制）。

六、索引与变长字段

1.前缀索引

变长字段不能全列索引（因长度不定）；

必须指定前缀长度：

CREATEINDEXidx_nameONusers(name(20));-- 前 20 字符

2.索引长度限制

COMPACT：索引前缀 ≤ 768 字节；
DYNAMIC：索引前缀 ≤3072 字节（因主页无 768B 限制）。

🔧启用长索引：

SETinnodb_default_row_format=DYNAMIC;CREATETABLEt(nameVARCHAR(3000),INDEX(name(3000)));

七、性能权衡：何时快，何时慢？

场景	COMPACT	DYNAMIC
查询不含大字段（`SELECT id, name`）	慢（主页膨胀，缓存效率低）	快（主页紧凑，缓存更多行）
查询含大字段（`SELECT id, bio`）	快（部分数据在主页）	慢（需额外 I/O 读溢出页）
插入/更新大字段	中（可能需移动 768B 数据）	快（仅更新指针）

最佳实践：

多数查询不含大字段→ 用DYNAMIC；
总是查询大字段→ 考虑COMPACT或分离大字段到单独表。

八、JSON 与变长字段

MySQL 8.0+ 的JSON类型底层是BLOB，因此：

遵循BLOB的存储规则；
必须使用DYNAMIC行格式（否则无法创建虚拟列索引）；
虚拟列索引实际存储在二级索引页，非溢出页。

✅ 总结：变长字段的“牛体结构”

维度	解析
本质	长度前缀 + 实际内容，可能溢出
存储	主页 or 溢出页，由行格式决定
限制	行逻辑长度 ≤ 65,535B（仅指针/前缀）
索引	需前缀索引，DYNAMIC 支持更长前缀
性能	DYNAMIC 优化缓存，COMPACT 优化大字段读取
哲学	“分离大小，各安其位”

如庖丁所言：“彼节者有间，而刀刃者无厚。”
变长字段正是那条“间隙”——
它不显于表结构，
却是InnoDB 存储的咽喉要道。
善用DYNAMIC者，则“恢恢乎其于游刃必有余地矣”；
滥用VARCHAR(65535)者，则“技经肯綮，砉然已解”——行满页裂，性能崩坏。
故曰：知其隙（溢出机制），守其衡（查询模式），以 TEXT 为道，以 DYNAMIC 为刃，
方可在数据库之林，游刃有余。