在指定的表上创建索引。
索引可以用来提高数据库查询性能,但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引:
在分区表上创建索引与在普通表上创建索引的语法不太一样,使用时请注意,如分区表上不支持并行创建索引、不支持创建部分索引。
在表上创建索引。
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ] ON table_name [ USING method ]
({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] )
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ];
在分区表上创建索引。
CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ] ON table_name [ USING method ]
( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
[ LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
[ WITH ( { storage_parameter = value } [, ...] ) ]
[ TABLESPACE tablespace_name ];
UNIQUE
创建唯一性索引,每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时,将导致一个错误。
目前只有B-tree索引支持唯一索引。
CONCURRENTLY
以不阻塞DML的方式创建索引(加ShareUpdateExclusiveLock锁)。创建索引时,一般会阻塞其他语句对该索引所依赖表的访问。指定此关键字,可以实现创建过程中不阻塞DML。
说明:
- 创建索引时指定此关键字,需要执行先后两次对该表的全表扫描来完成build,第一次扫描的时候创建索引,不阻塞读写操作;第二次扫描的时候合并更新第一次扫描到目前为止发生的变更。
- 由于需要执行两次对表的扫描和build,而且必须等待现有的所有可能对该表执行修改的事务结束。这意味着该索引的创建比正常耗时更长,同时因此带来的CPU和I/O消耗对其他业务也会造成影响。
- 如果在索引构建时发生失败,那会留下一个“不可用”的索引。这个索引会被查询忽略,但它仍消耗更新开销。这种情况推荐的恢复方法是删除该索引并尝试再次CONCURRENTLY建索引。
- 由于在第二次扫描之后,索引构建必须等待任何持有早于第二次扫描拿的快照的事务终止,而且建索引时加的ShareUpdateExclusiveLock锁(4级)会和大于等于4级的锁冲突,在创建这类索引时,容易引发卡住(hang)或者死锁问题。例如:
- 两个会话对同一个表创建CONCURRENTLY索引,会引起死锁问题;
- 两个会话,一个对表创建CONCURRENTLY索引,一个drop table,会引起死锁问题;
- 三个会话,会话1先对表a加锁,不提交,会话2接着对表b创建CONCURRENTLY索引,会话3接着对表a执行写入操作,在会话1事务未提交之前,会话2会一直被阻塞;
- 将事务隔离级别设置成可重复读(默认为读已提交),起两个会话,会话1起事务对表a执行写入操作,不提交,会话2对表b创建CONCURRENTLY索引,在会话1事务未提交之前,会话2会一直被阻塞。
schema_name
模式的名称。
取值范围:已存在模式名。
index_name
要创建的索引名,索引的模式与表相同。
取值范围:字符串,要符合标识符的命名规范。
table_name
需要为其创建索引的表的名称,可以用模式修饰。
取值范围:已存在的表名。
USING method
指定创建索引的方法。
取值范围:
行存表(ASTORE存储引擎)支持的索引类型:btree(行存表缺省值)、hash、gin、gist。行存表(USTORE存储引擎)支持的索引类型:ubtree。列存表支持的索引类型:Psort(列存表缺省值)、btree、gin。
说明: 列存表对GIN索引支持仅限于对于tsvector类型的支持,即创建列存GIN索引入参需要为to_tsvector函数(的返回值)。此方法为GIN索引比较普遍的使用方式。
column_name
表中需要创建索引的列的名称(字段名)。
如果索引方式支持多字段索引,可以声明多个字段。全局索引最多可以声明31个字段,其他索引最多可以声明32个字段。
expression
创建一个基于该表的一个或多个字段的表达式索引,通常必须写在圆括弧中。如果表达式有函数调用的形式,圆括弧可以省略。
表达式索引可用于获取对基本数据的某种变形的快速访问。比如,一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。
在创建表达式索引时,如果表达式中包含IS NULL子句,则这种索引是无效的。此时,建议用户尝试创建一个部分索引。
COLLATE collation
COLLATE子句指定列的排序规则(该列必须是可排列的数据类型)。如果没有指定,则使用默认的排序规则。排序规则可以使用“select * from pg_collation”命令从pg_collation系统表中查询,默认的排序规则为查询结果中以default开始的行。
opclass
操作符类的名称。对于索引的每一列可以指定一个操作符类,操作符类标识了索引那一列的使用的操作符。例如一个B-tree索引在一个四字节整数上可以使用int4_ops;这个操作符类包括四字节整数的比较函数。实际上对于列上的数据类型默认的操作符类是足够用的。操作符类主要用于一些有多种排序的数据。例如,用户想按照绝对值或者实数部分排序一个复数。能通过定义两个操作符类然后当建立索引时选择合适的类。
ASC
指定按升序排序(默认)。
DESC
指定按降序排序。
NULLS FIRST
指定空值在排序中排在非空值之前,当指定DESC排序时,本选项为默认的。
NULLS LAST
指定空值在排序中排在非空值之后,未指定DESC排序时,本选项为默认的。
LOCAL
指定创建的分区索引为LOCAL索引。
GLOBAL
指定创建的分区索引为GLOBAL索引,当不指定LOCAL、GLOBAL关键字时,默认创建GLOBAL索引。
WITH ( {storage_parameter = value} [, ... ] )
指定索引方法的存储参数。
取值范围:
只有GIN索引支持FASTUPDATE,GIN_PENDING_LIST_LIMIT参数。GIN和Psort之外的索引都支持FILLFACTOR参数。
FILLFACTOR
一个索引的填充因子(fillfactor)是一个介于10和100之间的百分数。
取值范围:10~100
FASTUPDATE
GIN索引是否使用快速更新。
取值范围:ON,OFF
默认值:ON
GIN_PENDING_LIST_LIMIT
当GIN索引启用fastupdate时,设置该索引pending list容量的最大值。
取值范围:64~INT_MAX,单位KB。
默认值:gin_pending_list_limit的默认取决于GUC中gin_pending_list_limit的值(默认为4MB)
TABLESPACE tablespace_name
指定索引的表空间,如果没有声明则使用默认的表空间。
取值范围:已存在的表空间名。
WHERE predicate
创建一个部分索引。部分索引是一个只包含表的一部分记录的索引,通常是该表中比其他部分数据更有用的部分。例如,有一个表,表里包含已记账和未记账的定单,未记账的定单只占表的一小部分而且这部分是最常用的部分,此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。
取值范围:predicate表达式只能引用表的字段,它可以使用所有字段,而不仅是被索引的字段。目前,子查询和聚集表达式不能出现在WHERE子句里。
PARTITION index_partition_name
索引分区的名称。
取值范围:字符串,要符合标识符的命名规范。
TABLESPACE index_partition_tablespace
索引分区的表空间。
取值范围:如果没有声明,将使用分区表索引的表空间index_tablespace。
--创建表tpcds.ship_mode_t1。
openGauss=# create schema tpcds;
openGauss=# 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)
)
;
--在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建普通的唯一索引。
openGauss=# 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字段上创建指定B-tree索引。
openGauss=# CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
--在表tpcds.ship_mode_t1上SM_CODE字段上创建表达式索引。
openGauss=# 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的部分索引。
openGauss=# CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
--重命名一个现有的索引。
openGauss=# ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;
--设置索引不可用。
openGauss=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
--重建索引。
openGauss=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
--删除一个现有的索引。
openGauss=# DROP INDEX tpcds.ds_ship_mode_t1_index2;
--删除表。
openGauss=# DROP TABLE tpcds.ship_mode_t1;
--创建表空间。
openGauss=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
openGauss=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
openGauss=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
openGauss=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
--创建表tpcds.customer_address_p1。
openGauss=# 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)
)
TABLESPACE example1
PARTITION BY RANGE(CA_ADDRESS_SK)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
--创建分区表索引ds_customer_address_p1_index1,不指定索引分区的名称。
openGauss=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
--创建分区表索引ds_customer_address_p1_index2,并指定索引分区的名称。
openGauss=# 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 TABLESPACE example3,
PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
)
TABLESPACE example2;
--创建GLOBAL分区索引
openGauss=CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
--不指定关键字,默认创建GLOBAL分区索引
openGauss=CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);
--修改分区表索引CA_ADDRESS_SK_index2的表空间为example1。
openGauss=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index2 TABLESPACE example1;
--修改分区表索引CA_ADDRESS_SK_index3的表空间为example2。
openGauss=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index3 TABLESPACE example2;
--重命名分区表索引。
openGauss=# ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
--删除索引和分区表。
openGauss=# DROP INDEX tpcds.ds_customer_address_p1_index1;
openGauss=# DROP INDEX tpcds.ds_customer_address_p1_index2;
openGauss=# DROP TABLE tpcds.customer_address_p1;
--删除表空间。
openGauss=# DROP TABLESPACE example1;
openGauss=# DROP TABLESPACE example2;
openGauss=# DROP TABLESPACE example3;
openGauss=# DROP TABLESPACE example4;
--创建列存表以及列存表GIN索引。
openGauss=# create table cgin_create_test(a int, b text) with (orientation = column);
CREATE TABLE
openGauss=# create index cgin_test on cgin_create_test using gin(to_tsvector('ngram', b));
CREATE INDEX
create index
建议仅在匹配如下条件之一时创建索引:
约束限制:
分区表上不支持创建部分索引。
分区表创建GLOBAL索引时,存在以下约束条件:
在相同属性列上,分区LOCAL索引与GLOBAL索引不能共存。
GLOBAL索引,最大支持31列。
如果alter语句不带有UPDATE GLOBAL INDEX,那么原有的GLOBAL索引将失效,查询时将使用其他索引进行查询;如果alter语句带有UPDATE GLOBAL INDEX,原有的GLOBAL索引仍然有效,并且索引功能正确。
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。