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

MySQL 的数据页默认为 16 KB，而行记录最大为 65535 字节，如果某些字段较大可能会导致一页放不下一条记录而导致行记录溢出。

它的意思大概是，如果字段过长，可能会导致一页放不了很多条记录（如果使用的是 Compact Row Format，即使通过页分裂也需要至少存储 768 字节的数据和一个溢出页地址），不如直接把长字段放到别的表中，这样该表中的字段都是短字段，一页中可以放更多的数据。

nekolr

2020-08-27 18:19:44 +08:00

对，我说了这么多，其实根本原因就是一楼说的那样。

statement

2020-08-27 18:29:12 +08:00 via iPhone

这种手册有看的必要？

JasonLaw

2020-08-27 18:31:48 +08:00 via iPhone

@PopRain
@nekolr

我明白你们所说的，将不常用字段放在附加表可以让一页可以存储主表更多的纪录。可能我没有表达清楚我的问题，我最大的疑问是为什么要将 varchar 换为 text 呢？它们的表现基本是一样的。

JasonLaw

2020-08-27 18:33:56 +08:00 via iPhone

@statement 其实我是没怎么看的，因为它只说了应该怎么做，但是没有解释为什么这么做好，为什么这么做不好。

CRUD

2020-08-27 18:36:14 +08:00

varchar 有最大长度限制的，最大长度不够用才换 text

JasonLaw

2020-08-27 18:46:25 +08:00 via iPhone

@CRUD https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html 关于 varchar：A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. 关于 text：A TEXT column with a maximum length of 65,535 (216 − 1) characters.

OysterQAQ

2020-08-27 19:07:05 +08:00

除非你查询直接到落到表上而不是索引上，我记得大字段是会自己独立出去的高性能 mysql 上有瞄一眼

CRUD

2020-08-27 19:28:05 +08:00

@CRUD #8 丢人了

比 varchar 存储空间更大的是 longtext，查了一下，看到的比较也只是说明索引的问题和在不知道长度的情况下使用 text，没找到与长度超过 5000 时 varchar 换 text 的答案..

cnoder

2020-08-27 19:43:48 +08:00

为什么超过 5000 要改为 text ？
varchar 会算在行记录 65535 字节中，text 不占用
为什么 text，独立出来一张表？
我也不懂

JasonLaw

2020-08-27 19:53:30 +08:00 via iPhone

@cnoder 你说“ varchar 会算在行记录 65535 字节中，text 不占用”，text 不占用是什么？能具体解释一下吗？

zsdroid

2020-08-27 20:23:23 +08:00

SELECT `desc` FROM `table` GROUP BY `other_id` ORDER BY `id`;

desc 字段如果是 text 类型，会创建磁盘临时表

BQsummer

2020-08-27 23:09:52 +08:00

1. 用 text 一般是长度限制
2. 分表是因为会影响 io,哪怕你 select 的时候不选这一列(1 楼是对的)

JasonLaw

2020-08-27 23:30:35 +08:00

@BQsummer #15

你说“用 text 一般是长度限制”，什么长度限制？我在 9 楼已经回复了关于 varchar 和 text 容量的问题，它们的最大容量是一样的。

关于“使用附加表存储 text”，如果不是顺序获取 clustered index 中的 index record 之类的话，其实根本没有什么影响，比如直接通过主键 id 获取唯一的一行数据的时候。

littlewing

2020-08-28 00:03:20 +08:00

估计是怕有人老是 select *

crclz

2020-08-28 07:47:10 +08:00

因为其他字段相当于元数据，TEXT 相当于数据。

JasonLaw

2020-08-28 07:56:02 +08:00 via iPhone

@crclz 你在说什么？能够详细解释一下吗？

atonku

2020-08-28 08:35:06 +08:00

因为他们都不遵循这个规定

xuanbg

2020-08-28 08:51:23 +08:00

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

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

Infernalzero

2020-08-28 09:56:03 +08:00

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

JasonLaw

2020-08-28 10:02:59 +08:00

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

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

l00t

2020-08-28 10:12:31 +08:00

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

Jrue0011

2020-08-28 10:15:24 +08:00

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

wakzz

2020-08-28 10:28:33 +08:00

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

Aresxue

2020-08-28 10:36:18 +08:00

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

Aluhao

2020-08-28 10:42:57 +08:00

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

crclz

2020-08-28 10:45:37 +08:00

@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

2020-08-28 10:47:41 +08:00

@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

2020-08-28 10:50:41 +08:00

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

519718366

2020-08-28 10:53:02 +08:00

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

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

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

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

xsm1890

2020-08-28 11:10:23 +08:00

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

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

l00t

2020-08-28 12:07:26 +08:00

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

Aresxue

2020-08-28 12:22:15 +08:00

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

JasonLaw

2020-08-28 13:14:44 +08:00

@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

2020-08-28 13:51:05 +08:00

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

xsm1890

2020-08-28 13:54:46 +08:00

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

xiangbohua

2020-08-28 13:57:37 +08:00

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

listenerri

2020-08-29 09:33:03 +08:00

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