我想深入了解JSON和TEXT到底是如何存储在MySQL InnoDB中的。我在一些地方听说过，对于这些类型，只有一个指针存储在行中，而实际数据驻留在磁盘的其他地方。
- 这些类型的长度（特别是在JSON中）是否会影响它们是存储在行中带有指针的不同位置（在JSON大小很大的情况下）还是存储在没有任何指针的行的相同位置（在JSON大小很小的情况下）？
- 如果指针总是被存储，那么对于任何需要存储的JSON文档，如果不将数据类型保持为JSON（因为数据类型名称的原因，这似乎是存储JSON的正确类型选择，即使JSON的大小很小），那么读取性能就会受到严重影响，因为现在每行都必须从单独的位置获取其JSON文档。
- 提供
- 我总是在MySQL中插入有效的JSON
- 我只阅读完整文档，不查询JSON
- JSON的大小是可预测的，并且小于1000个字符
从读取性能的Angular 来看，与JSON数据类型相比，VARCHAR是否是更好的选择，因为现在数据将与行沿着驻留，而不是在单独的位置，从而节省了额外的磁盘页提取？
- 是否有任何度量标准来衡量JSON与VARCHAR对读取性能的影响程度？
- 如果要使用JSON列，为了避免多次基于指针的读取，将所有JSON保存在单个列中而不是创建多个JSON列是否是更好的选择？
- 由于基于指针的JSON提取导致的额外磁盘读取对读取性能的影响是否可以忽略不计，以至于我不应该考虑这种优化（考虑到我正在处理可能具有数百万到数十亿行的表的设计）？
1条答案
按热度按时间9rbhqvlz1#
在MySQL 5.7和8.0中，JSON是BLOB的子类型https://github.com/mysql/mysql-server/blob/8.0/sql/field.h#L143-L150
（我不得不问这个版本，因为有些人在真正使用MariaDB的时候会把问题标记为mysql。MariaDB对JSON的实现与MySQL不同，我不知道MariaDB是如何存储它的。）
如果存储有效的JSON，则应该使用JSON类型（如果存储JSON的片段，则不能使用JSON类型）。
BLOB、TEXT、VARCHAR、VARBINARY以及JSON在InnoDB中的存储方式都是相同的，因此选择VARCHAR而不是JSON没有任何优势。
MySQL5.7和8.0中默认的InnoDB行格式都是DYNAMIC，所以我将假设您使用默认的行格式进行评论，请参见https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html#innodb-row-format-dynamic了解相关描述。
如果给定的字符串（BLOB/TEXT/VARCHAR/VARBINARY/JSON）足够短，可以容纳在40个字节内，则此行格式将其与其他列内联存储在同一页上。否则，它将字符串存储在一个或多个附加页上，并在主页上存储指向第一个附加页的20字节指针。因此，读取这些数据类型之一 * 可能 * 需要多次页读取。但这取决于长度。
每页的大小是固定的（默认为16 KB）。如果字符串太长，无法容纳在一个附加页中，则将其拆分为多页，并且这些页不一定是连续的。每页都指向下一页以继续字符串。
我无法回答您感兴趣的指标。存储没有性能;这与你将要运行的特定查询有关。如上所述，它还取决于你的JSON文档的长度。它还可能取决于你的服务器能力、可用于缓存页面的RAM量以及其他争用RAM的并发查询。你要得到这个问题的准确答案的唯一方法是自己在自己的服务器上使用自己的数据运行一些测试。
我也无法回答将数据分离到多个JSON文档中与将它们合并到一个文档中是否更适合您的情况。一个原因是，您的查询模式可能自然需要更频繁地使用JSON数据的一个子集，因此将其单独存储将是一个优势。但如果您总是需要读取整个JSON内容，那么就没有理由不把它们组合成一个。没有人可以在栈溢出上为你回答这个问题，因为这取决于你的查询需要。