Mysql大单表JSON优化

于 2024-08-04 06:21:22 发布
阅读量1k 收藏 11
点赞数 17
分类专栏： 数据库 文章标签： mysql json 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010597819/article/details/140901141
版权

优化方案

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

  1. 使用压缩: MySQL 8.0 支持表级压缩，可以通过修改表来启用压缩。
ALTER TABLE your_table ROW_FORMAT=COMPRESSED;
  1. 优化 JSON 存储: JSON 数据通常会有一些冗余，您可以考虑对 JSON 数据进行预处理，删除不必要的字段或缩短字段名。
  2. 分区表: 分区表可以有助于更好地管理大表的数据，可以通过 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
);
  1. 使用适当的数据类型: 如果可能，尽量避免使用 JSON 存储大数据量，可以使用结构化的表来存储数据，这样更容易进行优化和压缩。
  2. 外部存储: 如果 JSON 数据非常大，考虑将其存储在外部文件系统或对象存储中，如 Amazon S3，然后在表中只存储文件路径或索引。
  3. 删除重复数据: 检查是否有重复的数据，删除重复数据可以节省大量存储空间。
  4. 数据库清理: 定期清理和维护数据库，如删除无用的索引、归档旧数据等。
  5. 使用 InnoDB 压缩: InnoDB 支持透明的数据压缩，可以启用表或索引级的压缩。
ALTER TABLE your_table ENGINE=InnoDB, ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
  1. 调整 InnoDB 配置: 适当地调整 InnoDB 的配置参数，如 innodb_file_format, innodb_file_per_table 等，以获得更好的性能和存储效率。
  2. 手动压缩 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 数据。

太阳伞下的阿呆
关注
专栏目录
mysql8 json性能优化_Explain - 非官方 MySQL 8.0 优化指南 - 学习笔记
weixin_39783633的博客
02-18 941
EXPLAIN描述了 MySQL 是如何计划，去执行给定的查询的。即这是一个预执行的考虑，不要和查询性能的分析混淆了。在评估了许多可能的执行方式后，EXPLAIN 打印出了最佳的执行计划。在 “possible_keys” 中有可以简明地看到其他可能选择的计划，但一般来说你需要在 “OPTIMIZER_TRACE” 看这些信息。我们推荐一直使用EXPLAIN FORMAT=JSON，因为它会打印出...
mysql navicat premium，最新版本，支持mysql5.7 json格式数据，解压即可直接使用
12-04
mysql navicat premium，最新版本，支持mysql5.7 json格式数据，解压即可直接使用
mysql json特性优化
u013008898的博客
12-04 484
mysql json特性优化
MySQL虚拟列及json优化
雪辉博客
05-09 712
文章目录
Good Idea, 利用MySQL JSON特性优化千万级文库表
qq_51033936的博客
03-09 1203
记录一下sql调优中学到的东西,使用mysql的json列的存储方式,将一个200ms的查询减少到了20ms, 这是一个非常好的办法, 在以后的sql调优中, 不只是索引可以办到, 不妨试试json, 也许效果更好
JSON性能优化
m0_66970275的博客
01-03 918
**解析优化**: 有些情况下，你可能不需要解析整个JSON文档，只需要特定部分的数据，你可以优化解析过程只提取你需要的信息。- **流式解析**: 对于大型JSON文件，使用支持流式处理的库，这样可以边读取边解析，而不必一次性加载整个文件到内存中。- **选择合适的解析库**: 不同的JSON解析库有不同的性能特点，选择一个适合你的需求和编程环境的高效解析库。- **缓存**: 对于不常变化的数据，可以在客户端或服务器端缓存解析后的JSON数据，以减少重复解析的需要。
简单谈谈MySQL5.7 JSON格式检索
09-09
总的来说，在MySQL 5.7中，虽然InnoDB存储引擎已经原生支持JSON格式的数据存储，但依然需要借助虚拟列和索引来优化JSON数据的查询性能。这背后实际上是一种“空间换时间”的策略，它通过冗余存储JSON列中的关键信息...
mysql_json_JPAmysqljson_mysqljsonjpa_MYSQL_mysql支持json_
10-02
MySQL数据库在近年来增加了对JSON数据类型的原生支持，这一特性极大地扩展了其在现代Web应用程序中的应用潜力。JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，它以其易于人阅读和机器解析的特点，...
MySQL操作之JSON数据类型操作详解
12-16
上一篇文章我们介绍了mysql数据存储过程参数实例详解，今天我们看看MySQL操作之JSON数据类型的相关内容。 概述 mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql...
idea Java 解析json文件并导入mysql数据库
11-10
在Java开发环境中，IntelliJ IDEA（简称Idea）是一个非常强大的集成开发工具，而处理JSON数据和与MySQL数据库交互是常见的任务。本教程将详细讲解如何在Idea中使用Java解析JSON文件，并将数据有效地导入到MySQL...
mysql数据库优化
Jsonstan_Z的博客
04-02 162
sql语句优化 应尽量避免在 where 子句中对字段进行 null 值判断，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值 应尽量避免在 where 子句中使用!=或<>操作符， MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。 应尽...
MySQL中大字段列压缩
sharkdoodoo
03-13 3671
最近MySQL遇到一个表，有一个字段是存储计算模型的所有值，是一个JSON长字符串，存储为longtext，
MySQL遇上JSON 一文处理Mysql中的JSON数据 Mysql如何处理嵌套的JSON结构
最新发布
赵KK的博客
04-26 1416
在这篇文章中，我们探讨了MySQL中JSON数据类型的增删改查操作，以及它们的运行原理和应用场景。JSON数据类型为数据库设计带来了新的灵活性和可能性，作为一名Java架构师，掌握这些技能将极大地提升你的技术实力。
MySQL5.7新特性之JSON类型
热门推荐
Vince的修炼之路
10-26 1万+
随着NOSQL数据库的广泛应用，可扩展的存储方式在关系型数据库中也有了很好的支持，最新的MySQL5.7中就新增加了一个数据类型：JSON 使用示例： 插入一条数据 insert into vince.test(`data`) values('{"name":"tomcat","age":15}'); 更新数据 UPDATE `vince`.`test` SET `data` =
Mysql存储json格式数据需要掌握的
怪咖@的博客
05-03 8133
最近做的一个 填报项目，由于填报的字段比较多于是便在数据库当中使用了longtext类型的字段直接存储json数据。为什么选择直接存储json数据？业务比较复杂，涉及到很多 一对多 的多表关联，假如拆分存到各个业务表，那么每次查询修改是非常繁琐的，存储json一张表即可解决。字段全部由前端来定，减少了前后端联调时间。避免填报内容需求频繁调整而不断修改数据库结构，比如加字段，减字段这些都是经常有的事，完全可以让前端全权负责，你存什么我就给你返回什么。但是这里我也是有一点好奇，既然存储为json。
解锁Mysql中的JSON数据类型，怎一个爽字了得
俺叫赵小邪的博客
02-20 1325
面对数据结构的复杂多变，解锁使用MySql的JSON数据类型，灵活处理非结构化或半结构化数据的存储和处理。
mysql5.7 json的一些用法
qq_22994783的博客
12-04 388
配置表添加主键 json里面每个表多加一层以主键作为key值 insert: order_no存在 追加 -----json数组 update tracking_order set order_info=json_array_append(order_info, ‘$.data’, cast(’{“size”: “5”, “type”: “m”, “so_no”: “345”}’ as json)...
JSON常见几种压缩方式
yyongsheng的博客
08-03 8226
经过上面的常规操作，我们的json文件大小减少到了405kb387KB但是比刚才未使用压缩算法的原始数据1067KB小了整整662KB压缩率为62.04%，压缩后体积为原来的37.95%，也是不错的！经过上面的常规操作，我们的json文件大小减少到了387KB比刚才未使用压缩算法的原始数据1067KB小了整整680KB压缩率为63.73%，压缩后体积为原来的36.27%优化步骤体积1.未处理的原始json2.13MB2.将JSON压缩成一行，去掉换行和空格字符1.39MB。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值