使用表达式的虚拟列优化查询

本页面介绍了如何在列式引擎中使用表达式的虚拟列来加速查询。

列式引擎可以具体化并缓存常用表达式的结果。通过预先计算和存储这些结果，AlloyDB 可以避免在多个查询中重复解析和评估相同的表达式。此过程可以提高查询性能并降低 CPU 消耗，尤其是在处理大型数据集的分析工作负载时。

表达式的虚拟列的一些应用场景如下：

  • 基于 JSON 属性进行过滤 ：当您经常根据 JSON 列中的特定键值对过滤查询时。
  • 复杂表达式评估 ：对于涉及复杂或计算密集型表达式的查询。

启用表达式的虚拟列扫描后，使用列式引擎并包含常用表达式的查询会自动优化。列式引擎会在刷新基本列时自动刷新这些虚拟列。

虚拟列中支持的表达式

在（预览版）中，AlloyDB 支持对 JSON 和 JSONB 列使用 -> 和 ->> 运算符。

准备工作

  1. 为 AlloyDB 集群启用列式引擎。如需了解详情，请参阅配置列式引擎。

  2. 使用 Query Insights 识别 AlloyDB 查询中常用的表达式。

  3. 将表达式的基本列添加到列式引擎。如需了解详情，请参阅手动管理列存储区内容。

启用表达式的虚拟列

您可以使用 gcloud beta alloydb instances update 命令为实例启用对表达式的虚拟列的支持。

如需使用 gcloud CLI，您可以 安装并初始化 Google Cloud CLI，也可以 使用 Cloud Shell。

    gcloud beta alloydb instances update INSTANCE_ID \
        --region=REGION_ID \
        --cluster=CLUSTER_ID \
        --project=PROJECT_ID \
        --update-mode=INPLACE \
        --add-database-flags="google_columnar_engine.enable_virtual_columns_scan=on"

替换以下内容：

  • INSTANCE_ID：您的 AlloyDB 实例 ID。
  • REGION_ID：您的 AlloyDB 实例的区域。
  • CLUSTER_ID：您的 AlloyDB 实例的集群 ID。
  • PROJECT_ID：您的 Google Cloud 项目 ID。

如需在会话级设置此标志，请运行以下命令：

SET google_columnar_engine.enable_virtual_columns_scan=on;

添加表达式的虚拟列

如需将常用表达式添加到列式引擎，请使用 psql 客户端调用 google_columnar_engine_add 函数。除了数据库中已有的列之外，这些表达式还会作为列填充到列式引擎中。

您可以将多个表达式指定为以逗号分隔的值。

    SELECT google_columnar_engine_add(
    relation => 'DB.SCHEMA.TABLE_NAME',
    columns => 'COLUMN_NAME, COLUMN_NAME',
    expressions => 'EXP1, EXP2, EXP3'
    );

替换以下内容：

  • DB.SCHEMA：存储表的数据库架构。
  • TABLE_NAME：存储列的表的名称。
  • COLUMN_NAME：包含表达式的列的名称。

  • EXP1, EXP2, EXP3，替换为表达式的逗号分隔列表。支持的 JSON 提取表达式为 -> 和 ->>。

    例如，如需为 public 架构中的 employee 表添加 user ->> 'email' 和 user ->> 'name' 表达式，请使用以下查询：

        SELECT google_columnar_engine_add(
    relation => 'postgres.public.employee',
    expressions => '"user ->> ''email''", "user ->> ''name''"'
    );

    表达式语法：

    • 将整个表达式值用英文单引号引起来，例如 expressions => 'EXP1,EXP2,EXP3'。
    • 使用英文逗号分隔多个表达式。
    • 将每个表达式用英文双引号引起来。
    • 使用另一个英文单引号转义表达式中的任何英文单引号。

    例如，如需添加 col -> 'level1' 和 col -> 'level1' ->> 'level2' 表达式，请使用以下格式：

        expressions => '"col -> ''level1''", "col -> ''level1'' ->> ''level2''"'

表达式的虚拟列示例

此示例演示了如何使用表达式的虚拟列功能。创建一个包含 profile JSONB 列的 users 表，并使用示例数据填充该表。然后，根据查询分析，使用 google_columnar_engine_add 函数将 常用 profile ->> 'email' 表达式添加到列式引擎。列式引擎随后会使用此常用表达式来帮助优化后续查询。

如需将此示例常用表达式添加到列式引擎，请按以下步骤操作：

  1. 在 Google Cloud 控制台中，前往 集群 页面。

    转到集群

  2. 在资源名称 列中，点击集群的名称。

  3. 在导航窗格中，点击 AlloyDB Studio 。

  4. 如需创建包含 profile JSONB 列的 users 表，请运行以下命令：

    CREATE TABLE users (
id int,
username TEXT,
profile JSONB
);

  5. 如需使用示例数据填充 users 表，请运行以下命令：

    INSERT INTO users (id, username, profile)
SELECT
    i,
    'user' || i,
    jsonb_build_object(
        'name', 'User ' || i,
        'email', 'user' || i || '@example.com',
        'active', (i % 2 = 0)
    )
FROM generate_series(1, 100000) AS i;

  6. 如需提高包含常用表达式的查询的性能，请将 profile ->> 'email' 表达式添加到列式引擎：

    SELECT google_columnar_engine_add(
    relation => 'users',
    columns => 'username, profile',
    expressions => '"profile ->> ''email''"'
);

  7. 运行使用常用表达式的查询，并观察查询完成所需的时间。

    SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

  8. 启用表达式的虚拟列功能。

    SET google_columnar_engine.enable_virtual_columns_scan=on;

  9. 重新运行使用常用表达式的查询，并观察查询完成所需的时间。

    SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

启用表达式的虚拟列后，查询运行时速度会更快。

查看表达式的虚拟列

如需查找为特定表添加的所有表达式，请查询 g_columnar_virtual_columns 视图：

    SELECT * FROM g_columnar_virtual_columns;

输出类似于以下内容，其中 description 字段显示为表（关系）添加的所有表达式。

SELECT * FROM g_columnar_virtual_columns;
category           | expression
database_name      | testdb
schemas            | {public}
relations          | {users}
description        | profile->>'email'
column_data_type   | text
status             | Usable
last_accessed_time | 2026-02-04 06:25:32.499601+00
num_times_accessed | 1

移除表达式的虚拟列

如需移除表达式，请调用 google_columnar_engine_drop() 函数：

    SELECT google_columnar_engine_drop(
      relation => 'DB.SCHEMA.TABLE_NAME',
      expressions => 'EXP1, EXP2, EXP3'
    );

将 EXP1, EXP2, EXP3 替换为表中表达式的逗号分隔列表，格式与添加表达式时使用的格式相同。