使用索引

使用索引¶

在大部分传统数据库中，索引能够极大地改善数据访问时间。但是在SeaboxMPP这样的分布式数据库中，索引应该被更保守地使用。 SeaboxMPP数据库会非常快地执行顺序扫描，索引则使用一种随机搜索的模式在磁盘上定位记录。 SeaboxMPP的数据分布在executor上，因此每个executor会扫描全体数据的一小部分来得到结果。通过表分区，要扫描的数据量可能会更少。因为商业智能（BI）查询负载通常会返回非常大的数据集，使用索引并不是很有效。

首先在不加索引时尝试用户的查询负载。索引更有可能为OLTP负载改进性能，在那种场景中查询会返回一个单一记录或者数据的一个小的子集。在被压缩过的列存表上，索引也可以提高返回一个目标行集合的查询的性能，因为优化器在适当的时候可以使用一种索引访问方法而不是全表扫描。对于压缩过的数据，使用一种索引访问方法意味着只有必要的行会被解压。

SeaboxMPP数据库会自动为带有主键的表创建PRIMARY KEY约束。要在一个被分区的表上创建索引，就在用户创建的分区表上创建一个索引。该索引会被传播到SeaboxMPP数据库所创建的所有子表上。不支持在SeaboxMPP数据库为分区表创建的子表上创建索引。

注意一个UNIQUE CONSTRAINT（例如PRIMARY KEY CONSTRAINT）会隐式地创建一个UNIQUE INDEX，它必须包括分布键中所有的列以及任何分区键。 UNIQUE CONSTRAINT会在整个表上被强制要求，包括所有的表分区（如果有）。

索引会增加一些数据库负担，它们使用存储空间并且在表被更新时需要被维护。要确保查询负载会用到用户创建的索引，并且检查用户增加的索引是否改进了查询性能（与表的顺序扫描相比）。要确定是否使用了索引，检查查询的EXPLAIN计划。参见查询分析。

在创建索引时请考虑以下几点。

用户的查询负载。 索引能改进查询返回单一记录或者非常小的数据集的性能，例如OLTP负载。
压缩表。在被压缩过的列存表上，索引也可以提高返回一个目标行集合的查询的性能。对于压缩过的数据，一种索引访问方法意味着只有必要的行会被解压。
避免在频繁更新的列上建立索引 在一个被频繁更新的列上建立索引会增加该列被更新时所要求的写操作数据量。
创建选择性的B-树索引 索引选择度是一个列中具有的可区分值的数量除以表中行数得到的比例。例如，如果一个表有1000行并且一个列中有800个可区分的值，则该索引的选择度为0.8，这还不错。唯一索引的选择度总是1.0，这是最好的选择度。SeaboxMPP数据库只允许在分布键列上的唯一索引。
为低选择度的列使用位图索引。 SeaboxMPP数据库的位图索引类型在常规的SeaboxSQL中不可用。参见关于位图索引。
索引在连接中用到的列。 在被用于频繁连接的一个列（例如一个外键列）上的索引能够提升连接性能，因为这让查询优化器有更多的连接方法可以使用。
索引在谓词中频繁使用的列。 频繁地在WHERE子句中被引用的列是索引的首选。
避免重叠的索引具有相同前导列的索引是冗余的。
批量载入前删掉索引。 对于载入大量数据到一个表中，请考虑先删掉索引并且在数据导入完成后重建它们。这常常比更新索引更快。
考虑一个聚簇索引。 聚簇一个索引意味着记录会根据索引被物理排序后存储在磁盘上。如果用户需要的数据被随机分布在磁盘上，数据库必须在磁盘上来回寻找以取得所需的记录。如果这些记录被存储得彼此临近，那么取得它们的操作就会更高效。例如，一个在日期列上的聚簇索引中数据会按照日期顺序存放。针对一个指定日期范围的查询将会导致对磁盘的一次有序地读取，这会利用快速的顺序访问。

聚簇索引¶

使用CLUSTER命令根据一个索引从物理上重新排序一个非常大的表可能会花费很长的时间。为了更快达到同样的结果，用户可以通过创建一个中间表并且按照想要的顺序重载数据来手工在磁盘上重排数据。例如：

CREATE TABLE new_table (LIKE old_table) 
       AS SELECT * FROM old_table ORDER BY myixcolumn;
DROP old_table;
ALTER TABLE new_table RENAME TO old_table;
CREATE INDEX myixcolumn_ix ON old_table;
VACUUM ANALYZE old_table;

索引类型¶

SeaboxMPP数据库数据库支持Seabox索引类型B-树、GiST和GIN，不支持Hash索引。每一种索引类型都使用一种不同的算法，它们最适合的查询类型也不同。 B-树索引适合于最常见的情况并且是默认的索引类型。

注意: 只有索引键的列与SeaboxMPP分布键相同（或者是其超集）时，SeaboxMPP数据库才允许唯一索引。在分区表上，唯一索引无法在一个分区表的所有子表分区之间被实施。唯一索引只能在一个分区内实施。

关于位图索引¶

SeaboxMPP数据库提供位图索引类型。位图索引最适合于拥有大量数据、很多临时查询以及少量数据修改（DML）事务的数据仓库应用和决策支持系统。

一个索引提供了指向表中包含一个给定键值的行的指针。常规索引存储了每个键存储了一个元组ID的列表，列表中的元组ID对应于具有那个键值的行。位图索引为每一个键值都存储一个位图。常规索引可能会比表中的数据大几倍，但位图索引提供了和常规索引相同的功能并且只需要被索引数据尺寸的一小部分。

位图中的每一个位对应于一个可能的元组ID。如果该位被设置，则具有相应元组ID的行包含该键值。一个映射函数负责将这个位的位置转换成一个元组ID。位图被压缩存储。如果可区分键值的数量很小，位图索引会小很多同时也会被压缩得更好，并且比常规索引节省可观的空间。一个位图索引的大小与该表中行数乘以被索引列中不同值数量的结果成比例。

位图索引对于在WHERE子句中包含多个条件的查询最有效。满足某些但不是全部条件的行在访问表之前就会被过滤掉。这通常会极大地改善响应时间。

何时使用位图索引

位图索引最适合用户只查询数据而不更新数据的数据仓库应用。对于拥有100至100,000个可区分值的列并且当被索引列常常与其他被索引列联合查询时，位图索引表现最好。低于100个可区分值的列通常无法从任何类型的索引受益，例如有两个可区分值的性别列（男和女）。而在具有超过100,000个可区分值的列上，位图索引的性能和空间效率会下降。

位图索引能够提升临时查询的查询性能。在将结果位图转换成元组ID之前，一个查询的WHERE子句中的AND以及OR条件可以通过在位图上直接执行相应的布尔操作快速地解决。如果结果行数很小，查询能够在不做全表扫描的情况下很快地被回答。

何时不用位图索引

不要为唯一列或者具有高基数数据的列使用位图索引，例如顾客姓名或者电话号码。位图索引的性能增益和磁盘空间优势在具有100,000或者更多唯一值的列上开始减小，这与表中的行数无关。

位图索引不适合有大量并发事务修改数据的OLTP应用。

请保守地使用位图索引。测试并且比较使用索引和不使用索引的查询性能。只有被索引列的查询性能有提升时才增加索引。

创建索引¶

CREATE INDEX命令在一个表上定义一个索引。例如，要在表employee的gender列上创建一个B-树索引：

CREATE INDEX gender_idx ON employee (gender);

要在表title中的列title上创建一个位图索引：

CREATE INDEX title_bmp_idx ON films USING bitmap (title);

表达式索引¶

索引列不必只是表的一列，而是可以是从表的一列或多列计算的函数或标量表达式。此功能对于根据计算结果快速访问表非常有用。

索引表达式的维护成本相对较高，因为必须在插入和每次更新时为每一行计算派生表达式。但是，索引表达式在索引搜索期间不会重新计算，因为它们已存储在索引中。在下面两个例子中，系统把查询视为WHERE indexedcolumn = 'constant'，所以查询的速度与其他普通索引相同。因此，当检索速度比插入和更新速度更重要时，表达式上的索引很有用。

第一个示例是使用lower函数进行不区分大小写的比较的常用方法：

SELECT * FROM test1 WHERE lower(col1) = 'value';

如果一个索引已经在lower(col1)函数的结果上被定义，该查询可以使用索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

此示例假定经常执行以下类型的查询。

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

查询可能会受益于以下索引。

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX命令的语法通常需要在索引表达式周围编写括号，如第二个示例所示。当表达式只是函数调用时，可以省略括号，如第一个示例中所示。

检查索引使用¶

SeaboxMPP数据库的索引并不要求维护和调优。用户可以检查实际的查询负载使用了哪些索引。使用EXPLAIN命令可以检查一个查询的索引使用。

查询计划展示了数据库将用来回答一个查询的步骤或者计划节点以及每一个计划节点的时间估计。要检查索引的使用，请在用户的EXPLAIN输出中寻找以下查询计划节点类型：

索引扫描 - 一次索引的扫描。
位图堆扫描 - 检索所有由BitmapAnd、BitmapOr或者BitmapIndexScan生成的位图并且访问堆以检索相关的行。
位图索引扫描 - 计算一个由所有来自底层索引的满足查询谓词的位图通过OR操作形成的位图。
BitmapAnd或BitmapOr - 取得从多个BitmapIndexScan节点生成的位图，把它们AND或者OR在一起，并且生成一个新的位图作为其输出。

用户必须做实验来确定要创建哪些索引。请考虑以下几点。

在创建或者更新一个索引后运行ANALYZE。 ANALYZE会收集表统计信息。查询优化器使用表统计信息来估算一个查询所返回的行数并且为每一种可能的查询计划赋予实际开销。
实验中使用真实数据。使用测试数据建立索引会告诉用户该测试数据需要什么样的索引，但也仅此而已。
不要使用非常小的测试数据集，因为它们的结果很可能是不真实的或者倾斜的。
在开发测试数据时要小心。相似的、完全随机的或者排序后插入的值都将使统计信息偏离真实数据的分布。
通过使用运行时参数来关闭特定的计划类型，用户可以强制使用索引来进行测试。例如，关闭顺序扫描（enable_seqscan）以及嵌套循环连接（enable_nestloop）两种最基本的计划来强制系统使用一种不同的计划。对用户的查询使用索引和不用索引的执行进行计时，并且使用EXPLAIN ANALYZE命令来比较结果。

管理索引¶

使用REINDEX命令可以重建一个表现不好的索引。 REINDEX使用存储在一个索引的基表中的数据重建该索引来替换该索引。

重建一个表上的所有索引

REINDEX my_table;

REINDEX my_index;

删除索引¶

DROP INDEX命令移除一个索引。例如：

DROP INDEX title_idx;

在载入数据时，删除所有索引、载入数据然后重建索引会更快。