1. 连接池：数据库操作的交通指挥员
想象一下高速公路收费站场景：每条车道就像数据库连接，车流量大的时候如果反复开关车道，必然造成效率低下。连接池就是管理这些车道的智能调度系统，通过复用已有连接显著提升数据库操作效率。
PostgreSQL配置示例（pg模块）：
const { Pool } = require('pg');
// 连接池黄金参数配置
const pool = new Pool({
user: 'db_user',
host: '127.0.0.1',
database: 'order_system',
password: 'securePass123!',
port: 5432,
max: 20, // 最大连接数（根据服务器内存调整）
min: 4, // 最小保活连接
idleTimeoutMillis: 30000, // 空闲连接超时(ms)
connectionTimeoutMillis: 5000 // 连接等待超时
});
// 使用示例
async function getUserOrders(userId) {
const client = await pool.connect();
try {
const res = await client.query(
'SELECT * FROM orders WHERE user_id = $1',
[userId]
);
return res.rows;
} finally {
client.release(); // 必须释放连接！
}
}
参数配置指南：
max设置应同时考虑应用并发量和数据库最大连接数限制min建议设置为常规并发量的1/3，避免频繁创建销毁idleTimeout需根据业务峰谷周期调整，通常30秒-5分钟- 连接超时时间要略短于API网关超时设置
2. SQL语句优化的实战技巧
2.1 索引的正确使用姿势
// 创建覆盖索引示例
await pool.query(`
CREATE INDEX idx_orders_user_status
ON orders (user_id, status)
INCLUDE (total_amount, created_at)
`);
// 使用索引的查询
const efficientQuery = `
SELECT user_id, total_amount
FROM orders
WHERE user_id = $1 AND status = 'completed'
`;
优化要点：
- 联合索引字段顺序按区分度排列
- 使用
EXPLAIN ANALYZE分析查询计划 - 定期重建索引维护查询效率
2.2 分页查询加速方案对比
// 传统分页（效率随页码降低）
const slowPaging = `
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10 OFFSET ${(page-1)*10}
`;
// 游标分页优化（保持稳定效率）
const fastPaging = pageToken ? `
SELECT * FROM products
WHERE created_at < $1
ORDER BY created_at DESC
LIMIT 10
` : `
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10
`;
3. 事务处理的十八般武艺
3.1 基础事务模板
async function transferFunds(senderId, receiverId, amount) {
const client = await pool.connect();
try {
await client.query('BEGIN');
// 扣除发送方余额
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE user_id = $2',
[amount, senderId]
);
// 增加接收方余额
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE user_id = $2',
[amount, receiverId]
);
// 验证余额不为负
const checkResult = await client.query(
'SELECT balance FROM accounts WHERE user_id = $1',
[senderId]
);
if (checkResult.rows[0].balance < 0) {
throw new Error('Insufficient balance');
}
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
3.2 事务隔离级别实战
// 设置可重复读隔离级别
await client.query('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
// 查看当前隔离级别
const { rows } = await client.query('SHOW transaction_isolation');
console.log(rows[0].transaction_isolation); // 输出: repeatable-read
4. 进阶性能优化锦囊
4.1 批量插入的六种武器
// 使用COPY命令实现高效导入
const data = [[1, '手机', 2999], [2, '笔记本', 8999]];
const csvData = data.map(row => row.join(',')).join('\n');
await client.query(`
COPY products (id, name, price)
FROM stdin WITH (FORMAT csv)
`);
client.query({ rowMode: 'csv', text: csvData });
4.2 查询缓存实战策略
const queryCache = new Map();
async function getProductDetails(productId) {
if (queryCache.has(productId)) {
return queryCache.get(productId);
}
const { rows } = await pool.query(
'SELECT * FROM products WHERE id = $1',
[productId]
);
queryCache.set(productId, rows[0]);
return rows[0];
}
5. 典型应用场景分析
电商系统优化案例：
- 连接池应对秒杀高并发
- 商品搜索使用全文检索优化
- 订单处理严格采用事务
- 商品详情页实施查询缓存
物联网数据处理：
- 时序数据采用专用存储
- 批量上报使用COPY命令
- 设备状态更新启用乐观锁
6. 优化方案选型指南
| 优化手段 | 适用场景 | 收益等级 | 实施难度 |
|---|---|---|---|
| 连接池配置 | 所有数据库交互 | ★★★★★ | ★★ |
| 查询重构 | 慢查询接口 | ★★★★ | ★★★ |
| 事务优化 | 资金类操作 | ★★★★ | ★★★★ |
| 索引优化 | 复杂查询条件 | ★★★★ | ★★★ |
| 缓存策略 | 热点数据读取 | ★★★★ | ★★ |
7. 注意事项与实战经验
- 连接泄漏排查：
// 使用async_hooks跟踪连接
const asyncHooks = require('async_hooks');
const activeClients = new Set();
asyncHooks.createHook({
init(asyncId, type, triggerAsyncId, resource) {
if (resource instanceof pg.Client) {
activeClients.add(resource);
}
},
destroy(asyncId) {
// 实现清理逻辑
}
}).enable();
- 慢查询监控方案：
pool.on('connect', (client) => {
const queryStart = Date.now();
client.on('query', (query) => {
query.startTime = Date.now();
});
client.on('end', () => {
const duration = Date.now() - queryStart;
if (duration > 1000) {
console.warn(`Slow query detected: ${duration}ms`);
}
});
});
8. 全站优化总结
数据库性能优化是持续改进的过程，需要：
- 建立性能基准指标
- 定期分析慢查询日志
- 监控连接池使用状况
- 进行定期索引维护
- 针对业务场景选择合适的事务级别