1. PostgreSQL数据库创建与使用
PostgreSQL 的优势与不足
核心优势
-
功能完备性
- 完整支持 ACID 事务与 MVCC 并发控制
- 提供窗口函数、递归查询 (WITH RECURSIVE)、JSON/XML 处理等高级功能
- 支持 150+ 索引类型 (B-tree/GIN/GiST/SP-GiST/BRIN/Hash)
-
扩展生态
- 地理空间处理: PostGIS (支持 $$ST_Distance(geom1, geom2)$$ 等空间计算)
- 时序数据处理: TimescaleDB 扩展
- 全文检索: 内置文本搜索 + pg_trgm 模糊匹配
-
标准兼容性
实现 SQL:2016 标准核心特性的 160/179 项，远超 MySQL (92项) 和 SQL Server (99项) -
混合数据处理
支持结构化与非结构化数据混合存储，例如：CREATE TABLE hybrid_data ( id SERIAL PRIMARY KEY, sensor_values JSONB, geo_data GEOMETRY(POINT,4326), created_at TIMESTAMPTZ );
典型劣势
-
内存管理
默认配置针对通用场景优化，高并发时需要调整： $$shared_buffers = 25% \times total_RAM$$ $$work_mem = (total_RAM - shared_buffers) / max_connections$$ -
集群方案
原生复制方案对比：特性 PostgreSQL MySQL InnoDB Cluster 同步延迟 秒级 毫秒级 自动故障转移 需外置工具 内置 拓扑变更复杂度 较高 中等 -
特定场景性能
简单键值查询吞吐量对比 (单位：QPS)：Redis: 100,000+ PostgreSQL: 15,000 (B-tree索引) MongoDB: 25,000 (WiredTiger引擎)
适用场景决策树
graph TD
A[需要事务保证?] -->|是| B{数据复杂度}
A -->|否| C[考虑NoSQL]
B -->|高(JSON/空间/时序)| D[选择PostgreSQL]
B -->|常规关系型| E[MySQL/MariaDB]
D --> F[需要企业级支持?]
F -->|是| G[评估EnterpriseDB]
F -->|否| H[使用社区版]
技术选型建议
当存在以下需求时优先选择 PostgreSQL：
- 需要执行复杂分析查询 (如使用 $$EXPLAIN ANALYZE$$ 优化执行计划)
- 混合数据类型存储需求
- 要求高度可扩展性 (如自定义聚合函数)
- 地理空间数据处理
- 需要符合严格的数据完整性要求
推荐搭配工具链：
# 监控工具
pg_stat_activity + pghero
# 迁移工具
pg_dump --format=custom -Z 9
# 性能分析
EXPLAIN (ANALYZE, BUFFERS) SELECT...
一、数据库创建
-- 创建新数据库
CREATE DATABASE testdb
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8';
-- 切换数据库
\c testdb
-- 查看数据库列表
\l 或 \l+
二、数据表操作
1. 创建表结构
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(user_id),
amount DECIMAL(10,2),
order_date DATE NOT NULL
);
2. 表结构维护
-- 添加新列
ALTER TABLE users ADD COLUMN phone VARCHAR(15);
-- 修改列类型
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(20);
-- 添加索引
CREATE INDEX idx_users_email ON users(email);
三、数据操作
1. 插入数据
INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');
INSERT INTO orders (user_id, amount, order_date)
VALUES (1, 99.99, '2023-08-15');
2. 查询数据
-- 联合查询
SELECT u.username, o.order_date, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.amount > 50.00;
-- 聚合查询
SELECT
EXTRACT(YEAR FROM order_date) AS year,
COUNT(*) AS total_orders,
SUM(amount) AS total_amount
FROM orders
GROUP BY year;
3. 事务处理
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
四、数据库维护
1. 备份与恢复
# 备份数据库
pg_dump -U username -d testdb -f backup.sql
# 恢复数据库
psql -U username -d restoredb -f backup.sql
2. 性能优化
-- 查询执行计划分析
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';
-- 表空间优化
VACUUM FULL VERBOSE ANALYZE users;
-- 分区表示例
CREATE TABLE sales (
sale_id SERIAL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);
五、安全设置
-- 创建角色
CREATE ROLE analyst WITH
LOGIN
PASSWORD 'securepass'
VALID UNTIL '2024-12-31';
-- 权限管理
GRANT SELECT ON orders TO analyst;
REVOKE DELETE ON users FROM analyst;
六、高级功能
1. JSON数据处理
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
details JSONB NOT NULL
);
INSERT INTO products (details) VALUES
('{"name": "Laptop", "specs": {"cpu": "i7", "ram": "16GB"}}');
SELECT details->>'name' AS product_name
FROM products
WHERE details @> '{"specs": {"ram": "16GB"}}';
2. 地理空间数据
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
position GEOGRAPHY(POINT)
);
INSERT INTO locations (name, position) VALUES
('Office', ST_GeographyFromText('POINT(121.4737 31.2304)'));
3. 全文搜索
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
search_vector TSVECTOR
);
CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
ON documents FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', content);
SELECT id, content
FROM documents
WHERE search_vector @@ to_tsquery('english', 'database & optimization');
2.Java项目中使用PostgreSQL
一、添加驱动依赖
Maven项目配置：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
Gradle项目配置：
implementation 'org.postgresql:postgresql:42.6.0'
二、数据库连接配置
String url = "jdbc:postgresql://localhost:5432/mydatabase";
String user = "postgres";
String password = "your_password";
三、建立连接示例
public class PGConnection {
public static void main(String[] args) {
Connection conn = null;
try {
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT version()");
if(rs.next()) {
System.out.println("PostgreSQL版本: " + rs.getString(1));
}
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
四、基本操作示例
1. 创建表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. 插入数据
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, "张三");
pstmt.setString(2, "zhangsan@example.com");
pstmt.executeUpdate();
}
3. 查询数据
String sql = "SELECT * FROM users WHERE name LIKE ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, "%张%");
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
// 处理结果...
}
}
五、重要注意事项
- 连接池配置（推荐HikariCP）：
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(10);
HikariDataSource ds = new HikariDataSource(config);
- 事务管理：
conn.setAutoCommit(false);
try {
// 执行多个更新操作
conn.commit();
} catch (SQLException e) {
conn.rollback();
}
- 性能优化：
CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
- 类型映射： | PostgreSQL类型 | Java类型 | |---------------|----------------| | VARCHAR | String | | INTEGER | Integer | | BIGINT | Long | | NUMERIC | BigDecimal | | TIMESTAMP | LocalDateTime | | JSONB | PGobject/String|
提示：
- 始终使用try-with-resources确保资源释放
- 使用PreparedStatement防止SQL注入
- 定期检查连接有效性：
ds.validate();
- 配置连接参数建议：
ssl=true
sslmode=require
connectTimeout=30
socketTimeout=300
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
