管理搜索索引

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

前提条件

使用限制

搜索索引仅支持以下数据类型:

  • 基础数据类型:BOOLEAN,BYTE,SHORT,INT,LONG,FLOAT,DOUBLE,STRING,CHAR,BINARY,TIMESTAMP。

  • JSON数据类型:JSON。

重要

JSON和TIMESTAMP数据类型自宽表引擎2.6.5开始支持,如需使用请升级至2.6.5及以上版本。如何查看或升级当前版本,请参见宽表引擎版本说明升级小版本

数据准备

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

  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, PRIMARY KEY (user_id));
  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 INDEX IF NOT EXISTS idx USING SEARCH ON search_table (name,age,gender,address(type=text, analyzer=ik),email,city);
    说明
    • 执行创建搜索索引语句后如果出现The Lindorm Search cluster address is null报错,请确保已开通Lindorm实例的搜索索引服务。

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

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

    • 创建搜索索引时,系统默认在后台异步构建搜索索引,历史数据越多,构建所需的时间越长。如果您希望同步构建搜索索引,请在创建搜索索引语句后添加SYNC关键字。

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

    SHOW INDEX FROM search_table;

    查询结果如下:

    +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
    | TABLE_SCHEMA  |  DATA_TABLE  | INDEX_NAME | INDEX_STATE | INDEX_TYPE | INDEX_COVERED |            INDEX_COLUMN            | INDEX_TTL | INDEX_DESCRIPTION |
    +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
    | searchindex_db| search_table | idx        | BUILDING    | SEARCH     | NA            | address,city,age,gender,name,email | 0         |                   |
    +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
    说明

    搜索索引尚未构建完成时,索引状态为BUILDING。搜索索引构建完成后,索引状态为ACTIVE。返回结果的详细说明,请参见SHOW INDEX

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

    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 | 杭州  |
    +---------+--------+-----+--------+--------------+------------------+------+
  4. 如果您不再需要搜索索引,可以执行以下语句删除。

    1. 在删除索引前,您需要执行以下语句先禁用索引。

      ALTER INDEX IF EXISTS idx ON search_table DISABLED;
      说明

      索引禁用后状态为DISABLED,如果想要再次启用,需执行ALTER INDEX IF EXISTS idx ON search_table REBUILD;语句重新构建。

    2. 删除索引。

      DROP INDEX IF EXISTS idx ON search_table;

      详细说明请参见DROP INDEX

使用动态列

  1. 开启动态列功能。

    ALTER TABLE search_table SET 'DYNAMIC_COLUMNS' = 'true';
    说明

    更多说明,请参见开启动态列

  2. 添加动态列。动态列需要显式添加至搜索索引中。

    • 新建搜索索引时指定动态列。

      CREATE INDEX idx USING SEARCH ON search_table(user_id,name,age,gender,password);
    • 已有搜索索引,通过ALTER INDEX命令添加动态列。

      ALTER INDEX idx ON search_table ADD COLUMNS(password);

      其中password未在建表时定义,为新添加的动态列。

  3. 写入动态列。

    UPSERT INTO search_table (user_id,name,age,gender,address,email,city,password) VALUES (30, '王女士', 38, 'F', '深圳市南山区', 'a***@example.net', '深圳', 'ef0011');

    其中动态列password只能写入HexString格式数据,详细使用说明,请参见写入动态列

  4. 查询动态列。

    SELECT * FROM search_table WHERE password='ef0011' AND gender='F' LIMIT 1;

    其中password列只能以HexString格式查询,详细使用说明,请参见查询动态列

    返回结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+----------+
    | user_id |  name  | age | gender |   address    |      email       | city | password |
    +---------+--------+-----+--------+--------------+------------------+------+----------+
    | 30      | 王女士  | 38  | F      | 深圳市南山区  | a***@example.net | 深圳 | 0xef0011 |
    +---------+--------+-----+--------+--------------+------------------+------+----------+