数据库优化技术
概览
数据库优化涉及提高查询性能、减少资源消耗和确保高效的数据访问模式。这项技能包括查询分析、索引策略、缓存和维护实践。
前提条件
- 理解SQL和数据库操作
- 了解数据库模式设计原则
- 熟悉数据库管理工具
- 基本了解性能监控
关键概念
性能优化领域
- 查询优化：编写高效的SQL查询
- 索引策略：创建和维护索引
- 连接管理：高效的连接池
- 缓存：减少数据库负载
- 模式设计：适当的规范化和反规范化
- 维护：定期的数据库维护任务
性能指标
- 查询响应时间：执行查询的时间
- 吞吐量：每秒查询数
- 资源使用：CPU、内存、I/O
- 锁争用：等待锁的时间
- 缓存命中率：从缓存中服务的查询百分比
实施指南
查询优化
EXPLAIN分析
-- 基本EXPLAIN
EXPLAIN
SELECT * FROM users WHERE email = 'user@example.com';
-- EXPLAIN带实际执行（PostgreSQL）
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';
-- EXPLAIN带缓冲区（PostgreSQL）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE email = 'user@example.com';
-- EXPLAIN在MySQL中
EXPLAIN
SELECT * FROM users WHERE email = 'user@example.com';
-- EXPLAIN在MongoDB中
db.users.find({ email: 'user@example.com' }).explain('executionStats');
查询规划
-- 理解查询计划
-- 分析的关键指标：
-- - 顺序扫描与索引扫描
-- - 过滤条件
-- - 连接类型（嵌套循环、哈希连接、合并连接）
-- - 排序操作
-- - 聚合操作
-- 示例：分析复杂查询
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;
-- 查找：
-- 1. 大表上的顺序扫描（应使用索引）
-- 2. 高成本估计
-- 3. 被物化的大型结果集
-- 4. 低效的连接策略
连接优化
-- 好：使用索引列进行连接
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 好：在索引列上进行连接
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';
-- 坏：在非索引列上进行连接
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.email = o.user_email
WHERE o.status = 'completed';
-- 好：使用适当的连接类型
-- INNER JOIN：仅匹配行
-- LEFT JOIN：左侧全部，右侧匹配
-- RIGHT JOIN：右侧全部，左侧匹配
-- FULL JOIN：两个表的全部
-- 好：连接前先过滤
SELECT u.name, o.total
FROM users u
INNER JOIN (
SELECT user_id, total
FROM orders
WHERE status = 'completed'
) o ON u.id = o.user_id;
索引策略
B-Tree索引
-- 单列索引
CREATE INDEX idx_users_email ON users(email);
-- 多列（复合）索引
CREATE INDEX idx_users_name_email ON users(name, email);
-- 带顺序的索引
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- 部分索引（仅索引子集的行）
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- 表达式索引（索引计算值）
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- 覆盖索引（包括非索引列）
-- PostgreSQL
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);
复合索引
-- 好：按选择性排序列
-- 最具选择性的列放在最前面
CREATE INDEX idx_users_status_created ON users(status, created_at);
-- 好：将查询顺序与索引顺序匹配
-- 此查询使用索引：
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';
-- 此查询不能有效地使用索引：
SELECT * FROM users WHERE created_at > '2024-01-01' AND status = 'active';
-- 好：为不同的查询模式创建多个索引
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_users_status_created ON users(status, created_at);
-- 坏：复合索引中列太多
CREATE INDEX idx_users_all ON users(name, email, status, created_at, updated_at);
-- 只有前几列被有效使用
部分索引
-- 仅索引活跃用户
CREATE INDEX idx_active_users_email ON users(email) WHERE active = true;
-- 仅索引近期订单
CREATE INDEX idx_recent_orders ON orders(user_id) WHERE created_at > NOW() - INTERVAL '30 days';
-- 仅索引高价值订单
CREATE INDEX idx_high_value_orders ON orders(user_id) WHERE total > 1000;
-- 好处：
-- - 更小的索引大小
-- - 更快的索引扫描
-- - 维护开销更小
-- - 更适合过滤查询
索引维护
-- 重建索引（PostgreSQL）
REINDEX INDEX idx_users_email;
-- 重建表上的所有索引（PostgreSQL）
REINDEX TABLE users;
-- 分析表统计信息（PostgreSQL）
ANALYZE users;
-- 分析表（MySQL）
ANALYZE TABLE users;
-- 优化表（MySQL）
OPTIMIZE TABLE users;
-- 检查索引使用情况（PostgreSQL）
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;
-- 查找未使用的索引（PostgreSQL）
SELECT
schemaname,
tablename,
indexname,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%_pkey';
连接池
连接池配置
// Prisma连接池
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
// 连接池设置
connection_limit = 10 // 最大连接数
pool_timeout = 20 // 等待连接的秒数
}
// PostgreSQL池配置
const pool = new Pool({
host: 'localhost',
port: 5432,
database: 'mydb',
user: 'postgres',
password: 'password',
max: 20, // 最大池大小
min: 2, // 最小池大小
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
连接池最佳实践
// 好：使用连接池
const pool = new Pool({
max: 20,
min: 2,
idleTimeoutMillis: 30000,
});
// 坏：为每个查询创建新连接
async function getUser(id: string) {
const client = new Client({ /* config */ });
await client.connect();
const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
await client.end();
return result.rows[0];
}
// 好：使用池进行查询
async function getUser(id: string) {
const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
return result.rows[0];
}
N+1查询问题
识别N+1问题
// 坏：N+1查询问题
async function getUsersWithOrders() {
const users = await db.user.findMany(); // 1个查询
const usersWithOrders = [];
for (const user of users) {
const orders = await db.order.findMany({ // N个查询
where: { userId: user.id }
});
usersWithOrders.push({ ...user, orders });
}
return usersWithOrders;
}
// 总查询数：1 + N（N是用户数）
使用连接解决N+1
// 好：使用JOIN
async function getUsersWithOrders() {
const result = await db.$queryRaw`
SELECT
u.id, u.name, u.email,
o.id as order_id, o.total, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
`;
// 转换结果为嵌套结构
const usersMap = new Map();
for (const row of result) {
if (!usersMap.has(row.id)) {
usersMap.set(row.id, {
id: row.id,
name: row.name,
email: row.email,
orders: []
});
}
if (row.order_id) {
usersMap.get(row.id).orders.push({
id: row.order_id,
total: row.total,
status: row.status
});
}
}
return Array.from(usersMap.values());
}
// 总查询数：1
使用Include解决N+1
// 好：Prisma include
async function getUsersWithOrders() {
return await db.user.findMany({
include: {
orders: true
}
});
}
// 好：Mongoose populate
async function getUsersWithOrders() {
return await User.find().populate('orders');
}
使用批量加载解决N+1
// 好：批量加载
async function getUsersWithOrders() {
const users = await db.user.findMany();
const userIds = users.map(u => u.id);
const orders = await db.order.findMany({
where: { userId: { in: userIds } }
});
const ordersMap = new Map();
orders.forEach(order => {
if (!ordersMap.has(order.userId)) {
ordersMap.set(order.userId, []);
}
ordersMap.get(order.userId).push(order);
});
return users.map(user => ({
...user,
orders: ordersMap.get(user.id) || []
}));
}
// 总查询数：2
缓存策略
查询结果缓存
// 使用Redis缓存查询结果
import { redisClient } from './redis';
async function getCachedUser(userId: string) {
const cacheKey = `user:${userId}`;
// 先尝试缓存
const cached = await redisClient.get(cacheKey);
if (cached) {
return JSON.parse(cached);
}
// 缓存未命中 - 查询数据库
const user = await db.user.findUnique({ where: { id: userId } });
// 缓存存储1小时TTL
await redisClient.set(cacheKey, JSON.stringify(user), {
EX: 3600
});
return user;
}
缓存失效
// 更新时使缓存失效
async function updateUser(userId: string, data: any) {
const user = await db.user.update({
where: { id: userId },
data
});
// 使缓存失效
await redisClient.del(`user:${userId}`);
return user;
}
// 使多个缓存失效
async function invalidateUserCaches(userId: string) {
await redisClient.del(`user:${userId}`);
await redisClient.del(`user:${userId}:profile`);
await redisClient.del(`user:${userId}:orders`);
}
旁路缓存模式
// 旁路缓存实现
class CacheAside {
async get(key: string) {
// 1. 尝试缓存
const cached = await redisClient.get(key);
if (cached) {
return JSON.parse(cached);
}
// 2. 缓存未命中 - 从数据库加载
const data = await this.loadFromDatabase(key);
// 3. 填充缓存
await redisClient.set(key, JSON.stringify(data), { EX: 3600 });
return data;
}
async set(key: string, data: any) {
// 更新数据库
await this.saveToDatabase(key, data);
// 更新缓存
await redisClient.set(key, JSON.stringify(data), { EX: 3600 });
}
async delete(key: string) {
// 从数据库删除
await this.deleteFromDatabase(key);
// 使缓存失效
await redisClient.del(key);
}
}
必要时去规范化
何时去规范化
-- 好：为读重负载去规范化
-- 添加冗余数据以避免连接
-- 规范化模式
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total DECIMAL(10, 2),
status VARCHAR(50),
created_at TIMESTAMP
);
-- 快速读取的去规范化模式
CREATE TABLE orders_denormalized (
id SERIAL PRIMARY KEY,
user_id INTEGER,
user_name VARCHAR(255), -- 去规范化
user_email VARCHAR(255), -- 去规范化
total DECIMAL(10, 2),
status VARCHAR(50),
created_at TIMESTAMP
);
-- 好：对于复杂聚合使用物化视图
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
u.id,
u.name,
u.email,
COUNT(o.id) as order_count,
SUM(o.total) as total_spent,
MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;
-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_order_summary;
权衡
-- 去规范化的好处：
-- - 更快的读取查询（无连接）
-- - 更简单的查询
-- - 更适合报告
-- 去规范化的缺点：
-- - 数据冗余
-- - 更新异常
-- - 存储增加
-- - 复杂的数据同步
分区
表分区（PostgreSQL）
-- 按日期范围分区
CREATE TABLE orders (
id SERIAL,
user_id INTEGER,
total DECIMAL(10, 2),
status VARCHAR(50),
created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
-- 创建分区
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');
CREATE TABLE orders_2024_q3 PARTITION OF orders
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE orders_2024_q4 PARTITION OF orders
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
-- 列表分区
CREATE TABLE orders_by_status (
id SERIAL,
user_id INTEGER,
total DECIMAL(10, 2),
status VARCHAR(50),
created_at TIMESTAMP
) PARTITION BY LIST (status);
CREATE TABLE orders_active PARTITION OF orders_by_status
FOR VALUES IN ('pending', 'processing');
CREATE TABLE orders_completed PARTITION OF orders_by_status
FOR VALUES IN ('completed');
CREATE TABLE orders_cancelled PARTITION OF orders_by_status
FOR VALUES IN ('cancelled', 'refunded');
-- 哈希分区
CREATE TABLE orders_hash (
id SERIAL,
user_id INTEGER,
total DECIMAL(10, 2),
status VARCHAR(50),
created_at TIMESTAMP
) PARTITION BY HASH (user_id);
CREATE TABLE orders_hash_0 PARTITION OF orders_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_hash_1 PARTITION OF orders_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_hash_2 PARTITION OF orders_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_hash_3 PARTITION OF orders_hash
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
分区修剪
-- 查询受益于分区修剪
-- 仅扫描相关分区
-- 好：带有分区键的查询
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';
-- 仅扫描orders_2024_q1分区
-- 坏：没有分区键的查询
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE total > 1000;
-- 扫描所有分区
数据库维护
真空和分析
-- 真空（PostgreSQL）
-- 回收存储空间并更新统计信息
VACUUM users;
-- 真空带分析
VACUUM ANALYZE users;
-- 真空所有表
VACUUM ANALYZE;
-- 真空特定表带完整选项
VACUUM FULL users; -- 回收更多空间但锁定表
-- 自动真空配置（PostgreSQL）
ALTER SYSTEM SET autovacuum = on;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;
SELECT pg_reload_conf();
重新索引
-- 重新索引单个索引（PostgreSQL）
REINDEX INDEX idx_users_email;
-- 重新索引表上的所有索引（PostgreSQL）
REINDEX TABLE users;
-- 同时重新索引（PostgreSQL）
-- 不锁定表但需要更长时间
REINDEX INDEX CONCURRENTLY idx_users_email;
表优化
-- 优化表（MySQL）
OPTIMIZE TABLE users;
-- 优化表（SQLite）
VACUUM;
-- 分析表（MySQL）
ANALYZE TABLE users;
-- 检查表完整性（MySQL）
CHECK TABLE users;
监控查询
慢查询日志
-- 启用慢查询日志（PostgreSQL）
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 记录> 1秒的查询
SELECT pg_reload_conf();
-- 查看慢查询
SELECT
query,
calls,
total_time,
mean_time,
max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
-- 启用慢查询日志（MySQL）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录> 1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
查询性能监控
-- 查找最耗时的查询（PostgreSQL）
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- 查找最频繁的查询（PostgreSQL）
SELECT
query,
calls,
total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;
-- 监控查询执行（MySQL）
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;
数据库指标
-- 连接使用情况（PostgreSQL）
SELECT
state,
COUNT(*) as count
FROM pg_stat_activity
GROUP BY state;
-- 表大小（PostgreSQL）
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- 索引大小（PostgreSQL）
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
常见反模式
SELECT *
-- 坏：SELECT *检索所有列
SELECT * FROM users WHERE id = 1;
-- 好：仅选择需要的列
SELECT id, name, email FROM users WHERE id = 1;
-- 好处：
-- - 传输的数据更少
-- - 使用的内存更少
-- - 可以使用覆盖索引
通配符前缀匹配
-- 坏：前缀通配符阻止索引使用
SELECT * FROM users WHERE name LIKE '%Smith%';
-- 好：使用全文搜索
SELECT * FROM users WHERE to_tsvector('english', name) @@ to_tsquery('english', 'Smith');
-- 好：使用trigram扩展（PostgreSQL）
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%Smith%';
OR条件
-- 坏：OR条件可以阻止索引使用
SELECT * FROM users WHERE email = 'user@example.com' OR name = 'John';
-- 好：使用UNION
SELECT * FROM users WHERE email = 'user@example.com'
UNION
SELECT * FROM users WHERE name = 'John';
-- 好：使用IN子句
SELECT * FROM users WHERE email IN ('user@example.com', 'user2@example.com');
子查询
-- 坏：子查询可能效率低下
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
-- 好：使用JOIN
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;
-- 好：使用EXISTS
SELECT u.*
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.total > 1000
);
索引列上的函数
-- 坏：索引列上的函数阻止索引使用
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- 好：存储数据时使用规范化形式
SELECT * FROM users WHERE email = 'user@example.com';
-- 好：使用表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
大事务
// 坏：大事务持有锁太久
async function processLargeBatch() {
const transaction = await db.$transaction([
db.user.create({ data: user1 }),
db.user.create({ data: user2 }),
db.user.create({ data: user3 }),
// ... 1000多个操作
]);
}
// 好：分批处理
async function processInBatches(users: any[]) {
const batchSize = 100;
for (let i = 0; i < users.length; i += batchSize) {
const batch = users.slice(i, i + batchSize);
await db.$transaction(
batch.map(user => db.user.create({ data: user }))
);
}
}
缺少索引
-- 坏：频繁查询的列上没有索引
SELECT * FROM orders WHERE user_id = 123;
-- 好：创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 坏：外键上没有索引
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id), -- 没有索引
total DECIMAL(10, 2)
);
-- 好：始终索引外键
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total DECIMAL(10, 2)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
过度索引
-- 坏：太多索引会减慢写入
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_users_updated ON users(updated_at);
CREATE INDEX idx_users_name_email ON users(name, email);
CREATE INDEX idx_users_email_name ON users(email, name);
-- ... 更多索引
-- 好：只创建必要的索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_created ON users(status, created_at);
最佳实践
-
查询优化
- 使用EXPLAIN分析查询计划
- 只选择需要的列
- 使用适当的连接类型
- 避免在索引列上使用函数
-
索引策略
- 在频繁查询的列上创建索引
- 为多列查询使用复合索引
- 考虑为过滤查询使用部分索引
- 监控并移除未使用的索引
-
连接管理
- 使用连接池
- 配置适当的池大小
- 设置连接超时
- 监控连接使用情况
-
缓存
- 缓存频繁访问的数据
- 实施适当的缓存失效
- 使用适当的TTL值
- 监控缓存命中率
-
维护
- 定期进行真空和分析操作
- 监控慢查询
- 定期重建索引
- 保持统计信息更新