创建索引

本文将向您介绍如何使用 SQL 语句来创建索引，同时介绍创建索引的前提条件、索引简介、限制和建议等，并提供一些示例。

信息

本文主要介绍使用 CREATE INDEX 语句创建索引，多值索引除外。多值索引仅支持在创建表时创建。其他创建索引的方式请参见CREATE TABLE 或 ALTER TABLE 语句。

索引简介

索引也叫二级索引，是一种可选的表结构。seekdb 采用的是聚集索引表模型，对于用户指定的主键，系统会自动生成主键索引，而对于用户创建的其他索引，则是二级索引。您可以根据自身业务需要来决定在哪些字段上创建索引，以便加快在这些字段上的查询速度。

更多有关 seekdb 索引的信息，请参见 索引简介。

前提条件

在创建索引前，您需要确认以下事项：

• 您已部署 seekdb。更多有关部署 seekdb 的信息，请参见 部署概述。

• 您已连接到 seekdb。更多连接数据库的信息，请参见 连接方式概述。

• 您已创建数据库。更多有关创建数据库的信息，请参见 创建数据库。

• 您已创建表。更多有关创建表的信息，请参见 创建表。

• 您已拥有 INDEX 权限。查看当前用户权限的相关操作请参见 查看用户权限。如果不具备该权限，请联系管理员为您授权，用户授权的相关操作请参见 直接授予权限。

创建索引的限制

• 在 seekdb 中，索引名称必须在数据库（DataBase）的范围内保证唯一。

• 索引名称的长度不能超过 64 字节。

• 唯一索引使用限制：

  • 在一个表中可以创建多个唯一索引，但是每个唯一索引所对应的列值都必须保持唯一。

  • 如果除了主键之外，还希望其他列的组合满足全局唯一性的要求，需要使用全局唯一索引来实现。

  • 在使用局部唯一索引时，索引必须包含表的分区函数中的所有列。

• 在使用全局索引时，全局索引的分区规则不一定需要与表的分区规则完全相同或一致。

• 空间索引使用限制：

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

  • 创建空间索引的列必须定义为 SRID 属性，否则，在该列上添加的空间索引在后续查询时会无法生效。关于 SRID 相关的介绍，请参见 空间参考系统（SRS）。

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

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

  • seekdb 暂不支持通过 ALTER TABLE 来修改列的 SRID 属性，因此需要建表的时候定义好空间列的 SRID 属性，才能让空间索引生效。

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

  非分区表和分区表上有无主键都可以创建全文索引，创建全文索引限制如下：

  • 全文索引仅支持应用于 CHAR、VARCHAR 和 TEXT 类型的列。
  • 当前版本只支持创建局部（LOCAL）全文索引。
  • 创建全文索引时不可以指定 UNIQUE 关键字。
  • 如果要创建涉及多列的全文索引，则必须确保这些列具有相同的字符集。

创建索引的建议

• 建议使用能够简洁地描述索引所涵盖的列和用途的名称，例如，idx_customer_name。更多命名信息，请参见 对象命名规范综述。

• 如果全局索引的分区规则和主表的分区规则相同并且分区数相同，建议创建一个局部索引。

• 建议并行下发创建索引的 SQL 语句条数，不要超过 seekdb 的核数上限。例如，seekdb 规格为 4 核（4C），则建议并发创建索引不超过 4 条。

• 对经常更新的表要避免对其进行过多的索引，对经常用于查询的字段应该创建索引。

• 数据量小的表建议不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

• 当修改性能远远大于检索性能时，不建议创建索引。

• 创建高效索引：

  • 索引要全部包含所需查询的列，包含的列越全越好，这样可以尽可能的减少回表的行数。

  • 等值条件永远放在最前面。

  • 过滤与排序数据量大的放前面。

使用命令行创建索引

请使用 CREATE INDEX 语句创建索引。

信息

您可以使用 SHOW INDEX FROM table_name; 语句查看表中索引的信息，其中 table_name 为表名。

示例

示例一：创建唯一索引

如果需要索引列上不存在重复的值，可以创建唯一索引。

使用以下 SQL 语句创建一个名为 tbl1 的表，并为表 tbl1 创建一个基于 col2 列的唯一索引。

1. 创建表 tbl1。

   CREATE TABLE tbl1(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));

2. 在表 tbl1 上基于 col2 列创建一个名为 idx_tbl1_col2 的唯一索引。

   CREATE UNIQUE INDEX idx_tbl1_col2 ON tbl1(col2);

3. 查看表 tbl1 索引信息。

   SHOW INDEX FROM tbl1;

   返回结果如下：

   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl1  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl1  |          0 | idx_tbl1_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

示例二：创建非唯一索引

使用以下 SQL 语句创建一个名为 tbl2 的表，并为表 tbl2 创建一个基于 col2 列的索引。

1. 创建表 tbl2。

   CREATE TABLE tbl2(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));

2. 在表 tbl2 上基于 col2 列创建一个名为 idx_tbl2_col2 的索引。

   CREATE INDEX idx_tbl2_col2 ON tbl2(col2);

3. 查看表 tbl2 索引信息。

   SHOW INDEX FROM tbl2;

   返回结果如下：

   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl2  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl2  |          1 | idx_tbl2_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

示例三：创建局部索引

局部索引又名分区索引，创建局部索引的关键字是 LOCAL。局部索引的分区键等同于表的分区键，局部索引的分区数等同于表的分区数，所以局部索引的分区机制和表的分区机制一样。支持创建局部索引和局部唯一索引。如果要使用局部唯一索引去对数据的唯一性做约束，那么局部唯一索引中必须包含表分区键。

使用以下 SQL 语句创建一个名为 tbl3_rl 的二级分区表，并为表 tbl3_rl 创建一个基于 col1 和 col2 列的局部唯一索引索引。

1. 创建 Range + List 二级分区表 tbl3_rl。

   CREATE TABLE tbl3_rl(col1 INT,col2 INT) 
     PARTITION BY RANGE(col1)
     SUBPARTITION BY LIST(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
         SUBPARTITION sp1 VALUES IN(4,6),
         SUBPARTITION sp2 VALUES IN(7,9)),
       PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
         SUBPARTITION sp4 VALUES IN(4,6),
         SUBPARTITION sp5 VALUES IN(7,9))
       ); 

2. 在表 tbl3_rl 上基于 col1 和 col2 列创建一个名为 idx_tbl3_rl_col1_col2 的索引。

   CREATE UNIQUE INDEX idx_tbl3_rl_col1_col2 ON tbl3_rl(col1,col2) LOCAL;

3. 查看表 tbl3_rl 索引信息。

   SHOW INDEX FROM tbl3_rl;

   返回结果如下：

   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table   | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            1 | col1        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            2 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

示例四：创建全局索引

创建全局索引的关键字是 GLOBAL。

使用以下 SQL 语句创建一个名为 tbl4_h 的一级分区表，并为表 tbl4_h 创建一个基于 col2 列的全局索引。

1. 创建 Hash 分区一级分区表 tbl4_h。

   CREATE TABLE tbl4_h(col1 INT PRIMARY KEY,col2 INT)
     PARTITION BY HASH(col1) PARTITIONS 5;

2. 在表 tbl4_h 上创建一个名为 idx_tbl4_h_col2 的全局索引，该索引是基于 col2 列的 Range 分区索引。

   CREATE INDEX idx_tbl4_h_col2 ON tbl4_h(col2) GLOBAL
     PARTITION BY RANGE(col2)
     (PARTITION p0 VALUES LESS THAN(100), 
       PARTITION p1 VALUES LESS THAN(200), 
       PARTITION p2 VALUES LESS THAN(300)
     );

3. 查看表 tbl4_h 索引信息。

   SHOW INDEX FROM tbl4_h;

   返回结果如下：

   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl4_h |          0 | PRIMARY         |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl4_h |          1 | idx_tbl4_h_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set

示例五：创建空间索引

空间索引是一种用于处理和优化空间数据的数据库索引。它被广泛应用于地理信息系统（GIS）和位置数据的存储和查询。seekdb 支持创建常规索引的语法创建空间索引，但空间索引需要使用 SPATIAL 关键字。

使用以下 SQL 语句创建一个名为 tbl5 的表，并为表 tbl5 创建一个基于 g 列的空间索引。

1. 创建表 tbl5。

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

2. 在表 tbl5 上基于 g 列创建一个名为 idx_tbl5_g 的空间索引。

   CREATE INDEX idx_tbl5_g ON tbl5(g);

3. 查看表 tbl5 索引信息。

   SHOW INDEX FROM tbl5;

   返回结果如下：

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

示例六：创建函数索引

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

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

Session 变量固化机制

函数索引中的 Session 变量固化机制确保索引在创建时和使用时的计算语义完全一致，避免因 Session 环境变化导致"索引命中但结果错误"。

工作原理

当创建函数索引时，系统会自动分析索引表达式，识别该表达式依赖哪些 Session 变量（如 time_zone、sql_mode、collation_connection 等），并将这些变量的当前值保存到索引元数据中。

当查询命中函数索引时：

1. 系统根据索引表达式的 local_session_var_id 获取创建索引时保存的 Session 变量快照
2. 在表达式类型推导和计算时，使用保存的变量值而不是当前 Session 的变量值
3. 确保计算结果与创建索引时完全一致

支持的 Session 变量

函数索引支持固化的 Session 变量包括：

| 变量名 | 说明 | |--------|------|----------| | time_zone | 时区设置，影响时间相关函数（如 NOW()、CURTIME()）的结果 | | sql_mode | SQL 模式，影响 SQL 语法解析和执行行为 | | collation_connection | 连接字符集排序规则，影响字符串比较和函数结果 | | max_allowed_packet | 最大允许的数据包大小 | | ob_compatibility_version | seekdb 兼容版本号 |

系统会根据索引表达式动态推导需要固化的变量。例如，如果索引表达式包含 NOW() 等时间函数，则会固化 time_zone；如果包含字符串函数，则可能固化 collation_connection。

使用注意事项

避免依赖易变表达式

虽然系统会自动固化 Session 变量，但建议尽量减少在索引中使用受 Session 影响的函数，优先使用确定性列：

-- ❌ 不推荐（依赖 time_zone）
CREATE INDEX idx_now ON logs ((DATE(NOW())));

-- ✅ 推荐（使用确定性列）
CREATE INDEX idx_log_date ON logs ((DATE(log_time)));

无需手动管理 Session 变量

系统会自动处理 Session 变量的一致性，您无需担心当前 Session 变量变化会影响已创建的函数索引的正确性。查询时会自动使用创建索引时保存的变量快照。

使用以下 SQL 语句创建一个名为 tbl6 的表，并为表 tbl6 创建一个基于 c_time 列的函数索引。

1. 创建表 tbl6。

   CREATE TABLE tbl6(id INT, name VARCHAR(18), c_time DATE);

2. 在表 tbl6 上创建一个名为 idx_tbl6_c_time 的索引，该索引是基于 c_time 列的年份部分进行创建的。

   CREATE INDEX idx_tbl6_c_time ON tbl6((YEAR(c_time)));

3. 使用下面 SQL 语句可以查看创建的函数索引。

   SHOW INDEX FROM tbl6;

   返回结果如下：

   SHOW INDEX FROM tbl6;
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression     |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | tbl6  |          1 | idx_tbl6_c_time |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | year(`c_time`) |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   1 row in set

示例七：创建列存索引

列存索引是一种针对列式存储数据库的索引结构，主要用于优化列式存储的查询性能。

使用以下 SQL 语句创建列存索引。

创建 tbl7 表后，再创建列存索引 idx1_tbl7_cg。

1. 创建表 tbl7。

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

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

   CREATE INDEX idx1_tbl7_cg ON tbl7(id) STORING(age) WITH COLUMN GROUP(each column);

3. 使用下面 SQL 语句可以查看创建的函数索引。

   SHOW INDEX FROM tbl7;

   返回结果如下：

   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl7  |          1 | idx1_tbl7_cg |            1 | id          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   1 row in set

示例九：创建全文索引

示例如下：

创建 tbl8 表后，再创建全文索引 ft_idx1_tbl8。

1. 创建表 tbl8。

   CREATE TABLE tbl8(col1 INT, col2 VARCHAR(4096));

2. 在表 tbl8 上创建全文索引 ft_idx1_tbl8，指定全文索引的分词器为 IK，同时通过 PARSER_PROPERTIES 设置分词器属性。

   CREATE FULLTEXT INDEX ft_idx1_tbl8 ON tbl8(col2)
       WITH PARSER IK
       PARSER_PROPERTIES=(ik_mode='max_word');

3. 使用下面 SQL 语句可以查看创建的函数索引。

   SHOW INDEX FROM tbl8;

   返回结果如下：

   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl8  |          1 | ft_idx1_tbl8 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | FULLTEXT   | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   1 row in set

后续操作

在创建完索引后，您可能需要进行查询性能优化。更多有关 SQL 调优的信息，请参见 SQL 调优概览。