# CREATE GLOBAL INDEX #### 功能描述 在指定的表上创建全局二级索引（Global Secondary Index，简称GSI）。 全局二级索引允许用户定义与基表分布不一致的索引，从而实现基表非分布列查询的单节点计划和基表非分布列上的unique/主键约束。 ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 建议在GSI的基表创建普通索引提升DML操作执行效率。另外，在基表上创建GSI后将影响DML操作的性能，带来劣化。 #### 功能约束 - 仅支持GTM-LITE模式。 - GSI支持表达式索引，但存在以下约束： - 同基表约束，不支持分布键包含表达式（且无法创建索引列仅包含表达式的GSI，因为此时分布键必定为表达式）。 - 同普通索引约束，不支持CONTAINING列中包含表达式。 - 若表上存在以"expr"为前缀的列名，不支持创建带有表达式的GSI。 - 创建GSI： - 不支持创建与基表分布一致的GSI。 - 不支持基表为复制表、List分布/Range分布等，对于GSI本身不支持Hash分布以外的分布。 - 当基表为分区非HASHBUCKET表时，GSI最多支持27列；当基表为HASHBUCKET非分区表时，GSI最多支持27列；当基表为HASHBUCKET分区表时，GSI最多支持26列；当基表为非分区非HASHBUCKET表最多支持28列。 - 在线创建重建GSI： 不支持表达式索引和部分索引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通信时延，当选择率过低或者谓词命中行数较少时，性能非最优，不建议使用GSI STREAM回表计划。非STREAM分布式回表计划，需使用gsitable hint，适合在谓词命中行数较少的场景使用。当STREAM回表计划性能非最优时，也可考虑使用非STREAM分布式回表计划。具体案例参见《SQL调优指南》 中 "实际调优案例 \> 案例：使用全局二级索引加速查询"章节。 - 对于INSERT INTO SELECT批量插入、UPDATE批量更新、DELETE批量删除、UPSERT批量场景，建议打开enable_stream_operator，执行STREAM计划 （当基表为HASHBUCKET表，以及防篡改表时，不会执行STREAM计划，仍然采用回到CN的计划），如果关闭enable_stream_operator，执行计划采用回到CN的方式，性能较差。 - 对于建有GSI的基表的DELETE、UPDATE、UPSERT语句，若为STREAM计划，同UPDATE约束，不支持并发更新同一行。 - GSI支持UPSERT，约束和普通UPSERT保持一致，具体请参见[参数说明](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0349.html#ZH-CN_TOPIC_0000002793707311__zh-cn_topic_0059778902_s06dfa4f09bfd4e0d9826a80e6a91b0a6)。 - 当前会使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索引，会存在阻塞的情况。 - GSI回表为跨数据节点回表，当优化器优先生成下推计划时，无法生成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} [, ... ] )] [ ILM ADD POLICY ROW STORE COMPRESS { TURBO [ LOW | MEDIUM | HIGH ] UNIT AFTER n { hour | day | month | year } OF NO MODIFICATION| NONE}] [ TABLESPACE tablespace_name ] [ VISIBLE | INVISIBLE ] [ WHERE predicate]; ``` ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/figure/zh-cn_image_0000002793708179.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并发执行，存在一定概率的死锁、报错等情况，可以重新连接客户端进行重试。 - 其他注意事项请参见[CREATE INDEX](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0253.html)章节中的[CONCURRENTLY](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0253.html#ZH-CN_TOPIC_0000002764308654__li1664032114110)。 - **schema_name** 模式的名称。 取值范围：已存在模式名。 - **index_name** 创建的索引名，不能包含模式名，索引的模式与表相同。 取值范围：字符串，要符合[标识符命名规范](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0003.html#ZH-CN_TOPIC_0000002793787739__section14873217506)。 - **table_name** 需要为其创建索引的表的名称，可以使用模式进行具体指定。 取值范围：已存在的表名。 - **USING method** 指定创建索引的方法。 取值范围：ubtree，提供多版本B-Tree索引，索引页面上包含事务信息。 - **column_name** 表中需要创建索引的列的名称（字段名）。 如果索引方式支持多字段索引，可以声明多个字段，对于非分区基表最多可以声明28个字段，对于分区基表最多可以声明27个字段。 - **column_name ( length )** 支持前缀键，具体请参见[CREATE INDEX](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0253.html)章节中的[column_name ( length )](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0253.html#ZH-CN_TOPIC_0000002764308654__li1464834613545)。 前缀索引作为一种表达式索引，在GSI中的约束与表达式键一致。 - **expression** 创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括号中。如果表达式有函数调用的形式，括号可以省略。表达式索引可用于获取对基本数据的某种变形的快速访问。比如，一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。在创建表达式索引时，如果表达式中包含IS NULL子句，则该索引为无效的索引。此时，建议用户尝试创建一个部分索引。 - **COLLATE collation** 指定列的排序规则（该列必须为可排列的数据类型）。如果未指定，则使用默认的排序规则。可以使用"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参数。 取值范围：字符串 默认值：空 - opt_with_pkey 指定当前创建的全局二级索引为主键优化索引。该参数仅支持创建时指定，不支持手动设置或更改。 取值范围：on/off。 默认值：off ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 允许通过opt_with_pkey选项创建主键优化的全局二级索引（下称主键优化GSI）。当表发生更新时，如果更新列不涉及该主键优化GSI的列，则不更新该主键优化GSI，从而提升更新性能。 - 创建/重建主键优化GSI时，要求表上存在生效（USABLE）状态的非GSI主键索引。 - 集群升级未提交时，不允许创建主键优化GSI。 - 无法创建带有主键优化的表达式索引GSI和部分索引GSI。 - 当目标表上存在主键优化GSI时，主键无法直接删除，只能进行级联删除（CASCADE）。 - 当目标表上存在主键优化GSI时，失效（UNUSABLE）主键索引，会将所有主键优化GSI级联失效。 - 主键优化状态（opt_with_pkey）仅支持在创建时指定，无法更改。 - new_partbucket_gsi 指定当前在分区hashbucket表上创建的全局二级索引是否使用新的数据结构。该参数在创建全局二级索引时内部自动指定。 取值范围：on 默认值：on ![](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 在首次升级至支持new_partbucket_gsi选项的版本的升级观察期，new_partbucket_gsi选项不支持指定。 - 当尝试在非全局二级索引或非分区hashbucket表上的全局二级索引上指定new_partbucket_gsi选项时，将报错。 - REINDEX不会影响new_partbucket_gsi选项的值。 - new_partbucket_gsi选项的值不支持使用ALTER语法进行更改。 - **\[ ILM ADD POLICY ROW STORE COMPRESS { TURBO \[ LOW \| MEDIUM \| HIGH \] UNIT AFTER n { hour \| day \| month \| year } OF NO MODIFICATION \| NONE}\]** - 创建GSI索引时，可以调用ILM ADD POLICY添加跨页透明压缩策略。比如： ``` CREATE GLOBAL INDEX gsi_idx on t1(a) ILM ADD POLICY ROW STORE COMPRESS TURBO HIGH UNIT AFTER 3 HOUR OF NO MODIFICATION; ``` 表示创建gsi_idx索引并添加跨页透明压缩策略：策略级别为high，冷热管理时间间隔为3小时。 其中，AFTER n { hour \| day \| month \| year } OF NO MODIFICATION表示n小时/天/月/年没有修改的压缩组。 - 创建索引时，可以调用ILM ADD POLICY ROW STORE COMPRESS NONE显式指定索引不添加跨页透明压缩策略。 - **TABLESPACE tablespace_name** 指定索引的表空间，如果没有声明则使用默认的表空间。 取值范围：已存在的表空间名。 - **VISIBLE** 设置索引状态为可见，此选项为默认的。 ![](https://support.huaweicloud.com/distributed-ref-v10-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-ref-v10-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-ref-v10-gaussdb/gaussdb-08-0193.html)，[CREATE INDEX](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0253.html)，[DROP INDEX](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0303.html)