CREATE INDEX
功能描述
在指定的表上创建索引。
注意事项
- 索引自身也占用存储空间、消耗计算资源，创建过多的索引将对数据库性能造成负面影响（尤其影响数据导入的性能，建议在数据导入后再建索引）。因此，仅在必要时创建索引。
- 索引定义里的所有函数和操作符都必须是immutable类型的，即结果必须依赖于其输入参数，而不受任何外部的影响（如另外一个表的内容或者当前时间），该限制可以确保该索引的行为是定义良好的。在一个索引上或WHERE语句中使用用户定义函数，请将其标记为immutable类型函数。
- 在分区表上创建索引时，索引项中必须包含分布列和所有分区键。
- DWS在分区表上创建索引时只支持本地（LOCAL）索引，不支持全局（GLOBAL）索引。
- 列存表和HDFS表支持B-tree索引，不支持创建表达式索引、部分索引。
- 列存表支持通过B-tree索引建立唯一索引。
- 列存表和HDFS表支持的PSORT索引不支持创建表达式索引、部分索引和唯一索引。
- 列存表支持的GIN索引支持创建表达式索引，但表达式不能包含空分词、空列和多列，不支持创建部分索引和唯一索引。
- 列存索引不支持OR查询过滤条件及inlist场景。
- roundrobin表不支持创建主键/唯一索引。
- 对表执行CREATE INDEX或REINDEX操作时会触发索引重建（索引重建过程中会先把数据转储到一个新的数据文件中，重建完成之后会删除原始文件），当表比较大时，重建会消耗较多的磁盘空间。当磁盘空间不足时，要谨慎对待大表CREATE INDEX或REINDEX操作，防止触发集群只读。
- 在GUC参数enable_consistent_oid为on时，对同一张分区表进行CREATE INDEX与REINDEX TABLE并发操作时，低概率会导致不同节点间分区索引的数量不一致，进而导致REINDEX TABLE报错。出现此情况时，重试执行REINDEX TABLE语句即可成功。
- 针对有大批量数据增删改的表，索引个数建议控制在3个以内，最多不超过5个。
- 避免在业务高峰期对大表执行CREATE INDEX和REINDEX操作。
- 更多开发设计规范参见总体开发设计规范。
语法格式
- 在表上创建索引。
1 2 3 4 5 6 7
CREATE [ UNIQUE ] INDEX [ [IF NOT EXISTS] [ schema_name. ] index_name ] ON table_name [ USING method ] ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] [ COMMENT 'text' ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ WHERE predicate ];
- 在分区表上创建索引。
1 2 3 4 5 6 7
CREATE [ UNIQUE ] INDEX [ [IF NOT EXISTS] [ schema_name. ] index_name ] ON table_name [ USING method ] ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] ) [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] [ COMMENT 'text' ] LOCAL [ ( { PARTITION index_partition_name } [, ...] ) ] [ WITH ( { storage_parameter = value } [, ...] ) ] ;
参数说明
|
参数 |
描述 |
取值范围 |
|---|---|---|
|
UNIQUE |
创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，系统会报错。 目前只有行存表B-tree索引和列存表的B-tree索引支持唯一索引。 |
- |
|
IF NOT EXISTS |
指定IF NOT EXISTS时，若不存在同名索引，则可以成功创建索引。若已存在同名索引，创建时不会报错，仅会提示该索引已存在，且不执行任何操作。当使用IF NOT EXISTS时，需要指定索引名。该参数仅9.1.0及以上集群版本支持。 |
- |
|
schema_name |
要创建的索引所在的模式名。指定的模式名需与表所在的模式相同。 |
- |
|
index_name |
要创建的索引名，索引的模式与表相同。 |
取值范围：字符串，要符合标识符的命名规范。 |
|
table_name |
需要为其创建索引的表的名字，可以用模式修饰。 |
取值范围：已存在的表名。 |
|
USING method |
指定创建索引的方法。 |
取值范围：
行存表支持的索引类型：btree（行存表缺省值）、gin、gist。 列存表支持的索引类型：psort（列存表缺省值）、btree、gin。 |
|
column_name |
表中需要创建索引的列的名字（字段名）。 |
如果索引方式支持多字段索引，可以声明多个字段。最多可以声明32个字段。 |
|
expression |
创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括弧中。 |
如果表达式有函数调用的形式，圆括弧可以省略。 表达式索引可用于获取对基本数据的某种变形的快速访问。比如，一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。 在创建表达式索引时，如果表达式中包含IS NULL子句，则这种索引是无效的。此时，建议用户尝试创建一个部分索引。 |
|
COLLATE collation |
COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。 |
- |
|
opclass |
操作符类的名字。 |
对于索引的每一列可以指定一个操作符类，操作符类标识了索引那一列的使用的操作符。例如一个B-tree索引在一个四字节整数上可以使用int4_ops；这个操作符类包括四字节整数的比较函数。实际上对于列上的数据类型默认的操作符类是足够用的。操作符类主要用于一些有多种排序的数据。例如，用户想按照绝对值或者实数部分排序一个复数。能通过定义两个操作符类然后在建立索引时选择合适的类。 |
|
ASC |
指定按升序排序 （默认）。本选项仅行存支持。 |
- |
|
DESC |
指定按降序排序。本选项仅行存支持。 |
- |
|
NULLS FIRST |
指定空值在排序中排在非空值之前，当指定DESC排序时，本选项为默认的。 |
- |
|
NULLS LAST |
指定空值在排序中排在非空值之后，未指定DESC排序时，本选项为默认的。 |
- |
|
NULLS DISTINCT NULLS NOT DISTINCT NULLS IGNORE |
指定Unique唯一索引中索引列NULL值的处理方式。 |
默认取值：该参数默认取值为空，即NULL值可重复插入。 在对插入的新数据和表中原始数据进行列的等值比较时，对于NULL值有以下三种处理方式：
三种处理方式具体的行为参见表4所示。 |
|
COMMENT 'text' |
指定索引的注释信息。 |
- |
|
WITH ( {storage_parameter = value} [, ... ] ) |
指定索引方法的存储参数。包括：FILLFACTOR、INVISIBLE、FASTUPDATE、GIN_PENDING_LIST_LIMIT。 |
取值范围和详细用法参见表3。 |
|
WHERE predicate |
创建一个部分索引。 部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。例如，有一个表，表里包含已记账和未记账的订单，未记账的订单只占表的一小部分而且这部分是最常用的部分，此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。 |
取值范围：predicate表达式只能引用表的字段，它可以使用所有字段，而不仅是被索引的字段。目前，子查询和聚集表达式不能出现在WHERE子句里。 |
|
PARTITION index_partition_name |
索引分区的名称。 |
取值范围：字符串，要符合标识符的命名规范。 |
|
存储参数 |
描述 |
示例 |
||
|---|---|---|---|---|
|
FILLFACTOR |
GIN和PSORT索引不支持该参数，表示一个索引的填充因子（fillfactor），取值范围是一个介于10和100之间的百分比值，用于指定索引页初始填充的百分比，剩余的空间可以用于后续的插入和更新操作，从而减少页面分裂和提高性能。 该参数的作用是控制索引页的填充程度，从而影响数据库的性能和存储空间的利用效率，通过调整FILLFACTOR的值，可以减少后续更新或插入操作导致的页分裂，优化索引的存储和性能。 FILLFACTOR对索引性能的影响：
如何选择合适的FILLFACTOR值：
|
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引，并指定该索引的填充因子FILLFACTOR为70%。 这意味着每个数据页面在初次填充时只会填满70%。剩余的30%空间留给将来的插入和更新操作。
|
||
|
INVISIBLE |
控制优化器是否生成索引扫描相关计划。 适用场景：维护期间临时禁用索引，或测试索引对性能的影响。 取值范围：
默认值：OFF |
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引，并设置优化器不生成该索引扫描相关计划
|
||
|
FASTUPDATE |
仅GIN索引支持该参数，GIN索引是否使用快速更新，当启用后，数据变更会暂存到待处理列表（Pending List）。 取值范围：ON，OFF 默认值：ON |
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定GIN索引，并打开快速更新参数FASTUPDATE，设置该索引Pending List容量的最大值为8MB。 CREATE INDEX ds_ship_mode_t1_index8 ON tpcds.ship_mode_t1 USING gin(SM_SHIP_MODE_SK) WITH (FASTUPDATE = ON, GIN_PENDING_LIST_LIMIT = 8192); |
||
|
GIN_PENDING_LIST_LIMIT |
仅GIN索引支持该参数，当GIN索引启用fastupdate时，设置该索引Pending List容量的最大值。 Pending List是GIN索引特有的数据结构，用于临时存储索引更新操作。当GIN索引启用FASTUPDATE参数时，新的索引项不会直接写入主索引结构，而是先存入Pending List，待满足特定条件时再批量合并到主索引中。 取值范围：64~INT_MAX，单位KB。 默认值：由GUC参数gin_pending_list_limit的值决定（默认为4MB）。 |
示例
- 创建示例表tpcds.ship_mode_t1：
1 2 3 4 5 6 7 8 9 10 11 12
CREATE SCHEMA tpcds; DROP TABLE IF EXISTS tpcds.ship_mode_t1; CREATE TABLE tpcds.ship_mode_t1 ( SM_SHIP_MODE_SK INTEGER NOT NULL, SM_SHIP_MODE_ID CHAR(16) NOT NULL, SM_TYPE CHAR(30) , SM_CODE CHAR(10) , SM_CARRIER CHAR(20) , SM_CONTRACT CHAR(20) ) DISTRIBUTE BY HASH(SM_SHIP_MODE_SK);
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建唯一索引：
1CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建UNIQUE唯一索引，并指定NULL值的处理方式：
1CREATE UNIQUE INDEX ds_ship_mode_t1_index5 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) NULLS NOT DISTINCT;
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建索引时添加索引的注释。
1CREATE INDEX ds_ship_mode_t1_index_comment ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) COMMENT 'index';
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引。
1CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引，并指定该索引的填充因子FILLFACTOR为70%。
1CREATE INDEX ds_ship_mode_t1_index6 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK) WITH (FILLFACTOR = 70);
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引，并设置优化器不生成该索引扫描相关计划(默认为OFF，表示生成，ON表示不生成)。
1CREATE INDEX ds_ship_mode_t1_index7 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK) WITH (INVISIBLE = ON);
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定GIN索引，并打开快速更新参数FASTUPDATE，设置该索引Pending List容量的最大值为8MB。
在表tpcds.ship_mode_t1上SM_CODE字段上创建表达式索引。
1CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建SM_SHIP_MODE_SK大于10的部分索引。
CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
- 创建示例表tpcds.customer_address_p1。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
CREATE TABLE tpcds.customer_address_p1 ( CA_ADDRESS_SK INTEGER NOT NULL, CA_ADDRESS_ID CHAR(16) NOT NULL, CA_STREET_NUMBER CHAR(10) , CA_STREET_NAME VARCHAR(60) , CA_STREET_TYPE CHAR(15) , CA_SUITE_NUMBER CHAR(10) , CA_CITY VARCHAR(60) , CA_COUNTY VARCHAR(30) , CA_STATE CHAR(2) , CA_ZIP CHAR(10) , CA_COUNTRY VARCHAR(20) , CA_GMT_OFFSET DECIMAL(5,2) , CA_LOCATION_TYPE CHAR(20) ) DISTRIBUTE BY HASH(CA_ADDRESS_SK) PARTITION BY RANGE(CA_ADDRESS_SK) ( PARTITION p1 VALUES LESS THAN (3000), PARTITION p2 VALUES LESS THAN (5000) , PARTITION p3 VALUES LESS THAN (MAXVALUE) ) ENABLE ROW MOVEMENT;
创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名字。
1CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
创建分区表索引ds_customer_address_p1_index2，并指定索引分区的名字。
1 2 3 4 5 6 7
CREATE INDEX ds_customer_address_p1_index2 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL ( PARTITION CA_ADDRESS_SK_index1, PARTITION CA_ADDRESS_SK_index2, PARTITION CA_ADDRESS_SK_index3 ) ;
创建分区表索引ds_customer_address_p1_index_comment，并添加索引注释。
1 2 3 4 5 6 7
CREATE INDEX ds_customer_address_p1_index_comment ON tpcds.customer_address_p1(CA_ADDRESS_SK) COMMENT 'index' LOCAL ( PARTITION CA_ADDRESS_SK_index1, PARTITION CA_ADDRESS_SK_index2, PARTITION CA_ADDRESS_SK_index3 ) ;
示例
假设有一张表users，包含id和email（可能为 NULL，表示未提供邮箱）。
场景 1：默认 NULLS DISTINCT（NULL可重复插入）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 创建表 DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT, email TEXT ) DISTRIBUTE BY HASH(email); -- 创建唯一索引（默认 NULLS DISTINCT） CREATE UNIQUE INDEX idx_email_unique ON users (email); -- 插入数据 INSERT INTO users (id, email) VALUES (1, NULL); -- 成功 INSERT INTO users (id, email) VALUES (2, NULL); -- 成功（NULL 被视为不同） INSERT INTO users (id, email) VALUES (3, 'a@example.com'); -- 成功 INSERT INTO users (id, email) VALUES (4, 'a@example.com'); -- 失败（非 NULL 重复） |
场景 2：NULLS NOT DISTINCT（NULL不可重复插入）
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 重新创建唯一索引，指定NULLS NOT DISTINCT DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT, email TEXT ) DISTRIBUTE BY HASH(email); CREATE UNIQUE INDEX idx_email_unique ON users (email) NULLS NOT DISTINCT; -- 插入数据 INSERT INTO users (id, email) VALUES (5, NULL); -- 成功（第一个 NULL） INSERT INTO users (id, email) VALUES (6, NULL); -- 失败（第二个 NULL，违反唯一约束） INSERT INTO users (id, email) VALUES (7, 'b@example.com'); -- 成功 INSERT INTO users (id, email) VALUES (8, 'b@example.com'); -- 失败（非 NULL 重复） |
场景 3：NULLS IGNORE（忽略NULL值）
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 创建索引，忽略NULL值 DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT, email TEXT ) DISTRIBUTE BY HASH(email); CREATE UNIQUE INDEX idx_email_ignore ON users (email) NULLS IGNORE; -- 插入数据 INSERT INTO users (id, email) VALUES (9, NULL); -- 成功（第一个 NULL） INSERT INTO users (id, email) VALUES (10, NULL); -- 成功（NULL值被忽略） INSERT INTO users (id, email) VALUES (11, 'c@example.com'); -- 成功 INSERT INTO users (id, email) VALUES (12, 'c@example.com'); -- 失败（非 NULL 重复） |
相关链接
- 修改和删除索引语法参见ALTER INDEX和DROP INDEX。
- 更多索引案例请参见创建和管理索引 。
- 有关GIN索引的更多示例，请参见GIN索引使用实践。
