为什么mysql不建议存放大字段？

## 为什么 MySQL 不建议存放大字段？ 为了清楚大字段对性能的影响，我们需要了解 InnoDB 存储引擎的处理方式： ### 1. 一些知识点 #### 1.1 行记录存储格式 在 InnoDB 1.0.x 版本之前，InnoDB 提供了 Compact 和 Redundant 两种格式来存放行记录数据，称为 Antelope (羚羊) 格式。对于 BLOB、TEXT、VARCHAR(5120) 这样的大字段，InnoDB 只会存放前 768 字节在数据页中，其余的数据存储在溢出段中。最大 768 字节是为了便于创建前缀索引。由于溢出段会占用额外的 page，即使只多一个字节也会使用整个 page 来存储剩余字节，浪费空间。 #### 1.2 Barracuda 文件格式 MySQL 5.1 引入了 Barracuda 文件格式，拥有新的两种行格式：Compressed 和 Dynamic。对 BLOB 字段采用完全溢出的方式，数据页中只存放 20 字节，其余数据存放在溢出段中。因此，建议避免使用 BLOB、TEXT、超过 255 长度的 VARCHAR 列类型。 #### 1.3 Page 大小和存储限制 InnoDB 的 page 大小默认为 16KB。由于 InnoDB 存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应有两行记录，单行数据不能超过 8KB。但实际情况可能更小，因为需要存储 InnoDB 内部数据结构。InnoDB 的 data page 在写入新数据时，会预留 1/16 的空间以减少频繁新增 data page 的开销。 #### 1.4 行格式影响 COMPRESSED 行格式相比 COMPACT 能节省大约 50% 的存储空间，但会导致 TPS 下降 90%，因此强烈不推荐使用 COMPRESSED 行格式。通常，BLOB 类型数据会存放在溢出段中，但关键点在于 page 中是否能存放两行数据。 #### 1.5 命中率问题 MySQL 在操作数据时，以 page 为单位。不必要的数据（大字段）会随着需要操作的数据一同被读取到内存中，降低内存利用率，增加随机读的频率。 ### 2. 对 TEXT/BLOB 大字段类型的影响 #### 2.1 Compact 行格式 变长大字段类型包括 BLOB、TEXT、VARCHAR，其中 VARCHAR 列值长度大于某数 N 时也会存溢出页。对于 InnoDB，内存是极为珍贵的，如果把 768 字节长度的 BLOB 都放在数据页，会减少能缓存的行数，降低索引效率。 #### 2.2 Dynamic 行格式 Dynamic 行格式根据行大小决定列存储是否放到 off-page 页。TEXT/BLOB 列 ≤40 字节时总是存放于数据页中。这种方式避免了 Compact 行格式将大列值放到 B-tree Node。 #### 2.3 压缩行格式 Compressed 物理结构与 Dynamic 类似，但对表的数据行使用 zlib 算法进行压缩存储。在 long BLOB 列类型较多的情况下使用，可减少存储空间但需要更高的 CPU。 ### 3. 对 TEXT/BLOB 型字段存取优化 #### 3.1 压缩与合并 a. 使用 Barracuda 文件格式，将大字段完全存放在溢出段中。 b. 使用 COMPRESS() 压缩后再存为 BLOB。 c. 将多个类 BLOB 字段组合压缩存储。 d. 避免使用 TEXT，使用 VARCHAR 满足需求。 #### 3.2 拆分 将主表拆分为一对一的两个关联表，将大字段单独放到另一张表，使得数据页能够存放更多行，提高内存命中率。应用程序需要额外维护大字段的子表。 ### 总结 为了优化 MySQL 在存储大字段时的性能，应尽可能避免使用 BLOB、TEXT、超过 255 长度的 VARCHAR 列类型。可以通过压缩与合并、拆分表等方式提高内存命中率和数据存取效率。如果实际业务中确实需要存储大字段，可以考虑以下几点： 1. 尽可能将数据序列化、压缩后存储在同一个列里，避免多次 off-page。 2. 使用 VARCHAR 满足需求，避免使用 TEXT。 3. 根据每个列最大长度排列组合后拆分成多个子表，使每个子表总行长度小于 8KB，减少 off-page 频率。 通过优化数据库存储方式，可以显著提升数据库性能。

### 为什么 MySQL 不建议存放大字段？ 在 MySQL 中，处理大字段（如 `TEXT` 类型）可能会对性能产生影响。以下是一些详细的原因和解决方案。 #### 1. InnoDB 页大小限制（innodb_page_size） InnoDB 引擎下，有一个参数 `innodb_page_size`，默认值是 16k。 ```sql SHOW VARIABLES LIKE '%innodb_page_size%'; ``` ```plaintext innodb_page_size 16384 ``` #### 2. 字段长度限制 65535 字节 在创建表时，所有字段的总长度不能超过 65535 字节。例如，使用 `utf8` 编码时，一个字符占用 3 个字节，65535/3 ≈ 21845。因此，可以创建两个 `varchar(10000)` 字段，但如果超过这个限制，就会报错。 ```sql -- 创建示例表 CREATE TABLE example ( col1 VARCHAR(10000), col2 VARCHAR(10000) ) CHARSET=utf8; ``` 若每个字段长度非常大，超过限制，会自动转换为 `MEDIUMTEXT` 类型。 #### 3. 溢出页（off-page） `TEXT` 和 `BLOB` 字段的长度不计算在 65535 字节限制内，实际存储在溢出页中，并且每个字段有单独的溢出页。聚簇索引页只存储 20 字节的指针指向溢出页（前提是 `innodb_file_format=Barracuda` 且 `row_format=dynamic` 或 `compressed`）。 ```sql SHOW TABLE STATUS IN your_database/your_table; SHOW VARIABLES LIKE '%format%'; ``` #### 4. 每页必须有 2 行数据 InnoDB 是索引组织表（IOT），存储结构是 B+ 树，叶子节点是双向链表，每页至少要有 2 行数据。默认 `innodb_page_size` 是 16k，因此每行数据大小不能超过 8k，比创建表时限制的 65535 字节更小。 #### 5. `varchar` 和 `text` 的存储差异 并不是所有 `varchar` 都不会溢出，所有 `text` 都会溢出。例如，在 `compact` 压缩方式下，`char(255)` 使用 `utf8` 编码占用 765 字节（每字符 3 字节），但使用 `utf8mb4` 编码占用 1020 字节（每字符 4 字节），会导致溢出。 ### 场景分析及影响 #### 1. 查询时不包含 `text` 列 根据知识点 3，这种情况下几乎不影响查询性能。因为 `text` 字段存储在溢出页中，仅有 20 字节存储在主索引中。如果用的是 Percona 版本，可以考虑将不常查询的 `text` 列独立成一张表，使用 tokudb 引擎和行压缩以减少存储空间。 #### 2. `varchar(2000)` vs `text` 如果字段长度不会超过 2000，建议直接使用 `text`，让 MySQL 决定存储位置。如果只有 200 个字符，则不要用 `text` 替代 `varchar(200)`。 #### 3. 同时查询多个 `text` 字段 由于 `text` 字段存储在不同的溢出页中，需要读取多次。因此，尽量避免同时查询多个 `text` 字段。 #### 4. 对 `text` 字段建立前缀索引进行 `like` 查询 这种情况下，选择 `compact` 压缩方式。 ### 总结 - **避免大字段**：尽量避免在表中存储大字段，如 `TEXT` 类型。 - **使用合适的类型**：根据字段实际长度选择合适的类型，如 `varchar` 或 `text`。 - **独立存储**：将不常用的大字段独立成一张表，可以使用专用引擎（如 tokudb）和压缩方式。 - **注意查询性能**：尽量避免同时查询多个大字段。 通过合理设计和选择，可以优化 MySQL 的性能并避免大字段带来的问题。