PostgreSql详细介绍（四）

PostgreSQL 学习手册(索引) 
一、索引的类型： 
 
    PostgreSQL 提供了多  种索引类型：B-Tree、Hash、GiST 和 GIN，由于它们使用了不同的算法，因此每种索引类型都有其 适合的查询类型，缺省时，CREATE INDEX 命令将创建 B-Tree 索引。

         1. B-Tree:

    CREATE TABLE test1 (

        id integer,

        content varchar

    );

    CREATE INDEX test1_id_index ON test1 (id);

        B-Tree 索引主要用于等于和范围查询，特别是当索引列包含操作符" <、<=、=、>=和>"作为查询条件时，PostgreSQL 的查 询规划器都会考虑使用 B-Tree 索引。在使用 BETWEEN、IN、 IS NULL 和 IS NOT NULL 的查询中，PostgreSQL 也可以使用 B-Tree 索引。然而对于基于模式匹配操作符的查询，如 LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头 时，如 col LIKE 'foo%'或 col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。

          2. Hash：

    CREATE INDEX name ON table USING hash (column);  

  散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。     这里需要额外说明的是，PostgreSQL 散列索引的性能不比 B-Tree 索引强，但是散列索引的尺寸和构造时间则更差。另外，由于 散列索引操作目前没有记录 WAL 日志，因此一旦发生了数据库崩溃，我们将不得不用 REINDEX 重建散列索引。

         3. GiST：     GiST 索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使 GiST 索引根据不同 的索引策略，而使用特定的操作符类型。

          4. GIN：     GIN 索引是反转索引，它可以处理包含多个键的值(比如数组)。与 GiST 类似，GIN 同样支持用户定义的索引策略，从而可以使 GIN 索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL 的标准发布中包含了用于一维数组的 GIN 操作 符类型，如：<@、@>、=、&&等。 
 
二、复合索引： 
 
    PostgreSQL 中的索引可以定义在数据表的多个字段上，如：

    CREATE TABLE test2 (

        major int,

        minor int,

        name varchar

    }

    CREATE INDEX test2_mm_idx ON test2 (major, minor);

    在当前的版本中，只有 B-tree、GiST 和 GIN 支持复合索引，其中最多可以声明 32 个字段。

    1. B-Tree 类型的复合索引：

    在 B-Tree 类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被 包含其中时，才可以获得最高效率。
    2. GiST 类型的复合索引：

    在 GiST 类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字 段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用 GiST 索引就会比较低 效。  
 
    3. GIN 类型的复合索引：

    与 B-Tree 和 GiST 索引不同的是，GIN 复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得 到相同的效率。 
    使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定， 否则超过三个字段的索引几乎没什么用处。  
 
三、组合多个索引： 
 
    PostgreSQL 可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可 以在多个索引扫描之间组成 AND 和 OR 的条件。比如，一个类似 WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 的查询，可 以被分解成四个独立的基于 x 字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果 OR 在一起并生成最终的结果。 另外一个例子是，如果我们在 x 和 y 上分别存在独立的索引，那么一个类似 WHERE x = 5 AND y = 6 的查询，就会分别基于这两 个字段的索引进行扫描，之后再将各自扫描的结果进行 AND 操作并生成最终的结果行。     为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个 BITMAP，它将给出索引扫描出的数据在数据表中的物理 位置。然后，再根据查询的需要，把这些位图进行 AND 或者 OR 的操作并得出最终的 BITMAP。最后，检索数据表并返回数据行。 表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有 ORDER BY 子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选 择使用简单的索引扫描，即使有多个索引可用也会如此。      
     
四、唯一索引： 
 
    目前，只有 B-Tree 索引可以被声明为唯一索引。

    CREATE UNIQUE INDEX name ON table (column [, ...]);

    如果索引声明为唯一索引，那么就不允许出现多个索引值相同的行。我们认为 NULL 值相互间不相等。      
五、表达式索引： 
 
    表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：

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

    此时，如果我们仅仅是在 col1 字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的 数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在 test1 表上建立基于 col1 字段的表达式索引，如：

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

    如果我们把该索引声明为 UNIQUE，那么它会禁止创建那种 col1 数值只是大小写有区别的数据行，以及 col1 数值完全相同的数 据行。因此，在表达式上的索引可以用于强制那些无法定义为简单唯一约束的约束。现在让我们再看一个应用表达式索引的例子。

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

    和上面的例子一样，尽管我们可能会为 first_name 和 last_name 分别创建独立索引，或者是基于这两个字段的复合索引，在执 行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。

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

  CREATE INDEX 命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个 函数调用，那么可以省略，就像我们在第一个例子里显示的那样。     从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并 将该结果直接存储到索引里。然而在查询时，PostgreSQL 就会把它们看做 WHERE idxcol = 'constant'，因此搜索的速度等效于基 于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。       
六、部分索引： 
 
    部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索 引只包含表中那些满足这个谓词的行。     由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小， 因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：     1. 索引字段和谓词条件字段一致：

    CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)  

      WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');    

下面的查询将会用到该部分索引：

    SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

    下面的查询将不会用该部分索引：

    一个不能使用这个索引的查询可以是∶

    SELECT * FROM access_log WHERE client_ip = inet '192.16