时间戳

首页 > 解决方案 > 选择 MySQL 表中 JSON 类型值的计数

mysql - 选择 MySQL 表中 JSON 类型值的计数

问题描述

假设我们有下表

+-----+------------+
| id  | categories |
+-----+------------+
| id1 | [20,25]    |
| id2 | [25]       |
| id3 | [20,25,28] |
| id4 | [28,25]    |
| id5 | [20,25]    |
+-----+------------+

字段categories为 JSON 类型。它仅包含已知且有限的整数列表 - 例如 20、25、28。所以，我需要以某种方式计算所有这些值的所有包含项，如下所示：

+-------+--------+
| count | number |
+-------+--------+
|    20 |      3 |
|    25 |      5 |
|    28 |      2 |
+-------+--------+

主要问题是使用单个请求进行此操作，而无需遍历服务器代码或过程调用中的类别编号

正面决定如下

SELECT 
    COUNT(id) AS 'count', '20' AS number
FROM
    ml_categories
WHERE
    JSON_CONTAINS(categories, '20') 
UNION SELECT 
    COUNT(id) AS 'count', '25' AS number
FROM
    ml_categories
WHERE
    JSON_CONTAINS(categories, '25') 
UNION SELECT 
    COUNT(id) AS 'count', '28' AS number
FROM
    ml_categories
WHERE
    JSON_CONTAINS(categories, '28')

但是这个解决方案具有 O(n) 复杂性，并且代码本身还不够好。例如，对于我的硬件上的一个类别，循环约 500K 记录大约需要 1 秒，因此计算 10 个类别大约需要 10 秒。不好。有没有办法优化这样的查询？

提前谢谢各位

标签： mysqlsqljson

解决方案

但是这个解决方案有 O(n) 复杂度

我不确定你的情况是什么。但我敢肯定，您不会找到比O(n)更好的解决方案。

假设以下数字：

  • n：项目数（“ml_categories”表中的行）
  • m : 所有类别的数量
  • a：每个项目的平均类别数

您的查询的复杂度为O(n*m)。即使是 Bill Karwins 解决方案（我认为这是最佳的）也具有O(n*a)的复杂性（假设 GROUP BY 子句的索引category_id）。

如果您有一个categories包含所有类别的表，则可以使用以下查询：

select c.id as category, count(*)
from ml_categories i
join categories c on json_contains(i.categories, cast(c.id as json))
group by c.id

它将返回与您的 UNION 查询相同的内容：

| category | count(*) |
| -------- | -------- |
| 20       | 3        |
| 25       | 5        |
| 28       | 2        |

在 DB Fiddle 上查看

并且因为没有索引可以用于 JOIN，所以它可能与您的查询一样快或一样慢（最好的情况是索引可用于 GROUP BY 避免使用filesort）。

如果你使用 MySQL 8（至少 8.0.4），你可以使用JSON_TABLE()：

select c.category, count(*)
from ml_categories i
join json_table(
  i.categories,
  '$[*]' columns (category int path '$')
) c
group by c.category;

在 DB Fiddle 上查看

使用 JSON_TABLE 的 JOIN 会将 JSON 列中的类别“解包”成行。如果您删除 GROUP BY 子句，那么您将获得一个（动态）规范化表。这应该与O(n*a)一起缩放。但是由于表是动态创建的，所以不会有索引来支持 GROUP BY 子句。所以必须首先对结果进行排序，最终的复杂度为O(n*a * log(n*a))。这比O(n*m)更好地扩展（如果m增长而a不增长）。但是，如果m（类别的数量）足够小，您的查询可能仍然是使用给定模式所能做的最好的查询。

推荐阅读