某电商平台大促前夜，DBA 收到告警：订单列表接口 P99 延迟从 50ms 飙升至 3200ms。排查发现，一条看似简单的查询 SELECT * FROM orders WHERE user_id = ? AND status = 'paid' ORDER BY created_at DESC LIMIT 20 在 5000 万行的订单表上走了全表扫描。加了索引后延迟降到 8ms，但三天后同样的查询又慢了——原因是优化器的统计信息过期，选择了错误的执行计划。与此同时，连接池被大量慢查询占满，整个服务陷入雪崩。

这不是一个索引问题，也不是一个查询问题，而是一个涉及存储引擎、优化器、连接管理三层的架构级问题。本文将从 B+树（B+tree）的物理 I/O 模型出发，逐层拆解数据库性能的核心模式。

一、B+树索引的存储结构与 I/O 成本模型

为什么是 B+树

关系型数据库几乎都选择 B+树作为默认索引结构，根本原因在于磁盘 I/O 的物理特性。磁盘的最小读写单位是页（Page），通常为 4KB 或 8KB。一次随机 I/O（Random I/O）需要 5-10ms（机械磁盘）或 0.1ms（SSD），而顺序 I/O（Sequential I/O）的吞吐量可达随机 I/O 的 100 倍以上。

B+树的设计目标就是最小化查找过程中的磁盘 I/O 次数。

B+树的物理结构

                        [Root Node]
                     /       |       \
              [Internal]  [Internal]  [Internal]
              /   |   \    /   |   \    /   |   \
           [Leaf]-[Leaf]-[Leaf]-[Leaf]-[Leaf]-[Leaf]
            ↕      ↕      ↕      ↕      ↕      ↕
           数据页  数据页  数据页  数据页  数据页  数据页

B+树的关键特征：

• 所有数据存储在叶子节点（Leaf Node），内部节点只存储索引键和指针
• 叶子节点通过双向链表连接，支持高效的范围扫描
• 每个节点对应一个磁盘页，一次 I/O 读取一个节点

扇出率与树高计算

扇出率（Fan-out）是 B+树性能的核心参数，表示每个节点能容纳的子指针数量。

Fan-out = PageSize / (KeySize + PointerSize)

以 InnoDB 为例，页大小 16KB，假设索引键为 8 字节的 BIGINT，指针为 6 字节：

Fan-out = 16384 / (8 + 6) ≈ 1170

树高与可索引的行数关系：

关键结论：对于绝大多数业务表（行数 < 1 亿），B+树的查找只需要 3 次磁盘 I/O。 根节点常驻内存后，实际只需 2 次 I/O。

I/O 成本分析

数据库的查询成本可以分解为三类 I/O：

总成本 = 索引查找成本 + 数据读取成本 + 排序/聚合成本

索引查找成本由树高决定，通常为 2-3 次随机 I/O。

数据读取成本取决于访问模式：

全表扫描成本 = 表页数 × 顺序 I/O 单价
索引范围扫描成本 = 匹配行数 × 随机 I/O 单价（回表场景）

这解释了一个反直觉的现象：当查询需要返回表中大比例数据时（通常 > 15-20%），全表扫描反而比索引扫描更快，因为顺序 I/O 的效率远高于大量随机 I/O。

graph LR
    subgraph 全表扫描["全表扫描<br/>Sequential I/O"]
        S1["Page 1"] --> S2["Page 2"] --> S3["Page 3"] --> S4["Page 4"] --> S5["..."]
    end
    subgraph 索引回表["索引回表<br/>Random I/O"]
        I1["Index Leaf"] --> D3["Page 3"]
        I1 --> D7["Page 7"]
        I1 --> D1["Page 1"]
        I2["Index Leaf"] --> D9["Page 9"]
        I2 --> D4["Page 4"]
    end
    style 全表扫描 fill:#3fb950,stroke:#3fb950,color:#ffffff
    style 索引回表 fill:#f0883e,stroke:#f0883e,color:#ffffff

即使在 NVMe SSD 上，随机 I/O 与顺序 I/O 仍有数倍的性能差距。在 HDD 上，这个差距超过 600 倍。

二、覆盖索引与仅索引扫描

回表的代价

普通索引查找的完整流程是：

1. 在索引 B+树中定位到叶子节点，取得主键值
2. 用主键值到聚簇索引（Clustered Index）中查找完整行数据

第二步称为回表（Table Lookup），每一行都需要一次额外的随机 I/O。当查询返回大量行时，回表成本可能远超索引查找本身。

覆盖索引消除回表

覆盖索引（Covering Index）的核心思想是：将查询需要的所有列都包含在索引中，使查询完全在索引上完成，不需要回表。

-- 原始查询：需要回表
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 10086 AND status = 'paid'
ORDER BY created_at DESC;

-- 普通索引：只能加速查找，仍需回表取 created_at
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 覆盖索引：包含所有查询列，消除回表
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

使用覆盖索引后，EXPLAIN 输出中会出现 Using index（MySQL）或 Index Only Scan（PostgreSQL）标记：

EXPLAIN SELECT user_id, status, created_at
FROM orders
WHERE user_id = 10086 AND status = 'paid'
ORDER BY created_at DESC;

+----+-------------+--------+------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | type | possible_keys            | key     | rows | Extra                    |
+----+-------------+--------+------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | ref  | idx_user_status_created  | idx_... |   42 | Using where; Using index |
+----+-------------+--------+------+--------------------------+---------+------+------+----------+--------------------------+

PostgreSQL 的仅索引扫描与可见性映射

PostgreSQL 的仅索引扫描（Index Only Scan）有一个额外约束：由于 MVCC 机制，索引中不包含行的可见性信息。PostgreSQL 需要检查可见性映射（Visibility Map）来确认行是否对当前事务可见。

Index Only Scan 流程：
1. 在索引中找到匹配的条目
2. 检查 Visibility Map：
   - 如果该页所有行都可见 → 直接从索引返回数据
   - 如果存在不可见行 → 回表检查行的可见性

实践建议： 在频繁更新的表上，定期执行 VACUUM 以更新可见性映射，确保仅索引扫描的效率。

-- 检查表的可见性映射覆盖率
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / greatest(n_live_tup, 1), 2) AS dead_ratio_pct
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC
LIMIT 10;

三、复合索引的列顺序设计

最左前缀原则

复合索引（Composite Index）的列顺序至关重要。B+树按照索引列的定义顺序进行排序，查询只能利用从最左列开始的连续前缀。

CREATE INDEX idx_a_b_c ON t(a, b, c);

-- 可以使用索引的查询
WHERE a = 1                      -- 前缀 (a)
WHERE a = 1 AND b = 2            -- 前缀 (a, b)
WHERE a = 1 AND b = 2 AND c = 3  -- 完整匹配 (a, b, c)
WHERE a = 1 AND b > 5            -- 前缀 (a, b) 但 b 之后的列无法用于范围过滤
WHERE a = 1 ORDER BY b           -- 前缀 (a)，b 用于排序

-- 无法使用索引的查询
WHERE b = 2                      -- 跳过了 a
WHERE b = 2 AND c = 3            -- 跳过了 a
WHERE a = 1 AND c = 3            -- 跳过了 b（a 可用，c 不可用）

列顺序选择策略

设计复合索引时，列顺序的选择遵循以下原则：

原则一：等值查询列在前，范围查询列在后

-- 查询模式：WHERE status = 'paid' AND created_at > '2026-01-01'
-- 正确：等值列在前
CREATE INDEX idx_status_created ON orders(status, created_at);
-- 错误：范围列在前，status 无法利用索引
CREATE INDEX idx_created_status ON orders(created_at, status);

原则二：高选择性列在前（有争议）

传统建议是将基数（Cardinality）高的列放在前面，但这并不总是最优解。关键要看实际查询模式：

-- user_id 基数高（百万级），status 基数低（5 个值）
-- 如果查询总是同时指定 user_id 和 status，顺序影响不大
-- 如果有时只按 user_id 查询，user_id 应该在前

CREATE INDEX idx_user_status ON orders(user_id, status);

原则三：排序列的位置

-- 查询模式：WHERE user_id = ? ORDER BY created_at DESC LIMIT 20
-- created_at 放在 user_id 之后，可以利用索引排序，避免 filesort
CREATE INDEX idx_user_created ON orders(user_id, created_at);

索引设计的权衡

四、查询优化器内部机制

基于成本的优化

现代关系型数据库使用基于成本的优化器（Cost-Based Optimizer, CBO）来选择执行计划。优化器的核心工作是：

1. 枚举候选计划：不同的连接顺序、连接算法、访问路径
2. 估算每个计划的成本：I/O 成本 + CPU 成本
3. 选择成本最低的计划

flowchart TD
    SQL["SQL 查询"] --> Parser["语法解析器<br/>Parser"]
    Parser --> Rewriter["查询重写器<br/>Query Rewriter"]
    Rewriter --> Optimizer["查询优化器<br/>Cost-Based Optimizer"]
    Optimizer --> PlanEnum["计划枚举"]
    Optimizer --> CostEst["成本估算"]
    Optimizer --> StatInfo["统计信息<br/>pg_statistics / INFORMATION_SCHEMA"]
    PlanEnum --> BestPlan["最优执行计划"]
    CostEst --> BestPlan
    StatInfo --> CostEst
    BestPlan --> Executor["执行引擎<br/>Executor"]
    style SQL fill:#388bfd,stroke:#388bfd,color:#ffffff
    style Optimizer fill:#f0883e,stroke:#f0883e,color:#ffffff
    style BestPlan fill:#3fb950,stroke:#3fb950,color:#ffffff
    style Executor fill:#a371f7,stroke:#a371f7,color:#ffffff

基数估算与选择率

优化器做出正确决策的前提是准确估算每一步操作的输出行数（基数，Cardinality）。选择率（Selectivity）是基数估算的核心概念：

选择率 = 满足条件的行数 / 总行数

基数 = 总行数 × 选择率

对于等值查询，假设数据均匀分布：

选择率(col = value) = 1 / NDV(col)

NDV = Number of Distinct Values（不同值的数量）

对于范围查询：

选择率(col > value) = (max - value) / (max - min)

直方图统计

均匀分布假设在真实数据中经常不成立。为了处理数据倾斜（Data Skew），数据库使用直方图（Histogram）来记录数据的实际分布。

PostgreSQL 使用两种直方图：

-- 查看 PostgreSQL 的统计信息
SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

输出示例：

attname     | status
n_distinct  | 5
most_common_vals  | {pending,paid,shipped,delivered,cancelled}
most_common_freqs | {0.35,0.28,0.20,0.12,0.05}
histogram_bounds  | (NULL — 因为已用 MCV 覆盖所有值)

• 最频繁值列表（Most Common Values, MCV）：记录出现频率最高的值及其频率
• 等高直方图（Equi-height Histogram）：将剩余值划分为等频的桶

MySQL 8.0 也引入了直方图支持：

-- MySQL 8.0 创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, user_id WITH 100 BUCKETS;

-- 查看直方图信息
SELECT
    SCHEMA_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    JSON_EXTRACT(HISTOGRAM, '$.histogram-type') AS hist_type,
    JSON_EXTRACT(HISTOGRAM, '$.number-of-buckets-specified') AS buckets
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';

PostgreSQL 查询规划器统计案例

以下是一个 PostgreSQL 中统计信息影响查询计划的真实场景：

-- 创建测试表，status 列严重倾斜
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- 插入 500 万行数据，95% 的订单状态为 'delivered'
INSERT INTO orders (user_id, status, amount, created_at)
SELECT
    (random() * 1000000)::BIGINT,
    CASE
        WHEN random() < 0.95 THEN 'delivered'
        WHEN random() < 0.97 THEN 'shipped'
        WHEN random() < 0.99 THEN 'paid'
        ELSE 'pending'
    END,
    (random() * 10000)::DECIMAL(10, 2),
    NOW() - (random() * INTERVAL '365 days')
FROM generate_series(1, 5000000);

CREATE INDEX idx_orders_status ON orders(status);

-- 收集统计信息
ANALYZE orders;

-- 查询少量数据（status = 'pending'，约 0.5%）→ 走索引
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- Index Scan using idx_orders_status on orders
-- rows=25012 (实际) vs rows=25000 (估算) → 估算准确

-- 查询大量数据（status = 'delivered'，约 95%）→ 走全表扫描
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'delivered';
-- Seq Scan on orders
-- rows=4750089 (实际) vs rows=4750000 (估算) → 估算准确，正确选择全表扫描

优化器根据统计信息正确区分了两种场景：少量匹配走索引，大量匹配走全表扫描。

五、优化器判断失误的场景

统计信息不可能完美反映数据的实时状态，以下场景容易导致优化器做出错误决策。

场景一：统计信息过期

大批量数据写入后，如果未及时更新统计信息，优化器基于旧的统计数据做决策：

-- 表原来有 100 万行，优化器知道这个信息
-- 批量导入 900 万行后，优化器仍然认为表只有 100 万行

-- 此时应该手动触发统计信息更新
ANALYZE orders;                    -- PostgreSQL
ANALYZE TABLE orders;              -- MySQL

PostgreSQL 的 autovacuum 进程会自动更新统计信息，但默认阈值是表的 20% 行数发生变化才触发。对于大表，这个阈值可能太高：

-- 调整特定表的自动分析阈值
ALTER TABLE orders SET (
    autovacuum_analyze_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.01  -- 1% 变化就触发
);

场景二：数据倾斜

当查询使用参数化（Parameterized Query）时，优化器可能用一个”平均”选择率来生成通用计划，忽略不同参数值之间的巨大差异。

-- prepared statement
PREPARE order_by_status(text) AS
    SELECT * FROM orders WHERE status = $1;

-- status = 'pending' 只有 0.5% 的行 → 应该走索引
-- status = 'delivered' 有 95% 的行 → 应该走全表扫描
-- 但 generic plan 可能对两者使用相同的执行计划

PostgreSQL 的解决方案：前 5 次执行使用 custom plan（针对具体参数值优化），之后如果 custom plan 成本与 generic plan 接近，切换到 generic plan。

-- 强制使用 custom plan（PostgreSQL 12+）
SET plan_cache_mode = force_custom_plan;

场景三：相关列

优化器默认假设 WHERE 子句中的多个条件是独立的。当列之间存在相关性时，基数估算会严重偏差：

-- city 和 zipcode 高度相关
-- 优化器假设：P(city='Beijing' AND zipcode='100000') = P(city='Beijing') × P(zipcode='100000')
-- 实际上：P(city='Beijing' AND zipcode='100000') ≈ P(city='Beijing')

SELECT * FROM addresses
WHERE city = 'Beijing' AND zipcode = '100000';
-- 优化器可能严重低估匹配行数

PostgreSQL 11+ 引入了多列统计（Extended Statistics）来解决这个问题：

-- 创建多列统计
CREATE STATISTICS stat_city_zip (dependencies, ndistinct)
ON city, zipcode FROM addresses;

ANALYZE addresses;

场景四：复杂表达式

对列进行函数运算或表达式计算后，优化器无法使用该列的统计信息：

-- 优化器无法估算以下查询的选择率
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- 改写为范围查询，优化器可以利用统计信息
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

优化器失误诊断清单

六、EXPLAIN 计划解读实战

MySQL EXPLAIN 关键字段

EXPLAIN SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

+----+-------------+-------+------+---------------------+------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys       | key              | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------------+------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | o     | ref  | idx_status_created  | idx_status_creat | 82      | const |  520 | Using where; Using filesort |
|  1 | SIMPLE      | u     | eq_ref| PRIMARY            | PRIMARY          | 8       | o.uid |    1 | NULL                        |
+----+-------------+-------+------+---------------------+------------------+---------+-------+------+-----------------------------+

关键字段解读：

• type：访问类型，从优到劣：system > const > eq_ref > ref > range > index > ALL
• key：实际使用的索引
• rows：优化器估算需要扫描的行数
• Extra：
  • Using index：覆盖索引，不需要回表
  • Using filesort：需要额外排序，通常是性能瓶颈
  • Using temporary：使用临时表，常见于 GROUP BY
  • Using where：在存储引擎返回行后，Server 层还需要过滤

PostgreSQL EXPLAIN ANALYZE 解读

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

Limit  (cost=1520.45..1520.50 rows=20 width=52) (actual time=12.345..12.350 rows=20 loops=1)
  Buffers: shared hit=1842 read=156
  ->  Sort  (cost=1520.45..1532.78 rows=4932 width=52) (actual time=12.340..12.345 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=1842 read=156
        ->  Nested Loop  (cost=0.87..1380.22 rows=4932 width=52) (actual time=0.045..11.230 rows=4890 loops=1)
              Buffers: shared hit=1842 read=156
              ->  Index Scan using idx_status_created on orders o  (cost=0.43..685.20 rows=4932 width=28) (actual time=0.030..3.120 rows=4890 loops=1)
                    Index Cond: ((status)::text = 'paid'::text AND created_at > '2026-01-01'::timestamp)
                    Buffers: shared hit=952 read=86
              ->  Index Scan using users_pkey on users u  (cost=0.43..0.14 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=4890)
                    Index Cond: (id = o.user_id)
                    Buffers: shared hit=890 read=70
Planning Time: 0.285 ms
Execution Time: 12.410 ms

关键指标：

• cost：启动成本..总成本，单位是 PostgreSQL 内部成本单位
• actual time：启动时间..总时间，单位是毫秒
• rows：estimated rows vs actual rows，偏差大说明统计信息有问题
• Buffers：shared hit（缓存命中）vs read（磁盘读取），衡量 I/O 效率
• loops：该节点执行的次数，嵌套循环中内表会执行多次

七、连接池大小与数据库并发度

为什么需要连接池

数据库连接（Connection）是昂贵的资源：

• 建立连接的成本：TCP 三次握手 + TLS 协商 + 认证 + 会话初始化，通常 20-50ms
• 连接占用的内存：PostgreSQL 每个连接约 10MB（per-connection process），MySQL 每个连接约 1-8MB
• 操作系统开销：文件描述符、线程/进程调度

连接池（Connection Pool）通过复用已建立的连接来摊薄连接建立的成本，同时限制并发连接数来保护数据库。

HikariCP 连接池大小公式

HikariCP 是 Java 生态最流行的数据库连接池，其作者给出了一个经典的连接池大小公式：

Pool Size = T_n × (C_m - 1) + 1

其中： - T_n = 最大同时执行的线程数 - C_m = 单个任务中最大同时持有的连接数

对于大多数 Web 应用，每个请求只使用一个连接，简化为：

Pool Size ≈ CPU 核心数 × 2 + 有效磁盘数

这个公式的核心洞察是：连接池不是越大越好。 过多的连接会导致：

1. CPU 上下文切换开销增大：操作系统需要调度更多线程
2. 锁竞争加剧：更多并发事务意味着更多锁等待
3. 缓存效率下降：每个连接使用独立的工作内存，总内存需求增加

Little 定律与连接池

利特尔定律（Little’s Law）是排队论的基本定律，完美适用于连接池分析：

L = λ × W

L = 系统中的平均请求数（即需要的连接数）
λ = 请求到达率（QPS）
W = 每个请求的平均处理时间（秒）

案例计算：

QPS = 2000
平均查询耗时 = 5ms = 0.005s

L = 2000 × 0.005 = 10

理论上只需要 10 个连接就能处理 2000 QPS

加上安全余量（处理突发流量和慢查询）：

推荐连接池大小 = L × 1.5 ~ 2.0 = 15 ~ 20

连接池配置示例

Java（HikariCP）：

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("app");
config.setPassword("***");
config.setMaximumPoolSize(20);          // 最大连接数
config.setMinimumIdle(5);              // 最小空闲连接数
config.setConnectionTimeout(3000);     // 获取连接超时（ms）
config.setIdleTimeout(600000);         // 空闲连接存活时间（ms）
config.setMaxLifetime(1800000);        // 连接最大存活时间（ms）

HikariDataSource dataSource = new HikariDataSource(config);

Go（database/sql + pgx）：

package main

import (
    "context"
    "database/sql"
    "fmt"
    "time"

    _ "github.com/jackc/pgx/v5/stdlib"
)

func initDB() (*sql.DB, error) {
    dsn := "postgres://app:***@localhost:5432/mydb?sslmode=disable"
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        return nil, fmt.Errorf("open db: %w", err)
    }

    // 连接池配置
    db.SetMaxOpenConns(20)               // 最大打开连接数
    db.SetMaxIdleConns(5)                // 最大空闲连接数
    db.SetConnMaxLifetime(30 * time.Minute) // 连接最大存活时间
    db.SetConnMaxIdleTime(10 * time.Minute) // 空闲连接最大存活时间

    // 验证连接
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        return nil, fmt.Errorf("ping db: %w", err)
    }

    return db, nil
}

连接池与 max_connections 的关系

                ┌──────────────────────────────────┐
                │       数据库服务器                 │
                │   max_connections = 200           │
                │                                  │
  ┌─────────┐   │   ┌─────────────────────┐        │
  │ App-1   │───│──▶│ 连接池-1 (size=20)   │        │
  │ (10实例) │   │   │ 实际使用: 10×20=200  │        │
  └─────────┘   │   └─────────────────────┘        │
                │                                  │
  ┌─────────┐   │   ┌─────────────────────┐        │
  │ App-2   │───│──▶│ 连接池-2 (size=15)   │        │
  │ (5实例)  │   │   │ 实际使用: 5×15=75    │        │
  └─────────┘   │   └─────────────────────┘        │
                │                                  │
                │   总连接需求: 200 + 75 = 275      │
                │   超过 max_connections! 需要调整   │
                └──────────────────────────────────┘

关键公式：

max_connections ≥ Σ(每个应用实例数 × 该应用连接池大小) + 管理连接预留

管理连接预留通常为 5-10 个（用于监控、DBA 操作等）

当应用实例数动态伸缩时，考虑使用连接池中间件：

• PgBouncer：PostgreSQL 的轻量级连接池代理
• ProxySQL：MySQL 的高性能代理

连接池大小权衡表

八、预编译语句与计划缓存

Prepared Statement 的三重收益

预编译语句（Prepared Statement）不只是防 SQL 注入的工具，它在性能上有三重收益：

1. 避免重复解析：SQL 文本只解析一次，后续执行直接使用解析好的语法树
2. 计划缓存：执行计划只生成一次（或按需更新），避免重复优化
3. 二进制协议传输：参数以二进制格式传输，减少序列化/反序列化开销

-- PostgreSQL 的 Prepared Statement
PREPARE get_order(bigint) AS
    SELECT id, user_id, status, amount, created_at
    FROM orders
    WHERE id = $1;

EXECUTE get_order(12345);
EXECUTE get_order(67890);

-- 查看已缓存的执行计划
SELECT name, statement, prepare_time, generic_plans, custom_plans
FROM pg_prepared_statements;

计划缓存的陷阱

PostgreSQL 对 Prepared Statement 使用以下策略：

前 5 次执行：custom plan（使用实际参数值生成计划）
第 6 次起：比较 custom plan 和 generic plan 的成本
  - 如果 generic plan 成本 ≤ 平均 custom plan 成本 × 1.1
    → 使用 generic plan
  - 否则 → 继续使用 custom plan

这个策略在大多数场景下工作良好，但当参数值的数据分布差异很大时，generic plan 可能对某些参数值严重不适用（如前文的 status 例子）。

Java 中的 Prepared Statement 最佳实践

// 推荐：使用 PreparedStatement
public List<Order> findByUserId(Connection conn, long userId) throws SQLException {
    String sql = "SELECT id, amount, status, created_at " +
                 "FROM orders WHERE user_id = ? AND status = ? " +
                 "ORDER BY created_at DESC LIMIT ?";

    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setLong(1, userId);
        pstmt.setString(2, "paid");
        pstmt.setInt(3, 20);

        try (ResultSet rs = pstmt.executeQuery()) {
            List<Order> orders = new ArrayList<>();
            while (rs.next()) {
                orders.add(mapRow(rs));
            }
            return orders;
        }
    }
}

// 避免：字符串拼接（SQL 注入风险 + 无法复用计划）
public List<Order> findByUserIdBad(Connection conn, long userId) throws SQLException {
    String sql = "SELECT * FROM orders WHERE user_id = " + userId; // 危险！
    // ...
}

Go 中的 Prepared Statement

func findOrdersByUserID(ctx context.Context, db *sql.DB, userID int64) ([]Order, error) {
    query := `SELECT id, amount, status, created_at
              FROM orders
              WHERE user_id = $1 AND status = $2
              ORDER BY created_at DESC
              LIMIT $3`

    rows, err := db.QueryContext(ctx, query, userID, "paid", 20)
    if err != nil {
        return nil, fmt.Errorf("query orders: %w", err)
    }
    defer rows.Close()

    var orders []Order
    for rows.Next() {
        var o Order
        if err := rows.Scan(&o.ID, &o.Amount, &o.Status, &o.CreatedAt); err != nil {
            return nil, fmt.Errorf("scan order: %w", err)
        }
        orders = append(orders, o)
    }
    return orders, rows.Err()
}

Go 的 database/sql 包在内部自动管理 Prepared Statement 的缓存。调用 db.QueryContext 时，驱动程序会根据连接和 SQL 文本复用已有的 Prepared Statement。

九、N+1 查询问题与批量操作

N+1 问题的本质

N+1 查询是 ORM 场景下最常见的性能反模式。其本质是将一次批量操作拆解为 N 次单独操作，每次操作都承担完整的网络往返和查询解析开销。

1 次查询获取 100 个订单
+ 100 次查询分别获取每个订单的用户信息
= 101 次数据库往返（N+1）

网络往返成本：101 × 0.5ms = 50.5ms
vs 2 次查询：2 × 0.5ms = 1ms

解决方案一：JOIN 查询

-- N+1 方式
SELECT * FROM orders WHERE created_at > '2026-01-01';  -- 返回 100 行
SELECT * FROM users WHERE id = 1001;  -- 第 1 次
SELECT * FROM users WHERE id = 1002;  -- 第 2 次
-- ... 重复 100 次

-- JOIN 方式：1 次查询
SELECT o.id, o.amount, o.status, o.created_at, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-01-01';

解决方案二：IN 批量查询

-- 先获取订单
SELECT * FROM orders WHERE created_at > '2026-01-01';

-- 收集所有 user_id，一次性查询
SELECT * FROM users WHERE id IN (1001, 1002, 1003, ..., 1100);

Go 实现：

func findOrdersWithUsers(ctx context.Context, db *sql.DB, since time.Time) ([]OrderWithUser, error) {
    // 第一步：查询订单
    orderQuery := `SELECT id, user_id, amount, status, created_at
                   FROM orders WHERE created_at > $1`
    rows, err := db.QueryContext(ctx, orderQuery, since)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var orders []Order
    userIDs := make(map[int64]struct{})
    for rows.Next() {
        var o Order
        if err := rows.Scan(&o.ID, &o.UserID, &o.Amount, &o.Status, &o.CreatedAt); err != nil {
            return nil, err
        }
        orders = append(orders, o)
        userIDs[o.UserID] = struct{}{}
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }

    // 第二步：收集所有 user_id，批量查询用户
    ids := make([]int64, 0, len(userIDs))
    for id := range userIDs {
        ids = append(ids, id)
    }
    userMap, err := findUsersByIDs(ctx, db, ids)
    if err != nil {
        return nil, err
    }

    // 第三步：组装结果
    results := make([]OrderWithUser, 0, len(orders))
    for _, o := range orders {
        results = append(results, OrderWithUser{Order: o, User: userMap[o.UserID]})
    }
    return results, nil
}

解决方案三：批量插入与更新

单条插入 vs 批量插入的性能差距巨大：

-- 单条插入：1000 次网络往返 + 1000 次事务提交
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 101, 2);
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 102, 1);
-- ... 重复 1000 次

-- 批量插入：1 次网络往返（MySQL）
INSERT INTO order_items (order_id, product_id, qty) VALUES
    (1, 101, 2),
    (1, 102, 1),
    (1, 103, 3),
    -- ... 
    (1, 1100, 1);

-- PostgreSQL COPY 命令：最高效的批量导入
COPY order_items (order_id, product_id, qty)
FROM STDIN WITH (FORMAT csv);

Java 批量插入（JDBC Batch）：

public void batchInsertItems(Connection conn, List<OrderItem> items) throws SQLException {
    String sql = "INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)";

    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        conn.setAutoCommit(false);
        int count = 0;

        for (OrderItem item : items) {
            pstmt.setLong(1, item.getOrderId());
            pstmt.setLong(2, item.getProductId());
            pstmt.setInt(3, item.getQuantity());
            pstmt.addBatch();

            if (++count % 1000 == 0) {
                pstmt.executeBatch();
            }
        }
        pstmt.executeBatch();
        conn.commit();
    }
}

批量操作的注意事项

十、工程案例：电商订单系统的数据库性能优化

背景

某中型电商平台，日均订单量 50 万，订单表 orders 累计 8000 万行。系统在一次促销活动中出现了严重的性能问题：

• 订单列表接口 P99 延迟从 45ms 上升到 4200ms
• 数据库 CPU 使用率持续 95% 以上
• 应用日志出现大量 Connection pool exhausted 错误

第一步：慢查询定位

-- PostgreSQL 启用 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查找最耗时的查询
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

发现排名第一的慢查询：

SELECT o.*, u.name, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.merchant_id = $1
  AND o.status = $2
  AND o.created_at BETWEEN $3 AND $4
ORDER BY o.created_at DESC
LIMIT 20 OFFSET $5;

这条查询的问题：

1. merchant_id + status + created_at 没有合适的复合索引
2. 使用 OFFSET 进行分页，深翻页时性能急剧下降
3. SELECT * 导致无法使用覆盖索引

第二步：索引优化

-- 创建复合索引，列顺序按查询模式设计
-- merchant_id: 等值查询（高选择性）
-- status: 等值查询（低选择性，但与 merchant_id 组合后选择性提升）
-- created_at: 范围查询 + 排序
CREATE INDEX CONCURRENTLY idx_orders_merchant_status_created
ON orders(merchant_id, status, created_at DESC);

-- 验证索引效果
EXPLAIN ANALYZE
SELECT o.id, o.amount, o.status, o.created_at, u.name, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.merchant_id = 10086
  AND o.status = 'paid'
  AND o.created_at BETWEEN '2026-04-01' AND '2026-04-13'
ORDER BY o.created_at DESC
LIMIT 20;

优化前后对比：

优化前：Seq Scan on orders → 全表扫描 8000 万行，耗时 3800ms
优化后：Index Scan using idx_orders_merchant_status_created → 扫描 42 行，耗时 0.8ms

第三步：分页优化

将 OFFSET 分页改为游标分页（Cursor-based Pagination）：

-- 传统 OFFSET 分页（第 100 页）
SELECT * FROM orders
WHERE merchant_id = 10086 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20 OFFSET 1980;
-- 需要扫描 2000 行，丢弃前 1980 行

-- 游标分页：使用上一页最后一条记录的 created_at 作为游标
SELECT * FROM orders
WHERE merchant_id = 10086
  AND status = 'paid'
  AND created_at < '2026-04-10 15:30:00'  -- 上一页最后一条的 created_at
ORDER BY created_at DESC
LIMIT 20;
-- 直接从索引定位，只扫描 20 行

第四步：连接池调优

应用实例数：20 个，每个实例连接池 maxPoolSize = 50。总连接需求：20 × 50 = 1000，远超数据库 max_connections = 200。

根据 Little 定律重新计算：

QPS = 3000（峰值）
平均查询耗时 = 2ms（索引优化后）
L = 3000 × 0.002 = 6（理论需要 6 个连接）

加上安全余量：每实例 maxPoolSize = 10
总连接数 = 20 × 10 = 200 = max_connections

预留管理连接：将 max_connections 调整为 210

优化效果

十一、数据库性能监控体系

建立持续的监控体系比一次性优化更重要。

查询层与索引监控

-- PostgreSQL：慢查询日志配置
ALTER SYSTEM SET log_min_duration_statement = 100;  -- 记录超过 100ms 的查询
SELECT pg_reload_conf();

-- PostgreSQL：检测未使用的索引
SELECT
    schemaname || '.' || relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
      SELECT conindid FROM pg_constraint WHERE contype = 'p'
  )
ORDER BY pg_relation_size(indexrelid) DESC;

-- PostgreSQL：检测缺失索引（高频顺序扫描）
SELECT
    schemaname || '.' || relname AS table_name,
    seq_scan,
    idx_scan,
    CASE WHEN seq_scan > 0
        THEN round(seq_tup_read::numeric / seq_scan, 0)
        ELSE 0
    END AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND seq_tup_read / greatest(seq_scan, 1) > 10000
ORDER BY seq_tup_read DESC
LIMIT 20;

监控告警阈值

十二、总结

数据库性能优化不是零散的技巧集合，而是一个涉及存储引擎、优化器、连接管理三个层次的系统工程。

存储层：理解 B+树的 I/O 成本模型是索引设计的基础。扇出率决定树高，树高决定查找的 I/O 次数。覆盖索引通过消除回表将随机 I/O 降到最低。复合索引的列顺序需要根据实际查询模式精心设计。

优化器层：基于成本的优化器依赖统计信息做决策。统计信息过期、数据倾斜、列相关性都可能导致优化器选错执行计划。通过 EXPLAIN ANALYZE 比较估算行数与实际行数是诊断问题的第一步。

连接管理层：连接池不是越大越好。Little 定律为连接池大小提供了理论依据。应用实例数 × 连接池大小必须小于数据库的 max_connections。预编译语句在安全性和性能上都应该是默认选择。

应用层：N+1 查询是最容易被忽视也最容易修复的性能问题。批量操作在网络往返、事务开销和索引维护三个维度都优于逐行操作。

这些层次之间相互影响。索引设计影响优化器的计划选择，优化器的错误决策导致慢查询，慢查询耗尽连接池，连接池耗尽导致整个服务不可用。性能优化的目标不是让每个查询都跑到极致，而是建立一个可观测、可预测、可持续运行的系统。

下一篇将讨论负载测试——在生产环境出问题之前，如何系统性地发现性能瓶颈。

导航

上一篇：零拷贝

下一篇：负载测试

参考资料

书籍

• Markus Winand, SQL Performance Explained, 2012. 从索引结构到执行计划的系统性讲解，附带可视化网站 use-the-index-luke.com。
• Baron Schwartz et al., High Performance MySQL, 4th Edition, O’Reilly, 2021. MySQL 性能优化的权威参考。
• Gregory Smith, PostgreSQL 14 Internals, Postgres Professional, 2023. 深入 PostgreSQL 内核实现，包括查询优化器和缓存管理。

论文与技术报告

• Goetz Graefe, “Modern B-Tree Techniques”, Foundations and Trends in Databases, 3(4):203-402, 2011. B 树家族的全面综述，涵盖并发控制、日志恢复和现代变体。
• Viktor Leis et al., “How Good Are Query Optimizers, Really?”, PVLDB, 9(3):204-215, 2015. 对主流数据库优化器基数估算准确性的系统性评测。

官方文档

• PostgreSQL Documentation, “Chapter 14. Performance Tips”, postgresql.org/docs/current/performance-tips.html. PostgreSQL 性能调优官方指南，包括 EXPLAIN 用法和统计信息管理。
• MySQL Documentation, “Chapter 8. Optimization”, dev.mysql.com/doc/refman/8.0/en/optimization.html. MySQL 优化参考，覆盖索引、查询优化器和服务器配置。
• HikariCP Wiki, “About Pool Sizing”, github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing. HikariCP 连接池大小计算的原始文档，包含 PostgreSQL 基准测试数据。

博客与在线资源

• Markus Winand, “Use The Index, Luke”, use-the-index-luke.com. 交互式 B+树可视化与索引教程。
• Brandur Leach, “Connections Are Not Free”, brandur.org/connections. PostgreSQL 连接的真实成本分析。

工具

• pgMustard: https://www.pgmustard.com — PostgreSQL EXPLAIN 计划可视化分析工具。
• pg_stat_statements: PostgreSQL 扩展 — 收集查询执行统计信息，定位慢查询的首选工具。
• HikariCP: https://github.com/brettwooldridge/HikariCP — Java 生态最快的数据库连接池。
• PgBouncer: https://www.pgbouncer.org — PostgreSQL 连接池中间件，支持事务级和语句级连接复用。
• pt-query-digest: https://docs.percona.com/percona-toolkit/pt-query-digest.html — Percona Toolkit 中的慢查询分析工具。