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后的字段。
在分区表上创建索引与在普通表上创建索引的语法不太一样，使用时请注意，如当索引带GLOBAL/LOCAL关键字或者创建索引为GLOBAL索引时不支持创建部分索引。
注意事项
- 索引自身也占用存储空间、消耗计算资源，创建过多的索引将对数据库性能造成负面影响（尤其影响数据导入的性能，建议在数据导入后再建索引）。因此，仅在必要时创建索引。
- 索引定义里的所有函数和操作符都必须是immutable类型的，即它们的结果必须只能依赖于它们的输入参数，而不受任何外部的影响（如另外一个表的内容或者当前时间）。这个限制可以确保该索引的行为是定义良好的。要在一个索引上或WHERE中使用用户定义函数，请把它标记为immutable类型函数。
- 分区表索引分为LOCAL索引与GLOBAL索引，LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。
- 被授予CREATE ANY INDEX权限的用户，可以在public模式和用户模式下创建索引。
- 如果表达式索引中调用的是用户自定义函数，按照函数创建者权限执行表达式索引函数。
语法格式
- 在表上创建索引。
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ] ON table_name [ USING method ] ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [ INCLUDE ( column_name [, ...] )] [ 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 | SUBPARTITION index_subpartition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ] [ INCLUDE ( column_name [, ...] )] [ WITH ( { storage_parameter = value } [, ...] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ];
参数说明
- UNIQUE
创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将导致一个错误。
目前只有B-tree及UBtree索引支持唯一索引。
- CONCURRENTLY
以不阻塞DML的方式创建索引（加ShareUpdateExclusiveLock锁）。创建索引时，一般会阻塞其他语句对该索引所依赖表的访问。指定此关键字，可以实现创建过程中不阻塞DML。
- 此选项只能指定一个索引的名称。
- 普通CREATE INDEX命令可以在事务内执行，但是CREATE INDEX CONCURRENTLY不可以在事务内执行。
- 分区表不支持CONCURRENTLY方式创建索引。对于临时表，支持使用CONCURRENTLY关键字创建索引，但是实际创建过程中，采用的是阻塞式的创建方式，因为没有其他会话会并发访问临时表，并且阻塞式创建成本更低。
- 创建索引时指定此关键字，需要执行先后两次对该表的全表扫描来完成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会一直被阻塞。
- 在I/O、CPU不受限的情况下，在线创建索引对业务性能的劣化一般可以控制在10%以内，但在特殊场景下劣化可能会超过此数值。这是因为在线创建索引本身是一种消耗I/O、CPU资源较多的长事务，需要比离线创建索引消耗更多的资源。在线创建索引事务持续时间越长，对业务性能的影响越大。在线创建索引时间与基表数据量、并发DML产生的数据量正相关，在I/O、CPU不受限的情况下，在线创建索引时间大约是离线创建索引的2~6倍，但当并发事务量较大（>10000tps）或存在资源争抢的情况时，可能会超过此数值。可以使用并行创建索引来缩短创建索引时间；在线并行创建索引性能随着并行工作线程数量增加而提升到一定值后稳定，相比串行创建索引性能一般可提升30%左右。建议在业务低谷期进行在线创建索引，以避免对业务造成较大影响。虽然在线创建索引在一定程度上提供了业务不中断的能力，但仍然需要谨慎实施。
- schema_name
模式的名称。
取值范围：已存在模式名。
- index_name
要创建的索引名，索引的模式与表相同。
取值范围：字符串，要符合标识符命名规范。
- table_name
需要为其创建索引的表的名称，可以用模式修饰。
取值范围：已存在的表名。
- USING method
指定创建索引的方法。
取值范围：
- btree：B-tree索引使用一种类似于B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。建索引时在表为ustore时会自动变换为ubtree。
- ubtree：仅供ustore表使用的多版本B-tree索引，索引页面上包含事务信息，能并自主回收页面。ubtree索引默认开启insertpt功能。
行存表（ASTORE存储引擎）支持的索引类型：btree（行存表缺省值）、。行存表（USTORE存储引擎）支持的索引类型：ubtree。
- 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索引。
- INCLUDE ( column_name [, ...] )
可选的 INCLUDE 子句指定将一些非键列（non-key columns）包含在索引中。非键列不能用于作为索引扫描的加速搜索条件，同时在检查索引的唯一性约束时会忽略它们。
仅索引扫描 (Index Only Scan) 可以直接返回非键列中的内容，而不必去访问索引所对应的堆表。
将非键列添加为 INCLUDE 列需要保守一些，尤其是对于宽列。如果索引元组超过索引类型允许的最大大小，数据将插入失败。需要注意的是，任何情况下为索引添加非键列都会增加索引的空间占用，从而可能减慢搜索速度。
目前只有ubtree索引访问方式支持该特性。非键列会被保存在与堆元组对应的索引叶子元组中，不会包含在索引上层页面的元组中。
- WITH ( {storage_parameter = value} [, ... ] )
指定索引方法的存储参数。
取值范围：
- TABLESPACE tablespace_name
指定索引的表空间，如果没有声明则使用默认的表空间。
取值范围：已存在的表空间名。
- WHERE predicate
创建一个部分索引。部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。例如，有一个表，表里包含已记账和未记账的订单，未记账的订单只占表的一小部分而且这部分是最常用的部分，此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。
取值范围：predicate表达式只能引用表的字段，它可以使用所有字段，而不仅是被索引的字段。目前，子查询和聚集表达式不能出现在WHERE子句里。不建议使用int等数值类型作为predicate，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。
对于分区表索引，当创建索引带GLOBAL/LOCAL关键字，或者最终创建的索引类型为GLOBAL索引时，不支持带WHERE子句创建索引。
- PARTITION index_partition_name
索引分区的名称。
取值范围：字符串，要符合标识符命名规范。
- SUBPARTITION index_subpartition_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;
相关链接
优化建议
- 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后的字段。
约束限制：- 普通表的索引支持最大列数为32列；分区表的GLOBAL索引支持最大列数为31列。
- 单个索引大小不能超过索引页面大小（8k），其中B-tree及UBtree索引不能超过页面大小的三分之一。
- 分区表上不支持创建部分索引。
- 分区表创建GLOBAL索引时，存在以下约束条件：
- 不支持表达式索引、部分索引
- 仅支持B-tree索引
- 在相同属性列上，分区LOCAL索引与GLOBAL索引不能共存。
- 如果alter语句不带有UPDATE GLOBAL INDEX，那么原有的GLOBAL索引将失效，查询时将使用其他索引进行查询；如果alter语句带有UPDATE GLOBAL INDEX，原有的GLOBAL索引仍然有效，并且索引功能正确。
