全文检索如何创建索引 - 云原生数据仓库AnalyticDB

本文介绍了使用云原生数据仓库 AnalyticDB MySQL 版进行全文检索创建全文索引的方法。

前提条件

集群内核版本需为3.1.4.9及以上版本。

说明

推荐使用内核版本为3.1.4.17及以上的AnalyticDB for MySQL集群。
如何查看集群的内核版本，请参见如何查看实例版本信息。

使用限制

全文索引仅支持对一列进行设置，如需对多个列创建全文索引，可通过在多个列上单独创建全文索引实现。
全文索引只支持VARCHAR类型的列。

可见策略

AnalyticDB for MySQL的全文索引对新写入的数据实时可见。
历史数据需要使用BUILD TABLE `表名` force=true；强制建立索引。

创建表时添加全文索引

语法结构

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  [FULLTEXT [INDEX|KEY] [index_name] (column_name)] [index_option]} [, ... ]  )
   table_attribute
   [partition_options]
   [AS] query_expression
   COMMENT 'string'

参数说明

参数	说明
table_name	表名。表名以字母或下划线（_）开头，可包含字母、数字以及下划线（_），长度为1~127个字符。支持`db_name.table_name`格式，区分不同数据库下相同名字的表。
column_name	列名。列名以字母或下划线（_）开头，可包含字母、数字以及下划线（_），长度为1~127个字符。
column_type	要添加的列的数据类型。至少有一列的数据类型为VARCHAR。 AnalyticDB for MySQL支持的数据类型，请参见基础数据类型。
column_attributes	列属性。AnalyticDB for MySQL支持的列属性，详情请参见CREATE TABLE。
column_constraints	列约束条件。AnalyticDB for MySQL支持的列约束，详情请参见CREATE TABLE。
FULLTEXT	定义全文索引。
INDEX\|KEY	全文索引的关键字标识。INDEX和KEY无差异。
index_name	全文索引名称。
column_name	全文索引的列。必须是VARCHAR类型的列。
index_option	可指定全文索引的分词器和自定义词典。 `WITH ANALYZER analyzer_name`：指定全文索引的分词器。AnalyticDB for MySQL支持的分词器类型，请参见全文索引的分词器。 `WITH DICT tbl_dict_name`：指定全文索引的自定义词典。AnalyticDB for MySQL自定义词典的详细信息，请参见全文索引的自定义词典。

示例

创建表tbl_fulltext_name时，为列content设定了全文索引。

CREATE TABLE `tbl_fulltext_name` (
  `id` int,
  `content` varchar,
  `keyword` varchar,
  FULLTEXT INDEX fidx_c(`content`),
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id);

您可以通过以下语句查看表的索引。

SHOW index from tbl_fulltext_name;

返回结果如下：

+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_fulltext_name |          0 | PRIMARY       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_fulltext_name |          1 | id_0_idx      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_fulltext_name |          1 | keyword_2_idx |            1 | keyword     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_fulltext_name |          1 | fidx_c        |            1 | content     | A         |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

为已存在的表添加全文索引

语法结构

ALTER TABLE `table_name` ADD FULLTEXT [INDEX|KEY] index_name (column_name) [index_option]

参数说明

详情的参数说明，请参见创建表时添加全文索引。

示例

创建表tbl_fulltext_name。

CREATE TABLE `tbl_fulltext_name` (
  `id` int,
  `content` varchar,
  `keyword` varchar,
  FULLTEXT INDEX fidx_c(`content`), 
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id) INDEX_ALL = 'N';

对列keyword添加全文索引，设定全文索引的名称为fidx_k，同时指定Standard分词器。
说明
不指定分词器时，将采用默认分词器。详情请参见全文索引的分词器。
```
ALTER TABLE `tbl_fulltext_name` ADD FULLTEXT INDEX fidx_k(`keyword`) WITH ANALYZER standard;
```
如需对历史数据建立全文索引，请执行下列语句。Build任务结束后索引才能生效，构建全文索引花费的时间和数据量有关系。
```
BUILD TABLE `tbl_fulltext_name` force=true;
```

删除全文索引

语法结构

ALTER TABLE table_name DROP FULLTEXT INDEX index_name;

示例

删除表tbl_fulltext_name中全文索引fidx_k。

ALTER TABLE `tbl_fulltext_name` DROP FULLTEXT INDEX fidx_k;