创建和管理索引

背景信息

索引可以提高数据访问速度，但会增加插入、更新和删除操作的处理时间。因此，在创建索引前，需要仔细分析应用程序的业务处理、数据使用情况、以及经常用于查询或排序的字段，以确定是否建立索引及索引应建立在哪些字段上。

在创建索引时，应考虑在哪些列上创建索引，可参考以下原则：

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

注意事项

索引创建成功后，系统会自动判断何时引用索引。当系统认为使用索引比顺序扫描更快时，就会使用索引。
索引创建成功后，必须和表保持同步以保证能够准确地找到新数据，这样就增加了数据操作的负荷。因此请定期删除无用的索引。
分区表索引分为 LOCAL 索引与 GLOBAL 索引，一个 LOCAL 索引对应一个具体分区，而 GLOBAL 索引则对应整个分区表。
表字段大于等于 2074 字节不支持创建 BTree 索引，建议使用 HASH 或 GIN 索引。
在开启逻辑复制的场景下，如需创建包含系统列的主键索引，必须将该表的REPLICA IDENTITY 属性设置为 FULL 或使用 USING INDEX 指定不包含系统列的、唯一的、非局部的、不可延迟的、仅包括标记为 NOT NULL 的列的索引。

功能描述

不同的索引方式与索引类型适用于不同的场景。

索引方式

VexDB 支持的索引方式如下表所示：

表1 索引方式

索引方式	描述
BTree	使用一种类似于 B+ 树的结构来存储数据的键值，通过这种结构能够快速的查找索引。适合比较查询以及查询范围。
GiST	适用于几何和地理等多维数据类型和集合数据类型。支持的数据类型为： box、point、polygon、circle、tsvector、tsquery、range。
Hash	使用 Hash 函数对索引的关键字进行散列。仅支持等值比较。适用于索引列较长的场景使用。
Gin	倒排索引，可以处理包含多个键的值。支持的数据类型为：array、json、tsvector、tsquery。
Psort	是针对列存表进行局部排序的索引。
UBTree	多版本 BTree 索引，适用于 Ustore 表。

索引类型

VexDB 支持的索引类型如下表所示：

表2 索引类型

索引类型	描述
唯一索引	可用于约束索引属性值的唯一性，或者属性组合值的唯一性。如果一个表声明了唯一约束或者主键，则 VexDB 自动在组成主键或唯一约束的字段上创建唯一索引（可能是多字段索引），以实现这些约束。目前，VexDB 只有 BTree 可以创建唯一索引。
多字段索引	一个索引可以定义在表中的多个属性上，在一些场合也被称为联合索引。目前，VexDB 中的 BTree 支持多字段索引，且最多可在32个字段上创建索引（全局分区索引最多支持31个字段）。
部分索引	建立在一个表的子集上的索引，这种索引方式只包含满足条件表达式的元组。
表达式索引	索引建立在一个函数或者从表中一个或多个属性计算出来的表达式上。表达式索引只有在查询时使用与创建时相同的表达式才会起作用。
前缀索引	取指定字段数据的前缀作为索引键值。对于 MySQL 兼容模式，支持 column(N) 形式的前缀索引。对于 Oracle 兼容模式，支持 substr(col,1,offset) 形式的前缀索引。

创建索引

使用 CREATE INDEX 语句创建索引。

语法格式

创建索引的基础语法如下：

CREATE INDEX index_name ON table_name [ USING method] (column_name[(N)] [,...])

参数说明

index_name
要创建的索引名称，默认与 table_name 所声明的表名创建在相同的模式中。
table_name
需要为其创建索引的表的名称，可以用模式修饰。
method
指定创建索引的方法。默认会将索引创建为 B-Tree 索引。
支持的取值为：
- btree：创建 BTree 索引。
- hash：创建 Hash 索引。
- gin：创建 Gin 索引。
- gist：创建 Gist 索引。
- Psort：创建 Psort 索引。
- ubtree：创建 UBtree 索引。
column_name(N)
表中需要创建索引的列的名称（字段名）。
指定多列时，创建为多字段索引。
指定 (N) 时，将创建前缀索引，其中 N 为前缀长度。

示例

示例1： 基于普通表创建索引

创建普通表。

CREATE TABLE customer_address 
( 
    ca_address_sk       integer                  NOT NULL   , 
    ca_address_id       character(16)            NOT NULL   , 
    ca_street_number    integer                             , 
    ca_street_name      character varying(60)               , 
    ca_street_type      character(15)                       , 
    ca_suite_number     character(10)                       , 
    ca_city             character varying(60)               , 
    ca_county           character varying(30)               , 
    ca_state            character(2)                        , 
    ca_zip              character(10)                       , 
    ca_country           character varying(20)               , 
    ca_gmt_offset       numeric(5,2)                        , 
    ca_location_type    character(20) 
);

创建普通索引。
如果对于 customer_address 表，需要经常进行以下查询。
```
SELECT ca_address_sk FROM customer_address WHERE ca_address_sk=14888;
```
通常，数据库系统需要逐行扫描整个 customer_address 表以寻找所有匹配的元组。如果表 customer_address 的规模很大，但满足 WHERE 条件的只有少数几个（可能是零个或一个），则这种顺序扫描的性能就比较差。如果让数据库系统在 ca_address_sk 属性上维护一个索引，用于快速定位匹配的元组，则数据库系统只需要在搜索树上查找少数的几层就可以找到匹配的元组，这将会大大提高数据查询的性能。同样，在数据库中进行更新和删除操作时，索引也可以提升这些操作的性能。
使用如下命令创建索引：
```
CREATE INDEX index_wr_returned_date_sk ON customer_address (ca_address_sk);
```
创建多字段索引。
假如用户需要经常查询表 customer_address 中 ca_address_sk 是 5050，且ca_street_number 小于 1000 的记录，使用以下命令进行查询。
```
SELECT ca_address_sk,ca_address_id FROM customer_address WHERE ca_address_sk = 5050 AND ca_street_number < 1000;
```
使用以下命令在字段 ca_address_sk 和 ca_street_number 上定义一个多字段索引。
```
CREATE INDEX more_column_index ON customer_address(ca_address_sk ,ca_street_number );
```

创建部分索引。

CREATE INDEX part_index ON customer_address(ca_address_sk) WHERE ca_address_sk = 5050;

创建表达式索引。
假如经常需要查询 ca_street_number 小于 1000 的信息，执行如下命令进行查询。
```
SELECT * FROM customer_address WHERE trunc(ca_street_number) < 1000;
```
可以为上面的查询创建表达式索引：
```
CREATE INDEX para_index ON customer_address (trunc(ca_street_number));
```
清理环境。
```
DROP TABLE customer_address CASCADE;
```

示例2： 基于分区表创建索引

创建分区表。

CREATE TABLE web_returns_p2 
( 
    ca_address_sk       integer                  NOT NULL   , 
    ca_address_id       character(16)            NOT NULL   , 
    ca_street_number    character(10)                       , 
    ca_street_name      character varying(60)               , 
    ca_street_type      character(15)                       , 
    ca_suite_number     character(10)                       , 
    ca_city             character varying(60)               , 
    ca_county           character varying(30)               , 
    ca_state            character(2)                        , 
    ca_zip              character(10)                       , 
    ca_country           character varying(20)               , 
    ca_gmt_offset       numeric(5,2)                        , 
    ca_location_type    character(20) 
) 
PARTITION BY RANGE (ca_address_sk) 
( 
        PARTITION P1 VALUES LESS THAN(5000), 
        PARTITION P2 VALUES LESS THAN(10000), 
        PARTITION P3 VALUES LESS THAN(15000), 
        PARTITION P4 VALUES LESS THAN(20000), 
        PARTITION P5 VALUES LESS THAN(25000), 
        PARTITION P6 VALUES LESS THAN(30000), 
        PARTITION P7 VALUES LESS THAN(40000), 
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
) 
ENABLE ROW MOVEMENT;

创建分区表 LOCAL 索引 tpcds_web_returns_p2_index1，不指定索引分区的名称。
```
CREATE INDEX tpcds_web_returns_p2_index1 ON web_returns_p2 (ca_address_id) LOCAL;
```

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

CREATE INDEX tpcds_web_returns_p2_index2 ON web_returns_p2 (ca_address_sk) LOCAL
(
    PARTITION web_returns_p2_P1_index,
    PARTITION web_returns_p2_P2_index,
    PARTITION web_returns_p2_P3_index,
    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
);

创建分区表 GLOBAL 索引 tpcds_web_returns_p2_global_index。

CREATE INDEX tpcds_web_returns_p2_global_index ON web_returns_p2 (ca_street_number) GLOBAL;

清理测试环境。
```
DROP TABLE web_returns_p2  CASCADE;
```

修改索引

使用 ALTER INDEX 语句修改索引。

语法格式

ALTER INDEX index_name SET tablespace_name;
ALTER INDEX index_name MOVE PARTITION partition_index_name TABLESPACE tablespace_name;
ALTER INDEX index_name UNUSABLE;
ALTER INDEX index_name REBUILD;

参数说明

index_name
索引名称。
partition_index_name
分区上的局部索引名称。
table_spacename
表空间名称。
UNUSABLE
将索引设置为不可用，不可用的索引不会参与查询优化。
REBUILD
重建索引。对应不可用的索引，进行重建后将变为可用状态。
也可以使用 REINDEX 语句重建索引，请参阅 REIDNEX。

示例

示例3： 修改索引

创建分区表。

CREATE TABLE web_returns_p2 
( 
    ca_address_sk       integer                  NOT NULL   , 
    ca_address_id       character(16)            NOT NULL   , 
    ca_street_number    character(10)                       , 
    ca_street_name      character varying(60)               , 
    ca_street_type      character(15)                       , 
    ca_suite_number     character(10)                       , 
    ca_city             character varying(60)               , 
    ca_county           character varying(30)               , 
    ca_state            character(2)                        , 
    ca_zip              character(10)                       , 
    ca_country           character varying(20)               , 
    ca_gmt_offset       numeric(5,2)                        , 
    ca_location_type    character(20) 
) 
PARTITION BY RANGE (ca_address_sk) 
( 
        PARTITION P1 VALUES LESS THAN(5000), 
        PARTITION P2 VALUES LESS THAN(10000), 
        PARTITION P3 VALUES LESS THAN(15000), 
        PARTITION P4 VALUES LESS THAN(20000), 
        PARTITION P5 VALUES LESS THAN(25000), 
        PARTITION P6 VALUES LESS THAN(30000), 
        PARTITION P7 VALUES LESS THAN(40000), 
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
) 
ENABLE ROW MOVEMENT;

创建表空间。

CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace/tablespace_2';

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

ALTER INDEX tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P2_index TABLESPACE example1;

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

ALTER INDEX tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P3_index TABLESPACE example2;

将索引 tpcds_web_returns_p2_index2 设置为不可用。
```
ALTER INDEX tpcds_web_returns_p2_index2 UNUSABLE;
```

重建 tpcds_web_returns_p2_index2 索引。

ALTER INDEX tpcds_web_returns_p2_index2 REBUILD;

清理测试环境。

DROP TABLE web_returns_p2 CASCADE;
DROP TABLESPACE example1;
DROP TABLESPACE example2;

查询索引

查询方式

PG_CLASS 系统表记录了所有的数据库对象，其中 relkind 列为 i 或 I 分别表示该对象为索引或分区表上的全局索引。此外，还可以通过 vsql 的元命令 \di 查看索引信息。

示例

示例4： 查询索引

创建分区表。

CREATE TABLE web_returns_p2 
( 
    ca_address_sk       integer                  NOT NULL   , 
    ca_address_id       character(16)            NOT NULL   , 
    ca_street_number    character(10)                       , 
    ca_street_name      character varying(60)               , 
    ca_street_type      character(15)                       , 
    ca_suite_number     character(10)                       , 
    ca_city             character varying(60)               , 
    ca_county           character varying(30)               , 
    ca_state            character(2)                        , 
    ca_zip              character(10)                       , 
    ca_country           character varying(20)               , 
    ca_gmt_offset       numeric(5,2)                        , 
    ca_location_type    character(20) 
) 
PARTITION BY RANGE (ca_address_sk) 
( 
        PARTITION P1 VALUES LESS THAN(5000), 
        PARTITION P2 VALUES LESS THAN(10000), 
        PARTITION P3 VALUES LESS THAN(15000), 
        PARTITION P4 VALUES LESS THAN(20000), 
        PARTITION P5 VALUES LESS THAN(25000), 
        PARTITION P6 VALUES LESS THAN(30000), 
        PARTITION P7 VALUES LESS THAN(40000), 
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
) 
ENABLE ROW MOVEMENT;

创建表空间。

CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace/tablespace_1';
CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace/tablespace_2';

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

SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';

使用 vsql 元命令查询指定索引的信息。

\di+ tpcds_web_returns_p2_index2

返回结果如下：

                                            List of relations
 Schema |            Name             | Type  |  Owner   |     Table      | Size  | Storage | Description
--------+-----------------------------+-------+----------+----------------+-------+---------+-------------
 public | tpcds_web_returns_p2_index2 | index | vexdb    | web_returns_p2 | 64 kB |         |
(1 row)

清理环境。

DROP TABLE web_returns_p2 CASCADE;
DROP TABLESPACE example1;
DROP TABLESPACE example2;

删除索引

删除索引。

DROP INDEX tpcds_web_returns_p2_index1;
DROP INDEX tpcds_web_returns_p2_index2;

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

DROP INDEX

创建和管理索引

需要帮助？