创建和管理索引

索引类型

• btree：B-tree索引使用一种类似于B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。
• gin：GIN索引是倒排索引，可以处理包含多个键的值（比如数组）。
• gist：Gist索引适用于几何和地理等多维数据类型和集合数据类型。
• Psort：Psort索引。针对列存表进行局部排序索引。

行存表支持的索引类型：btree（行存表缺省值）、gin、gist。列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。

对于点查询场景，推荐建立btree索引。

索引的选择原则

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

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

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

创建索引

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

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

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

• 创建一个普通表。

  1	CREATE TABLE tpcds.customer_address_bak AS TABLE tpcds.customer_address;

• 创建普通索引
  如果对于tpcds.customer_address_bak表，需要经常进行以下查询。

  1	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	CREATE INDEX index_wr_returned_date_sk ON tpcds.customer_address_bak (ca_address_sk);

• 创建唯一索引

  如果一个表声明了唯一约束或者主键，则GaussDB(DWS)自动在组成主键或唯一约束的字段上创建唯一索引（可能是多字段索引）。如果建表时未声明唯一约束或主键，可使用CREATE INDEX创建。

  1	CREATE UNIQUE INDEX unique_index ON tpcds.customer_address_bak(ca_address_sk);

• 创建多字段索引
  假如用户需要经常查询表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	CREATE INDEX more_column_index ON tpcds.customer_address_bak(ca_address_sk ,ca_street_number );

• 创建部分索引

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

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

• 创建表达式索引
  假如经常需要查询ca_street_number小于1000的信息，执行如下命令进行查询。

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

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

  1	CREATE INDEX para_index ON tpcds.customer_address_bak (trunc(ca_street_number));

查看索引

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

  1	SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i';

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

  1	\di+ index_wr_returned_date_sk

重建索引

• 重建索引index_wr_returned_date_sk：

  1	REINDEX INDEX index_wr_returned_date_sk;

• 重建表上的所有索引：

  1	REINDEX TABLE tpcds.customer_address_bak;

删除索引

1

DROP INDEX index_wr_returned_date_sk;