早在 2015 年年中，随着 MySQL 5.7.8 的发布，MySQL 就为我们提供了JSON 数据类型。从那时起，它就被用作逃避严格的列定义和存储各种形状和大小的 JSON 文档的一种方式：审计日志、配置设置、第 3 方有效负载、用户定义的字段等等。
尽管 MySQL 为我们提供了读取和写入 JSON 数据的功能，但您很快就会发现明显缺少的东西：直接索引 JSON 列的能力。
在其他数据库中，直接索引 JSON 列的最佳方式通常是通过一种称为广义倒排索引或简称 GIN 的索引。由于 MySQL 不提供 GIN 索引，我们无法直接索引整个存储的 JSON 文档。不过，一切并没有丢失，因为 MySQL 确实为我们提供了一种间接索引部分存储 JSON 文档的方法。
根据您使用的 MySQL 版本，您有两个用于索引 JSON 的选项。在 MySQL 5.7 中，您必须创建中间生成列，但从 MySQL 8.0.13 开始，您可以直接创建函数索引。
让我们从一个用于记录应用程序中执行的各种操作的示例表开始。
CREATE TABLE `activity_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`properties` json NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
我们将在该表中插入具有以下形状的 JSON 文档：
{
"uuid": "e7af5df8-f477-4b9b-b074-ad72fe17f502",
"request": {
"email": "little.bobby@tables.com",
"firstName": "Little",
"formType": "vehicle-inquiry",
"lastName": "Bobby",
"message": "Hello, can you tell me what the specs are for this vehicle?",
"postcode": "75016",
"townCity": "Dallas"
}
}
在我们的示例中，我们将索引对象email内的键request。这将允许我们的（虚构的）用户快速找到特定人员提交的表单。
让我们看看我们的第一个索引选项：生成的列。
通过生成的列索引 JSON
生成的列可以被认为是计算的、计算的或派生的列。它是一列，其值是表达式的结果，而不是直接数据输入。表达式可以包含文字值、内置函数或对其他列的引用。表达式的结果必须是标量和确定性的。
由于我们试图索引列request.email中的properties字段，因此我们生成的列将使用JSON 取消引用提取运算符来提取值。
为了验证我们是否正确地形成了我们的表达式，我们将首先运行一个SELECT语句并检查结果。
mysql> SELECT properties->>"$.request.email" FROM activity_log;
+--------------------------------+
| properties->>"$.request.email" |
+--------------------------------+
| little.bobby@tables.com |
+--------------------------------+
该->>运算符是一个速记，不带引号的提取运算符，使其等价于JSON_UNQUOTE(JSON_EXTRACT(column, path)). 我们本可以SELECT使用速记写前面的语句并得到相同的结果。
mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email"))
-> FROM activity_log;
+-----------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(properties, "$.request.email")) |
+-----------------------------------------------------------+
| little.bobby@tables.com |
+-----------------------------------------------------------+
你也可以根据个人喜好来选择不同的方式。
现在我们已经确认我们的表达式是有效且准确的，让我们用它来创建一个生成的列。
ALTER TABLE activity_log ADD COLUMN email VARCHAR(255)
GENERATED ALWAYS as (properties->>"$.request.email");
语句的第一部分ALTER应该看起来很熟悉，我们添加了一个名为的列email并将其定义为VARCHAR(255). 在语句的后半部分，我们声明该列已生成并且它应该始终等于表达式的结果properties->>"$.request.email"。
我们可以通过选择它来确认我们的列已被添加，就像我们选择任何其他列一样。
mysql> SELECT id, email FROM activity_log;
+----+-------------------------+
| id | email |
+----+-------------------------+
| 1 | little.bobby@tables.com |
+----+-------------------------+
您会看到 MySQL 现在正在为我们维护这个专栏。如果我们要更新 JSON 值，生成的列值也会改变。
现在我们已经生成了列，我们可以像添加任何其他列一样为其添加索引。
ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;
您现在已经为request.emailJSONproperties列中的键建立了索引。让我们验证 MySQL 是否会使用索引来加速过滤电子邮件的查询。
mysql> EXPLAIN SELECT * FROM activity_log WHERE email = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: activity_log
partitions: NULL
type: ref
possible_keys: email
key: email
key_len: 768
ref: const
rows: 1
filtered: 100.00
Extra: NULL
MySQL 报告它计划使用email索引来满足这个查询。
生成的列索引和优化器
MySQL 的优化器是一个强大而神秘的实体。当我们给 MySQL 一个命令时，我们告诉它我们想要什么，而不是如何得到它。很多时候 MySQL 会接受我们的查询并稍微重写它，这是一件好事！数十年来的数万小时用于使优化器有效和高效。
当涉及到生成列的索引时，优化器可以“看穿”不同的访问模式，以确保使用底层索引。
我们在 上定义了一个索引email，它是基于表达式生成的列properties->>"$.request.email"。我们已经证明在查询email列时使用了索引。更有趣的是，如果我们忘记查询命名email列，优化器足够聪明，可以帮助我们！
在以下查询中，我们不按名称访问生成的列，而是使用简写 JSON 提取运算符。EXPLAIN（为简洁起见，语句中省略了一些行。）
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
possible_keys: email
key: email
key_len: 768
[...]: [...]
即使我们没有通过名称显式地寻址列，优化器也知道基于该表达式生成的列上有一个索引，并选择使用该索引。感谢优化器！
我们可以确认这也是速记的情况。
mysql> EXPLAIN SELECT * from activity_log WHERE
-> JSON_UNQUOTE(
-> JSON_EXTRACT(properties, "$.request.email")
-> ) = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
possible_keys: email
key: email
key_len: 768
[...]: [...]
同样，优化器“读取”我们的表达式并使用电子邮件索引。
SHOW WARNINGS让我们通过在之前的语句之后运行 a 来查看优化器正在做什么，EXPLAIN以查看重写后的查询。
mysql> SHOW WARNINGS;
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `activity_log`.`id` AS `id`,`activity_log`.`properties` AS `properties`,`activity_log`.`created_at` AS `created_at`,`activity_log`.`email` AS `email` from `activity_log` where (`activity_log`.`email` = 'little.bobby@tables.com')
如果您仔细观察，您会发现优化器已经重写了我们的查询并更改了相等比较以引用索引列。如果您无法控制访问模式，因为查询是从代码库中的第 3 方包发出的，或者由于某些其他原因您无法更改这部分代码，这将特别有用。
如果底层表达式的匹配不是很紧密，那么优化器将无法使用索引，因此在创建生成的列时一定要小心。MySQL 文档更详细地解释了优化器对生成的列索引的使用。
功能指标
从 MySQL 8.0.13 开始，您可以跳过创建生成列的中间步骤并创建所谓的“功能索引”。MySQL 文档将这些功能关键部分称为“功能索引”。
函数索引是表达式而不是列的索引。听起来很像生成的列，不是吗？听起来很相似是有原因的，那是因为功能索引是使用隐藏的生成列实现的！我们不再需要创建生成的列，但仍在创建生成的列。
不过，功能索引有一些陷阱，尤其是在将它们用于 JSON 时。
像这样创建我们的 JSON 索引会很好：
ALTER TABLE activity_log
ADD INDEX email ((properties->>"$.request.email")) USING BTREE;
但如果你真的尝试，你会得到一个令人讨厌的错误：
Query 1 ERROR: Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.
那么这里发生了什么？在我们之前的示例中，我们负责创建生成的列，我们将其声明为 a VARCHAR(255)，这很容易被 MySQL 索引。
但是，当我们使用功能索引时，MySQL 将根据它推断的数据类型为我们创建该列。JSON_UNQUOTE返回一个LONGTEXT无法索引的值。
幸运的是，错误消息为我们指明了正确的方向：我们需要将值转换为 not 的类型LONGTEXT。使用该CHAR函数进行强制转换告诉 MySQL 推断VARCHAR数据类型。
ALTER TABLE activity_log
ADD INDEX email ((CAST(properties->>"$.request.email" as CHAR(255)))) USING BTREE;
现在我们已经添加了索引，我们将通过运行EXPLAIN。
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: activity_log
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
不幸的是，我们的索引根本没有被考虑，所以我们还没有走出困境。
除非另有说明，否则将值转换为字符串会将排序规则设置为utf8mb4_0900_ai_ci. 另一方面，JSON 提取函数返回一个带有utf8mb4_bin排序规则的字符串。这就是我们的问题！因为查询表达式和存储索引之间的排序规则不匹配，所以我们的新功能索引没有被使用。
最后一步是将强制转换的排序规则显式设置为utf8mb4_bin.
ALTER TABLE activity_log
ADD INDEX email ((
CAST(properties->>"$.request.email" as CHAR(255)) COLLATE utf8mb4_bin
)) USING BTREE;
重新运行之前的EXPLAIN，我们可以看到我们终于可以使用函数索引了。
mysql> EXPLAIN SELECT * FROM activity_log
-> WHERE properties->>"$.request.email" = 'little.bobby@tables.com';
*************************** 1. row ***************************
id: 1
possible_keys: email
key: email
key_len: 1023
[...]: [...]
显然，函数式索引有一些缺陷，其中一些是明确的且易于调试，而一些则需要对文档进行更多的挖掘。
请记住，功能索引在后台使用隐藏的生成列。如果您更喜欢自己控制生成的列（即使在 MySQL 8.0.13 及更高版本中），这是一个非常合理的方法！
虽然直接 JSON 索引在 MySQL 中可能不可用，但特定键的间接索引可以涵盖大多数用例。
也不要只停留在 JSON 上，您可以在所有类型的常见、难以索引的模式中使用生成的列和功能索引。
原文标题：Indexing JSON in MySQL
原文作者：Aaron Francis
原文地址：https://planetscale.com/blog/indexing-json-in-mysql
