CREATE 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后的字段。

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

新增可以指定 ALGORITHM 选项语法。

注意事项

本章节只包含dolphin新增的语法，原openGauss的语法未做删除和修改。新增支持option的无序排列。

语法格式

在表上创建索引。

1. CREATE [ UNIQUE | FULLTEXT ] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ] 2. { ON table_name [ USING method ] | [ USING method ] ON table_name } 3. ({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] ) 4. [ index_option ] 5. [ WHERE predicate | ALGORITHM [=] {DEFAULT | INPLACE | COPY} ];

1. CREATE [UNIQUE] INDEX index_name 2. ON tbl_name (key_part,...) 3. [USING {BTREE | HASH}] - 在分区表上创建索引。

1. CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ] 2. { ON table_name [ USING method ] | [ USING method ] ON table_name } 3. ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] ) 4. [ LOCAL [ ( { PARTITION index_partition_name [ ( SUBPARTITION index_subpartition_name [, ...] ) ] [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ] 5. [ index_option ] [ALGORITHM [=] {DEFAULT | INPLACE | COPY} ]

参数说明

FULLTEXT

该关键字为创建兼容MySQL的全文索引的语法。该全文索引主要用于字符串的搜索匹配。包含局部匹配搜索，支持中文，韩文，日文。与MATCH () AGAINST ()配合使用。 - column_name ( length )

创建一个基于该表一个字段的前缀键索引，column_name为前缀键的字段名，length为前缀长度。

前缀键将取指定字段数据的前缀作为索引键值，可以减少索引占用的存储空间。含有前缀键字段的过滤条件和连接条件可以使用索引。

说明：

前缀键支持的索引方法：btree、ubtree。

前缀键的字段的数据类型必须是二进制类型或字符类型（不包括特殊字符类型）。

前缀长度必须是不超过2676的正整数，并且不能超过字段的最大长度。对于二进制类型，前缀长度以字节数为单位。对于非二进制字符类型，前缀长度以字符数为单位。键值的实际长度受内部页面限制，若字段中含有多字节字符、或者一个索引上有多个键，索引行长度可能会超限，导致报错，设定较长的前缀长度时请考虑此情况。

CREATE INDEX语法中，不支持以下关键字作为前缀键的字段名称：COALESCE、CONVERT、DAYOFMONTH、DAYOFWEEK、DAYOFYEAR、DB_B_FORMAT、EXTRACT、GREATEST、HOUR_P、IFNULL、LEAST、LOCATE、MICROSECOND_P、MID、MINUTE_P、NULLIF、NVARCHAR、NVL、OVERLAY、POSITION、QUARTER、SECOND_P、SUBSTR、SUBSTRING、TEXT_P、TIME、TIMESTAMP、TIMESTAMPDIFF、TREAT、TRIM、WEEKDAY、WEEKOFYEAR、XMLCONCAT、XMLELEMENT、XMLEXISTS、XMLFOREST、XMLPARSE、XMLPI、XMLROOT、XMLSERIALIZE。若含有上述关键字的前缀键所在的索引是通过ALTER TABLE或CREATE TABLE语法创建的，导出的CREATE INDEX语句可能无法成功执行，请尽量不要使用上述关键字作为前缀键的列名称。

index_option

创建索引时可指定选项，其语法为：

1. INCLUDE ( column_name [, ...] ) 2. | WITH ( { storage_parameter = value } [, ...] ) 3. | TABLESPACE tablespace_name

其中，TABLESPACE选项允许输入多次，以最后一次的输入为准。 - ALGORITHM

指定算法，可选项：DEFAULT、INPLACE、COPY。当前只做语法兼容，暂无实际功能。

示例

1. --创建表tpcds.ship_mode_t1。
2. openGauss=# create schema tpcds;
3. openGauss=# 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. ;

14. --在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建普通的唯一索引。
15. openGauss=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);

17. --在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建指定B-tree索引。
18. openGauss=# CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);

20. --在表tpcds.ship_mode_t1上SM_CODE字段上创建表达式索引。
21. openGauss=# CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));

23. --在表tpcds.ship_mode_t1上的SM_SHIP_MODE_SK字段上创建SM_SHIP_MODE_SK大于10的部分索引。
24. openGauss=# CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;

26. --重命名一个现有的索引。
27. openGauss=# ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;

29. --设置索引不可用。
30. openGauss=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;

32. --重建索引。
33. openGauss=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;

35. --删除一个现有的索引。
36. openGauss=# DROP INDEX tpcds.ds_ship_mode_t1_index2;

38. --删除表。
39. openGauss=# DROP TABLE tpcds.ship_mode_t1;

41. --创建表空间。
42. openGauss=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
43. openGauss=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
44. openGauss=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
45. openGauss=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
46. --创建表tpcds.customer_address_p1。
47. openGauss=# CREATE TABLE tpcds.customer_address_p1
48. (
49. CA_ADDRESS_SK             INTEGER               NOT NULL,
50. CA_ADDRESS_ID             CHAR(16)              NOT NULL,
51. CA_STREET_NUMBER          CHAR(10)                      ,
52. CA_STREET_NAME            VARCHAR(60)                   ,
53. CA_STREET_TYPE            CHAR(15)                      ,
54. CA_SUITE_NUMBER           CHAR(10)                      ,
55. CA_CITY                   VARCHAR(60)                   ,
56. CA_COUNTY                 VARCHAR(30)                   ,
57. CA_STATE                  CHAR(2)                       ,
58. CA_ZIP                    CHAR(10)                      ,
59. CA_COUNTRY                VARCHAR(20)                   ,
60. CA_GMT_OFFSET             DECIMAL(5,2)                  ,
61. CA_LOCATION_TYPE          CHAR(20)
62. )
63. TABLESPACE example1
64. PARTITION BY RANGE(CA_ADDRESS_SK)
65. (
66. PARTITION p1 VALUES LESS THAN (3000),
67. PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
68. PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
69. )
70. ENABLE ROW MOVEMENT;
71. --创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名称。
72. openGauss=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
73. --创建分区表索引ds_customer_address_p1_index2，并指定索引分区的名称。
74. openGauss=# CREATE INDEX ds_customer_address_p1_index2 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
75. (
76. PARTITION CA_ADDRESS_SK_index1,
77. PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
78. PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
79. )
80. TABLESPACE example2;

82. --创建GLOBAL分区索引
83. openGauss=CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;

85. --不指定关键字，默认创建GLOBAL分区索引
86. openGauss=CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);

88. --修改分区表索引CA_ADDRESS_SK_index2的表空间为example1。
89. openGauss=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index2 TABLESPACE example1;

91. --修改分区表索引CA_ADDRESS_SK_index3的表空间为example2。
92. openGauss=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION CA_ADDRESS_SK_index3 TABLESPACE example2;

94. --重命名分区表索引。
95. openGauss=# ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;

97. --删除索引和分区表。
98. openGauss=# DROP INDEX tpcds.ds_customer_address_p1_index1;
99. openGauss=# DROP INDEX tpcds.ds_customer_address_p1_index2;
100. openGauss=# DROP TABLE tpcds.customer_address_p1;
101. --删除表空间。
102. openGauss=# DROP TABLESPACE example1;
103. openGauss=# DROP TABLESPACE example2;
104. openGauss=# DROP TABLESPACE example3;
105. openGauss=# DROP TABLESPACE example4;

107. --创建列存表以及列存表GIN索引。
108. openGauss=# create table cgin_create_test(a int, b text) with (orientation = column);
109. CREATE TABLE
110. openGauss=# create index cgin_test on cgin_create_test using gin(to_tsvector('ngram', b));
111. CREATE INDEX

全文索引

1. openGauss=# CREATE SCHEMA fulltext_test;
2. CREATE SCHEMA
3. openGauss=# set current_schema to 'fulltext_test';
4. SET
5. openGauss=# CREATE TABLE test (
6. id int unsigned auto_increment not null primary key,
7. title varchar,
8. boby text,
9. name name
10. );
11. NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
12. NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
13. CREATE TABLE
14. openGauss=# \d test
15. Table "fulltext_test.test"
16. Column |       Type        |        Modifiers
17. --------+-------------------+-------------------------
18. id     | uint4             | not null AUTO_INCREMENT
19. title  | character varying |
20. boby   | text              |
21. name   | name              |
22. Indexes:
23. "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

25. openGauss=# CREATE FULLTEXT INDEX test_index_1 ON test (title, boby) WITH PARSER ngram;
26. \d test_index_1
27. Index "fulltext_test.test_index_1"
28. Column    | Type |                   Definition
29. --------------+------+------------------------------------------------
30. to_tsvector  | text | to_tsvector('"ngram"'::regconfig, title::text)
31. to_tsvector1 | text | to_tsvector('"ngram"'::regconfig, boby)
32. gin, for table "fulltext_test.test"

35. openGauss=# CREATE FULLTEXT INDEX test_index_2 ON test (title, boby, name);
36. CREATE INDEX

CREATE INDEX

CREATE INDEX

功能描述

注意事项

语法格式

参数说明

示例

全文索引

相关链接