CREATE INDEX

定义一个新索引。

概要

CREATE [UNIQUE] INDEX [ CONCURRENTLY ] [name] ON table_name [USING method]
       ( {column_name | (expression)} [COLLATE parameter] [opclass] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
       [ WITH ( storage_parameter = value [, ... ] ) ]
       [ TABLESPACE tablespace ]
       [ WHERE predicate ]

描述

CREATE INDEX在指定表的指定列上构造索引。索引主要用于增强数据库性能（尽管使用不当会导致性能降低）。

索引的键字段指定为列名，或者指定为用括号括起来的表达式。如果index方法支持多列索引，则可以指定多个字段。

索引字段可以是根据表行的一个或多个列的值计算的表达式。该功能可用于基于基本数据的某种转换来快速访问数据。例如，在upper(col)上计算的索引将允许子句WHERE upper(col) = 'JIM'使用索引。

MatrixDB 数据库提供了索引方法B树，位图，GiST，SP-GiST和GIN。用户还可以定义自己的索引方法，但这相当复杂。

存在WHERE子句时，将创建部分索引。部分索引是仅包含表一部分的条目的索引，通常索引的部分比表的其余部分更有用。例如，如果您有一个既包含开票订单又包含未开票订单的表，其中未开票订单仅占总表的一小部分，但最常被选择，则可以通过仅在该部分上创建索引来提高性能。

WHERE子句中使用的表达式可能仅引用基础表的列，但它可以使用所有列，而不仅仅是被索引的列。 WHERE中也禁止子查询和聚合表达式。相同的限制适用于作为表达式的索引字段。

索引定义中使用的所有函数和运算符必须是不可变的。它们的结果必须仅取决于其参数，而不得取决于任何外部影响（例如另一个表的内容或参数值）。此限制可确保索引的行为得到良好定义。要在索引表达式或WHERE子句中使用用户定义的函数，请记住在创建函数时将其标记为IMMUTABLE。

参数

UNIQUE

创建索引并每次添加数据时，检查表中是否有重复值。重复的条目将产生错误。唯一索引仅适用于B树索引。在 MatrixDB 数据库中，仅当索引键的列与 MatrixDB 分布键相同（或超集）时，才允许唯一索引。在分区表上，唯一索引仅在单个分区中受支持 - 不可以跨所有分区。

CONCURRENTLY

创建索引时不启用任何防止插入、更新及删除操作的锁。一个标准索引的创建过程会对写操作上锁（但读操作不会受到影响）直到创建完成，但当 CONCURRENTLY 选项被打开，MatrixDB 将不用启用所有对写操作的锁。对于临时表，CREATE INDEX 语句通常是非并发的，因为没有其他会话可以进入。非并发的索引创建过程开销也较小。

name

要创建的索引的名称。索引始终与其父表在相同的模式中创建。如果省略该名称，则 MatrixDB 数据库将基于父表的名称和索引列的名称选择一个合适的名称。

table_name

要建立索引的表的名称（可以由模式指定）。

method

要使用的索引方法的名称。选择为btree，bitmap， gist，spgist和gin。默认方法是btree。
当前，仅B树，GiST和GIN索引方法支持多列索引。默认情况下，最多可以指定32个字段。当前只有B树支持唯一索引。

column_name

在其上创建索引的表的列的名称。仅B树，位图，GiST和GIN索引方法支持多列索引。

expression

一种基于表的一个或多个列的表达式。表达式通常必须用括号括起来，如语法所示。但是，如果表达式具有函数调用的形式，则可以省略括号。

collation

索引使用的归类名称。默认情况下，索引使用为要建立索引的列声明的归类或要建立索引的表达式的结果归类。具有非默认归类的索引对于涉及使用非默认归类的表达式的查询很有用。

opclass

运算符类的名称。 operator类标识该列的索引要使用的运算符。例如，在四字节整数上的B树索引将使用int4_ops类（此运算符类包含四字节整数的比较函数）。实际上，列数据类型的默认运算符类通常就足够了。拥有运算符类的要点是，对于某些数据类型，可能会有不止一种有意义的排序。例如，复数数据类型可以按绝对值或实数进行排序。为此，我们可以为数据类型定义两个运算符类，然后在创建索引时选择适当的类。

ASC

指定升序排序（默认）。

DESC

指定降序排序。

NULLS FIRST

指定null排在非null之前。当指定DESC时，这是默认设置。

NULLS LAST

指定空值在非空值之后排序。如果未指定DESC，这是默认设置。

storage_parameter

特定于索引方法的存储参数的名称。每个索引方法都有其自己的一组允许的存储参数。
FILLFACTOR-B树，位图，GiST和SP-GiST索引方法都接受此参数。索引的FILLFACTOR是一个百分比，它确定索引方法将尝试打包索引页面的程度。对于B树，在初始索引构建期间以及在向右扩展索引时（添加新的最大键值），叶子页将填充到该百分比。如果页面随后完全填满，它们将被拆分，从而导致索引效率逐渐下降。 B树使用默认填充因子90，但可以选择10到100之间的任何整数值。如果表是静态的，则fillfactor 100最好最大程度地减小索引的物理大小，但是对于大量更新的表，较小的fillfactor更好地最小化对页面拆分的需求。其他索引方法以不同但大致相似的方式使用fillfactor；默认的填充因子因方法而异。
BUFFERING - 除FILLFACTOR之外，GiST索引还接受BUFFERING参数。 BUFFERING确定 MatrixDB 数据库是否使用PostgreSQL文档中GiST缓冲构建中描述的缓冲构建技术构建索引。如果设置为OFF，则禁用它；如果设置为ON，则启用；将其设置为AUTO时，最初将其禁用；但是，一旦索引大小达到有效缓存大小，它就会即时打开。默认为AUTO。
FASTUPDATE - GIN索引方法接受FASTUPDATE存储参数。 FASTUPDATE是一个布尔参数，用于禁用或启用GIN索引快速更新技术。值ON启用快速更新（默认值），而值OFF禁用它。有关更多信息，请参见PostgreSQL文档中的GIN快速更新技术。

tablespace_name

在其中创建索引的表空间。如果未指定，则使用默认表空间。

predicate

部分索引的约束表达式。

注解

可以为索引的每一列指定一个运算符类。 operator类标识该列的索引要使用的运算符。例如，在四字节整数上的B树索引将使用int4_ops类。此运算符类包含用于四字节整数的比较函数。实际上，列数据类型的默认运算符类通常就足够了。拥有运算符类的要点是，对于某些数据类型，可能会有不止一种有意义的排序。例如，我们可能想按绝对值或实数对复数数据类型进行排序。为此，我们可以为数据类型定义两个运算符类，然后在创建索引时选择适当的类。

对于支持排序扫描的索引方法（当前仅支持B树），可以指定可选的子句ASC，DESC， NULLS FIRST和/或NULLS LAST来修改索引的排序顺序。由于可以向前或向后扫描有序索引，因此创建单列DESC索引通常没有用 - 常规索引已经可以使用排序顺序。这些选项的值在于可以创建与混合排序查询所请求的排序顺序匹配的多列索引，例如SELECT ... ORDER BY x ASC, y DESC。如果您需要在依赖索引以避免排序步骤的查询中支持“nulls sort low”行为，而不是默认的“nulls sort high”行为，则NULLS选项很有用。

对于大多数索引方法，创建索引的速度取决于maintenance_work_mem的设置。较大的值将减少索引创建所需的时间，只要您不使其大于实际可用的内存量即可，这会促使计算机进行交换。

在分区表上创建索引后，该索引将传播到 Matrix 数据库创建的所有子表。不支持在由 MatrixDB 数据库创建的表上创建索引以供分区表使用。

仅当索引列与 MatrixDB 分布键列相同（或超集）时，才允许使用UNIQUE索引。

附加优化表上不允许使用UNIQUE索引。

可以在分区表上创建UNIQUE索引。但是，唯一性仅在分区内有效。分区之间不强制唯一性。例如，对于具有基于年份的分区和基于季度的子分区的分区表，仅在每个单独的季度分区上实施唯一性。季度分区之间不强制唯一性。

默认情况下，索引不用于IS NULL子句。在这种情况下，使用索引的最佳方法是使用IS NULL谓词创建部分索引。

对于具有100到100,000个不同值的列，位图索引的性能最佳。对于具有超过100,000个不同值的列，位图索引的性能和空间效率会下降。位图索引的大小与表中的行数乘以索引列中不同值的数量成正比。

少于100个不同值的列通常不会从任何类型的索引中受益太多。例如，对于男性和女性仅具有两个不同值的性别列将不是索引的理想选择。

MatrixDB 数据库的先前版本也具有R树索引方法。该方法已被删除，因为它与GiST方法相比没有明显的优势。如果指定了USING rtree，则CREATE INDEX会将其解释为USING gist。

哈希索引的使用已在 MatrixDB 数据库中禁用。

示例

要在films表的title列上创建B树索引：

CREATE UNIQUE INDEX title_idx ON films (title);

要在表employee中的gender列上创建位图索引：

CREATE INDEX gender_bmp_idx ON employee USING bitmap 
(gender);

要在表达式lower(title)上创建索引，以允许有效的不区分大小写的搜索：

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

（在此示例中，我们选择省略索引名称，因此系统将选择一个名称，通常为films_lower_idx。）

要使用非默认排序规则创建索引：

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

要创建具有非默认填充因子的索引：

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

要创建禁用了快速更新的GIN索引：

CREATE INDEX gin_idx ON documents_table USING gin (locations) WITH (fastupdate = off);

要在表films的列code上创建索引并使索引驻留在表空间indexspace中：

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

要在point属性上创建GiST索引，以便我们可以对转换函数的结果有效地使用box运算符：

CREATE INDEX pointloc ON points USING gist (box(location,location));
SELECT * FROM points WHERE box(location,location) && '(0,0),(1,1)'::box;

不启用对写操作的锁创建索引：

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

兼容性

CREATE INDEX是 MatrixDB 数据库语言的扩展。 SQL标准中没有索引的规定。

另见

ALTER INDEX，DROP INDEX