在 PostgreSQL 里如何处理数据的多版本并发控制（MVCC）导致的性能开销？

PostgreSQL 中的多版本并发控制（MVCC）是一种用于处理并发事务访问数据的机制，它在提高并发性能和数据一致性方面具有显著优势，但在某些情况下也可能导致性能开销。以下将详细探讨 MVCC 性能开销的来源以及相应的处理方法。

一、MVCC 机制概述

MVCC 允许并发事务读取数据的不同版本，从而避免读操作被写操作阻塞。当一个事务修改数据时，PostgreSQL 不会直接覆盖原有数据，而是创建一个新的版本。每个版本都带有事务 ID 和其他相关元数据。读事务根据其自身的事务隔离级别和可见性规则来确定读取哪个版本的数据。

二、MVCC 导致性能开销的原因

（一）存储空间增加

由于每个修改都会创建一个新的数据版本，随着时间的推移，数据库中的数据版本数量可能会大量增加，从而导致存储空间的消耗。特别是在频繁更新的表中，可能会积累大量的过时版本。

示例：假设有一个频繁更新的订单表，每天都有大量的订单状态更新。随着时间的推移，每个订单记录可能会有多个版本，占用更多的磁盘空间。

（二）索引维护成本

对于带有索引的表，每次数据版本的更新都可能需要对索引进行相应的修改。这增加了索引维护的开销，特别是在高并发环境下，大量的并发更新可能使索引频繁调整。

示例：例如一个用户表，按照用户 ID 建立了索引。当用户信息频繁更新时，索引中的对应条目也需要不断调整。

（三）旧版本清理开销

PostgreSQL 会周期性地清理不再需要的旧版本数据。这个清理过程（称为 VACUUM）本身也需要消耗系统资源，尤其是在数据量庞大且更新频繁的情况下。

示例：如果一个表一直有大量的更新和插入操作，但没有及时进行 VACUUM 操作，可能会导致表的膨胀和性能下降。

（四）读操作的复杂性

在读取数据时，需要根据事务隔离级别和可见性规则来确定读取哪个版本的数据。这增加了读操作的复杂性和处理时间，尤其是在复杂的查询和多表关联的情况下。

示例：当执行一个涉及多个表并且具有特定事务隔离级别的复杂查询时，PostgreSQL 需要花费更多的时间来确定每个表的可见数据版本。

三、处理 MVCC 性能开销的解决方案

（一）合理设计表结构和索引

1. 选择合适的数据类型
   • 使用最适合实际数据范围和业务需求的数据类型，避免过度占用空间。例如，如果一个字段的取值范围很小，使用较小的数据类型（如 SMALLINT 而不是 INTEGER ）可以减少存储空间的消耗，从而减少 MVCC 带来的版本存储开销。
2. 精简表结构
   • 避免不必要的列，尤其是那些很少更新或很少使用的大字段。只包含真正需要的字段可以降低版本存储和更新的成本。
3. 优化索引
   • 只创建必要的索引，过多的索引会增加写操作的开销。对于经常用于查询、连接和排序的列创建索引，但要避免过度索引。例如，如果一个表主要通过某个字段进行查询，可以为该字段创建索引；但如果很少基于另一个字段进行查询，就没必要为其创建索引。
   • 定期检查和评估索引的使用情况，删除不再使用或很少使用的索引。

示例：
假设有一个员工表 employees ，包含 id 、 name 、 age 、 department_id 、 salary 和一个大的 description 字段（用于存储详细的员工描述，通常很少被查询和更新）。如果业务中很少查询和更新 description 字段，那么可以考虑在设计表结构时不包含该字段，或者将其存储在单独的关联表中，以减少 MVCC 版本存储的开销。

对于索引优化，如果发现表中的 age 字段很少用于查询和条件过滤，而之前为其创建了索引，就可以删除该索引来减少更新操作时的开销。

（二）调整事务隔离级别

1. 理解事务隔离级别的影响
   • PostgreSQL 支持多种事务隔离级别，包括 Read Uncommitted 、 Read Committed 、 Repeatable Read 和 Serializable 。默认的隔离级别是 Read Committed 。不同的隔离级别对 MVCC 的可见性规则和并发控制有不同的影响。
2. 选择适当的隔离级别
   • 根据应用程序的需求和并发特性，选择最适合的隔离级别。如果应用程序对并发性要求较高，并且可以容忍一定程度的数据不一致性，可以考虑使用较低的隔离级别，如 Read Committed 。对于对数据一致性要求非常严格的场景，可能需要使用更高的隔离级别，但要注意其对性能的影响。

示例：
假设一个在线论坛应用，用户在发布帖子时会立即显示在论坛页面上，但其他用户可能会看到尚未完全提交的帖子内容短暂的时间。在这种情况下，可以将事务隔离级别设置为 Read Uncommitted ，以提高并发性能，因为不需要保证读取到的数据是完全提交的稳定版本。

如果是一个银行交易系统，对数据的一致性要求极高，每一笔交易都必须准确无误，此时应使用 Serializable 隔离级别，但要承受其可能带来的性能开销，并通过其他优化手段来缓解。

（三）定期执行 VACUUM 操作

1. 手动 VACUUM
   • 对于频繁更新的大型表，可以定期手动执行 VACUUM 或 VACUUM FULL 操作。 VACUUM 回收已删除或过时的数据版本所占用的存储空间，并更新可见性信息； VACUUM FULL 会重新组织表的物理存储，以达到更好的空间利用，但它会阻塞对表的并发访问，因此通常在维护窗口中使用。
2. 自动 VACUUM
   • 配置 PostgreSQL 使其自动执行 VACUUM 操作。可以调整相关的参数，如 autovacuum_vacuum_scale_factor 和 autovacuum_vacuum_threshold ，来控制自动 VACUUM 操作的触发条件和频率。

示例：
对于一个大型的订单处理系统中的订单表 orders ，如果每天都有大量的订单更新和删除操作，可以在每天业务低谷期手动执行 VACUUM 操作来清理过时的版本和回收空间。

如果系统中有很多小表并且更新频繁，可以通过调整自动 VACUUM 的参数，使其更积极地清理这些表，例如将 autovacuum_vacuum_scale_factor 设置为一个较小的值，以更频繁地触发自动 VACUUM 操作。

（四）分区表

1. 表分区策略
   • 根据数据的特征和访问模式，将大型表分割为多个较小的分区。例如，可以按照时间范围、地域或其他具有明确边界的业务规则进行分区。
2. 分区的好处
   • 分区可以将数据的读写和清理操作限制在特定的分区上，减少 MVCC 对整个表的影响范围。同时，对于只涉及某个分区的操作，性能会得到提升。

示例：
假设有一个销售订单表 sales_orders ，其中包含多年的订单数据。可以按照年份将其分为多个分区表，如 sales_orders_2020 、 sales_orders_2021 等。当查询或更新特定年份的订单数据时，只会影响对应的分区表，减少了 MVCC 的处理范围和开销。

如果需要清理过期的订单数据，如删除两年前的订单，只需针对相应的分区表进行操作，而不是对整个大表进行全表扫描和处理。

（五）批量操作和事务优化

1. 批量处理数据更新
   • 尽量将多个相关的小更新操作组合成一个批量操作，减少事务的数量和频率。这样可以降低 MVCC 版本创建和索引维护的开销。
2. 控制事务大小
   • 避免在事务中处理过多的数据或执行过长时间的操作。将大型事务分解为较小的逻辑单元，以便更快地提交并释放资源。

示例：
假设要更新一批用户的状态信息，而不是逐个用户进行更新并提交事务，可以在一个事务中一次性处理多个用户的更新操作。

如果有一个复杂的数据处理流程，需要从多个表读取数据、进行计算并更新结果，将其分解为多个较小的事务，每个事务完成一部分相对独立和明确的操作，然后及时提交。

（六）监控和性能分析

1. 数据库指标监控
   • 使用 PostgreSQL 提供的监控工具和扩展，如 pg_stat_activity 、 pg_stat_bgwriter 、 pg_stat_user_tables 等，持续监控数据库的活动和性能指标。
2. 分析查询计划
   • 对于性能关键的查询，使用 EXPLAIN 命令分析查询计划，了解查询的执行步骤和资源使用情况。根据分析结果，进行索引优化、表结构调整或查询重写。
3. 定期性能审计
   • 定期对数据库进行性能审计，识别可能存在的性能瓶颈和开销来源，及时采取相应的优化措施。

示例：
通过定期查看 pg_stat_user_tables 视图，可以了解每个表的插入、更新、删除和查询的频率，以及表的大小和空间使用情况。如果发现某个表的更新操作异常频繁，且空间增长过快，就需要进一步分析和优化。

对于一个执行缓慢的复杂查询，使用 EXPLAIN 可以查看是否存在全表扫描、不合理的索引使用或其他导致性能低下的步骤。例如，如果发现查询计划中对一个大表进行了全表扫描，而该表有合适的索引但未被使用，就需要检查查询条件和索引的定义是否匹配。

四、示例演示

为了更直观地理解如何处理 MVCC 导致的性能开销，下面通过一个具体的示例来展示优化过程。

假设我们有一个 sales 表，用于记录销售交易信息，表结构如下：

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    customer_id INT,
    sale_amount DECIMAL(10, 2),
    sale_date DATE
);

这个表经常有插入和更新操作，随着时间的推移，可能会出现性能问题。

性能问题表现

我们发现随着数据量的增加和业务的增长，以下操作的性能开始下降：

1. 频繁的小批量插入新销售记录。
2. 更新特定客户的销售记录。

分析和优化

1. 合理设计表结构和索引

   • 为 customer_id 字段添加索引，以提高基于客户的查询和更新性能：

   CREATE INDEX idx_sales_customer_id ON sales(customer_id);
   

   • 考虑将 sale_amount 的数据类型从 DECIMAL(10, 2) 调整为更合适的类型，如果精度要求不高，可以改为 FLOAT ，减少存储空间和更新开销。

2. 调整事务隔离级别

   • 如果应用程序可以接受一定程度的读未提交数据，将事务隔离级别从默认的 Read Committed 调整为 Read Uncommitted ：

   SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   

   但要注意这可能会导致读取到未完全提交的数据。

3. 定期执行 VACUUM 操作

   • 在业务低峰期手动执行 VACUUM 操作：

   VACUUM ANALYZE sales;
   

4. 分区表

   • 假设按照年度对销售数据进行分区，可以创建以下分区表：

   CREATE TABLE sales_2022 (
       CHECK (sale_date >= '2022-01-01' AND sale_date < '2023-01-01')
   ) INHERITS (sales);
   
   CREATE TABLE sales_2023 (
       CHECK (sale_date >= '2023-01-01' AND sale_date < '2024-01-01')
   ) INHERITS (sales);
   

   然后在插入数据时，根据 sale_date 字段的值将数据插入到相应的分区表中。

优化后的性能评估

在实施上述优化措施后，通过以下方式评估性能改进：

1. 执行相同的插入和更新操作，并记录执行时间。
2. 使用 EXPLAIN 分析优化后的查询计划，确保操作使用了预期的索引和执行路径。
3. 监控数据库的资源使用情况，例如 CPU 利用率、内存使用和磁盘 I/O ，以确认整体性能的提升。

通过示例演示，可以清晰地看到如何针对具体的性能问题应用前面提到的优化解决方案，以有效地处理 MVCC 带来的性能开销。

处理 PostgreSQL 中 MVCC 导致的性能开销需要综合考虑表结构设计、事务隔离级别设置、定期清理、分区策略以及性能监控等多个方面。通过合理的优化措施，可以在充分利用 MVCC 优势的同时，最小化其对性能的负面影响，确保数据库系统高效稳定地运行。

🎉相关推荐

• 🍅关注博主🎗️ 带你畅游技术世界，不错过每一次成长机会！
• 📚领书：PostgreSQL 入门到精通.pdf
• 📙PostgreSQL 中文手册
• 📘PostgreSQL 技术专栏