Mysql Json字段 函数索引、多值索引使用

需求： 使用Mysql的Json字段实现类似Nosql的嵌套字段，利用Mysql 8 的多值索引（Multi-Valued Indexes）提高Json字段的查询效率

PS： 为什么嵌套格式的数据不直接用MongoDB等Nosql数据库呢？因为Nosql的事务支持不够强，目前Nosql事务支持比较好的是MongoDB，但是没办法满足单个文档多事务的场景，简单来说，就是当要修改一行数据的时候，如果加了事务，会给整个文档加上写锁（相当于Mysql的表级锁），这时候其他行的修改请求是没办法执行的【绝望.gif】，这点是目前Nosql数据库跟Mysql不能比的，因此Nosql比较适用于对事务要求比较低的场景。
不要问我为什么不把嵌套格式的数据保存到单独的表里，问了就是因为 懒。
现在Mysql支持Json格式后真好，又能联表又能用嵌套格式。 (〃‘▽’〃)
个人人为未来SQL和Nosql的界限会越来越模糊，最终卷成一团。

说回正题。

用了Json格式后，感觉还不错，但是有个问题要解决才能愉快的用下去。如果要根据Json字段里的某个属性作为查询条件，如果数据量大的话要怎么加索引？

Mysql 8 提供了两种新的索引：函数索引 & 多值索引

表

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮箱',
  `wallets` json DEFAULT NULL COMMENT '钱包',
  `other_info` json DEFAULT NULL COMMENT '其他信息',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

wallets内容：

[
    {
        "name":"支付宝钱包",
        "currencyList":[
        	{"type":"USD","amount":999.19},
            {"type":"RMB","amount":1000.19}
        ]
    },
    {
        "name":"微信钱包",
        "currencyList":[
            {"type":"USD","amount":888.18,"userId":2},
            {"type":"RMB","amount":1000.18,"userId":1}
        ]
    }
]

场景一：
要查询other_info中城市为南昌的数据，other_info查询比较简单，只是一个Json对象：

SELECT * FROM user where other_info->>'$.city'='南昌';

查询结果：

看执行计划：

加函数索引：

CREATE index func_idx_city on  `user`(( CAST(other_info->>'$.city'  AS char(128) )));

查询：

SELECT * FROM `user` where CAST(other_info->>'$.city'  AS char(128)) ='南昌';

看执行计划，发现已经使用了索引：

场景二：
要查询所有记录中包含wallets的name为‘微信钱包’的数据，wallets是一个对象数组，添加函数索引时，要指定为多值索引

添加索引：

CREATE index func_idx_name on  `user`(( CAST(wallets->'$[*].name'  AS char(128)  ARRAY)));

以下三种方式都可以查询出结果：
1、SELECT * FROM user where '微信钱包' member of ( wallets->'$[*].name') ;
2、SELECT * FROM user where JSON_CONTAINS(wallets->'$[*].name' , cast('["微信钱包"]' as json));
3、SELECT * FROM user where JSON_CONTAINS(wallets , JSON_OBJECT('name', '微信钱包'));

查询结果：

查看执行计划，发现只有1和2能使用到索引
1、

2、

3、