解决因字段过长使MYSQL数据解析超时导致线上CPU告警问题

1.首先发生CPU告警 占有率到98.8%

2.DBA找到最终的原因是这条SQL导致

select * from trade_order_sku_071 where order_id in ( 'P760205793431071471' ) and user_id in ( '67d9158c000000000e0134ff' );”

3.观察系统指标，发现CPU和网络同时飙升，同时网络出口带宽最高峰接近2GB/s。

4.使用EXPLAIN分析 执行计划没问题，扫描了99行。ref查询，使用了key。

5.发现问题 attribute(属性) 只找到了一行，发现attribute字段特别长，并且是JSON字段

6.查看表结构

attribute(属性)字段是json结构

7.并发复现该问题 使用32并发就将cpu拉满 抓取火焰图查看CPU到底消耗在哪里了？

8.如何修复？

1.业务表示这个字段无法拆分，先暂时不考虑这个修复方式。

2.业务有上下游关系，QPS也只能暂时限流，不能保证100%流量不增加，潜在的风险还是存在。

3.短期能不能修改成text或者blob，然后再数据库不进行解析，当成一个string存储？那就再线下环境进行验证。

4.长期来看，select大字段肯定要治理掉。

9.解决办法

验证将JSON修改为BLOB

alter table trade_order_sku_071 MODIFY COLUMN attribute BLOB;

10.最终效果

更改前

1.进行压测，QPS可以提升到1078了。

2.网络使用的localhost2GB/s 80%的CPU都消耗在了将结果集按照json格式进行序列化上了。

更改后

再次进行压测，QPS可以提升到3600了。

此时的网络使用的localhost已经10GB/s了，差不多到了瓶颈。可以看到一半的CPU都在网络侧了。

将 trade_order_sku_071 表中的 attribute 列从 JSON 类型修改为 BLOB 类型，主要是为了优化查询性能，特别是在处理包含大量数据的字段时。

---

🧩 JSON 与 BLOB 类型的区别

✅ JSON 类型的特点：

• 结构化存储：MySQL 的 JSON 类型会将数据转换为内部的二进制格式，便于快速访问文档元素。 (MySQL开发者专区)

• 自动验证：插入的数据会进行 JSON 格式的验证，确保其合法性。

• 支持函数操作：可以使用如 JSON_EXTRACT() 等函数对数据进行操作。(Database Administrators Stack Exchange)

• 索引支持：通过生成列（Generated Columns）和函数索引，可以对 JSON 字段中的特定路径建立索引，从而提高查询性能。 (SitePoint)

✅ BLOB 类型的特点：

• 原始二进制存储：BLOB 类型将数据以原始二进制形式存储，不进行任何解析或验证。

• 更少的存储开销：由于不包含额外的元数据，BLOB 类型在存储大数据时可能更为高效。

• 查询限制：无法直接使用 JSON 函数对 BLOB 类型的数据进行操作。

---

🚀 为什么将 attribute 列改为 BLOB 能提升查询性能？

1. 减少解析开销：使用 JSON 类型时，MySQL 在查询时需要解析 JSON 结构，尤其是在使用 JSON 函数时，这会增加 CPU 负载。而 BLOB 类型存储的是原始数据，查询时无需解析，降低了 CPU 使用率。

2. 降低存储开销：JSON 类型的数据在存储时会包含额外的元数据，用于支持其结构化特性。这些元数据会增加存储空间的使用。相比之下，BLOB 类型仅存储原始数据，减少了存储空间的占用。

3. 提升查询效率：在某些情况下，尤其是当不需要对 JSON 数据进行解析或操作时，使用 BLOB 类型可以避免不必要的处理步骤，从而提高查询效率。

---

⚠️ 注意事项

• 功能限制：将字段类型改为 BLOB 后，无法使用 JSON 函数对其进行操作。如果需要对数据进行结构化查询或操作，可能需要在应用层进行解析。

• 索引限制：BLOB 类型的字段无法直接建立索引，这可能影响基于该字段的查询性能。

• 数据迁移：在修改字段类型前，建议备份数据，并确保应用程序能够正确处理新的数据类型。

---

✅ 总结

将 attribute 列从 JSON 类型修改为 BLOB 类型，适用于以下场景：

• 数据主要用于存储和检索，且不需要在数据库层进行解析或操作。

• 希望减少数据库的 CPU 和存储开销，提升查询性能。

然而，若需要在数据库层对数据进行结构化查询或操作，仍建议使用 JSON 类型，并结合生成列和索引优化查询性能。

---

如果您需要进一步的帮助，例如如何在应用层解析 BLOB 类型的 JSON 数据，或如何在数据库层优化 JSON 字段的查询性能，请随时提出。

3.将 MySQL 表中的 attribute 列从 JSON 类型修改为 BLOB 类型，会对查询行为和性能产生以下影响：

---

1. 数据存储与解析方式的变化

• JSON 类型：MySQL 会对 JSON 类型的数据进行解析和验证，确保其格式正确，并在内部以特定的结构存储。这种结构支持对 JSON 数据的字段进行索引和查询。

• BLOB 类型：BLOB 是一种二进制大对象类型，MySQL 不会对其内容进行解析或验证。数据以原始二进制形式存储，数据库无法直接理解其结构。

---

1. 查询性能的变化

• 使用 JSON 类型时：当查询包含大型 JSON 字段时，MySQL 需要解析整个 JSON 数据，以便进行格式验证和字段提取。这会增加 CPU 的负担，尤其是在处理大字段或高并发查询时，可能导致查询性能下降。

• 使用 BLOB 类型时：将字段改为 BLOB 后，MySQL 不再解析其内容，查询时直接读取原始二进制数据。这减少了 CPU 的解析开销，提升了查询性能，尤其是在只需要读取整个字段而不需要解析其内容的场景中。

---

1. 功能性的变化

• JSON 类型的优势：支持使用 JSON 函数（如 JSON_EXTRACT、JSON_CONTAINS 等）进行字段查询和操作，可以对 JSON 数据的内部结构进行索引和筛选。

• BLOB 类型的限制：无法使用 JSON 函数对字段内容进行操作，数据库无法识别其内部结构，限制了对数据的查询和操作能力。

---

1. 适用场景的变化

• 适合使用 JSON 类型的场景：需要对 JSON 数据的内部字段进行查询、筛选或索引的应用场景。

• 适合使用 BLOB 类型的场景：只需要存储和检索整个字段内容，而不需要对其内部结构进行操作的场景，如日志存储、文件内容存储等。

---

总结

将 attribute 列从 JSON 类型改为 BLOB 类型，可以提升查询大型字段时的性能，减少 CPU 的解析负担。但同时也失去了对 JSON 数据结构的操作能力。因此，是否进行此更改应根据具体的应用需求权衡性能和功能性的取舍。