Tio Boot Docs

Appearance

PostgreSQL 查询优化 ​

在面对大数据量（如 rumi_rmp_rating 表中的 1 千万 条记录）时，分区表是一种有效的优化手段。本文将详细探讨在将表转换为分区表后，查询和插入操作的变化，以及如何利用分区表进行优化。

一、索引 ​

原始查询示例：

sql
SELECT
  id,
  admin_reviewed_at,
  attendance_mandatory,
  clarity_rating_rounded,
  class_name,
  comment,
  course_type,
  date,
  difficulty_rating_rounded,
  flag_status,
  grade,
  helpful_rating_rounded,
  would_like_take_again,
  is_for_credit,
  is_for_online_class,
  mask_count,
  quality_rating,
  textbook_is_used,
  thumbs_down_total,
  thumbs_up_total,
  class_name_vector
FROM rumi_rmp_rating
WHERE teacher_id = 868673;

在未优化前，此查询耗时约 19 秒。通过在 teacher_id 列上创建索引：

sql
CREATE INDEX idx_rumi_rmp_rating_teacher_id
  ON public.rumi_rmp_rating (teacher_id);

查询时间可缩短至约 5 秒。

二、分区表 ​

为了进一步优化和管理大数据量，可以将 rumi_rmp_rating 表改为分区表。分区表按特定规则将数据拆分到多个子表中，从而在查询和维护上带来多方面的优势。

分区表示例： ​

  1. 创建父表：

    sql
    CREATE TABLE public.rumi_rmp_rating_partitioned (
  -- 字段定义同原表
) PARTITION BY RANGE (teacher_id);

  2. 创建子分区：

    sql
    CREATE TABLE public.rumi_rmp_rating_part_1
  PARTITION OF public.rumi_rmp_rating_partitioned
  FOR VALUES FROM (0) TO (1000000);

CREATE TABLE public.rumi_rmp_rating_part_2
  PARTITION OF public.rumi_rmp_rating_partitioned
  FOR VALUES FROM (1000000) TO (2000000);

-- 根据数据分布继续创建更多子分区

三、使用分区表后的查询和插入操作 ​

1. 查询 SQL 的变化 ​

在将表改为分区表后，日常查询的 SQL 语句基本保持不变。主要变化在于指定使用分区表的父表名。例如：

原始查询：

sql
SELECT
  id, admin_reviewed_at, attendance_mandatory, clarity_rating_rounded, ...
FROM public.rumi_rmp_rating
WHERE teacher_id = YOUR_TEACHER_ID;

分区表查询：

sql
SELECT
  id, admin_reviewed_at, attendance_mandatory, clarity_rating_rounded, ...
FROM public.rumi_rmp_rating_partitioned
WHERE teacher_id = YOUR_TEACHER_ID;

在此查询中：

  • 只需将表名由原来的 rumi_rmp_rating 替换为父表 rumi_rmp_rating_partitioned。
  • PostgreSQL 会根据 teacher_id 的值自动选择对应的子分区，从而利用分区裁剪（partition pruning）技术优化查询，仅扫描相关分区，提高查询性能。

2. 插入 SQL 的变化 ​

插入操作同样保持简洁，无需指定具体的子分区。只需将目标表名替换为父表即可：

原始插入操作：

sql
INSERT INTO public.rumi_rmp_rating (id, teacher_id, school_id, ...)
VALUES (1, 12345, 67890, ...);

使用分区表后的插入操作：

sql
INSERT INTO public.rumi_rmp_rating_partitioned (id, teacher_id, school_id, ...)
VALUES (1, 12345, 67890, ...);

在插入时，数据库会根据记录中的 teacher_id 自动路由到对应的子分区，因此应用层不需要做额外的处理。

四、其他注意事项 ​

  1. DDL 操作与分区管理

    • 定期需要添加新分区或调整分区范围，以适应数据增长和变化。这些操作通常不影响日常查询和插入，但需要数据库管理员进行规划和维护。
    • 在创建子分区时或事后，需要为每个分区单独创建索引和约束，因为 PostgreSQL 不支持全局索引。可以在父表上定义索引模板，通过继承到各子分区。

  2. 索引与约束

    • 每个子分区需单独管理索引和约束，以保证查询性能和数据完整性。
    • 父表上的全局约束和索引不会自动应用到所有子分区，需要在每个子分区上重复创建。

  3. 性能影响

    • 分区表能显著减少单次查询的数据扫描范围，尤其是在查询条件包含分区键（例如 teacher_id）时，性能提升明显。
    • 插入操作在路由到具体分区时有少量额外开销，但通常不影响整体性能。

五、改造表为分区表 ​

在 PostgreSQL 中，将现有的非分区表直接改造为分区表存在一定限制和复杂性。最推荐和稳妥的方法是创建新的分区表并迁移数据。下面详细介绍此方法的完整步骤和注意事项。

1. 挑战与背景 ​

直接将已有普通表转换为分区表存在以下挑战：

  • 较早版本的 PostgreSQL 不支持直接转换表为分区表，即使在较新版本中支持，也有诸多限制。
  • 转换要求表上没有外键等依赖关系，操作期间可能锁定表，影响业务。
  • 需要明确分区策略（如基于某字段范围），并处理已有数据如何分配到各子分区。

鉴于这些限制，建议采用创建新分区表并迁移数据的方式来实现改造。

2. 创建新分区表并迁移数据 ​

这是最稳妥和推荐的方法。具体步骤如下：

步骤一：新建分区父表及子分区 ​

  1. 创建父表
    根据原表结构创建一个新的分区父表，并指定分区键。例如，以 teacher_id 作为分区键：
sql
CREATE TABLE public.rumi_rmp_rating_partitioned (
  "id" "int8" NOT NULL,
  "teacher_id" "int8",
  "school_id" "int8",
  "admin_reviewed_at" "timestamptz",
  "attendance_mandatory" "varchar" ,
  "clarity_rating_rounded" "int4",
  "class_name" "varchar" ,
  "comment" "varchar" ,
  "course_type" "int4",
  "created_by_user" "bool",
  "date" "timestamptz",
  "difficulty_rating_rounded" "int4",
  "flag_status" "varchar" ,
  "grade" "varchar" ,
  "helpful_rating_rounded" "int4",
  "would_like_take_again" "int4",
  "is_for_credit" "bool",
  "is_for_online_class" "bool",
  "mask_count" "int4",
  "quality_rating" "int4",
  "rating_tags" "varchar" ,
  "textbook_is_used" "bool",
  "thumbs_down_total" "int4",
  "thumbs_up_total" "int4",
  "source_url" "varchar" ,
  "remark" "varchar" ,
  "creator" "varchar" ,
  "create_time" "timestamptz" NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updater" "varchar" ,
  "update_time" "timestamptz" NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "deleted" "int2" NOT NULL DEFAULT 0,
  "tenant_id" "int8" NOT NULL DEFAULT 0,
  "class_name_vector" "vector"
) PARTITION BY RANGE (teacher_id);
  1. 创建子分区
    根据业务需求和数据分布创建多个子分区。例如：
sql
CREATE TABLE public.rumi_rmp_rating_part_1
  PARTITION OF public.rumi_rmp_rating_partitioned
  FOR VALUES FROM (0) TO (1000000);

CREATE TABLE public.rumi_rmp_rating_part_2
  PARTITION OF public.rumi_rmp_rating_partitioned
  FOR VALUES FROM (1000000) TO (2000000);

按需继续创建更多子分区，以覆盖所有可能的 teacher_id 范围。

步骤二：迁移数据 ​

  1. 准备工作

    • 暂时禁止向原表 rumi_rmp_rating 写入新数据，以保证数据一致性。
    • 确保新分区表及其子分区已正确创建且结构与原表兼容。

  2. 执行数据迁移
    使用 INSERT INTO ... SELECT FROM ... 将数据从原表导入新分区表：

sql
INSERT INTO public.rumi_rmp_rating_partitioned (
  id, teacher_id, school_id, admin_reviewed_at, attendance_mandatory,
  clarity_rating_rounded, class_name, comment, course_type, date,
  difficulty_rating_rounded, flag_status, grade, helpful_rating_rounded,
  would_like_take_again, is_for_credit, is_for_online_class, mask_count,
  quality_rating, textbook_is_used, thumbs_down_total, thumbs_up_total,
  class_name_vector
)
SELECT
  id, teacher_id, school_id, admin_reviewed_at, attendance_mandatory,
  clarity_rating_rounded, class_name, comment, course_type, date,
  difficulty_rating_rounded, flag_status, grade, helpful_rating_rounded,
  would_like_take_again, is_for_credit, is_for_online_class, mask_count,
  quality_rating, textbook_is_used, thumbs_down_total, thumbs_up_total,
  class_name_vector
FROM public.rumi_rmp_rating;
  • 确保迁移过程中无数据丢失，并在迁移后验证数据完整性和一致性。

步骤三：切换表名 ​

  1. 备份原表
    在确认数据已经成功迁移且验证无误后，可以重命名原有表作为备份：
sql
ALTER TABLE public.rumi_rmp_rating RENAME TO rumi_rmp_rating_backup;
  1. 重命名新分区表
    将新分区表改名为原表名，以保持应用程序无需修改查询语句：
sql
ALTER TABLE public.rumi_rmp_rating_partitioned RENAME TO rumi_rmp_rating;

步骤四：重新创建约束和索引 ​

  1. 索引与约束
    在新的分区表及各子分区上重新创建必要的索引和约束。例如：
sql
CREATE INDEX idx_rumi_rmp_rating_teacher_id
  ON public.rumi_rmp_rating (teacher_id);

此外，根据需要在各子分区上设置其他索引和约束，以保证性能和数据完整性。

  1. 验证与测试
  • 运行典型查询，确保性能符合预期。
  • 测试插入、更新和删除操作，验证它们在分区表上的正确性和高效性。

3. 总结 ​

通过上述步骤：

  1. 创建新的分区父表及子分区。
  2. 迁移数据到新的分区表。
  3. 切换表名，将新分区表替换为原有表名。
  4. 在新表上重新创建索引和约束，并进行验证。

这种方法安全可靠，能够在生产环境中平稳地将非分区表转换为分区表，同时最小化对现有应用程序的影响。

4. 自动创建分区 ​

PostgreSQL 本身不支持在创建分区父表时自动生成子分区。每个子分区必须显式创建，指定其范围或列表值。您可以通过编写脚本或使用程序化手段（如 PL/pgSQL 脚本或其他自动化工具）来批量生成多个子分区的创建语句，但数据库不会自动根据父表定义生成子分区。

例如，您可以编写一个循环在 PL/pgSQL 中动态创建多个分区：

sql
DO $$
DECLARE
  start_val INTEGER := 0;
  end_val   INTEGER;
  step      INTEGER := 1000000;
  partition_name TEXT;
BEGIN
  WHILE start_val < 10000000 LOOP  -- 假设创建 10 个分区
    end_val := start_val + step;
    partition_name := format('rumi_rmp_rating_part_%s', start_val/step + 1);
    EXECUTE format(
      'CREATE TABLE public.%I PARTITION OF public.rumi_rmp_rating_partitioned
       FOR VALUES FROM (%s) TO (%s);',
      partition_name, start_val, end_val
    );
    start_val := end_val;
  END LOOP;
END
$$;

上述脚本根据设定的范围循环创建多个子分区。您可根据实际业务需求调整循环参数和范围。

5. 迁移数据时字段完全相同 ​

如果新分区表 rumi_rmp_rating_partitioned 与原表 rumi_rmp_rating 的结构完全一致，且字段顺序也相同，您可以简化迁移数据的 SQL 语句，而不必列出每个字段。可直接使用以下形式：

sql
INSERT INTO public.rumi_rmp_rating_partitioned
SELECT * FROM public.rumi_rmp_rating;

此语句将从原表中选择所有列的数据并插入到新分区表中。使用此简化形式的前提条件是：

  • 两个表的列完全一致且顺序相同。
  • 不需要对部分字段或特定数据做转换。

注意事项：

  • 尽管简化语句减少了代码量，但明确列出字段有助于防止因表结构不完全匹配导致的错误，尤其是在生产环境中。
  • 在实际操作前，建议备份数据并在测试环境中验证迁移流程，以确保数据完整性。。

六、总结 ​

在使用分区表后，绝大多数查询和插入 SQL 语句的写法与未分区时几乎相同。主要区别在于将操作对象换成父表名，PostgreSQL 底层会自动将数据路由到对应子分区，并利用分区裁剪优化查询。

通过引入分区表，可以在大数据量场景下进一步提升查询性能和系统的可维护性，而无需对应用层代码进行显著修改。开发人员应重点关注分区管理、索引策略以及定期维护，以确保系统持续高效运行。

MIT Licensed

Copyright © 2023-present litongjava