CREATE INDEX

功能描述

在指定的表上创建索引。

索引可以用来提高数据库查询性能,但是不恰当的使用将导致数据库性能下降。建议仅在匹配如下某条原则时创建索引:

  • 经常执行查询的字段。
  • 在连接条件上创建索引,对于存在多字段连接的查询,建议在这些字段上建立组合索引。例如,select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b,可以在t1表上的a,b字段上建立组合索引。
  • where子句的过滤条件字段上(尤其是范围条件)。
  • 在经常出现在order by、group by和distinct后的字段。

在分区表上创建索引与在普通表上创建索引的语法不太一样,使用时请注意,如分区表上不支持并行创建索引、不支持创建部分索引、不支持NULL FIRST特性。

注意事项

  • 索引自身也占用存储空间、消耗计算资源,创建过多的索引将对数据库性能造成负面影响(尤其影响数据导入的性能,建议在数据导入后再建索引)。因此,仅在必要时创建索引。
  • 索引定义里的所有函数和操作符都必须是immutable类型的,即它们的结果必须只能依赖于它们的输入参数,而不受任何外部的影响(如另外一个表的内容或者当前时间)。这个限制可以确保该索引的行为是定义良好的。要在一个索引上或WHERE中使用用户定义函数,请把它标记为immutable类型函数。
  • 在分区表上创建唯一索引时,索引项中必须包含分布列和所有分区键。
  • 列存表支持的PSORT和B-tree索引都不支持创建表达式索引、部分索引和唯一索引。
  • 列存表支持的GIN索引支持创建表达式索引,但表达式不能包含空分词、空列和多列,不支持创建部分索引和唯一索引。

语法格式

  • 在表上创建索引。

    1. CREATE [ UNIQUE ] INDEX [ [schemaname.]index_name ] ON table_name [ USING method ]
    2. ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] )
    3. [ WITH ( {storage_parameter = value} [, ... ] ) ]
    4. [ TABLESPACE tablespace_name ]
    5. [ WHERE predicate ];
  • 在分区表上创建索引。

    1. CREATE [ UNIQUE ] INDEX [ [schemaname.]index_name ] ON table_name [ USING method ]
    2. ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
    3. LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ]
    4. [ WITH ( { storage_parameter = value } [, ...] ) ]
    5. [ TABLESPACE tablespace_name ];

参数说明

  • UNIQUE

    创建唯一性索引,每次添加数据时检测表中是否有重复值。如果插入或更新的值会引起重复的记录时,将导致一个错误。

    目前只有行存表B-tree索引支持唯一索引。

  • schema_name

    模式的名称。

    取值范围:已存在模式名。

  • index_name

    要创建的索引名,不能包含模式名,索引的模式与表相同。

    取值范围:字符串,要符合标识符的命名规范。

  • table_name

    需要为其创建索引的表的名称,可以用模式修饰。

    取值范围:已存在的表名。

  • USING method

    指定创建索引的方法。

    取值范围:

    • btree:B-tree索引使用一种类似于B+树的结构来存储数据的键值,通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。
    • gin:GIN索引是倒排索引,可以处理包含多个键的值(比如数组)。
    • gist:Gist索引适用于几何和地理等多维数据类型和集合数据类型。
    • Psort:Psort索引。针对列存表进行局部排序索引。

    行存表支持的索引类型:btree(行存表缺省值)、gin、gist。列存表支持的索引类型:Psort(列存表缺省值)、btree、gin。

    CREATE INDEX - 图1 说明:
    列存表对GIN索引支持仅限于对于tsvector类型的支持,即创建列存GIN索引入参需要为to_tsvector函数(的返回值)。此方法为GIN索引比较普遍的使用方式。

  • column_name

    表中需要创建索引的列的名称(字段名)。

    如果索引方式支持多字段索引,可以声明多个字段。最多可以声明32个字段。

  • expression

    创建一个基于该表的一个或多个字段的表达式索引,通常必须写在圆括弧中。如果表达式有函数调用的形式,圆括弧可以省略。

    表达式索引可用于获取对基本数据的某种变形的快速访问。比如,一个在upper(col)上的函数索引将允许WHERE upper(col) = ‘JIM’子句使用索引。

    在创建表达式索引时,如果表达式中包含IS NULL子句,则这种索引是无效的。此时,建议用户尝试创建一个部分索引。

  • COLLATE collation

    COLLATE子句指定列的排序规则(该列必须是可排列的数据类型)。如果没有指定,则使用默认的排序规则。

  • opclass

    操作符类的名称。对于索引的每一列可以指定一个操作符类,操作符类标识了索引那一列的使用的操作符。例如一个B-tree索引在一个四字节整数上可以使用int4_ops;这个操作符类包括四字节整数的比较函数。实际上对于列上的数据类型默认的操作符类是足够用的。操作符类主要用于一些有多种排序的数据。例如,用户想按照绝对值或者实数部分排序一个复数。能通过定义两个操作符类然后当建立索引时选择合适的类。

  • ASC

    指定按升序排序 (默认)。

  • DESC

    指定按降序排序。

  • NULLS FIRST

    指定空值在排序中排在非空值之前,当指定DESC排序时,本选项为默认的。

  • NULLS LAST

    指定空值在排序中排在非空值之后,未指定DESC排序时,本选项为默认的。

  • WITH ( {storage_parameter = value} [, … ] )

    指定索引方法的存储参数。

    取值范围:

    只有GIN索引支持FASTUPDATE,GIN_PENDING_LIST_LIMIT参数。GIN和Psort之外的索引都支持FILLFACTOR参数。

    • FILLFACTOR

      一个索引的填充因子(fillfactor)是一个介于10和100之间的百分数。

      取值范围:10~100

    • FASTUPDATE

      GIN索引是否使用快速更新。

      取值范围:ON,OFF

      默认值:ON

    • GIN_PENDING_LIST_LIMIT

      当GIN索引启用fastupdate时,设置该索引pending list容量的最大值。

      取值范围:64~INT_MAX,单位KB。

      默认值:gin_pending_list_limit的默认取决于GUC中gin_pending_list_limit的值(默认为4MB)

  • TABLESPACE tablespace_name

    指定索引的表空间,如果没有声明则使用默认的表空间。

    取值范围:已存在的表空间名。

  • WHERE predicate

    创建一个部分索引。部分索引是一个只包含表的一部分记录的索引,通常是该表中比其他部分数据更有用的部分。例如,有一个表,表里包含已记账和未记账的定单,未记账的定单只占表的一小部分而且这部分是最常用的部分,此时就可以通过只在未记账部分创建一个索引来改善性能。另外一个可能的用途是使用带有UNIQUE的WHERE强制一个表的某个子集的唯一性。

    取值范围:predicate表达式只能引用表的字段,它可以使用所有字段,而不仅是被索引的字段。目前,子查询和聚集表达式不能出现在WHERE子句里。

  • PARTITION index_partition_name

    索引分区的名称。

    取值范围:字符串,要符合标识符的命名规范。

  • TABLESPACE index_partition_tablespace

    索引分区的表空间。

    取值范围:如果没有声明,将使用分区表索引的表空间index_tablespace。

示例

  1. --创建表tpcds.ship_mode_t1
  2. postgres=# create schema tpcds;
  3. postgres=# CREATE TABLE tpcds.ship_mode_t1
  4. (
  5. SM_SHIP_MODE_SK INTEGER NOT NULL,
  6. SM_SHIP_MODE_ID CHAR(16) NOT NULL,
  7. SM_TYPE CHAR(30) ,
  8. SM_CODE CHAR(10) ,
  9. SM_CARRIER CHAR(20) ,
  10. SM_CONTRACT CHAR(20)
  11. )
  12. ;
  13. --在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建普通索引。
  14. postgres=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
  15. --在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引。
  16. postgres=# CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
  17. --在表tpcds.ship_mode_t1SM_CODE字段上创建表达式索引。
  18. postgres=# CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
  19. --在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建SM_SHIP_MODE_SK大于10的部分索引。
  20. postgres=# CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
  21. --重命名一个现有的索引。
  22. postgres=# ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;
  23. --设置索引不可用。
  24. postgres=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
  25. --重建索引。
  26. postgres=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
  27. --删除一个现有的索引。
  28. postgres=# DROP INDEX tpcds.ds_ship_mode_t1_index2;
  29. --删除表。
  30. postgres=# DROP TABLE tpcds.ship_mode_t1;
  31. --创建表空间。
  32. postgres=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
  33. postgres=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
  34. postgres=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
  35. postgres=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
  36. --创建表tpcds.customer_address_p1
  37. postgres=# CREATE TABLE tpcds.customer_address_p1
  38. (
  39. CA_ADDRESS_SK INTEGER NOT NULL,
  40. CA_ADDRESS_ID CHAR(16) NOT NULL,
  41. CA_STREET_NUMBER CHAR(10) ,
  42. CA_STREET_NAME VARCHAR(60) ,
  43. CA_STREET_TYPE CHAR(15) ,
  44. CA_SUITE_NUMBER CHAR(10) ,
  45. CA_CITY VARCHAR(60) ,
  46. CA_COUNTY VARCHAR(30) ,
  47. CA_STATE CHAR(2) ,
  48. CA_ZIP CHAR(10) ,
  49. CA_COUNTRY VARCHAR(20) ,
  50. CA_GMT_OFFSET DECIMAL(5,2) ,
  51. CA_LOCATION_TYPE CHAR(20)
  52. )
  53. TABLESPACE example1
  54. PARTITION BY RANGE(CA_ADDRESS_SK)
  55. (
  56. PARTITION p1 VALUES LESS THAN (3000),
  57. PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
  58. PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
  59. )
  60. ENABLE ROW MOVEMENT;
  61. --创建分区表索引ds_customer_address_p1_index1,不指定索引分区的名称。
  62. postgres=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
  63. --创建分区表索引ds_customer_address_p1_index2,并指定索引分区的名称。
  64. postgres=# CREATE INDEX ds_customer_address_p1_index2 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
  65. (
  66. PARTITION CA_ADDRESS_SK_index1,
  67. PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
  68. PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
  69. )
  70. TABLESPACE example2;
  71. --修改分区表索引CA_ADDRESS_SK_index2的表空间为example1
  72. postgres=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index2 TABLESPACE example1;
  73. --修改分区表索引CA_ADDRESS_SK_index3的表空间为example2
  74. postgres=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index3 TABLESPACE example2;
  75. --重命名分区表索引。
  76. postgres=# ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
  77. --删除索引和分区表。
  78. postgres=# DROP INDEX tpcds.ds_customer_address_p1_index1;
  79. postgres=# DROP INDEX tpcds.ds_customer_address_p1_index2;
  80. postgres=# DROP TABLE tpcds.customer_address_p1;
  81. --删除表空间。
  82. postgres=# DROP TABLESPACE example1;
  83. postgres=# DROP TABLESPACE example2;
  84. postgres=# DROP TABLESPACE example3;
  85. postgres=# DROP TABLESPACE example4;
  86. --创建列存表以及列存表GIN索引。
  87. postgres=# create table cgin_create_test(a int, b text) with (orientation = column);
  88. CREATE TABLE
  89. postgres=# create index cgin_test on cgin_create_test using gin(to_tsvector('ngram', b));
  90. CREATE INDEX

相关链接

ALTER INDEXDROP INDEX

优化建议

  • create index

    建议仅在匹配如下条件之一时创建索引:

    • 经常执行查询的字段。
    • 在连接条件上创建索引,对于存在多字段连接的查询,建议在这些字段上建立组合索引。例如,select * from t1 join t2 on t1.a=t2.a and t1.b=t2.b,可以在t1表上的a,b字段上建立组合索引。
    • where子句的过滤条件字段上(尤其是范围条件)。
    • 在经常出现在order by、group by和distinct后的字段。

    约束限制:

    • 分区表上不支持创建部分索引、不支持NULL FIRST特性。

    • 在分区表上创建唯一索引时,索引项中必须包含分布列和所有分区键。