创建和管理索引
背景信息
索引可以提高数据的访问速度，但同时也增加了插入、
更新和删除操作的处理时间。所
以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。需要
分析应用程序的业务处理、
数据使用、
经常被用作查询的条件或者被要求排序的字段来确定
是否建立索引。
索引建立在数据库表中的某些列上。因此，在创建索引时，
应该仔细考虑在哪些列上创
建索引。
在经常需要搜索查询的列上创建索引，可以加快搜索的速度。
在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。
在经常需要根据范围进行搜索的列上创建索引，
因为索引已经排序，
其指定的范围是连
续的。
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，
加快排序查询时间。
在经常使用WHERE 子句的列上创建索引，加快条件的判断速度。
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
39
为经常出现在关键字ORDER BY、GROUP BY、DISTINCT 后面的字段建立索引。
索引创建成功后，
系统会自动判断何时引用索引。
当系统认为使用索引比顺序扫描
更快时，就会使用索引。
索引创建成功后，
必须和表保持同步以保证能够准确地找到新数据，
这样就增加了
数据操作的负荷。因此请定期删除无用的索引。
分区表索引分为LOCAL 索引与GLOBAL 索引，一个LOCAL 索引对应一个具体
分区，而GLOBAL 索引则对应整个分区表。
操作步骤
创建分区表的步骤请参考3.8.2 创建和管理表。
步骤1 创建索引
创建分区表LOCAL 索引tpcds_web_returns_p2_index1，不指定索引分区的名称。
postgres=# CREATE INDEX tpcds_web_returns_p2_index1 ON tpcds.web_returns_p2
(ca_address_id) LOCAL;
当结果显示为如下信息，则表示创建成功。
CREATE INDEX
创建分区表LOCAL 索引tpcds_web_returns_p2_index2，并指定索引分区的名称。
postgres=# 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
创建分区表GLOBAL 索引tpcds_web_returns_p2_global_index。
CREATE INDEX tpcds_web_returns_p2_global_index ON tpcds.web_returns_p2
(ca_street_number) GLOBAL;
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
40
步骤2 修改索引分区的表空间
修改索引分区web_returns_p2_P2_index 的表空间为example1。
postgres=# 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。
postgres=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION
web_returns_p2_P3_index TABLESPACE example2;
当结果显示为如下信息，则表示修改成功。
ALTER INDEX
步骤3 重命名索引分区
执行如下命令对索引分区进行重命名操作。如将web_returns_p2_P8_index 重命名为
web_returns_p2_P8_index_new。
postgres=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 RENAME PARTITION
web_returns_p2_P8_index TO web_returns_p2_P8_index_new;
当结果显示为如下信息，则表示重命名成功。
ALTER INDEX
步骤4 查询索引
执行如下命令查询系统和用户定义的所有索引。
postgres=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i' or RELKIND='I';
执行如下命令查询指定索引的信息。
postgres=#
\di+ tpcds.tpcds_web_returns_p2_index2
步骤5 删除索引
postgres=# DROP INDEX tpcds.tpcds_web_returns_p2_index1;
postgres=# DROP INDEX tpcds.tpcds_web_returns_p2_index2;
当结果显示为如下信息，则表示删除成功。
DROP INDEX
GBase 8c 支持4 种创建索引的方式请参见表3-4。
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
41
索引创建成功后，
系统会自动判断何时引用索引。
当系统认为使用索引比顺序扫描
更快时，就会使用索引。
索引创建成功后，
必须和表保持同步以保证能够准确地找到新数据，
这样就增加了数据
操作的负荷。因此请定期删除无用的索引。
表3- 5 索引方式
索引方式
描述
唯一索引
可用于约束索引属性值的唯一性，或者属性组合值的唯一性。如果
一个表声明了唯一约束或者主键，则GBase 8c 自动在组成主键或
唯一约束的字段上创建唯一索引（可能是多字段索引），以实现这
些约束。目前，GBase 8c 只有B-Tree 可以创建唯一索引。
多字段索引
一个索引可以定义在表中的多个属性上。目前，GBase 8c 中的B-
Tree 支持多字段索引，。
部分索引
建立在一个表的子集上的索引，
这种索引方式只包含满足条件表达
式的元组。
表达式索引
索引建立在一个函数或者从表中一个或多个属性计算出来的表达
式上。
表达式索引只有在查询时使用与创建时相同的表达式才会起
作用。
步骤6 创建一个普通表
postgres=# CREATE TABLE tpcds.customer_address_bak AS TABLE
tpcds.customer_address;
INSERT 0 0
步骤7 创建普通索引
如果对于tpcds.customer_address_bak 表，需要经常进行以下查询。
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
属性上维护一个索引，
用于快速定位匹配的元组，
则数据库系统只需要在搜索树上查找少数
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
42
的几层就可以找到匹配的元组，这将会大大提高数据查询的性能。同样，
在数据库中进行更
新和删除操作时，索引也可以提升这些操作的性能。
postgres=# CREATE INDEX index_wr_returned_date_sk ON pcds.customer_address_bak
(ca_address_sk);
CREATE INDEX
使用以下命令创建索引。
postgres=# CREATE INDEX index_wr_returned_date_sk ON
tpcds.customer_address_bak(ca_address_sk);
CREATE INDEX
步骤8 创建唯一索引
在表tpcds.ship_mode_t1 上的SM_SHIP_MODE_SK 字段上创建唯一索引。
postgres=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON
tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
步骤9 创建多字段索引
假如用户需要经常查询表tpcds.customer_address_bak 中ca_address_sk 是5050，且
ca_street_number 小于1000 的记录，使用以下命令进行查询。
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 上定义一个多字段索引。
postgres=# CREATE INDEX more_column_index ON
tpcds.customer_address_bak(ca_address_sk ,ca_street_number );
CREATE INDEX
步骤10 创建部分索引
如果只需要查询ca_address_sk 为5050 的记录，可以创建部分索引来提升查询效率。
postgres=# CREATE INDEX part_index ON tpcds.customer_address_bak(ca_address_sk)
WHERE ca_address_sk = 5050;
CREATE INDEX
步骤11 创建表达式索引
假如经常需要查询ca_street_number 小于1000 的信息，执行如下命令进行查询。
postgres=# SELECT * FROM tpcds.customer_address_bak WHERE runc(ca_street_number)
< 1000;
GBase 8c V5 开发者手册
南大通用数据技术股份有限公司
43
可以为上面的查询创建表达式索引：
postgres=# CREATE INDEX para_index ON tpcds.customer_address_bak
(trunc(ca_street_number));
CREATE INDEX
步骤12 删除tpcds.customer_address_bak 表
postgres=# DROP TABLE tpcds.customer_address_bak;
DROP TABLE