Question

名称

CREATE INDEX--定义一个新索引

语法

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

描述

CREATE INDEX 在指定的表上创建一个名为 index_name 的索引。索引主要用来提高数据库性能。但是如果不恰当的使用将导致性能的下降。

索引的键字字段是以字段名的方式声明的，或者是可选的写在一个圆括弧里面的表达式。如果索引方式支持多字段索引，那么也可以声明多个字段。

索引字段可以是一个使用一个或多个字段值进行计算的表达式。这个特性可用于获取对基本数据的某种变形的快速访问。比如，一个在 upper(col) 上的函数索引将允许 WHERE upper(col) = 'JIM' 子句使用索引。

PostgreSQL 为从索引提供 B-tree, hash, GiST, GIN 索引方法。用户也可以定义它们自己的索引方法，但这个工作相当复杂。

如果出现了 WHERE 子句，则创建一个部分索引。部分索引是一个只包含表的一部分记录的索引，通常是该表中比其它部分数据更有用的部分。比如，如果你有一个表，里面包含已记账和未记账的定单，未记账的定单只占表的一小部分而且这部分是最常用的部分，那么你就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有 UNIQUE 的 WHERE 强制一个表的某个子集的唯一性。参阅节11.7获取更多信息。

WHERE 子句里的表达式只能引用下层表的字段，它可以使用所有字段，而不仅仅是被索引的字段。目前，子查询和聚集表达式也不能出现在 WHERE 子句里。

索引定义里的所有函数和操作符都必须是"immutable"(不变的)，也就是说，它们的结果必须只能依赖于它们的输入参数，而不能依赖任何外部的影响(比如另外一个表的内容或者当前时间)。这个限制可以确保该索引的行为是定义良好的。要在一个索引上或 WHERE 中使用用户定义函数，请把它标记为 immutable 函数。

参数

UNIQUE

令系统在创建索引时(如果数据已经存在)和每次添加数据时检测表中是否有重复值。如果插入或更新的值会导致重复的记录时将生成一个错误。

CONCURRENTLY

使用该选项后，PostgreSQL 将在创建索引的过程中不在表上持有任何防止插入、更改、删除的写入锁；否则将持有写入锁直到创建完成才释放。使用这个选项时有几个注意点，参见并行创建索引。

name

要创建的索引名，不能包含模式名。索引总是在同一个模式中作为父表创建的。

table

要索引的表名(可能有模式修饰)

method

要使用的索引方法的名字。可选的名字是 btree(缺省), hash, gist, gin

column

表的列/字段名

expression

一个基于该表的一个或多个字段的表达式。这个表达式通常必须带着圆括弧包围写出，如语法中显示那样。不过，如果表达式有函数调用的形式，那么圆括弧可以省略。

opclass

一个关联的操作符类。参阅下文获取细节。

storage_parameter

索引方法特定的存储参数的名字。参阅下文获取细节。

tablespace

创建索引的表空间。如果没有声明，则使用 default_tablespace 表空间，如果 default_tablespace 是空字符串，则使用数据库的缺省表空间。

predicate

为一个部分索引定义约束表达式

索引存储参数

WITH 子句可以为索引指定存储参数。每个索引方法都可以有它自己的一套存储参数。内置的索引方法都都接受下面这个单独的参数：

FILLFACTOR

一个索引的填充因子(fillfactor)是一个百分比，它表示创建索引时每个索引页的数据填充率。对于 B-trees 来说，意味着在创建索引时叶子页将按照此百分比填充数据，在右侧(最大的键值)扩展索引时同样也按照此百分比填充数据。如果后来某个页被完全填满，那么该页将被分割，从而导致索引性能退化。B-trees 默认的填充因子是 90 ，但是有效的取值范围是 10 到 100 。对于静态的不会发生改变的表，最佳值 100 可以让索引的物理体积最小，但是对于不断增长的表，较小的填充因子更合适，因为这将尽可能减少对页的分割。其它索引方法对填充因子的理解与此类似，但是其默认值各不相同。(译者注：如果有条件周期性的重建索引，那么建议使用较大的填充因子以减少索引的物理体积)

并行创建索引

创建索引的过程会对数据库的常规操作性能有不利影响。通常，在创建索引的时候 PostgreSQL 会锁定表以防止写入，然后对表做一次完整扫描以完成索引的创建。在此过程中其他事务仍然可以读取表，但是插入、更新、删除将被一直阻塞到索引创建完毕。这样做对于处于活跃状态的数据库可能会产生严重的性能影响。因为某些很大的表可能需要数个小时的时间来建立索引，而且即使对于生产中正在使用的较小的表，这种阻塞通常也是不可接受的。

PostgreSQL 允许在 CREATE INDEX 创建索引时使用 CONCURRENTLY 选项指定不锁定表。这样 PostgreSQL 就必须对表扫描两次，并且必须等待所有正在执行的事务完成。这种方法增加了总体工作量并且可能需要非常长的时间才能完成索引的创建。然而由于这种方法允许在创建索引的同时进行常规操作，因此这种方法适合于在运行过程中添加新索引。当然，创建索引所需要的额外 CPU 和 I/O 开销仍然会对其它操作有不利影响。

如果在对表进行第二次扫描的时候出现了问题，比如在唯一索引字段上出现了重复值，CREATE INDEX 命令将会失败并遗留下一个"非法"的索引。这个"非法"索引将被忽略，因为它可能是不完整的，当然它也不会导致更新、插入、删除时的额外开销。在这种情况下推荐删除该索引然后尝试重新执行 CREATE INDEX CONCURRENTLY 命令，另一种可能的方法是使用 REINDEX 重建索引。由于 REINDEX 不支持并行创建索引，因此可能不是一个好方法。

对于并行创建唯一索引还有一个警告：在开始对表进行第二次扫描的时候，已经在其他事务上强制进行唯一约束了。这意味着在索引创建完毕之前，如果有其它违反唯一约束的行为那么将会报错，甚至在索引最终创建失败的情况下也是如此。同样，如果在第二次扫描的过程中发生错误，生成的"非法"索引仍将在随后强制执行唯一约束的检查。

并行创建表达式索引和部分索引也是可以的。在表达式求值过程中发生的错误同样也会在唯一约束索引上导致类似前面描述过的行为。

在创建普通索引的同时还允许在同一张表上创建其他普通索引，但是在一张表上只能进行一个并行索引的创建。在此两种情况下，都不允许同时对表所在的模式进行修改。另一个差异是 CREATE INDEX 可以放在一个事务块中执行，但 CREATE INDEX CONCURRENTLY 不可以。

注意

参阅章11获取有关何时使用索引、何时不使用索引，以及索引在哪种情况下是有用的信息。

目前，只有 B-tree 和 GiST 索引方法支持多字段索引。缺省时最多可以声明 32 个键字(可以在编译 PostgreSQL 时修改)。目前只有 B-tree 支持唯一索引。

可以为索引的每个列/字段声明一个操作符类标识将要被该索引用于该列/字段的操作符。例如，一个四字节整数的 B-tree 索引将使用 int4_ops 表；这个操作符类包括四字节整数的比较函数。实际上，该域的数据类型的缺省操作符类一般就足够了。某些数据类型有操作符类的原因是它们可能有多个有意义的顺序。例如，复数类型可能以绝对值或者实部排序。可以通过为该数据类型定义两个操作符类，然后在建立索引的时候选择合适的表来实现。有关操作符类更多的信息在节11.8和节33.14里。

使用 DROP INDEX 删除一个索引。

缺省时索引不会用于 IS NULL 子句。这种场合下使用索引的最好方法是用 IS NULL 谓词创建一个部分索引。

早先的 PostgreSQL 版本还有一个 R-tree 索引方法。因为它并不比 GiST 方法优秀，因此现在已经被删除了。如果指定了 USING rtree 的话，CREATE INDEX 将把它当作 USING gist 看待，并将老的索引转化为 GiST 索引。

例子

在表 films 上的 title 字段上创建一个 B-tree 索引：

CREATE UNIQUE INDEX title_idx ON films (title);

在表达式 lower(title) 上创建一个索引以允许高效的大小写无关搜索：

CREATE INDEX lower_title_idx ON films ((lower(title)));

使用非默认的填充因子创建索引：

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

在表 films 的 code 字段上创建一个索引，并且让索引位于表空间 indexspace 内：

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

在不锁定表的情况下创建索引：

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

兼容性

CREATE INDEX 是 PostgreSQL 语言扩展。在 SQL 标准中没有这个命令。

又见

ALTER INDEX, DROP INDEX