{"position":0,"title":"介绍","layout":"doc-fullscreen","text":"## 介绍nn在本实验中，你将学习如何在 PostgreSQL 中实现表分区。目标是将一个大型表划分为更小、更易于管理的部分，这可以显著提高查询性能，并简化数据管理任务，如备份或归档。nn你将首先创建一个用于分区的“父”表。然后，你将定义几个“子”表，即分区，每个分区存储特定日期范围内的数据。最后，你将向父表插入数据，并观察 PostgreSQL 如何自动将其路由到正确的分区。你还将学习如何查询分区表，并了解 PostgreSQL 如何通过仅访问相关分区来优化这些查询。n","need_verify":false,"has_solution":false},{"position":1,"title":"创建父分区表","layout":"doc-workbench-split","text":"## 创建父分区表nn在此步骤中，你将创建 `sales` 主表，它将作为我们分区的父表。此表定义了其所有分区的结构，但本身不存储任何数据。nn首先，你需要连接到 PostgreSQL 数据库。打开终端，使用以下命令以 `postgres` 用户身份启动 `psql` 交互式 shell：nn```bashnsudo -u postgres psqln```nn现在你应该看到 PostgreSQL 提示符，它看起来像 `postgres=#`。本实验中后续的所有 SQL 命令都将在此提示符下运行。nn接下来，创建 `sales` 表。此表将按 `sale_date` 列进行范围分区。nn```sqlnCREATE TABLE sales (n sale_id SERIAL,n sale_date DATE NOT NULL,n product_id INTEGER, sale_amount DECIMAL(10, 2), PRIMARY KEY (sale_id, sale_date) ) PARTITION BY RANGE (sale_date); ``` 这条命令可以拆开来看： - `CREATE TABLE sales (...)`：先把销售数据表需要的字段定义出来。 - `PRIMARY KEY (sale_id, sale_date)`：对于分区表来说，主键里必须带上分区列，这里的分区列就是 `sale_date`。 - `PARTITION BY RANGE (sale_date)`：这才是核心。它表示这张表会按照 `sale_date` 字段，采用 `RANGE` 方式进行分区。 命令执行完成后，通常会看到一条 `CREATE TABLE` 的确认信息。 如果想确认表是否真的建好了，可以在 `psql` 里用 `\d` 查看表结构。 ```sql \d sales ``` 输出结果会列出这张表的各个字段，并在底部明确标注它是一个 “Partitioned table”（分区表），同时显示对应的 “Partition key”（分区键）。 ``` Table "public.sales" Column | Type | Collation | Nullable | Default -------------+---------------+-----------+----------+------------------------------------------------ sale_id | integer | | not null |extval('sales_sale_id_seq'::regclass)n sale_date | date | | not null |n product_id | integer | | |n sale_amount | numeric(10,2) | | |nPartition key: RANGE (sale_date)nIndexes:n "sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)nNumber of partitions: 0n```nn请注意，“Number of partitions”（分区数量）为 0。你将在下一步创建实际的分区。n","need_verify":true,"has_solution":false},{"position":2,"title":"定义日期范围分区","layout":"doc-workbench-split","text":"## 定义日期范围分区nn现在你已经有了父表 `sales`，你需要创建实际存储数据的分区。每个分区将存储特定日期范围内的数据。在此步骤中，你将为 2023 年和 2024 年创建季度分区。nn你应该仍然在 `psql` 交互式终端中。nn首先，为 2023 年创建四个分区。每个命令都将一个新表定义为 `sales` 的分区，并指定它将覆盖的日期范围。nn```sqlnCREATE TABLE sales_2023_q1 PARTITION OF salesn FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');nnCREATE TABLE sales_2023_q2 PARTITION OF salesn FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');nnCREATE TABLE sales_2023_q3 PARTITION OF salesn FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');nnCREATE TABLE sales_2023_q4 PARTITION OF salesn FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');n```nn`FOR VALUES FROM ... TO ...` 子句定义了每个分区的范围。下限是包含的，上限是不包含的。例如，`sales_2023_q1` 将存储 `sale_date` 从 `2023-01-01` 到但不包括 `2023-04-01` 的记录。nn接下来，使用相同的季度方案为 2024 年创建分区：nn```sqlnCREATE TABLE sales_2024_q1 PARTITION OF salesn FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');nnCREATE TABLE sales_2024_q2 PARTITION OF salesn FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');nnCREATE TABLE sales_2024_q3 PARTITION OF salesn FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');nnCREATE TABLE sales_2024_q4 PARTITION OF salesn FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');n```nn在运行每个 `CREATE TABLE` 命令后，你将看到一个确认消息。nn要验证所有分区是否已创建，你可以再次列出数据库中的表。nn```sqln\dtn```nn现在，你应该在输出中看到父表 `sales` 以及你刚刚创建的所有八个分区（`sales_2023_q1`、`sales_2023_q2` 等）。n","need_verify":true,"has_solution":false},{"position":3,"title":"插入和路由数据","layout":"doc-workbench-split","text":"## 插入和路由数据nn在此步骤中，你将插入示例数据。分区的一个关键特性是，你直接将数据插入父表（`sales`），PostgreSQL 会根据分区键（`sale_date`）的值自动将每一行路由到正确的分区。nn你应该仍然在 `psql` 交互式终端中。nn执行以下 `INSERT` 语句，添加 16 条跨越 2023 年和 2024 年的示例销售记录：nn```sqlnINSERT INTO sales (sale_date, product_id, sale_amount) VALUESn('2023-01-15', 101, 50.00),n('2023-02-20', 102, 75.50),n('2023-04-10', 103, 100.00),n('2023-05-25', 104, 60.25),n('2023-07-01', 105, 120.00),n('2023-08-12', 106, 80.75),n('2023-10-05', 107, 90.00),n('2023-11-18', 108命令执行完成后，终端会显示 `INSERT 0 16`，这就说明 16 行数据已经成功写入。 接下来，可以通过查询各个分区来确认数据是否被正确路由。先看一下 2023 年第一季度的记录数： ```sql SELECT COUNT(*) FROM sales_2023_q1; ``` 预期输出如下： ```sql count ------- 2 (1 row) ``` 然后再检查 2024 年第四季度的记录数： ```sql SELECT COUNT(*) FROM sales_2024_q4; ``` 输出同样应为 `2`。这就说明 PostgreSQL 确实已经把数据准确分发到了对应的基础分区表中。 查询数据并分析性能 最后一步，需要查询分区表 `sales`。分区最核心的优势之一，就是“分区裁剪”（partition pruning）。说白了，PostgreSQL 的查询规划器会自动判断哪些分区真正有必要扫描，只访问相关分区，尽量避开对整张大表的全表扫描。 此时，你应该仍然停留在 `psql` 交互式终端中。nn首先，运行一个查询来检索 2023 年第一季度的所有销售记录。nn```sqlnSELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';n```nn你将看到落入此日期范围内的两条记录。要查看 PostgreSQL 如何优化此查询，你可以使用 `EXPLAIN` 命令，它会显示查询的执行计划。nn```sqlnEXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';n```nn输出将类似于：nn```n QUERY PLANn------------------------------------------------------------------------------------n Seq Scan on sales_2023_q1 sales (cost=0.00..31.75 rows=7 width=28)n Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))n(2 rows)n```nn注意 `Seq Scan on sales_2023_q1` 这一行。这证明了 PostgreSQL 只扫描了 `sales_2023_q1` 分区，而忽略了其他七个分区，这在大数据集上可以大大加快查询速度。nn现在，让我们运行一个更复杂的查询，以查找 2024 年每个产品的总销售额。nn```sqlnSELECT product_id, SUM(sale_amount) AS total_salesnFROM salesnWHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'nGROUP BY product_idnORDER BY product_id;n```nn此查询将有效地只扫描 2024 年的四个分区来计算结果。输出将显示从 109 到 116 的每个产品的总销售额。nn最后，你可以通过输入以下命令退出 PostgreSQL 交互式终端：nn```sqln\qn```nn你将返回到常规的 shell 提示符。n","need_verify":true,"has_solution":false},{"position":5,"title":"总结","layout":"doc-fullscreen","text":"## 总结nn在本实验中，你学习了 PostgreSQL 表分区的基本知识。你成功创建了一个按日期范围分区的父表，为不同的时间段定义了具体的分区，并插入了自动路由到正确分区的数据。最重要的是，你使用了 `EXPLAIN` 命令来观察分区裁剪（partition pruning）的实际效果，展示了分区如何通过允许数据库只扫描部分数据来显著提高查询性能。这是一种高效管理大规模数据集（large-scale datasets）的强大技术。n","need_verify":false,"has_solution":false}