在带有 JSON 数据的生成列上使用索引

在带有 JSON 数据的生成列上使用索引

问题

您想要在 JSON 数据中执行搜索，但速度很慢。

解决方案

使用生成的列，该列是根据搜索 JSON 值的表达式创建的以及该列上的索引。

讨论

在本食谱中，我们将讨论一个book_authors表：

CREATE TABLE `book_authors` (
`id` int NOT NULL AUTO_INCREMENT,
`author` json NOT NULL,
PRIMARY KEY (`id`)
);


该表在 JSON 列中包含每个作者的图书记录：

mysql> SELECT * FROM book_authorsG
*************************** 1. row ***************************
id: 1
author: {"id": 1, "name": "Paul",
"books": [
"Software Portability with imake: Practical Software Engineering",
"Mysql: The Definitive Guide to Using, Programming,
and Administering Mysql 4 (Developer's Library)",
"MYSQL Certification Study Guide",
"MySQL (OTHER NEW RIDERS)",
"MySQL Cookbook",
"MySQL 5.0 Certification Study Guide",
"Using csh & tcsh: Type Less, Accomplish More (Nutshell Handbooks)",
"MySQL (Developer's Library)"],
"lastname": "DuBois"}
lastname: "DuBois"
*************************** 2. row ***************************
id: 2
author: {"id": 2, "name": "Alkin", "books": ["MySQL Cookbook"],
"lastname": "Tezuysal"}
lastname: "Tezuysal"
*************************** 3. row ***************************
id: 3
author: {"id": 3, "name": "Sveta",
"books": ["MySQL Troubleshooting", "MySQL Cookbook"],
"lastname": "Smirnova"}
lastname: "Smirnova"
3 rows in set (0,00 sec)


如果您想搜索特定作者，您可以考虑按他们的姓名进行搜索。

该CREATE INDEX命令在表中的列上创建索引。JSON 数据存储在单个列中，因此使用简单CREATE INDEX命令创建的任何索引都会索引整个 JSON 文档，而您可能只需要搜索其中的一部分。

此外，该CREATE INDEX命令对于 JSON 列将失败：

mysql> CREATE INDEX author_name ON book_authors(author);
ERROR 3152 (42000): JSON column 'author' supports indexing only via generated columns on a specified JSON path.


此问题的解决方案是使用生成的列并在其上创建索引。生成列中的值是使用创建列时定义的表达式创建的：

ALTER TABLE book_authors ADD COLUMN lastname VARCHAR(255) GENERATED ALWAYS AS(JSON_UNQUOTE(JSON_EXTRACT(author, '$.lastname')));


在此示例中，我们创建了一个从表达式 生成的列JSON_EXTRACT(author, ‘$.lastname’)。我们还可以使用->和->>运算符来提取 JSON 值：

ALTER TABLE book_authors ADD COLUMN name VARCHAR(255) GENERATED ALWAYS AS (author->>'$.name');


我们在表达式中 使用了JSON_UNQUOTE函数和运算符来删除作者姓名中的尾随引号（如果存在）。->>

两个新列name和lastname不占用任何空间，并且在每次查询访问表时生成。

提示
SELECT如果您希望以额外的存储空间和写入时速度变慢为代价来 提高查询性能，请使用关键字 定义生成的列STORED。在这种情况下，表达式将仅执行一次：当插入或修改表达式中使用的值然后物理存储在磁盘上时。

现在我们可以在新生成的列上创建索引：

CREATE INDEX author_name ON book_authors(lastname, name);


要使用新创建的索引访问数据，请像引用任何其他列一样引用新列：

mysql> SELECT author->'$.books' FROM book_authors WHERE name = 'Sveta' AND lastname='Smirnova';
+---------------------------------------------+
| author->'$.books' |
+---------------------------------------------+
| ["MySQL Troubleshooting", "MySQL Cookbook"] |
+---------------------------------------------+
1 row in set (0,00 sec)


EXPLAIN确认新索引已被使用：

mysql> EXPLAIN SELECT author->'$.books' FROM book_authors WHERE name = 'Sveta' AND lastname='Smirnova'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: book_authors
partitions: NULL
type: ref
possible_keys: author_name
key: author_name
key_len: 2046
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0,00 sec)