# CREATE GLOBAL INDEX #### 功能描述 在指定的表上创建全局二级索引（Global Secondary Index，简称GSI）。 全局二级索引允许用户定义与基表分布不一致的索引，从而实现基表非分布列查询的单节点计划和基表非分布列上的unique/主键约束。 ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 建议在GSI的基表创建普通索引提升IUD执行效率。另外，在基表上创建GSI后将影响IUD的性能，带来劣化。 #### 功能约束 - 只支持GTM-LITE模式。 - GSI的分布列不支持更新（UPDATE、MERGE INTO）操作。 - 不支持UPSERT，建有GSI的基表上不支持IUD returning功能。 - GSI支持表达式索引，但存在以下约束： - 同基表约束，不支持分布键包含表达式（且无法创建索引列仅包含表达式的GSI，因为此时分布键必定为表达式）。 - 同普通索引约束，不支持CONTAINING列中包含表达式。 - 若表上存在以"expr"为前缀的列名，不支持创建带有表达式的GSI。 - 创建GSI： - 不支持创建与基表分布一致的GSI。 - 不支持基表为复制表、list/range分布、二级分区表等，对于GSI本身不支持hash分布以外的分布。 - 当基表为分区非HASHBUCKET表时，GSI最多支持27列；当基表为HASHBUCKET非分区表时，GSI最多支持27列；当基表为HASHBUCKET分区表时，GSI最多支持26列；当基表为非分区非HASHBUCKET表最多支持28列（包括索引键和分布键）。 - 在线创建重建GSI： - 只支持对Ustore表执行CREATE GSI CONCURRENTLY。 - 不支持表达式索引和部分索引CREATE GSI CONCURRENTLY。 #### 注意事项 - 对于创建GSI、重建GSI以及涉及重建GSI的操作：比如分区表分区操作（包括DROP、TRUNCATE、MERGE、SPLIT、EXCHANGE PARTITION）指定UPDATE DISTRIBUTED GLOBAL INDEX，ALTER TABLE涉及重建数据的操作，HASHBUCKET表ALTER SET TABLESPACE操作、MOVE PARTITION操作，建议开启STREAM模式，以达到最优性能。（其中，STREAM模式指设置enable_stream_operator参数为ON，并设置create_gsi_opt参数值为build） - 如果在执行VACUUM FULL、CLUSTER或者REINDEX操作时中断，表上的GSI可能会变为UNUSABLE状态，此时查询语句走GSI会报错，建议执行REINDEX INDEX重建GSI。 - 在对建有GSI的基表执行COPY、GDS数据导入时，需要开启enable_stream_operator参数，以达到最优数据导入性能。 - 回表支持STREAM和非STREAM分布式计划。其中STREAM分布式计划，继承STREAM相关约束。考虑到STREAM通信时延，当选择率过低或者谓词命中行数较少时，性能非最优，不建议使用回表计划，建议与普通索引配合使用。非STREAM分布式计划，需使用gsitable hint，适合在谓词命中行数较少的场景使用。当STREAM分布式计划性能非最优时，也可考虑使用非STREAM分布式计划。具体案例参见[7.14.11-全局二级索引回表扫描](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1729.html#ZH-CN_TOPIC_0000002527826070)。 - 对于Insert into select批量插入场景，建议打开enable_stream_operator，插入执行STREAM计划 （当基表为段页式表，HASHBUCKET表，以及防篡改表时，不会执行STREAM计划，仍然采用回到CN的计划），如果关闭enable_stream_operator，执行计划采用回到CN的方式，性能较差（类比503.1.0版本创建GSI性能）。 - 对于INSERT、UPDATE、DELETE，执行计划采用分布式执行计划，会有性能损失，其中，UPDATE/DELETE批量场景，执行计划采用回到CN的方式，性能较差。 - 当ENABLE_PBE_OPTIMIZATION关闭时，对于INSERT、UPDATE、DELETE、MERGE INTO操作的GSI层将会走gplan。 - 当前会使GSI失效的操作： - REINDEX数据库级、CLUSTER数据库级/分区级、ALTER TABLE PARTITION（DROP、TRUNCATE、MERGE、SPLIT、EXCHANGE PARTITION未指定UPDATE DISTRIBUTED GLOBAL INDEX将失效分区表上的所有GSI，其中，EXCHANGE PARTITION未指定UPDATE DISTRIBUTED GLOBAL INDEX将同步失效普通表上的所有GSI） 。 - 不支持对基表列名或者ctid、xc_node_hash、xmin、xmax、tableoid（当基表为分区表时）、tablebucketid（当基表为HASHBUCKET表时）增加_new$$、_NEW$$后与自身列名重复的基表创建GSI。 - 不支持REINDEX INDEX并发重建GSI，会造成业务阻塞。 #### 语法格式 ``` CREATE GLOBAL [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ] ON table_name [ USING method ] ({ { column_name [ ( length ) ] | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [CONTAINING (column_name [ ,... ])] [DISTRIBUTE BY hash(column_name [ ,... ])][ WITH ( {storage_parameter = value} [, ... ] )] [ TABLESPACE tablespace_name ] [ VISIBLE | INVISIBLE ] [ WHERE predicate]; ``` ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/figure/zh-cn_image_0000002558217219.png "点击放大") #### 参数说明 - **UNIQUE** 创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将报错。 - **CONCURRENTLY** 以不阻塞DML的方式创建索引（加ShareUpdateExclusiveLock锁）。创建索引时，一般会阻塞其他语句对该索引所依赖表的访问。指定此关键字，可以实现创建过程中不阻塞DML。 - 普通CREATE GLOBAL INDEX命令可以在事务内执行，但是CREATE GLOBAL INDEX CONCURRENTLY不可以在事务内执行。 - 索引在线创建过程中可能会发生构建失败。构建失败的可能情形包括用户指令终止在线创建命令、修改该索引的定义、修改基表定义且影响了该索引的定义等。对于非严重错误，索引在线构建失败时有报错提示并尝试自动清理该索引。对于严重错误（如FATAL/PANIC/数据库故障等场景）或清理失败时，会留下一个"不可用"的索引。这个索引会被查询忽略，但它仍消耗IUD开销。因此推荐的恢复方法是通过DROP INDEX IF EXISTS语法删除该索引并尝试再次在线创建索引，或通过REINDEX/REBUILD语法重建索引。 - CREATE GLOBAL INDEX CONCURRENTLY执行期间，应尽量避免与其他DDL并发执行，一定概率出现死锁、报错等，可以重新连接客户端进行重试。 - **schema_name** 模式的名称。 取值范围：已存在模式名。 - **index_name** 要创建的索引名，不能包含模式名，索引的模式与表相同。 取值范围：字符串，要符合[标识符命名规范](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-0322.html#ZH-CN_TOPIC_0000002559105775__zh-cn_topic_0000002558056023_section14873217506)。 - **table_name** 需要为其创建索引的表的名称，可以用模式修饰。 取值范围：已存在的表名。 - **USING method** 指定创建索引的方法。 取值范围：ubtree，提供多版本B-tree索引，索引页面上包含事务信息。 - **column_name** 表中需要创建索引的列的名称（字段名）。 如果索引方式支持多字段索引，可以声明多个字段，对于非分区基表最多可以声明28个字段，对于分区基表最多可以声明27个字段。 - **column_name ( length )** 支持前缀键，详见：[column_name ( length )](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-0549.html#ZH-CN_TOPIC_0000002527986148__zh-cn_topic_0000002558055321_li1464834613545)。 前缀索引作为一种表达式索引，在GSI中的约束与表达式键一致。 - **expression** 创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括号中。如果表达式有函数调用的形式，圆括号可以省略。表达式索引可用于获取对基本数据的某种变形的快速访问。比如，一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。在创建表达式索引时，如果表达式中包含IS NULL子句，则这种索引是无效的。此时，建议用户尝试创建一个部分索引。 - **COLLATE collation** COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用"select \* from pg_collation"命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。 - **opclass** 操作符类的名称。对于索引的每一列可以指定一个操作符类，操作符类标识了索引那一列的使用的操作符。 - **ASC** 指定按升序排序 （默认）。 - **DESC** 指定按降序排序。 - **NULLS FIRST** 指定空值在排序中排在非空值之前，当指定DESC排序时，本选项为默认的。 - **NULLS LAST** 指定空值在排序中排在非空值之后，未指定DESC排序时，本选项为默认的。 - **CONTAINING** 全局二级索引可以包含索引键以外的基表属性。 - **CONTAINING (column_name \[, ...\])** 全局二级索引包含的索引键以外的基表属性。 - **DISTRIBUTE BY** 指定全局二级索引分布键，其分布键需要与基表不同，且只能使用哈希分布方式。 - **DISTRIBUTE BY hash(column_name \[,...\])** 全局二级索引分布键。 需要被包含在索引键中。 - **WITH ( {storage_parameter = value} \[, ... \] )** 指定GSI的存储参数。 取值范围： - FILLFACTOR 一个索引的填充因子（fillfactor）是一个介于10和100之间的百分数。 取值范围：10\~100 - STORAGE_TYPE 指定GSI的存储类型。 取值范围：USTORE - INDEXSPLIT 控制选择UBtree合适分裂点的方式，与Btree相同。 取值范围：DEFAULT、INSERTPT - enable_tde 指定该索引为加密索引。数据库会自动将加密索引中的数据先加密再存储。使用该参数前，请确保已通过GUC参数enable_tde开启透明加密功能，并通过GUC参数tde_key_info设置访问密钥服务的信息，以及基表也设置了enable_tde属性。在《特性指南》中"透明数据加密"章节可获取该参数的详细使用方法。本参数仅支持btree，ubtree索引，不支持hash等其他索引。 取值范围：on/off。设置enable_tde=on时，key_type、tde_cmk_id、dek_cipher参数由数据库自动生成。 默认值：off - encrypt_algo 指定加密索引的加密算法，需与enable_tde结合使用。 取值范围：字符串，有效值为：AES_128_CTR，SM4_CTR、AES_128_CTR_SHA256_HMAC、SM4_CTR_SM3_HMAC或AES_128_GCM。 默认值：如果enable_tde=on，GUC参数tde_encrypt_config中子参数table_algorithm不为空，默认值与table_algorithm相同；如果table_algorithm为空，默认值为AES_128_CTR。如果enable_tde=off，默认值为空。 - dek_cipher 数据密钥的密文。用户为索引设置enable_tde参数后，索引会自动复制基表的dek_cipher参数，该参数不支持主动设置或更改。 取值范围：字符串 默认值：空 - key_type 主密钥的类型。用户为表设置enable_tde参数后，索引会自动复制基表的key_type参数，该参数不支持主动设置或更改。 默认值：空 - cmk_id 主密钥的ID。用户为表设置enable_tde参数后，索引会自动复制基表的cmk_id参数，该参数不支持主动设置或更改。 取值范围：字符串 默认值：空 - **TABLESPACE tablespace_name** 指定索引的表空间，如果没有声明则使用默认的表空间。 取值范围：已存在的表空间名。 - **VISIBLE** 设置索引状态为可见，此选项为默认的。 ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - VISIBLE仅支持在ORA模式数据库下（即sql_compatibility = 'ORA'）设置，在其他数据库兼容模式下不支持。 - 当disable_keyword_options参数设置为"visible"时，此关键字不支持使用。 - 升级未提交阶段，不支持使用此关键字。 - **INVISIBLE** 设置索引状态为不可见。 ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - INVISIBLE仅支持在ORA模式数据库下（即sql_compatibility = 'ORA'）设置，在其他数据库兼容模式下不支持。 - 当disable_keyword_options参数设置为"invisible"时，此关键字不支持使用。 - 升级未提交阶段，不支持使用此关键字。 - **WHERE predicate** 创建一个部分索引。部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。例如，有一个表，表里包含已记账和未记账的订单，未记账的订单只占表的一小部分而且这部分是最常用的，此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。 取值范围：predicate表达式只能引用表的字段，它可以使用所有字段，而不仅是被索引的字段。目前，子查询和聚集表达式不能出现在WHERE子句里。不建议使用int等数值类型作为predicate，因为int等数值类型可以隐式转换为BOOLEAN值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。 #### 示例 ``` --创建包含3列的基表test1。 gaussdb=# CREATE TABLE test1(c1 int, c2 int, c3 int); --在test表的c2列上创建GSI，并包含c3列，基于c2列哈希分布。 gaussdb=# CREATE GLOBAL INDEX idx_gsi_1 ON test1(c2) CONTAINING(c3) DISTRIBUTE BY HASH(c2); --创建包含3列的基表test2。 gaussdb=# CREATE TABLE test2(c1 int, c2 int, c3 int); --在test2表的c2列上创建GSI，并包含c3列，默认基于c2列哈希分布。 gaussdb=# CREATE GLOBAL INDEX idx_gsi_2 ON test2(c2) CONTAINING(c3); --创建包含3列的基表test3。 gaussdb=# CREATE TABLE test3(c1 int, c2 int, c3 int); --在test3表的c2列上创建UNIQUE形式的GSI，默认基于c2列哈希分布。 gaussdb=# CREATE GLOBAL UNIQUE INDEX idx_gsi_3 ON test3(c2) DISTRIBUTE BY HASH(c2); --删除索引。 gaussdb=# DROP INDEX idx_gsi_1; gaussdb=# DROP INDEX idx_gsi_2; gaussdb=# DROP INDEX idx_gsi_3; --删除表。 gaussdb=# DROP TABLE test1; gaussdb=# DROP TABLE test2; gaussdb=# DROP TABLE test3; ``` #### 相关链接 [ALTER INDEX](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-0491.html#ZH-CN_TOPIC_0000002559105875)，[CREATE INDEX](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-0549.html#ZH-CN_TOPIC_0000002527986148)，[DROP INDEX](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-0598.html#ZH-CN_TOPIC_0000002527986174)