创建索引

本文介绍了如何创建索引。

背景信息

OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或非唯一索引，还可以是空间索引或函数索引。如果是分区表的唯一索引，则局部的唯一索引必须包含表分区的分区键。关于索引的更多介绍，请参见 索引简介 章节。

本节主要介绍如何在非分区表上创建索引，分区表上索引的创建请参见 在分区表上建立索引 章节。

注意事项

创建索引时需要注意，在 OceanBase 数据库中，索引名称在数据库（DataBase）范围内不能重复。

创建普通索引

可以使用 CREATE TABLE 语句、CREATE INDEX 语句或 ALTER TABLE 语句来创建普通索引。

使用 CREATE TABLE 语句创建普通索引

可以通过 CREATE TABLE 语句在创建表时即为表创建索引。

语法如下：

CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...] [UNIQUE] INDEX|KEY [index_name](column_name));

相关参数说明如下：

• table_name：指定待创建的表的表名。

• column_name：指定表的列。

• column_definition：定义表中各列对应的数据类型。

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，指定待创建的索引的索引名。如果未指定，默认索引名与指定的列名相同。

创建表 tbl1，同时创建索引 tbl1_idx1 的示例如下：

CREATE TABLE tbl1(id INT,name VARCHAR(18),date DATE,PRIMARY KEY (id),INDEX tbl1_idx1 (date));

使用 CREATE INDEX 语句创建普通索引

可以通过 CREATE INDEX 语句为已有的表创建索引。

语法如下：

CREATE [UNIQUE] INDEX index_name ON table_name (column_name);

相关参数说明如下：

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• index_name：指定待添加的索引的索引名。

• table_name ：指定待创建索引的表的表名。

• column_name ：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。

创建 tbl2 表后，再创建索引 tbl2_idx1 的示例如下：

1. 创建表 tbl2。

   CREATE TABLE tbl2(id INT,name VARCHAR(20));
   

2. 在表 tbl2 上创建索引 tbl2_idx1。

   CREATE INDEX tbl2_idx1 ON tbl2(id);
   

使用 ALTER TABLE 语句创建普通索引

可以通过 ALTER TABLE 语句为已存在的表创建索引，且该语句支持一次增加多个索引。

语法如下：

ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY [index_name](column_name);

相关参数说明如下：

• table_name ：指定待创建索引的表的表名。

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，指定待创建的索引的索引名。如果未指定，默认索引名与指定的列名相同。

• column_name ：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。

创建表 tbl3 后，在创建索引 tbl3_idx1 的示例如下：

1. 创建表 tbl3。

   CREATE TABLE tbl3(id INT,name VARCHAR(20));
   

2. 为表 tbl3 创建索引 tbl3_idx1。

   ALTER TABLE tbl3 ADD INDEX tbl3_idx1(id);
   

创建空间索引

OceanBase 数据库的 MySQL 模式支持创建空间索引。空间索引是对存储在介质上的数据位置信息的描述，可以用来提高系统对数据获取的效率。

创建空间索引的语法与创建普通索引的语法基本一致，也是使用 CREATE TABLE 语句、CREATE INDEX 语句或 ALTER TABLE 语句来创建索引。不同的是，空间索引需要使用 SPATIAL 关键字，并且空间索引中的列必须为 NOT NULL。

空间索引使用限制

在创建空间索引前，需要了解以下使用限制：

• 空间索引仅支持局部索引，不支持全局索引。

• 创建空间索引的列必须定义为 SRID 属性，否则，在该列上添加的空间索引在后续查询时会无法生效。这是因为，创建空间索引后，在使用空间索引进行比较时，需要使用列定义中的 SRID 所对应的坐标系。空间索引存储了几何对象的 MBR（Minimum Bounding Rectangle）构建，而 MBR 的比较方式也依赖于 SRID。

  关于 SRID 相关的介绍，请参见 空间参考系统（SRS）。

• 只能对空间数据类型的数据列创建空间索引。OceanBase 数据库支持的空间数据类型请参见 空间数据类型概述。

• 创建空间索引的列的列属性必须为 NOT NULL。

  如果不为 NOT NULL，您也可以通过 ALTER TABLE 语句先将该列的列属性修改为 NOT NULL 后再添加空间索引。修改列属性的具体操作可参见 定义列的约束类型。

使用 CREATE TABLE 语句创建空间索引

语法如下：

CREATE TABLE table_name(column_g_name GEOMETRY NOT NULL SRID 4326,[column_name column_definition,...] SPATIAL INDEX|KEY [index_name](column_g_name));

相关参数说明如下：

• table_name：指定待创建的表的表名。

• column_g_name、column_name：定义表的列名。

• SRID 4326：显示指定空间列的 SRID。

• column_definition：定义表中各列对应的数据类型。待添加空间索引的列的列类型必须为空间数据类型。OceanBase 数据库支持的空间数据类型请参见 空间数据类型概述。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：指定待添加的索引的索引名。如果未指定，默认索引名与指定的列名相同。

创建 tbl1_g 表，同时添加空间索引 tbl1_g_idx1 的示例如下：

CREATE TABLE tbl1_g (id INT,name VARCHAR(18),g GEOMETRY NOT NULL SRID 4326,SPATIAL INDEX tbl1_g_idx1(g));

创建后，可以通过 SHOW INDEX 语句查看索引信息。

SHOW INDEX FROM tbl1_g;

查询结果如下：

+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| tbl1_g |          1 | tbl1_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
1 row in set

使用 CREATE INDEX 语句创建空间索引

语法如下：

CREATE SPATIAL INDEX index_name ON table_name(column_g_name);

相关参数说明如下：

• table_name：指定待创建索引的表的表名。

• index_name：指定待添加的索引的索引名。

• column_g_name：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。待添加空间索引的列的列类型必须为空间数据类型。OceanBase 数据库支持的空间数据类型请参见 空间数据类型概述。

创建 tbl2_g 表后，再创建空间索引 tbl2_g_idx1 的示例如下：

1. 创建表 tbl2_g。

   CREATE TABLE tbl2_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
   

2. 为表创建空间索引 tbl2_g_idx1。

   CREATE SPATIAL INDEX tbl2_g_idx1 ON tbl2_g(g);
   

3. 创建成功后，查看索引信息。

   SHOW INDEX FROM tbl2_g;
   

   查询结果如下：

   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | tbl2_g |          1 | tbl2_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set
   

使用 ALTER TABLE 语句创建空间索引

ALTER TABLE table_name ADD SPATIAL INDEX|KEY [index_name](column_g_name);

相关参数说明如下：

• table_name：指定待创建索引的表的表名。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，指定待添加的索引的索引名。如果未指定，默认索引名与指定的列名相同。

• column_g_name：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。待添加空间索引的列的列类型必须为空间数据类型。OceanBase 数据库支持的空间数据类型请参见 空间数据类型概述。

创建 tbl3_g 表后，再创建空间索引 tbl3_g_idx1 的示例如下：

1. 创建表 tbl3_g。

   CREATE TABLE tbl3_g(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 4326);
   

2. 在表上创建空间索引。

   ALTER TABLE tbl3_g ADD SPATIAL INDEX tbl3_g_idx1(g);
   

3. 查看索引信息。

   SHOW INDEX FROM tbl3_g;
   

   查询结果如下：

   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | tbl3_g |          1 | tbl3_g_idx1 |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     |
   +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set
   

创建函数索引

基于表中一列或多列的值进行计算后的结果建立的索引称为函数索引。函数索引是一种优化技术，使用函数索引可以在查询时快速定位匹配的函数值，从而避免重复计算，提高查询效率。

假设您有一张表 sale，表中有一个数据类型为 date 的列 date_col：

CREATE TABLE sale(date_col date, sales_col int, name_col varchar(10));

如果您需要获取 3 月份的数据，则可以使用 Month() 函数来查询数据：

SELECT * FROM sale WHERE month(date_col) = 3;

该场景下，数据库需要为表中的每行计算 month(date_col)，并过滤掉不符合 month(date_col) = 3 的行。如果您需要频繁地使用月份信息来过滤数据，则每次都需要重新计算 month(date_col)，就会造成大量的开销。因此，您可以在 date_col 上创建一个函数索引，将 month(date_col) 存储到索引表中，来加速查询。

CREATE INDEX idx1 ON sale((month(date_col)));

您可以使用 CREATE TABLE 语句、CREATE INDEX 语句或 ALTER TABLE 语句来创建函数索引。函数索引也可以是唯一索引或空间索引。

函数索引使用限制

在 OceanBase 数据库的 MySQL 模式中，对函数索引的表达式进行了限制，禁止部分系统函数的表达式作为函数索引，具体的函数列表请参见 函数索引支持的系统函数列表 和 函数索引不支持的系统函数列表。

使用 CREATE TABLE 语句创建函数索引

可以在创建表时即为表创建函数索引。语法如下：

CREATE TABLE table_name({column_name column_definition,[column_name column_definition,...]} [UNIQUE| SPATIAL] {INDEX|KEY} [index_name](expr));

相关参数说明如下：

• table_name：指定待创建索引的表的表名。

• column_definition：定义表中各列的数据类型。

• [UNIQUE | SPATIAL]：可选，UNIQUE 表示唯一索引；SPATIAL 表示空间索引。创建唯一索引或空间索引时需要添加对应的关键字。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，待创建的函数索引的名称。如果不指定，则系统会自动生成一个名称。格式为 functional_index_xx，xx 为索引编号。

• expr：合法的函数索引表达式，且允许是布尔表达式，例如 c1=c1。

创建函数索引 tbl1_func_idx1 的示例如下：

CREATE TABLE tbl1_func (c1 int, c2 int, index tbl1_func_idx1 ((c1+1)), UNIQUE KEY ((c1+c2)));

创建成功后，可以通过 SHOW CREATE TABLE 语句查看建表语句，语句如下：

SHOW CREATE TABLE tbl1_func;

查询结果如下：

+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1_func | CREATE TABLE `tbl1_func` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL,
  KEY `tbl1_func_idx1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

使用 CREATE INDEX 语句创建函数索引

表创建成功后，可以为已有表创建函数索引。语法如下：

CREATE [UNIQUE | SPATIAL] INDEX index_name ON table_name (expr);

相关参数说明如下：

• [UNIQUE | SPATIAL]：可选，UNIQUE 表示唯一索引；SPATIAL 表示空间索引。创建唯一索引或空间索引时需要添加对应的关键字。

• index_name：待创建的函数索引的名称。

• table_name：待创建索引的表。

• expr：合法的函数索引表达式，且允许是布尔表达式，例如 c1=c1。

在表上创建一个索引定义为 c1+c2 < 1 的函数索引 tbl2_func_idx1，示例如下：

1. 创建表 tbl2_func。

   CREATE TABLE tbl2_func(c1 int, c2 int);
   

2. 创建函数索引 tbl2_func_idx1。

   CREATE INDEX tbl2_func_idx1 on tbl2_func ((c1+c2 < 1));
   

3. 创建成功后，可以查看创建的函数索引。

   SHOW INDEX FROM tbl2_func;
   

   结果如下：

   +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
   | Table     | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression          |
   +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
   | tbl2_func |          1 | tbl2_func_idx1 |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | ((`c1` + `c2`) < 1) |
   +-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+---------------------+
   1 row in set
   

使用 ALTER TABLE 语句创建函数索引

语法如下：

ALTER TABLE table_name ADD [UNIQUE | SPATIAL] {INDEX | KEY} [index_name](expr);

相关参数说明如下：

• table_name：待创建索引的表。

• [UNIQUE | SPATIAL]：可选，UNIQUE 表示唯一索引；SPATIAL 表示空间索引。创建唯一索引或空间索引时需要添加对应的关键字。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，待创建的函数索引的名称。如果不指定，则系统会自动生成一个名称，且名称格式为 functional_index 前缀加编号。

• expr：合法的函数索引表达式，且允许是布尔表达式，例如 c1=c1。

1. 创建表 tbl3_func。

   CREATE TABLE tbl3_func(c1 int, c2 int);
   

2. 为表创建 2 个函数索引。

   ALTER TABLE tbl3_func ADD INDEX ((c1+1));
   

   ALTER TABLE tbl3_func ADD INDEX ((concat(c1,'a')));
   

3. 查看索引信息。

   SHOW INDEX FROM tbl3_func;
   

   查询结果如下：

   +-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------------+
   | Table     | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression       |
   +-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------------+
   | tbl3_func |          1 | functional_index   |            1 | SYS_NC18$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | (`c1` + 1)       |
   | tbl3_func |          1 | functional_index_2 |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | concat(`c1`,'a') |
   +-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------------+
   2 rows in set
   

创建列存索引

可以使用 CREATE TABLE 语句、CREATE INDEX 语句或 ALTER TABLE 语句来创建列存索引。

使用 CREATE TABLE 语句创建列存索引

可以通过 CREATE TABLE 语句在创建表时即为表创建列存索引。

语法如下：

CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...] [UNIQUE] INDEX|KEY [index_name](column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column));

相关参数说明如下：

• table_name：指定待创建的表的表名。

• column_name：指定表的列。

• column_definition：定义表中各列对应的数据类型。

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，指定待创建的索引的索引名。如果未指定，默认索引名与指定的列名相同。

• STORING(column_name, ...)：可选，指定索引表上的冗余列。即使 column_name 列不是索引字段，也会按照列存格式进行冗余存储。

• WITH COLUMN GROUP([all columns, ]each column)：指定索引的列存属性。

  • WITH COLUMN GROUP(all columns, each column)：指定创建行存列存冗余的索引。
  • WITH COLUMN GROUP(all columns)：指定创建行存索引。
  • WITH COLUMN GROUP(each column)：指定创建列存索引。

示例如下：

• 创建表 tbl4，同时创建行存列存冗余索引 idx1_tbl4_cg。

  CREATE TABLE tbl4(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), INDEX idx1_tbl4_cg (date) WITH COLUMN GROUP(all columns, each column));
  

• 创建表 tbl5，同时创建列存索引 idx1_tbl5_cg，并且在索引表上存储 id 列的冗余数据。

  CREATE TABLE tbl5(id INT, name VARCHAR(18), date DATE, INDEX idx1_tbl5_cg (date) STORING(id) WITH COLUMN GROUP(each column));
  

使用 CREATE INDEX 语句创建列存索引

可以通过 CREATE INDEX 语句为已有的表创建列存索引。

语法如下：

CREATE [UNIQUE] INDEX index_name ON table_name (column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);

相关参数说明如下：

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• index_name：指定待添加的索引的索引名。

• table_name ：指定待创建索引的表的表名。

• column_name ：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。

• STORING(column_name, ...)：可选，指定索引表上的冗余列。即使 column_name 列不是索引字段，也会按照列存格式进行冗余存储。

• WITH COLUMN GROUP([all columns, ]each column)：指定索引的列存属性。

  • WITH COLUMN GROUP(all columns, each column)：指定创建行存列存冗余的索引。
  • WITH COLUMN GROUP(all columns)：指定创建行存索引。
  • WITH COLUMN GROUP(each column)：指定创建列存索引。

示例如下：

创建 tbl6 表后，再创建列存索引 idx1_tbl6_cg。

1. 创建表 tbl6。

   CREATE TABLE tbl6(id INT, name VARCHAR(20), age INT);
   

2. 在表 tbl6 上创建列存索引 idx1_tbl6_cg，并且在索引表上存储 age 列的冗余数据。

   CREATE INDEX idx1_tbl6_cg ON tbl6(id) STORING(age) WITH COLUMN GROUP(each column);
   

使用 ALTER TABLE 语句创建列存索引

可以通过 ALTER TABLE 语句为已存在的表创建列存索引。

语法如下：

ALTER TABLE table_name ADD [UNIQUE] INDEX|KEY [index_name](column_name) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);

相关参数说明如下：

• table_name ：指定待创建索引的表的表名。

• UNIQUE：可选，表示唯一索引。创建唯一索引时需要添加该关键字。

• INDEX|KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，指定待创建的索引的索引名。如果未指定，默认索引名与指定的列名相同。

• column_name ：指定对哪些列进行索引，指定多列时，各列之间用英文逗号分隔。

• STORING(column_name, ...)：可选，指定索引表上的冗余列。即使 column_name 列不是索引字段，也会按照列存格式进行冗余存储。

• WITH COLUMN GROUP([all columns, ]each column)：指定索引的列存属性。

  • WITH COLUMN GROUP(all columns, each column)：指定创建行存列存冗余的索引。
  • WITH COLUMN GROUP(all columns)：指定创建行存索引。
  • WITH COLUMN GROUP(each column)：指定创建列存索引。

示例如下：

创建表 tbl7 后，在创建列存索引 idx1_tbl6_cg。

1. 创建表 tbl7。

   CREATE TABLE tbl7(id INT, name VARCHAR(20), age INT);
   

2. 为表 tbl7 创建列存索引 idx1_tbl7_cg，并且在索引表上存储 age 列的冗余数据。

   ALTER TABLE tbl7 ADD INDEX idx1_tbl7_cg(id) STORING(age) WITH COLUMN GROUP(each column);
   

创建多值索引

在 OceanBase 数据库的当前版本中，只能在创建表时（CREATE TABLE）来预先创建多值索引。目前不支持在表创建之后添加多值索引，但可以通过 ALTER TABLE 语句来删除已存在的索引。

多值索引的使用限制

• 每个多值索引只允许有一个多值键部分。然而，CAST(... AS ... ARRAY) 表达式可以引用 JSON 文档中的多个数组，如下所示：

  CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)
  

  在这种情况下，所有匹配 JSON 表达式的值都作为单个平面数组存储在索引中。

• 多值索引不能是覆盖索引。

• 在多值键部分中允许的唯一类型的表达式是 JSON 表达式。表达式不必引用插入到索引列中的 JSON 文档中的现有元素，但必须在语法上有效。

• 由于同一个聚簇索引记录的索引记录在多值索引中分散，多值索引不支持范围扫描或仅索引扫描。

• 多值索引不允许在外键规范中使用。

• 不能为多值索引定义索引前缀。

• 不能在 Binary 类型的数据上定义多值索引。

• 不支持多值索引的在线创建，这意味着操作使用 ALGORITHM = COPY。

使用 CREATE TABLE 语句创建多值索引

使用 CREATE TABLE 语句创建多值索引简化语法如下：

创建多值索引：

CREATE TABLE table_name(column_name column_definition, [column_name column_definition, ...],
    json_column_name JSON, 
    [UNIQUE] INDEX idx_json_name((CAST(json_column_name->'$.json_field_name' AS UNSIGNED ARRAY)))
);

创建复合多值索引：

CREATE TABLE table_name(column_name column_definition, [column_name column_definition, ...],
    json_column_name JSON,
    INDEX idx_json_name(column_name, [column_name, ...] (CAST(json_column_name->'$.json_field_name' AS CHAR(n) ARRAY)))
);                   

相关参数说明如下：

• table_name：指定待创建的表的表名。

• column_name：指定表其他（除 JSON 外）数据类型的列名称。

• column_definition：定义表中各列对应的数据类型。

• json_column_name：指定表 JSON 数据类型的列名称。

• UNIQUE：可选，表示创建唯一多值索引。唯一多值索引，索引 Key 不可以重复。

  注意

  多值索引可以定义为唯一索引。如果将其定义为唯一索引，尝试插入已存在于多值索引中的值会返回重复键错误。如果已经存在重复值，尝试添加唯一的多值索引会失败。

• idx_json_name ：指定待创建的多值索引的索引名。

• json_field_name：Json 列中需要创建索引的字段名称。

更多创建表语法信息，参见 CREATE TABLE。

创建多值示例如下：

1. 创建表 json_tbl1，同时创建多值索引 idx1_json_tbl1。

   CREATE TABLE json_tbl1(col1 INT, col2 VARCHAR(18), col3 JSON,
      INDEX idx1_json_tbl1((CAST(col3->'$.ipcode' AS UNSIGNED ARRAY)))
   );
   

2. 使用以下语句向表 json_tbl1 中插入测试数据。

   INSERT INTO json_tbl1 VALUES(1, 'a1', '{"user":"Jk001","user_id":37,"ipcode":[94582,94536]}'),
      (2, 'a2', '{"user":"Jk002","user_id":22,"ipcode":[94568,94507,94582]}'),
      (3, 'a3', '{"user":"Jk003","user_id":31,"ipcode":[94477,94507]}'),
      (4, 'a4', '{"user":"Jk004","user_id":72,"ipcode":[94536]}'),
      (5, 'a5', '{"user":"Jk005","user_id":56,"ipcode":[94507,94582]}');
   

创建多值示例如下：

创建表 json_tbl2，同时创建复合多值索引 idx1_json_tbl2。

CREATE TABLE json_tbl2(col1 INT, col2 VARCHAR(18), col3 JSON,
      INDEX idx1_json_tbl2(col1, (CAST(col3->'$.ipcode' AS UNSIGNED ARRAY)))
);

查询中使用多值索引

优化器在 WHERE 子句中指定了以下函数时，会使用多值索引来查询。

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS

示例如下：

• 使用 MEMBER OF() 查询示例。

  SELECT *
     FROM json_tbl1
     WHERE 94507 MEMBER OF(col3->'$.ipcode');
  

  返回结果如下：

  +------+------+-------------------------------------------------------------------+
  | col1 | col2 | col3                                                              |
  +------+------+-------------------------------------------------------------------+
  |    2 | a2   | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} |
  |    3 | a3   | {"user": "Jk003", "ipcode": [94477, 94507], "user_id": 31}        |
  |    5 | a5   | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56}        |
  +------+------+-------------------------------------------------------------------+
  3 rows in set
  

• 对表 json_tbl1 执行查询，筛选出 col3 列中的 JSON 数据中字段 ipcode 中包含值 94507 的行。使用 EXPLAIN 命令查看查询的执行计划。

  EXPLAIN SELECT *
     FROM json_tbl1
     WHERE 94507 MEMBER OF(col3->'$.ipcode');
  

  返回结果如下：

  +-------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Query Plan                                                                                                                                            |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------+
  | =====================================================================                                                                                 |
  | |ID|OPERATOR        |NAME                     |EST.ROWS|EST.TIME(us)|                                                                                 |
  | ---------------------------------------------------------------------                                                                                 |
  | |0 |TABLE RANGE SCAN|json_tbl1(idx1_json_tbl1)|3       |13          |                                                                                 |
  | =====================================================================                                                                                 |
  | Outputs & filters:                                                                                                                                    |
  | -------------------------------------                                                                                                                 |
  |   0 - output([json_tbl1.col1], [json_tbl1.col2], [json_tbl1.col3]), filter(nil)                                                                       |
  |       access([json_tbl1.__pk_increment], [json_tbl1.col3], [json_tbl1.col1], [json_tbl1.col2]), partitions(p0)                                        |
  |       is_index_back=true, is_global_index=false,                                                                                                      |
  |       range_key([json_tbl1.SYS_NC_mvi_19], [json_tbl1.__pk_increment], [json_tbl1.__doc_id_1715756608129539]), range(94507,MIN,MIN ; 94507,MAX,MAX),  |
  |       range_cond([JSON_MEMBER_OF(94507, json_tbl1.SYS_NC_mvi_19)])                                                                                    |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------+
  12 rows in set
  

• 使用 JSON_CONTAINS() 查询示例。

  SELECT *
     FROM json_tbl1
     WHERE JSON_CONTAINS(col3->'$.ipcode', CAST('[94507,94582]' AS JSON));
  

  返回结果如下：

  +------+------+-------------------------------------------------------------------+
  | col1 | col2 | col3                                                              |
  +------+------+-------------------------------------------------------------------+
  |    2 | a2   | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} |
  |    5 | a5   | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56}        |
  +------+------+-------------------------------------------------------------------+
  2 rows in set
  

• 使用 JSON_OVERLAPS() 查询示例。

  SELECT *
     FROM json_tbl1
     WHERE JSON_OVERLAPS(col3->'$.ipcode', CAST('[94507,94582]' AS JSON));
  

  返回结果如下：

  +------+------+-------------------------------------------------------------------+
  | col1 | col2 | col3                                                              |
  +------+------+-------------------------------------------------------------------+
  |    1 | a1   | {"user": "Jk001", "ipcode": [94582, 94536], "user_id": 37}        |
  |    2 | a2   | {"user": "Jk002", "ipcode": [94568, 94507, 94582], "user_id": 22} |
  |    3 | a3   | {"user": "Jk003", "ipcode": [94477, 94507], "user_id": 31}        |
  |    5 | a5   | {"user": "Jk005", "ipcode": [94507, 94582], "user_id": 56}        |
  +------+------+-------------------------------------------------------------------+
  4 rows in set
  

创建全文索引

OceanBase 数据库 MySQL 模式当前版本可以使用 CREATE TABLE 语句来创建全文索引。

更多全文索引信息，参见 全文索引。

使用全文索引查询数据的信息，参见 全文查询。

全文索引使用限制及注意事项

• 在非分区表上支持创建全文索引，即非分区表上有无主键都可以创建全文索引。对于分区表：

  • 支持在有主键分区表上创建全文索引。
  • 不支持在无主键分区表上创建全文索引。

• 全文索引仅支持应用于 CHAR、VARCHAR 和 TEXT 类型的列。

• 当前版本只支持创建局部（LOCAL）全文索引。

• 创建全文索引时不可以指定 UNIQUE 关键字。

• 如果要创建涉及多列的全文索引，则必须确保这些列具有相同的字符集。

使用 CREATE TABLE 语句创建全文索引

通过 CREATE TABLE 语句在创建表时即为表创建全文索引，简化语法如下：

CREATE TABLE table_name(column_name column_definition,[column_name column_definition,...] FULLTEXT [INDEX | KEY] [index_name](column_name) [WITH PARSER {SPACE | NGRAM | BENG}] [LOCAL];

更多 CREATE TABLE 语法信息，参见 CREATE TABLE。

相关参数说明如下：

• table_name：指定待创建的表的表名。

• column_name：指定表的列。

• column_definition：定义表中各列对应的数据类型。

• FULLTEXT：指定创建全文索引。

  注意

  • 当前版本只支持创建局部全文索引。
  • 全文索引功能目前处于实验阶段，不推荐在生产环境中使用，以免影响系统稳定性。

• INDEX | KEY：表示在该语句中，索引关键字使用 INDEX 或 KEY 都可以。

• index_name：可选，指定待创建的索引的索引名。如果未指定，默认索引名与指定的列名相同。

• WITH PARSER {SPACE | NGRAM | BENG}：可选，指定全文索引的分词器。当前版本仅支持 SPACE（空格）、NGRAM 和 BENG（基础英文）三种内置分词器，不指定默认使用 SPACE（空格）分词器。

• LOCAL：可选，指定创建局部索引。

  说明

  当创建索引时，如果没有指定 LOCAL 或者 GLOBAL 关键字，在分区表上会默认会创建出局部索引（`LOCAL`）。

示例如下：

• 创建表 tbl1，同时创建全文索引 full_idx1_tbl1。

  CREATE TABLE tbl1(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), FULLTEXT INDEX full_idx1_tbl1(name));
  

• 创建表 tbl2，同时创建全文索引 full_idx1_tbl2，指定全文索引的分词器为 NGRAM。

  CREATE TABLE tbl2(id INT, name VARCHAR(18), doc TEXT, FULLTEXT INDEX full_idx1_tbl2(name, doc) WITH PARSER NGRAM);
  

相关文档

更多 MySQL 模式下索引相关的内容，参见以下信息：

• 索引简介

• 在分区表上建立索引

• 查看索引

• 删除索引