搜索索引

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

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

功能说明

• 典型场景：与混合搜索共同使用，或与分析型宽表上多列搜索索引 + 多索引联合查询（Index Merge）共同使用，提升性能。
  • AI 混合搜索场景：全文分词与关键词检索由全文索引负责，搜索索引侧重复杂类型内部路径的结构化/半结构化条件（也可用于简单标量），二者适用列类型不同，可同表并存并与向量索引共同使用。
  • AP 分析型场景：在宽表、多列分别建有搜索索引的前提下，优化器可借助 Index Merge 能力自动识别可用的索引组合并选择较优扫描路径，提升性能。
• DDL 操作：搜索索引的 DDL 操作与普通索引一致，支持创建、删除、重命名、可见性等变更。

功能和语法限制

• 租户：该功能仅适用于 MySQL 模式租户。
• 表类型：目标表须为 ORGANIZATION = HEAP 堆表。
• 列级配置：列级 WITH (...) 仅对 JSON 列生效。
• JSON 路径长度：编码后的 JSON 路径最大为 2KB。如果 JSON 文档的键名非常长或嵌套层级极深，DML 操作时可能因路径超限而失败（ERROR 1071）。建议 JSON 文档设计时避免超长键名。
• 数组类型：当前仅支持单级数组，不支持嵌套数组。
• 分区表：当前分区表仅支持局部搜索索引（LOCAL）。
• 索引列：不支持对索引列的 LIKE 模式匹配加速。
• 生成列：不支持在生成列（GENERATED）上创建搜索索引；不支持 STORING 子句。

使用方法

支持的数据类型

搜索索引主要用于标量列和 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]...)]

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]...)]

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]...)]

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

支持的过滤查询

标量

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

• 等值：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"');
   

相关文档

• 本功能主要用于混合搜索场景，相关场景示例请参见索引混合搜索
• 原理和概念说明请参见 搜索索引（Search Index）
• 完整的语法和参数说明请参见 CREATE INDEX
• 完整的语法和参数说明请参见 CREATE TABLE
• 完整的语法和参数说明请参见 ALTER TABLE