MySQL 5.7 - JSON 索引 - 使用非标量值生成的列

Question

我一直在玩MySQL 5.7中的JSON支持。我有几个关于用于索引目的的生成的列的问题。https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-secondary-indexes-virtual-columns .

具体来说，请参阅此行：

无法为 JSON 列编制索引。可以通过在生成的列上创建索引来解决此限制，该索引从 JSON 列中提取标量值。

这对我来说似乎是一个很大的限制。无论我看到哪里，人们都建议使用生成的列。但是这种解决方法适用于一组非常有限的用例。或者，我理解了什么。

搭建舞台

让我解释一下我的用例。假设您有一个名为standards的表。它具有以下结构：

CREATE TABLE `standards` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`sections` json DEFAULT NULL,
`subjects` json DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sections列包含一个 JS 对象数组：

[
{
"id": 90491,
"name": "A",
},
{
"id": 90494,
"name": "B",
}
]

subjects列包含一个嵌套的 JS 对象：

{
"576845": {
"id": 576845,
"name": "Computer Education"
},
"576848": {
"id": 576848,
"name": "English Language"
},
"576854": {
"id": 576854,
"name": "Environmental Science"
},
"576860": {
"id": 576860,
"name": "Mathematics"
}
}

示例查询

查询 1

要查找section ID为90494的Standard记录，查询将是：

SELECT * from standards WHERE JSON_CONTAINS( sections->>'$[*].id', '90494' );

查询 2

要查找具有subject ID576854的Standard记录，查询将是：

SELECT * from standards WHERE JSON_CONTAINS_PATH( subjects, 'one', '$."576854"');

或

SELECT * from standards WHERE JSON_CONTAINS( subjects->>'$.*.id', '576854' );

问题

现在，以上所有工作。问题是查询执行全表扫描。

考虑到上面的查询 1，如何生成包含所有section IDs标量数据的虚拟列？

每条Standard记录有多个sections，有多个ID。因此，我不能只创建一个整数虚拟列来存储单个值。它必须是我们需要搜索的部分 ID 数组。

因此，我生成的列如下所示：

ALTER TABLE standards
ADD section_ids json GENERATED ALWAYS AS (sections->>'$[*].id') VIRTUAL NOT NULL;

生成的列现在将仅存储部分 ID 数组。但是我无法在生成的列上添加索引，因为它又是 JSON 列。

问题 - 如何使用索引？

因此，问题归结为这一点 - 对于上面显示的查询，如何避免全表扫描？

任何建议将不胜感激。

Answer 1

我不会说MySQL 5.7不可能 - 因为它是，具有笨拙的解决方法和限制 - 但我不会进入该版本的操作方法，因为它要困难得多，并且在许多情况下，如果可以将大量项目添加到数组中，则会达到限制。

但是，从MySQL 8.0.17开始，现在可以支持多值索引。

ALTER TABLE standards
ADD INDEX section_ids ( (CAST(sections->'$[*].id' AS UNSIGNED ARRAY)) ),
ADD INDEX subject_ids ( (CAST(subjects->'$.*.id'AS UNSIGNED ARRAY)) );

** 请注意，$.*将采用所有对象属性并返回每个格式化为数组的查询值(.id(。

EXPLAIN SELECT * from standards WHERE JSON_CONTAINS( sections->'$[*].id', '90494' );
EXPLAIN SELECT * from standards WHERE JSON_CONTAINS( subjects->'$.*.id', 576854 );

您将看到索引用于这些查询。

Answer 2

我会在旧版本中通过手动创建一个单独的索引表并使用触发器使其保持最新来解决此问题。