创建和管理索引

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

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

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

    操作步骤

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

    • 创建索引

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

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

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

        1.   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_P7_index,
        9.       PARTITION web_returns_p2_P8_index
        10.   ) TABLESPACE example2;

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

      • 创建分区表GLOBAL索引tpcds_web_returns_p2_global_index。

        1. CREATE INDEX tpcds_web_returns_p2_global_index ON tpcds.web_returns_p2 (ca_street_number) GLOBAL;

    • 修改索引分区的表空间

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

        1. 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. ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P3_index TABLESPACE example2;

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

    • 重命名索引分区

      1. 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. SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';

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

        1. \di+ tpcds.tpcds_web_returns_p2_index2

    • 删除索引

      1. openGauss=# DROP INDEX tpcds.tpcds_web_returns_p2_index1;

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

      1. DROP INDEX

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

    说明：

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

    表 1 索引方式

    • 创建一个普通表

      2. INSERT 0 0

    • 创建普通索引

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

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

      使用以下命令创建索引。

      1. openGauss=# 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. 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. openGauss=# CREATE INDEX more_column_index ON tpcds.customer_address_bak(ca_address_sk ,ca_street_number );
      2. CREATE INDEX

    • 创建部分索引

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

      1. openGauss=# 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. SELECT * FROM tpcds.customer_address_bak WHERE trunc(ca_street_number) < 1000;

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

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