CREATE INDEX
功能描述
在指定的表上创建索引。
索引可以用来提高数据库查询性能,但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引:
- 经常执行查询的字段。
- 在连接条件上创建索引,对于存在多字段连接的查询,建议在这些字段上建立组合索引。例如,select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b,可以在t1表上的a,b字段上建立组合索引。
- where子句的过滤条件字段上(尤其是范围条件)。
- 在经常出现在order by、group by和distinct后的字段。
- 对于点查询场景,推荐建立btree索引。
在分区表上创建索引与在普通表上创建索引的语法不太一样,使用时请注意,如分区表上不支持并行创建索引、不支持创建部分索引、不支持NULL FIRST特性。
注意事项
- 索引自身也占用存储空间、消耗计算资源,创建过多的索引将对数据库性能造成负面影响(尤其影响数据导入的性能,建议在数据导入后再建索引)。因此,仅在必要时创建索引。
- 索引定义里的所有函数和操作符都必须是immutable类型的,即结果只能依赖于其输入参数,而不受任何外部的影响(如另外一个表的内容或者当前时间),该限制可以确保该索引的行为是定义良好的。在一个索引上或WHERE语句中使用用户定义函数时,请将其标记为immutable类型函数。
- 在分区表上创建唯一索引时,索引项中必须包含分布列和所有分区键。
- GaussDB(DWS)在分区表上创建索引时只支持本地(LOCAL)索引,不支持全局(GLOBAL)索引。
- 列存表和HDFS表支持B-tree索引,不支持创建表达式索引、部分索引。
- 列存表支持通过B-tree索引建立唯一索引。
- 列存表和HDFS表支持的PSORT索引不支持创建表达式索引、部分索引和唯一索引。
- 列存表支持的GIN索引支持创建表达式索引,但表达式不能包含空分词、空列和多列,不支持创建部分索引和唯一索引。
- roundrobin表不支持创建主键/唯一索引。
- 对表执行CREATE INDEX或REINDEX操作时会触发索引重建(索引重建过程中会先把数据转储到一个新的数据文件中,重建完成之后会删除原始文件),当表比较大时,重建会消耗较多的磁盘空间。当磁盘空间不足时,要谨慎对待大表CREATE INDEX或REINDEX操作,防止触发集群只读。
- 针对有大批量数据增删改的表,索引个数建议控制在3个以内,最多不超过5个。
- 避免在业务高峰期执行对大表执行CREATE INDEX和REINDEX操作。
- 更多开发设计规范参见总体开发设计规范。
语法格式
- 在表上创建索引。
1 2 3 4 5 6
CREATE [ UNIQUE ] INDEX [ [ schema_name. ] index_name ] ON table_name [ USING method ] ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [ COMMENT 'text' ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ WHERE predicate ];
- 在分区表上创建索引。
1 2 3 4 5 6
CREATE [ UNIQUE ] INDEX [ [ schema_name. ] index_name ] ON table_name [ USING method ] ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] ) [ COMMENT 'text' ] LOCAL [ ( { PARTITION index_partition_name } [, ...] ) ] [ WITH ( { storage_parameter = value } [, ...] ) ] ;
参数说明
- UNIQUE
创建唯一性索引,每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时,将导致一个错误。
目前只有行存表B-tree索引和列存表的B-tree索引支持唯一索引。
- schema_name
要创建的索引所在的模式名。指定的模式名需与表所在的模式相同。
- index_name
要创建的索引名,索引的模式与表相同。索引名不能与数据库中已有的表名重复。
取值范围:字符串,要符合标识符的命名规范。
- table_name
需要为其创建索引的表的名字,可以用模式修饰。
取值范围:已存在的表名。
- USING method
指定创建索引的方法。
取值范围:
- btree:B-tree索引使用一种类似于B+树的结构来存储数据的键值,通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。
- gin:GIN索引是倒排索引,可以处理包含多个键的值(比如数组)。
- gist:Gist索引适用于几何和地理等多维数据类型和集合数据类型。
- Psort:Psort索引。针对列存表进行局部排序索引。
行存表支持的索引类型: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排序时,本选项为默认的。
- COMMENT 'text'
指定索引的注释信息。
- WITH ( {storage_parameter = value} [, ... ] )
指定索引方法的存储参数。
取值范围:
只有GIN索引支持FASTUPDATE,GIN_PENDING_LIST_LIMIT参数。GIN和Psort之外的索引都支持FILLFACTOR参数。 - WHERE predicate
创建一个部分索引。部分索引是一个只包含表的一部分记录的索引,通常是该表中比其他部分数据更有用的部分。例如,有一个表,表里包含已记账和未记账的定单,未记账的定单只占表的一小部分而且这部分是最常用的部分,此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。
取值范围:predicate表达式只能引用表的字段,它可以使用所有字段,而不仅是被索引的字段。目前,子查询和聚集表达式不能出现在WHERE子句里。
- PARTITION index_partition_name
索引分区的名称。
取值范围:字符串,要符合标识符的命名规范。
示例
- 在表上创建索引。
创建示例表tpcds.ship_mode_t1:
1 2 3 4 5 6 7 8 9 10 11
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字段上创建唯一索引:
1
CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建索引时添加索引的注释。
1
CREATE 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索引。
1
CREATE 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字段上创建指定GIN索引。
CREATE INDEX ship_mode_index ON tpcds.ship_mode_t1 USING gin(SM_SHIP_MODE_SK);
在表tpcds.ship_mode_t1上SM_CODE字段上创建表达式索引。
1
CREATE 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 25
DROP TABLE IF EXISTS tpcds.customer_address_p1; 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,不指定索引分区的名字。
1
CREATE 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 ) ;