后端工程

数据库扩展与性能优化：索引策略、分库分表与读写分离完全指南

从SQL优化到分库分表，全面掌握数据库性能优化技巧。涵盖索引设计、查询优化、连接池管理、读写分离、分片策略、缓存策略及PostgreSQL/MySQL实战。

Leeting Yan 5 分钟阅读 2286 字

引言

数据库往往是系统瓶颈的第一站。无论应用层做了多少缓存和异步处理，最终大量请求都会落到数据库上。一个未经优化的慢查询在高并发下会迅速耗尽连接、拖垮系统。

数据库优化的核心思路：减少访问（缓存）、减少负载（索引与查询优化）、分散压力（读写分离与分库分表）。

目录

1. 索引设计与优化

1.1 索引类型对比

索引类型适用场景优势劣势
B-Tree等值/范围查询、排序通用性强不适合全文搜索
Hash仅等值查询等值查询快不支持范围和排序
GIN全文搜索、数组、JSONB多值类型高效写入开销大
GiST几何数据、范围类型支持空间查询略慢于 GIN
BRIN时间序列、有序大表索引极小仅适用物理有序数据
-- B-Tree 复合索引（注意列顺序：高选择性在前）
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- GIN 索引（JSONB 查询）
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- BRIN 索引（时间序列，索引大小仅为 B-Tree 的 1/1000）
CREATE INDEX idx_logs_created_brin ON logs USING BRIN (created_at);

-- Partial Index（只索引活跃数据）
CREATE INDEX idx_orders_pending ON orders (id) WHERE status = 'pending';

1.2 索引设计原则

  1. 根据查询模式设计索引，而非根据列结构
  2. 复合索引遵循最左前缀原则
  3. 避免过度索引——每个索引降低写入性能约 5-10%
  4. 定期清理无用索引：
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

2. 查询优化

2.1 EXPLAIN ANALYZE 解读

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2025-01-01'
GROUP BY u.name;

关键指标：Seq Scan（全表扫描，大表需避免）、Index Only Scan（最快，不访问表数据）、actual time（实际执行时间）、rows（实际 vs 预估行数差距大说明统计过时）、Buffers: shared hit/read（缓存命中 vs 磁盘读取）。

2.2 常见慢查询优化案例

N+1 查询 → 批量查询或 JOIN：

-- 优化前：每个用户单独查询（N+1）
-- 优化后：一次 JOIN 获取所有数据
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id = ANY(ARRAY[1, 2, 3]);

SELECT * → 指定列（可能触发 Index Only Scan）：

SELECT id, total_amount, status FROM orders WHERE user_id = 123;

相关子查询 → EXISTS：

SELECT name FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 1000
);

3. 连接池管理

3.1 PgBouncer 配置

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
pool_mode = transaction    # 事务级释放（推荐）
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600

三种模式：Session（断开时释放）、Transaction（事务结束时释放，推荐）、Statement（每条 SQL 后释放，不支持事务）。

3.2 连接池大小计算

PostgreSQL 官方公式：connections = (core_count * 2) + effective_spindle_count

4 核 + SSD 示例：(4 * 2) + 0 = 8，考虑网络延迟乘以 2-4 倍，实际设置 16-32。

4. 读写分离架构

4.1 主从复制原理

PostgreSQL 流复制：主库写 WAL → WAL Sender 流式传输 → 从库 WAL Receiver 接收 → Startup 进程回放。

4.2 中间件方案

ProxySQL（MySQL）路由规则示例：

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup) VALUES
  (1, 1, '^SELECT .* FOR UPDATE$', 1),  -- 路由到主库
  (2, 1, '^SELECT', 2),                  -- 路由到从库
  (3, 1, '^(INSERT|UPDATE|DELETE)', 1);  -- 路由到主库

4.3 应用层方案

type DBRouter struct {
    primary  *sqlx.DB
    replicas []*sqlx.DB
    counter  uint64
}

func (r *DBRouter) ReadDB() *sqlx.DB {
    idx := atomic.AddUint64(&r.counter, 1) % uint64(len(r.replicas))
    return r.replicas[idx]
}

注意：写后立即读必须路由到主库，避免复制延迟导致读到旧数据。

5. 分库分表（Sharding）

5.1 水平分片 vs 垂直分片

维度水平分片垂直分片
拆分方式按行拆分按列拆分
适用场景单表数据量过大部分列很少查询
复杂度高（路由、跨片查询）中等

5.2 分片键选择策略

选择标准：高基数（值分布均匀）、查询亲和性（大多数查询包含该字段）、稳定性（不频繁变更）、避免热点。

常见策略：哈希分片（分布均匀，扩容困难）、范围分片（扩容方便，可能产生热点）、一致性哈希（扩容时仅迁移少量数据）。

分片带来的挑战：跨片查询性能差、分布式事务复杂（Saga/2PC）、全局唯一 ID 生成（Snowflake）、跨片 JOIN 不可行。

6. 数据库迁移策略

大表在线迁移方案：

gh-ost（MySQL）：创建影子表 → 同步增量 → 原子切换

gh-ost --host="primary.db" --database="myapp" --table="users" \
  --alter="ADD INDEX idx_email (email)" --allow-on-master --execute

pg_repack（PostgreSQL）：消除表膨胀、重建索引

pg_repack -t users -D myapp

最佳实践：先在从库验证 → 分批迁移减少锁竞争 → 设置监控和回滚方案 → 避免高峰期执行。

7. PostgreSQL vs MySQL 2025 选型

维度PostgreSQL 17MySQL 8.4
JSON 支持原生 JSONB，性能优秀JSON 类型，功能较弱
扩展性丰富生态（PostGIS、TimescaleDB）有限插件机制
全文搜索内置 + GIN 索引内置但较弱
复制逻辑复制 + 物理复制Group Replication
适用场景复杂查询、地理信息、分析型简单 CRUD、高并发读取
社区趋势DB-Engines 排名持续上升成熟稳定，大量存量

2025 建议：新项目优先考虑 PostgreSQL。MySQL 适合团队已有深厚经验或维护存量系统。

8. NoSQL 使用场景

类型推荐产品适用场景
键值存储Redis缓存、会话、排行榜、限流
文档数据库MongoDB灵活 Schema、内容管理
宽列存储Cassandra高写入吞吐、时间序列
图数据库Neo4j关系网络、推荐、欺诈检测
搜索引擎Elasticsearch全文搜索、日志分析

Redis Cache-Aside 模式（Go）：

func GetUser(ctx context.Context, id string) (*User, error) {
    if cached, err := rdb.Get(ctx, "user:"+id).Result(); err == nil {
        var user User; json.Unmarshal([]byte(cached), &user); return &user, nil
    }
    user, err := db.GetUser(ctx, id) // 缓存未命中，查数据库
    if err != nil { return nil, err }
    data, _ := json.Marshal(user)
    ttl := 30*time.Minute + time.Duration(rand.Intn(300))*time.Second // 随机 TTL 防雪崩
    rdb.Set(ctx, "user:"+id, data, ttl)
    return user, nil
}

9. 数据归档与冷数据管理

使用分区表按时间管理数据生命周期：

CREATE TABLE orders (
    id BIGINT, user_id BIGINT, total_amount DECIMAL(10,2),
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

归档策略：热数据（3 个月内，SSD）→ 温数据（3-12 月，HDD 归档库）→ 冷数据（12 月+，对象存储 Parquet 格式）→ 超期清理。推荐使用 pg_partman 自动管理分区创建和归档。

10. 总结与性能优化 Checklist

索引： 分析慢查询识别缺失索引 → 创建合适复合索引 → 清理未使用索引 → 定期维护

查询： EXPLAIN ANALYZE Top 10 慢查询 → 消除 N+1 → 避免 SELECT * → 分页用游标替代 OFFSET

连接： 部署连接池 → 合理设置大小 → 配置超时参数

架构： 读写分离 → 评估分库分表 → Redis 缓存热点 → 历史数据归档分区

监控： 复制延迟告警 → 连接数/锁等待/死锁监控 → 定期 ANALYZE 和 VACUUM

11. 延伸阅读

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页