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ù)。
您可能感興趣的文章:- MySQL5.7中的JSON基本操作指南
- MySQL5.7 JSON類型使用詳解
- Mysql5.7中JSON操作函數(shù)使用說明
- mysql5.6及以下版本如何查詢數(shù)據(jù)庫里的json
- 詳解Mysql中的JSON系列操作函數(shù)
- MySQL操作之JSON數(shù)據(jù)類型操作詳解
- MySQL插入json問題
- mysql(5.6及以下)解析json的方法實(shí)例詳解
- mysql5.7 新增的json字段類型用法實(shí)例分析