针对大数据量的分析函数优化：索引和分区

当数据变得很多（就像公司群里催ddl的消息一样多），查询和处理的速度就会慢下来。主要原因有这些：

1. 没有索引。当PostgreSQL执行查询时不得不全表扫描（这叫"Seq Scan"——顺序扫描），那查询就会明显变慢。
2. SQL查询写得不高效。如果写查询时没考虑优化，就算有索引你也可能遇到生产上的麻烦。比如忘了在WHERE里加关键条件？那就等着慢慢跑吧。
3. 一张表里数据太多。比如你想一次性分析所有年份的销售数据，这时候就算有索引也救不了你。

别慌，我们有两个靠谱的办法搞定：索引和分区。

用索引加速查询

来看个简单的建索引例子：

CREATE INDEX idx_sales_date ON sales(transaction_date);

• 这里idx_sales_date是索引名（你可以随便起，但最好有点意义）。
• ON sales(transaction_date)——指定在哪个表哪个字段上建索引。

如果你经常按transaction_date字段过滤，这个索引就特别有用。

下面这个查询就能用上这个索引：

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

多字段索引

如果你的查询经常用到多个字段，比如region和product_id，可以考虑建个复合索引：

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

现在像下面这样的查询会快很多：

SELECT *
FROM sales
WHERE region = '北美' AND product_id = 42;

唯一索引

唯一索引不仅查找快，还能保证字段值唯一。比如：

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

这样你就不会不小心加两个邮箱一样的客户了。

分析函数的索引

有些数据分析函数，比如SUM、COUNT或AVG，可以用索引加速。比如：

CREATE INDEX idx_sales_amount ON sales(amount);

查询：

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

因为有索引，执行会更快。

大数据量下的表分区

表分区就是把一张大表拆成更小的逻辑部分，叫分区。比如你可以按年份把sales表拆成sales_2021、sales_2022等等。

觉得很难？其实PostgreSQL做起来比你想象的简单。

分区类型

1. 范围分区（Range Partitioning）。按范围分，比如按日期。
2. 列表分区（List Partitioning）。按具体值分，比如按地区。
3. 哈希分区（Hash Partitioning）。用哈希函数分（手动用得少）。

创建分区表

来建个按年份分区的销售表：

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

现在给不同年份建分区：

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

按日期过滤的查询会自动只查对应分区。你可以用EXPLAIN命令验证。

分区例子

比如只统计2021年销售额的查询：

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

你看，PostgreSQL只查sales_2021分区，不会全表扫描。

例子：按地区优化统计

假设你想算每个地区的销售总额。没索引没分区的话会慢到爆。先给region字段建个索引：

CREATE INDEX idx_sales_region ON sales(region);

你的查询：

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

有了索引，处理速度就快多了。

例子：时间数据分区

像交易或日志这种时间数据，可以按月分区。比如：

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

查询：

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

会更快，因为PostgreSQL只查sales_jan_2023分区。

例子：索引和分区一起用

索引和分区可以一起用，性能更好。比如你可以在每个分区里建索引。例子：

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

怎么避免常见错误

很多性能问题都是索引和分区用错了。比如：

• 索引太多会让插入变慢。
• 分区要设计得均匀，太小或太大都会拖慢性能。
• 优化前不做性能分析（EXPLAIN ANALYZE），就像修车不看发动机一样。

一定要检查你的优化到底有没有提速，别怕多试试。