需求: 使用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、