Mysql大单表JSON优化

优化方案

MySQL 8.0.32 中，有几种方法可以优化存储 JSON 字符串的数据表。以下是一些建议，可以帮助您减少存储空间：

1. 使用压缩: MySQL 8.0 支持表级压缩，可以通过修改表来启用压缩。

ALTER TABLE your_table ROW_FORMAT=COMPRESSED;

2. 优化 JSON 存储: JSON 数据通常会有一些冗余，您可以考虑对 JSON 数据进行预处理，删除不必要的字段或缩短字段名。
3. 分区表: 分区表可以有助于更好地管理大表的数据，可以通过 RANGE、LIST、HASH 或 KEY 分区。

ALTER TABLE your_table PARTITION BY RANGE (TO_DAYS(creation_date)) (PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')),PARTITION p1 VALUES LESS THAN (TO_DAYS('2024-01-01')),PARTITION p2 VALUES LESS THAN (TO_DAYS('2025-01-01')),PARTITION p3 VALUES LESS THAN MAXVALUE
);

4. 使用适当的数据类型: 如果可能，尽量避免使用 JSON 存储大数据量，可以使用结构化的表来存储数据，这样更容易进行优化和压缩。
5. 外部存储: 如果 JSON 数据非常大，考虑将其存储在外部文件系统或对象存储中，如 Amazon S3，然后在表中只存储文件路径或索引。
6. 删除重复数据: 检查是否有重复的数据，删除重复数据可以节省大量存储空间。
7. 数据库清理: 定期清理和维护数据库，如删除无用的索引、归档旧数据等。
8. 使用 InnoDB 压缩: InnoDB 支持透明的数据压缩，可以启用表或索引级的压缩。

ALTER TABLE your_table ENGINE=InnoDB, ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

9. 调整 InnoDB 配置: 适当地调整 InnoDB 的配置参数，如 innodb_file_format, innodb_file_per_table 等，以获得更好的性能和存储效率。
10. 手动压缩 JSON 数据: 在插入数据之前，可以使用压缩算法（如 gzip）手动压缩 JSON 字符串，然后在查询时解压缩。

通过这些方法，您可以优化存储 JSON 字符串的数据表，从而减少存储空间。具体采用哪种方法可以根据您的实际情况和需求进行选择和组合。

使用 InnoDB 压缩

InnoDB 引擎支持表和索引的压缩，可以通过 ROW_FORMAT=COMPRESSED 来启用压缩。压缩可以显著减少存储空间，同时还能提高某些查询的性能（尤其是读取更多数据时）。

启用压缩

要启用压缩，可以在创建表时指定 ROW_FORMAT=COMPRESSED 和 KEY_BLOCK_SIZE。KEY_BLOCK_SIZE 指定压缩块的大小，通常可以设置为 1, 2, 4, 8, 或 16 KB。

CREATE TABLE your_table (id INT PRIMARY KEY,data JSON
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

对于已有表，您可以通过 ALTER TABLE 命令启用压缩：

ALTER TABLE your_table ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

KEY_BLOCK_SIZE 是 InnoDB 表和索引压缩时使用的一个参数，它指定压缩块的大小。该参数在启用 InnoDB 表压缩时非常重要，因为它直接影响到数据的压缩率和性能。

含义和使用

• KEY_BLOCK_SIZE 参数：定义压缩块的大小，以千字节（KB）为单位。有效的值通常为 1, 2, 4, 8, 或 16 KB。
• 压缩块的大小：指定的块大小决定了数据在磁盘上的存储方式。较小的块大小通常会有更高的压缩率，但可能会对性能产生负面影响，因为更多的块需要被管理和访问。较大的块大小通常会有较好的性能，但压缩率可能会较低。

选择合适的块大小

选择 KEY_BLOCK_SIZE 时，可以考虑以下因素：

• 数据类型和大小：如果您的数据比较小且重复性高，较小的块大小可能会提供更高的压缩率。对于较大的数据，较大的块大小可能会更合适。
• 性能需求：如果性能是关键考虑因素，较大的块大小通常会更好，因为它减少了压缩和解压缩的开销。
• 存储空间：如果存储空间有限且需要最大化压缩率，较小的块大小可能会更好。

示例配置的含义

使用 KEY_BLOCK_SIZE=8：

• 块大小为 8 KB：指定每个压缩块的大小为 8 KB。
• 压缩效率和性能的平衡：8 KB 的块大小通常在压缩效率和性能之间提供一个良好的平衡。它通常适用于大多数应用程序，但具体效果仍然需要根据实际数据和查询模式进行测试和调整。

总之，KEY_BLOCK_SIZE 是一个关键参数，用于调整 InnoDB 压缩表的压缩块大小，从而影响表的存储效率和性能。选择合适的块大小需要根据具体应用场景和数据特性进行权衡和测试。

调整 InnoDB 配置

适当调整 InnoDB 配置参数可以提高性能和存储效率。以下是一些重要的 InnoDB 配置参数及其含义：

innodb_file_format

这个参数指定 InnoDB 的文件格式。MySQL 8.0 默认使用 Barracuda 文件格式，支持表压缩和动态行格式。

SET GLOBAL innodb_file_format = Barracuda;

在 MySQL 8.0 中，innodb_file_format 变量已被废弃（deprecated），并且默认的文件格式已经固定为 Barracuda，因此执行 SHOW VARIABLES LIKE ‘innodb_file_format’; 返回为空是预期行为。
在 MySQL 8.0 中，不再需要手动设置 innodb_file_format，因为 Barracuda 文件格式是默认的且唯一支持的格式。这也是为什么即使您尝试查询这个变量，返回的结果会是空的。
如果您想确认当前的表使用的是 Barracuda 文件格式，您可以通过以下命令查看表的行格式：

SHOW TABLE STATUS LIKE 'your_table_name';

在输出中，Row_format 列会显示 Compressed 或 Dynamic，这表示使用的是 Barracuda 文件格式。

innodb_file_per_table

这个参数决定 InnoDB 是否为每个表使用单独的表空间文件。启用这个选项后，每个表的数据和索引将存储在独立的 .ibd 文件中。这可以更容易管理表的压缩和存储。

SET GLOBAL innodb_file_per_table = ON;

innodb_page_size

这个参数指定 InnoDB 页的大小，默认是 16KB。较小的页面大小可能有助于压缩率，但会增加开销。一般情况下，保持默认设置即可。

SET GLOBAL innodb_page_size = 16384;  -- 16KB

innodb_log_file_size

这个参数指定 InnoDB 日志文件的大小。较大的日志文件可以减少写入的频率，改善性能，但也会增加恢复时间。

SET GLOBAL innodb_log_file_size = 512M;  -- 512MB

innodb_buffer_pool_size

这个参数指定 InnoDB 缓冲池的大小。缓冲池用于缓存数据和索引，提高读取性能。根据系统内存大小进行调整，通常设置为系统内存的 70-80%。

SET GLOBAL innodb_buffer_pool_size = 8G;  -- 8GB

配置示例

在 MySQL 配置文件 (my.cnf 或 my.ini) 中进行这些设置：

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_page_size = 16384
innodb_log_file_size = 512M
innodb_buffer_pool_size = 8G

应用这些配置

修改配置文件后，您需要重启 MySQL 服务以使更改生效。

sudo systemctl restart mysql

通过以上调整和配置，您可以有效地减少存储空间，并在某些情况下提高性能。确保在更改配置前备份数据，并逐步测试这些调整对系统的影响。

案例

版本：8.0.32
text类型字段，底层为JSON字符串
全量数据: 35G -> 开启压缩后: 26G 压缩比=0.74，压缩率=0.26

2010-01-01数据: 18G -> 开启压缩后: 8.9G 压缩比=0.49，压缩率=0.51

读写

未开启压缩

Seconds % Task name

3441.68033 98% write task
0079.842993 02% read task

开启压缩后

Seconds % Task name

3442.119693 85% write task
0627.699276 15% read task
开启压缩+关闭binlog，性能没有太大的变化，cpu负载整体有降低

Seconds % Task name

3442.033828 85% write task
0624.982888 15% read task

CPU

4核心设备
未开启压缩：load avg 4.x
开启压缩后：load avg 7.x
cpu负载在优化webClient线程池后（8个IO-worker），稳定在3.x-4.x之间。并且任务完成时间没有波动，还是在3442.412387S左右。生产消息的队列也没有了满队告警日志"buffer full"。
也就是说cpu负载加大主要是因为webClient请求并行度过高导致的

小结

开启压缩后，CPU负载显著增高，写入性能稳定，读取性能显著降低，约增加了7倍。

多次实验验证在数据量达到350W+之后写入性能也会下降，导致应用程序操作并发受限，cpu使用率飙升，mybatis线程出现锁竞争，劣化严重导致应用程序宕机，同样配置环境，取消压缩表之后表现良好。
非压缩表在并发提升后，数据量达到300W左右时也会出现同样的劣化效果
其根本原因在于压缩数据的cpu成本

"web-client-consumer-7" #64 daemon prio=5 os_prio=31 cpu=108005.30ms elapsed=2343.21s tid=0x00007f8b55ba7600 nid=0xc203 waiting for monitor entry  [0x000070000b6fa000]java.lang.Thread.State: BLOCKED (on object monitor)at org.apache.ibatis.ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:1151)- waiting to lock <0x0000000703f319d8> (a java.lang.reflect.Method)at org.apache.ibatis.ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1958)"web-client-consumer-1" #50 daemon prio=5 os_prio=31 cpu=109368.51ms elapsed=2344.58s tid=0x00007f8b5fe31000 nid=0xb907 waiting for monitor entry  [0x000070000aee2000]java.lang.Thread.State: BLOCKED (on object monitor)at org.apache.ibatis.ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:1151)- locked <0x0000000703f319d8> (a java.lang.reflect.Method)at org.apache.ibatis.ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1958)

数据存在本地环境的客观因素，mysql与服务共用一台设备等等，以及mysql配置合理性问题，案例仅供参考，具体数据建议参考相关官方文档

TEXT 类型 vs JSON 类型

TEXT 类型

TEXT 类型用于存储长文本数据，包括 JSON 字符串。它适用于大多数文本存储需求，但对 JSON 数据的处理功能有限。
优点：

• 兼容性高：TEXT 类型在不同版本和工具中具有广泛的支持。
• 无额外开销：没有 JSON 数据类型的内部处理开销。

缺点：

• 缺乏内置功能：TEXT 类型没有 JSON 数据类型的内置函数和操作符，例如 JSON_EXTRACT、JSON_SET 等。
• 性能问题：大 JSON 数据的查询和操作可能会影响性能。

示例定义：

ALTER TABLE your_table ADD COLUMN json_text TEXT;

JSON 类型

JSON 类型是 MySQL 5.7+ 中的专用数据类型，用于存储 JSON 数据。它提供了丰富的功能来操作 JSON 数据。
优点：

• 内置函数：支持各种 JSON 函数，如 JSON_EXTRACT、JSON_SET、JSON_ARRAYAGG 等。
• 数据验证：MySQL 会验证 JSON 格式是否合法。
• 索引支持：可以对 JSON 字段创建虚拟列，并在虚拟列上创建索引，提高查询性能。

缺点：

• 性能开销：存储 JSON 数据时可能会有额外的开销。
• 兼容性问题：某些旧版工具和应用可能不完全支持 JSON 数据类型。

示例定义：

ALTER TABLE your_table ADD COLUMN json_data JSON;

选择建议

对于包含大 JSON 数据的字段，推荐使用 JSON 类型。以下是详细的理由和操作步骤：

推荐使用 JSON 类型

推荐理由：

• 数据验证：JSON 类型自动验证数据格式，确保数据符合 JSON 标准。
• 功能丰富：JSON 类型提供了丰富的 JSON 操作函数，适合需要对 JSON 数据进行操作和查询的场景。
• 未来兼容性：JSON 类型在未来版本中可能会得到更多支持和优化。

使用 TEXT 类型的情况

如果您的 JSON 数据是一次性写入且不需要经常查询或操作，可以继续使用 TEXT 类型
在这种情况下，您只需确保 JSON 数据在写入时是有效的，并且查询和操作的复杂度较低。

总结

类型	优点	缺点
TEXT	兼容性高；无额外的 JSON 数据类型开销	没有 JSON 数据类型的内置功能；查询性能较差
JSON	内置函数和操作符；数据验证；可以创建索引	存储 JSON 数据时有额外开销；兼容性问题

对于大 JSON 数据，推荐使用 JSON 类型，以利用 MySQL 的内置功能和优化。对于简单的文本存储，TEXT 类型也可以满足需求，但可能需要额外的处理步骤来管理 JSON 数据。