Engineering Database

数据库性能优化实战：从索引设计到查询调优

为什么你的应用越来越慢？80% 的性能问题来自数据库。分享我 6 年来在 PostgreSQL 和 MySQL 上的优化经验，包括索引策略、查询优化和架构设计模式。

Ioodu · · Updated: Feb 12, 2026 · 22 min read
#Database #PostgreSQL #MySQL #Performance #Optimization #Indexing

那个拖垮系统的查询

2022 年，我们的用户dashboard页面加载时间从 2 秒变成了 15 秒。

排查发现罪魁祸首是一个看似简单的查询：

SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

问题？orders 表有 5000 万行，这个查询全表扫描，每次执行 12 秒。

加一个索引后：查询时间降到 5 毫秒。

这次经历让我明白：数据库性能优化是后端工程师的必修课。

数据库性能的本质

性能瓶颈在哪里

应用层 ──10%──→ 网络传输 ──5%──→ 数据库连接 ──85%──→ SQL 执行
                                                        ↓
                                                ┌─────────────┐
                                                │ 解析 SQL     │
                                                │ 生成执行计划 │
                                                │ 访问数据     │ ← 瓶颈在这里
                                                │ 返回结果     │
                                                └─────────────┘

80/20 法则：80% 的数据库性能问题来自 20% 的查询。

性能指标

指标健康值警告值危险值
查询平均耗时< 10ms10-100ms> 100ms
慢查询比例< 1%1-5%> 5%
索引命中率> 95%90-95%< 90%
连接池使用率< 70%70-90%> 90%
CPU 使用率< 50%50-80%> 80%

索引：数据库的性能加速器

索引的工作原理

类比：索引就像书的目录。

没有目录：你需要翻完整本书（全表扫描）。 有目录：直接跳到对应页（索引查找）。

B-Tree 索引结构（PostgreSQL/MySQL 默认）：

                    [50]
                   /    \
               [25]      [75]
              /   \      /    \
          [10]   [30] [60]    [90]
          ...    ...   ...     ...

查找 30：从根节点开始，3 次磁盘 I/O 即可定位

索引类型选择

索引类型适用场景优点缺点
B-Tree等值查询、范围查询通用，平衡大数据量维护成本高
Hash精确匹配O(1) 查找不支持范围查询
GIN数组、JSON、全文搜索多值索引写入慢，占用空间大
GiST地理数据、范围类型灵活查询效率取决于数据分布
BRIN大数据量时序数据体积小，维护快只适合有序数据

索引设计原则

原则 1：选择性高的列优先

选择性 = 不同值数量 / 总行数

-- 高选择性（好）
email: 1,000,000 / 1,000,000 = 1.0
user_id: 1,000,000 / 1,000,000 = 1.0

-- 低选择性（差）
status: 5 / 1,000,000 = 0.000005
gender: 2 / 1,000,000 = 0.000002

反例：在 gender 字段上建索引（只有男/女），查询仍然要扫描一半数据。

原则 2：最左前缀匹配

-- 联合索引 (a, b, c)
CREATE INDEX idx_abc ON orders(a, b, c);

-- ✅ 有效使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 部分使用

-- ❌ 不使用索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

原则 3：覆盖索引

覆盖索引：查询所需的所有列都在索引中，无需回表。

-- 索引包含 user_id 和 created_at
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- ✅ 覆盖索引查询
SELECT user_id, created_at FROM orders WHERE user_id = 123;
-- 只需访问索引，不需要访问表数据

-- ❌ 非覆盖索引查询
SELECT * FROM orders WHERE user_id = 123;
-- 找到索引后，还要回表取其他列

覆盖索引的性能提升：

  • 普通索引：需要 2 次磁盘 I/O（索引 + 表）
  • 覆盖索引：只需 1 次磁盘 I/O（仅索引）

原则 4：避免过多索引

索引的成本：

  • 写入变慢（每次 INSERT/UPDATE/DELETE 都要维护索引）
  • 占用磁盘空间
  • 缓存效率降低

建议：

  • 单表索引数量 < 5 个
  • 频繁更新的表，索引数量 < 3 个
  • 定期清理无用索引

实战：订单表索引设计

-- 订单表结构
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL, -- pending, paid, shipped, delivered, cancelled
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 业务查询分析：
-- Q1: 查询用户的订单列表（高频）
-- Q2: 查询某状态的订单（中频）
-- Q3: 查询某时间段的订单（低频，后台用）

-- 索引设计
-- 1. 主键索引（自动创建）
-- PRIMARY KEY (id)

-- 2. 用户订单查询（高频，覆盖索引）
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC)
INCLUDE (status, amount);

-- 3. 状态查询（中频）
CREATE INDEX idx_orders_status_created
ON orders(status, created_at DESC);

-- 4. 时间范围查询（低频）
CREATE INDEX idx_orders_created
ON orders(created_at);

查询优化：让 SQL 跑得更快

EXPLAIN 分析执行计划

PostgreSQL：

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

关键指标：

指标含义优化目标
cost预估成本越低越好
actual time实际执行时间< 10ms 为佳
rows扫描行数接近返回行数
Shared Hits缓存命中越高越好
Shared Reads磁盘读取越低越好

常见查询优化技巧

1. 避免 SELECT *

-- ❌ 错误：查询所有列
SELECT * FROM users WHERE id = 123;
-- 需要回表取所有列的数据

-- ✅ 正确：只查询需要的列
SELECT id, name, email FROM users WHERE id = 123;
-- 如果索引覆盖这些列，无需回表

2. 避免在索引列上使用函数

-- ❌ 错误：函数导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

-- ✅ 正确：范围查询使用索引
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02';

3. 避免隐式类型转换

-- ❌ 错误：类型不匹配
-- phone 是 VARCHAR，传入 INT
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 正确：类型匹配
SELECT * FROM users WHERE phone = '13800138000';

4. 大分页优化

-- ❌ 错误：深度分页，越往后越慢
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
-- 需要扫描 100020 行，丢弃 100000 行

-- ✅ 正确：基于游标/书签的分页
SELECT * FROM orders
WHERE created_at < '2024-01-15 10:00:00'
ORDER BY created_at DESC
LIMIT 20;
-- 使用索引，只扫描 20 行

-- ✅ 更好的方案：记住上次的位置
SELECT * FROM orders
WHERE (created_at, id) < ('2024-01-15 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

5. OR 条件优化

-- ❌ 错误：OR 导致索引失效
SELECT * FROM orders
WHERE user_id = 123 OR status = 'pending';

-- ✅ 正确：拆分为 UNION
SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 123;

-- 或者使用 IN（如果适用）
SELECT * FROM orders WHERE user_id IN (123, 456);

6. JOIN 优化

-- ❌ 错误：大表驱动小表
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';
-- orders 表很大，users 表较小

-- ✅ 正确：小表驱动大表，先过滤
SELECT * FROM (
    SELECT * FROM users WHERE status = 'active'
) u
JOIN orders o ON o.user_id = u.id;

-- 确保 JOIN 列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_status ON users(status);

7. 子查询优化

-- ❌ 错误：相关子查询，每行都执行
SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 10;

-- ✅ 正确：使用 JOIN 替代
SELECT u.* FROM users u
JOIN (
    SELECT user_id, COUNT(*) as cnt
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) > 10
) o ON u.id = o.user_id;

-- 或者使用 EXISTS（有时更高效）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    LIMIT 11
);

批量操作优化

批量插入

-- ❌ 错误：单条插入，N 次网络往返
INSERT INTO orders (user_id, amount) VALUES (1, 100);
INSERT INTO orders (user_id, amount) VALUES (2, 200);
-- ... 1000 次

-- ✅ 正确：批量插入
INSERT INTO orders (user_id, amount) VALUES
(1, 100), (2, 200), (3, 300), ...;

-- 或者用 COPY（PostgreSQL）
COPY orders (user_id, amount) FROM '/path/to/data.csv' CSV;

批量更新

-- ❌ 错误：逐条更新
UPDATE orders SET status = 'shipped' WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE id = 2;

-- ✅ 正确：批量更新
UPDATE orders
SET status = 'shipped'
WHERE id IN (1, 2, 3, ...);

-- 或者使用 CASE
UPDATE orders
SET status = CASE id
    WHEN 1 THEN 'shipped'
    WHEN 2 THEN 'delivered'
    -- ...
END
WHERE id IN (1, 2, ...);

表设计优化

数据类型选择

数据类型推荐选择避免使用
整数INT/BIGINT根据范围选择，不要过度使用 BIGINT
字符串VARCHAR(n)TEXT（除非真的需要无限长度）
日期时间TIMESTAMPDATETIME（MySQL），使用 TIMESTAMP
JSONJSONB (PG) / JSON (MySQL 8+)TEXT 存储 JSON
布尔BOOLEANINT (0/1)，TINYINT
枚举VARCHAR + CHECK 约束ENUM 类型（不利于扩展）

规范化 vs 反规范化

规范化（3NF）：

  • 优点：数据一致性，无冗余
  • 缺点：JOIN 多，查询复杂

反规范化：

  • 优点：查询快，无需 JOIN
  • 缺点：数据冗余，维护复杂

实践建议：

  • 写入频繁、查询简单：规范化
  • 读取频繁、查询复杂：适度反规范化

反规范化示例：

-- 原始设计（规范化）
users: id, name
orders: id, user_id, amount
-- 查询用户总消费需要 JOIN + GROUP BY

-- 反规范化：users 表增加冗余字段
users: id, name, total_spent
-- 查询直接读取，但需要在订单变更时更新

-- 触发器自动更新（PostgreSQL）
CREATE OR REPLACE FUNCTION update_user_total_spent()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET total_spent = total_spent + NEW.amount WHERE id = NEW.user_id;
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE users SET total_spent = total_spent - OLD.amount + NEW.amount WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET total_spent = total_spent - OLD.amount WHERE id = OLD.user_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_update_total
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_user_total_spent();

分区表

适用场景：

  • 单表数据量 > 1000 万行
  • 数据有明显的时间特征（日志、订单）
  • 查询通常只涉及部分数据

PostgreSQL 分区示例：

-- 按时间分区（按月）
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2),
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 查询优化：PostgreSQL 自动只扫描相关分区
SELECT * FROM orders WHERE created_at >= '2024-01-15';
-- 只扫描 orders_2024_01 分区

架构设计模式

读写分离

┌─────────┐     ┌─────────────┐     ┌─────────┐
│  应用   │────→│ 主库（写）  │     │ 从库（读）│
└─────────┘     └─────────────┘     └─────────┘
                      │                      │
                      └──────────┬───────────┘
                                 │
                            主从复制

实现：

  • PostgreSQL：流复制
  • MySQL：主从复制
  • 或使用中间件：ProxySQL、MaxScale

应用层实现（Node.js 示例）：

// 读操作走从库
const user = await readPool.query('SELECT * FROM users WHERE id = ?', [id]);

// 写操作走主库
await writePool.query('UPDATE users SET name = ? WHERE id = ?', [name, id]);

// 写后读问题：延迟可能导致读不到最新数据
// 解决方案：强制走主库
const user = await writePool.query('SELECT * FROM users WHERE id = ?', [id]);

缓存策略

┌─────────┐     ┌─────────┐     ┌─────────┐     ┌─────────┐
│  应用   │────→│  缓存   │────→│ 数据库  │
└─────────┘     │ (Redis) │     └─────────┘
                └─────────┘

缓存模式：

模式适用场景风险
Cache-Aside读多写少缓存击穿、穿透
Write-Through强一致性要求写性能下降
Write-Behind高写入吞吐量数据丢失风险

Cache-Aside 实现：

async function getUser(id) {
    // 1. 查缓存
    const cached = await redis.get(`user:${id}`);
    if (cached) return JSON.parse(cached);

    // 2. 缓存未命中，查数据库
    const user = await db.query('SELECT * FROM users WHERE id = ?', [id]);
    if (!user) return null;

    // 3. 写入缓存（TTL 5 分钟）
    await redis.setex(`user:${id}`, 300, JSON.stringify(user));

    return user;
}

async function updateUser(id, data) {
    // 1. 更新数据库
    await db.query('UPDATE users SET ? WHERE id = ?', [data, id]);

    // 2. 删除缓存（不是更新，避免并发问题）
    await redis.del(`user:${id}`);
}

缓存问题处理：

// 缓存穿透：查询不存在的数据
// 解决：缓存空值
const user = await db.query('...');
if (!user) {
    await redis.setex(`user:${id}:null`, 60, 'null'); // 短期缓存空值
    return null;
}

// 缓存击穿：热点 key 过期，瞬间大量请求
// 解决：互斥锁
async function getHotDataWithLock(key) {
    const cached = await redis.get(key);
    if (cached) return cached;

    // 获取锁
    const locked = await redis.set(key + ':lock', '1', 'EX', 10, 'NX');
    if (!locked) {
        // 没拿到锁，等待后重试
        await sleep(100);
        return getHotDataWithLock(key);
    }

    try {
        const data = await db.query('...');
        await redis.setex(key, 300, JSON.stringify(data));
        return data;
    } finally {
        await redis.del(key + ':lock');
    }
}

// 缓存雪崩：大量 key 同时过期
// 解决：随机过期时间
const ttl = 300 + Math.random() * 60; // 300-360 秒
await redis.setex(key, ttl, data);

连接池管理

// PostgreSQL 连接池配置
const pool = new Pool({
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    user: 'user',
    password: 'pass',

    // 连接池配置
    max: 20,           // 最大连接数
    min: 5,            // 最小连接数
    acquire: 30000,    // 获取连接超时
    idle: 10000,       // 连接空闲超时
    evict: 1000        // 检查空闲连接间隔
});

// 使用连接池
const client = await pool.connect();
try {
    const result = await client.query('SELECT * FROM users');
    return result.rows;
} finally {
    client.release(); // 释放回连接池
}

连接池大小建议：

  • CPU 核心数 × 2 + 有效磁盘数
  • Web 应用：10-50
  • 批处理：5-10
  • 监控活跃连接数和等待队列

监控与诊断

慢查询日志

PostgreSQL 配置：

# postgresql.conf
log_min_duration_statement = 100  # 记录超过 100ms 的查询
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'none'  # 不记录所有查询，只记录慢查询

MySQL 配置：

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.1  # 100ms

pg_stat_statements（PostgreSQL）

-- 启用扩展
CREATE EXTENSION pg_stat_statements;

-- 查询最耗时的 SQL
SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 重置统计
SELECT pg_stat_statements_reset();

性能监控工具

工具功能推荐场景
pgAdminPostgreSQL 管理开发调试
MySQL WorkbenchMySQL 管理开发调试
pgHeroPostgreSQL 性能分析生产监控
Percona MonitoringMySQL 监控生产监控
Datadog综合监控企业级
Prometheus + Grafana开源监控自建监控

总结：数据库优化检查清单

设计阶段

  • 选择合适的数据类型
  • 设计合理的表结构（规范化/反规范化）
  • 规划分区策略（大数据量）
  • 设计索引策略
  • 规划缓存策略

开发阶段

  • 使用 EXPLAIN 分析查询
  • 避免 SELECT *
  • 使用覆盖索引
  • 优化深度分页
  • 批量操作替代单条
  • 使用连接池

部署阶段

  • 配置连接池大小
  • 启用慢查询日志
  • 配置监控告警
  • 设置备份策略

运维阶段

  • 定期分析慢查询
  • 定期清理无用索引
  • 监控磁盘空间增长
  • 定期分析表（ANALYZE）
  • 规划扩容策略

参考资源

书籍：

  • 《High Performance MySQL》Baron Schwartz
  • 《PostgreSQL 9.0 High Performance》Gregory Smith
  • 《SQL Performance Explained》Markus Winand

官方文档：

在线工具：

你遇到过哪些数据库性能问题？有什么优化技巧？欢迎分享。

本文的优化技巧来自真实生产环境，帮助过多个系统从「卡顿」到「丝滑」。

---

评论