数据库架构与优化
1. 数据库架构模式
1.1 集群模式
集群模式通过多个数据库节点协同工作，提供高可用性和负载均衡。
优点：
- 高可用性：节点故障时自动切换
- 负载均衡：分散读写压力
- 水平扩展：通过添加节点提升性能
常见实现：
- MySQL Group Replication
- PostgreSQL Patroni
- MongoDB Replica Set
1.2 读写分离模式
读写分离通过主从复制，将读操作分发到从库，写操作在主库执行。
text
┌─────────┐
│ 应用 │
└────┬────┘
│
├──────写──────► 主库 (Master)
│ │
│ │ 复制
│ ▼
└──────读──────► 从库 (Slave 1)
│
└──────► 从库 (Slave 2)优点：
- 提升读性能
- 减轻主库压力
- 数据备份
注意事项：
- 主从延迟问题
- 数据一致性保证
1.3 分片模式
分片（Sharding）将数据分散存储到多个数据库实例中。
1.3.1 哈希分片
使用哈希函数将数据均匀分布到不同分片。
一致性哈希（Consistent Hashing）：
- 减少数据迁移
- 适合节点动态增减
- 虚拟节点提升均衡性
python
import hashlib
def consistent_hash(key, num_shards):
"""一致性哈希示例"""
hash_value = int(hashlib.md5(key.encode()).hexdigest(), 16)
return hash_value % num_shards
# 示例
user_id = "user123"
shard_id = consistent_hash(user_id, 4)
print(f"User {user_id} -> Shard {shard_id}")取模哈希（Modulo Hashing）：
- 实现简单
- 固定分片数量
- 扩容时需要重新分配
python
def modulo_hash(key, num_shards):
"""取模哈希示例"""
return hash(key) % num_shards1.3.2 范围分片
按照数据范围（如 ID、时间）将数据分配到不同分片。
优点：
- 范围查询高效
- 数据有序性
缺点：
- 可能出现热点问题
- 负载不均衡
2. MySQL 优化
2.1 索引优化
MAX() 和 MIN() 函数与索引利用
在使用 MAX() 和 MIN() 函数时，合理使用索引可以显著提升性能。
sql
-- 创建索引
CREATE INDEX idx_created_at ON users(created_at);
-- 高效查询最大值
SELECT MAX(created_at) FROM users;
-- 高效查询最小值
SELECT MIN(created_at) FROM users;参考：MySQL 之查询：max() 和 min() 函数与索引利用
2.2 查询优化
使用 EXPLAIN 分析查询
sql
EXPLAIN SELECT * FROM users WHERE age > 18;关键字段说明：
- type：访问类型（ALL, index, range, ref, eq_ref, const）
- key：实际使用的索引
- rows：扫描的行数
- Extra：额外信息
避免全表扫描
sql
-- 不好的查询
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 优化后的查询
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';2.3 连接池配置
合理配置连接池可以提升数据库性能：
python
import pymysql
from dbutils.pooled_db import PooledDB
# 创建连接池
pool = PooledDB(
creator=pymysql,
maxconnections=20, # 最大连接数
mincached=2, # 最小空闲连接
maxcached=5, # 最大空闲连接
blocking=True, # 连接不足时阻塞
host='localhost',
user='root',
password='password',
database='mydb',
charset='utf8mb4'
)
# 使用连接
conn = pool.connection()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
cursor.close()
conn.close()3. PostgreSQL 优化
3.1 VACUUM 和 ANALYZE
定期运行 VACUUM 和 ANALYZE 保持数据库性能：
sql
-- 清理死元组
VACUUM users;
-- 更新统计信息
ANALYZE users;
-- 完整清理和分析
VACUUM ANALYZE users;
-- 自动清理所有表
VACUUM ANALYZE;3.2 分区表
使用分区表优化大表查询：
sql
-- 创建分区表
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL,
amount DECIMAL(10, 2)
) PARTITION BY RANGE (order_date);
-- 创建分区
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');4. MongoDB 优化
4.1 索引策略
javascript
// 创建单字段索引
db.users.createIndex({ email: 1 })
// 创建复合索引
db.users.createIndex({ age: 1, name: 1 })
// 创建唯一索引
db.users.createIndex({ username: 1 }, { unique: true })
// 创建文本索引
db.posts.createIndex({ content: "text" })
// 查看索引使用情况
db.users.find({ email: "test@example.com" }).explain("executionStats")4.2 聚合优化
使用聚合管道优化复杂查询：
javascript
db.orders.aggregate([
// 过滤
{ $match: { status: "completed" } },
// 分组统计
{ $group: {
_id: "$user_id",
total_amount: { $sum: "$amount" },
order_count: { $sum: 1 }
}},
// 排序
{ $sort: { total_amount: -1 } },
// 限制结果
{ $limit: 10 }
])5. SQLite 优化
5.1 WAL 模式
启用 WAL（Write-Ahead Logging）模式提升并发性能：
python
import sqlite3
conn = sqlite3.connect('database.db')
conn.execute('PRAGMA journal_mode=WAL')
conn.commit()WAL 模式优点：
- 读写不互斥
- 提升并发性能
- 减少磁盘 I/O
5.2 批量插入优化
python
import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
# 使用事务批量插入
cursor.execute('BEGIN TRANSACTION')
for i in range(10000):
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)',
(f'User{i}', 20 + i % 50))
cursor.execute('COMMIT')
conn.close()6. Redis 优化
6.1 数据结构选择
选择合适的数据结构可以显著提升性能：
python
import redis
r = redis.Redis(host='localhost', port=6379, db=0)
# String：简单的键值对
r.set('user:1000:name', 'Alice')
# Hash：存储对象
r.hset('user:1000', mapping={
'name': 'Alice',
'age': 25,
'email': 'alice@example.com'
})
# List：队列、栈
r.lpush('queue:tasks', 'task1', 'task2', 'task3')
# Set：去重、集合运算
r.sadd('tags:python', 'web', 'backend', 'async')
# Sorted Set：排行榜
r.zadd('leaderboard', {'player1': 100, 'player2': 200})6.2 缓存策略
缓存穿透解决方案
python
import redis
import json
r = redis.Redis()
def get_user(user_id):
# 1. 查询缓存
cache_key = f'user:{user_id}'
cached = r.get(cache_key)
if cached:
if cached == b'null': # 防止缓存穿透
return None
return json.loads(cached)
# 2. 查询数据库
user = db.query_user(user_id)
# 3. 更新缓存
if user:
r.setex(cache_key, 3600, json.dumps(user))
else:
r.setex(cache_key, 60, 'null') # 缓存空结果
return user