⚡

数据库优化 database-optimization

数据库优化技能专注于提升PostgreSQL和MySQL数据库的性能，涉及EXPLAIN分析、索引策略、查询优化、连接池、读取副本和分区技术。关键词：数据库性能调优、查询优化、索引策略、PostgreSQL、MySQL、性能提升、SQL优化。

后端开发 0 次安装 0 次浏览 更新于 3/8/2026

name: 数据库优化 description: 用于PostgreSQL和MySQL的查询优化、索引策略和数据库性能调优

数据库优化

EXPLAIN分析

在优化之前始终运行EXPLAIN ANALYZE。从下往上阅读输出。

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;

需要关注的关键指标：

  • Seq Scan 在大表上 = 缺少索引
  • Nested Loop 有高行数 = 考虑哈希/合并连接
  • Sort 没有索引 = 在排序列上添加索引
  • Rows estimated vs actual 差异 = 过时统计信息，运行 ANALYZE

索引策略

B树（默认，大多数情况）

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

用于：等值查询、范围查询、排序。在复合索引中，列顺序重要：首先放置等值列，然后是范围/排序列。

部分索引（PostgreSQL）

CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

当查询总是基于特定条件过滤时使用。比完整索引小得多。

GIN（PostgreSQL - 数组、JSONB、全文搜索）

CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_docs_search ON documents USING GIN (to_tsvector('english', content));

GiST（PostgreSQL - 空间、范围类型）

CREATE INDEX idx_locations_point ON locations USING GiST (coordinates);
CREATE INDEX idx_events_period ON events USING GiST (tsrange(start_at, end_at));

覆盖索引（仅索引扫描）

-- PostgreSQL
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name);

-- MySQL
CREATE INDEX idx_users_email_name ON users (email, name);

N+1 查询检测

症状：1个查询获取父级 + 每个子级N个查询。

# 不好：N+1
users = db.query(User).all()
for user in users:
    print(user.orders)  # 每个用户触发查询

# 好：急切加载
users = db.query(User).options(joinedload(User.orders)).all()

// 不好：N+1
const users = await User.findAll();
for (const user of users) {
  const orders = await Order.findAll({ where: { userId: user.id } });
}

// 好：批量加载
const users = await User.findAll({ include: [Order] });

检测：启用查询日志，计算每个请求的查询数。单个端点超过10个查询是警告信号。

连接池

经验法则：pool_size = (核心数 * 2) + 磁盘数
典型Web应用：每个应用实例10-20个连接

PostgreSQL:

  • 在高连接场景中使用PgBouncer事务模式
  • 设置 idle_in_transaction_session_timeout = '30s'
  • 使用 pg_stat_activity 监控

MySQL:

  • 根据可用RAM设置 max_connections（每个连接使用约10MB）
  • 使用ProxySQL进行连接复用
  • 使用 SHOW PROCESSLIST 监控

读取副本

  • 路由所有 SELECT 查询到副本
  • 路由所有写入到主库
  • 考虑复制延迟（通常10-100ms）
  • 不要在副本上进行读后写；对于一致性关键的读取使用主库
  • 使用连接级别路由，而非查询级别
# SQLAlchemy 读取副本路由
class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None):
        if self._flushing or self.is_modified():
            return engines["primary"]
        return engines["replica"]

分区策略

范围分区（时间序列数据）

-- PostgreSQL
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    data jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_q1 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE events_2025_q2 PARTITION OF events
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

哈希分区（均匀分布）

CREATE TABLE sessions (
    id uuid PRIMARY KEY,
    user_id bigint NOT NULL
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);

分区当表超过50-100GB或需要快速删除旧数据时。

查询优化检查清单

  1. 运行 EXPLAIN ANALYZE 并阅读计划
  2. 检查在大表（>10K行）上的顺序扫描
  3. 验证索引使用（检查 pg_stat_user_indexes 中的 idx_scan）
  4. 查找阻止索引使用的隐式类型转换
  5. 用特定列替换 SELECT *
  6. 添加 LIMIT 到仅需要子集的查询
  7. 使用 EXISTS 代替 COUNT(*) > 0
  8. 批量 INSERT/UPDATE 操作（每批500-1000行）
  9. 避免在 WHERE 子句中对索引列使用函数
  10. 监控慢查询日志（pg: log_min_duration_statement = 100）

危险模式

  • LIKE '%term%' 在未索引列上（使用全文搜索替代）
  • ORDER BY RANDOM()（使用 TABLESAMPLE 或应用级别随机化）
  • SELECT DISTINCT 掩盖连接问题
  • UPDATE/DELETE 上缺少 WHERE（首先用 SELECT 验证）
  • 长时间运行的事务持有锁
  • 使用 OFFSET 进行深度分页（使用键集/游标分页替代）