TDSQL Boundless JSON 多值索引

一、概述

JSON 多值索引适用于以下场景：

·JSON 数组字段被频繁用于 MEMBER OF()、JSON_CONTAINS()、JSON_OVERLAPS() 等条件查询，且表数据量较大、全表扫描性能无法满足要求。

·商品标签、用户兴趣、权限列表等以 JSON 数组存储的多值属性的查询。

二、支持版本

适用于 TDSQL Boundless V21.6.4.0 及以上版本。

三、工作原理

JSON 多值索引本质上是一种函数索引，通过 CAST(json_col->'$[*]' AS type ARRAY) 表达式定义，由内核在数据写入时自动维护。内核将 JSON 数组中每个元素分别展开并转换为指定类型，将其逐条存储到索引中；查询时，优化器根据条件判断是否可以利用该索引，自动采用 Range 扫描或 MRR 方式读取，并通过内建的去重过滤器确保同一行数据不因数组中有多个元素命中索引而被重复返回。

对于含多值索引的表执行批量 UPDATE/DELETE 时，TDSQL Boundless 默认启用批量优化逻辑（由参数 tdsql_stmt_optim_mv_index 控制，默认开启），以提升写入效率。

四、使用限制

·每个多值索引只能包含一个多值键部分，不支持将多个 CAST(… AS type ARRAY) 表达式组合为复合索引；多值键部分可与普通列组成复合索引。

·多值索引不支持指定显式排序方向（ASC/DESC）。

·多值索引暂时不支持 全局二级索引（GSI）形态。

·CAST(… AS … ARRAY) 表达式不能在存储程序（存储过程、存储函数、触发器、事件）中使用。

·CAST 表达式中不支持显式指定字符集（BINARY(n) 指定 binary 字符集除外）。CHAR(n) 的字符集由内核自动使用 utf8mb4_0900_bin，无需手动指定。

·CAST 目标类型不支持 YEAR、DOUBLE、FLOAT、JSON 及所有空间类型（POINT、LINESTRING、POLYGON 等）。

五、创建语法

JSON 多值索引通过 CAST(json_col->'$[*]' AS type ARRAY) 表达式定义，支持在建表时声明，也支持在已有表上通过 CREATE INDEX 或 ALTER TABLE 添加。多值索引也支持 UNIQUE 约束，可对数组元素进行唯一性校验。

— 建表时定义多值索引CREATE TABLE t_orders ( order_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, tags JSON, INDEX idx_tags ( (CAST(tags->'$[*]' AS CHAR(64) ARRAY)))) ENGINE=ROCKSDB;– 在已有表上通过 CREATE INDEX 添加CREATE INDEX idx_tags ON t_orders ( (CAST(tags->'$[*]' AS CHAR(64) ARRAY)));– 在已有表上通过 ALTER TABLE 添加ALTER TABLE t_orders ADD INDEX idx_tags ( (CAST(tags->'$[*]' AS CHAR(64) ARRAY)));

CAST 目标类型支持 SIGNED、UNSIGNED、DECIMAL(M,D)、DATE、DATETIME[(fsp)]、TIME[(fsp)]、CHAR(n)、BINARY(n)。其中 CHAR(n) 和 BINARY(n) 的长度 n 不能超过512，超过该长度将视为 BLOB 类型而报错。

六、使用示例

以下示例展示了 JSON 多值索引在用户兴趣标签场景中的完整用法。

创建带多值索引的表并插入数据：

CREATE TABLE user_info ( user_id BIGINT PRIMARY KEY, name VARCHAR(64) NOT NULL, hobbies JSON) ENGINE=ROCKSDB;ALTER TABLE user_info ADD INDEX idx_hobbies ( (CAST(hobbies->'$[*]' AS CHAR(64) ARRAY)) );INSERT INTO user_info VALUES (1, 'Alice', '["reading", "hiking", "knitting"]');INSERT INTO user_info VALUES (2, 'Bob', '["hiking", "swimming", "camping"]');INSERT INTO user_info VALUES (3, 'Carol', '["reading", "painting"]');

使用 MEMBER OF() 查询包含某个爱好的用户：

SELECT user_id, nameFROM user_infoWHERE 'hiking' MEMBER OF (hobbies->'$[*]');

使用 JSON_CONTAINS() 查询包含多个爱好之一的用户：

SELECT user_id, nameFROM user_infoWHERE JSON_CONTAINS(hobbies->'$[*]', CAST('["reading"]' AS JSON));

使用 JSON_OVERLAPS() 查询兴趣有交集的用户：

SELECT user_id, nameFROM user_infoWHERE JSON_OVERLAPS(hobbies->'$[*]', CAST('["hiking", "camping"]' AS JSON));

七、NULL 与空数组行为

当 JSON 列为 NULL 或空数组时，MEMBER OF()、JSON_CONTAINS()、JSON_OVERLAPS() 等数组元素级条件不会命中该行。在复合索引仅使用非多值键部分的前缀查询中，值为 NULL 或空数组的行仍可通过索引中的占位条目被正常返回。

八、验证索引是否生效

通过 EXPLAIN 查看执行计划，确认查询已使用多值索引。当 key 列显示为多值索引名称时，表明多值索引已生效：MEMBER OF() 查询通常以 ref 方式访问，JSON_OVERLAPS()/JSON_CONTAINS() 查询通常以 range + Using MRR 方式访问。

EXPLAIN SELECT user_id, nameFROM user_infoWHERE 'hiking' MEMBER OF (hobbies->'$[*]');

九、相关参数