一、引言
在数据库的世界里，我们常常需要对数据进行各种计算和处理。PostgreSQL 作为一款功能强大的开源关系型数据库，提供了多种方式来实现计算需求，其中生成列和表达式索引就是两个非常有用的特性。今天，我们就来深入探讨一下它们的应用场景和性能对比。
二、生成列
2.1 什么是生成列
生成列是 PostgreSQL 12 及以后版本引入的一个特性，它的值是通过表达式计算出来的，而不是手动插入或更新的。生成列可以分为两种类型：存储生成列和虚拟生成列。存储生成列会将计算结果存储在磁盘上，而虚拟生成列则不会存储，每次查询时都重新计算。
2.2 应用场景
生成列在很多场景下都非常有用，比如在电商系统中，商品的总价可以通过单价和数量计算得出，我们可以使用生成列来自动计算这个值。
-- 创建一个商品表，包含生成列
CREATE TABLE products (
id SERIAL PRIMARY KEY,
-- 商品名称
name VARCHAR(100),
-- 商品单价
price DECIMAL(10, 2),
-- 商品数量
quantity INTEGER,
-- 总价为存储生成列，通过 price * quantity 计算得出
total_price DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);
-- 插入数据
INSERT INTO products (name, price, quantity) VALUES ('iPhone', 999.99, 2);
-- 查询数据
SELECT * FROM products;
在这个例子中，当我们插入商品的单价和数量后，总价会自动计算并存储在 total_price 列中。这使得我们在查询时可以直接获取总价，而不需要每次都手动计算。
2.3 优缺点
优点
- 数据一致性：生成列的值是根据表达式自动计算的，避免了手动计算可能出现的错误，保证了数据的一致性。
- 查询优化：存储生成列可以减少查询时的计算量，提高查询性能。
缺点
- 存储成本：存储生成列会占用额外的磁盘空间，尤其是在数据量较大时，这可能会成为一个问题。
- 更新性能：当生成列依赖的列发生变化时，存储生成列需要更新磁盘上的数据，这会影响更新性能。
2.4 注意事项
- 表达式的确定性：生成列的表达式必须是确定性的，即对于相同的输入，总是返回相同的输出。例如，不能使用
NOW()这样的非确定性函数。 - 存储类型：需要根据实际情况选择存储生成列还是虚拟生成列。如果生成列的计算比较复杂，且查询频繁，可以考虑使用存储生成列；如果生成列的计算简单，且更新频繁，可以考虑使用虚拟生成列。
三、表达式索引
3.1 什么是表达式索引
表达式索引是在索引中存储表达式的计算结果，而不仅仅是列的值。当查询条件中包含该表达式时，数据库可以直接使用索引来加速查询，而不需要在查询时重新计算表达式。
3.2 应用场景
在一个博客系统中，我们经常需要根据文章的发布年份进行查询。这时，我们可以创建一个基于 date_trunc('year', published_at) 表达式的索引。
-- 创建一个博客文章表
CREATE TABLE blog_posts (
id SERIAL PRIMARY KEY,
-- 文章标题
title VARCHAR(200),
-- 文章发布时间
published_at TIMESTAMP
);
-- 插入一些数据
INSERT INTO blog_posts (title, published_at)
VALUES
('Post 1', '2023-01-01 10:00:00'),
('Post 2', '2022-05-15 14:30:00'),
('Post 3', '2023-07-20 09:15:00');
-- 创建表达式索引
CREATE INDEX idx_blog_posts_year ON blog_posts (date_trunc('year', published_at));
-- 查询 2023 年发布的文章
SELECT * FROM blog_posts WHERE date_trunc('year', published_at) = '2023-01-01';
在这个例子中，我们创建了一个基于 date_trunc('year', published_at) 表达式的索引，当我们查询 2023 年发布的文章时，数据库可以直接使用这个索引来加速查询。
3.3 优缺点
优点
- 查询性能提升：当查询条件中包含表达式时，表达式索引可以大大提高查询性能，减少查询时间。
- 灵活的索引：可以根据实际需求创建基于不同表达式的索引，满足各种复杂的查询场景。
缺点
- 索引维护成本：表达式索引需要额外的维护成本，当表中的数据发生变化时，索引也需要更新。
- 占用空间：表达式索引会占用额外的磁盘空间，尤其是在表达式比较复杂时。
3.4 注意事项
- 查询匹配：查询条件中的表达式必须与索引中的表达式完全匹配，数据库才能使用该索引。
- 索引 selectivity：需要确保表达式索引的 selectivity 足够高，即索引能够筛选出较少的数据，这样才能真正提高查询性能。
四、性能对比
4.1 测试环境
为了进行性能对比，我们使用一个包含 100 万条记录的表，分别测试生成列和表达式索引在不同查询场景下的性能。
4.2 测试场景
场景一：查询包含生成列或表达式的结果
-- 使用生成列的查询
-- 假设我们有一个包含生成列的表 products
SELECT * FROM products WHERE total_price > 1000;
-- 使用表达式索引的查询
-- 假设我们有一个包含表达式索引的表 blog_posts
SELECT * FROM blog_posts WHERE date_trunc('year', published_at) = '2023-01-01';
在这个场景下，表达式索引通常比生成列更快，因为表达式索引可以直接使用索引来筛选数据，而生成列可能需要扫描整个表。
场景二：更新包含生成列或表达式依赖列的数据
-- 更新生成列依赖的列
UPDATE products SET price = 100 WHERE id = 1;
-- 更新表达式索引依赖的列
UPDATE blog_posts SET published_at = '2024-01-01' WHERE id = 1;
在这个场景下，生成列的更新性能可能会比表达式索引差，因为存储生成列需要更新磁盘上的数据，而表达式索引只需要更新索引。
4.3 总结性能对比
- 在查询性能方面，当查询条件中包含表达式时，表达式索引通常比生成列更快。
- 在更新性能方面，生成列的更新性能可能会比表达式索引差，尤其是存储生成列。
- 在存储成本方面，生成列的存储生成列会占用额外的磁盘空间，而表达式索引也会占用一定的空间，但通常比存储生成列小。
五、总结
生成列和表达式索引都是 PostgreSQL 中非常有用的特性，它们各自有不同的应用场景和优缺点。
生成列适合在需要保证数据一致性，且查询时需要直接获取计算结果的场景下使用。我们可以根据计算复杂度和更新频率来选择存储生成列或虚拟生成列。
表达式索引适合在查询条件中包含表达式，需要快速筛选数据的场景下使用。但需要注意索引的维护成本和占用空间。
在实际应用中，我们需要根据具体的业务需求和数据特点来选择合适的方法，以达到最佳的性能和数据管理效果。
评论