二级索引

索引是一个逻辑对象，可以帮助hubble更有效地查找数据。当创建索引时，hubble为索引选择的列的副本，然后按索引列值对数据行进行排序，而不对表本身中的值进行排序。

hubble自动在表的主键列上创建索引。该索引称为主索引。主索引帮助hubble更有效地扫描按表的主键列排序的行，但它无助于查找由任何其他列标识的值。

二级索引（所有不是主索引的索引）提高了查询的性能，这些查询识别具有不在表主键中的列的行。hubble自动为具有UNIQUE约束的列创建二级索引。

本页提供了创建二级索引的最佳实践。

准备工作

在阅读本页之前，请执行以下操作：

• 创建hubble集群
• 确认数据库架构对象
• 创建数据库
• 创建用户定义的架构
• 创建一个表

创建二级索引

要向表添加二级索引，请执行以下操作之一：

• 在CREATE TABLE语句末尾添加一个INDEX子句

INDEX子句通常采用以下形式：

index {index_name} ({column_names});

• 用CREATE INDEX声明

CREATE INDEX语句通常采用以下形式：

create index {index_name} on {table_name} ({column_names});

最佳实践

索引内容

• 索引计划用于过滤数据的所有列。

基数较高的列（不同值的数量较多）应放在索引中基数较低的列之前。

• 如果需要索引应用于单个表的一个或多个列的函数的结果，请使用该函数创建一个计算列并对该列进行索引。

• 避免在顺序键上建立索引。

使用随机生成的唯一ID或多列键。

• 避免创建不需要的二级索引。

如果需要更改主键，并且不打算过滤现有主键列的查询，请不要使用ALTER PRIMARY KEY，因为它会从现有主键创建二级索引。相反，使用DROP CONSTRAINT ... PRIMARY KEY/ADD CONSTRAINT ... PRIMARY KEY，它不会创建二级索引。

索引管理

• 将二级索引的创建和删除限制在非高峰时间。如果在高峰工作时间完成，可能会影响性能。

• 不要以root用户身份创建索引。建议以不同的用户身份创建索引，权限更少。

• 尽可能删除未使用的索引。

示例

示例数据如下

create type vtype as enum ('table tennis', 'basketball', 'football');

create table game (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      type vtype,
      begin_time TIMESTAMPTZ DEFAULT now(),
      available bool,
      end_loc varchar(10)
  );

应用程序不会根据这些值进行过滤或排序。如果查询中的任何列不在主索引键中，hubble将需要对表执行完整扫描以查找值。全表扫描可能代价高昂，应尽可能避免。

为了帮助避免不必要的全表扫描，在索引中添加一个STORING子句：

create index type_available_idx on game (type, available) storing (end_loc);

where子句中存储列可以提高其值的查询的性能。