# 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索引时不支持创建部分索引。需要注意分区表上创建索引会根据如下规则进行判断：如果创建索引时申明了GLOBAL/LOCAL关键字，则创建对应类型的索引；否则如果创建索引指定分区名，则创建LOCAL索引；否则如果是unique索引，包含非分区键时创建GLOBAL索引，包含全部分区键则创建LOCAL索引；否则默认创建GLOBAL索引。 #### 注意事项 - 索引自身也占用存储空间、消耗计算资源，创建过多的索引将对数据库性能造成负面影响（尤其影响数据导入的性能，建议在数据导入后再建索引）。因此，仅在必要时创建索引。 - 索引定义里的所有函数和操作符都必须是immutable类型的，即它们的结果必须只能依赖于它们的输入参数，而不受任何外部的影响（如另外一个表的内容或者当前时间）。这个限制可以确保该索引的行为是定义良好的。要在一个索引上或WHERE中使用用户定义函数，请把它标记为immutable类型函数。 - 分区表索引分为LOCAL索引与GLOBAL索引，LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。 - 被授予CREATE ANY INDEX权限的用户，可以在public模式和用户模式下创建索引。 - 如果表达式索引中调用的是用户自定义函数，按照表Owner权限执行表达式索引函数。 - 所有用户都可以在自己的表上创建表达式索引。非初始用户不能在初始用户的表上创建表达式索引，初始用户可以对所有表创建表达式索引。三权分立关闭时，系统管理员可以在非初始用户表上创建表达式索引。三权分立开的时候，系统管理员可以在普通用户表上创建表达式索引，不可以在初始用户、审计管理员、安全管理员上表上创建表达式索引。 - 不支持XML类型数据作为普通索引、UNIQUE索引、GLOBAL索引、LOCAL索引、部分索引。 - 在线创建索引的类型只支持btree索引和ubtree索引。索引创建形式只支持非分区表普通索引及分区表GLOBAL索引、LOCAL索引，不支持PCR ubtree索引、二级分区与GSI。在线并行创建索引只支持Astore及Ustore的普通索引、GLOBAL索引、LOCAL索引。 - UGIN索引目前仅支持Ustore存储引擎，仅支持单列索引，其他场景说明请参见[ugin]。 - CREATE INDEX创建索引可能会改变表的访问方式从而导致查询执行计划改变。 - 当创建索引的SQL语句过长（长度大于等于5250字符）时，会上报一条WARNING，建议缩短创建索引语句的长度。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 创建索引的SQL语句过长时，可能会在备份恢复索引时发生失败。由于备份恢复创建索引语句由gs_dump导出，gs_dump在导出索引定义时会对函数名、条件、列名进行添加引号、括号和列的类型的操作，原SQL语句会变得更长，进而导致该语句对应的pg_index元组不能存到一个页面上，造成创建索引失败。 #### 语法格式 - 在表上创建索引。 ``` CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ IF NOT EXISTS ] [ [schema_name.] index_name ] ON table_name [ USING method ] ({ { column_name [ ( length ) ] | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) [ INCLUDE ( column_name [, ...] ) ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ TABLESPACE tablespace_name ] { [ COMMENT 'string' ] [ ... ] } [ { VISIBLE | INVISIBLE } ] [ WHERE predicate ]; ``` - 在分区表上创建索引。 ``` CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.] index_name ] ON table_name [ USING method ] ( { { column_name [ ( length ) ] | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] } [, ...] ) [ LOCAL [ ( { PARTITION index_partition_name [ FOR { partition_name | ( partition_value [, ...] ) } ] [ TABLESPACE index_partition_tablespace ] [ ( [SUBPARTITION index_subpartition_name] [ FOR { partition_name | ( partition_value [, ...] ) } ] [ TABLESPACE index_partition_tablespace ] [, ...] ) ] [, ...] } ) ] | GLOBAL ] [ INCLUDE ( column_name [, ...] ) ] [ WITH ( { storage_parameter = value } [, ...] ) ] [ TABLESPACE tablespace_name ] { [ COMMENT 'string' ] [ ... ] } [ { VISIBLE | INVISIBLE } ]; ``` #### 参数说明 - **UNIQUE** 创建唯一性索引，每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时，将导致一个错误。 目前只有B-tree及UBtree索引支持唯一索引。 - **CONCURRENTLY** 以不阻塞DML的方式创建索引（加ShareUpdateExclusiveLock锁）。创建索引时，一般会阻塞其他语句对该索引所依赖表的访问。指定此关键字，可以实现创建过程中不阻塞DML。 - 此选项只能指定一个索引的名称。 - 普通CREATE INDEX命令可以在事务内执行，但是CREATE INDEX CONCURRENTLY不可以在事务内执行。 - 对于临时表，支持使用CONCURRENTLY关键字创建索引，但是实际创建过程中，采用的是阻塞式的创建方式，因为没有其他会话会并发访问临时表，并且阻塞式创建成本更低。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 创建索引时指定此关键字，Astore需要执行先后两次对该表的全表扫描来完成build，第一次扫描的时候创建索引，不阻塞读写操作；第二次扫描的时候合并更新第一次扫描到目前为止发生的变更。Ustore只需全表扫描一次，在全表扫描过程中并发DML产生的数据会被插入到以"index_oid_cctmp"命名的临时表中，扫描结束后将临时表中的数据合并到主索引中并删除临时表，完成索引创建。可使用GUC参数delete_cctmp_table控制在线创建索引结束后是否删除临时表：该参数默认为on，即默认删除临时表；设置为off时，保留临时表。 - Astore由于需要执行两次对表的扫描和build，而且必须等待现有的所有可能对该表执行修改的事务结束。这意味着该索引的创建比正常耗时更长，同时因此带来的CPU和I/O消耗对其他业务也会造成影响。Ustore虽只需全表扫描一次来完成索引创建，但上述消耗同样存在。 - 在线创建索引过程中可能会发生异常（例如用户手动取消、唯一索引键值重复、资源不足、启动线程失败、锁超时等场景），导致在线创建索引失败，这种情况可能会留下not valid状态的索引，为避免占用系统资源，会自动将其清理（清理失败索引前，需要先等该表上的事务结束，如果存在长事务，可能会出现一直等待的情况，用户取消该线程会残留失败索引。特别地，如果用户取消了在线创建索引线程，首先进入失败索引清理流程，如果用户再次取消，则会结束失败索引清理流程，残留下失败索引）。但对于严重错误，比如FATAL/PANIC/数据库故障等场景，需要用户手动清理索引及临时表。在线创建索引失败且自动残留清理失效的情况下，会残留下not valid的索引，这些残留索引可能是ready或者not ready状态（取决于在线创建索引是在哪一个阶段失败的，比如在第一阶段失败索引是not ready状态，在第三阶段失败索引是ready状态）。如果残留索引是ready状态，DML仍会维护此残留索引，且维护此索引过程中可能会产生其他错误（比如索引列大小超过最大值，违反唯一索引约束等）。为了避免残留索引占用系统资源和产生用户预期外报错，需要用户尽快手动将其删除。 - Astore由于在第二次扫描之后，索引构建必须等待任何持有早于第二次扫描拿的快照的事务终止，而且建索引时加的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）或存在资源争抢的情况时，可能会超过此数值。如果在线创建索引期间存在长事务，还要加上长事务运行时间。在Astore及Ustore模式下，可以使用并行创建索引来缩短创建索引时间；在线并行创建索引性能随着并行工作线程数量增加而提升到一定值后稳定，相比串行创建索引性能一般可提升30%左右。建议在业务低谷期进行在线创建索引，以避免对业务造成较大影响。同时避免在线创建索引期间执行长事务。虽然在线创建索引在一定程度上提供了业务不中断的能力，但仍然需要谨慎实施。 - 在线创建唯一索引（CREATE UNIQUE INDEX CONCURRENTLY）时，会扫表检查表上数据是否满足唯一性，如果在线创建索引扫表过程中表上数据不满足唯一性，在线创建索引就会报错退出。注意在线创建索引过程中，表上数据是否满足唯一性可能会发生变化，是否失败取决于扫表过程中是否扫到了重复的数据。考虑以下情况：（1）扫表过程中插入了元组A和元组B，且它们的索引列相同，在线创建索引扫表时同时扫到了A和B，则可能会报违反唯一性，但是如果后续B被删除了，则可能会出现在线创建索引过程中报错违反唯一性，但在线创建索引失败退出后查找表上数据发现满足唯一性的情况。（2）在线创建索引过程中，先插入元组A，后删除A，再插入A，使用SnapshotNow扫表，则A可能会被扫表两次，可能会违反唯一性约束。在线创建索引过程中Astore使用SnapshotMVCC扫表，Ustore使用SnapshotNow扫表。 - **IF NOT EXISTS** 如果指定IF NOT EXISTS关键字，创建索引前会在当前schema中查找是否已有名字相同的relation。若已有同名relation存在，则不会新建，返回NOTICE提示。未指定IF NOT EXISTS关键字时，若schema中存在同名relation，返回ERROR告警。 - **schema_name** 模式的名称。 取值范围：已存在模式名。 - **index_name** 要创建的索引名，索引的模式与表相同。 取值范围：字符串，要符合[标识符命名规范](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0330.html#ZH-CN_TOPIC_0000002558947985__zh-cn_topic_0000002527058134_section1257443811315)。 - **table_name** 需要为其创建索引的表的名称，可以用模式修饰。 取值范围：已存在的表名。 - **USING method** 指定创建索引的方法。 取值范围： - btree：btree索引使用一种类似于B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。btree适合支持比较查询以及范围查询。 - ubtree：仅供ustore表使用的多版本btree索引，索引页面上包含事务信息，并能自主回收页面。ubtree索引默认开启insertpt功能。 - ugin：倒排索引，ugin索引是仅供ustore表使用的gin索引。 行存表（ASTORE存储引擎）支持的索引类型：btree（行存表缺省值）。行存表（USTORE存储引擎）支持的索引类型：ubtree、ugin。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/notice_3.0-zh-cn.png) btree与ubtree是与表的存储类型ASTORE/USTORE强相关，在创建索引时指定索引类型与主表不对应时会自动进行转换。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) ugin索引说明如下： - 只支持一种opclass：ugin_trgm_ops。 - ugin_trgm_ops不是默认opclass。 - ugin_trgm_ops支持 text、varchar、nvarchar2、clob数据类型的索引键，以及这四种类型的别名数据类型，不支持char(n)类型。 - 指定了ugin_trgm_ops的索引列数据的最大字节长度为238609291。 - 指定了ugin_trgm_ops的索引键，其字符序不允许是B模式字符序，否则创建索引时将报错。B模式字符序列表参考[COLLATE collation](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0573.html#ZH-CN_TOPIC_0000002527988262__zh-cn_topic_0000002558058189_zh-cn_topic_0283137629_zh-cn_topic_0237122117_zh-cn_topic_0059778169_l506a9e4b62be4f6880be407386b4032b)。 - 指定了ugin_trgm_ops的索引键，只有LIKE条件匹配时可以进行索引扫描。 - ugin_trgm_ops提取索引键时将每3字符作为一个分词，若索引扫描条件提取的分词少于3个字符时，索引扫描时无法定位到具体的索引键，会扫描所有的索引键。例如："col LIKE '%ab%'"。 - 索引键数量不得超过1，索引键不支持NULLS FIRST/LAST语法，不支持INCLUDE语法。 - 只能通过Bitmap Index Scan执行计划扫描，不支持Index Scan执行计划。 - 极致RTO的备机查询时，不会选中ugin索引扫描。 - GUC参数gin_fuzzy_search_limit为非0值时，ugin索引扫描时将报错。 - 支持以下WITH选项：storage_type、gin_pending_list_limit、fastupdate。 - 暂时不支持关闭增量缓存区（pending list区），所以fastupdate仅支持设置为ON，设置为OFF会报错。 - 不支持作为段页式表、本地临时表、全局临时表、unlog表、加密表的索引，不支持作为分区表的GLOBAL索引。 - 不支持作为主键，不支持作为唯一键，不支持作为虚拟索引，不支持加密。 - 不支持在线（CONCURRENTLY）重建，不支持在线（CONCURRENTLY）创建，不支持在线（CONCURRENTLY）删除。 - 不支持并行功能，如并行创建、并行扫描等。 - 不支持在sql_compatibility = 'M'的数据库中创建和使用。 - **column_name** 表中需要创建索引的列的名称（字段名）。 如果索引方式支持多字段索引，可以声明多个字段。全局索引最多可以声明31个字段，其他索引最多可以声明32个字段。 - **column_name ( length )** 创建一个基于该表一个字段的前缀键索引，column_name为前缀键的字段名，length为前缀长度。 前缀键将取指定字段数据的前缀作为索引键值，可以减少索引占用的存储空间。含有前缀键字段的部分过滤条件和连接条件可以使用索引。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 前缀键支持的索引方法：btree、ubtree。 - 前缀键的字段的数据类型必须是二进制类型或字符类型（不包括特殊字符类型）。 - 前缀长度必须是不超过2676的正整数，并且不能超过字段的最大长度。对于二进制类型，前缀长度以字节数为单位。对于非二进制字符类型，前缀长度以字符数为单位。键值的实际长度受内部页面限制，若字段中含有多字节字符、或者一个索引上有多个键，索引行长度可能会超限，导致报错，设定较长的前缀长度时请考虑此情况。 - CREATE INDEX语法中，不支持以下关键字作为前缀键的字段名称：COALESCE、EXTRACT、GREATEST、LEAST、LNNVL、NULLIF、NVL、NVL2、OVERLAY、POSITION、REGEXP_LIKE、SUBSTRING、TIMESTAMPDIFF、TREAT、TRIM、XMLCONCAT、XMLELEMENT、XMLEXISTS、XMLFOREST、XMLPARSE、XMLPI、XMLROOT、XMLSERIALIZE。 - 前缀键属于一种特殊的表达式键，部分未说明的约束和限制，与表达式键一致，请参考表达式索引的说明。 - **expression** 创建一个基于该表的一个或多个字段的表达式索引，通常必须写在圆括弧中。如果表达式有函数调用的形式，圆括弧可以省略。 表达式索引可用于获取对基本数据的某种变形的快速访问。比如，一个在upper(col)上的函数索引将允许WHERE upper(col) = 'JIM'子句使用索引。 在创建表达式索引时，如果表达式中包含IS NULL子句，则这种索引是无效的。此时，建议用户尝试创建一个部分索引。 - **COLLATE collation** COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用"SELECT \* FROM pg_collation"命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。 - **opclass** 操作符类的名称。对于索引的每一列可以指定一个操作符类，操作符类标识了索引那一列的使用的操作符。例如一个btree索引在一个四字节整数上可以使用int4_ops；这个操作符类包括四字节整数的比较函数。实际上对于列上的数据类型默认的操作符类是足够用的。操作符类主要用于一些有多种排序的数据。例如，用户想按照绝对值或者实数部分排序一个复数。能通过定义两个操作符类然后在建立索引时选择合适的类。另外，如果包含字符串类型（varchar、varchar2、text等）的索引的COLLATE的值不是C或者POSIX，但希望索引能够支持前缀匹配，则需要指定varchar_pattern_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索引访问方式支持该特性。非键列会被保存在与堆元组对应的索引叶子元组中，不会包含在索引上层页面的元组中。 - **PARTITION子句** 指定一级分区分区信息语法如下： ``` PARTITION index_partition_name [ FOR { partition_name | ( partition_value [, ...] ) } ] [ TABLESPACE index_partition_tablespace ] ``` 指定二级分区分区信息语法如下： ``` SUBPARTITION index_subpartition_name [ FOR { partition_name | ( partition_value [, ...] ) } ] [ TABLESPACE index_partition_tablespace ] ``` 如果分区子句指定了for partition_name或partition_value，此时创建的分区索引带有分类索引属性；如果未指定for partition_name或partition_value，分区子句个数必须和目标表分区个数一一对应。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 分类索引是分区表local索引的一种，即只在单个分区上创建索引分区的local索引，与普通local索引相比，普通local索引需要在所有表分区上创建对应的索引分区。 - 仅支持在单个分区上创建分类索引。 - 分类索引仅支持btree和ubtree索引。 - 分类索引不支持UNIQUE属性。 - 创建的分类索引会带有sparsely_partitioned=true选项，需要注意该选项不可以通过CREATE TABLE或ALTER TABLE语句将非分类索引显式指定为分类索引。如果创建的索引为分类索引，即使声明sparsely_partitioned=false也会被忽略。 - 从505.0.0之前版本升级到505.0.0及更高版本时，在升级观察期期间，不支持创建分类索引。 - 以for partition_value方式创建分类索引时，若指定的分区不存在，且分区表支持自动扩展，则会创建一个新的分区，在新分区上创建分类索引。创建新分区采用自治事务的方案，主事务异常回滚后，新建的分区依然可能存在。 - **PARTITION index_partition_name** 索引分区的名称。 取值范围：字符串，要符合[标识符命名规范](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0330.html#ZH-CN_TOPIC_0000002558947985__zh-cn_topic_0000002527058134_section1257443811315)。 - **SUBPARTITION index_subpartition_name** 索引二级分区的名称。 取值范围：字符串，要符合[标识符命名规范](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0330.html#ZH-CN_TOPIC_0000002558947985__zh-cn_topic_0000002527058134_section1257443811315)。 - **FOR partition_name** 指定的目标分区名。如果指定的分区不存在则报错。 - **FOR (partition_value \[, ...\])** 指定的分区键值。一级分区键值需要放入一级分区的分区键值FOR列表中，二级分区键值需要放入二级分区的分区键值FOR列表中。如果存在多列分区键，则需要指定多个partition_value。 - **TABLESPACE index_partition_tablespace** 索引分区的表空间。 取值范围：如果没有声明，将使用分区表索引的表空间index_tablespace。 - **WITH ( {storage_parameter = value} \[, ... \] )** 指定索引方法的存储参数。 取值范围： Psort之外的索引都支持FILLFACTOR参数。只有UBtree索引支持INDEXSPLIT参数。只有非分区表的BTREE索引支持DEDUPLICATION参数。只有UBTREE索引支持INDEX_TXNTYPE参数。 - STORAGE_TYPE 表示索引所在的表的存储引擎类型，当索引指定的storage_type与索引类型冲突时，会自动修改为正确的存储类型。仅支持BTREE、UBTREE。该参数设置成功后就不再支持修改。 取值范围：USTORE，表示索引所在的表为Inplace-Update存储引擎。ASTORE，表示索引所在的表为Append-Only存储引擎。 默认值：Ustore表创建的索引默认为USTORE，Astore表创建的索引默认为ASTORE。 - FILLFACTOR 索引的填充因子（fillfactor）是一个介于10和100之间的百分数。对于大并发插入且键值范围比较密集的场景，插入时同一个索引页面的竞争比较大，选择较小的填充因子更加合适。 取值范围：10\~100 - FASTUPDATE 指定UGIN索引是否使用快速更新。暂时不支持关闭增量缓存区（pending list区），所以fastupdate仅支持设置为ON，设置为OFF会报错。 取值范围：ON，索引使用快速更新。 默认值：ON - GIN_PENDING_LIST_LIMIT 设置UGIN索引pending list容量的最大值。 取值范围：64\~2147483647，单位KB。 默认值：gin_pending_list_limit的默认取决于GUC参数gin_pending_list_limit的值（默认为4MB） - INDEXSPLIT UBTREE索引选择采取哪种分裂策略。其中DEFAULT策略指的是与BTREE相同的分裂策略。INSERTPT策略能在某些场景下显著降低索引空间占用。 取值范围：INSERTPT，DEFAULT 默认值：INSERTPT - INDEX_TXNTYPE UBTree索引类型（只有UBTree索引支持INDEX_TXNTYPE），当该值取值为PCR时，可支持通过UBTree进行闪回查询。PCR版本UBTree索引当前不支持在线创建索引、全局二级索引、极致RTO回放和备机读的功能。当不指定index_txntype时，具体创建哪种类型的索引通过GUC参数index_txntype进行控制。INDEX_TXNTYPE不支持ALTER INDEX INDEX_NAME SET (INDEX_TXNTYPE=PCR或RCR)进行修改。 类型：字符串（不区分大小写） 取值范围： RCR, PCR 默认值：RCR 举例： ``` CREATE UNIQUE INDEX t2_b_pkey ON t(b) WITH(index_txntype='pcr'); ``` - ACTIVE_PAGES 表示索引的页面数量，可能比实际的物理文件页面少，可以用于优化器调优。目前只对ustore的分区表local索引生效，且会被vacuum、analyze更新（包括auto vacuum）。不建议用户手动设置该参数。 - DEDUPLICATION 索引参数，设置索引是否对键值重复的元组进行去重压缩。在重复键值的索引较多时，开启参数可以有效降低索引占用空间。对主键索引和唯一索引不生效。非唯一索引且索引键值重复度很低或者唯一的场景，开启参数会使索引插入性能小幅度劣化。暂不支持分区表的local/global索引。 取值范围：布尔值，默认取GUC参数中enable_default_index_deduplication的值（默认为off）。 - stat_state 标识该索引的统计信息是否被锁定，如果被锁定了，该索引的统计信息无法更新。 取值范围：locked、unlock。 默认值：unlock。 - 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参数，该参数不支持主动设置或更改。 取值范围：字符串 默认值：空 - LPI_PARALLEL_METHOD 索引参数，设置分区表LOCAL索引并行创建的方式。 类型：字符串。 取值范围：PAGE、PARTITION、AUTO。PAGE为页面级并行创建索引，开启多个子线程执行数据的扫描和排序，每个子线程一次处理一个数据页面，扫描排序后，在主线程串行合并排序结果并将元组插入到索引中；PARTITION为分区级并行创建索引，开启多个子线程，每个子线程负责一个分区中数据的扫描、排序、索引插入；AUTO会根据分区表统计信息来预估页面级和分区级两种并行创建索引方式的代价，并选择代价较小的并行创建方式（统计信息可能会和实际数据情况有误差而导致计算结果不准确）。 默认值：PAGE。 设置建议：当分区表数据在各分区分布均匀时，建议指定该参数为PARTITION。该参数仅支持Astore分区表的BTREE LOCAL索引，不支持分区表GLOBAL索引、非分区表索引、段页式表索引、在线创建索引。 举例： ``` CREATE INDEX idx ON tbl(col) WITH (lpi_parallel_method = 'partition'); ``` - **TABLESPACE tablespace_name** 指定索引的表空间，如果没有声明则使用默认的表空间。 取值范围：已存在的表空间名。 - **WHERE predicate** 创建一个部分索引。部分索引是一个只包含表的一部分记录的索引，通常是该表中比其他部分数据更有用的部分。例如，有一个表，表里包含已记账和未记账的订单，未记账的订单只占表的一小部分而且这部分是最常用的部分，此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。 取值范围：predicate表达式只能引用表的字段，它可以使用所有字段，而不仅是被索引的字段。目前，子查询和聚集表达式不能出现在WHERE子句里。不建议使用int等数值类型作为predicate，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。 对于分区表索引，当创建索引带GLOBAL关键字，或者最终创建的索引类型为GLOBAL索引时，不支持带WHERE子句创建索引。 - **COMMENT 'string'** COMMENT 'string'表示给索引添加注释。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/notice_3.0-zh-cn.png) - 该子句仅在B模式数据库下（即sql_compatibility = 'B'）有效。 - 索引级注释支持的最大长度为1024字符。 - **VISIBLE** 设置索引状态为可见，此选项为默认的。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - VISIBLE仅支持在A模式数据库下（即sql_compatibility = 'A'）设置，在其他数据库兼容模式下不支持。 - 当disable_keyword_options参数设置为"visible"时，此关键字不支持使用。 - 升级未提交阶段，不支持使用此关键字。 - **INVISIBLE** 设置索引状态为不可见。 ![](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - INVISIBLE仅支持在A模式数据库下（即sql_compatibility = 'A'）设置，在其他数据库兼容模式下不支持。 - 当disable_keyword_options参数设置为"invisible"时，此关键字不支持使用。 - 升级未提交阶段，不支持使用此关键字。 #### 示例 - **普通索引** ``` --创建tbl_test1表。 gaussdb=# CREATE TABLE tbl_test1( id int, --用户id name varchar(50), --用户姓名 postcode char(6) --邮编 ); --创建表空间tbs_index1。 gaussdb=# CREATE TABLESPACE tbs_index1 RELATIVE LOCATION 'test_tablespace/tbs_index1'; --为表tbl_test1创建索引idx_test1指定表空间。 gaussdb=# CREATE INDEX idx_test1 ON tbl_test1(name) TABLESPACE tbs_index1; --查询索引idx_test1信息。 gaussdb=# SELECT indexname,tablename,tablespace FROM pg_indexes WHERE indexname = 'idx_test1'; indexname | tablename | tablespace -----------+-----------+------------ idx_test1 | tbl_test1 | tbs_index1 (1 row) --删除索引。 gaussdb=# DROP INDEX idx_test1; --删除表空间。 gaussdb=# DROP TABLESPACE tbs_index1; ``` - **唯一索引** ``` --为表tbl_test1创建唯一索引idx_test2。 gaussdb=# CREATE UNIQUE INDEX idx_test2 ON tbl_test1(id); --查询索引信息。 gaussdb=# \d tbl_test1 Table "public.tbl_test1" Column | Type | Modifiers ----------+-----------------------+----------- id | integer | name | character varying(50) | postcode | character(6) | Indexes: "idx_test2" UNIQUE, btree (id) TABLESPACE pg_default --删除索引。 gaussdb=# DROP INDEX idx_test2; ``` - **表达式索引** ``` --为表tbl_test1创建一个表达式索引。 gaussdb=# CREATE INDEX idx_test3 ON tbl_test1(substr(postcode,2)); --查询索引信息。 gaussdb=# \d tbl_test1 Table "public.tbl_test1" Column | Type | Modifiers ----------+-----------------------+----------- id | integer | name | character varying(50) | postcode | character(7) | Indexes: "idx_test3" btree (substr(postcode::text, 2)) TABLESPACE pg_default --删除索引。 gaussdb=# DROP INDEX idx_test3; ``` - **部分索引** ``` --为表tbl_test1中id不为为空的数据建立索引。 gaussdb=# CREATE INDEX idx_test4 ON tbl_test1(id) WHERE id IS NOT NULL; --删除索引。 gaussdb=# DROP INDEX idx_test4; --删除表。 gaussdb=# DROP TABLE tbl_test1; ``` - **分区索引** ``` --建表。 gaussdb=# CREATE TABLE student(id int, name varchar(20)) PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN (MAXVALUE) ); --创建LOCAL分区索引不指定索引分区的名称。 gaussdb=# CREATE INDEX idx_student1 ON student(id) LOCAL; --查看索引分区信息，发现LOCAL索引分区数和表的分区数一致。 gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student1'::regclass; relname ------------- p1_id_idx pmax_id_idx (2 rows) --删除LOCAL分区索引。 gaussdb=# DROP INDEX idx_student1; --创建GLOBAL索引。 gaussdb=# CREATE INDEX idx_student2 ON student(name) GLOBAL; --查看索引分区信息，发现GLOBAL索引分区数和表的分区数不一致。 gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student2'::regclass; relname --------- (0 rows) --删除GLOBAL分区索引。 gaussdb=# DROP INDEX idx_student2; --创建LOCAL表达式索引，不指定索引分区的名称。 gaussdb=# CREATE INDEX idx_student3 ON student(lower(name)) LOCAL; --查看索引分区信息，发现LOCAL索引分区数和表的分区数一致。 gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student3'::regclass; relname ------------- p1_id_idx pmax_id_idx (2 rows) --删除LOCAL分区表达式索引。 gaussdb=# DROP INDEX idx_student3; --创建GLOBAL表达式索引。 gaussdb=# CREATE INDEX idx_student4 ON student(lower(name)) GLOBAL; --查看索引分区信息，GLOBAL表达式索引分区数和表的分区数不一致。 gaussdb=# SELECT relname FROM pg_partition WHERE parentid = 'idx_student4'::regclass; relname --------- (0 rows) --删除GLOBAL分区表达式索引。 gaussdb=# DROP INDEX idx_student4; --删除表。 gaussdb=# DROP TABLE student; ``` - **UGIN索引** ``` --建表。 gaussdb=# CREATE TABLE test_trgm(t varchar(100)) WITH (STORAGE_TYPE=USTORE); --插入数据。 gaussdb=# INSERT INTO test_trgm VALUES('qwertyu0001'); INSERT INTO test_trgm VALUES('qwertyu0099'); INSERT INTO test_trgm VALUES('2024新年快乐7days'); --创建ugin索引。 gaussdb=# create index trgm_idx on test_trgm using ugin (t ugin_trgm_ops); --like查询语句。 gaussdb=# select * from test_trgm where t like '%24新%'; ``` #### 相关链接 [ALTER INDEX](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0490.html#ZH-CN_TOPIC_0000002558948091)，[DROP INDEX](https://support.huaweicloud.com/centralized-devg-v8-gaussdb/gaussdb-42-0607.html#ZH-CN_TOPIC_0000002558948147) #### 优化建议 - create index 约束限制： - 普通表的索引支持最大列数为32列；分区表的GLOBAL索引支持最大列数为31列。 - 单个索引大小不能超过索引页面大小（8k），其中B-tree、UBtree不能超过页面大小的三分之一。 - 分区表创建GLOBAL索引时，存在以下约束条件： - 不支持表达式索引、部分索引。 - 仅支持Btree索引。 - 在相同属性列上，分区LOCAL索引与GLOBAL索引不能共存。 - 如果ALTER语句不带有UPDATE GLOBAL INDEX，那么原有的GLOBAL索引将失效，查询时将使用其他索引进行查询；如果ALTER语句带有UPDATE GLOBAL INDEX，原有的GLOBAL索引仍然有效，并且索引功能正确。