国家网站集约化建设试点方案,万盛经开区建设局官方网站,WordPress禁用邮件注册,网站后台如何做优化方案
MySQL 8.0.32 中#xff0c;有几种方法可以优化存储 JSON 字符串的数据表。以下是一些建议#xff0c;可以帮助您减少存储空间#xff1a;
使用压缩: MySQL 8.0 支持表级压缩#xff0c;可以通过修改表来启用压缩。
ALTER TABLE your_table ROW_FORMATCOMPRESS…优化方案
MySQL 8.0.32 中有几种方法可以优化存储 JSON 字符串的数据表。以下是一些建议可以帮助您减少存储空间
使用压缩: MySQL 8.0 支持表级压缩可以通过修改表来启用压缩。
ALTER TABLE your_table ROW_FORMATCOMPRESSED;优化 JSON 存储: JSON 数据通常会有一些冗余您可以考虑对 JSON 数据进行预处理删除不必要的字段或缩短字段名。分区表: 分区表可以有助于更好地管理大表的数据可以通过 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
);使用适当的数据类型: 如果可能尽量避免使用 JSON 存储大数据量可以使用结构化的表来存储数据这样更容易进行优化和压缩。外部存储: 如果 JSON 数据非常大考虑将其存储在外部文件系统或对象存储中如 Amazon S3然后在表中只存储文件路径或索引。删除重复数据: 检查是否有重复的数据删除重复数据可以节省大量存储空间。数据库清理: 定期清理和维护数据库如删除无用的索引、归档旧数据等。使用 InnoDB 压缩: InnoDB 支持透明的数据压缩可以启用表或索引级的压缩。
ALTER TABLE your_table ENGINEInnoDB, ROW_FORMATCOMPRESSED KEY_BLOCK_SIZE8;调整 InnoDB 配置: 适当地调整 InnoDB 的配置参数如 innodb_file_format, innodb_file_per_table 等以获得更好的性能和存储效率。手动压缩 JSON 数据: 在插入数据之前可以使用压缩算法如 gzip手动压缩 JSON 字符串然后在查询时解压缩。
通过这些方法您可以优化存储 JSON 字符串的数据表从而减少存储空间。具体采用哪种方法可以根据您的实际情况和需求进行选择和组合。
使用 InnoDB 压缩
InnoDB 引擎支持表和索引的压缩可以通过 ROW_FORMATCOMPRESSED 来启用压缩。压缩可以显著减少存储空间同时还能提高某些查询的性能尤其是读取更多数据时。
启用压缩
要启用压缩可以在创建表时指定 ROW_FORMATCOMPRESSED 和 KEY_BLOCK_SIZE。KEY_BLOCK_SIZE 指定压缩块的大小通常可以设置为 1, 2, 4, 8, 或 16 KB。
CREATE TABLE your_table (id INT PRIMARY KEY,data JSON
) ENGINEInnoDB ROW_FORMATCOMPRESSED KEY_BLOCK_SIZE8;对于已有表您可以通过 ALTER TABLE 命令启用压缩
ALTER TABLE your_table ROW_FORMATCOMPRESSED KEY_BLOCK_SIZE8;KEY_BLOCK_SIZE 是 InnoDB 表和索引压缩时使用的一个参数它指定压缩块的大小。该参数在启用 InnoDB 表压缩时非常重要因为它直接影响到数据的压缩率和性能。
含义和使用
KEY_BLOCK_SIZE 参数定义压缩块的大小以千字节KB为单位。有效的值通常为 1, 2, 4, 8, 或 16 KB。压缩块的大小指定的块大小决定了数据在磁盘上的存储方式。较小的块大小通常会有更高的压缩率但可能会对性能产生负面影响因为更多的块需要被管理和访问。较大的块大小通常会有较好的性能但压缩率可能会较低。
选择合适的块大小
选择 KEY_BLOCK_SIZE 时可以考虑以下因素
数据类型和大小如果您的数据比较小且重复性高较小的块大小可能会提供更高的压缩率。对于较大的数据较大的块大小可能会更合适。性能需求如果性能是关键考虑因素较大的块大小通常会更好因为它减少了压缩和解压缩的开销。存储空间如果存储空间有限且需要最大化压缩率较小的块大小可能会更好。
示例配置的含义
使用 KEY_BLOCK_SIZE8
块大小为 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; -- 16KBinnodb_log_file_size
这个参数指定 InnoDB 日志文件的大小。较大的日志文件可以减少写入的频率改善性能但也会增加恢复时间。
SET GLOBAL innodb_log_file_size 512M; -- 512MBinnodb_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 prio5 os_prio31 cpu108005.30ms elapsed2343.21s tid0x00007f8b55ba7600 nid0xc203 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 prio5 os_prio31 cpu109368.51ms elapsed2344.58s tid0x00007f8b5fe31000 nid0xb907 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 数据。
