创建索引

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

背景信息

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 的示例如下：

创建表 tbl2。

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

在表 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 的示例如下：

创建表 tbl3。

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

为表 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 的示例如下：

创建表 tbl2_g。

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

为表创建空间索引 tbl2_g_idx1。

CREATE SPATIAL INDEX tbl2_g_idx1 ON tbl2_g(g);

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

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 的示例如下：

创建表 tbl3_g。

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

在表上创建空间索引。

ALTER TABLE tbl3_g ADD SPATIAL INDEX tbl3_g_idx1(g);

查看索引信息。

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，示例如下：

创建表 tbl2_func。

CREATE TABLE tbl2_func(c1 int, c2 int);

创建函数索引 tbl2_func_idx1。

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

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

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。

创建表 tbl3_func。

CREATE TABLE tbl3_func(c1 int, c2 int);

为表创建 2 个函数索引。

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

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

查看索引信息。

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。

创建表 tbl6。

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

在表 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。

创建表 tbl7。

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

为表 tbl7 创建列存索引 idx1_tbl7_cg，并且在索引表上存储 age 列的冗余数据。
```
ALTER TABLE tbl7 ADD INDEX idx1_tbl7_cg(id) STORING(age) WITH COLUMN GROUP(each column);
```

背景信息

注意事项

创建普通索引

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

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

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

创建空间索引

空间索引使用限制

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

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

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

创建函数索引

函数索引使用限制

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

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

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

创建列存索引

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

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

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

相关文档

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

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

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

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

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

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

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

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

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