MySQL 8.0 表达式索引在 JSON 字段查询中的应用

好的，各位观众老爷们，程序媛/猿们，大家好！我是你们的老朋友，江湖人称“Bug终结者”的码农小李。今天咱们不聊风花雪月，也不谈情说爱，就来硬核地聊聊MySQL 8.0里一个能让你的JSON字段查询速度像火箭一样嗖嗖嗖的秘密武器——表达式索引。

准备好了吗？系好安全带，咱们开车啦！🚀

一、 啥是JSON？为啥要索引它？

首先，咱们得先搞明白，JSON是个啥玩意儿？别跟我说你不知道，不知道的自觉去面壁思过三分钟！😎

简单来说，JSON (JavaScript Object Notation) 是一种轻量级的数据交换格式。它长得像JavaScript的对象，但其实跟语言没啥关系，各种编程语言都能用它。 它的优点在于易于阅读和编写，而且解析起来也很快。

举个栗子：

{
  "name": "小李",
  "age": 28,
  "city": "北京",
  "skills": ["Java", "Python", "MySQL"]
}

瞧见没？ key-value 键值对，简洁明了，一目了然。

那么问题来了，为啥我们需要对JSON字段进行索引呢？

想象一下，你的数据库里存了几百万甚至几千万条用户数据，每个用户的信息都以JSON格式存在一个字段里。有一天，老板突然发话了：“小李啊，给我查一下所有会Python的北京用户！”

如果你没对JSON字段进行任何索引，MySQL就只能乖乖地一条一条扫描，然后解析JSON，判断是否符合条件。这简直就是大海捞针，慢到你怀疑人生！🐌

所以，为了拯救你的头发，也为了让老板满意，我们必须想办法加速JSON字段的查询。而表达式索引，就是我们手中的屠龙宝刀！🗡️

二、 表达式索引：屠龙宝刀，锋芒毕露

MySQL 8.0 之前的版本，虽然也能用一些奇技淫巧来索引JSON字段，但总归有些局限性，不够优雅，性能也不够极致。

而表达式索引的出现，简直就是一道曙光，照亮了我们这些苦逼码农的道路！✨

啥是表达式索引？ 简单来说，它允许你基于一个表达式的结果来创建索引。这个表达式可以是任何合法的MySQL表达式，包括从JSON字段中提取特定值的表达式。

举个例子，假设我们的用户表users长这样：

id	user_info
1	{"name": "小明", "age": 25, "city": "上海", "skill": "Java"}
2	{"name": "小红", "age": 23, "city": "北京", "skill": "Python"}
3	{"name": "小刚", "age": 30, "city": "上海", "skill": "C++"}
…	…

user_info字段存储的是JSON格式的用户信息。

现在，我们要创建一个索引，基于city字段的值。用表达式索引，可以这么写：

CREATE INDEX idx_city ON users ((JSON_EXTRACT(user_info, '$.city')));

这条SQL语句的意思是：

• CREATE INDEX idx_city ON users：创建一个名为idx_city的索引，作用于users表。
• ((JSON_EXTRACT(user_info, '$.city')))：这是表达式索引的关键！JSON_EXTRACT()函数用于从JSON字段user_info中提取city字段的值。 '$.city' 是 JSON Path 表达式，指定要提取的 JSON 字段。 注意外面的括号，这是表达式索引的语法要求，必须用括号把表达式括起来。

有了这个索引，当我们执行以下查询时：

SELECT * FROM users WHERE JSON_EXTRACT(user_info, '$.city') = '北京';

MySQL 就能利用idx_city索引，快速定位到符合条件的记录，而不用全表扫描了！

三、 JSON函数：提取数据的神兵利器

刚才我们用到了JSON_EXTRACT()函数，这个函数是MySQL提供的众多JSON函数中的一个，它负责从JSON文档中提取数据。 MySQL 为我们提供了很多 JSON 函数，简直是居家旅行，必备良药！💊 我们来简单介绍几个常用的：

• JSON_EXTRACT(json_doc, path): 就是刚才用过的，根据路径从 JSON 文档中提取数据。 path 可以是单个路径，也可以是多个路径，返回的结果会组成一个新的 JSON 数组。
• JSON_VALUE(json_doc, path): 和 JSON_EXTRACT 类似，也是根据路径提取数据，但它会把提取出来的数据转换为标量值（例如字符串、数字、布尔值）。 如果提取出来的是 JSON 对象或数组，则返回 NULL。
• JSON_CONTAINS(json_doc, target, path): 判断 JSON 文档是否包含指定的 JSON 对象或值。 path 是可选的，用于指定在 JSON 文档的哪个部分进行查找。
• JSON_CONTAINS_PATH(json_doc, one_or_all, path): 判断 JSON 文档是否包含指定的路径。 one_or_all 可以是 'one' 或 'all'，表示只要包含任意一个路径或必须包含所有路径。
• JSON_ARRAY([val[, val] ...]): 创建一个 JSON 数组。
• JSON_OBJECT([key, val[, key, val] ...]): 创建一个 JSON 对象。
• JSON_INSERT(json_doc, path, val[, path, val] ...): 向 JSON 文档中插入数据。
• JSON_REPLACE(json_doc, path, val[, path, val] ...): 替换 JSON 文档中的数据。
• JSON_REMOVE(json_doc, path[, path] ...): 从 JSON 文档中删除数据。

有了这些函数，我们可以像玩积木一样，灵活地操作JSON数据，提取、修改、删除，想怎么玩就怎么玩！😎

四、 表达式索引的进阶玩法：花式秀操作

表达式索引的功能远不止提取单个字段那么简单，它还可以玩出更多花样，满足更复杂的查询需求。

1. 基于多个字段的索引

   我们可以把多个JSON字段组合起来，创建一个复合索引。例如，我们可以创建一个索引，基于city和age字段：

   CREATE INDEX idx_city_age ON users ((JSON_EXTRACT(user_info, '$.city')), (JSON_EXTRACT(user_info, '$.age')));

   这样，当我们执行以下查询时：

   SELECT * FROM users WHERE JSON_EXTRACT(user_info, '$.city') = '北京' AND JSON_EXTRACT(user_info, '$.age') > 25;

   MySQL 就能利用这个复合索引，更快地找到符合条件的记录。

2. 基于计算结果的索引

   表达式索引还可以基于计算结果创建索引。例如，我们可以创建一个索引，基于age字段的平方：

   CREATE INDEX idx_age_squared ON users ((CAST(JSON_EXTRACT(user_info, '$.age') AS SIGNED) * CAST(JSON_EXTRACT(user_info, '$.age') AS SIGNED)));

   注意，这里我们需要先把age字段转换为数字类型，才能进行计算。 CAST() 函数用于类型转换。

   有了这个索引，我们可以执行以下查询：

   SELECT * FROM users WHERE CAST(JSON_EXTRACT(user_info, '$.age') AS SIGNED) * CAST(JSON_EXTRACT(user_info, '$.age') AS SIGNED) > 900;

   虽然这种查询场景比较少见，但它展示了表达式索引的强大灵活性。

3. 索引虚拟列

   除了直接在CREATE INDEX语句中使用表达式，我们还可以先创建一个虚拟列，然后对虚拟列创建索引。

   ALTER TABLE users ADD COLUMN city VARCHAR(255) GENERATED ALWAYS AS (JSON_EXTRACT(user_info, '$.city'));
   CREATE INDEX idx_city ON users (city);

   这种方式的好处是，我们可以像操作普通列一样操作虚拟列，例如，我们可以直接查询city列：

   SELECT * FROM users WHERE city = '北京';

   虽然虚拟列本质上也是基于表达式计算出来的，但它可以让SQL语句更简洁易读。

五、 表达式索引的注意事项：坑爹的地方也得提

表达式索引虽好，但也不是万能的，在使用时需要注意以下几点：

1. 性能损耗

   表达式索引需要在每次插入或更新数据时计算表达式的值，这会带来一定的性能损耗。 所以，不要滥用表达式索引，只对那些经常用于查询的JSON字段创建索引。

2. 索引长度限制

   MySQL的索引长度是有限制的。如果表达式的结果太长，可能会导致索引创建失败。 对于字符串类型的JSON字段，可以考虑只索引前缀。

3. 表达式的确定性

   表达式必须是确定性的，也就是说，对于相同的输入，表达式必须返回相同的结果。 否则，索引可能会失效。 例如，RAND() 函数就不能用于表达式索引，因为它每次都会返回不同的随机数。

4. 数据类型

   表达式的结果必须是可索引的数据类型，例如数字、字符串、日期等。 不能索引JSON对象或数组。

5. 版本要求

   表达式索引是MySQL 8.0及以上版本才支持的特性。 如果你还在使用MySQL 5.7，那就只能羡慕嫉妒恨了。😢

六、 实战演练：手把手教你优化JSON查询

光说不练假把式，咱们来个实战演练，看看如何利用表达式索引优化JSON查询。

假设我们有一个products表，用于存储商品信息，其中product_info字段存储的是JSON格式的商品详情：

id	product_info
1	{"name": "iPhone 13", "price": 7999, "category": "手机", "attributes": {"screen_size": "6.1英寸", "color": "蓝色", "storage": "128GB"}}
2	{"name": "华为 Mate 50", "price": 6999, "category": "手机", "attributes": {"screen_size": "6.7英寸", "color": "黑色", "storage": "256GB"}}
3	{"name": "小米 12S Ultra", "price": 5999, "category": "手机", "attributes": {"screen_size": "6.73英寸", "color": "白色", "storage": "512GB"}}
…	…

现在，我们要查询所有屏幕尺寸大于6.5英寸的手机。

如果没有索引，我们可以这样写：

SELECT * FROM products WHERE CAST(JSON_EXTRACT(product_info, '$.attributes.screen_size') AS DECIMAL(3,1)) > 6.5;

这条SQL语句会进行全表扫描，效率非常低。

为了优化查询，我们可以创建一个表达式索引：

CREATE INDEX idx_screen_size ON products ((CAST(JSON_EXTRACT(product_info, '$.attributes.screen_size') AS DECIMAL(3,1))));

有了这个索引，再次执行上面的查询，MySQL就能利用索引快速定位到符合条件的记录，查询速度会得到显著提升。

七、 总结：有了表达式索引，腰不酸了，腿不疼了，查询也更快了！

好了，各位观众，今天的分享就到这里了。希望通过今天的讲解，大家能够掌握MySQL 8.0表达式索引的用法，并将其应用到实际项目中，让你的JSON查询速度像坐了火箭一样！🚀

记住，表达式索引是优化JSON查询的一大利器，但也要合理使用，避免过度索引。

最后，祝大家编码愉快，Bug永不相见！🙏

(文章结束)

表情包插入示例：

• “别跟我说你不知道，不知道的自觉去面壁思过三分钟！😎”
• “这简直就是大海捞针，慢到你怀疑人生！🐌”
• “而表达式索引的出现，简直就是一道曙光，照亮了我们这些苦逼码农的道路！✨”
• “MySQL 为我们提供了很多 JSON 函数，简直是居家旅行，必备良药！💊”
• “我们可以像玩积木一样，灵活地操作JSON数据，提取、修改、删除，想怎么玩就怎么玩！😎”
• “如果你还在使用MySQL 5.7，那就只能羡慕嫉妒恨了。😢”
• “查询速度会得到显著提升。” (可以插入一个火箭升空的表情)
• “让你的JSON查询速度像坐了火箭一样！🚀”
• “最后，祝大家编码愉快，Bug永不相见！🙏” (可以插入一个双手合十祈祷的表情)

(文章结束)