MySQL成员超慢;在查询中使用JSON数组值的替代方法?

pkbketx9  于 2022-11-19  发布在  Mysql
关注(0)|答案(3)|浏览(202)

我们有一个系统take,它将JSON数组作为参数来定义匹配记录。为了简单起见,我们有一个查询:

SELECT ai.ID 
FROM association_internal ai 
WHERE ai.source_object_record_id MEMBER OF('[57928,57927]');

现在上面的调用需要380 ms,这很荒谬。为什么?因为下面版本的完全相同的调用需要11 ms。

SELECT ai.ID 
FROM association_internal ai 
WHERE ai.source_object_record_id IN(57928,57927);

仅仅使用IN和MEMBER OF,就可以很好地工作。MEMBER OF太慢了,(老实说)在企业环境中是不可用的。速度会随着我们拥有的数字的增加而降低(在上面的例子中,我们可以拥有100+)。
显然,MEMBER OF的处理速度非常慢(也许它甚至避免了索引?),但我们不能将JSON数组作为IN的属性传递。现在,我可以编写动态SQL并手动执行,但这很难看,也很草率。
因此,问题是--有没有一种方法可以传递JSON数组,以便在不使用MEMBER OF的硬编码查询中使用?有没有一种方法可以像硬编码IN值一样快?或者有没有一种方法可以将JSON数组的值转换为可用的IN或EXISTS用例?

2wnc66cl

2wnc66cl1#

我简直不敢相信。使用JSON_TABLE只需要12ms就可以转换数组。所以我可以将任何数组作为CTE进行前端加载,然后使用IN,速度非常快。
为清楚起见,修复方法为:

WITH frontload_array AS
(
  SELECT jt.ID 
  FROM JSON_TABLE('[1,2,3,4,5]', '$[*]' COLUMNS(ID int PATH '$')) AS jt
)
SELECT ai.ID 
FROM association_internal ai 
WHERE ai.source_object_record_id IN(SELECT ID FROM frontload_array);
rkttyhzu

rkttyhzu2#

优化此查询的正确方法是使用IN(),并为数组中的每个元素指定一个离散值。您无法优化与逗号分隔字符串的比较。

SELECT ai.ID 
FROM association_internal ai 
WHERE ai.source_object_record_id IN(?, ?, ?, ...);

根据数组中元素的数量创建?占位符,并将每个元素作为标量绑定到每个参数。
这并不难看或草率,这是使用SQL的正确方式。当你用Java或其他语言编写函数时,你是否以逗号分隔的字符串形式传递参数,并期望函数拆分该字符串?不,你会为每个参数传递一个单独的值。

9udxz4iz

9udxz4iz3#

JSON是字符串;必须对它进行解析以查看其中的内容。而且,在大多数情况下,查询必须查看每一行;没有捷径。
RDBMS是一种为获取内容而优化的结构,当使用INDEXes时尤其如此。
JSON中的索引是最小的,您必须努力获取它们--例如使用“生成的”列。
对于在WHEREGROUP BYORDER BY中不需要的东西,使用JSON

相关问题