MySQL 存储精要：从数据类型到特长字段处理

前言：为什么要写这篇笔记？

在面试中，面试官通过“字段类型选择”和“长数据存储”这两个问题，考察的不仅是基础知识，更是对 InnoDB 存储引擎底层原理（如行溢出、页限制）的理解。

• 反思： 以前只知道 VARCHAR 设为 255，却不知道为什么要设为 255。
• 目标： 建立从“物理存储”到“业务选型”的全链路认知，确保在处理海量数据时，能从底层规避 IO 瓶颈。

---

一、 数值与时间：精准与空间的权衡

1. 整数类型（Integer）

类型	存储字节	无符号最大值	建议场景
TINYINT	1 字节	255	状态位、枚举、逻辑删除 (0/1)
INT	4 字节	约 42 亿	普通 ID、计数器
BIGINT	8 字节	$1.84 \times 10^{19}$	雪花算法 ID、超大业务流水

2. 金额处理（最重要）

• 错误做法： 使用 FLOAT 或 DOUBLE（存在浮点数精度丢失风险）。

• 正确做法： * DECIMAL(M, D)：定点数，不丢失精度。

  • BIGINT：以“分”为单位存储，展示时除以 100。

---

二、 深度拆解：VARCHAR 的长度极限

1. 物理上限：65535 字节

MySQL 单行（除大对象外）所有字段的总和不能超过 65,535 字节。

2. “字符”与“字节”的换算（面试核心）

面试官问“最多存多少字符”，答案取决于 字符集：

• UTF8MB4 (推荐)： 1 字符 = 4 字节。最大长度 $\approx 65535 / 4 = 16383$ 字符。
• VARCHAR(255) 的玄学： 长度 $\le 255$ 时，MySQL 用 1 字节 存储长度；$> 255$ 时用 2 字节。因此 VARCHAR(255) 是平衡空间利用率的经典做法。

---

三、 特长数据的存储策略：当单行装不下时

当遇到文章详情、长日志等“特别长”的数据时，应遵循以下阶梯式方案：

1. 类型选型：TEXT 家族

• TEXT (64KB) / MEDIUMTEXT (16MB) / LONGTEXT (4GB)
• 隐患： 无法使用内存临时表，查询涉及此类字段时会产生磁盘临时表，IO 开销巨大。

2. 性能方案：垂直拆分（冷热分离）

• 核心思想： 别让“大块头”拖累“小灵快”。

• 操作： * 主表： 存 ID、标题、作者（高频检索，全索引，物理行小）。

  • 详情表： 仅存 ID 和 LONGTEXT 内容（仅在点击阅读时按 ID 查询）。

3. 架构方案：外部存储（Off-loading）

对于图片、音视频或极长的文档，遵循 “存路径，不存内容” ：

• 存储： 文件上传至对象存储（如阿里云 OSS、AWS S3）。
• 数据库： 只存一个指向该文件的 URL 字符串。

---

四、 底层原理：行溢出（Row Overflow）

这是体现深度的地方。InnoDB 默认页大小为 16KB。

• 如果一行数据太长，超过了页大小的一半，InnoDB 会触发行溢出。
• 现象： 原始页只保留一个 20 字节的指针，真实数据被踢到 Overflow Page（溢出页） 。
• 后果： 读取该行需要额外的磁盘随机 IO，性能大幅下降。

---

💡 面试高分话术总结

问：特别长的数据你怎么存？

“我会根据数据规模和访问频率分三步走：

1. 512 字节到 64KB： 优先用 VARCHAR，如果超过行限制则用 TEXT。

2. 性能优化： 如果该字段不是搜索的高频条件，我会进行垂直拆分，将大字段隔离到扩展表中，防止大字段占满缓存页（Buffer Pool），提高主表的扫描效率。

3. 海量数据/非结构化： 如果是几百 MB 的日志或附件，我会采用 OSS 对象存储 + 数据库记录 URL 的方案。

   这样做的底层逻辑是避免 InnoDB 频繁触发行溢出，从而保护整体系统的 IO 稳定性。”