【数据库】PostgreSQL实战：从基础到高级特性

【数据库】PostgreSQL实战从基础到高级特性引言PostgreSQL是一个功能强大的开源关系型数据库以其可靠性、扩展性和丰富的特性而闻名。本文将详细介绍PostgreSQL的核心特性、SQL操作和高级功能。一、基础概念1.1 数据库对象-- 创建数据库 CREATE DATABASE mydb; -- 创建表 CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, age INTEGER CHECK (age 0), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 创建索引 CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_created_at ON users(created_at DESC);1.2 数据类型类型说明示例INTEGER整数42VARCHAR(n)可变字符串HelloTEXT长文本长文章内容BOOLEAN布尔值TRUETIMESTAMP时间戳2024-01-01 12:00:00ARRAY数组ARRAY[1, 2, 3]JSONBJSON二进制{key: value}二、SQL操作2.1 查询数据-- 基础查询 SELECT id, name, email FROM users; -- 条件查询 SELECT * FROM users WHERE age 18 AND created_at 2024-01-01; -- 排序和分页 SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- 聚合查询 SELECT COUNT(*), AVG(age), MAX(age) FROM users; -- 分组查询 SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) 1;2.2 插入和更新-- 插入数据 INSERT INTO users (name, email, age) VALUES (Alice, aliceexample.com, 30); -- 批量插入 INSERT INTO users (name, email, age) VALUES (Bob, bobexample.com, 25), (Charlie, charlieexample.com, 35); -- 更新数据 UPDATE users SET age 31 WHERE name Alice; -- 删除数据 DELETE FROM users WHERE id 1;2.3 连接查询-- 创建订单表 CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), amount NUMERIC(10, 2), status VARCHAR(20) ); -- INNER JOIN SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id; -- LEFT JOIN SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name;三、高级特性3.1 JSONB操作-- 创建带JSONB字段的表 CREATE TABLE documents ( id SERIAL PRIMARY KEY, content JSONB ); -- 插入JSON数据 INSERT INTO documents (content) VALUES ( {title: PostgreSQL Guide, tags: [database, sql], author: {name: Alice}} ); -- 查询JSON字段 SELECT content-title as title FROM documents; -- JSONB索引 CREATE INDEX idx_documents_content ON documents USING GIN (content); -- JSONB查询 SELECT * FROM documents WHERE content {tags: [database]};3.2 数组类型-- 创建带数组字段的表 CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100), tags TEXT[] ); -- 插入数据 INSERT INTO products (name, tags) VALUES ( Laptop, ARRAY[electronics, computer] ); -- 查询包含特定标签的产品 SELECT * FROM products WHERE electronics ANY(tags); -- 添加标签 UPDATE products SET tags array_append(tags, sale) WHERE id 1;3.3 窗口函数-- 排名函数 SELECT name, amount, RANK() OVER (ORDER BY amount DESC) as rank, DENSE_RANK() OVER (ORDER BY amount DESC) as dense_rank, ROW_NUMBER() OVER (ORDER BY amount DESC) as row_num FROM orders; -- 移动平均 SELECT date, value, AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg FROM metrics; -- 分区窗口 SELECT user_id, amount, SUM(amount) OVER (PARTITION BY user_id) as total_amount FROM orders;四、性能优化4.1 索引优化-- 查看执行计划 EXPLAIN ANALYZE SELECT * FROM users WHERE email aliceexample.com; -- 创建复合索引 CREATE INDEX idx_users_name_email ON users(name, email); -- 部分索引 CREATE INDEX idx_users_active ON users(id) WHERE status active; -- 表达式索引 CREATE INDEX idx_users_lower_email ON users(LOWER(email));4.2 查询优化-- 避免SELECT * SELECT id, name FROM users WHERE age 18; -- 使用JOIN替代子查询 SELECT u.name FROM users u JOIN orders o ON u.id o.user_id WHERE o.amount 100; -- 限制结果集大小 SELECT * FROM large_table LIMIT 100;4.3 数据库配置# postgresql.conf shared_buffers 2GB # 共享缓冲区 work_mem 64MB # 排序和哈希内存 maintenance_work_mem 512MB # 维护操作内存 effective_cache_size 8GB # 有效缓存大小五、事务和并发5.1 事务控制-- 开始事务 BEGIN; -- 执行操作 UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK;5.2 锁机制-- 行级锁 BEGIN; SELECT * FROM orders WHERE id 1 FOR UPDATE; UPDATE orders SET status processing WHERE id 1; COMMIT; -- 共享锁 SELECT * FROM products WHERE id 1 FOR SHARE;5.3 隔离级别-- 设置隔离级别 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 事务隔离级别 -- READ UNCOMMITTED - 最低级别 -- READ COMMITTED - 默认级别 -- REPEATABLE READ - 可重复读 -- SERIALIZABLE - 最高级别六、备份和恢复6.1 使用pg_dump# 备份整个数据库 pg_dump mydb mydb_backup.sql # 备份特定表 pg_dump -t users -t orders mydb tables_backup.sql # 压缩备份 pg_dump mydb | gzip mydb_backup.sql.gz # 恢复数据库 psql mydb mydb_backup.sql6.2 增量备份# 启用WAL日志 # postgresql.conf wal_level replica archive_mode on archive_command cp %p /backup/%f # 基础备份 pg_basebackup -D /backup/base -X stream # 恢复 pg_restore -d mydb /backup/base七、扩展功能7.1 安装扩展-- 安装PostGIS扩展 CREATE EXTENSION postgis; -- 安装pg_trgm扩展用于模糊搜索 CREATE EXTENSION pg_trgm; -- 安装uuid-ossp扩展 CREATE EXTENSION uuid-ossp;7.2 全文搜索-- 创建全文索引 ALTER TABLE documents ADD COLUMN content_tsv TSVECTOR; UPDATE documents SET content_tsv to_tsvector(english, content-body); CREATE INDEX idx_documents_content_tsv ON documents USING GIN(content_tsv); -- 全文搜索 SELECT * FROM documents WHERE content_tsv to_tsquery(english, database postgresql);7.3 触发器-- 创建触发器函数 CREATE FUNCTION update_modified_at() RETURNS TRIGGER AS $$ BEGIN NEW.modified_at CURRENT_TIMESTAMP; RETURN NEW; END; $$ LANGUAGE plpgsql; -- 创建触发器 CREATE TRIGGER trigger_users_modified BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_modified_at();八、实战案例用户管理系统8.1 数据库设计-- 用户表 CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 用户角色表 CREATE TABLE roles ( id SERIAL PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL, description TEXT ); -- 用户角色关联表 CREATE TABLE user_roles ( user_id INTEGER REFERENCES users(id), role_id INTEGER REFERENCES roles(id), PRIMARY KEY (user_id, role_id) ); -- 创建索引 CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);8.2 查询示例-- 获取用户及其角色 SELECT u.username, r.name as role FROM users u LEFT JOIN user_roles ur ON u.id ur.user_id LEFT JOIN roles r ON ur.role_id r.id; -- 统计各角色用户数 SELECT r.name, COUNT(ur.user_id) as user_count FROM roles r LEFT JOIN user_roles ur ON r.id ur.role_id GROUP BY r.id, r.name;九、常见问题与解决方案9.1 性能问题问题解决方案查询慢添加合适的索引锁竞争使用合适的隔离级别内存不足调整shared_buffers等参数9.2 数据一致性问题解决方案并发更新使用事务和锁数据重复使用UNIQUE约束外键引用使用FOREIGN KEY约束9.3 备份恢复问题解决方案备份过大使用压缩恢复时间长使用增量备份数据损坏定期检查数据库十、结语PostgreSQL是一个功能丰富的数据库系统掌握其核心特性对于构建可靠的应用至关重要。本文介绍了PostgreSQL的基础操作、高级特性和性能优化希望能帮助你更好地使用PostgreSQL。#PostgreSQL #数据库 #SQL #后端开发