database-optimization

用途

该技能面向 PostgreSQL 与 MySQL 的数据库性能优化，提供查询优化、索引策略与性能调优的实践指导。

主要能力

EXPLAIN 分析

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

PostgreSQL：

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

MySQL：

EXPLAIN ANALYZE SELECT ...;

需关注的关键指标：

大表上的 Seq Scan 表示缺少索引
高行数的 Nested Loop 可考虑 hash/merge join
无索引的 Sort 应在排序列上添加索引
预估行数与实际行数偏差表示统计信息过期，运行 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 次查询即为警示信号。

连接池

经验法则：pool_size = (core_count * 2) + disk_count

典型 Web 应用：每个应用实例 10-20 个连接。

PostgreSQL：

在无服务器/高连接场景使用 PgBouncer 的 transaction 模式
设置 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 或需要快速删除旧数据时进行分区。

查询优化检查清单

运行 EXPLAIN ANALYZE 并阅读执行计划
检查超过 1 万行表上的顺序扫描
验证索引使用情况（检查 pg_stat_user_indexes 中的 idx_scan）
查找阻止索引使用的隐式类型转换
用具体列替换 SELECT *
为只需子集的查询添加 LIMIT
使用 EXISTS 而非 COUNT(*) > 0
批量 INSERT/UPDATE 操作（每批 500-1000 行）
避免在 WHERE 子句中对索引列使用函数
监控慢查询日志（pg: log_min_duration_statement = 100）

危险模式

在无索引列上使用 LIKE '%term%'（改用全文检索）
ORDER BY RANDOM()（改用 TABLESAMPLE 或应用层随机化）
SELECT DISTINCT 掩盖连接问题
UPDATE/DELETE 缺少 WHERE（先用 SELECT 验证）
长时间运行的事务持有锁
使用 OFFSET 进行深度分页（改用 keyset/cursor 分页）

使用方式

该技能以文档形式提供数据库优化的操作指南与代码示例，供开发者在优化 PostgreSQL 或 MySQL 时参考。具体安装或调用方式暂未提供。

适用场景

适用于需要优化 PostgreSQL 或 MySQL 查询性能、设计索引、排查 N+1 查询、配置连接池、实施读写分离或分区策略的开发与运维场景。

限制

该技能仅覆盖 PostgreSQL 与 MySQL，不涉及其他数据库。具体工具集成方式、版本兼容性与自动化能力暂未提供。