在带有 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_authors\G
*************************** 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)