创建和管理索引

背景信息

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

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

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

  • 为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。

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

操作步骤

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

  • 创建索引

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

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

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

      1. CREATE INDEX

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

      1. postgres=# CREATE INDEX tpcds_web_returns_p2_index2 ON tpcds.web_returns_p2 (ca_address_sk) LOCAL
      2. (
      3.     PARTITION web_returns_p2_P1_index,
      4.     PARTITION web_returns_p2_P2_index TABLESPACE example3,
      5.     PARTITION web_returns_p2_P3_index TABLESPACE example4,
      6.     PARTITION web_returns_p2_P4_index,
      7.     PARTITION web_returns_p2_P5_index,
      8.     PARTITION web_returns_p2_P6_index,
      9.     PARTITION web_returns_p2_P7_index,
      10.     PARTITION web_returns_p2_P8_index
      11. ) TABLESPACE example2;

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

      1. CREATE INDEX

  • 修改索引分区的表空间

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

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

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

      1. ALTER INDEX

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

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

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

      1. ALTER INDEX

  • 重命名索引分区

    执行如下命令对索引分区_web_returns_p2_P8_index_重命名_web_returns_p2_P8_index__new。

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

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

    1. ALTER INDEX

  • 查询索引

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

      1. postgres=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i';

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

      1. postgres=# \di+ tpcds.tpcds_web_returns_p2_index2

  • 删除索引

    1. postgres=# DROP INDEX tpcds.tpcds_web_returns_p2_index1;
    2. postgres=# DROP INDEX tpcds.tpcds_web_returns_p2_index2;

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

    1. DROP INDEX

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

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

表 1 索引方式

索引方式

描述

唯一索引

可用于约束索引属性值的唯一性，或者属性组合值的唯一性。如果一个表声明了唯一约束或者主键，则openGauss自动在组成主键或唯一约束的字段上创建唯一索引（可能是多字段索引），以实现这些约束。目前，openGauss只有B-Tree可以创建唯一索引。

多字段索引

一个索引可以定义在表中的多个属性上。目前，openGauss中的B-Tree支持多字段索引，且最多可在32个字段上创建索引。

部分索引

建立在一个表的子集上的索引，这种索引方式只包含满足条件表达式的元组。

表达式索引

索引建立在一个函数或者从表中一个或多个属性计算出来的表达式上。表达式索引只有在查询时使用与创建时相同的表达式才会起作用。

  • 创建一个普通表。

    1. postgres=# CREATE TABLE tpcds.customer_address_bak AS TABLE tpcds.customer_address;
    2. INSERT 0 0

  • 创建普通索引

    如果对于tpcds.customer_address_bak表，需要经常进行以下查询。

    1. postgres=# SELECT ca_address_sk FROM tpcds.customer_address_bak WHERE ca_address_sk=14888;

    通常，数据库系统需要逐行扫描整个tpcds.customer_address_bak表以寻找所有匹配的元组。如果表tpcds.customer_address_bak的规模很大，但满足WHERE条件的只有少数几个（可能是零个或一个），则这种顺序扫描的性能就比较差。如果让数据库系统在ca_address_sk属性上维护一个索引，用于快速定位匹配的元组，则数据库系统只需要在搜索树上查找少数的几层就可以找到匹配的元组，这将会大大提高数据查询的性能。同样，在数据库中进行更新和删除操作时，索引也可以提升这些操作的性能。

    使用以下命令创建索引。

    1. postgres=# CREATE INDEX index_wr_returned_date_sk ON tpcds.customer_address_bak (ca_address_sk);
    2. CREATE INDEX

  • 创建多字段索引

    假如用户需要经常查询表tpcds.customer_address_bak中ca_address_sk是5050，且ca_street_number小于1000的记录，使用以下命令进行查询。

    1. postgres=# SELECT ca_address_sk,ca_address_id FROM tpcds.customer_address_bak WHERE ca_address_sk = 5050 AND ca_street_number < 1000;

    使用以下命令在字段ca_address_sk和ca_street_number上定义一个多字段索引。

    1. postgres=# CREATE INDEX more_column_index ON tpcds.customer_address_bak(ca_address_sk ,ca_street_number );
    2. CREATE INDEX

  • 创建部分索引

    如果只需要查询ca_address_sk为5050的记录，可以创建部分索引来提升查询效率。

    1. postgres=# CREATE INDEX part_index ON tpcds.customer_address_bak(ca_address_sk) WHERE ca_address_sk = 5050;
    2. CREATE INDEX

  • 创建表达式索引

    假如经常需要查询ca_street_number小于1000的信息，执行如下命令进行查询。

    1. postgres=# SELECT * FROM tpcds.customer_address_bak WHERE trunc(ca_street_number) < 1000;

    可以为上面的查询创建表达式索引：

    1. postgres=# CREATE INDEX para_index ON tpcds.customer_address_bak (trunc(ca_street_number));
    2. CREATE INDEX

  • 删除tpcds.customer_address_bak表。

    1. postgres=# DROP TABLE tpcds.customer_address_bak;
    2. DROP TABLE