如何在mysql中优化JSON数据查询性能

说实话，当我第一次了解MySQL的JSON优化时，我真的感觉新世界的大门被打开了。
当时，我正在处理一个用户表，里面有一个配置文件字段，存储了各种扩展信息，但结果我根本找不到它。
后来学习了生成列+索引这个很酷的功能，性能立马就上去了。
我想告诉你我的陷阱。

先说一个真实的案例。
我们有一个电子商务系统。
用户表中的地址是一个JSON字段，存储各种送货地址。
一开始我用地址WHERE->>'$.city' = 'Beijing'去查，每次都是全表扫描，CPU直接来了。
后来想了想，把常用的字段拆分成列创建：
sql ALTER TABLE users ADD COLUMN city VARCHAR(5 0) AS (JSON_UNQUOTE(JSON_EXTRACT(address, '$.city'))); 在用户（城市）上创建索引 idx_city;
现在查询直接改为WHERE city = 'Beijing'，直接命中索引，延迟从秒降到毫秒。
说实话，这种方法比为原始JSON建立函数索引要容易得多，而且维护成本也低。

有趣的是，虽然函数索引也能解决问题，但要谨慎使用。
我尝试使用 CREATE INDEX idx_profile_age ON users ((JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age'))))，但发现查询应该完全一致。
比如不能写成WHERE Age > 3 0，而必须写成WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age')) > 3 0。
这种写法非常脆弱，稍微改变一下条件就会失败，所以我不推荐。
当时有一个同事犯了这个错误。
经过长时间的调试，他发现是表达式不匹配导致索引失败。

说到这里，我必须提到一个陷阱。
当我将整个配置表保存为 JSON 后，发现每次读取配置时，我都必须解析整个文档。
当时没意识到，后来才意识到，虽然配置数据变化很大，但查询频率其实并不高。
此数据必须直接存储在关系字段中，或使用生成的列进行映射。
我在一个项目中犯了这个错误。
当需要后台配置时，CPU 使用率仍然很高。
最后我加了缓存解决了问题。

关于全表扫描的陷阱，我建议您记住几个场景。
例如，JSON 搜索函数 JSON_SEARCH 完全忽略索引。
测试的时候我差点被它骗死了。
还有嵌套的深度查询，比如WHERE profile->>'$.deep.path.value' = 'data'。
如果路径没有被索引，MySQL会像爬树一样逐层解析。
我当时正在制作一个包含大量细节的订单表。
结果，每次检查特定 SKU 时，我都必须扫描整个表。
最后，我不得不将所有常用的SKU分成独立的列。

最后说一下我现在使用的优化套路。
我永远不会将用户年龄和性别等频繁请求的字段放入 JSON 中。
这些字段必须直接存储在关系列中，然后建立索引。
至于JSON，我通常用它来存储扩展属性，即例如用户定义的标签和操作日志，通常不用于数据过滤。
有一个原则需要记住：如果某个字段将来可能会被频繁请求，请立即共享。
我有一个项目，我已经提前完成了这一点，现在查询性能比那些努力工作的团队要好得多。

验证方法其实很简单。
我通常会先调出一个模板并 SPLAIM 查看执行计划以确认是否使用了索引。
然后SELECT SQL_NO_CACHE比较前后的响应时间。
当我优化了用户画像查询并将JSON中的标签字段拆分为独立的列后。
查询速度直接快了 5 倍。
这种感觉，嗯，还蛮清爽的。

如何使用SQL Developer导出表数据到json文件

坦白讲，使用 Oracle SQLDeveloper 将表数据导出到 JSON 文件实际上非常容易。
我们先来说说最重要的事情。
您需要在SQLDeveloper中找到目标表，进入数据视图，启动导出过程，选择JSON格式，配置参数，最后完成导出。

具体来说，我去年跑的一个项目有大约3 000条数据，导出过程不到1 0分钟。
还有一点：一开始我以为导出会直接导出所有数据，后来发现我错了。
事实上，您可以通过 SQL 查询过滤特定数据来节省时间。

等一下，还有一件事。
导出的 JSON 文件结构默认包含表的所有列和行的数据。
如果需要调整此结构，可以在导出之前进行。
老实说，这很令人困惑。
很多人不注意这个细节。
最后，请记住确认您的导出设置正确，然后单击“完成”。
系统自动生成JSON文件并保存到指定位置。

我认为值得一试。
如果这是您第一次导出，请按照下列步骤操作： 首先，确保 SQLDeveloper 版本为 4 .1 或兼容版本，并耐心等待导出过程完成。
导出完成后，使用文件管理器或文本编辑器打开生成的 JSON 文件并检查数据是否正确导出。
如果一切顺利，将会导出一个 JSON 文件。

Json转译SQL

是的，它就是 Json 到 SQL 转换器。
简单来说，这个东西就是解析Json，然后生成SQL语句。

原理？ 首先读取Json，找到key值，然后拼出SQL语句。
优化一下，然后运行数据库，结果会以Json的形式返回。

用这个工具吗？ 数据迁移、查询、分析都非常方便。

比如Sql-Translation，这个开源项目是免费的，页面简单，功能也不错。
代码可以在GitHub上找到，自己部署也很方便。

用户界面？ 简单，输入Json，点击，SQL就出来了。
直接看到结果非常方便。

总结？ 这个工具就是帮助你快速将Json转换为SQL，提高效率。
使用 Sql-Translation 尝试一下，亲自看看效果。

sql按某一字段内容进行提取数据

嘿嘿，说到SQL中按字段内容提取数据，我就了解很多了。
在问答论坛混了这么多年，见过很多在这方面头疼的朋友。
让我详细向您展示哪种方法最适合您。

首先，你应该知道字段类型和提取要求是必不可少的。
例如，如果您正在处理字符串字段，则 SUBSTR() 函数可能很有用。
这个东西就像剪刀一样，它可以帮助你从绳子上剪掉一部分。
例如，如果有一个日期字段，想要输出年份和月份，可以使用SUBSTR(shop_order.createtime,1 ,7 )这样写。
不过要注意的是，数据格式一定要统一，否则就像切错地方一样，结果也可能不正确。

再举个例子，如果你想提取 JSON 字段中的值，这有点像解谜题。
在MySQL中，可以使用字段名->'$.键名'语法来提取。
例如，如果要从内容字段中提取 Execution_date 的值，则可以键入 content->'$.Execution_Date'。
但是，此技巧仅适用于结构化 JSON 字段。

然后，如果您想过滤特定值，WHERE 子句是您最好的朋友。
例如，如果要查找状态为活动的用户，可以键入 WHERE status='active'。
如果想要模糊匹配，比如查找姓张的用户，可以使用LIKE '张%'。

说到工会问题，就像一个谜题。
通过连接两个表，您可以提取相关数据。
例如，如果您想知道订单信息和对应的客户名称，可以使用JOIN来链接订单和客户表。

聚合函数就像一个统计学家，可以帮助你按字段分组，然后统计数量。
例如，如果要统计每个部门的员工人数，可以使用 GROUP BY 部门，然后使用 COUNT()。

最后，还应该关注安全性和性能优化。
请勿使用可能导致数据泄露的非法方法。
使用大表时记得添加索引，这样查询效率更高。

总之，这几种方法各有各的用途，大家应该根据实际情况进行选择。
我这里举的例子都是我在实际工作中使用过的。
我希望他们能帮助你。