GaussDB数据库-创建和管理索引

背景信息

索引可以提高数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。需要分析应用程序的业务处理、数据使用、经常被用作查询的条件或者被要求排序的字段来确定是否建立索引。

索引建立在数据库表中的某些列上。因此，在创建索引时，应该仔细考虑在哪些列上创建索引。

• 在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
• 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。
• 在经常使用连接的列上创建索引，可以加快连接的速度。
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 在经常使用WHERE子句的列上创建索引，加快条件的判断速度。
• 为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。

  说明：

  • 索引创建成功后，系统会自动判断何时引用索引。当系统认为使用索引比顺序扫描更快时，就会使用索引。
  • 索引创建成功后，必须和表保持同步以保证能够准确地找到新数据，这样就增加了数据操作的负荷。因此请定期删除无用的索引。

• 在开启逻辑复制的场景下，如需创建包含系统列的主键索引，必须将该表的REPLICA IDENTITY属性设置为FULL或是使用USING INDEX指定不包含系统列的、唯一的、非局部的、不可延迟的、仅包括标记为NOT NULL的列的索引。

操作步骤

创建分区表的步骤请参考创建和管理分区表。

• 创建索引
  • 创建分区表索引tpcds_web_returns_p2_index1，不指定索引分区的名称。

    gaussdb=# CREATE INDEX tpcds_web_returns_p2_index1 ON tpcds.web_returns_p2 (ca_address_id) LOCAL;

    当结果显示为如下信息，则表示创建成功。

    CREATE INDEX

  • 创建分区表索引tpcds_web_returns_p2_index2，并指定索引分区的名称。

    gaussdb=# CREATE INDEX tpcds_web_returns_p2_index2 ON tpcds.web_returns_p2 (ca_address_sk) LOCAL ( PARTITION web_returns_p2_P1_index, PARTITION web_returns_p2_P2_index TABLESPACE example3, PARTITION web_returns_p2_P3_index TABLESPACE example4, PARTITION web_returns_p2_P4_index, PARTITION web_returns_p2_P5_index, PARTITION web_returns_p2_P6_index, PARTITION web_returns_p2_P7_index, PARTITION web_returns_p2_P8_index ) TABLESPACE example2;

    当结果显示为如下信息，则表示创建成功。

    CREATE INDEX

• 修改索引分区的表空间
  • 修改索引分区web_returns_p2_P2_index的表空间为example1。

    gaussdb=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P2_index TABLESPACE example1;

    当结果显示为如下信息，则表示修改成功。

    ALTER INDEX

  • 修改索引分区web_returns_p2_P3_index的表空间为example2。

    gaussdb=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P3_index TABLESPACE example2;

    当结果显示为如下信息，则表示修改成功。

    ALTER INDEX

• 重命名索引分区

  执行如下命令对索引分区web_returns_p2_P8_index重命名web_returns_p2_P8_index_new。

  gaussdb=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 RENAME PARTITION web_returns_p2_P8_index TO web_returns_p2_P8_index_new;

  当结果显示为如下信息，则表示重命名成功。

  ALTER INDEX

• 查询索引
  • 执行如下命令查询系统和用户定义的所有索引。

    gaussdb=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i';

  • 执行如下命令查询指定索引的信息。

    gaussdb=# \di+ tpcds.tpcds_web_returns_p2_index2

• 删除索引

  gaussdb=# DROP INDEX tpcds.tpcds_web_returns_p2_index1; gaussdb=# DROP INDEX tpcds.tpcds_web_returns_p2_index2;

  当结果显示为如下信息，则表示删除成功。

  DROP INDEX

GaussDB Kernel支持4种创建索引的方式请参见表1。

说明：

• 索引创建成功后，系统会自动判断何时引用索引。当系统认为使用索引比顺序扫描更快时，就会使用索引。

• 索引创建成功后，必须和表保持同步以保证能够准确地找到新数据，这样就增加了数据操作的负荷。因此请定期删除无用的索引。

• 创建一个普通表。

  gaussdb=# CREATE TABLE tpcds.customer_address_bak AS TABLE tpcds.customer_address; INSERT 0 0