DDL语句 - INDEX - 《ZNBase 数据库技术文档》

INDEX

INDEX

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

CREATE INDEX

CREATE INDEX 语句用于为表创建索引。索引通过帮助SQL定位数据而无需浏览表的每一行来提高数据库的性能。

以下类型不能包含在索引键中，但是可以使用STORING或者COVERING子句存储（并在覆盖的查询中使用）：

JSONB
ARRAY
计算TUPLE类型，即使它是由索引字段构造的

要在JSONB列中的无模式数据上创建索引，请使用反向索引。

注意：将自动为表PRIMARY KEY和UNIQUE列创建索引,查询表时，InCloud ZNBASE使用最快的索引。

语法格式

创建索引的语法格式如下：

如果加上IF NOT EXISTS关键字则仅当不存在同名索引时才创建新索引; 如果确实存在，不返回错误。下面是一些详细的语法结构图：

opt_unique

opt_using_gin_btree

opt_storing

opt_interleave

opt_partition_by

opt_locate_in

index_params

参数说明

opt_index_name

要创建的索引的名称，该名称对于表来说必须是唯一的，并遵循本数据库标识符规则。

index_name

要创建的索引的名称，该名称对于表来说必须是唯一的，并遵循本数据库标识符规则。

table_name

要在其上创建索引的表的名称。

opt_locate_in

确定分区的落盘位置，详情请见PARTITION&LOCATE。

opt_storing

存储（但不排序）包括其名称的每个列。

有关何时使用的信息STORING，请参见存储列。请注意，不能将属于表的列PRIMARY KEY指定为该STORING表的二级索引中的列。COVERING是STORING的别名，工作原理相同。

opt_interleave

可以通过交错索引( interleaving indexes)来优化查询性能，这会改变InCloud ZNBASE存储数据的方式。

opt_partition_by

对索引进行分区。详情请见PARTITION&LOCATE。

示例

示例1：创建标准索引

单列索引

单列索引对单个列的值进行排序

CREATE INDEX ON re_users (city);

由于每个查询只能使用一个索引，因此单列索引通常不如多列索引有用。

多列索引

多列索引按列出的顺序对列进行排序。

CREATE INDEX ON re_users (city, first_name);

唯一索引

唯一索引不允许其列中包含重复值。

CREATE UNIQUE INDEX ON re_users (credit_card, dl);

这也适用于表级别的唯一约束，类似于ALTER TABLE。上面的例子相当于：

ALTER TABLE re_users ADD CONSTRAINT re_users _ credit_card _ dl_key UNIQUE (credit_card, dl);

示例2：创建倒排索引

可以在JSONB列中的无模式数据上创建反向索引。

CREATE INVERTED INDEX ON users (profile);

示例3：存储列

存储列可以提高检索（但不过滤）其值的查询的性能。

CREATE INDEX ON products (price) STORING (name);

但是，要使用存储列，查询必须过滤同一索引中的另一列。例如，只有当查询的WHERE子句过滤price时，SQL才能从上面的索引中检索name值。

示例4：更改排序顺序

要按降序对列进行排序，必须在创建索引时显式设置该选项。（默认值是升序）

CREATE INDEX ON products (price DESC, stock);

列的排序方式会影响使用索引查询返回的行的顺序，这尤其会影响使用LIMIT的查询。

示例5：查询特定索引

通常，InCloud ZNBASE选择它计算将扫描最少的行的索引,但是，你可以覆盖该选择并指定要使用的索引的名称。要查看索引名称，请使用SHOW INDEX。

SHOW INDEX FROM products;

+—————+——————————+————+——-+————+—————-+————-+—————+

| Table | Name | Unique | Seq | Column | Direction | Storing | Implicit |

+—————+——————————+————+——-+————+—————-+————-+—————+

| products | primary | true | 1 | id | ASC | false | false |

|products | products_price_idx | false | 1 | price | ASC | false | false |

|products | products_price_idx | false | 2 | id | ASC | false | true |

+—————+——————————+————+——-+————+—————-+————-+—————+

(3 rows)

SELECT name FROM products@products_price_idx WHERE price > 10;

SHOW INDEX

SHOW INDEX 语句用于返回表或数据库的索引信息。任何用户拥有目标表的任意权限，即可查看该表上的索引信息。

语法格式

显示索引的语法格式如下：

在InCloud ZNBASE中，以下是SHOW INDEX的别名：

SHOW INDEXES
SHOW KEYS

参数说明

table_name

要为其显示索引的表的名称。

示例

示例1：显示表的索引

SHOW INDEX FROM re_users;

table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit

+——————+———————————————+——————+———————+——————-+—————-+————-+—————+

re_users | primary | false | 1 | id | ASC | false | false

re_users | users_dl_key | false | 1 | dl | ASC | false | false

re_users | users_dl_key | false | 2 | id | ASC | false | true

re_users | re_users_city_idx | true | 1 | city | ASC | false | false

re_users | re_users_city_idx | true | 2 | id | ASC | false | true

re_users | re_users_city_first_name_idx | true | 1 | city | ASC | false | false

re_users | re_users_city_first_name_idx | true | 2 | first_name | ASC | false | false

re_users | re_users_city_first_name_idx | true | 3 | id | ASC | false | true

re_users | re_users_credit_card_dl_key | false | 1 | credit_card | ASC | false | false

re_users | re_users_credit_card_dl_key | false | 2 | dl | ASC | false | false

re_users | re_users_credit_card_dl_key | false | 3 | id | ASC | false | true

(11 rows) |

ALTER INDEX

ALTER INDEX 语句修改索引的schema，任何用户拥有目标索引所属表的REFERENCES权限时，即可执行重命名目标索引的操作。

语法格式

配置索引的Range副本的语法格式如下：

如果为设置值，则新区域配置将从其父区域继承其值（例如，分区区域从表区域继承），不必使用default。

如果将变量设置为COPY FROM PARENT（例如range_max_bytes = COPY FROM PARENT），则该变量将从其父Range副本复制其值。该COPY FROM PARENT值是使用方便的快捷方式，因此不必查找父级的当前值。例如，必须将range_max_bytes和range_min_bytes变量一起设置，因此在编辑一个值时，可以将其COPY FROM PARENT用于另一个。请注意，如果在复制子Range副本后更改了父Range副本中的变量，则更改不会反映在子区域中。

在索引中的指定行上强制进行范围分割的语法格式如下：

InCloud ZNBASE将数据分为多个范围。默认情况下，InCloud ZNBASE尝试将范围保持在64MiB以下。为此，如果系统扩大到超过此限制，系统将自动分割范围。在大多数使用情况下，这种自动范围分割就足够了，不必担心系统决定何时或在何处分割范围。但是，出于某些原因，可能要对存储表或索引的范围执行手动拆分：

当一个表仅由一个范围组成时，对该表的所有租户都将对该表进行所有写入和读取操作。如果表仅包含少量数据，但服务于大量流量，则负载分配可能会变得不平衡。手动拆分表的范围可以使表上的负载更均匀地分布在多个节点上。对于包含多个范围的表，负载自然会分布在多个节点上，因此不必担心。

创建表时，它将仅包含一个范围。如果用户知道一个新表将立即收到大量写流量，则用户可能希望在施加负载之前根据期望的写分布来抢先拆分表。这样可以避免自动拆分无法满足写流量时导致的工作负载性能下降。

请注意，当表被截断时，它实际上是在单个新的空范围内重新创建的，而用于构成该表的旧范围则被垃圾回收。在表格的旧版本上执行的任何预拆分都不会转移到新版本中。新表将需要再次预先分割。

更改索引的名称的语法格式如下：

CONFIGURE ZONE

配置索引的Range副本。

SPLIT AT

在索引中的指定行上强制进行范围分割。

standalone_index_name

select_stmt

参数说明

table_name

显示Range副本配置的表的名称。

index_name

显示Range副本配置的索引的名称。

variable

要更改的变量的名称。

value

要更改的变量的值。该变量含有多个参数，详情请见ALTER DATABASE里面对应的参数说明。

standalone_index_name

索引名称。

select_stmt

一种选择查询，该查询产生一个或多个用于拆分表或索引的行。

示例

示例1：重命名索引

SHOW INDEXES FROM re_users;

table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit

+——————+———————————————+——————+———————+——————-+—————-+————-+—————+

re_users | primary | false | 1 | id | ASC | false | false

re_users | users_dl_key | false | 1 | dl | ASC | false | false

re_users | users_dl_key | false | 2 | id | ASC | false | true

re_users | re_users_city_idx | true | 1 | city | ASC | false | false

re_users | re_users_city_idx | true | 2 | id | ASC | false | true

re_users | re_users_city_first_name_idx | true | 1 | city | ASC | false | false

re_users | re_users_city_first_name_idx | true | 2 | first_name | ASC | false | false

re_users | re_users_city_first_name_idx | true | 3 | id | ASC | false | true

re_users | re_users_credit_card_dl_key | false | 1 | credit_card | ASC | false | false

re_users | re_users_credit_card_dl_key | false | 2 | dl | ASC | false | false

re_users | re_users_credit_card_dl_key | false | 3 | id | ASC | false | true

ALTER INDEX re_users@re_users_city_idx RENAME TO re_users_city_idx_rename;

SHOW INDEXES FROM re_users;

table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit

+——————+———————————————+——————+———————+——————-+—————-+————-+—————+

re_users | primary | false | 1 | id | ASC | false | false

re_users | users_dl_key | false | 1 | dl | ASC | false | false

re_users | users_dl_key | false | 2 | id | ASC | false | true

re_users | re_users_city_idx_rename | true | 1 | city | ASC | false | false

re_users | re_users_city_idx_rename | true | 2 | id | ASC | false | true

re_users | re_users_city_first_name_idx | true | 1 | city | ASC | false | false

re_users | re_users_city_first_name_idx | true | 2 | first_name | ASC | false | false

re_users | re_users_city_first_name_idx | true | 3 | id | ASC | false | true

re_users | re_users_credit_card_dl_key | false | 1 | credit_card | ASC | false | false

re_users | re_users_credit_card_dl_key | false | 2 | dl | ASC | false | false

re_users | re_users_credit_card_dl_key | false | 3 | id | ASC | false | true

(11 rows) |

示例2：拆分表

SHOW EXPERIMENTAL_RANGES FROM TABLE kv;

start_key | end_key | range_id | replicas | lease_holder

NULL | NULL | 197 | {1,2,3,9,10} | 9

(1 row) |

拆分将被合并队列立即丢弃所以首先需要通过运行“ SET CLUSTER SETTING kv.range_merge.queue_enabled = false”来禁用合并队列。

SET CLUSTER SETTING kv.range_merge.queue_enabled = false;

SET CLUSTER SETTING

ALTER TABLE kv SPLIT AT VALUES (‘6’);

key | pretty

+———————+———————-+ \

\345\211\216 | /Table/93/1/6

(1 row)

SHOW EXPERIMENTAL_RANGES FROM TABLE kv;

start_key | end_key | range_id | replicas | lease_holder

+—————-+————-+—————+———————+———————+

NULL | /6 | 197 | {1,2,3,9,10} | 9 /6 |

NULL | 465 | {1,2,3,9,10} | 9

(2 rows)

示例3：拆分索引

CREATE INDEX kv_idx ON kv(v);

CREATE INDEX

ALTER INDEX kv@kv_idx SPLIT AT VALUES (‘hello’), (‘world’d’);

key | pretty

+—————————————-+——————————-+

\345\212\022hello\000\001 | /Table/93/2/“hello”

\345\212\022world\000\001 | /Table/93/2/“world”

(2 rows)

SHOW EXPERIMENTAL_RANGES FROM INDEX kv@kv_idx;

start_key | end_key | range_id | replicas | lease_holder

+—————-+—————+—————+———————+———————+

NULL | /“hello” | 465 | {1,2,3,9,10} | 9

/“hello” | /“world” | 466 | {1,2,3,9,10} | 9

/“world” | NULL | 467 | {1,2,3,9,10} | 9

(3 rows) |

DROP INDEX

DROP INDEX 语句用于从表中删除索引。任何用户拥有无依赖关系的目标索引所属表的REFERENCES权限，即可执行删除目标索引的操作。

当目标索引存在依赖关系时，需使用CASCADE关键字删除。

语法格式

删除索引的语法格式如下：

如果添加了IF EXISTS，当要删除的索引存在时，删除索引；如果不存在，删除索引不成功，但是不抛出错误。

如果不添加IF EXISTS，当要删除的索引存在时，删除索引；如果不存在，删除索引不成功，抛出错误：索引不存在。

CASCADE删除所有依赖于索引的其他对象（例如CONSTRAINTS）。CASCADE不会列出删除的对象，因此应谨慎使用。

RESTRICT（默认值）如果有其他对象依赖该索引，则不会删除该索引。

参数说明

table_name

具有要删除的索引的表的名称。使用查找表名SHOW TABLES。

index_name

要删除的索引的名称。使用查找索引名称SHOW INDEX。

不能删除表的primary索引。

standalone_index_name

数据库对象名称。

示例

示例1：删除索引（没有依赖关系）

SHOW INDEX FROM tl;

+——————+——————-+——————+———————+——————-+—————-+————-+—————+

| table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit |

+——————+——————-+——————+———————+——————-+—————-+————-+—————+

| t1 | primary | false | 1 | id | ASC | false | false | \

| t1 | t1_name_idx | true | 1 | name | ASC | false | false |

| t1 | t1_name_idx | true | 2 | id | ASC | false | true |

(3 rows)

DROP INDEX t1@t1_name_idx;

SHOW INDEX FROM tbl;

+——————+——————+——————+———————+——————-+—————-+————-+—————+

| table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit |

+——————+——————+——————+———————+——————-+—————-+————-+—————+

| t1 | primary | false | 1 | id | ASC | false | false |