人大金仓分析型数据库使用之索引

目录

前言

一、创建索引

二、检查索引使用

三、管理索引

---

前言

        在大部分传统数据库中，索引能够极大地改善数据访问时间。 不过，在一个分布式数据库中，索引应该被更保守地使用。 分布式数据库会执行非常快的顺序扫描，索引则使用一种随机搜索的模式在磁盘上定位记录。 通过表分区，要扫描的数据量可能会更少。 因为商业智能（BI）查询负载通常会返回非常大的数据集，使用索引并不是很有效。索引更有可能为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));

二、检查索引使用

        数据库的索引并不要求维护和调优。 用户可以检查实际的查询负载使用了哪些索引。查询计划展示了数据库将用来回答一个查询的步骤或者计划节点以及每一个计划节点的时间估计。 要检查索引的使用，请在用户的EXPLAIN输出中寻找以下查询计划节点类型：

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

三、管理索引

        重建一个表上的所有索引：

REINDEX my_table;
REINDEX my_index;

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

DROP INDEX title_idx;

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