为什么阿里巴巴的 Java 开发手册说 text 要独立出来一张表？

[JasonLaw]

在p3c/Java 开发手册（嵩山版）.pdf at master · alibaba/p3c中，它说：

[强制] varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

它说的“避免影响其它字段索引效率”到底是什么？

关于 varchar 和 text，我找了相关的资料。

建表规约 text 类型独立出一张表避免影响其他字段索引效率？ · Issue #641 · alibaba/p3c：这是相关人员的回复，但是并没有解答我的问题。

在MySQL :: MySQL 8.0 Reference Manual :: 15.10 InnoDB Row Formats - COMPACT Row Format中，它说“Tables that use the COMPACT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages.”。mysql - In what cases are BLOB and TEXT stored in-line on InnoDB? - Database Administrators Stack Exchange也讨论了这个问题。

那么，阿里巴巴所说的到底是什么？真的有必要这么做吗？

[xuanbg]

text 这种属于历史遗留，现在已经没必要使用了。

其实我的建议是：超过 4K 的数据使用对象存储才是正经。2-4K 的数据如果数量较多(10w 以上)，建议使用 NoSQL 。

[Infernalzero]

其实你帖的那个官方回复已经解释了，只是你缺乏了一些基础知识的理解，建议看下 MySQL 技术内幕这本书里有介绍的。
当某一行存储的数据过大时，InnoDB 就不会把所有数据都存放在数据页，而是将数据存放在 Uncompressed BLOB Page 或者 Compressed BLOB Page 当中，在数据页中通过指针指向 BLOB Page，这种现象叫做行溢出。
因为 MySQL 之前的版本默认行格式都是 compact，text 场景很大概率你数据页要使用的字节数量肯定多，这样每一页可以存的行数变少，索引效率自然也低了

[JasonLaw]

@Infernalzero #22 我在 6 楼已经说了我明白你所说的内容，可能我的题目取得有点不好，除了原文提到的问题，我遗漏了我最关心的问题，为什么使用 text 替换 varchar，后来我也加了附言。你知道为什么要使用 text 替换 varchar 吗？

还有，你说的“你缺乏了一些基础知识的理解”是什么？我想知道。

[l00t]

@Infernalzero #22 每一页可以存的行数变少，为什么索引效率就低了？

[Jrue0011]

https://dev.mysql.com/doc/refman/8.0/en/optimize-character.html
官方貌似提供了一些关于字符串的优化，拆分表应该是第四条
至于大字符串用 text 换 varchar，可能是把第三条反过来，只不过官方推荐用 blob /手动 doge

[wakzz]

3 楼说的很对，无论是 text 还是 varchar，当长度超长（看表结构，有的是 8K 左右溢出页，有的是 768 字节溢出页）时会页溢出，然后导致聚簇索引的数据疏松，扫描需要更多的 IO 成本。
因此需要尽量避免热点数据所在的表有溢出页，所以将长度超长的 text 或 varchar 字段独立出来放到一个表中。

[Aresxue]

对于 innodb 来说 varchar 会和其他列存在一起, 而 text 存的是指针, 实际数据是在别的地方的，至于独立一张表影响索引效率的说法应该是历史问题，新版本里面是没有必要的，且不独立出去也不会影响索引的效率

[Aluhao]

@cnoder 其实很容易理解的，如 表 1 text 字段值很大，放在原 表 1 存放的话，这张表查询以频繁的话效率就低下了，如果把 text 字段 新建议一个表存放就可以减少 表 1 表大小，查询速度自然快些。

[crclz]

@JasonLaw 这是一个很基础的问题。

所有的拆分都应当按照业务来进行拆分，而按照 元数据-数据 这种拆分方式，又能够比较好地符合业务边界。

我们假设一个 Article 表 (article_id: int, creator_id: int, created_at: int, is_private:int , content: TEXT)

那么，如果不拆分会存在什么问题？
- 如果 content 较短，不会有明显的性能问题
- 如果 content 较长，**一部分业务操作的性能会受到影响**

那么，对于 Article 实体，有哪些业务操作呢？
1. 修改文章内容(content)
2. 修改是否是私密文章(is_private)

那么，如果不拆分，2 号业务原本不需要获取庞大的 content，所以 2 号业务的性能就会受到很大的影响。因为 ORM 大多数是对实体整存整取。并且，即使 ORM 不读取 content，数据库的性能也要受损，因为行占用的空间变大了）

所以，这种拆分能够优化 2 号业务的性能，避免取太多不需要的数据。
---

从更加抽象的角度来看，1 号业务是关于“数据”的业务，2 号业务是关于“元数据”的业务。

为什么我开头说，所有拆分要按照业务拆分？举个例子，在一个新的项目中，对于 Article 实体，如果所有的业务操作都要依赖于数据和元数据（这种情况罕见），这个时候，即使数据再大，都不需要进行拆分。

总结一下，拆分有 2 个条件：
1. content 较长
2. 存在其他不依赖于 content，但依赖于其他字段的业务

为什么要按照阿里这样拆分？
1. 阿里的拆分恰好是 元数据-数据 的拆分
2. 元数据-数据 这种拆分方式，恰好能够比较好地符合业务边界。

[JasonLaw]

@Aresxue #27

https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-compact - Tables that use the COMPACT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages.

https://dev.mysql.com/doc/refman/8.0/en/optimize-character.html - If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.

[Aluhao]

其实很有必要这么做的，我设计数据表都是按这原则来，关键常用查询频繁的数据放在一个表 [表 1] 上面，把附加的数据放在 [表 1 附加表] 上面（插入的时候带上 [表 1] 递增 [ID] 关联），这样我们去查询 [表 1] 的时候那是非常快的，如果查询单条就需要整合 [表 1 附加表] 一起查询出来（查询二次），如果只查询列表的话要先查询 [表 1] 然后取得递增 [ID] 然后 [表 1 附加表] IN(1,2,3)出来后整合查询出来（查询二次），这样几千万数据一点压力都没。

[519718366]

不从数据库角度，从业务或者后端开发的角度简单说下我的理解：

我自己在建表时使用 varchar(N)是我们对这个字段有预期的或者业务上有限制的，比如昵称不能超过多少字符，推荐语不能超过多少字，不然页面展示效果不行..

当你一个 varchar>=5000 时，大概率上你已经对这个字段失去了可预期上的控制吧，比如爬虫存一些描述性文案，所以不如直接改成 text 不限制长度

阿里的 java 开发手册，我个人也认为他是从一个后端开发的角度总结的一套经验，比如数据不要外键，应用代码里做逻辑外键。

[xsm1890]

1.关于 varchar 用 text 替换，我想到一个角度就是空间碎片化及重用问题。MySQL 的数据删除在数据页中并不是把数据擦除，而是数据头中的删除标记置为 1，同时标记为可重用。varchar 变长根据需要分配长度，例如插入两条数据，删除第一天，再插入第三条，此时如果第三条的长字段更长的话，空间没法重用。如果大量发生的话，空间的使用效率及碎片化会是个问题。

2.关于索引效率。innodb 对数据的查找的操作最小单位是数据页，先把数据页加载到内存，然后根据页中的稀疏目录及双向链表查找到具体的记录。而索引存储的是数据所在的数据页地址。所以每一个数据页能存储的行数变少后索引效率就低了 @l00t

[l00t]

@xsm1890 #33 可是索引是个 key-value 结构，几个不同的 key，value 值是同样的还是不同的有什么关系么…… 从索引中找到数据页地址，跳到数据页后再在页内查记录，一个记录更多，一个记录更少，那不是记录少更容易查到吗？

[Aresxue]

@JasonLaw 默认是 dynamic 的，不要照本宣科

[JasonLaw]

@Aresxue #35 我在主题正文就已经说了 COMPACT Row Format 。什么叫不要照本宣科？难道你所说的东西不用有官方文档的支撑吗？如果你的答案是不用的话，那没什么了。

还有，你在 27 楼说“对于 innodb 来说 varchar 会和其他列存在一起”，假定你使用的是 DYNAMIC Row Format，有什么材料可以证明你所说的吗？反正我在 https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-dynamic 看到的是“When a table is created with ROW_FORMAT=DYNAMIC, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page.”。

[ppyybb]

感觉找几个版本的，然后动手试试各种索引和 query 下的效率更有说服力
看上去用在他说的场景下，用 text 和用 varchar 也没啥性能啥的差异

[xsm1890]

@l00t 怪我观点表述的不够详细。页内查找是在内存中进行的，相对于整个流程来说影响非常小。当单查找某一条数据的时候，或者说查询的数据在同一个数据页的时候，确实像你说的一样，没有变化。但是关系型数据库范围、多条件查找等的情况是非常常见的，这种时候效率确实是有影响的。说白了就是索引查找次数，读盘及 io 的次数不一样。

[xiangbohua]

@statement 看完之后自己花时间搞清楚背后的原因，大概会提高不少技术吧

[listenerri]

那这手册跟“留图不留种”差不多[斜]