MySQL在2015年中期发布了MySQL 5.7.8，为我们提供了JSON数据类型。从那时起，它一直被用作逃避严格的列定义并存储所有形状和大小的JSON文档的一种方式：审核日志，配置设置，第三方有效负载，用户定义的字段等。
尽管MySQL为我们提供了读取和写入JSON数据的函数，但您很快就会发现明显缺少的东西：直接索引JSON列的功能。
在其他数据库中，直接索引 JSON 列的最佳方法通常是通过一种称为广义倒排索引（简称 GIN）的索引类型。由于MySQL不提供GIN索引，因此我们无法直接为整个存储的JSON文档编制索引。但是，MySQL确实为我们提供了一种间接索引存储的JSON文档部分的方法。
根据您使用的 MySQL 版本，您有两个为 JSON 编制索引的选项。在MySQL 5.7中，您必须创建一个中间生成的列，但从MySQL 8.0.13开始，您可以[直接创建功能索引-(https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts)。
让我们从一个示例表开始，该表用于记录在应用程序中执行的各种操作。
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" } }复制
在我们的示例中，我们将对 request 对象内的 email 进行索引。这将使我们的（虚构的）用户能够快速找到特定人员提交的表单。
让我们看一下索引的第一种方式：生成的列。
通过生成的列为 JSON 编制索引
生成的列可以被视为计算列、计算列或派生列。它是一个列，其值是表达式的结果，而不是直接数据输入。表达式可以包含文本值、内置函数或对其他列的引用。表达式的结果必须是标量和确定性的。
由于我们尝试在 properties 列中为 request.email 字段编制索引，因此生成的列将使用 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;
复制就是这样！现在，你已在 JSON 的 properties 列中索引了 request.email 键。让我们验证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
[...]: [...]
复制即使我们没有按名称显式处理列，优化程序也会知道基于该表达式生成的列上有一个索引，并选择使用该索引。谢谢优化器！
我们可以确认，longhand也是如此。
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
[...]: [...]
复制同样，优化程序“通读”我们的表达式并使用电子邮件索引。
不相信？让我们看一下优化程序正在执行的操作，方法是在前面的 EXPLAIN 语句之后运行 SHOW WARNINGS 以查看重写的查询。
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')
复制如果仔细观察，您会发现优化程序已重写我们的查询，并将相等性比较更改为引用索引列。如果您无法控制访问模式，因为查询是从代码库中的第三方包发出的，或者由于其他原因无法更改这部分代码，则此功能特别有用。
如果基础表达式不匹配，则优化程序将无法使用索引，因此在创建生成的列时请务必小心。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.
复制这到底是怎么回事呢？在我们之前的示例中，我们是负责创建生成列的人，我们将其称为 VARCHAR(255)，MySQL可以轻松对其进行索引。
但是，当我们使用功能索引时，MySQL将根据它推断的数据类型为我们创建该列。JSON_UNQUOTE 返回 LONGTEXT 值，是一个无法编制索引的值。
幸运的是，错误消息为我们指明了正确的方向：我们需要将值转换为非 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及更高版本中），这是一个完全合理的方法！
虽然MySQL中可能不提供直接JSON索引，但特定密钥的间接索引可以涵盖大多数用例。
也不要只停留在 JSON 上！可以将生成的列和功能索引用于所有类型的常见、难以编制索引的模式。
继续前进，充满信心地进行索引。
原文标题：Indexing JSON in MySQL
原文作者：Aaron Francis
原文地址：https://planetscale.com/blog/indexing-json-in-mysql
