怎样在 PostgreSQL 中优化对大数值类型数据的计算？

在 PostgreSQL 中处理大数值类型数据（例如 BIGINT、NUMERIC 等）的计算时，可能会遇到性能瓶颈。为了优化这些计算，我们可以从多个方面入手，包括适当的数据类型选择、索引优化、查询语句的优化、数据库配置调整以及使用扩展功能等。

一、选择合适的数据类型

PostgreSQL 提供了多种数据类型来表示数值，例如 INTEGER、BIGINT、NUMERIC 等。选择合适的数据类型对于优化计算至关重要。

1. INTEGER：适用于较小的整数值，范围通常在 -2147483648 到 2147483647 之间。
2. BIGINT：用于更大的整数值，范围从 -9223372036854775808 到 9223372036854775807 。
3. NUMERIC：可以精确地表示任意精度的十进制数值，但在存储空间和计算性能上可能会有一定的开销。

如果能确定数值的范围并且不需要高精度，优先选择 INTEGER 或 BIGINT。例如，如果一个字段表示年龄，通常使用 INTEGER 就足够了，而如果是订单数量等可能很大的数值，可以选择 BIGINT。

下面是一个创建表并选择不同数据类型的示例：

CREATE TABLE users (
    age INTEGER,
    order_count BIGINT,
    total_amount NUMERIC(10, 2)
);

在上述示例中，age 字段使用 INTEGER 表示年龄，order_count 字段使用 BIGINT 表示订单数量，total_amount 字段使用 NUMERIC(10, 2) 表示带有两位小数的总金额。

二、索引优化

为涉及大数值类型数据的计算的列创建合适的索引可以显著提高查询性能。

1. 对于经常用于查询、连接和排序的大数值列，可以创建 B 树索引。

CREATE INDEX idx_order_count ON users (order_count);

2. 如果查询主要基于范围条件（例如大于、小于、在某个范围内），可以考虑使用索引来加速。例如，如果经常查询订单数量大于 1000 的用户，可以创建如下索引：

CREATE INDEX idx_order_count_range ON users (order_count) WHERE order_count > 1000;

需要注意的是，过多的索引会影响数据插入和更新的性能，因此应该谨慎创建索引。

三、查询语句优化

1. 避免不必要的计算
   在查询中，尽量避免对大数值列进行不必要的计算。例如，如果只需要比较大数值的大小，而不需要进行数学运算，就不要进行额外的计算。

-- 不好的示例：计算订单数量的平方并比较
SELECT * FROM users WHERE POWER(order_count, 2) > 1000000;

-- 好的示例：直接比较订单数量
SELECT * FROM users WHERE order_count > 1000;

2. 利用条件筛选减少数据量
   在进行复杂的计算之前，先使用条件筛选出尽量少的数据，然后再对筛选后的数据进行计算。

-- 不好的示例：先计算所有用户订单数量的平均值，再筛选出大于平均值的用户
SELECT * FROM users WHERE order_count > (SELECT AVG(order_count) FROM users);

-- 好的示例：先筛选出满足条件的用户，再计算这些用户订单数量的平均值
WITH eligible_users AS (
    SELECT * FROM users WHERE some_condition
)
SELECT * FROM eligible_users WHERE order_count > (SELECT AVG(order_count) FROM eligible_users);

3. 合理使用子查询和连接
   有时候，将复杂的计算分解为子查询或者使用适当的连接方式可以提高查询性能。

-- 不好的示例：在一个查询中进行多个复杂的关联和计算
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE (u.order_count + o.amount) > 10000;

-- 好的示例：将计算分解为子查询
WITH user_orders AS (
    SELECT u.id, u.order_count, SUM(o.amount) AS total_order_amount
    FROM users u
    JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.order_count
)
SELECT * FROM user_orders WHERE (order_count + total_order_amount) > 10000;

四、数据库配置调整

1. 调整 shared_buffers
   shared_buffers 是 PostgreSQL 用于缓存数据页的内存区域。增加 shared_buffers 的值可以提高数据库对经常访问的数据的缓存命中率，从而减少磁盘 I/O 操作，提高性能。

在 postgresql.conf 文件中，可以将 shared_buffers 设置为系统内存的 20% - 40% ，例如：

shared_buffers = 8GB

2. 调整 work_mem
   work_mem 决定了 PostgreSQL 在执行排序、哈希连接等操作时可用的内存大小。对于涉及大数值计算且可能需要进行大量内存操作的查询，可以适当增加 work_mem 的值。

work_mem = 64MB

但需要注意的是，为每个连接分配过大的 work_mem 可能会导致系统内存不足，因此应根据系统资源和实际工作负载进行调整。

五、使用扩展功能

1. pg_trgm 扩展
   如果需要对大数值数据进行模糊匹配或相似性搜索，可以使用 pg_trgm 扩展。

CREATE EXTENSION pg_trgm;

SELECT * FROM users
WHERE similarity(order_count::text, '1000') > 0.8;

2. postgres_fdw 外部数据包装器
   如果大数值数据分布在多个 PostgreSQL 数据库中，可以使用 postgres_fdw 扩展来进行分布式查询和计算。

-- 在本地数据库中创建外部服务器
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'emote_host', port '5432', dbname'remote_db');

-- 创建用户映射
CREATE USER MAPPING FOR local_user
SERVER remote_server
OPTIONS (user'remote_user', password'remote_password');

-- 创建外部表
CREATE FOREIGN TABLE remote_users (
    id BIGINT,
    order_count BIGINT
)
SERVER remote_server
OPTIONS (schema_name 'public', table_name 'users');

-- 进行分布式查询和计算
SELECT SUM(u1.order_count + u2.order_count) FROM users u1 JOIN remote_users u2 ON u1.id = u2.id;

六、示例

假设我们有一个名为 sales 的表，其中包含 customer_id（INTEGER）、product_id（INTEGER）、quantity（BIGINT）和 price（NUMERIC(10, 2)）等列，用于存储销售数据。

1. 查询购买数量最多的前 10 个客户

SELECT customer_id, SUM(quantity) as total_quantity
FROM sales
GROUP BY customer_id
ORDER BY total_quantity DESC
LIMIT 10;

为了优化这个查询，可以在 customer_id 和 quantity 列上创建复合索引：

CREATE INDEX idx_sales_customer_quantity ON sales (customer_id, quantity);

2. 计算所有销售的总金额

SELECT SUM(quantity * price) as total_amount
FROM sales;

3. 查询某个产品的销售数量大于 1000 的销售记录

SELECT * FROM sales WHERE product_id = 123 AND quantity > 1000;

对此查询，在 product_id 和 quantity 列上创建索引会有帮助：

CREATE INDEX idx_sales_product_quantity ON sales (product_id, quantity);

七、总结

优化 PostgreSQL 中对大数值类型数据的计算需要综合考虑数据类型的选择、索引的创建、查询语句的优化、数据库配置的调整以及扩展功能的使用。通过合理的优化策略，可以显著提高数据库的性能，确保在处理大数值数据的计算时能够快速高效地响应查询请求。

需要注意的是，每个数据库系统和应用场景都有其独特性，因此在实际应用中，需要根据具体的性能测试和分析来调整优化策略，以达到最佳的性能效果。

🎉相关推荐

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