database-optimization

数据库优化

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-tree（默认，适用于大多数情况）

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 次查询。

# BAD: N+1
users = db.query(User).all()
for user in users:
    print(user.orders)  # triggers query per user

# GOOD: eager load
users = db.query(User).options(joinedload(User.orders)).all()

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

// GOOD: batch load
const users = await User.findAll({ include: [Order] });

检测方法：启用查询日志，统计每个请求的查询次数。单个端点的查询次数超过 10 次就是危险信号。

连接池

Rule of thumb: pool_size = (core_count * 2) + disk_count
Typical web app: 10-20 connections per app instance

PostgreSQL：

• 在无服务器或连接数较高的场景中，使用事务模式的 PgBouncer
• 设置 idle_in_transaction_session_timeout = '30s'
• 使用 pg_stat_activity 进行监控

MySQL：

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

只读副本

• 将所有 SELECT 查询路由到副本
• 将所有写操作路由到主库
• 考虑复制延迟（通常为 10-100ms）
• 切勿在副本上执行写后读；对一致性要求高的读取应使用主库
• 使用连接级路由，而非查询级路由

# SQLAlchemy read replica routing
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 进行深度分页（改用键集分页或游标分页）