Lindorm SQL通配符功能与JSON列完整使用示例-云原生多模数据库 Lindorm(Lindorm)-阿里云帮助中心

本文档提供了 Lindorm 宽表引擎中通配符 JSON 列的完整使用示例，涵盖建表、数据操作、索引管理和查询优化的全流程演示。阅读本文内容之前，建议先阅读并了解通配符列。

场景说明

假设我们要构建一个用户配置管理系统，需要存储用户的各种配置信息。由于不同用户可能有不同类型的配置项，我们使用通配符 JSON 列来实现灵活的 Schema 设计。

一、建表

1. 创建带通配符 JSON 列的表

-- 创建用户配置表
-- pk: 用户ID
-- username: 用户名
-- created_at: 创建时间
-- `config_*`: JSON 类型的通配符列，用于存储各种配置
CREATE TABLE user_config (
    user_id VARCHAR,
    username VARCHAR,
    created_at TIMESTAMP,
    `config_*` JSON,
    PRIMARY KEY(user_id)
) WITH (
    wildcard_column='config_*',
    CONSISTENCY='strong',
    MUTABILITY='MUTABLE_LATEST'
);

说明

config_* 是通配符模式，可以匹配 config_profile、config_preference、config_settings 等任意以 config_ 开头的列。
wildcard_column='config_*' 声明了通配符列模式。
所有匹配该模式的列都将是 JSON 类型。

2. 验证表结构

-- 查看表结构
DESCRIBE user_config;

-- 查看建表语句
SHOW CREATE TABLE user_config;

二、插入数据

1. 插入基础配置数据

-- 插入用户1的配置
UPSERT INTO user_config(
    user_id, 
    username, 
    created_at,
    config_profile,
    config_preference
) VALUES (
    'user001',
    'alice',
    CURRENT_TIMESTAMP,
    '{"avatar": "https://example.com/avatar1.jpg", "bio": "Software Engineer", "age": 28}',
    '{"theme": "dark", "language": "zh-CN", "notifications": true}'
);

-- 插入用户2的配置（包含更多配置项）
UPSERT INTO user_config(
    user_id,
    username,
    created_at,
    config_profile,
    config_preference,
    config_privacy
) VALUES (
    'user002',
    'bob',
    CURRENT_TIMESTAMP,
    '{"avatar": "https://example.com/avatar2.jpg", "bio": "Data Scientist", "age": 32, "location": "Beijing"}',
    '{"theme": "light", "language": "en-US", "notifications": false}',
    '{"profile_visible": true, "email_visible": false, "allow_messages": true}'
);

-- 插入用户3的配置（包含嵌套 JSON）
UPSERT INTO user_config(
    user_id,
    username,
    created_at,
    config_profile,
    config_settings
) VALUES (
    'user003',
    'charlie',
    CURRENT_TIMESTAMP,
    '{"avatar": "https://example.com/avatar3.jpg", "bio": "Product Manager", "age": 35}',
    '{"display": {"font_size": 14, "line_height": 1.5}, "editor": {"auto_save": true, "tab_size": 4}}'
);

2. 批量插入数据

-- 批量插入多个用户配置
UPSERT INTO user_config(user_id, username, created_at, config_profile, config_preference) VALUES
    ('user004', 'david', CURRENT_TIMESTAMP, '{"avatar": "https://example.com/avatar4.jpg", "age": 25}', '{"theme": "dark", "language": "zh-CN"}'),
    ('user005', 'emma', CURRENT_TIMESTAMP, '{"avatar": "https://example.com/avatar5.jpg", "age": 29}', '{"theme": "light", "language": "en-US"}'),
    ('user006', 'frank', CURRENT_TIMESTAMP, '{"avatar": "https://example.com/avatar6.jpg", "age": 31}', '{"theme": "dark", "language": "ja-JP"}');

三、基础查询

1. 查询所有数据

-- 查询所有用户配置
SELECT * FROM user_config LIMIT 100;

-- 查询指定列
SELECT user_id, username, config_profile, config_preference 
FROM user_config;

2. 使用 JSON 操作符查询

说明

使用此功能时，要求宽表引擎版本号不低于2.8.6.5。

-- 使用 -> 操作符提取 JSON 值
SELECT 
    user_id,
    username,
    config_profile->'$.age' AS age,
    config_profile->'$.bio' AS bio
FROM user_config;

-- 使用 ->> 操作符提取 JSON 值
SELECT 
    user_id,
    username,
    config_profile->>'$.age' AS age,
    config_profile->>'$.bio' AS bio,
    config_preference->>'$.theme' AS theme
FROM user_config;

3. 使用 JSON 函数查询

-- 使用 json_extract_string 提取字符串值
SELECT 
    user_id,
    username,
    json_extract_string(config_profile, '$.bio') AS bio,
    json_extract_string(config_preference, '$.theme') AS theme
FROM user_config;

-- 使用 json_extract_long 提取数值
SELECT 
    user_id,
    username,
    json_extract_long(config_profile, '$.age') AS age
FROM user_config;

四、创建二级索引

Lindorm 宽表默认支持基于主键的高效查询。当需要根据 JSON 列中的某个字段进行条件查询时，如果没有索引，查询将触发全表扫描并被系统拒绝。通过为常用查询字段创建二级索引，可以显著提升查询性能，避免全表扫描。

1. 为常用查询字段创建索引

-- 为 config_profile 中的 bio 字段创建索引
CREATE INDEX idx_profile_bio 
ON user_config (json_extract_string(config_profile, '$.bio'));

-- 为 config_preference 中的 theme 字段创建索引
CREATE INDEX idx_preference_theme 
ON user_config (json_extract_string(config_preference, '$.theme'));

-- 为 config_preference 中的 language 字段创建索引
CREATE INDEX idx_preference_language 
ON user_config (json_extract_string(config_preference, '$.language')) ;

2. 创建包含覆盖列的索引

-- 创建包含覆盖列的索引，避免回表查询
CREATE INDEX idx_profile_age_with_cover 
ON user_config (json_extract_long(config_profile, '$.age')) 
INCLUDE(username, config_profile, config_preference);

3. 查看索引信息

-- 查看表的所有索引
SHOW INDEX FROM user_config;

-- 查看索引详细信息
SHOW TABLE VARIABLES FROM user_config;

五、使用二级索引的查询

1. 单条件查询

-- 查询年龄等于 30 的用户（使用索引）
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') = 30;

-- 查询年龄大于 30 的用户（使用索引）
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;

-- 查询使用暗色主题的用户（使用索引）
SELECT user_id, username, config_preference->>'$.theme' AS theme
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';

2. 多条件查询

-- 查询年龄大于 25 且使用暗色主题的用户
SELECT 
    user_id, 
    username,
    json_extract_long(config_profile, '$.age') AS age,
    json_extract_string(config_preference, '$.theme') AS theme
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 25
  AND json_extract_string(config_preference, '$.theme') = 'dark';

-- 查询年龄在 25-35 之间的用户
SELECT 
    user_id, 
    username,
    json_extract_long(config_profile, '$.age') AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') BETWEEN 25 AND 35;

3. 验证索引使用

-- 使用 EXPLAIN 查看执行计划
EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;
-- 输出应包含: "Chose index table [idx_profile_age]"

EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';
-- 输出应包含: "Chose index table [idx_preference_theme]"

六、更新数据

1. 更新整个 JSON 列

-- 更新用户的 profile 配置
UPSERT INTO user_config(user_id, config_profile)
VALUES (
    'user001',
    '{"avatar": "https://example.com/new_avatar1.jpg", "bio": "Senior Software Engineer", "age": 29, "skills": ["Java", "Python", "SQL"]}'
);

-- 更新用户的 preference 配置
UPSERT INTO user_config(user_id, config_preference)
VALUES (
    'user002',
    '{"theme": "auto", "language": "zh-CN", "notifications": true, "email_digest": "daily"}'
);

2. 添加新的配置列

-- 为用户添加新的配置项
UPSERT INTO user_config(user_id, config_security)
VALUES (
    'user001',
    '{"two_factor_enabled": true, "login_alerts": true, "trusted_devices": ["device1", "device2"]}'
);

-- 同时更新多个配置列
UPSERT INTO user_config(
    user_id,
    config_profile,
    config_preference,
    config_security
) VALUES (
    'user003',
    '{"avatar": "https://example.com/avatar3_new.jpg", "bio": "Senior Product Manager", "age": 36}',
    '{"theme": "dark", "language": "zh-CN", "notifications": true}',
    '{"two_factor_enabled": false, "login_alerts": false}'
);

七、搜索索引

1. 创建搜索索引

-- 情况一：对config_*建索引，类型根据实际写入值进行推断，子字段只能推断成基础类型如long、keyword等
CREATE INDEX idx_search USING SEARCH ON user_config(
  `config_*`
);

-- 情况二：对config_*建索引，指定其中子字段的类型，比如所有config_*下面的address子字段都指定为text类型
CREATE INDEX idx_search USING SEARCH ON user_config(
  `config_*`(mapping='{
      "type":"object",
      "properties": {
          "address": {
            "type": "text",
            "analyzer": "ik_max_word"
          }
      }
  }')
);

2. 查询搜索索引

-- 查询年龄等于 30 的用户（使用索引）
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') = 30;

-- 查询年龄大于 30 的用户（使用索引）
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;

-- 查询使用暗色主题的用户（使用索引）
SELECT user_id, username, config_preference->>'$.theme' AS theme
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';

3. 验证查询使用搜索索引

-- 使用 EXPLAIN 查看执行计划
EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;
-- 输出应包含: "Chose search index [idx_search]"

EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';
-- 输出应包含: "Chose search index [idx_search]"