MySql8.0实战之JSON

公司使用的数据库是MySql8.0版本的，而且在用户表中有个字段使用了JSON类型，最近线上一直报CPU异常，最终确定了是查找这个JSON字段的时候没有走索引导致了全表扫描。今天就来复习一下JSON字段如何添加索引

创建一张表，loginInfo为JSON类型

CREATE TABLE UserLogin (
    userId BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(userId)
);

插入二条记录

SET @a = '
    {
    "cellphone" : "13918888888",
    "wxchat" : "破产码农",
    "QQ" : "82946772"
    }
';
INSERT INTO UserLogin VALUES (1,@a);

SET @b = ' 
  {
   "cellphone" : "15026888888" 
   "wxchat" : "java",
   "QQ" : "82946772898"
  }
';

INSERT INTO UserLogin VALUES (2,@b);

1：查询loginInfo这个JSON字段中的cellphone的值

explain 
select userId,
  JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone
from UserLogin;

也可以使用 ->> 来写，就不需要使用JSON_UNQUOTE函数了

explain 
  select userId,
  loginInfo ->> "$.cellphone" cellphone
  from UserLogin;
  
但是发现走的就是全表扫描

2：为loginInfo字段中的cellphone建立虚拟列，然后创建索引

alter table UserLogin add column cellphone varchar(255) as (loginInfo ->> "$.cellphone");

alter table UserLogin add unique index idx_cellphone(cellphone);

但是此时如果我们要查询loginInfo中的cellphonec的值就要换成下面这种方式

explain select * from UserLogin where cellphone = "13918888888"

此时是走了索引的

3：如果JSON是一个数组

创建一张表

CREATE TABLE UserTag (
    userId bigint NOT NULL,
    userTags JSON,
    PRIMARY KEY (userId)
);

插入一条数据
INSERT INTO UserTag VALUES (1,'[2,6,8,10]'); INSERT INTO UserTag VALUES (2,'[3,10,12]');

4：查询某个或者某几个元素是否在数据中，走的都是全表扫描

//查询出有userTags中包含 10 的记录
explain select * from UserTag where 10 MEMBER OF(userTags->"$")

//查询出有userTags中包含 2和10 的记录
explain select * from UserTag where JSON_CONTAINS(userTags->"$", '[2,10]')

//查询出有userTags中包含 2和10 的记录
explain select * from UserTag where JSON_OVERLAPS(userTags->"$",'[2,10]')

5：加索引，然后再执行发现上面三条SQL都走了索引

alter table UserTag add index idx_user_tag ((cast((userTags->"$") as unsigned array)));