为混合表建立索引

本主题说明如何为为 混合表 建立索引。

索引类型

混合表支持两种类型的索引：

  • 在声明混合表列约束时自动创建的索引：

    • PRIMARY KEY 约束索引

    • FOREIGN KEY 约束索引

    • UNIQUE 约束索引

  • 用户定义的索引（称为 二级索引），可根据需要在其他列上定义。单个索引可覆盖一列或多列。可使用 CREATE HYBRID TABLE 或 CREATE INDEX 定义二级索引。

    创建二级索引时，可以“包含”不属于索引键但与之关联并存储在索引本身的列。请参阅 INCLUDE 列。

    注意

    若要添加二级索引，您必须使用已获授混合表 SELECT 权限的角色。如果您有权访问混合表中的数据视图，但没有访问表本身的权限，则无法添加二级索引。

添加二级索引

所有混合表都需要唯一主键。混合表中的数据会按此主键排序。您可以为非主键属性创建其他二级索引，以加快根据这些属性查找的速度。当查询谓词使用以下条件之一时，索引可能减少扫描的记录数：

如果对特定属性或复合属性组的谓词执行通用的重复查询，请考虑向该属性或属性组添加索引以提高性能。在使用索引时，请注意以下事项：

  • 在索引中存储数据子集的额外副本时，存储消耗量会增加。

  • 由于索引是同步维护的，DMLs 的开销会增加。

您可以在创建混合表时添加二级索引，也可后续使用 CREATE INDEX 命令添加。例如，以下 CREATE HYBRID TABLE 语句会自动创建两个索引（基于 PRIMARY KEY 和 UNIQUE 列 col1 和 col2），以及一个用户自定义的二级索引（基于 col3）：

CREATE OR REPLACE HYBRID TABLE target_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE,
    col3 NUMBER(38,0),
    INDEX index_col3 (col3)
    )
  AS SELECT col1, col2, col3 FROM source_table;
Copy

或者，您也可以使用 CREATE INDEX 命令为现有混合表创建二级索引。使用此命令可向正在活跃用于工作负载、正在处理查询或具有外键的混合表添加索引。CREATE INDEX 命令适用于需要为正在承载工作负载、处理查询或包含外键的混合表添加索引的场景。

小技巧

使用 SHOW INDEXES 命令检查索引构建状态。每次仅支持一个索引构建。

然而，如果您的混合表应用处于开发或测试模式，且允许表短暂不可用，则通过运行优化批量加载来重建混合表并创建索引是更高效的方式。此方法比使用 CREATE INDEX 命令构建在线索引更高效。

CTAS、COPY 及 INSERT INTO ... SELECT 均支持优化批量加载，但若表存在 FOREIGN KEY 约束，则无法使用 CTAS。在此示例中创建的第二个表 fk_hybrid_table 必须通过 COPY 或 INSERT INTO ... SELECT 进行批量加载：

CREATE OR REPLACE HYBRID TABLE ref_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE
);

CREATE OR REPLACE HYBRID TABLE fk_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0),
    col3 NUMBER(38,0),
    FOREIGN KEY (col2) REFERENCES ref_hybrid_table(col2),
    INDEX index_col3 (col3)
);
Copy

INCLUDE 列

虽然 INCLUDE 列不属于二级索引键的组成部分，但它们会与索引记录一同存储。由于实际索引列与包含列中的数据存在这种关联关系，某些查询可避免表扫描，并能通过成本更低的索引扫描获益。但需要注意的是，在索引中使用包含列可能会导致存储消耗增加，因为需要将额外列与索引列一同存储。

例如，请考虑下表和索引。此处的索引既可通过 CREATE TABLE 语句声明，也可通过 CREATE INDEX 语句声明：

CREATE OR REPLACE HYBRID TABLE sensor_data_device1 (
  device_id VARCHAR(10),
  timestamp TIMESTAMP PRIMARY KEY,
  temperature DECIMAL(6,4),
  vibration DECIMAL(6,4),
  motor_rpm INT
  );

CREATE INDEX sec_sensor_idx
  ON TABLE sensor_data_device1(temperature)
    INCLUDE (vibration, motor_rpm);
Copy

由于该二级索引直接覆盖一列 (temperature) 并间接覆盖两列 (vibration, motor_rpm)，因此该索引可用于优化那些对 temperature 有约束条件并从包含的列中选择数据的特定查询。

要测试此行为，请先为该表生成一些行：

INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
  SELECT 'DEVICE1', timestamp,
    UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
    UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
    UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
  FROM (
    SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
      FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
  );
Copy

现在运行以下查询：

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;
Copy

此查询使用名为 sec_sensor_idx 的二级索引。您可通过运行 EXPLAIN 命令或查看 Snowsight 中的查询配置文件来验证此行为。您将看到二级索引上的索引扫描，而混合表本身不会出现“探测扫描”。

以下使用其他受支持 WHERE 子句条件的查询，同样可受益于该二级索引：

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature IN (25.6, 31.2, 35.8);

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature BETWEEN 25.0 AND 26.0;
Copy

现在修改第一个查询，将 device_id 列添加到选择列表中。该列不在 sec_sensor_idx 索引的覆盖范围内。

SELECT device_id, temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;
Copy

此查询无法完全依赖二级索引；需要通过混合表的探测扫描来返回正确的 device_id 值。

语言: 中文