CHAR vs VARCHAR vs TEXT vs LONGTEXT：存储方式、长度限制、索引限制？

考察要点

这道题考察的是候选人对 MySQL 核心数据类型，特别是字符串类型的深入理解。面试官想看你是否懂：

1. 底层存储机制：固定长度（CHAR）与可变长度（VARCHAR, TEXT）的实现差异，以及变长类型中“行内存储”与“行外存储”（Off-page Storage）的区别。
2. 性能影响：不同类型在空间利用率、查询/更新效率、内存消耗和磁盘 I/O 上的权衡。
3. 使用边界：各种类型的长度限制、索引限制，以及它们在不同业务场景下的最佳实践。

核心概念

CHAR, VARCHAR, 和 TEXT 家族是 MySQL 中最常用的字符串存储类型，它们在存储方式、性能和使用场景上有本质区别。

核心对比

1. CHAR(N) - 定长字符串

• 存储机制：CHAR(N) 会分配固定 N 个字符的存储空间。如果存入的字符串长度小于 N，MySQL 会在右侧用空格填充至 N 个字符。
• 长度限制：N 的范围是 0 到 255。
• 性能：
  • 优点：由于长度固定，记录在磁盘上的位置是确定的，更新和查找速度快，不会产生磁盘碎片。
  • 缺点：当存储的字符串长度差异很大时，会浪费大量存储空间。
• DDL 示例：
  sql

  ⧉⛶

   1CREATE TABLE t_char (
   2    country_code CHAR(2) NOT NULL, -- 如 'CN', 'US'
   3    md5_hash CHAR(32) NOT NULL    -- MD5 值总是 32 位
   4);

2. VARCHAR(N) - 变长字符串

• 存储机制：VARCHAR(N) 只使用实际需要的空间，外加 1 或 2 个字节来存储字符串的长度。
  • 如果列声明的最大长度 N 小于等于 255 字节（注意是字节，非字符），则使用 1 个字节记录长度。
  • 如果大于 255 字节，则使用 2 个字节。
• 长度限制：N 的理论最大值是 65,535 个字符，但实际受限于 MySQL 单行最大 65,535 字节的限制，并且该限制由所有列共享。例如，使用 utf8mb4 字符集（每个字符最多 4 字节），VARCHAR 列理论上最多只能定义为 VARCHAR(16383)。
• 性能：
  • 优点：节省空间，适应性强。
  • 缺点：更新时若长度增加，可能导致数据页（Page）分裂，引发额外 I/O 和磁盘碎片，降低性能。
• DDL 示例：
  sql

  ⧉⛶

   1CREATE TABLE t_varchar (
   2    user_name VARCHAR(50) NOT NULL, -- 用户名长度多变
   3    email VARCHAR(100)
   4);

3. TEXT 家族 - 长文本数据

包括 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT。

• 存储机制：当数据很长时，TEXT 类型的数据通常不会直接存储在数据行（B+ 树的叶子节点）内，而是存储在专门的“溢出页”（Overflow Page）或“行外”（Off-page）区域。数据行内仅保留一个指向实际数据位置的指针（通常为 7-12 字节）。
• 长度限制：
  • TINYTEXT: $2^8 - 1$ (255 字节)
  • TEXT: $2^{16} - 1$ (64 KB)
  • MEDIUMTEXT: $2^{24} - 1$ (16 MB)
  • LONGTEXT: $2^{32} - 1$ (4 GB)
• 索引限制：这是 TEXT 类型的关键短板。不能直接在 TEXT 列上创建完整索引。如果需要索引，必须指定前缀长度。
  sql

  ⧉⛶

   1-- 错误示范，无法执行
   2-- CREATE INDEX idx_content ON articles (content);
   3
   4-- 正确示范，创建前缀索引
   5CREATE INDEX idx_content_prefix ON articles (content(255));

• 性能：
  • 优点：将大对象移出数据行，可以使行本身变得更小，内存中可以缓存更多行，从而提高对非 TEXT 列的查询性能。
  • 缺点：访问 TEXT 列需要一次额外的磁盘读取（指针跳转），性能开销更大。因此，要极力避免 SELECT * 查询包含 TEXT 列的表。

常见低分回答

1. 混淆字符和字节：“VARCHAR(255) 最多存 255 个字节”。这是错误的。N 代表字符数，实际字节数取决于字符集（如 latin1 是 1 字节/字符，utf8mb4 是 1-4 字节/字符）。
2. 绝对化优劣：“VARCHAR 总是比 CHAR 好，因为它省空间”。在存储定长数据（如 UUID）时，CHAR 因无长度前缀、无碎片风险，性能更优。
3. 不知行外存储：无法解释 VARCHAR 和 TEXT 在数据量大时的根本区别（行内 vs. 行外存储），这是区分初级和高级工程师的关键点。
4. 忽略索引限制：认为 TEXT 列可以像 VARCHAR 一样方便地创建索引，不知道必须使用前缀索引，也不知道前缀索引对于 LIKE '%keyword%' 这样的查询是无效的。
5. 误用 TEXT：为只需要存储几百个字符的字段（如备注）直接使用 TEXT 类型，而实际上 VARCHAR(500) 或 VARCHAR(1000) 更合适，因为它们默认行内存储，查询性能更好。

工程实践与优化

• 选型原则：使用能容纳数据的最小类型。例如，存储性别 'M'/'F'，用 CHAR(1) 而非 VARCHAR(10)。
• VARCHAR(N) 中 N 的选择：N 应该根据业务预估的最大长度来合理设置，而不是随意设为 255 或更大。虽然对存储空间影响不大，但更大的 N 会在内存排序、创建临时表时消耗更多内存。
• TEXT 列的查询优化：
  1. 避免 SELECT *：只在需要时才查询 TEXT 列，减少不必要的 I/O。
  2. 分离大字段：在极高并发的表中，可将 TEXT 或 BLOB 字段拆分到单独的“扩展表”中，通过主键关联，避免大字段影响主表的查询性能。
  3. 使用全文索引：对于 TEXT 内容的搜索需求，不要用 LIKE '%keyword%'（会导致全表扫描且无法利用前缀索引），而应创建 FULLTEXT 索引，并使用 MATCH() ... AGAINST() 语法进行高效搜索。
     sql

     ⧉⛶

      1ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
      2SELECT id, title FROM articles WHERE MATCH(content) AGAINST('MySQL performance' IN NATURAL LANGUAGE MODE);

• 执行计划分析：当查询涉及这些列时，通过 EXPLAIN 检查索引使用情况。特别是对于 TEXT 列的查询，要确认是否因为不当操作导致了全表扫描（type: ALL）。

常见追问与延伸题目

1. 追问：如果一个 VARCHAR(255) 的列，大部分只存了 10 个字符，某次 UPDATE 将其更新为 200 个字符，数据库底层会发生什么？

   • 回答要点：这可能会导致行溢出（Row Overflow）或页分裂（Page Split）。在 InnoDB 中，一行数据存储在一个数据页（默认 16KB）里。如果更新后的行变得太大，当前页无法容纳，InnoDB 会采取以下措施之一：
     • 页分裂：将当前页约一半的数据移动到一个新的数据页中，然后插入更新后的行。这是一个昂贵的 I/O 操作，会造成磁盘碎片，降低数据密度和扫描效率。
     • 行溢出：对于 COMPACT 和 REDUNDANT 行格式，当 VARCHAR 列过长时，InnoDB 会选择将部分数据存储到行外（溢出页），行内只保留前 768 字节和指向溢出页的指针。对于 DYNAMIC 和 COMPRESSED 行格式（MySQL 8.0 默认），则更倾向于直接将整个列存储到行外。
   • 无论哪种方式，都会带来性能开销。

2. 追问：既然 TEXT 不能很好地被索引，那对于文章内容的搜索，除了 FULLTEXT 索引，还有什么工业级的解决方案？

   • 回答要点：在大型应用中，通常会采用专业的外部搜索引擎，如 Elasticsearch (ES) 或 OpenSearch。
     • 工作流程：通过数据同步机制（如 Canal 监听 binlog，或使用 Logstash/Flink CDC）将 MySQL 中的数据实时或准实时地同步到 Elasticsearch 中。
     • 优点：ES 提供更强大、更灵活的全文检索能力，如分词、相关度排序、聚合分析、高亮显示等。其倒排索引（Inverted Index）结构专为快速文本搜索设计，性能远超 MySQL 的 FULLTEXT 索引。
     • 架构：这样形成了“业务存储（MySQL）”与“搜索服务（ES）”分离的架构，各司其职，扩展性更好。

3. 追问：MySQL 8.0 对 VARCHAR 和 TEXT 的处理与 5.7 有什么显著不同吗？

   • 回答要点：主要区别在于行格式（Row Format）的默认值和行为。
     • 默认行格式：MySQL 8.0 默认使用 DYNAMIC 行格式，而 5.7 默认是 COMPACT（在某些条件下）。
     • DYNAMIC 的行为：相比 COMPACT，DYNAMIC 行格式更倾向于将长的 VARCHAR 或 TEXT/BLOB 数据完全存储到行外（Off-page），行内只保留一个 20 字节的指针。这避免了 COMPACT 格式下将数据前 768 字节保留在行内导致 B-Tree 节点空间利用率低的问题。
     • 影响：在 8.0 中，即使是 VARCHAR 列，只要数据足够长，其存储行为也和 TEXT 类似，有助于保持 B-Tree 节点的紧凑，提高缓存效率。这模糊了长 VARCHAR 和 TEXT 在存储上的界限，但 TEXT 在索引限制和无长度声明上的特性依然存在。