搜索索引

本文说明如何使用搜索索引（Search Index）包括能力概要、使用前提、支持的数据类型与谓词、常用 DDL 及查询示例。

搜索索引可在单列或多列上构建统一的倒排结构，对 JSON 列内多条路径（或标量列整列取值）建立索引条目；一条索引即可覆盖多路径访问并支撑多种谓词。与“为每个访问路径单独建函数索引”或“偏数组元素展开的多值索引”相比，搜索索引强调一次建索引、覆盖多路径，降低索引数量与维护成本，并便于应对半结构化数据中路径多变、查询模式多样的情况。

功能说明

• 典型场景：与混合搜索共同使用，或与分析型宽表上多列搜索索引 + 多索引联合查询（Index Merge）共同使用，提升性能。
  • AI 混合搜索场景：全文分词与关键词搜索由全文索引负责，搜索索引侧重复杂类型内部路径的结构化/半结构化条件（也可用于简单标量），二者适用列类型不同，可同表并存并与向量索引共同使用。
  • AP 分析型场景：在宽表、多列分别建有搜索索引的前提下，优化器可借助 Index Merge 能力自动识别可用的索引组合并选择较优扫描路径，提升性能。
• DDL 操作：搜索索引的 DDL 操作与普通索引一致，支持创建、删除、重命名、可见性等变更。支持在 SEARCH 前增加 ASYNC 关键字创建异步搜索索引，将索引维护改为后台增量刷新。
• 嵌套查询（Nested Query）：JSON 对象数组默认扁平化存储，会丢失同对象字段的关联。通过 NESTED_PATHS 声明嵌套路径后，可在混合搜索语法中使用嵌套查询，要求多条件命中同一个数组元素，防止系统在一篇文档中“东拼西凑”。

功能和语法限制

• 租户：该功能仅适用于 MySQL 模式租户。
• 表类型：目标表须为 ORGANIZATION = HEAP 堆表。
• 列级配置：列级 WITH (...)/ NESTED_PATHS 均仅对 JSON 列生效。
• JSON 路径长度：编码后的 JSON 路径最大为 2KB。如果 JSON 文档的键名非常长或嵌套层级极深，DML 操作时可能因路径超限而失败（ERROR 1071）。建议 JSON 文档设计时避免超长键名。
• 数组类型：当前仅支持单级数组，不支持嵌套数组。
• 嵌套路径：
  • NESTED_PATHS 中的路径只能由对象成员名组成（如 $.comments、$.comments.replies），不允许是通配符（如 $.comments.*）或数组下标（如 $.comments[0]）；
  • 路径段数（不含根路径 $）不超过 20；
  • 每个搜索索引列只允许出现一个 NESTED_PATHS 子句；
  • 不支持将根路径 $ 声明为嵌套路径。
• 分区表：当前分区表仅支持局部搜索索引（LOCAL）。
• 索引列：不支持对索引列的 LIKE 模式匹配加速。
• 生成列：不支持在生成列（GENERATED）上创建搜索索引；不支持 STORING 子句。
• 异步搜索索引：不支持临时表、全局索引（GLOBAL）；同一张表上同步搜索索引与异步搜索索引互斥，但允许创建多个异步搜索索引，并可与同步或异步全文索引共存。查询语法与同步搜索索引一致，索引数据由后台增量刷新，仅保证最终一致性。

使用方法

支持的数据类型

搜索索引主要用于标量列和 JSON / 单级数组列的查询加速，具体支持的类型如下：

限制说明如下：

• 不支持在 BIT、ENUM、SET、MAP 类型列上创建搜索索引。
• 对于 PICK 类型，json_number 字段支持的数据类型包括整数类型、浮点/定点类型；json_string 字段支持字符串类型。

语法及参数

搜索索引的创建方式包括随表建、后建和追加。每种方式具体语法和通用参数项说明如下：

CREATE TABLE table_name (
  column_name type
  [, column_name type ...]

  SEARCH INDEX index_name (
    column_name [WITH (option_list)]
    [, column_name [WITH (option_list)] ...]
  )
) ORGANIZATION = HEAP;

option_list:
    [INCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| INCLUDE_TYPES = (type1[, type2]...)]
    [| NESTED_PATHS = ('path1'[, 'path2']...)]

CREATE SEARCH INDEX index_name ON table_name (
    column_name [WITH (option_list)]
    [, column_name [WITH (option_list)] ...]
) [LOCAL];

option_list:
    [INCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| INCLUDE_TYPES = (type1[, type2]...)]
    [| NESTED_PATHS = ('path1'[, 'path2']...)]

ALTER TABLE table_name ADD SEARCH INDEX index_name (
  column_name [WITH (option_list)]
  [, column_name [WITH (option_list)] ...]
);

option_list:
    [INCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| INCLUDE_TYPES = (type1[, type2]...)]
    [| NESTED_PATHS = ('path1'[, 'path2']...)]

其他删除、重命名、可见性等变更操作与常规索引一致，完整语法请参见文末相关文档。

异步搜索索引

异步搜索索引复用同步 Search Index 的 DDL，在 SEARCH 前增加 ASYNC 关键字，支持随表创建与后建。SEARCH KEY 与 SEARCH INDEX 为语法别名，不影响索引类型；独立后建仅支持 CREATE ASYNC SEARCH INDEX，不支持 CREATE ASYNC SEARCH KEY。

开启与使用

异步索引功能默认关闭，需通过 _enable_async_index 配置项开启：

ALTER SYSTEM SET _enable_async_index = true;

CREATE TABLE t (
  id BIGINT,
  doc JSON,
  ASYNC SEARCH INDEX idx_search (doc)
) ORGANIZATION HEAP;

-- 配置 Nested 路径（可同时声明多条）
CREATE TABLE t (
  id BIGINT,
  doc JSON,
  ASYNC SEARCH INDEX idx_search (
    doc WITH (
      NESTED_PATHS = ('$.comments', '$.items')
    )
  )
) ORGANIZATION HEAP;

CREATE ASYNC SEARCH INDEX idx_search ON t(doc);

ALTER TABLE t ADD ASYNC SEARCH INDEX idx_search(doc);

ALTER TABLE t ADD ASYNC SEARCH INDEX idx_search (
  doc WITH (
    NESTED_PATHS = ('$.comments')
  )
);

查询语法与同步搜索索引的混合搜索写法一致，支持标量与 JSON 上的 term、terms、range 等条件。写入后需等待后台增量刷新完成，数据才对查询可见。

SELECT id, doc FROM HYBRID_SEARCH(
  TABLE t,
  '{
    "query": {
      "term": {"doc.title": "OceanBase Manual A"}
    }
  }'
);

表级参数 ASYNC_INDEX_PARAMS

异步搜索索引与异步全文索引共用表级配置 ASYNC_INDEX_PARAMS。用户只需传入要修改的 key，未传入的 key 保持原值。

-- 随表建设置刷新周期
CREATE TABLE t_async_params (
  c1 INT,
  c2 JSON,
  ASYNC SEARCH INDEX idx_si (c2)
) ORGANIZATION HEAP ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10';

-- 修改已有表的异常行策略与刷新周期
ALTER TABLE t_async_params SET ASYNC_INDEX_PARAMS = 'REFRESH_ON_ERROR=SKIP, ASYNC_REFRESH_INTERVAL=30';

手动触发合并请参见 DBMS_ASYNC_INDEX 概述。异步全文索引上的同类配置说明请参见 异步全文索引。

嵌套路径与嵌套查询

JSON 对象数组在搜索索引中默认按路径扁平化，同对象内字段的关联会丢失。例如某电影有两条评论：小明打了 3 分、小红打了 9 分。若查询作者为小明且评分 ≥ 8，在非嵌套语义下，系统会把各字段拍扁为独立元素，只看到 存在小明 和 存在 9 分，因此可能误命中该文档。声明 NESTED_PATHS 后，可在混合搜索中使用嵌套查询（Nested Query），要求条件作用于同一条评论；此时 小明 与 3 分、小红 与 9 分分别关联，系统会判定小明不存在评分 ≥ 8，因而不会误命中该文档。文档示意：

{
  "title": "某电影",
  "comments": [
    {"author": "小明", "rating": 3},
    {"author": "小红", "rating": 9}
  ]
}

建索引时声明嵌套路径：

CREATE TABLE movie_rating (
  k INT,
  j1 JSON,
  SEARCH INDEX idx_nested (
    j1 WITH (
      -- 声明嵌套路径，$ 表示根路径，comments 表示第一层对象数组字段
      NESTED_PATHS = ('$.comments')
    )
  )
) ORGANIZATION = HEAP;

此时若查询 同一条评论中作者为小明且评分 ≥ 8，系统会判定小明不存在评分 ≥ 8，因而不会命中该文档：

SELECT k, j1 FROM HYBRID_SEARCH(
  TABLE movie_rating,
  '{
    "query": {
      -- 混合搜索中使用 nested 子句
      "nested": {
        "path": "j1.comments",
        "query": {
          "bool": {
            "must": [
              {"term": {"j1.comments.author": "小明"}},
              {"range": {"j1.comments.rating": {"gte": 8}}}
            ]
          }
        }
      }
    }
  }'
);

完整语法、路径匹配规则与限制请参见文末相关文档。

支持的过滤查询

标量

对各类标量列（数值、字符串、时间等），常见可加速的过滤查询包括：

• 等值：col = value
• 范围：col > / >= / < / <=、BETWEEN
• IN (list)
• IS NULL / IS NOT NULL
• 多条件组合 AND / OR：若多列在同一搜索索引定义中，或与其他索引组合，可能走 Index Merge。

建议：高选择性、范围有序的访问可继续保留 B-tree 索引；低选择率、多条件组合 OR、JSON/数组并存时，搜索索引 + Index Merge 往往更高效。

JSON

创建搜索索引后，可以直接基于 JSON 内部字段进行查询，无需全表扫描。

建议：查询尽量写明确路径；文档字段多时务必用 WITH 收窄索引范围；同路径多类型混存用 PICK；关键词搜索请用全文索引。

单级数组

对 ARRAY(element_type) 列：

建议：标签、多值属性用单级 ARRAY；按语义选用包含 / 全包含 / 求交三类函数；可与表上 B-tree 条件一起做 Index Merge。

确认是否使用索引

可以通过 EXPLAIN 查看执行计划，确认查询是否实际利用了搜索索引，例如：

EXPLAIN SELECT * FROM products WHERE info->'$.price' > 100 AND category = 'electronics';

如果执行计划中包含搜索索引名称（例如 idx_products），则表示查询实际利用了搜索索引。

场景示例

这里给出一些常见的场景示例供参考。

标量列查询

以下示例演示如何在整数和字符串列上创建搜索索引，并进行各种查询：

1. 创建示例表和搜索索引，并插入测试数据

   CREATE TABLE orders (
   order_id INT,
   user_id INT,
   amount DOUBLE,
   status VARCHAR(64),
   SEARCH INDEX idx_orders (user_id, amount, status)
   ) ORGANIZATION = HEAP;
   
   INSERT INTO orders VALUES (1, 100, 99.5, 'paid');
   INSERT INTO orders VALUES (2, 101, 200.0, 'pending');
   INSERT INTO orders VALUES (3, 100, 50.0, 'paid');
   INSERT INTO orders VALUES (4, 102, 150.0, 'shipped');
   INSERT INTO orders VALUES (5, 100, 300.0, 'refund');
   

2. 各种查询

   -- 等值查询：查找 user_id=100 的所有订单
   SELECT * FROM orders WHERE user_id = 100;
   -- 预期返回 order_id=1,3,5 的三行
   
   -- 范围查询：查找金额在 80~200 之间的订单
   SELECT * FROM orders WHERE amount > 80 AND amount < 200;
   -- 预期返回 order_id=1,4 的两行
   
   -- IN 查询：查找状态为 paid 或 shipped 的订单
   SELECT * FROM orders WHERE status IN ('paid', 'shipped');
   -- 预期返回 order_id=1,3,4 的三行
   
   -- 多条件组合
   SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
   -- 预期返回 order_id=1,3 的两行
   

3. 确认索引被使用

   EXPLAIN SELECT * FROM orders WHERE user_id = 100;
   

   预期返回的执行计划中包含搜索索引名称 idx_orders。

JSON 查询

1. 创建示例表和搜索索引，并插入测试数据

   CREATE TABLE products (
   id INT, --标量列
   info JSON, --JSON 列
   SEARCH INDEX idx_products (info)
   ) ORGANIZATION = HEAP;
   
   INSERT INTO products VALUES
   (1, '{"name": "Phone", "price": 999, "brand": "Apple", "tags": ["electronics", "mobile"]}'),
   (2, '{"name": "Book", "price": 29, "brand": "O\'Reilly", "tags": ["education"]}'),
   (3, '{"name": "Laptop", "price": 1999, "brand": "Dell", "tags": ["electronics", "computer"]}'),
   (4, '{"name": "Pen", "price": 5, "brand": "Pilot", "tags": ["stationery"]}');
   

2. 各种查询

   -- 路径比较：价格在 10~1000 之间
   SELECT * FROM products WHERE info->'$.price' BETWEEN 10 AND 1000;
   -- 预期返回 Book 和 Phone
   
   -- MEMBER OF 查询：查询 tags 中包含 "electronics" 的商品
   SELECT * FROM products WHERE '"electronics"' MEMBER OF (info->'$.tags');
   -- 预期返回 Phone 和 Laptop
   
   -- JSON_CONTAINS 查询：tags 同时包含 electronics 和 mobile
   SELECT * FROM products WHERE JSON_CONTAINS(info->'$.tags', '["electronics", "mobile"]');
   -- 预期返回 Phone
   

3. 确认索引被使用

   EXPLAIN SELECT * FROM products WHERE info->'$.price' BETWEEN 10 AND 1000;
   

   预期返回的执行计划中包含搜索索引名称 idx_products。

JSON 过滤查询

以下示例演示用户画像场景下，如何使用 WITH 子句限制索引范围。

1. 创建示例表和搜索索引，并插入测试数据

   CREATE TABLE users (
   id INT,
   profile JSON,
   SEARCH INDEX idx_profile (profile WITH (INCLUDE_PATHS = ('$.name', '$.address.*')))
   ) ORGANIZATION = HEAP;
   
   INSERT INTO users VALUES
   (1, '{"name": "张三", "age": 30, "address": {"city": "北京", "zip": 100000}, "debug": "xxx"}'),
   (2, '{"name": "李四", "age": 25, "address": {"city": "上海", "zip": 200000}, "debug": "yyy"}');
   

2. 各种查询

   -- 利用索引的查询（在 INCLUDE_PATHS 范围内）
   SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.name') = '"张三"';
   -- 预期命中索引
   
   SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.address.city') = '"北京"';
   -- 预期命中索引
   
   -- 不利用索引的查询（不在 INCLUDE_PATHS 范围内）
   SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') = 30;
   -- 预期走全表扫描（age 路径未被索引）
   

3. 确认索引被使用

   EXPLAIN SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') = 30;
   

   预期返回的执行计划中不包含搜索索引名称 idx_profile，走全表扫描。

多索引联合查询（Index Merge）

以下示例演示如何使用多索引联合查询。搜索索引的 Index Merge 能力让它可以与表上的 B-tree 索引、全文索引协同工作。

1. 创建包含多种索引的表，并插入测试数据

   CREATE TABLE products_v2 (
   id INT PRIMARY KEY,
   name VARCHAR(128),
   category INT,
   price INT,
   tags JSON,
   INDEX idx_category(category), -- B-tree 索引
   INDEX idx_price(price), -- B-tree 索引
   FULLTEXT INDEX ft_name(name), -- 全文索引
   SEARCH INDEX idx_tags(tags) -- 搜索索引
   ) ORGANIZATION HEAP;
   
   INSERT INTO products_v2 VALUES
   (1, 'OceanBase Database', 1, 0, '{"type": "database", "license": "open-source"}'),
   (2, 'MySQL Server', 1, 0, '{"type": "database", "license": "open-source"}'),
   (3, 'Premium Support', 2, 999, '{"type": "service"}');
   

2. 各种查询

   -- category 使用 BTree 索引，tags 使用 Search Index，结果取交集
   SELECT * FROM products_v2
   WHERE category = 1 AND json_extract(tags, '$.license') = '"open-source"';
   
   -- 全文搜索匹配 "database" 或 tags 中 type 为 "service"
   SELECT * FROM products_v2
   WHERE MATCH(name) AGAINST ("database") OR json_extract(tags, '$.type') = '"service"';
   
   -- 复杂组合：BTree AND (BTree OR 搜索索引)
   SELECT * FROM products_v2
   WHERE category = 1 AND (price = 0 OR json_extract(tags, '$.type') = '"service"');
   

嵌套查询

以下示例演示如何声明 NESTED_PATHS，并在混合搜索中按同一评论对象过滤。

1. 创建表与搜索索引，并插入测试数据

   CREATE TABLE movie_rating (
     k INT,
     j1 JSON,
     SEARCH INDEX idx_nested (
       j1 WITH (
         NESTED_PATHS = ('$.comments')
       )
     )
   ) ORGANIZATION = HEAP;
   
   INSERT INTO movie_rating VALUES
   (1, '{"title":"doc1","comments":[{"author":"alice","rating":4},{"author":"bob","rating":5}]}'),
   (2, '{"title":"doc2","comments":[{"author":"alice","rating":5},{"author":"bob","rating":4}]}');
   

2. 嵌套查询：查找存在 author=alice 且 rating>=5 的同一条 comment

   SELECT k, j1 FROM HYBRID_SEARCH(
     TABLE movie_rating,
     '{
       "query": {
         "nested": {
           "path": "j1.comments",
           "query": {
             "bool": {
               "must": [
                 {"term": {"j1.comments.author": "alice"}},
                 {"range": {"j1.comments.rating": {"gte": 5}}}
               ]
             }
           }
         }
       }
     }'
   );
   

   预期仅返回 k=2：文档 1 中 alice 与评分 5 不在同一条 comment 上。

相关文档

• 本功能主要用于混合搜索场景，相关场景示例请参见混合搜索
• 异步全文索引及表级刷新参数的补充说明请参见 异步全文索引
• 原理和概念说明请参见 搜索索引（Search Index）
• 嵌套查询在混合搜索中的语法请参见 HYBRID_SEARCH
• 完整的语法和参数说明请参见 CREATE INDEX
• 完整的语法和参数说明请参见 CREATE TABLE
• 完整的语法和参数说明请参见 ALTER TABLE