管理搜索索引

本文介绍管理搜索索引的操作方法。

前提条件

数据准备

管理搜索索引前需要先创建目标表并写入测试数据。

  1. 执行以下语句创建目标表,表名为search_table

    CREATE DATABASE searchindex_db;
    USE searchindex_db;
    CREATE TABLE IF NOT EXISTS search_table (user_id bigint, name varchar, age smallint, gender varchar, address varchar, email varchar, city varchar, constraint primary key (user_id asc));
  2. 执行以下语句在目标表中写入四条数据。

    UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (1, '张先生', 18, 'M', '北京市朝阳区', 'a***@example.net', '北京');
    UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (6, '李先生', 32, 'M', '杭州市余杭区', 'a***@example.net', '杭州');
    UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (20, '王先生', 28, 'M', '杭州市滨江区', 'a***@example.net', '杭州');
    UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (28, '陈女士', 36, 'F', '深圳市南山区', 'a***@example.net', '深圳');
  3. 执行以下语句查询目标表中的数据。

    SELECT * FROM search_table LIMIT 10;

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 1       | 张先生  | 18  | M      | 北京市朝阳区  | a***@example.net | 北京  |
    | 6       | 李先生  | 32  | M      | 杭州市余杭区  | a***@example.net | 杭州  |
    | 20      | 王先生  | 28  | M      | 杭州市滨江区  | a***@example.net | 杭州  |
    | 28      | 陈女士  | 36  | F      | 深圳市南山区  | a***@example.net | 深圳  |
    +---------+--------+-----+--------+--------------+------------------+------+

管理搜索索引

以上述测试数据为例,创建的搜索索引需要满足以下需求:

  • 对任意一列都可以进行快速检索。

  • address字段进行分词查询。

  • email字段进行模糊查询。

  1. 创建搜索索引,关于创建搜索索引的语法详情请参见CREATE SEARCH INDEX

    CREATE SEARCH INDEX IF NOT EXISTS idx on search_table (name,age,gender,address(type=text, analyzer=ik),email,city);
    说明
    • 执行创建搜索索引语句后如果出现The Lindorm Search cluster address is null报错,请确保已开通Lindorm实例的搜索索引服务。

    • 创建搜索索引语句中,address字段按照ik分词器进行分词。

    • 每张表只能创建一个搜索索引。

    • 创建搜索索引时系统默认会执行同步构建搜索索引,历史数据越多,执行过程所需的时间越长。如果您不希望执行同步构建搜索索引,可以在创建搜索索引语句后加ASYNC,创建完成后修改搜索索引属性为USABLE,修改方法请参见修改搜索索引属性

  2. 执行以下语句查询指定表的搜索索引信息。

    SHOW SEARCH INDEX FROM search_table;

    查询结果如下:

    +--------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+
    | TABLE_SCHEMA |  DATA_TABLE  | INDEX_NAME | INDEX_STATE | INDEX_TYPE | INDEX_COVERED |            INDEX_COLUMN            | INDEX_TTL |
    +--------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+
    | test         | search_table | idx        | BUILDING    | SEARCH     | NA            | address,city,age,gender,name,email | 0         |
    +--------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+
  3. (可选)构建搜索索引。如果采用ASYNC异步构建搜索索引,默认不会对历史数据构建搜索索引。如果需要对历史数据进行检索,请执行以下命令构建搜索索引,当搜索索引构建结束后索引状态会设置为ACTIVE,可以通过SHOW SEARCH INDEX FROM search_table;命令查看。

    ALTER SEARCH INDEX IF EXISTS idx ON search_table REBUILD;
    说明

    关于构建搜索索引的语法详情请参见ALTER SEARCH INDEX

  4. 查询数据,更多查询场景的方法请参见通过搜索索引查询宽表数据

    SELECT * FROM search_table WHERE name='王先生' AND age > 18 AND city='杭州';

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 20      | 王先生  | 28  | M      | 杭州市滨江区  | a***@example.net | 杭州  |
    +---------+--------+-----+--------+--------------+------------------+------+
  5. 如果您不需要搜索索引,可以执行以下语句删除搜索索引,关于删除搜索索引的语法详情请参见DROP SEARCH INDEX

    ALTER SEARCH INDEX IF EXISTS idx ON search_table DISABLED;
    DROP SEARCH INDEX IF EXISTS idx ON search_table;

使用动态列

  1. 开启动态列功能,具体请参见开启动态列

  2. 创建search index时,指定动态列c2和c3。

CREATE SEARCH INDEX IF NOT EXISTS idx ON search_table(p1,c1,c2,c3) with(indexState=ACTIVE);
  1. 指定动态列,创建search index后,可执行以下命令,查看动态列是否添加成功。

SHOW SEARCH INDEX FROM search_table;
show search index
  1. 已创建search index,如需要增加动态列,可以通过以下命令添加。

ALTER SEARCH INDEX idx ON search_table  ADD COLUMNS(c4);

结果如下:

add column
  1. 写入动态列和查询动态列,具体请参见管理动态列

阿里云首页 云原生多模数据库Lindorm 相关技术圈