为降低处理半结构化数据的难度,提升查询效率,AnalyticDB for MySQL提供了半结构化数据检索功能,即JSON索引。本文主要介绍如何创建JSON索引和JSON Array索引。
JSON索引介绍
AnalyticDB for MySQL支持创建JSON索引和JSON Array索引。通过为存储在JSON列中的数据创建JSON索引或JSON Array索引,可以避免在查询数据时扫描全表数据或对整个JSON文档进行解析,从而提升数据查询效率。JSON索引主要适用于存储和查询复杂半结构化数据的场景,例如日志信息、配置文件、设备信息等。
注意事项
JSON索引和JSON Array索引的列数据类型必须为JSON。
一个JSON索引或JSON Array索引仅能包含一个JSON列。如需对多个JSON列创建索引,可创建多个JSON索引或JSON Array索引。
创建JSON索引
创建JSON索引时,您还需注意以下内容:
3.1.5.10及以上内核版本的集群,创建表后不会自动创建JSON索引,您需手动创建JSON索引。
3.1.5.10以下内核版本的集群,创建表后会自动为JSON列创建JSON索引。
查看企业版或湖仓版集群的内核版本,请执行SELECT adb_version();
。如需升级内核版本,请联系技术支持。
创建表时创建JSON索引
注意事项
若您在建表时手动指定为某一列或某几列创建INDEX索引,AnalyticDB for MySQL不会再为表中其他列自动创建INDEX索引。
语法
CREATE TABLE table_name(
column_name column_type,
{INDEX|KEY} [index_name](column_name|column_name->'$.json_path')
)
DISTRIBUTED BY HASH(column_name)
参数说明
参数 | 说明 |
index_name | JSON索引名称。 重要 索引名称唯一,不能相同。 |
column_name|column_name->'$.json_path' |
|
建表语句的其他参数,请参见CREATE TABLE。
示例
为
json_test
表中的JSON列vj
创建JSON索引。CREATE TABLE json_test( id int, vj json, index idx_vj(vj) ) DISTRIBUTED BY HASH(id);
为
json_test
表中JSON列vj
的属性键name
创建JSON索引。CREATE TABLE json_test( id int, vj json COMMENT index idx_vj_path(vj->'$.name') ) DISTRIBUTED BY HASH(id);
为已存在的表创建JSON索引
语法
ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name|column_name->'$.json_path',...)
参数说明
参数 | 说明 |
db_name | 数据库名称。 |
table_name | 表名称。 |
index_name | JSON索引名称。 重要 索引名称唯一,不能相同。 |
column_name|column_name->'$.json_path' |
|
示例
为
json_test
表中的JSON列vj
创建JSON索引。ALTER TABLE json_test ADD KEY index_vj(vj);
为
json_test
表中JSON列vj
的属性键name
创建JSON索引。ALTER TABLE json_test ADD KEY index_vj_key(vj->'$.name');
创建JSON Array索引
仅3.1.10.6及以上内核版本的集群支持创建JSON Array索引。创建JSON Array索引后,您可以使用JSON_CONTAINS和JSON_OVERLAPS函数检索数据,提高数据查询效率。
注意事项
创建JSON Array索引时,AnalyticDB for MySQL仅会为数组中的数值和字符串类型元素构建索引,不会为数组中的其他类型(例如嵌套数组、对象等)元素构建索引。
创建表时创建JSON Array索引
语法
CREATE TABLE table_name(
column_name column_type,
{INDEX|KEY} [index_name](column_name->'$[*]')
)
DISTRIBUTED BY HASH(column_name);
参数说明
参数 | 说明 |
index_name | JSON Array索引名称。 重要 索引名称唯一,不能相同。 |
column_name->'$[*]' |
|
示例
为json_test
表中的JSON列vj
创建JSON Array索引。
CREATE TABLE json_test(
id int,
vj json,
index idx_vj_array(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);
为已存在的表创建JSON Array索引
注意事项
使用ALTER TABLE语句创建JSON Array索引,需要等待BUILD完成后,JSON Array索引才能生效。自动BUILD、手动BUILD以及BUILD任务的进度,请参见BUILD。
语法
ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name->'$[*]')
参数说明
参数 | 说明 |
db_name | 数据库名称。 |
table_name | 表名称。 |
index_name | JSON Array索引名称。 重要 索引名称唯一,不能相同。 |
column_name->'$[*]' |
|
示例
为json_test
表中的JSON列vj
创建JSON Array索引。
ALTER TABLE json_test ADD KEY index_vj(vj->'$[*]');
删除索引
语法
ALTER TABLE db_name.table_name DROP KEY index_name
参数说明
index_name:普通索引名称。您通过SHOW INDEX FROM db_name.table_name;
查询index_name
。
示例
删除
customer
表中名为age_idx
的索引。ALTER TABLE adb_demo.customer DROP KEY age_idx;
删除
json_test
表中名为index_vj
的JSON Array索引。ALTER TABLE adb_demo.customer DROP KEY index_vj;
相关文档
如何检索JSON数据,请参见JSON函数。