数据库学习教程

    索引、执行计划与调优

    索引类型与选择

    索引类型适用示例
    B-Tree等值、范围、排序（默认）普通列
    Hash仅等值USING HASH
    GIN数组、JSONB、全文、pg_trgm多值包含查询
    GiST几何、范围、最近邻PostGIS、TSTZRANGE
    BRIN有序大表（时间序列）与物理顺序相关的列
    SP-GiST非平衡结构（如 IP、四叉树）特殊场景
    -- B-Tree（默认）
CREATE INDEX idx_orders_user ON orders (user_id);

-- 部分索引：只为满足条件的行建索引，更小更快
CREATE INDEX idx_orders_paid ON orders (created_at)
WHERE status = 'paid';

-- 表达式索引：支撑函数查询
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 覆盖索引：INCLUDE 额外列，避免回表
CREATE INDEX idx_orders_user_cover ON orders (user_id) INCLUDE (amount, status);

-- GIN：JSONB
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- BRIN：时间序列大表，体积极小
CREATE INDEX idx_logs_time_brin ON logs USING BRIN (created_at);
    Tip

    部分索引（Partial Index） 是 PG 的独门利器：当某类查询只涉及很小比例的行（如 status='pending'），部分索引的体积和写入成本都远低于全表索引。

    读懂 EXPLAIN ANALYZE

    EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, o.amount
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

    要点：

    • cost=起始..总代价 rows=预估行数 width=；actual time=... rows=... loops=... 是实测值。
    • 预估行数与实际行数差异巨大 → 统计信息不准或相关性误判，需 ANALYZE 或调 default_statistics_target。
    • 关注扫描方式：Seq Scan（全表）、Index Scan、Index Only Scan（覆盖）、Bitmap Heap Scan。
    • 关注连接方式：Nested Loop、Hash Join、Merge Join。
    • BUFFERS 显示缓存命中：shared read 高说明磁盘 I/O 多。
    ANALYZE orders;                         -- 更新统计信息
CREATE EXTENSION pg_stat_statements;    -- 收集慢 SQL 统计

    VACUUM 与自动清理

    PG 的 MVCC 会让 UPDATE/DELETE 产生死元组，需要 VACUUM 回收；autovacuum 默认开启自动处理。

    VACUUM (VERBOSE, ANALYZE) orders;  -- 回收并更新统计
VACUUM FULL orders;                -- 重写表、回收磁盘（会长时间加排他锁，慎用）

-- 查看膨胀与死元组
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
    Warning

    大量 UPDATE/DELETE 却不清理会导致表膨胀、扫描变慢、事务 ID 回卷（wraparound）风险。对写频繁的大表，务必确认 autovacuum 能跟上，必要时应手动调优其触发阈值。VACUUM FULL 会锁表，生产环境优先使用 pg_repack 在线整理。

    关键参数调优

    参数作用经验值
    shared_buffers共享内存缓存机器内存的 25%
    work_mem每排序/哈希操作内存4~64MB，按并发调整
    maintenance_work_mem维护操作内存256MB~1GB
    effective_cache_size优化器假设的可用缓存内存的 50%~75%
    max_connections最大连接数不宜过大，用连接池
    -- 查看当前设置
SHOW shared_buffers;
SELECT name, setting, unit FROM pg_settings WHERE name = 'work_mem';
    Tip

    优先用连接池而不是堆 max_connections。PG 每个连接是一个进程，开销远大于 MySQL 线程。推荐 PgBouncer 或应用侧 pgxpool，把连接数控制在合理范围。

    小结

    • 索引按场景选型：B-Tree 通用，GIN 管多值，BRIN 管有序大表，部分/覆盖索引省空间。
    • EXPLAIN ANALYZE 的预估 vs 实际行数是诊断第一现场。
    • 关注死元组与膨胀，确保 autovacuum 跟得上写入。
    • 调参前先解决索引与 SQL 问题；连接层面用池化。