PostgreSQL教程FG009-PG高级特性初认知：索引/事务/全文检索入门
本文详细介绍PostgreSQL的三个核心高级特性：索引、事务和全文检索。通过学习本文，读者可以初步了解这些特性的基本概念、工作原理和使用方法，为后续深入学习打下基础。风哥教程参考PostgreSQL官方文档Indexing, Transaction Processing, Full Text Search等内容。
本文档风哥主要介绍PG高级特性初认知：索引/事务/全文检索入门相关内容。风哥教程参考PostgreSQL官方文档Indexes, Transactions, Full Text Search内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
本文档初步介绍PostgreSQL的高级特性，包括索引类型、事务处理、全文检索等功能，帮助读者了解PG的核心能力。风哥教程参考PostgreSQL官方文档Indexes, Transactions, Full Text Search内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 PostgreSQL高级特性概述
PostgreSQL提供了丰富的高级特性，包括索引、事务、全文检索、JSON支持、空间数据、分区表等。这些特性使得PostgreSQL能够满足各种复杂的业务需求和高性能应用场景。学习交流加群风哥微信: itpux-com
1.2 高级特性的核心价值
高级特性的核心价值在于：
– 提高查询性能（索引）
– 确保数据一致性和可靠性（事务）
风哥提示：
– 支持复杂的数据检索需求（全文检索）
– 适应多样化的数据存储需求（JSON、空间数据等）
Part02-索引基础
2.1 索引基本概念
索引是一种用于加速数据库查询的数据结构，类似于书籍的目录。它可以帮助数据库系统快速定位到需要查询的数据，而不需要扫描整个表。
2.2 索引类型介绍
PostgreSQL支持多种索引类型：
– B-tree：最常用的索引类型，适用于等值查询和范围查询
– Hash：适用于等值查询，但不支持范围查询
– GiST：通用搜索树索引，适用于空间数据等复杂数据类型
– GIN：通用倒排索引，适用于数组、JSON等数据类型
– SP-GiST：空间分区GiST索引，适用于具有自然分区结构的数据
– BRIN：块范围索引，适用于大数据量的顺序数据
2.3 索引创建与使用
使用CREATE INDEX命令创建索引，数据库系统会自动选择合适的索引进行查询优化。
# su – pgsql $ psql -d fgedudb
# 创建测试表
fgedudb=# CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10,2) NOT NULL,
stock_quantity INTEGER DEFAULT 0,
description TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE fgedu_# 插入测试数据
fgedudb=# INSERT INTO fgedu_products (product_name, category, price, stock_quantity, description)
SELECT
‘产品’ || i,
CASE WHEN i % 5 = 0 THEN ‘电子设备’
WHEN i % 5 = 1 THEN ‘图书’
WHEN i % 5 = 2 THEN ‘服装’
WHEN i % 5 = 3 THEN ‘食品’
ELSE ‘其他’ END,
10.00 + i * 2.50,
i * 2,
‘这是产品’ || i || ‘的详细描述，包含各种信息。’ || repeat(‘测试文本’, i % 10)
FROM generate_series(1, 10000) AS i;
INSERT 0 10000
# 创建B-tree索引
fgedudb=# CREATE INDEX idx_fgedu_products_category ON fgedu_products(category);
CREATE INDEX
# 创建GIN索引
fgedudb=# CREATE INDEX idx_fgedu_products_description ON fgedu_products USING GIN (to_tsvector(‘english’, description));
CREATE INDEX
# 查看索引列表
fgedudb=# \di
List of relations
Schema | Name | Type | Owner | Table
——–+———————————–+——-+———-+—————-
public | fgedu_products_pkey | index | pgsql | fgedu_products
public | idx_fgedu_products_category | index | pgsql | fgedu_products
public | idx_fgedu_products_description | index | pgsql | fgedu_products
(3 rows)
Part03-事务管理
3.1 事务基本概念
事务是一组原子性的SQL操作，要么全部执行成功，要么全部失败回滚。事务可以确保数据的一致性和可靠性。
3.2 ACID特性详解
PostgreSQL事务支持ACID特性：
– 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败
– 一致性（Consistency）：事务执行前后，数据库的状态保持一致
– 隔离性（Isolation）：多个事务并发执行时，相互之间不会产生干扰
– 持久性（Durability）：事务一旦提交，其结果将永久保存到数据库中
3.3 事务隔离级别
PostgreSQL支持四种事务隔离级别：
– READ UNCOMMITTED：允许读取未提交的数据（脏读）
– READ COMMITTED：只能读取已提交的数据（默认级别）
– REPEATABLE READ：同一事务中多次读取相同的数据，结果一致
– SERIALIZABLE：最高隔离级别，确保事务串行执行
3.4 事务操作实战
使用BEGIN、COMMIT、ROLLBACK等命令管理事务。
fgedudb=# SHOW transaction_isolation;
transaction_isolation
———————–
read committed
(1 row)
# 设置事务隔离级别
fgedudb=# SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
# 开始事务
fgedudb=# BEGIN;
BEGIN
# 执行SQL操作
fgedudb=# INSERT INTO fgedu_products (product_name, category, price, stock_quantity, description)
VALUES (‘测试产品’, ‘电子设备’, 99.90, 100, ‘这是一个测试产品’);
INSERT 0 1
fgedudb=# UPDATE fgedu_products SET price = price * 1.1 WHERE category = ‘电子设备’ AND product_id > 10000;
UPDATE 2001
# 查看事务中的修改
fgedudb=# SELECT COUNT(*) FROM fgedu_products WHERE category = ‘电子设备’ AND price > 200;
count
——-
400
(1 row)
# 提交事务
fgedudb=# COMMIT;
COMMIT
# 查看提交后的结果
fgedudb=# SELECT COUNT(*) FROM fgedu_products WHERE category = ‘电子设备’ AND price > 200;
count
——-
400
(1 row)
# 回滚示例
fgedudb=# BEGIN;
BEGIN
fgedudb=# DELETE FROM fgedu_products WHERE product_id > 10000;
DELETE 2001
fgedudb=# ROLLBACK;
ROLLBACK
# 查看回滚后的结果
fgedudb=# SELECT COUNT(*) FROM fgedu_products WHERE product_id > 10000;
count
——-
2001
(1 row)
Part04-全文检索入门
4.1 全文检索基本概念
全文检索是一种用于搜索文本内容的技术，可以快速定位包含特定关键词的文档。PostgreSQL提供了强大的全文检索功能，支持多语言、词干提取、同义词等高级特性。
4.2 全文检索数据类型
PostgreSQL全文检索使用两种主要的数据类型：
from oracle:www.itpux.com
– tsvector：存储文本的倒排索引表示
– tsquery：表示查询条件
4.3 全文检索操作
使用to_tsvector、to_tsquery、@@等函数和操作符进行全文检索。
fgedudb=# SELECT to_tsvector(‘english’, ‘This is a test document for full-text search.’) AS vector;
vector
———————————————————————
‘full’:6 ‘search’:8 ‘test’:4 ‘text’:7 ‘document’:5
(1 row)
fgedudb=# SELECT to_tsquery(‘english’, ‘test & document’) AS query;
query
——————–
‘test’ & ‘document’
(1 row)
# 使用全文检索查询
fgedudb=# SELECT product_id, product_name, description
FROM fgedu_products
WHERE to_tsvector(‘english’, description) @@ to_tsquery(‘english’, ‘test & description’)
LIMIT 5;
product_id | product_name | description
————+————–+———————————————————————————–
1 | 产品1 | 这是产品1的详细描述，包含各种信息。测试文本
2 | 产品2 | 这是产品2的详细描述，包含各种信息。测试文本测试文本
3 | 产品3 | 这是产品3的详细描述，包含各种信息。测试文本测试文本测试文本
4 | 产品4 | 这是产品4的详细描述，包含各种信息。测试文本测试文本测试文本测试文本
5 | 产品5 | 这是产品5的详细描述，包含各种信息。测试文本测试文本测试文本测试文本测试文本
(5 rows)
# 使用全文检索索引提高性能
fgedudb=# EXPLAIN ANALYZE SELECT product_id, product_name, description
FROM fgedu_products
WHERE to_tsvector(‘english’, description) @@ to_tsquery(‘english’, ‘test & description’);
QUERY PLAN
———————————————————————————————————————-
Bitmap Heap Scan on fgedu_products (cost=36.18..394.85 rows=50 width=68) (actual time=0.131..1.428 rows=10000 loops=1)
Recheck Cond: (to_tsvector(‘english’::regconfig, description) @@ to_tsquery(‘english’::regconfig, ‘test & description’::text))
Heap Blocks: exact=1188
-> Bitmap Index Scan on idx_fgedu_products_description (cost=0.00..36.17 rows=50 width=0) (actual time=0.082..0.083 rows=10000 loops=1)
Index Cond: (to_tsvector(‘english’::regconfig, description) @@ to_tsquery(‘english’::regconfig, ‘test & description’::text))
Planning Time: 0.243 ms
Execution Time: 1.812 ms
(7 rows)
Part05-实战操作与示例
5.1 索引实战
索引实战示例，包括索引创建、查询优化和索引管理。
fgedudb=# CREATE TABLE fgedu_fgedus (
fgedu_id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
join_date DATE,
last_login TIMESTAMP
);
CREATE TABLE fgedu_# 2. 插入测试数据
fgedudb=# INSERT INTO fgedu_fgedus (fgeduname, email, first_name, last_name, department, join_date, last_login)
SELECT
‘fgedu’ || i,
‘fgedu’ || i || ‘@fgedu.net.cn’,
‘名’ || i,
‘姓’ || i,
CASE WHEN i % 4 = 0 THEN ‘技术部’
WHEN i % 4 = 1 THEN ‘销售部’
WHEN i % 4 = 2 THEN ‘人事部’
ELSE ‘财务部’ END,
‘2023-01-01’::date + (i % 365)::int,
‘2026-04-01’::timestamp – (i % 1000)::int * interval ‘1 hour’
FROM generate_series(1, 50000) AS i;
INSERT 0 50000
# 3. 测试无索引查询性能
fgedudb=# EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE department = ‘技术部’ AND join_date > ‘2023-06-01’;
QUERY PLAN
———————————————————————————————————————-
Seq Scan on fgedu_fgedus (cost=0.00..1289.00 rows=3125 width=83) (actual time=0.016..15.718 rows=3125 loops=1)
Filter: (((department)::text = ‘技术部’::text) AND (join_date > ‘2023-06-01’::date))
Rows Removed by Filter: 46875
Planning Time: 0.088 ms
Execution Time: 15.850 ms
(5 rows)
# 4. 创建复合索引
fgedudb=# CREATE INDEX idx_fgedu_fgedus_dept_join ON fgedu_fgedus (department, join_date);
CREATE INDEX
# 5. 测试有索引查询性能
fgedudb=# EXPLAIN ANALYZE SELECT * FROM fgedu_fgedus WHERE department = ‘技术部’ AND join_date > ‘2023-06-01’;
QUERY PLAN
———————————————————————————————————————-
Bitmap Heap Scan on fgedu_fgedus (cost=38.69..836.18 rows=3125 width=83) (actual time=0.326..2.298 rows=3125 loops=1)
Recheck Cond: (((department)::text = ‘技术部’::text) AND (join_date > ‘2023-06-01’::date))
Heap Blocks: exact=141
-> Bitmap Index Scan on idx_fgedu_fgedus_dept_join (cost=0.00..37.91 rows=3125 width=0) (actual time=0.283..0.283 rows=3125 loops=1)
Index Cond: (((department)::text = ‘技术部’::text) AND (join_date > ‘2023-06-01’::date))
Planning Time: 0.167 ms
Execution Time: 2.468 ms
(7 rows)
# 6. 查看索引大小
fgedudb=# SELECT indexname, pg_size_pretty(pg_relation_size(indexname)) AS index_size
FROM pg_indexes
WHERE tablename = ‘fgedu_fgedus’;
indexname | index_size
———————————–+————
fgedu_fgedus_pkey | 1808 kB
fgedu_fgedus_email_key | 1184 kB
fgedu_fgedus_fgeduname_key | 1184 kB
idx_fgedu_fgedus_dept_join | 1184 kB
(4 rows)
# 7. 重建索引
fgedudb=# REINDEX INDEX idx_fgedu_fgedus_dept_join;
REINDEX
5.2 事务实战
事务实战示例，模拟银行转账场景。
fgedudb=# CREATE TABLE fgedu_bank_accounts (
account_id SERIAL PRIMARY KEY,
account_number VARCHAR(20) UNIQUE NOT NULL,
balance DECIMAL(15,2) NOT NULL CHECK (balance >= 0),
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE fgedu_# 2. 插入测试数据
fgedudb=# INSERT INTO fgedu_bank_accounts (account_number, balance) VALUES
(‘ACC001’, 10000.00),
(‘ACC002’, 5000.00),
(‘ACC003’, 20000.00);
INSERT 0 3
# 3. 模拟转账事务
fgedudb=# BEGIN;
BEGIN
# 检查转账方余额
fgedudb=# SELECT balance FROM fgedu_bank_accounts WHERE account_number = ‘ACC001’;
balance
———
10000.00
(1 row)
# 扣除转账方金额
fgedudb=# UPDATE fgedu_bank_accounts SET balance = balance – 3000.00 WHERE account_number = ‘ACC001’;
UPDATE 1
# 增加收款方金额
fgedudb=# UPDATE fgedu_bank_accounts SET balance = balance + 3000.00 WHERE account_number = ‘ACC002’;
UPDATE 1
# 验证转账结果
fgedudb=# SELECT account_number, balance FROM fgedu_bank_accounts WHERE account_number IN (‘ACC001’, ‘ACC002’);
account_number | balance
—————-+———-
ACC001 | 7000.00
ACC002 | 8000.00
(2 rows)
# 提交事务
fgedudb=# COMMIT;
COMMIT
# 查看最终结果
fgedudb=# SELECT * FROM fgedu_bank_accounts;
account_id | account_number | balance | created_at
————+—————-+———-+—————————-
1 | ACC001 | 7000.00 | 2026-04-02 14:00:00.123456
2 | ACC002 | 8000.00 | 2026-04-02 14:00:00.123456
3 | ACC003 | 20000.00 | 2026-04-02 14:00:00.123456
(3 rows)
5.3 全文检索实战
全文检索实战示例，创建博客文章表并进行全文检索。
fgedudb=# CREATE TABLE fgedu_blog_posts (
post_id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
author VARCHAR(100) NOT NULL,
published_date DATE DEFAULT NOW(),
tags VARCHAR(255)
);
CREATE TABLE fgedu_# 2. 创建全文检索索引
fgedudb=# CREATE INDEX idx_fgedu_blog_content ON fgedu_blog_posts USING GIN (to_tsvector(‘chinese’, title || ‘ ‘ || content));
CREATE INDEX
# 3. 插入测试数据
fgedudb=# INSERT INTO fgedu_blog_posts (title, content, author, published_date, tags) VALUES
(‘PostgreSQL索引优化实战’, ‘本文详细介绍PostgreSQL索引的优化方法和最佳实践，包括B-tree、GIN、GiST等不同类型索引的使用场景…’, ‘风哥’, ‘2026-03-15’, ‘PostgreSQL,索引,优化’),
(‘PostgreSQL事务管理详解’, ‘PostgreSQL支持ACID事务，本文介绍事务的基本概念、隔离级别和使用方法…’, ‘风哥’, ‘2026-03-20’, ‘PostgreSQL,事务,ACID’),
(‘PostgreSQL全文检索入门’, ‘PostgreSQL提供了强大的全文检索功能，本文介绍全文检索的基本概念和使用方法…’, ‘风哥’, ‘2026-03-25’, ‘PostgreSQL,全文检索’),
(‘Python与PostgreSQL集成开发’, ‘本文介绍如何使用Python连接PostgreSQL数据库，进行数据操作和应用开发…’, ‘风哥’, ‘2026-03-30’, ‘Python,PostgreSQL,开发’),
(‘PostgreSQL性能调优指南’, ‘本文提供PostgreSQL性能调优的全面指南，包括配置优化、查询优化和索引优化…’, ‘风哥’, ‘2026-04-01’, ‘PostgreSQL,性能调优’);
INSERT 0 5
# 4. 使用全文检索查询
fgedudb=# SELECT post_id, title, author, published_date
FROM fgedu_blog_posts
WHERE to_tsvector(‘chinese’, title || ‘ ‘ || content) @@ to_tsquery(‘chinese’, ‘索引 & 优化’);
post_id | title | author | published_date
———+————————+——–+—————-
1 | PostgreSQL索引优化实战 | 风哥 | 2026-03-15
5 | PostgreSQL性能调优指南 | 风哥 | 2026-04-01
(2 rows)
# 5. 使用全文检索排序（按相关性）
fgedudb=# SELECT post_id, title, ts_rank_cd(to_tsvector(‘chinese’, title || ‘ ‘ || content), to_tsquery(‘chinese’, ‘PostgreSQL & 优化’)) AS rank
FROM fgedu_blog_posts
WHERE to_tsvector(‘chinese’, title || ‘ ‘ || content) @@ to_tsquery(‘chinese’, ‘PostgreSQL & 优化’)
ORDER BY rank DESC;
post_id | title | rank
———+————————+———-
1 | PostgreSQL索引优化实战 | 0.15000001
5 | PostgreSQL性能调优指南 | 0.15000001
(2 rows)
本文介绍了PostgreSQL的三个核心高级特性：索引、事务和全文检索。通过学习本文，读者可以初步了解这些特性的基本概念、工作原理和使用方法，并能够进行简单的实战操作。这些高级特性是PostgreSQL强大功能的重要组成部分，掌握它们对于开发和管理高性能的PostgreSQL应用至关重要。更多学习教程公众号风哥教程itpux_com
from PostgreSQL:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
