簡(jiǎn)單談?wù)凪ySQL5.7 JSON格式檢索

MySQL5.7版本開始支持JSON格式，在創(chuàng)建表時(shí)，可以指定列表的數(shù)據(jù)類型為JSON，但是如何在JSON格式上創(chuàng)建索引呢？？

本人做了一個(gè)簡(jiǎn)單測(cè)試。

第一步：建立一個(gè)包含JSON類型的表：

CREATE TABLE json_test` (
 id` int (8) NOT NULL AUTO_INCREMENT,
 content` json NOT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

第二步：初始化數(shù)據(jù)

insert into json_test(content) value( '{"name":"zhangsan","age":18}' );
insert into json_test(content) value( '{"name":"lisi","age":19}' );
insert into json_test(content) value( '{"name":"wangwu","age":20}' );

第三步：查詢JSON類列的數(shù)據(jù)

select json_extract(content,  '$.name' )  from json_test  where json_extract(content,  '$.name' )= "zhangsan" ;

通過expain分析改查詢語句，發(fā)現(xiàn)其走全表掃描

在網(wǎng)上查詢資料，得知如果要在JSON列上進(jìn)行檢索，需要對(duì)檢索的key創(chuàng)建虛擬列，然后再虛擬列上創(chuàng)建索引

第四步：在content列上，對(duì)"name"建立虛擬列

ALTER TABLE json_test  ADD name_virtual  varchar (32) GENERATED ALWAYS  AS (json_extract(content,  '$.name' )) VIRTUAL;


第五步：對(duì)虛擬列創(chuàng)建索引

CREATE INDEX name_virtual_index  ON json_test(name_virtual);


再次做查詢（ 注，where條件需要使用虛擬列來進(jìn)行檢索，如果直接用JSON列比較，還是會(huì)走全表掃描 ）

explain  select json_extract(content,  '$.name' )  from json_test  where name_virtual= "zhangsan" \G

總結(jié)：

其實(shí)MySQL通過一種空間換時(shí)間的做法，類似創(chuàng)建一個(gè)觸發(fā)器，把JSON列上的數(shù)據(jù)冗余存儲(chǔ)到虛擬列上，比較的時(shí)候通過走虛擬列的索引，再定位到實(shí)際數(shù)據(jù)。