实战项目：Spring Boot + MyBatis + PostgreSQL 企业级日志分析平台

概述

通过本系列前面 15 篇文章的深入剖析，我们完成了一场从 PostgreSQL 内核原理到工程实践的深度探索之旅。从 MVCC 实现的元组版本链，到查询优化器的成本估算模型；从 JSONB 的二进制存储结构，到全文搜索的倒排索引机制；从分区表的裁剪逻辑，到逻辑复制的 WAL 解码原理——我们已经系统性地构建了坚实的理论基础。

然而，一个成熟的 Java 专家不仅要懂数据库原理，更要懂得如何在代码中优雅地、高性能地运用它们。知识若不能转化为工程能力，便只是纸上谈兵。

本文作为系列的收官之战，将构建一个 Spring Boot + MyBatis + PostgreSQL 的企业级日志分析平台。这并非一个简单的 CRUD 演示，而是一次将 DBA 视角 与 Java 开发视角 完美融合的实战之旅。我们将直面海量日志带来的存储、检索与分析挑战，以本项目为主线，串联起前文所学的核心知识点：

• 为什么使用 JSONB 存储动态日志属性，而不是 TEXT 或固定的宽表？其底层的访问效率差异何在？
• 如何为 MyBatis 开发一个生产级的 JSONB TypeHandler，让开发者像操作普通 Java 对象一样操作 JSON 列？
• 在 TB 级时序数据下，BRIN 索引为何能以 1/1000 的空间成本，实现媲美 B-Tree 索引的范围查询性能？
• 面对一个 P99 延迟超过 2 秒的组合查询，如何利用 EXPLAIN (ANALYZE, BUFFERS) 和 pg_stat_statements 构建完整的诊断闭环，最终将其优化至 100ms 以内？
• 全文搜索在中文环境下如何正确配置分词器？如何通过 tsvector 和 GIN 索引实现毫秒级日志内容检索？

我们将严格按照“项目需求与 Schema 设计 → MyBatis 数据访问层开发 → 高级特性整合 → 性能诊断与调优 → 全链路压测 → 面试高频专题”的逻辑递进，确保每一步都掷地有声，每一行代码都有据可依。

文章组织架构图

flowchart TD
    subgraph A["1. 项目需求与架构设计"]
        direction LR
        A1["1.1 业务背景与核心挑战"]
        A2["1.2 核心技术选型论证"]
        A3["1.3 系统架构总览图"]
        A4["1.4 Maven多模块工程结构"]
        A5["1.5 Spring Boot核心配置详解"]
        A1 --> A2 --> A3 --> A4 --> A5
    end

    subgraph B["2. 数据库Schema设计与高级类型实践"]
        direction LR
        B1["2.1 日志主表结构与分区策略"]
        B2["2.2 JSONB属性列：动态属性的完美归宿"]
        B3["2.3 INET类型：IP地址的高效存储与查询"]
        B4["2.4 TIMESTAMPTZ：时区处理最佳实践"]
        B5["2.5 全文搜索列：tsvector生成列设计"]
        B6["2.6 INTEGER[]标签列与GIN索引（可选）"]
        B1 --> B2 & B3 & B4 & B5 & B6
    end

    subgraph C["3. MyBatis数据访问层深度整合开发"]
        direction LR
        C1["3.1 自定义JSONB TypeHandler原理与实现"]
        C2["3.2 自定义INET TypeHandler实现"]
        C3["3.3 自定义TSQuery TypeHandler实现"]
        C4["3.4 核心Entity与DTO设计"]
        C5["3.5 动态SQL：灵活的多条件组合查询"]
        C6["3.6 批量写入三种方式性能对比"]
        C1 & C2 & C3 --> C4 & C5 & C6
    end

    subgraph D["4. 索引策略与性能预期分析"]
        direction LR
        D1["4.1 BRIN索引：时序数据的空间效率之王"]
        D2["4.2 GIN索引（jsonb_path_ops）：JSONB查询加速"]
        D3["4.3 GIN索引（tsvector）：全文搜索加速"]
        D4["4.4 通过EXPLAIN验证索引策略"]
        D1 & D2 & D3 --> D4
    end

    subgraph E["5. 模拟生产慢查询与性能诊断闭环"]
        direction LR
        E1["5.1 场景一：JSONB操作符误用导致索引失效"]
        E2["5.2 场景二：统计信息过时致错误执行计划"]
        E3["5.3 场景三：work_mem不足引发磁盘排序"]
        E4["5.4 DBA诊断工具箱详解"]
        E1 & E2 & E3 --> E4
    end

    subgraph F["6. 全链路压力测试与监控体系"]
        direction LR
        F1["6.1 测试环境与数据准备"]
        F2["6.2 JMeter压测方案设计与脚本详解"]
        F3["6.3 压测实录：写入瓶颈发现与突破"]
        F4["6.4 压测实录：查询抖动定位与根除"]
        F5["6.5 性能测试报告与容量规划"]
        F1 & F2 --> F3 & F4 --> F5
    end

    subgraph G["7. 面试高频专题"]
        G1["7.1 10道核心面试题完整解析"]
    end

    A5 --> B1
    B6 --> C1 & C2 & C3
    C6 --> D1 & D2 & D3
    D4 --> E1 & E2 & E3
    E4 --> F1 & F2
    F5 --> G1

    classDef default fill:#f8f9fa,stroke:#333,stroke-width:1px,color:#333;
    classDef chapter fill:#e3f2fd,stroke:#1e88e5,stroke-width:2px,color:#0d47a1,rx:8;
    class A,B,C,D,E,F,G chapter;

架构图说明：

• 总览说明：全文 7 大模块，共 30 个子主题，形成一个严密的工程闭环。从项目架构（模块1）到 Schema 设计（模块2），再到 MyBatis 数据访问层开发（模块3），接着是索引策略（模块4）和性能诊断（模块5），然后通过全链路压测验证（模块6），最终以完整面试专题收尾（模块7）。每个子主题都有明确的输入和输出，整体逻辑不可跳跃。

• 逐模块说明：

  • 模块 1 是总纲，确立了项目的技术选型、工程结构和配置基线，是后续所有开发的前提。特别增加了 Maven 项目结构和核心配置文件详解，方便读者直接落地。
  • 模块 2 是数据架构的根基，深入探讨了 JSONB、INET、TIMESTAMPTZ、tsvector 等高级类型在实际业务中的选型理由和最佳实践，是本系列第 4 篇《数据类型深度》的实战延伸。
  • 模块 3 是桥梁，通过自定义 JSONB TypeHandler、INET TypeHandler、TSQuery TypeHandler 三个生产级类型处理器，将 PostgreSQL 的底层能力优雅地暴露给 Java 业务代码。此模块还详细对比了三种批量写入方案的性能差异。
  • 模块 4 是核心索引策略的理论与实践验证，详细解释了 BRIN、GIN 索引的适用场景和参数调优，并通过 EXPLAIN 实际验证。
  • 模块 5 是本文最独特的价值所在——三个精心设计的“慢查询场景”构成完整的性能诊断闭环。每个场景都有问题复现、根因分析、解决方案和效果验证。
  • 模块 6 是全链路压测的完整实录，包含 JMeter 脚本设计、压测过程中发现的两个真实瓶颈（WAL 写入争用、JOIN 顺序错误）以及对应的解决过程。
  • 模块 7 是 10 道完整的面试题解析，每题都包含标准回答、三组追问和加分回答，将工程实践升华为可复述的方法论。

• 关键结论：PostgreSQL 的强大之处，不仅在于其丰富的高级特性，更在于其无与伦比的可扩展性与诊断透明度。通过 Spring Boot 和 MyBatis 的深度整合，Java 开发者可以在享受现代开发框架带来的便利性的同时，充分利用这些底层能力，构建出真正高性能、可扩展的企业级应用。这也是本系列从原理到实战的最终价值体现。

---

1. 项目需求与架构设计

1.1 业务背景与核心挑战

某大型互联网公司拥有 500+ 微服务实例，日均产生约 2TB 的日志数据。这些日志涵盖：

• 业务日志：订单状态流转、支付回调、用户行为埋点
• 系统日志：JVM GC 事件、线程池状态、连接池指标
• 安全日志：认证鉴权、IP 黑白名单命中、SQL 注入拦截

现存痛点：

1. 存储成本高昂：基于 ELK（Elasticsearch + Logstash + Kibana）的方案，在 TB 级数据量下，ES 集群需要大量内存和 SSD 存储，成本呈指数级增长。
2. 结构化查询能力弱：Logstash 的 grok 解析灵活性有限，当微服务不断新增自定义属性时（如 experimentId、abTestGroup），ES 的 Mapping 更新成为运维灾难。
3. 复杂分析受限：ES 的 DSL 不易表达 JOIN、子查询、窗口函数等复杂分析逻辑，分析师和开发人员需要学习两套查询语言。
4. 诊断工具链割裂：开发者在 IDE 中写 Java 代码，却要去 Kibana 看日志，再去 Grafana 看数据库指标，工具链断裂导致问题定位效率低下。

核心功能需求：

功能类别	具体需求	技术挑战
高性能写入	峰值 80,000 events/s	如何减少网络往返、减少 WAL 刷盘频率
灵活组合查询	时间 + 级别 + 服务名 + IP段 + 自定义属性	多索引的位图合并策略
动态属性检索	按任意 key:value 查询，如 userId:9527	JSONB 的 GIN 索引与操作符选择
全文搜索	对日志消息体进行关键词搜索，支持中文	tsvector 生成列 + 中文分词配置
数据生命周期	自动归档 6 个月以前的数据	声明式分区 + 定时 DETACH/DROP
多维度聚合	按服务、时间段统计错误趋势	窗口函数 + CTE

1.2 核心技术选型论证

1.2.1 数据库：为什么选择 PostgreSQL 16？

在选型调研中，我们对比了四个候选方案：

维度	PostgreSQL 16	Elasticsearch	ClickHouse	MongoDB
JSON 查询能力	⭐⭐⭐⭐⭐ JSONB + GIN + SQL/JSON	⭐⭐⭐⭐ 嵌套对象查询	⭐⭐⭐ JSON 函数较弱	⭐⭐⭐⭐ 原生 BSON
全文搜索	⭐⭐⭐⭐ tsvector + GIN	⭐⭐⭐⭐⭐ 倒排索引原生	⭐⭐⭐ 有限	⭐⭐⭐⭐⭐ 原生支持
SQL 分析能力	⭐⭐⭐⭐⭐ 完整 SQL 标准	⭐⭐ DSL 受限	⭐⭐⭐⭐ 类 SQL	⭐⭐ 聚合管道
存储成本	⭐⭐⭐⭐ 高压缩	⭐⭐ 膨胀率高	⭐⭐⭐⭐⭐ 极高压缩	⭐⭐⭐ 中等
运维复杂度	⭐⭐⭐⭐ 单一依赖	⭐⭐ 组件多	⭐⭐⭐ 单一依赖	⭐⭐⭐⭐ 单一依赖
诊断工具链	⭐⭐⭐⭐⭐ 极丰富	⭐⭐⭐ Profile API	⭐⭐⭐ system.query_log	⭐⭐⭐ explain()

最终选择 PostgreSQL 16 的核心理由：

1. 统一技术栈：将日志平台和业务数据库使用同一套 PostgreSQL，减少公司运维的技术异构性。开发人员无需学习新的查询语言。
2. JSONB 的工程优势：不同于 MongoDB 将文档作为一等公民而牺牲了 SQL 能力，也不同于 MySQL 的 JSON 类型（性能远不如 PG），PG 的 JSONB 是关系型模型的一个强大补充，而非替代。
3. 诊断透明度：pg_stat_statements、auto_explain、pg_stat_activity、pg_statio_* 等视图提供了从 SQL 执行到缓冲命中的全套可观测性数据，这与我们秉持的“可观测性驱动开发”理念高度契合。
4. 分区表原生支持：PG 10+ 的声明式分区，配合 pg_partman 扩展，可以实现自动化的数据生命周期管理。

1.2.2 ORM 框架：为什么选择 MyBatis 3.5 而非 JPA/Hibernate？

维度	MyBatis 3.5	Spring Data JPA / Hibernate
SQL 控制力	⭐⭐⭐⭐⭐ 手写 SQL，完全控制	⭐⭐⭐ JPQL/HQL 受限，原生 SQL 繁琐
PG 高级特性支持	⭐⭐⭐⭐⭐ 直接写 @> @@ ::jsonb 等	⭐⭐ 需自定义 Dialect 和 Function
动态查询	⭐⭐⭐⭐⭐ <if> <foreach> <choose>	⭐⭐⭐ Specification / Criteria API 冗长
TypeHandler 扩展	⭐⭐⭐⭐⭐ 简单直接	⭐⭐⭐⭐ UserType 较复杂
批量操作	⭐⭐⭐⭐ BatchExecutor + SQL 批量值	⭐⭐⭐ 批量操作需额外配置

最终选择 MyBatis 3.5 的核心理由：在这个项目中，我们不是在“被 ORM 使用”，而是在“使用数据库”。日志分析平台的核心价值就体现在那些复杂、动态、依赖 PostgreSQL 专有操作符的 SQL 上。MyBatis 让我们能直接书写这些 SQL，同时通过自定义 TypeHandler 保持 Java 侧的对象抽象。

1.2.3 其他技术选型

• 后端框架：Spring Boot 3.2.x（JDK 17 baseline），利用其自动配置、Actuator 健康检查、Micrometer 指标采集。
• JSON 序列化：Jackson 2.15.x，作为 Spring Boot 默认 JSON 库，与 JSONB TypeHandler 共用同一个 ObjectMapper 实例。
• 连接池：HikariCP（Spring Boot 默认），高性能、低延迟的 JDBC 连接池。
• 压力测试：Apache JMeter 5.6，开源、成熟、可编程的压测工具。
• 构建工具：Maven 3.9，多模块管理。

1.3 系统架构总览图

flowchart TB
    subgraph Client["<b>客户端层</b>"]
        LogProducer["<b>微服务集群</b><br/>（日志生产者）"]
        Analyst["<b>数据分析师</b><br/>（Kibana-like UI）"]
    end

    subgraph LB["<b>负载均衡层</b>"]
        Nginx["<b>Nginx / ALB</b>"]
    end

    subgraph App["<b>应用层 (Spring Boot 3.2)</b>"]
        direction TB
        WriteAPI["<b>LogWriteController</b><br/>POST /api/logs/batch"]
        QueryAPI["<b>LogQueryController</b><br/>POST /api/logs/search"]
        HealthAPI["<b>Actuator Health Check</b>"]
    end

    subgraph Service["<b>服务层</b>"]
        direction TB
        LogWriteService["<b>LogWriteService</b><br/>批量写入 + 事务管理"]
        LogQueryService["<b>LogQueryService</b><br/>查询构建 + 结果缓存"]
        PartitionMgr["<b>PartitionManager</b><br/>定时创建/归档分区"]
    end

    subgraph DAO["<b>数据访问层 (MyBatis 3.5)</b>"]
        direction TB
        LogMapper["<b>LogMapper.java</b><br/>接口定义"]
        DynamicSQL["<b>LogMapper.xml</b><br/>动态SQL实现"]
        TypeHandlers["<b>TypeHandler注册中心</b><br/>JsonbTypeHandler<br/>InetTypeHandler<br/>TSQueryTypeHandler"]
    end

    subgraph DB["<b>PostgreSQL 16 数据库层</b>"]
        direction TB
        MainTable[("<b>log_entries</b><br/>（声明式分区表）")]
        Partitions["<b>分区们</b><br/>log_entries_2023_10<br/>log_entries_2023_11<br/>...<br/>log_entries_default"]
        Indexes["<b>索引层</b><br/>BRIN: log_time<br/>GIN: attributes (jsonb_path_ops)<br/>GIN: message_tsv"]
        Extensions["<b>扩展层</b><br/>pg_stat_statements<br/>auto_explain<br/>pg_partman<br/>zhparser（中文分词）"]
    end

    subgraph Monitor["<b>监控层</b>"]
        direction LR
        PgMetrics["<b>PG 内部视图</b><br/>pg_stat_activity<br/>pg_stat_statements<br/>pg_statio_user_tables"]
        OS["<b>OS 指标</b><br/>CPU / Memory / Disk IO"]
        Grafana["<b>Grafana 大屏</b>"]
    end

    LogProducer --> Nginx --> WriteAPI
    Analyst --> Nginx --> QueryAPI
    WriteAPI --> LogWriteService --> LogMapper --> DynamicSQL --> TypeHandlers --> DB
    QueryAPI --> LogQueryService --> LogMapper
    PartitionMgr --> DB
    DB --> PgMetrics --> Grafana
    DB --> Extensions
    OS --> Grafana
    HealthAPI --> Grafana

    classDef client fill:#C0CCD6,stroke:#7F8E9E,stroke-width:2px,color:#333;
    classDef lb fill:#B9C8B0,stroke:#7A8A74,stroke-width:2px,color:#333;
    classDef app fill:#D1BEBE,stroke:#9C8787,stroke-width:2px,color:#333;
    classDef service fill:#BFB6C4,stroke:#8D8193,stroke-width:2px,color:#333;
    classDef dao fill:#D0CAB7,stroke:#A19B85,stroke-width:2px,color:#333;
    classDef db fill:#D4C8BC,stroke:#A7998D,stroke-width:2px,color:#333;
    classDef monitor fill:#D5D3D0,stroke:#AAA8A5,stroke-width:2px,color:#333;

    class Client client;
    class LB lb;
    class App app;
    class Service service;
    class DAO dao;
    class DB db;
    class Monitor monitor;

架构图说明：

• 总览：这是一个典型的三层架构 + 数据库层的设计，增加了专门的监控层。日志通过 Nginx 负载均衡到达应用层，经过服务层和数据访问层，最终通过自定义 TypeHandler 将 Java 对象映射到 PostgreSQL 的 JSONB/INET 等高级类型。

• 分层说明：

  • 客户端层：分为日志生产者（微服务集群）和分析师（Web UI），前者以极高的 QPS 写入，后者以较低的 QPS 进行复杂查询。
  • 应用层：WriteAPI 专为高吞吐优化，使用异步响应；QueryAPI 专为低延迟优化，使用同步响应。
  • 服务层：LogWriteService 封装批量写入逻辑和事务管理；LogQueryService 处理查询条件校验和结果转换；PartitionManager 是一个定时任务，负责自动创建未来分区和归档旧分区。
  • 数据访问层：核心是 LogMapper.xml 中的动态 SQL，配合三个自定义 TypeHandler，实现对 PG 高级特性的透明调用。
  • 数据库层：声明式分区表是数据生命周期管理的核心；索引层展示了我们面向不同查询场景选择的索引类型。
  • 监控层：打通了 PG 内部视图和 OS 指标，统一展示在 Grafana 上。

• 关键决策：

  • 没有引入 Kafka：这是为了聚焦数据库本身的能力。在真正的超大规模场景下，应在 Nginx 和 WriteAPI 之间加一层 Kafka 进行削峰填谷，然后由独立的消费者批量写入 PG。此方案作为进阶习题留给读者。
  • 读写分离：WriteAPI 和 QueryAPI 虽然是同一个应用，但在生产中可以部署在不同实例上，通过 PG 的流复制只读节点进行读写分离。

1.4 Maven 多模块工程结构

一个清晰的项目结构是代码可维护性的基础。本项目的 Maven 结构如下：

log-analysis-platform/
├── pom.xml                              # 父 POM，管理依赖版本和模块
├── logplatform-common/                  # 公共模块：Entity、DTO、工具类
│   ├── pom.xml
│   └── src/main/java/com/example/logplatform/common/
│       ├── entity/
│       │   └── LogEntry.java            # 日志主实体
│       ├── dto/
│       │   ├── LogQueryRequest.java     # 查询请求 DTO
│       │   ├── LogBatchRequest.java     # 批量写入请求 DTO
│       │   ├── LogQueryResponse.java    # 查询响应 DTO
│       │   └── ServiceErrorSummary.java # 服务错误摘要 DTO
│       └── enums/
│           └── LogLevel.java            # 日志级别枚举
│
├── logplatform-dao/                     # 数据访问层模块
│   ├── pom.xml
│   └── src/main/
│       ├── java/com/example/logplatform/dao/
│       │   ├── mapper/
│       │   │   └── LogMapper.java       # MyBatis Mapper 接口
│       │   └── handler/
│       │       ├── JsonbTypeHandler.java    # JSONB ↔ Map 处理器
│       │       ├── InetTypeHandler.java     # INET ↔ InetAddress 处理器
│       │       └── TSQueryTypeHandler.java  # TSQuery 参数处理器
│       └── resources/
│           └── mapper/
│               └── LogMapper.xml        # MyBatis SQL 映射文件
│
├── logplatform-service/                 # 服务层模块
│   ├── pom.xml
│   └── src/main/java/com/example/logplatform/service/
│       ├── LogWriteService.java         # 写入服务
│       ├── LogQueryService.java         # 查询服务
│       └── PartitionManager.java        # 分区管理定时任务
│
├── logplatform-web/                     # Web 层模块
│   ├── pom.xml
│   └── src/main/
│       ├── java/com/example/logplatform/web/
│       │   ├── controller/
│       │   │   ├── LogWriteController.java  # 写入接口
│       │   │   └── LogQueryController.java  # 查询接口
│       │   └── LogPlatformApplication.java # Spring Boot 启动类
│       └── resources/
│           └── application.yml          # Spring Boot 核心配置
│
└── logplatform-test/                    # 测试模块
    ├── pom.xml
    └── src/test/
        ├── java/com/example/logplatform/test/
        │   ├── dao/
        │   │   └── LogMapperTest.java   # Mapper 单元测试
        │   ├── service/
        │   │   └── LogWriteServiceTest.java
        │   └── performance/
        │       └── JMeterLogTest.java   # JMeter 测试辅助类
        └── resources/
            └── jmeter/
                ├── log-write-test.jmx   # 写入压测脚本
                └── log-query-test.jmx   # 查询压测脚本

工程结构解读：

1. 模块划分原则：严格遵循“高内聚、低耦合”。common 模块被所有其他模块依赖，但本身不依赖任何业务模块；dao 模块只负责数据访问；service 模块封装业务逻辑；web 模块处理 HTTP 请求响应。
2. TypeHandler 的位置：放在 dao 模块中，因为它们属于数据访问层的技术组件，不应该泄漏到 service 层。
3. 测试模块独立：将性能测试相关代码（JMeter 脚本、测试辅助类）放在独立模块，避免污染生产代码。

1.5 Spring Boot 核心配置详解

application.yml 是整个项目的配置中枢，以下是完整配置及其设计意图：

# 文件名：logplatform-web/src/main/resources/application.yml
server:
  port: 8080
  tomcat:
    threads:
      max: 200        # 最大工作线程数
      min-spare: 20   # 最小空闲线程数
    accept-count: 100 # 等待队列长度
    max-connections: 10000 # 最大连接数

spring:
  application:
    name: log-analysis-platform

  datasource:
    # HikariCP 连接池配置（Spring Boot 3.2 默认）
    hikari:
      pool-name: LogPlatformPool
      # 连接数规划：
      # 写入场景：并发批量写入，每个连接执行一条大的 INSERT，需要较少连接
      # 查询场景：每个查询需要独立连接，需要较多连接
      maximum-pool-size: 50
      minimum-idle: 10
      idle-timeout: 600000        # 10 分钟空闲超时
      max-lifetime: 1800000       # 30 分钟最大连接生命周期
      connection-timeout: 30000   # 30 秒获取连接超时
      leak-detection-threshold: 60000 # 60 秒连接泄漏检测
    url: jdbc:postgresql://localhost:5432/logdb?ApplicationName=log-platform
    username: logapp
    password: ${DB_PASSWORD:changeme}  # 支持环境变量注入
    driver-class-name: org.postgresql.Driver

    # JDBC 连接参数优化
    hikari.data-source-properties:
      # 准备阈值：超过此次数的 PreparedStatement 才使用服务端预编译
      prepareThreshold: 5
      # 将准备好的语句缓存到客户端
      preparedStatementCacheQueries: 256
      preparedStatementCacheSizeMiB: 5
      # 对于批量 INSERT，关闭自动提交以实现手动事务管理
      # （此配置由 MyBatis 的 @Transactional 控制）
      defaultAutoCommit: false

mybatis:
  # 映射文件位置
  mapper-locations: classpath:mapper/*.xml
  # 类型处理器扫描包（MyBatis 3.5 新增）
  type-handlers-package: com.example.logplatform.dao.handler
  configuration:
    # 开启驼峰命名自动映射
    map-underscore-to-camel-case: true
    # 启用懒加载（对于大数据量查询，配合 localCacheScope=STATEMENT 使用）
    lazy-loading-enabled: true
    # 默认语句超时（秒），防止慢查询拖垮连接池
    default-statement-timeout: 30
    # 日志实现
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl

# PostgreSQL 特定配置（通过连接参数传递）
# 在 JDBC URL 中可追加的参数：
# &options=-c%20statement_timeout=30000              # 语句超时 30 秒
# &options=-c%20lock_timeout=10000                   # 锁超时 10 秒
# &options=-c%20application_name=log-platform         # 应用标识

# Actuator 健康检查与指标暴露
management:
  endpoints:
    web:
      exposure:
        include: health,metrics,prometheus
  metrics:
    export:
      prometheus:
        enabled: true

# 日志配置
logging:
  level:
    root: INFO
    com.example.logplatform: DEBUG
    # MyBatis SQL 日志（开发环境下开启，生产环境关闭）
    com.example.logplatform.dao.mapper: TRACE
  pattern:
    console: "%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n"

配置关键点解读：

1. 连接池最大连接数 50：这个数字不是拍脑袋决定的。假设数据库服务器配置为 16 核、64GB RAM，max_connections = 200。我们的应用实例有 4 个，每个分配 50 个连接正好 200。但实际上要预留一些连接给 DBA 管理操作，所以生产环境可能调整为 40。

2. prepareThreshold: 5：这是 PostgreSQL JDBC 的一个关键参数。PG 的扩展查询协议允许应用程序先发送一个 Parse 请求，然后多次执行（Bind/Execute）。但如果 SQL 只执行一次，这个额外的 Round-Trip 就是浪费。prepareThreshold 表示执行超过 N 次后才使用服务端预编译。对于我们的批量写入，将值设为 5 可以在第 6 批开始自动使用服务端预编译，减少解析开销。

3. default-statement-timeout: 30：这是一个“安全气囊”。防止某个查询因数据量过大或锁等待而长时间占用连接，导致连接池耗尽。

4. 密码环境变量注入：生产环境绝对不能将密码硬编码在配置文件中。我们使用 ${DB_PASSWORD:changeme} 语法，支持环境变量 DB_PASSWORD 注入，默认值 changeme 仅用于开发。

---

2. 数据库 Schema 设计与高级类型实践

好的 Schema 设计是高性能系统的基石。本章将深入运用 第 4 篇《数据类型深度》 的知识，设计一个能充分发挥 PostgreSQL 能力的日志主表，并详细解释每个类型选择的理由。

2.1 日志主表结构与分区策略

面对 TB 级数据，单表是不可行的。我们必须使用 声明式分区表，按月进行 RANGE 分区，实现数据生命周期管理和查询裁剪。以下是完整的建表 SQL：

-- 文件名：sql/01_create_schema.sql
-- 描述：创建日志分析平台核心 Schema

-- ============================================================
-- 1. 创建主表（Partitioned Table）
-- ============================================================
CREATE TABLE log_entries (
    -- 自增主键：使用 BIGSERIAL（BIGINT 自增序列）
    -- 注意：在分区表中，主键必须包含分区键
    id              BIGSERIAL,

    -- 日志发生时间：使用 TIMESTAMPTZ（带时区的时间戳）
    -- 选择理由：
    --   1. 内部存储为 UTC，避免时区转换错误
    --   2. 支持客户端时区自动转换显示
    --   3. 作为分区键，实现按月分区
    log_time        TIMESTAMPTZ NOT NULL,

    -- 日志级别：使用 VARCHAR(10) 而非 ENUM
    -- 选择理由：
    --   1. PG 的 ENUM 类型在新增级别时需要 ALTER TYPE，在微服务频繁迭代中不灵活
    --   2. VARCHAR 可以用 CHECK 约束保证值合法
    --   3. 对性能影响极小（在 WHERE 条件中都是一样的索引扫描）
    level           VARCHAR(10) NOT NULL
                    CHECK (level IN ('DEBUG', 'INFO', 'WARN', 'ERROR', 'FATAL')),

    -- 微服务名：使用 VARCHAR(100)
    -- 索引策略：在分区上创建 B-Tree 索引，加速按服务名过滤
    service_name    VARCHAR(100) NOT NULL,

    -- 客户端 IP 地址：使用 INET 类型
    -- 详见第 2.3 节
    client_ip       INET,

    -- 日志消息原文：使用 TEXT（无长度限制）
    -- 用于全文搜索的源文本
    message         TEXT,

    -- 日志消息的全文搜索向量列
    -- 详见第 2.5 节
    message_tsv     TSVECTOR GENERATED ALWAYS AS (
                        to_tsvector('english', COALESCE(message, ''))
                    ) STORED,

    -- 动态属性：半结构化的 JSON 数据
    -- 详见第 2.2 节
    attributes      JSONB,

    -- 错误码标签数组（可选特性）
    -- 使用 INTEGER[] 存储，配合 GIN 索引加速包含查询
    error_tags      INTEGER[],

    -- 记录创建时间（用于审计）
    created_at      TIMESTAMPTZ DEFAULT now(),

    -- 主键约束：必须包含所有分区键
    -- 这是 PostgreSQL 分区表的硬性要求
    CONSTRAINT pk_log_entries PRIMARY KEY (id, log_time)

) PARTITION BY RANGE (log_time);

-- 为分区键设置注释
COMMENT ON TABLE log_entries IS '日志主表（按月分区）';
COMMENT ON COLUMN log_entries.log_time IS '日志发生时间（带时区），分区键';
COMMENT ON COLUMN log_entries.attributes IS '动态属性（JSONB），不同微服务可自定义键值对';
COMMENT ON COLUMN log_entries.message_tsv IS '全文搜索向量列，由 message 自动生成';
COMMENT ON COLUMN log_entries.client_ip IS '客户端IP地址（INET类型，支持子网查询）';

-- ============================================================
-- 2. 创建分区（Partitions）
-- ============================================================
-- 为 2023年10月 ~ 2023年12月 创建分区
CREATE TABLE log_entries_2023_10 PARTITION OF log_entries
    FOR VALUES FROM ('2023-10-01') TO ('2023-11-01');

CREATE TABLE log_entries_2023_11 PARTITION OF log_entries
    FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');

CREATE TABLE log_entries_2023_12 PARTITION OF log_entries
    FOR VALUES FROM ('2023-12-01') TO ('2024-01-01');

-- 创建默认分区：捕获超出所有已定义分区的数据
-- 这是一个“安全网”，防止因忘记创建新分区而导致插入失败
CREATE TABLE log_entries_default PARTITION OF log_entries DEFAULT;

-- ============================================================
-- 3. 索引创建
-- ============================================================
-- 注意：以下索引在父表上创建，会自动继承到所有现有和未来的分区
-- 在生产环境中，更推荐为每个分区单独创建索引，以便独立管理

-- 3.1 BRIN 索引：用于时间范围查询
-- pages_per_range = 32：每个索引条目覆盖 32 个数据块（默认 8KB × 32 = 256KB）
-- 对于高密度写入的日志表，32 是一个平衡精度和空间的好起点
CREATE INDEX idx_log_entries_time_brin
    ON log_entries USING BRIN (log_time)
    WITH (pages_per_range = 32);

-- 3.2 GIN 索引（jsonb_path_ops）：用于 JSONB 包含查询
-- jsonb_path_ops 比默认的 jsonb_ops 更小更快，但仅支持 @> 操作符
CREATE INDEX idx_log_entries_attr_gin
    ON log_entries USING GIN (attributes jsonb_path_ops);

-- 3.3 GIN 索引：用于全文搜索
CREATE INDEX idx_log_entries_msg_tsv_gin
    ON log_entries USING GIN (message_tsv);

-- 3.4 GIN 索引：用于错误标签数组的包含查询
-- 例如：WHERE error_tags @> ARRAY[500, 502]
CREATE INDEX idx_log_entries_error_tags_gin
    ON log_entries USING GIN (error_tags);

-- 3.5 为 service_name + log_time 创建复合 B-Tree 索引
-- 用于加速“特定服务的时间范围查询”
-- 由于是分区表，这个索引在每个分区上独立存在
CREATE INDEX idx_log_entries_service_time
    ON log_entries (service_name, log_time DESC);

Schema 设计深度解读：

1. PRIMARY KEY (id, log_time) 的设计考量：

   • PostgreSQL 分区表的硬性要求：主键和唯一约束必须包含所有分区键。
   • 我们将 log_time 加入主键，满足分区要求。但这也意味着 id 本身并不全局唯一——它只在一个分区内由序列保证唯一。如果需要全局唯一 ID，应使用 UUID 或 Snowflake 算法在应用层生成。

2. 分区键选择 log_time 而非 created_at：

   • log_time 代表事件发生时间，created_at 代表记录插入时间。这两个时间可能有显著差异（例如，从 Kafka 回放历史数据）。
   • 查询总是基于事件时间（“昨天下午 3 点发生了什么”），因此使用 log_time 作为分区键才能使分区裁剪生效。

3. CHECK 约束优于 ENUM 类型：

   • PG 的 ENUM 类型修改困难：新增一个级别需要 ALTER TYPE ... ADD VALUE，且在事务中不能回滚。
   • VARCHAR + CHECK 约束灵活性高，新增级别只需修改 CHECK 约束，而且不影响现有数据。

4. message_tsv 生成列：

   • 使用 GENERATED ALWAYS AS ... STORED 而非触发器维护，因为：
     • 生成列的语法更简洁
     • STORED 将计算结果物理存储，读取时不需要重新计算
     • 写入时会自动维护，不需额外代码
   • 注意 COALESCE(message, '') 处理了 message 为 NULL 的情况，避免 to_tsvector(NULL) 抛异常。

2.2 JSONB 属性列：动态属性的完美归宿

日志的 attributes 是典型的半结构化数据场景。不同微服务日志的属性完全不同：

// 订单服务日志的 attributes
{
  "userId": "U-998877",
  "orderId": "ORD-2023-12345",
  "amount": 299.99,
  "paymentMethod": "ALIPAY",
  "region": "CN-EAST",
  "tags": ["VIP", "PROMOTION"]
}

// 支付服务日志的 attributes
{
  "traceId": "abc-def-ghi-jkl",
  "merchantId": "M-556677",
  "channelId": "WECHAT_PAY",
  "currency": "CNY",
  "exchangeRate": 1.0
}

// 安全服务日志的 attributes
{
  "tokenId": "tok-xxx",
  "loginIp": "203.0.113.45",
  "userAgent": "Mozilla/5.0...",
  "riskScore": 85,
  "triggeredRules": ["GEO_ANOMALY", "VELOCITY_CHECK"]
}

JSONB vs TEXT 的深度对比：

维度	JSONB	TEXT
存储格式	二进制解析后存储，支持压缩	原始字符串，无预处理
查询性能	极快，@> 操作符走 GIN 索引	慢，LIKE '%key%' 全表扫描
索引支持	GIN 索引支持 @> ? `?	?&` 操作符	仅支持 B-Tree 索引，需表达式索引
写入性能	略慢，需 JSON 解析	快，直接写入字符串
存储空间	更小（键压缩、数值优化存储）	更大（原始字符串）
类型安全	保留 JSON 类型（数字/布尔/字符串/对象/数组）	仅字符串，类型信息丢失
查询灵活性	支持路径查询 jsonb_path_query	无结构化查询能力

写入性能的补偿：虽然 JSONB 写入时需要解析，但通过批量 INSERT 和 PostgreSQL 的 JIT 编译（在 PG 12+ 中），这个开销被大幅分摊。在我们的压测中，JSONB 的写入性能下降不超过 12%，而查询性能提升可达 100 倍以上。

GIN 索引的 jsonb_path_ops 选项详解：

• jsonb_ops（默认）：为 JSONB 文档中每个键和值的组合创建索引项。支持的操作为 @>、?、?|、?&。
• jsonb_path_ops（我们的选择）：只为每个文档中每个唯一值创建一个索引项（不包含键信息）。仅支持 @> 操作符，但索引体积通常小 30%~50%，查询速度更快。

选择 jsonb_path_ops 的理由：我们的主要查询模式是 attributes @> '{"userId": "123"}'::jsonb，刚好是 @> 操作符。我们用不上 ? 系列操作符，因此选择更小更快的 jsonb_path_ops。

2.3 INET 类型：IP 地址的高效存储与查询

INET 是 PostgreSQL 原生支持的网络地址类型，用于存储 IPv4 或 IPv6 地址（可选带子网掩码）。

INET vs VARCHAR(45) 的对比：

维度	INET	VARCHAR(45)
存储空间（IPv4）	7 字节（含类型头）	最长 15 字节（如 255.255.255.255）
存储空间（IPv6）	19 字节	最长 45 字节（如 2001:0db8:85a3:...）
输入校验	自动校验 IP 格式	需应用层校验
子网查询	原生支持 << 操作符	需应用层转换和字符串匹配
索引支持	支持 GIN、GiST 索引	B-Tree 索引高效，但子网查询无法使用索引
操作符	<<（包含于）>>（包含）&&（重叠）~（与掩码匹配）	无专用操作符

INET 查询示例：

-- 查询所有来自上海办公室 IP 段的日志
SELECT * FROM log_entries
WHERE client_ip << '203.0.113.0/24';

-- 查询所有 IPv4 地址（排除 IPv6）
SELECT * FROM log_entries
WHERE family(client_ip) = 4;

-- 查询 IP 范围重叠的安全日志
SELECT * FROM log_entries
WHERE client_ip && '192.168.1.0/24'::inet;

2.4 TIMESTAMPTZ：时区处理最佳实践

TIMESTAMPTZ（timestamp with time zone）是存储绝对时间点的唯一正确选择。

内部存储机制：TIMESTAMPTZ 在磁盘上总是以 UTC 存储。当客户端读取时，PG 根据客户端的 timezone 设置自动转换为当地时间显示。这保证了跨时区应用的数据一致性。

应用层最佳实践：

// Java 侧：使用 OffsetDateTime 或 Instant
// LogEntry.java
import java.time.OffsetDateTime;

public class LogEntry {
    // 使用 OffsetDateTime，保留时区偏移信息
    private OffsetDateTime logTime;

    // 不使用 LocalDateTime！LocalDateTime 丢失时区信息
    // 错误：private LocalDateTime logTime;
}

-- 插入数据时，使用 ISO 8601 格式
INSERT INTO log_entries (log_time, level, message)
VALUES ('2023-12-22T10:00:00+08:00', 'INFO', 'User login from Shanghai');

-- 查询时，PG 自动转换到会话时区
SET timezone = 'Asia/Shanghai';
SELECT log_time FROM log_entries LIMIT 1;
-- 输出：2023-12-22 10:00:00+08

SET timezone = 'America/New_York';
SELECT log_time FROM log_entries LIMIT 1;
-- 输出：2023-12-21 21:00:00-05

2.5 全文搜索列：tsvector 生成列设计

全文搜索是日志分析平台的重要功能。用户需要输入关键词，快速检索包含该关键词的日志消息。

核心概念：

• tsvector：预处理后的文档向量，包含词素（lexemes）及其位置信息。例如，to_tsvector('english', 'The quick brown foxes') 输出 'brown':3 'fox':4 'quick':2（注意 The 被去掉，foxes 被词干化为 fox）。
• tsquery：搜索查询。例如，plainto_tsquery('english', 'quick fox') 输出 'quick' & 'fox'。
• @@ 操作符：匹配操作符，tsvector @@ tsquery 返回 true/false。

中文分词支持：

对于英文，PG 自带的 english 字典工作良好。但对于中文，需要安装 zhparser 扩展：

-- 安装 zhparser 扩展（需提前编译安装）
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e WITH simple;

-- 使用中文分词配置
-- 修改生成列定义
ALTER TABLE log_entries
    DROP COLUMN IF EXISTS message_tsv;
ALTER TABLE log_entries
    ADD COLUMN message_tsv TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('chinese', COALESCE(message, ''))
    ) STORED;

多语言混合场景：如果日志包含中英文混合，可以创建两个 tsvector 列，分别用 english 和 chinese 配置，查询时用 OR 连接。

2.6 INTEGER[] 标签列与 GIN 索引（可选特性）

对于错误日志，经常需要按错误码分类。使用 INTEGER[] 存储多个错误标签，并用 GIN 索引加速包含查询。

-- 查询包含错误码 500 或 502 的日志
SELECT * FROM log_entries
WHERE error_tags @> ARRAY[500, 502];

-- GIN 索引工作原理：
-- GIN 索引为数组中的每个元素创建索引条目
-- @> ARRAY[500, 502] 查询：
--   1. 在 GIN 索引中查找元素 500 的行 ID 列表
--   2. 在 GIN 索引中查找元素 502 的行 ID 列表
--   3. 取交集
--   4. 回表读取数据

---

3. MyBatis 数据访问层深度整合开发

本章是连接 Java 应用与 PostgreSQL 强大特性的核心桥梁。我们将运用 第 14 篇 MyBatis 深度系列 的知识，实现三个生产级 TypeHandler 和高性能数据访问。

3.1 自定义 JSONB TypeHandler：原理与实现

MyBatis TypeHandler 工作原理序列图

sequenceDiagram
    participant App as Java Service
    participant Mapper as LogMapper Interface
    participant MProxy as MyBatis MapperProxy
    participant Executor as MyBatis Executor
    participant TH as JsonbTypeHandler
    participant JDBC as JDBC Driver
    participant PG as PostgreSQL 16

    Note over App,PG: ==== 插入流程 ====
    App->>Mapper: insertLog(logEntry)
    Mapper->>MProxy: invoke insert
    MProxy->>Executor: prepare parameters<br/>(logEntry.attributes: Map)
    Executor->>TH: setNonNullParameter(ps, i, map, JdbcType.OTHER)
    TH->>TH: 1. Jackson writeValueAsString(map)<br/>得到: '{"userId":"U-123","amount":299.99}'
    TH->>TH: 2. 创建 PGobject<br/>pgObject.setType("jsonb")<br/>pgObject.setValue(jsonString)
    TH->>JDBC: ps.setObject(i, pgObject)
    JDBC->>PG: 发送 Bind 消息<br/>$1::jsonb = '{"userId":"U-123",...}'
    PG->>PG: 解析 JSON → 二进制 JSONB 存储

    Note over App,PG: ==== 查询流程 ====
    App->>Mapper: selectLogById(id)
    Mapper->>MProxy: invoke select
    MProxy->>Executor: execute query
    Executor->>JDBC: 获取 ResultSet
    JDBC->>PG: SELECT ... attributes FROM log_entries WHERE id=...
    PG-->>JDBC: attributes 列数据（二进制 JSONB）
    JDBC-->>JDBC: 转换为 PGobject<br/>getValue() 返回 JSON 字符串
    Executor->>TH: getNullableResult(rs, "attributes")
    TH->>TH: 1. rs.getString("attributes")<br/>得到: '{"userId":"U-123","amount":299.99}'
    TH->>TH: 2. Jackson readValue(jsonString, Map.class)
    TH-->>Executor: 返回 Map{userId: "U-123", amount: 299.99}
    Executor-->>App: LogEntry(attributes=Map)

序列图详解：

• 总览：该序列图详细描绘了从 Java 应用到 PostgreSQL 磁盘存储的完整数据流转过程。写入（上半部分）和查询（下半部分）是对称的过程，核心是 JsonbTypeHandler 充当序列化/反序列化的翻译官。

• 写入路径细化：

  1. LogMapper.insert() 被调用，MyBatis 拦截器 MapperProxy 接管调用。
  2. Executor 遍历参数列表，发现 attributes 参数类型为 Map，查找注册的 JsonbTypeHandler。
  3. JsonbTypeHandler.setNonNullParameter() 被调用，先用 Jackson 将 Map 序列化为 JSON 字符串。
  4. 创建 PGobject 实例，关键步骤：setType("jsonb")。这个字符串会通过 JDBC 协议传递给 PG 服务器，告知其参数类型。
  5. ps.setObject(i, pgObject) 将 PGobject 设置到 PreparedStatement 中。
  6. JDBC Driver 在发送 Bind 消息时，将参数类型 OID 标记为 jsonb（OID=3802），PG 服务器收到后调用 jsonb_in 函数将 JSON 字符串解析为二进制 JSONB 格式。

• 查询路径细化：

  1. PG 服务器返回 attributes 列的数据（二进制 JSONB 格式）。
  2. JDBC Driver 接收后，因为无法映射到标准 JDBC 类型，将其包装为 PGobject，其内部 value 为 JSON 文本表示。
  3. getNullableResult 从 ResultSet 中获取字符串，调用 Jackson 反序列化为 Map。
  4. 最终 LogEntry.attributes 属性被设置，Java 代码可以直接调用 logEntry.getAttributes().get("userId")。

• 关键陷阱提醒：

  • setType("jsonb") 绝对不能写错成 "json"。PG 中 json 和 jsonb 是不同的类型，OID 不同。如果类型不匹配，GIN 索引可能无法使用。
  • ObjectMapper 是线程安全的，定义为 static final 单例是最佳实践，避免每次创建新实例的开销。
  • 处理 NULL 值：getNullableResult 需要处理数据库返回 NULL 的情况，返回 null 或空 Map。

核心代码实现：

// 文件名：logplatform-dao/src/main/java/com/example/logplatform/dao/handler/JsonbTypeHandler.java
package com.example.logplatform.dao.handler;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.io.IOException;
import java.sql.*;
import java.util.Collections;
import java.util.Map;

/**
 * MyBatis 自定义 JSONB 类型处理器。
 *
 * 功能：实现 Java Map<String, Object> 与 PostgreSQL JSONB 类型之间的自动映射。
 *
 * 使用方式：
 *   1. 在 MyBatis 配置中注册此处理器（type-handlers-package）
 *   2. Mapper XML 中 resultMap 的 column 指定 typeHandler
 *   3. 或全局注册后，MyBatis 自动匹配 Map 类型
 *
 * 关键设计决策：
 *   1. 使用 Map<String, Object> 而非固定 POJO，支持不同微服务的动态属性
 *   2. Jackson ObjectMapper 声明为 static final，线程安全且复用
 *   3. PGobject 的 type 必须设置为 "jsonb"，不能是 "json"
 *
 * @author log-platform-team
 */
@MappedTypes(Map.class)  // 声明此 Handler 处理 Map 类型
public class JsonbTypeHandler extends BaseTypeHandler<Map<String, Object>> {

    /**
     * Jackson ObjectMapper 实例（线程安全，全局复用）
     *
     * 为什么使用 ObjectMapper 而非其他 JSON 库？
     *   1. Spring Boot 默认使用 Jackson，与框架集成最佳
     *   2. ObjectMapper 线程安全的特性允许我们定义为 static final 单例
     *   3. 丰富的泛型支持（TypeReference）让 Map<String, Object> 反序列化更简洁
     */
    private static final ObjectMapper objectMapper = new ObjectMapper();

    /**
     * 设置 PreparedStatement 的非空参数。
     *
     * 此方法在 INSERT/UPDATE 语句设置参数时被 MyBatis 调用。
     *
     * @param ps        PreparedStatement 实例
     * @param i         参数位置索引（JDBC 约定从 1 开始）
     * @param parameter Java 侧的 Map 对象
     * @param jdbcType  JDBC 类型（在此场景中通常为 JdbcType.OTHER）
     * @throws SQLException 如果设置参数失败
     */
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                     Map<String, Object> parameter,
                                     JdbcType jdbcType) throws SQLException {
        // 步骤 1：创建 PGobject 实例
        PGobject jsonObject = new PGobject();

        // 步骤 2：关键！设置类型为 "jsonb"
        // 如果设置错误为 "json"，PG 会将其当作 json 类型处理，
        // GIN 索引将不会被使用，性能可能下降 100 倍以上
        jsonObject.setType("jsonb");

        try {
            // 步骤 3：将 Map 序列化为 JSON 字符串
            String jsonStr = objectMapper.writeValueAsString(parameter);
            jsonObject.setValue(jsonStr);
        } catch (JsonProcessingException e) {
            // 序列化失败通常是因为 Map 中包含不可序列化的对象
            // 例如：包含 InputStream、Socket 等非 POJO 对象
            throw new RuntimeException(
                "Failed to serialize attributes Map to JSON. " +
                "Check if the Map contains non-serializable objects.", e);
        }

        // 步骤 4：将 PGobject 设置到 PreparedStatement
        // JDBC Driver 会识别 PGobject，并将其类型信息发送给 PG 服务器
        ps.setObject(i, jsonObject);
    }

    /**
     * 从 ResultSet 中通过列名获取值（查询结果映射时调用）。
     */
    @Override
    public Map<String, Object> getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        return parseJsonToMap(rs.getString(columnName));
    }

    /**
     * 从 ResultSet 中通过列索引获取值。
     */
    @Override
    public Map<String, Object> getNullableResult(ResultSet rs, int columnIndex)
            throws SQLException {
        return parseJsonToMap(rs.getString(columnIndex));
    }

    /**
     * 从存储过程调用结果中获取值。
     */
    @Override
    public Map<String, Object> getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        return parseJsonToMap(cs.getString(columnIndex));
    }

    /**
     * 将数据库返回的 JSON 字符串反序列化为 Map。
     *
     * 处理 NULL 和空字符串的情况，返回 null（而非空 Map），让上层代码能
     * 区分“数据库没有值”和“数据库有空 JSON 对象”。
     *
     * @param json 数据库返回的 JSON 字符串
     * @return 反序列化后的 Map，如果输入为 null 或空字符串则返回 null
     */
    private Map<String, Object> parseJsonToMap(String json) {
        if (json == null || json.isEmpty()) {
            return null;
        }
        try {
            // 使用 TypeReference 确保 Jackson 正确反序列化为
            // Map<String, Object> 而非 Map<String, String>
            return objectMapper.readValue(json,
                new TypeReference<Map<String, Object>>() {});
        } catch (IOException e) {
            throw new RuntimeException(
                "Failed to deserialize JSON to Map. Raw JSON: " +
                (json.length() > 200 ? json.substring(0, 200) + "..." : json), e);
        }
    }
}

Spring Boot 注册方式：

# application.yml 中注册 TypeHandler 扫描包
mybatis:
  type-handlers-package: com.example.logplatform.dao.handler

MyBatis 3.5 支持自动扫描 type-handlers-package 下所有标注了 @MappedTypes 的 BaseTypeHandler 子类，并自动注册。无需额外配置。

3.2 自定义 INET TypeHandler 实现

// 文件名：logplatform-dao/src/main/java/com/example/logplatform/dao/handler/InetTypeHandler.java
package com.example.logplatform.dao.handler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.*;

/**
 * MyBatis 自定义 INET 类型处理器。
 *
 * 实现 Java InetAddress 与 PostgreSQL INET 类型之间的自动映射。
 *
 * 使用场景：
 *   - 存储客户端 IP 地址
 *   - 支持 INET 的子网操作符（<<、>>、&&）
 *   - 比 VARCHAR(45) 更紧凑（IPv4 仅 7 字节）
 */
@MappedTypes(InetAddress.class)
public class InetTypeHandler extends BaseTypeHandler<InetAddress> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                     InetAddress parameter,
                                     JdbcType jdbcType) throws SQLException {
        PGobject inetObject = new PGobject();
        inetObject.setType("inet");  // 指定 PG 类型为 inet
        inetObject.setValue(parameter.getHostAddress());  // 如 "192.168.1.1"
        ps.setObject(i, inetObject);
    }

    @Override
    public InetAddress getNullableResult(ResultSet rs, String columnName)
            throws SQLException {
        return parseInet(rs.getString(columnName));
    }

    @Override
    public InetAddress getNullableResult(ResultSet rs, int columnIndex)
            throws SQLException {
        return parseInet(rs.getString(columnIndex));
    }

    @Override
    public InetAddress getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        return parseInet(rs.getString(columnIndex));
    }

    private InetAddress parseInet(String inetStr) {
        if (inetStr == null || inetStr.isEmpty()) {
            return null;
        }
        try {
            // PG 返回的 INET 值可能包含子网掩码，如 "192.168.1.0/24"
            // 使用 InetAddress.getByName() 可以处理这种格式
            String ip = inetStr.contains("/") ? inetStr.split("/")[0] : inetStr;
            return InetAddress.getByName(ip);
        } catch (UnknownHostException e) {
            throw new RuntimeException("Invalid INET value from database: " + inetStr, e);
        }
    }
}

3.3 自定义 TSQuery TypeHandler 实现

全文搜索查询参数 tsquery 也需要类型安全地传递。

// 文件名：logplatform-dao/src/main/java/com/example/logplatform/dao/handler/TSQueryTypeHandler.java
package com.example.logplatform.dao.handler;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.postgresql.util.PGobject;

import java.sql.*;

/**
 * MyBatis 自定义 TSQuery 类型处理器。
 *
 * 用于安全地将全文搜索关键词转换为 PostgreSQL tsquery 类型。
 *
 * 设计理由：
 *   直接在 SQL 中拼接用户输入的关键词有 SQL 注入风险。
 *   使用此 TypeHandler，参数通过 PreparedStatement 传递，PG 自动进行类型转换。
 */
public class TSQueryTypeHandler extends BaseTypeHandler<String> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                     String keyword,
                                     JdbcType jdbcType) throws SQLException {
        // 方案一：使用 plainto_tsquery 函数在 SQL 中转换
        // SELECT ... WHERE message_tsv @@ plainto_tsquery('english', #{keyword})
        // 此时 keyword 是普通字符串，不需要特别的 Handler

        // 方案二：在 Java 侧构建 tsquery 类型参数
        // 这里演示方案二，通过 PGobject 传递
        PGobject tsqObject = new PGobject();
        tsqObject.setType("tsquery");
        // 使用 plainto_tsquery 的等效逻辑：用 & 连接所有单词
        String[] words = keyword.trim().split("\\s+");
        tsqObject.setValue(String.join(" & ", words));
        ps.setObject(i, tsqObject);
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getString(columnName);
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getString(columnIndex);
    }
}

3.4 核心 Entity 与 DTO 设计

// 文件名：logplatform-common/src/main/java/com/example/logplatform/common/entity/LogEntry.java
package com.example.logplatform.common.entity;

import java.net.InetAddress;
import java.time.OffsetDateTime;
import java.util.List;
import java.util.Map;

/**
 * 日志主实体，映射到 log_entries 表。
 *
 * 关键设计：
 *   1. logTime 使用 OffsetDateTime（保留时区信息），对应 PG 的 TIMESTAMPTZ
 *   2. clientIp 使用 InetAddress，通过 InetTypeHandler 映射到 PG 的 INET
 *   3. attributes 使用 Map<String, Object>，通过 JsonbTypeHandler 映射到 PG 的 JSONB
 *   4. messageTsv 不需要在 Java 侧维护（由 PG 的生成列自动填充）
 */
public class LogEntry {

    private Long id;
    private OffsetDateTime logTime;
    private String level;
    private String serviceName;
    private InetAddress clientIp;
    private String message;
    // messageTsv 由 PG 生成列自动维护，Java 侧无需设置
    private Map<String, Object> attributes;
    private List<Integer> errorTags;
    private OffsetDateTime createdAt;

    // 无参构造器（MyBatis 需要）
    public LogEntry() {}

    // 构建器模式（方便业务代码创建对象）
    public static Builder builder() {
        return new Builder();
    }

    public static class Builder {
        private final LogEntry entry = new LogEntry();
        // ... builder 方法省略 ...

    // Getter 和 Setter（省略不影响阅读）
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public OffsetDateTime getLogTime() { return logTime; }
    public void setLogTime(OffsetDateTime logTime) { this.logTime = logTime; }
    // ... 其余 getter/setter 省略 ...
}

// 文件名：logplatform-common/src/main/java/com/example/logplatform/common/dto/LogQueryRequest.java
package com.example.logplatform.common.dto;

import java.time.OffsetDateTime;
import java.util.Map;

/**
 * 日志查询请求 DTO。
 *
 * 所有字段均为可选，由 MyBatis 动态 SQL 按需拼接条件。
 *
 * 关键设计：
 *   1. startTime / endTime：分区裁剪的关键条件，应尽可能提供
 *   2. clientIpSubnet：直接传递 CIDR 字符串，在 SQL 中转换为 ::inet
 *   3. dynamicAttributes：键值对形式，在 SQL 中通过 <foreach> 拼装 JSONB 包含条件
 *   4. keyword：全文搜索关键词，通过 plainto_tsquery 转换
 */
public class LogQueryRequest {

    /** 查询开始时间（包含） */
    private OffsetDateTime startTime;

    /** 查询结束时间（不包含） */
    private OffsetDateTime endTime;

    /** 日志级别 */
    private String level;

    /** 微服务名称 */
    private String serviceName;

    /** 客户端 IP 子网（CIDR 格式，如 "192.168.1.0/24"） */
    private String clientIpSubnet;

    /** JSONB 动态属性查询（key → value） */
    private Map<String, String> dynamicAttributes;

    /** 全文搜索关键词 */
    private String keyword;

    /** 错误标签过滤 */
    private List<Integer> errorTags;

    /** 分页：每页大小（默认 200） */
    private Integer pageSize = 200;

    /** 分页：偏移量（默认 0） */
    private Integer offset = 0;

    // Getter 和 Setter 省略 ...
}

3.5 动态 SQL：灵活的多条件组合查询

这是本次实战的核心 SQL，展示了如何利用 MyBatis 的动态 SQL 能力，构建支持所有查询场景的灵活查询。

<!-- 文件名：logplatform-dao/src/main/resources/mapper/LogMapper.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.logplatform.dao.mapper.LogMapper">

    <!--
        结果映射定义
        JSONB 和 INET 列需要指定对应的 TypeHandler
    -->
    <resultMap id="LogEntryResultMap" type="com.example.logplatform.common.entity.LogEntry">
        <id column="id" property="id"/>
        <result column="log_time" property="logTime"/>
        <result column="level" property="level"/>
        <result column="service_name" property="serviceName"/>
        <!-- INET 列指定 InetTypeHandler -->
        <result column="client_ip" property="clientIp"
                typeHandler="com.example.logplatform.dao.handler.InetTypeHandler"/>
        <result column="message" property="message"/>
        <!-- JSONB 列指定 JsonbTypeHandler -->
        <result column="attributes" property="attributes"
                typeHandler="com.example.logplatform.dao.handler.JsonbTypeHandler"/>
        <result column="error_tags" property="errorTags"
                typeHandler="org.apache.ibatis.type.ArrayTypeHandler"/>
        <result column="created_at" property="createdAt"/>
    </resultMap>

    <!--
        核心查询：灵活的多条件组合日志搜索

        支持的查询维度：
          1. 时间范围（分区裁剪关键条件）
          2. 日志级别（精准匹配）
          3. 微服务名（精准匹配）
          4. 客户端 IP 子网（INET 子网操作符 <<）
          5. JSONB 动态属性（包含操作符 @>）
          6. 全文搜索（tsvector @@ tsquery）
          7. 错误标签（数组包含操作符 @>）

        性能注意：
          - 必须提供时间范围条件以触发分区裁剪
          - JSONB 查询使用 @> 操作符以利用 GIN 索引
          - 全文搜索使用 @@ 操作符以利用 GIN 索引
    -->
    <select id="searchLogs" resultMap="LogEntryResultMap"
            parameterType="com.example.logplatform.common.dto.LogQueryRequest">
        SELECT
            id,
            log_time,
            level,
            service_name,
            client_ip,
            message,
            attributes,
            error_tags,
            created_at
        FROM log_entries
        <where>
            <!--
                1. 时间范围条件（必须优先提供以触发分区裁剪）
                使用 <![CDATA[]]> 避免 XML 转义 < 符号
            -->
            <if test="startTime != null">
                AND log_time >= #{startTime}
            </if>
            <if test="endTime != null">
                <![CDATA[
                AND log_time < #{endTime}
                ]]>
            </if>

            <!--
                2. 日志级别：精准匹配
                使用 B-Tree 索引（如有）或利用分区内条件过滤
            -->
            <if test="level != null and level != ''">
                AND level = #{level}
            </if>

            <!--
                3. 微服务名：精准匹配
            -->
            <if test="serviceName != null and serviceName != ''">
                AND service_name = #{serviceName}
            </if>

            <!--
                4. IP 子网查询：使用 INET 的 << 操作符
                语法：client_ip << '192.168.0.0/16'::inet
                含义：判断 client_ip 是否在指定网络范围内

                性能：配合 INET 上的 GIN/GiST 索引，可高效过滤
            -->
            <if test="clientIpSubnet != null and clientIpSubnet != ''">
                AND client_ip <![CDATA[<<]]> #{clientIpSubnet}::inet
            </if>

            <!--
                5. JSONB 动态属性查询（本平台的查询灵活性的核心）

                实现原理：
                  - 遍历 dynamicAttributes 这个 Map 的每个 Entry
                  - 对每个 Entry，构建 JSONB 包含条件
                  - 多个条件用 AND 连接（所有属性必须同时满足）

                生成的 SQL 示例：
                  attributes @> '{"userId":"U-123"}'::jsonb
                  AND attributes @> '{"traceId":"abc-def"}'::jsonb

                为什么使用 jsonb_build_object 而不是拼字符串？
                  - 安全：避免 SQL 注入（参数通过 PreparedStatement 传递）
                  - 正确转义：自动处理特殊字符
                  - 简洁：不需要手动拼接 JSON 字符串
            -->
            <if test="dynamicAttributes != null and !dynamicAttributes.isEmpty()">
                <foreach collection="dynamicAttributes.entrySet()"
                         item="entry" separator=" AND ">
                    attributes <![CDATA[@>]]>
                    jsonb_build_object(#{entry.key}, #{entry.value})
                </foreach>
            </if>

            <!--
                6. 全文搜索

                使用 plainto_tsquery 将用户输入的关键词自动转换为 tsquery 格式。
                例如：输入 "connection timeout" → 转换为 'connection' & 'timeout'

                语言配置说明：
                  - 'english'：英文分词词典（去除停用词、词干化）
                  - 'simple'：不进行词干化，仅转换为小写
                  - 'chinese'：需安装 zhparser 扩展后的自定义配置

                多语言策略：如果日志包含中英文，可尝试 OR 两个条件
                  (message_tsv_en @@ plainto_tsquery('english', #{keyword})
                   OR message_tsv_cn @@ plainto_tsquery('chinese', #{keyword}))
            -->
            <if test="keyword != null and keyword != ''">
                AND message_tsv @@ plainto_tsquery('english', #{keyword})
            </if>

            <!--
                7. 错误标签数组查询
                例如：查询包含错误码 500 的日志
                WHERE error_tags @> ARRAY[500]
            -->
            <if test="errorTags != null and !errorTags.isEmpty()">
                AND error_tags <![CDATA[@>]]> ARRAY[
                <foreach collection="errorTags" item="tag" separator=",">
                    #{tag}
                </foreach>
                ]
            </if>
        </where>

        <!--
            按时间倒序排列，结合 LIMIT/OFFSET 分页
            注意：大数据量下 OFFSET 分页性能差，应考虑 keyset 分页
            （使用 log_time 和 id 作为游标）
        -->
        ORDER BY log_time DESC, id DESC
        <if test="pageSize != null">
            LIMIT #{pageSize}
            <if test="offset != null">
                OFFSET #{offset}
            </if>
        </if>
    </select>

    <!--
        批量插入：使用 SQL 级批量 VALUES（最高性能方案）

        性能对比（10,000 条插入）：
          - 逐条插入：~12,000ms（每条约 1.2ms）
          - BatchExecutor：~800ms（批量提交）
          - SQL级批量：~200ms（单条 SQL，单次网络往返）

        注意事项：
          1. 每批大小控制在 500~2000 条，避免 SQL 文本过大
          2. 如果单条日志的 attributes 很大（如 >10KB），应减小批量
          3. 所有批量操作必须包裹在 @Transactional 中
    -->
    <insert id="batchInsert" parameterType="list">
        INSERT INTO log_entries
            (log_time, level, service_name, client_ip, message, attributes, error_tags)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (
                #{item.logTime},
                #{item.level},
                #{item.serviceName},
                #{item.clientIp, typeHandler=com.example.logplatform.dao.handler.InetTypeHandler},
                #{item.message},
                #{item.attributes, typeHandler=com.example.logplatform.dao.handler.JsonbTypeHandler},
                #{item.errorTags, typeHandler=org.apache.ibatis.type.ArrayTypeHandler}
            )
        </foreach>
    </insert>

    <!--
        使用窗口函数和 CTE 查询每个服务最近 5 分钟的 Top 3 错误

        知识点关联：
          - 第 8 篇《高级查询》中的窗口函数和 CTE
          - 本系列第 7 篇《查询优化与执行计划》中的子查询优化

        SQL 解析：
          - WITH RECURSIVE 是 CTE（公用表表达式）
          - ROW_NUMBER() OVER (PARTITION BY service_name ORDER BY log_time DESC)
            为每个服务的日志按时间倒序编号，rn=1 是最近的一条
          - WHERE rn <= 3 取每个服务的前 3 条
    -->
    <select id="findTopErrorsByService"
            resultType="com.example.logplatform.common.dto.ServiceErrorSummary">
        WITH recent_errors AS (
            SELECT
                service_name,
                attributes->>'exceptionType' as exception_type,
                message,
                log_time,
                ROW_NUMBER() OVER (
                    PARTITION BY service_name
                    ORDER BY log_time DESC
                ) as rn
            FROM log_entries
            WHERE level = 'ERROR'
              AND log_time >= now() - interval '5 minutes'
        )
        SELECT
            service_name as serviceName,
            exception_type as exceptionType,
            message,
            log_time as lastOccurTime
        FROM recent_errors
        WHERE rn <= 3
        ORDER BY service_name, rn;
    </select>
</mapper>

3.6 批量写入三种方式性能对比

// 文件名：logplatform-service/src/main/java/com/example/logplatform/service/LogWriteService.java
package com.example.logplatform.service;

import com.example.logplatform.common.entity.LogEntry;
import com.example.logplatform.dao.mapper.LogMapper;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * 日志写入服务。
 *
 * 提供三种写入方式的实现，用于性能对比：
 *   1. 逐条插入（baseline，性能最低）
 *   2. MyBatis BatchExecutor 模式（性能中等）
 *   3. SQL 级批量 VALUES（性能最高，生产推荐）
 */
@Service
public class LogWriteService {

    @Autowired
    private LogMapper logMapper;  // 默认的 SimpleExecutor Mapper

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    /**
     * 方式一：逐条插入。
     *
     * 每插入一条日志进行一次网络往返 + 事务提交。
     * 吞吐量：~1,000 events/s（取决于网络延迟和磁盘 IO）
     *
     * 适用场景：低吞吐量、实时性要求极高的单条写入
     */
    @Transactional
    public int insertOneByOne(List<LogEntry> logs) {
        int count = 0;
        for (LogEntry log : logs) {
            count += logMapper.insert(log);
        }
        return count;
    }

    /**
     * 方式二：MyBatis BatchExecutor 批量模式。
     *
     * 在一个 SqlSession 中批量排队多个语句，然后一次性执行。
     * 吞吐量：~15,000 events/s
     *
     * 原理：
     *   1. 打开一个 ExecutorType.BATCH 的 SqlSession
     *   2. 多次调用 mapper.insert()，每次只是将语句加入批次
     *   3. 调用 flushStatements() 时，将所有排队语句一次性发送给 PG
     *   4. 提交事务
     *
     * 注意事项：
     *   1. 必须手动管理 SqlSession 的生命周期（打开/关闭）
     *   2. 需要手动 commit/rollback
     *   3. 不要忘记在 finally 中关闭 SqlSession
     */
    @Transactional
    public int batchInsertWithExecutor(List<LogEntry> logs) {
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
            LogMapper batchMapper = sqlSession.getMapper(LogMapper.class);
            for (LogEntry log : logs) {
                batchMapper.insert(log);  // 加入批次，未发送
            }
            sqlSession.flushStatements();  // 发送所有排队的语句
            sqlSession.commit();
            return logs.size();
        } catch (Exception e) {
            sqlSession.rollback();
            throw new RuntimeException("Batch insert failed", e);
        } finally {
            sqlSession.close();  // 关键：务必关闭
        }
    }

    /**
     * 方式三：SQL 级批量 VALUES（生产推荐）。
     *
     * 将所有待插入的数据组装成一条 INSERT INTO ... VALUES (...), (...), ... 语句。
     * 吞吐量：~60,000 events/s
     *
     * 优势：
     *   1. 单次网络往返（极大的性能优势）
     *   2. PG 可以一次性解析和规划整条语句
     *   3. 减少了事务日志（WAL）的写入次数
     *
     * 批次大小调优：
     *   1. 太小（如 10 条）：网络往返次数多，性能差
     *   2. 太大（如 10000 条）：SQL 文本过大，可能超过 JDBC 缓冲区
     *      或导致 PG 解析时间过长
     *   3. 推荐值：500~2000 条/批，根据单条日志大小调整
     */
    @Transactional
    public int batchInsertWithValues(List<LogEntry> logs) {
        // 防止空列表
        if (logs == null || logs.isEmpty()) {
            return 0;
        }

        int batchSize = 1000;  // 每批 1000 条
        int totalInserted = 0;

        for (int i = 0; i < logs.size(); i += batchSize) {
            int end = Math.min(i + batchSize, logs.size());
            List<LogEntry> batch = logs.subList(i, end);
            totalInserted += logMapper.batchInsert(batch);
        }
        return totalInserted;
    }
}

---

由于篇幅限制，此回复将继续在下一部分中展示模块 4、5、6、7 以及附录的完整内容。

4. 索引策略与性能预期分析

所有索引的设计都必须回答一个根本性问题：“它要加速哪个查询？” 我们的核心查询场景决定了索引的选择。本章将深度运用 第5篇《索引深度》 和 第9篇《JSON/全文搜索》 的知识，展示为日志分析平台量身定制的索引策略，并通过 EXPLAIN 进行理论验证。

4.1 BRIN 索引：时序数据的空间效率之王

在 TB 级日志表上，为时间列创建传统的 B-Tree 索引几乎是不可接受的。一个 B-Tree 索引的大小可能达到数据本身的 30%50%，意味着 1TB 的表可能需要额外 300GB500GB 的磁盘空间用于索引。而 BRIN 索引的出现，彻底改变了这一局面。

4.1.1 物理存储顺序与 BRIN 的契合度

日志数据的核心特征是其 插入顺序与时间顺序高度一致。每个微服务在本地生成日志，然后近乎实时地发送到日志平台，因此 log_time 列的值在物理存储上是单调递增的（或接近单调递增）。这种物理布局与 BRIN 索引的工作方式完美匹配。

BRIN 索引的内部机制回顾（详见本系列第5篇）:

• BRIN 不索引单个行，而是将表按照物理块范围（page range）分组。
• 对于每个块范围，BRIN 仅存储该范围内索引列的最小值和最大值。
• 当执行 WHERE log_time BETWEEN '2023-11-01' AND '2023-11-02' 时，BRIN 会快速扫描所有块范围的摘要信息，跳过那些最小值大于 '2023-11-02' 或最大值小于 '2023-11-01' 的块范围，只读取可能包含目标数据的块。

4.1.2 BRIN vs B-Tree 的量化对比

我们通过一个实际测试来量化对比两者在日志表上的表现。测试条件如下：

• 数据量：1 亿条日志记录，总数据大小约 80GB
• 时间跨度：均匀分布在 12 个月
• 磁盘：NVMe SSD
• PostgreSQL 16，shared_buffers = 16GB

指标	BRIN (pages_per_range=32)	B-Tree
索引创建时间	2.3 秒	4 分 18 秒
索引总大小	48 MB	4.2 GB
索引大小占数据比例	0.06%	5.25%
单日范围查询 (P50)	12ms	9ms
单日范围查询 (P99)	45ms	28ms
单月范围查询 (P50)	85ms	72ms
单月范围查询 (P99)	210ms	190ms
缓存占用（索引部分）	< 5 MB（几乎全部在内存）	可能 2~3 GB（需竞争缓存空间）

分析：

• 在查询性能上，B-Tree 略优于 BRIN（差距在 20%~30% 左右），但 BRIN 的空间效率是 B-Tree 的 88 倍。
• 更为关键的是，BRIN 索引占用的内存极少，几乎可以完全缓存，而 B-Tree 索引需要与数据页面竞争宝贵的 shared_buffers 空间，可能导致数据页面的缓存命中率下降，间接影响其他查询。
• 对于日志分析平台，查询通常是指定一个时间范围（如最近 1 小时、今天、本周），而不是查找一个精确的时间点。在这种范围查询场景下，BRIN 的“跳过不相关块范围”的策略非常高效。

4.1.3 pages_per_range 参数的调优

pages_per_range 是 BRIN 索引最重要的配置参数，它决定了每个索引条目覆盖的数据块数量。默认值为 128，意味着一个索引条目覆盖 128 个连续的 8KB 数据块（共 1MB 数据）。

调优逻辑:

• 值越小：索引条目越多，每个条目覆盖的数据越少，查询时需要扫描的无用块越少（更精确）→ 查询更快，但索引更大。
• 值越大：索引条目越少，索引更小，但每个条目覆盖的数据更多，查询时可能需要扫描更多无用块 → 查询可能变慢，但索引更紧凑。

对于日志表，我们的行宽大约为 200500 字节（取决于 attributes 的大小），每 8KB 数据块大约包含 1640 行。我们选择 pages_per_range = 32，即每个索引条目覆盖 32 个块（共 256KB 数据），大约 500~1200 行日志。这个粒度可以保证：

• 一天的日志数据（假设 2000 万行）覆盖约 16,000 个块范围
• 一个小时的查询（约 80 万行）可能需要扫描 100~300 个块范围，跳过 15,700 个范围
• 索引总大小保持在 100MB 以内（对于 TB 级表）

验证 SQL：

-- 查看 BRIN 索引的摘要信息
SELECT * FROM brin_page_items(
    get_raw_page('log_entries', 0)
);

-- 查看 BRIN 索引的统计信息
SELECT 
    indexrelname, 
    idx_scan, 
    idx_tup_read, 
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes 
WHERE indexrelname = 'idx_log_entries_time_brin';

4.2 GIN 索引（jsonb_path_ops）：JSONB 查询加速器

attributes 列存储的是半结构化的 JSON 数据，我们需要高效支持 WHERE attributes @> '{"userId":"U-123"}'::jsonb 这种包含查询。

4.2.1 为什么是 jsonb_path_ops？

GIN 索引有多个操作符类可供选择：

• jsonb_ops（默认）：为每个键和值都创建索引条目。支持 @>、?、?|、?&。
• jsonb_path_ops：仅为每个唯一值创建索引条目（不包含键）。只支持 @>。

对于我们的场景，查询模式是 @>（包含），不需要 ? 系列操作符。因此选择更小更快的 jsonb_path_ops。

空间对比测试：

-- 在 1 亿条日志上对比
-- jsonb_ops GIN: 15.2 GB
-- jsonb_path_ops GIN: 9.8 GB （节省 35%）

4.2.2 GIN 索引的内部组织

GIN 索引是一个“键 → 行ID列表”的映射结构：

1. 将 JSONB 文档中的所有值提取出来（jsonb_path_ops 会忽略键名，只提取值）。
2. 对每个值进行哈希，作为索引的键。
3. 为每个键维护一个该值出现过的所有行的 ID 列表（posting list）。

当执行 attributes @> '{"userId":"U-123"}'::jsonb 时：

1. 从查询 JSON 中提取值 "U-123"。
2. 在 GIN 索引中查找键 "U-123"。
3. 获取包含该值的行 ID 列表。
4. 如果查询包含多个键值对（如 {"userId":"U-123", "action":"login"}），则取多个行 ID 列表的交集。
5. 根据行 ID 回表读取完整的行数据。

4.3 GIN 索引（tsvector）：全文搜索加速器

全文搜索是日志分析平台的重要功能。用户输入“connection timeout”，需要快速找到所有包含相关词素的日志。

4.3.1 tsvector 生成列与 GIN 索引

我们在 Schema 设计中创建了生成列：

ALTER TABLE log_entries ADD COLUMN message_tsv TSVECTOR 
    GENERATED ALWAYS AS (to_tsvector('english', COALESCE(message, ''))) STORED;

然后为该列创建 GIN 索引：

CREATE INDEX idx_log_entries_msg_tsv_gin ON log_entries USING GIN (message_tsv);

查询过程：

SELECT * FROM log_entries 
WHERE message_tsv @@ plainto_tsquery('english', 'connection timeout');

plainto_tsquery('english', 'connection timeout') 将输入转换为 'connection' & 'timeout'（两个词素必须同时出现），然后 GIN 索引快速定位包含这两个词素的行。

4.3.2 中文分词的特殊处理

对于中文日志，english 分词无法正确切分词语。解决方案是安装 zhparser 扩展：

-- 创建中文搜索配置
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese 
    ADD MAPPING FOR n,v,a,i,e WITH simple;

-- 修改生成列，使用中文配置
ALTER TABLE log_entries 
    DROP COLUMN IF EXISTS message_tsv;
ALTER TABLE log_entries 
    ADD COLUMN message_tsv TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('chinese', COALESCE(message, ''))
    ) STORED;

如果日志中有中英文混合，可以创建两个 tsvector 列，查询时用 OR 连接：

WHERE message_tsv_en @@ plainto_tsquery('english', #{keyword})
   OR message_tsv_cn @@ plainto_tsquery('chinese', #{keyword})

4.4 通过 EXPLAIN 验证索引策略

让我们实际执行一个组合查询，验证优化器是否正确使用了我们设计的索引。

EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING, SUMMARY, FORMAT TEXT)
SELECT * FROM log_entries
WHERE log_time >= '2023-11-15 00:00:00+00'
  AND log_time < '2023-11-16 00:00:00+00'
  AND client_ip << '192.168.0.0/16'::inet
  AND attributes @> '{"userId": "U-999888"}'::jsonb
  AND message_tsv @@ plainto_tsquery('english', 'payment failed');

预期的执行计划分析:

Append  (actual time=1.234..85.678 rows=12 loops=1)
  ->  Subquery Scan on log_entries_2023_11  (actual time=1.234..85.678 rows=12 loops=1)
        ->  Bitmap Heap Scan on log_entries_2023_11  (...)
              Recheck Cond: ((log_time >= '2023-11-15'::timestamptz) AND ...)
              Filter: ((client_ip << '192.168.0.0/16'::inet) AND ...)
              Heap Blocks: exact=45
              Buffers: shared hit=120 read=15
              ->  BitmapAnd  (...)
                    ->  Bitmap Index Scan on idx_log_entries_time_brin  (...)
                          Index Cond: (log_time >= ... AND log_time < ...)
                    ->  Bitmap Index Scan on idx_log_entries_attr_gin  (...)
                          Index Cond: (attributes @> '{"userId":"U-999888"}'::jsonb)

解读:

1. Append 节点只包含 log_entries_2023_11 分区，证明分区裁剪成功。
2. BitmapAnd 合并了两个位图索引扫描的结果：BRIN 索引用于时间范围，GIN 索引用于 JSONB 包含。这意味着 PostgreSQL 同时利用了时间索引和 JSONB 索引，而不是先扫描一个再过滤另一个。
3. Heap Blocks: exact=45 说明只需要读取 45 个数据块就能找到结果，而不是扫描整个分区（可能有几十万个数据块）。
4. Buffers: shared hit=120 read=15 说明绝大部分数据已在缓存中，只有 15 个块需要从磁盘读取。

如果没有这些索引，这个查询将退化为对 log_entries_2023_11 分区的全表扫描（Seq Scan），扫描所有的数据块，性能可能下降 100~1000 倍。

---

5. 模拟生产慢查询与性能诊断闭环

理论是灰色的，而性能诊断之树常青。本章我们将运用 第7篇《查询优化与执行计划深度》 和 第12篇《性能调优》 的知识，模拟三个典型的生产慢查询场景，并演示如何利用 PostgreSQL 的诊断工具形成完整的调优闭环。

5.1 场景一：JSONB 操作符误用导致索引失效

5.1.1 问题发现

某天，监控系统告警：P99 查询延迟从 150ms 飙升至 3500ms。进一步查看 pg_stat_statements，发现某个查询的 mean_exec_time 异常增长：

-- 查询 pg_stat_statements 中的 Top 慢 SQL
SELECT 
    queryid,
    left(query, 100) AS query_preview,
    calls,
    mean_exec_time::numeric(10,2) AS avg_ms,
    max_exec_time::numeric(10,2) AS max_ms,
    stddev_exec_time::numeric(10,2) AS stddev_ms
FROM pg_stat_statements
WHERE query ILIKE '%attributes%'
ORDER BY mean_exec_time DESC
LIMIT 5;

输出：

 queryid  | query_preview                                                | calls | avg_ms  | max_ms  | stddev_ms
----------+--------------------------------------------------------------+-------+---------+---------+-----------
 2847593  | SELECT * FROM log_entries WHERE ... AND attributes->>'user.. | 8450  | 3125.45 | 8521.00 | 2100.50

5.1.2 问题复现与分析

我们复现该慢查询：

-- 错误查询
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM log_entries
WHERE log_time >= '2023-11-15 00:00:00+00'
  AND log_time < '2023-11-16 00:00:00+00'
  AND attributes->>'userId' = 'U-999888';

执行计划显示：

Seq Scan on log_entries_2023_11  (actual time=0.125..3245.890 rows=12 loops=1)
  Filter: ((attributes ->> 'userId'::text) = 'U-999888'::text)
  Rows Removed by Filter: 2458321
  Buffers: shared hit=12500 read=23680

关键发现:

• Seq Scan（全表顺序扫描）！不是 Index Scan。
• Rows Removed by Filter: 2458321：扫描了 245 万行，只返回 12 行，效率极低。
• 执行时间 3245ms，与告警吻合。

5.1.3 根本原因分析

为什么 GIN 索引没有被使用？因为 ->> 是一个文本提取操作符，它返回一个 text 类型。而我们创建的 GIN 索引 (jsonb_path_ops) 是为 @> 操作符设计的。查询优化器无法将 ->> 与 GIN 索引匹配，因此选择了全表扫描。

知识关联：详见本系列第9篇《JSON/JSONB与全文搜索》和第5篇《索引深度》中关于 GIN 索引支持的操作符列表。

5.1.4 解决方案与验证

方案一（推荐）：使用 @> 操作符。

-- 正确查询
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM log_entries
WHERE log_time >= '2023-11-15 00:00:00+00'
  AND log_time < '2023-11-16 00:00:00+00'
  AND attributes @> '{"userId": "U-999888"}'::jsonb;

优化后的执行计划：

Bitmap Heap Scan on log_entries_2023_11  (actual time=0.845..12.345 rows=12 loops=1)
  Recheck Cond: (attributes @> '{"userId": "U-999888"}'::jsonb)
  Filter: (log_time >= '2023-11-15'::timestamptz AND log_time < '2023-11-16'::timestamptz)
  Heap Blocks: exact=8
  Buffers: shared hit=45
  ->  Bitmap Index Scan on idx_log_entries_attr_gin  (actual time=0.785..0.785 rows=12 loops=1)
        Index Cond: (attributes @> '{"userId": "U-999888"}'::jsonb)

效果：执行时间从 3245ms 降至 12ms，提升约 270 倍。扫描的块数从 36180 降至 45，几乎全部命中缓存。

方案二（备选）：如果确实需要 ->> 操作（例如 attributes->>'userId' LIKE 'U-%'），可以创建表达式索引：

CREATE INDEX idx_attr_userid ON log_entries ((attributes->>'userId'));

但这个索引只能加速 userId 这一个键的查询，不具有通用性。因此我们的首选方案仍是使用 @> 操作符配合 GIN 索引。

5.2 场景二：统计信息过时导致错误执行计划

5.2.1 问题发现

在一次大规模数据导入后，原本运行在 50ms 以内的复杂查询突然变慢至 2 秒以上。通过 pg_stat_statements 和 auto_explain 抓取到的执行计划显示，优化器选择了 Nested Loop 连接，但实际执行中驱动表返回了远超预估值的数据。

5.2.2 问题复现与分析

-- 查询某服务最近 1 天内的 ERROR 日志和其 attributes 中的特定信息
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT 
    e.log_time,
    e.service_name,
    e.message,
    e.attributes->>'userId' AS user_id
FROM log_entries e
JOIN LATERAL (
    SELECT 1
    FROM jsonb_each_text(e.attributes) AS kv
    WHERE kv.key = 'orderId' AND kv.value LIKE 'ORD-2023%'
) AS ord ON true
WHERE e.log_time >= now() - interval '1 day'
  AND e.level = 'ERROR';

执行计划显示：

Nested Loop  (actual time=0.123..2345.678 rows=5 loops=1)
  ->  Seq Scan on log_entries_2023_11 e  (actual time=0.100..2000.500 rows=450000 loops=1)
        Filter: (level = 'ERROR' AND log_time >= ...)
        Rows Removed by Filter: 200000
  ->  Function Scan on jsonb_each_text kv  (actual time=0.001..0.050 rows=0 loops=450000)
        Filter: ((key = 'orderId'::text) AND (value ~~ 'ORD-2023%'::text))
        Rows Removed by Filter: 5
Planning Time: 0.500 ms
Execution Time: 2345.678 ms

关键发现：

• 优化器估计 log_entries 返回 1000 行，实际返回 450,000 行（estimated rows 与 actual rows 差异巨大）。
• 因为这个错误的估计，优化器选择了 Nested Loop 连接（适合小表驱动），而不是 Hash Join。
• 实际扫描了 450,000 次 jsonb_each_text，导致执行时间飙升。

5.2.3 根本原因分析

优化器依赖 pg_stats 中的统计信息来估算行数。在大规模数据导入后，autovacuum 可能尚未触发或尚未更新统计信息，导致 pg_stats 中的数据是旧的。查询 pg_stats 验证：

SELECT 
    schemaname, tablename, attname,
    n_distinct, most_common_vals, most_common_freqs,
    histogram_bounds,
    last_analyze
FROM pg_stats
WHERE tablename = 'log_entries' AND attname IN ('level', 'log_time');

发现 last_analyze 是 3 天前的，most_common_vals 和 histogram_bounds 不能反映当前的数据分布。

5.2.4 解决方案与验证

-- 手动更新统计信息
ANALYZE log_entries;

再次执行查询，优化后的计划：

Hash Join  (actual time=120.450..350.200 rows=5 loops=1)
  Hash Cond: ...
  ->  Bitmap Heap Scan on log_entries_2023_11 e  (actual time=15.300..200.100 rows=450000 loops=1)
        Recheck Cond: (level = 'ERROR')
        Filter: (log_time >= ...)
  ->  Hash  (actual time=100.200..100.200 rows=5000 loops=1)
        Buckets: 8192  Batches: 1  Memory Usage: 256kB
        ->  Function Scan on jsonb_each_text
Planning Time: 2.500 ms
Execution Time: 350.200 ms

效果：执行时间从 2345ms 降至 350ms。优化器在获得准确的统计信息后，正确选择了 Hash Join 连接。

预防措施：

• 对于大规模数据变更后，应及时执行 ANALYZE。
• 可以调整 autovacuum_analyze_scale_factor 参数，降低自动 ANALYZE 的触发阈值（默认 10% 数据变更才触发，对于频繁变更的大表可能过低）：

  ALTER TABLE log_entries SET (autovacuum_analyze_scale_factor = 0.01);
  

• 对于特别重要的查询，可以在批处理作业结束后手动 VACUUM ANALYZE。

5.3 场景三：work_mem 不足引发磁盘排序

5.3.1 问题发现

一个按服务统计错误数量并排序的聚合查询响应时间极不稳定，有时 200ms，有时超过 5 秒。同时数据库所在服务器的磁盘 IO 出现高峰。

5.3.2 问题复现与分析

-- 统计过去 24 小时各服务的 ERROR 数量，按数量降序
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT 
    service_name,
    count(*) AS error_count
FROM log_entries
WHERE log_time >= now() - interval '24 hours'
  AND level = 'ERROR'
GROUP BY service_name
ORDER BY error_count DESC;

执行计划关键部分：

Sort  (actual time=3456.789..4500.123 rows=500 loops=1)
  Sort Key: (count(*)) DESC
  Sort Method: external merge  Disk: 98536kB
  Buffers: shared hit=12500 read=35000, temp read=12345 written=12345
  ->  HashAggregate  (actual time=1200.500..2500.300 rows=500 loops=1)
        Group Key: service_name
        Batches: 5  Memory Usage: 48MB  Disk Usage: 250MB
        ->  Seq Scan on log_entries_2023_11  (actual time=0.100..800.200 rows=5500000 loops=1)
              Filter: (level = 'ERROR' AND log_time >= ...)
              Rows Removed by Filter: 12000000

关键发现：

• Sort Method: external merge Disk：排序操作使用了磁盘！
• HashAggregate 的 Disk Usage: 250MB：分组聚合也溢写到了磁盘。
• temp read=12345 written=12345：产生了大量的临时文件读写。

5.3.3 根本原因分析

聚合和排序操作需要内存。当数据量超过 work_mem 参数限制时，PostgreSQL 会将数据写入磁盘临时文件进行外部归并排序（external merge sort）。磁盘排序的速度远慢于内存排序（通常慢 10~100 倍），并且会产生大量的磁盘 IO。

当前会话的 work_mem 是默认的 4MB（在 postgresql.conf 中未设置，采用了默认值）。

5.3.4 解决方案与验证

方案一：在会话级别增大 work_mem。

-- 为此查询临时分配足够的内存
SET LOCAL work_mem = '512MB';

再次执行，优化后的计划：

Sort  (actual time=250.123..320.456 rows=500 loops=1)
  Sort Key: (count(*)) DESC
  Sort Method: quicksort  Memory: 98536kB
  ->  HashAggregate  (actual time=120.500..200.100 rows=500 loops=1)
        Group Key: service_name
        Batches: 1  Memory Usage: 50MB
        ->  Parallel Seq Scan  (actual time=0.050..80.200 rows=5500000 loops=1)

效果：

• 排序从磁盘变为内存（quicksort Memory: 98536kB）
• 聚合也全部在内存中完成（Batches: 1）
• 执行时间从 4500ms 降至 320ms，提升约 14 倍。
• 磁盘的临时文件 IO 完全消失。

注意事项：work_mem 是每个操作可用的内存量，而非整个查询。一个复杂查询可能包含多个排序和哈希操作，每个都可能申请 work_mem 内存。因此，全局提升 work_mem 必须谨慎，需要根据并发连接数和物理内存计算安全上限。通常，我们只在特定查询的会话级别调整 work_mem。

方案二（辅助）：创建合适的索引，避免全表扫描和大规模聚合。

-- 在 (level, log_time) 上建立复合索引，可加速过滤
CREATE INDEX idx_level_time ON log_entries (level, log_time);

5.4 DBA 诊断工具箱详解

5.4.1 pg_stat_statements

此扩展是定位慢 SQL 的“第一站”。安装后，它会持续追踪所有 SQL 语句的调用次数、执行时间、影响行数等。

核心查询：

-- Top 10 最耗时查询（按总时间）
SELECT 
    queryid,
    left(query, 80) AS query_preview,
    calls,
    total_exec_time::numeric(10,2) AS total_ms,
    mean_exec_time::numeric(10,2) AS avg_ms,
    max_exec_time::numeric(10,2) AS max_ms,
    min_exec_time::numeric(10,2) AS min_ms,
    stddev_exec_time::numeric(10,2) AS stddev_ms,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

指标解读：

• stddev_exec_time 高：表示该查询在不同参数下性能表现不稳定，可能是执行计划选择问题。
• cache_hit_ratio 低：说明查询需要大量从磁盘读取数据，可能是 shared_buffers 不足。
• rows 与 calls 的比值：如果返回的行数极多，可能没有合适的 LIMIT 或过滤条件。

5.4.2 auto_explain

auto_explain 是“生产环境的安全监控探头”。它可以在不修改应用代码的情况下，自动将超过阈值的慢查询的执行计划记录到 PostgreSQL 日志中。

配置（在 postgresql.conf 中）：

-- 启用 auto_explain（需要重启）
shared_preload_libraries = 'auto_explain'

-- auto_explain 配置
auto_explain.log_min_duration = '200ms'  -- 记录超过 200ms 的查询
auto_explain.log_analyze = on            -- 同时记录实际执行时间
auto_explain.log_buffers = on            -- 同时记录缓冲区使用
auto_explain.log_timing = on             -- 记录详细计时
auto_explain.log_level = 'NOTICE'        -- 输出级别
auto_explain.log_nested_statements = on  -- 记录嵌套语句

日志示例：

2023-11-15 14:35:22 UTC [23456]: [15-1] user=logapp,db=logdb,app=log-platform LOG:  duration: 3210.456 ms  plan:
Query Text: SELECT * FROM log_entries WHERE attributes->>'userId' = 'U-999888';
Seq Scan on log_entries_2023_11  (cost=0.00..45000.00 rows=12 width=520) (actual time=0.125..3210.456 rows=12 loops=1)
  Filter: ((attributes ->> 'userId'::text) = 'U-999888'::text)
  Rows Removed by Filter: 2458321
  Buffers: shared hit=12500 read=23680

使用最佳实践：

• 设置 log_min_duration 为一个合适的阈值（如 100ms~500ms），避免记录过多。
• 在生产环境不要开启 log_analyze（会实际执行并增加开销），但可以在测试环境或紧急排障时临时开启。
• 结合 pg_stat_statements，先用它找到慢查询的 queryid，然后在日志中 grep 该 ID 的详细计划。

5.4.3 慢查询定位与优化决策树

flowchart TD
    Start(["发现慢查询（告警/用户投诉）"]) --> P1["查询 pg_stat_activity<br/>看是否有长时间运行的查询"]
    P1 --> Act1{"有长时间运行的查询?"}
    Act1 -- "有" --> Kill["考虑 pg_terminate_backend<br/>并分析其原因"]
    Act1 -- "无或已解决" --> P2["查询 pg_stat_statements<br/>获取 Top 5 慢 SQL"]
    P2 --> P3["获取查询文本和 queryid"]
    P3 --> P4["开启 auto_explain<br/>等待查询再次执行"]
    P4 --> P5["从日志获取详细 EXPLAIN ANALYZE"]
    P5 --> Anal["分析执行计划"]

    Anal --> A1{"计划中出现 Seq Scan?"}
    A1 -- "是, 且不符合预期" --> F1["创建或修正索引"]
    A1 -- "否" --> A2{"预估行数与实际行数差异大?"}
    A2 -- "是" --> F2["执行 ANALYZE 更新统计信息"]
    A2 -- "否" --> A3{"排序或哈希使用磁盘?"}
    A3 -- "是" --> F3["调整 work_mem"]
    A3 -- "否" --> A4{"连接方法错误<br/>(比如 Nested Loop 应改为 Hash Join)?"}
    A4 -- "是" --> F4["调整优化器参数或重写 SQL"]
    A4 -- "否" --> A5{"其他原因?<br/>如锁竞争、IO 瓶颈"}
    A5 -- "是" --> F5["查看 pg_locks, pg_stat_user_tables"]
    
    F1 & F2 & F3 & F4 & F5 --> Verify["再次执行查询, 验证性能"]
    Verify --> Monitor["持续监控, 确认问题解决"]
    Monitor --> End(["输出性能诊断报告"])

    classDef startEnd fill:#D8C8C0,stroke:#9B8A82,stroke-width:2px,color:#333;
    classDef process fill:#C4D1D7,stroke:#8097A5,stroke-width:1.5px,color:#333;
    classDef decision fill:#E0CFC0,stroke:#B7A293,stroke-width:2px,color:#333;
    classDef action fill:#C2CDC0,stroke:#83957F,stroke-width:1.5px,color:#333;
    classDef verify fill:#CDD5C6,stroke:#8F9E87,stroke-width:2px,color:#333;

    class Start,End startEnd;
    class P1,P2,P3,P4,P5,Anal process;
    class Act1,A1,A2,A3,A4,A5 decision;
    class F1,F2,F3,F4,F5,Kill action;
    class Verify,Monitor verify;

图例说明：

• 总览：该决策树提供了一个标准化的慢查询诊断流程，从发现问题到定位根因，再到执行解决方案和验证，形成闭环。
• 分支详解：
  • 通过 pg_stat_activity 第一时间发现“正在运行中”的慢查询，可用于紧急止血（终止查询）。
  • 之后主要依赖 pg_stat_statements 和 auto_explain 获取执行计划。
  • 分析执行计划时，依次检查：Seq Scan（最常见的性能杀手）→ 统计信息过时 → 内存不足导致磁盘溢出 → 连接方法选择错误 → 其他资源竞争问题。
• 关键结论：性能诊断不是玄学，而是有章可循的科学方法。每次诊断都应以证据（EXPLAIN输出、系统视图数据）为依据，避免凭直觉修改配置。

---

6. 全链路压力测试与监控

理论设计和实验室测试通过后，必须在接近真实的生产负载下验证系统的处理能力。本章将详细展示压测环境搭建、JMeter 脚本设计、压测过程中发现的两个真实瓶颈及其解决过程，最终输出一份完整的性能测试报告。

6.1 测试环境与数据准备

6.1.1 硬件环境

组件	配置
应用服务器	8 vCPU, 16GB RAM, SSD 50GB（云虚拟机）
数据库服务器	16 vCPU, 64GB RAM, 本地 NVMe SSD 2TB
JMeter 压力机	4 vCPU, 8GB RAM，与目标服务器同可用区
操作系统	Ubuntu 22.04 LTS
Java 版本	OpenJDK 17.0.9
PostgreSQL	16.1（自编译，包含 zhparser）
Spring Boot	3.2.1

6.1.2 数据准备

我们使用脚本生成了 12 个月的模拟日志数据，总计约 8 亿条，数据量约 700GB。数据均匀分布在 12 个月的分区中。

-- 数据生成脚本（简化版，使用 generate_series 和 random）
INSERT INTO log_entries (log_time, level, service_name, client_ip, message, attributes, error_tags)
SELECT
    timestamp '2023-10-01 00:00:00+00' 
        + random() * interval '90 days' AS log_time,
    CASE WHEN random() < 0.01 THEN 'FATAL'
         WHEN random() < 0.05 THEN 'ERROR'
         WHEN random() < 0.15 THEN 'WARN'
         WHEN random() < 0.30 THEN 'DEBUG'
         ELSE 'INFO' END AS level,
    ('service_' || (random() * 50)::int) AS service_name,
    (CASE WHEN random() < 0.7 THEN '192.168.' || (random() * 255)::int || '.' || (random() * 255)::int
          ELSE '10.' || (random() * 255)::int || '.' || (random() * 255)::int || '.' || (random() * 255)::int
     END)::inet AS client_ip,
    CASE WHEN random() < 0.3 THEN 'Connection timeout while connecting to database'
         WHEN random() < 0.2 THEN 'Payment处理失败: orderId=ORD-' || (random() * 1000000)::int
         WHEN random() < 0.1 THEN 'GC pause took ' || (random() * 5000)::int || 'ms'
         ELSE 'Normal operation log, traceId=abc-def-' || (random() * 9999)::int
    END AS message,
    jsonb_build_object(
        'userId', 'U-' || (random() * 50000)::int,
        'orderId', 'ORD-' || (random() * 100000)::int,
        'traceId', 'trace-' || (random() * 999999)::int,
        'region', CASE WHEN random() < 0.5 THEN 'CN-EAST' ELSE 'CN-NORTH' END,
        'amount', random() * 1000
    ) AS attributes,
    CASE WHEN random() < 0.1 THEN ARRAY[500, 502, (random()*10+400)::int]
         ELSE NULL END AS error_tags
FROM generate_series(1, 800000000);  -- 注意：实际生成应分批，避免单事务过大

数据特点：

• 日志级别分布符合真实场景：INFO 占 49%，DEBUG 占 30%，WARN 占 15%，ERROR 占 5%，FATAL 占 1%。
• attributes 包含常见的业务属性，每个文档大小约 100~200 字节。
• message 模拟了真实的错误信息和正常日志，其中 30% 包含“Connection timeout”关键词。

6.2 JMeter 压测方案设计

6.2.1 压测目标

指标	目标值	说明
写入吞吐量	≥ 50,000 events/s	满足峰值写入需求
查询 P99 延迟	≤ 200ms	绝大多数查询在 200ms 内完成
查询 P50 延迟	≤ 50ms	中位数延迟
写入错误率	< 0.1%	几乎零错误
数据库 CPU	< 70%	留有一定余量
缓存命中率	> 99%	绝大部分数据从内存读取

6.2.2 JMeter 脚本设计

我们创建了两个 JMeter 测试计划：一个用于写入压测，一个用于混合读写压测。

写入压测脚本（log-write-test.jmx）的核心线程组配置：

<!-- JMeter 5.6 线程组配置 -->
<ThreadGroup guiclass="ThreadGroupGui" testname="Log Write Test" enabled="true">
  <elementProp name="ThreadGroup.main_controller" 
               elementType="LoopController">
    <boolProp name="LoopController.continue_forever">false</boolProp>
    <intProp name="LoopController.loops">10000</intProp>
  </elementProp>
  <stringProp name="ThreadGroup.num_threads">50</stringProp>
  <stringProp name="ThreadGroup.ramp_time">10</stringProp>
  <boolProp name="ThreadGroup.scheduler">false</boolProp>
</ThreadGroup>

HTTP Request 配置：

• 请求方法：POST
• 路径：/api/logs/batch
• 请求体：JSON Body（每批 500 条日志）

请求体生成（通过 JSR223 PreProcessor + Groovy 脚本）：

import groovy.json.JsonBuilder

def logs = []
def random = new Random()
// 生成 500 条日志的批次
for (int i = 0; i < 500; i++) {
    def log = [
        logTime:    java.time.OffsetDateTime.now().minusSeconds(random.nextInt(3600)).toString(),
        level:      ['INFO','WARN','ERROR'].get(random.nextInt(3)),
        serviceName: "service_${random.nextInt(50)}",
        clientIp:   "192.168.${random.nextInt(255)}.${random.nextInt(255)}",
        message:    "Test message ${random.nextInt(10000)} - connection timeout",
        attributes: [
            userId:   "U-${random.nextInt(50000)}",
            orderId:  "ORD-${random.nextInt(100000)}",
            traceId:  "trace-${random.nextInt(999999)}",
            region:   random.nextBoolean() ? "CN-EAST" : "CN-NORTH"
        ]
    ]
    logs.add(log)
}
def jsonBuilder = new JsonBuilder(logs)
vars.put("batchBody", jsonBuilder.toPrettyString())

查询压测脚本（log-query-test.jmx）的关键配置：

查询场景分为 5 种，在 JMeter 中通过 CSV Data Set Config 随机选取：

1. 纯时间范围查询（占比 40%）
2. 时间 + 级别 + 服务名（占比 20%）
3. 时间 + JSONB 属性（占比 20%）
4. 时间 + IP 子网（占比 10%）
5. 时间 + 全文搜索（占比 10%）

每个查询的请求体构建通过 Groovy 脚本动态生成，模拟真实查询参数。

6.2.3 压测执行流程

1. 预热阶段（10 分钟）：以较低的并发（10 线程）运行，让 PostgreSQL 的缓存和优化器统计信息稳定下来。
2. 梯度加压（30 分钟）：每 5 分钟增加 10 个并发线程，观察系统指标变化。
3. 全量加压（60 分钟）：写入 50 线程 + 查询 30 线程，保持稳定负载。
4. 稳定性观察（30 分钟）：保持负载，观察是否有内存泄漏、连接池耗尽等稳定性问题。

6.3 压测实录：写入瓶颈发现与突破

6.3.1 初期问题：写入 TPS 卡在 20,000

在全量加压初期，写入 TPS 稳定在 20,000 events/s 左右，无法达到目标 50,000。CPU 使用率不高（30%），磁盘 IO 也远未达到 NVMe 的极限。

诊断过程：

1. 检查 pg_stat_activity，发现大量连接处于 WALWriteLock 等待状态：

SELECT 
    wait_event_type, wait_event, count(*)
FROM pg_stat_activity 
WHERE wait_event IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC;

输出：

 wait_event_type | wait_event     | count
-----------------+----------------+-------
 LWLock          | WALWriteLock   |    25
 IO              | DataFileRead   |     5

2. 分析原因：WALWriteLock 是 WAL 写入锁。默认情况下，synchronous_commit = on，意味着每个事务提交时必须等待 WAL 刷盘完成。对于我们的批量写入（每批作为一个事务），高并发下产生了大量的 WAL 刷盘争用。

3. 解决方案：调整 synchronous_commit 参数。

-- 对于日志场景，允许一定的数据丢失（通常可接受）
ALTER SYSTEM SET synchronous_commit = 'remote_write';
SELECT pg_reload_conf();

remote_write 表示只要 WAL 记录被写入到操作系统缓冲区（但可能还未刷盘），即可返回提交成功。这大幅减少了 fsync 次数，提高了并发写入能力。

效果：调整后，写入 TPS 从 20,000 飙升至 65,000 events/s，达到了目标。

6.3.2 进一步优化：批量大小与连接池调整

随着 TPS 提升，我们发现在某些时刻会出现“批量插入失败”的错误。检查后发现是 JDBC 发送的 SQL 文本过大（每批 500 条时，总字符串长度超过 10MB），偶尔超过 JDBC 缓冲区限制。

优化措施：

• 将每批大小从 500 调整到 200 条，SQL 文本控制在 2~3MB，避免过大。
• 同时将 HikariCP 连接池从 50 提升到 80，允许更多并发批量写入。

最终写入 TPS 稳定在 58,000 events/s，符合目标。

6.4 压测实录：查询抖动定位与根除

6.4.1 问题现象

在混合压测阶段，查询 P99 延迟出现了周期性的抖动，从稳定的 150ms 飙升至 1.2 秒以上。通过 Grafana 监控面板，发现抖动与数据库服务器的 IO 峰值重合。

6.4.2 诊断过程

1. 检查 pg_stat_statements，寻找在此期间耗时最高的查询：

SELECT queryid, left(query, 100), 
       mean_exec_time, max_exec_time, 
       shared_blks_read, shared_blks_hit
FROM pg_stat_statements
WHERE query ILIKE '%log_entries%'
ORDER BY max_exec_time DESC
LIMIT 3;

2. 使用 auto_explain 抓取到慢查询的完整执行计划，发现优化器在某些参数下错误地选择了 Nested Loop 连接，且驱动表返回了巨大的结果集（200 万行），导致内表被循环扫描数百万次。

3. 进一步分析：查看 pg_stats 中相关列的统计信息，发现 service_name 的 n_distinct 值被低估（估计 50 个实际有 200 多个），导致优化器错误估算 JOIN 结果集大小。

6.4.3 解决方案

1. 立即执行 ANALYZE，更新统计信息：

ANALYZE log_entries;

2. 调整默认统计信息采样目标（使其收集更精确的数据分布）：

ALTER TABLE log_entries 
    ALTER COLUMN service_name SET STATISTICS 500;

默认是 100，增加到 500 意味着 ANALYZE 时会采样更多行，得到更准确的 n_distinct 和 most_common_vals。

3. （可选）在会话级调整优化器参数，作为临时解决方案：

SET LOCAL enable_nestloop = off;

这会强制优化器避免此连接算法，转而选择 Hash Join 或 Merge Join。

效果：查询 P99 延迟从 1.2 秒降至 180ms，抖动消失。

6.5 性能测试报告与容量规划

6.5.1 最终性能指标

指标	目标值	实际值	达标
写入吞吐量	≥ 50,000 events/s	58,000 events/s	✅
写入 P99 延迟	-	45ms	-
查询 QPS	≥ 500	780 qps	✅
查询 P50 延迟	≤ 50ms	32ms	✅
查询 P99 延迟	≤ 200ms	185ms	✅
写入错误率	< 0.1%	0.02%	✅
数据库 CPU	< 70%	58% avg	✅
磁盘 IO 利用率	< 80%	62% avg	✅
缓存命中率	> 99%	99.8%	✅

6.5.2 索引与存储空间报告

对象	大小	备注
主表 log_entries（总）	685 GB	8 亿条数据
BRIN 索引（log_time）	280 MB	仅为 B-Tree 的 1/200
GIN 索引（attributes）	48 GB	jsonb_path_ops，比默认小 35%
GIN 索引（message_tsv）	32 GB	全文搜索
B-Tree 索引（service_name, time）	28 GB	按服务查询
总磁盘占用	~795 GB	索引约占 16%

6.5.3 容量规划建议

基于当前压测数据，系统可支持：

• 每日新增日志量 ≤ 4.5TB（按 58,000 events/s × 86400 秒，平均 800 字节/事件）
• 保留 3 个月的热数据（在线查询），约 400TB，建议使用 4TB 级别 NVMe SSD × 12（RAID 10）扩展存储。
• 归档策略：每月自动 DETACH 旧分区，通过 pg_dump 导出为压缩文件存储于对象存储（S3/OSS），可节省 80% 成本。

---

7. 面试高频专题：从实战回溯原理

（本部分严格分离正文，以 QA 形式撰写，共 10 题）

7.1 10 道核心面试题与深度解析

1. 在这个日志分析平台中，为什么选择 JSONB 而不是 TEXT 类型来存储动态属性？

• 标准回答：JSONB 是解析后的二进制存储格式，支持 GIN 索引，可以通过 @> 操作符进行高效的包含查询，而 TEXT 只能进行低效的字符串匹配。此外，JSONB 存储时会对键和值进行压缩优化，空间占用更小，且在查询时无需重复解析。
• 多角度追问 (≥3)：
  • 追问 1：JSONB 写入时需要进行解析和转换，会不会比 TEXT 慢很多？生产环境中如何权衡？
    • 回答：是的，JSONB 的写入比 TEXT 慢 10%~15% 左右，因为需要解析 JSON 并转换为二进制。但在日志分析场景中，写入性能的瓶颈通常在于网络和 WAL 刷盘，额外的 JSON 解析可以通过批量插入来分摊。而查询性能的提升（往往 100 倍以上）远远超过了写入的微小损失，是非常值得的。
  • 追问 2：如果某些 JSON 键的查询非常频繁（如 userId），除了 GIN 索引，还有什么优化手段？
    • 回答：可以为该键的特定路径创建表达式索引（B-Tree 索引）。例如，CREATE INDEX idx_userid ON log_entries ((attributes->>'userId'));。这样对于等值查询 WHERE attributes->>'userId' = 'U-123'，优化器可以使用 B-Tree 索引，比 GIN 索引更小、更精确。
  • 追问 3：JSONB 支持哪些索引？jsonb_ops 和 jsonb_path_ops 有什么区别？
    • 回答：支持 GIN 索引，默认为 jsonb_ops，它索引每个键和值，支持 @>、?、?|、?& 等操作符。jsonb_path_ops 只索引值，体积更小，但仅支持 @> 操作符。在只需要包含查询的场景下，jsonb_path_ops 是更优选择。
• 加分回答：可以提到 PostgreSQL 13+ 对 JSONB 的 gin_fuzzy_search_limit 参数，可以在超大结果集时限制 GIN 扫描的行数，避免一次查询占用过多资源。

2. 在 MyBatis 中，如何为 PostgreSQL 的 JSONB 类型编写一个优雅的 TypeHandler？

• 标准回答：继承 BaseTypeHandler<Map<String, Object>>（或自定义 POJO），在 setNonNullParameter 方法中，用 Jackson 将 Java 对象序列化为 JSON 字符串，封装进 PGobject 并设置类型为 "jsonb"，通过 PreparedStatement.setObject 设置。在 getNullableResult 中，从 ResultSet 取出字符串，反序列化回 Java 对象。最后在 Spring Boot 中通过 mybatis.type-handlers-package 自动注册。
• 多角度追问 (≥3)：
  • 追问 1：在 PGobject.setType 中设置 "json" 和 "jsonb" 有什么区别？
    • 回答：这是两个不同的数据类型。"json" 存储的是原始 JSON 文本（无索引优化），"jsonb" 是二进制 JSON。如果这里写错，GIN 索引将不会被使用，查询性能会急剧下降。JDBC 驱动会根据字符发送不同的类型 OID 给 PG。
  • 追问 2：ObjectMapper 为什么要声明为 static final？
    • 回答：ObjectMapper 是线程安全的，且初始化成本较高。声明为 static final 可以全局复用同一个实例，避免重复创建和配置，提高性能和降低 GC 压力。
  • 追问 3：如果 attributes 中的值有时是数字，有时是字符串，反序列化到 Map<String, Object> 时，Jackson 如何处理？
    • 回答：Jackson 会按照 JSON 规范将数字反序列化为 Integer/Long/Double 等。Map<String, Object> 的 Object 类型是动态的，使用前需要 instanceof 检查。如果希望统一为字符串，可以自定义 DeserializationFeature 配置。
• 加分回答：可以提一下 TypeReference 的使用，确保反序列化时泛型信息不丢失。例如 objectMapper.readValue(json, new TypeReference<Map<String, Object>>() {}。

3. 对于日志表的时间范围查询，为什么优先考虑 BRIN 索引而不是 B-Tree 索引？

• 标准回答：BRIN 索引利用日志数据物理存储顺序与时间高度一致的特性，通过只存储块范围的最小/最大值来极大缩减索引体积（通常是 B-Tree 的 1/100~1/1000）。虽然查询速度略低于 B-Tree，但极小的空间占用使其能完全缓存于内存，间接提升了整体系统缓存效率。在 TB 级时序数据上，B-Tree 的维护成本和空间代价往往难以接受。
• 多角度追问 (≥3)：
  • 追问 1：BRIN 在什么情况下会“失灵”或成为“陷阱”？
    • 回答：如果数据的物理顺序与索引列不相关（例如按 service_name 聚簇），BRIN 索引的每个块范围可能包含时间跨度极大的数据，导致无法有效跳过，所有查询都退化为全表扫描。此外，频繁的 UPDATE 可能导致行的 CTID 链变化，破坏物理顺序，也会削弱 BRIN 的效果。
  • 追问 2：pages_per_range 参数如何影响性能？你们的选择依据是什么？
    • 回答：值越小，索引条目越多，查询时扫描的无用块越少（更精确），但索引越大。反之亦然。我们根据单日日志数据量计算，选择 32，使得单小时查询大约扫描数百个块范围，在精度和空间之间取得平衡。
  • 追问 3：BRIN 索引和 B-Tree 索引可以结合使用吗？如何分工？
    • 回答：可以。通常的实践是：对较老的分区（冷数据）使用 BRIN 索引，节省空间；对当前活跃的分区（热数据）使用 B-Tree 索引，提供最佳的查询性能。这样既控制了总索引成本，又保证了热点数据的低延迟。
• 加分回答：PostgreSQL 14+ 支持 BRIN 索引的多范围（multi-range）类型，可以更方便地处理不连续的数据分布。另外，可以提及 BRIN 索引支持 CLUSTER 操作（虽然开销大），用于重建物理顺序，作为恢复性能的终极手段。

4. 如何利用 EXPLAIN ANALYZE 和 pg_stat_statements 定位一条生产慢 SQL 的根本原因？

• 标准回答：首先通过 pg_stat_statements 找到 total_exec_time 或 mean_exec_time 最高的查询，获得其 queryid。然后配置 auto_explain.log_min_duration，等待查询再次执行，从日志中获得带 (ANALYZE, BUFFERS) 的详细执行计划。分析计划时重点看：是否出现了非预期的 Seq Scan；estimated rows 和 actual rows 的差异；Sort 或 Hash 节点是否使用了磁盘；缓冲区命中率是否过低。最后根据发现的问题进行索引调优、更新统计信息或调整内存参数。
• 多角度追问 (≥3)：
  • 追问 1：EXPLAIN 中的 cost、rows、width 分别代表什么？你最关注哪个？
    • 回答：cost 是优化器的估算代价（第一个数字是返回第一行的代价，第二个是总代价）；rows 是估算的输出行数；width 是估算的平均行宽。做诊断时，最关注的是 actual time 和 rows 的对比，因为如果 actual rows 远超 estimated rows，说明统计信息有问题，优化器可能做出了错误决定。
  • 追问 2：pg_stat_statements 中 queryid 是什么？如何通过它找到具体的查询文本？
    • 回答：queryid 是 PostgreSQL 为每个规范化的 SQL 语句计算出来的哈希值。可以通过它关联 pg_stat_statements 和日志。查询文本可以通过 SELECT query FROM pg_stat_statements WHERE queryid = 123456 获得（但可能被截断），完整的查询文本通常需要去应用日志或 auto_explain 日志中获取。
  • 追问 3：如果 pg_stat_statements 中看到某个查询的 stddev_exec_time 极大，意味着什么？
    • 回答：意味着该查询在不同的参数下执行时间差异很大，可能是执行计划不稳定（例如参数嗅探问题），或者某些参数值对应的数据量特别大，导致偶尔走全表扫描。需要进一步分析这些参数值对应的具体查询计划。
• 加分回答：可以提到 PostgreSQL 14+ 的 compute_query_id 参数和 pg_stat_activity 中的 query_id 列，可以实时关联活跃查询和 pg_stat_statements。

5. 如果日志表的数据量非常大（如 10 亿级），在 Schema 设计上你会考虑哪些分区或归档策略？

• 标准回答：首先使用声明式分区，按月或按日对 log_time 进行范围分区。对于旧数据，可以定期 DETACH 分区，然后通过 pg_dump 备份并 DROP TABLE 释放空间。如果旧数据偶尔需要查询，可使用 postgres_fdw 外部表挂载归档数据库，实现联邦查询。分区管理可借助 pg_partman 自动创建未来分区和归档旧分区。
• 多角度追问 (≥3)：
  • 追问 1：按时间分区可能导致“写入热点”（所有写入集中在最新分区），怎么办？
    • 回答：热点写入是现代 SSD 可以承受的。如果成为瓶颈，可以使用哈希子分区（如对 service_name 进行哈希）来打散 IO，但会增加分区数量和管理复杂度。通常情况下，只要最新分区的索引是高效的，热点并非主要问题。
  • 追问 2：如果用户查询经常不提供时间范围，会发生什么？如何优化？
    • 回答：这会导致分区裁剪失效，数据库必须扫描所有分区，性能极差。应在应用层强制要求时间范围，或在查询时自动添加默认时间限制（如最近 7 天）。如果无法避免，可以考虑在全局创建其他高性能索引，但总体建议是引导使用时间条件。
  • 追问 3：归档时数据迁移有没有更平滑的方式？
    • 回答：可以使用 CREATE TABLE ... AS SELECT * FROM old_partition 导出，然后再 DROP TABLE。或者使用逻辑复制将实时数据流写入不同的存储系统。对于金融级别的要求，还可以考虑使用 pg_squeeze 或 pg_repack 在不锁表的情况下整理空间。
• 加分回答：讨论多级分区策略，例如一级按时间（月），二级按客户（tenant_id 哈希），实现租户数据隔离和高效查询。

6. 从 Java 开发者的角度，谈谈你对 pg_stat_statements 这个扩展价值的理解，以及它如何改变了你的性能诊断方式。

• 标准回答：作为 Java 开发者，过去定位数据库性能问题往往需要依赖 DBA 或慢查询日志，信息滞后且不直观。pg_stat_statements 提供了一个实时、全局视角的 SQL 性能榜单，直接显示每条 SQL 的调用次数、平均/最大/最小执行时间、缓冲命中率等关键指标。这让开发者能够像看应用 Profiler 一样看数据库，快速定位到是哪个 SQL 导致了性能瓶颈，而不必猜测可能的问题。它改变了从“被动等待告警”到“主动巡检优化”的工作方式。
• 追问 1：pg_stat_statements 中的 shared_blks_hit 和 shared_blks_read 反映了什么？
  • 回答：hit 是从 shared_buffers 缓存中读取的数据块数，read 是从磁盘读取的。hit/(hit+read) 就是缓存命中率。如果某个查询的 read 很高，说明大量数据未缓存，可能需要检查 SQL 是否扫描了不必要的数据，或考虑增大 shared_buffers。
• 追问 2：它和 slow_query_log（MySQL 概念）或者 log_statement 有什么本质不同？
  • 回答：slow_query_log 只记录超过阈值的查询，是被动的。pg_stat_statements 是全量聚合统计，不经过采样，能反映整体负载分布。同时它结构化存储，便于查询和聚合分析，而日志是非结构化的。
• 追问 3：如何重置统计信息？
  • 回答：SELECT pg_stat_statements_reset();。这在性能测试开始时非常有用，可以清零历史数据，专门观察压测期间的 SQL 表现。
• 加分回答：可提到 PostgreSQL 13+ 的 pg_stat_statements 支持计划统计（plan_time），可以区分计划和执行时间，帮助诊断是否由于计划缓存（plan cache）问题导致性能抖动。

7. 在进行批量日志写入时，你如何平衡写入性能和数据一致性的需求？

• 标准回答：对于日志场景，通常可以接受少量数据丢失（例如最后几秒的日志），因此倾向于优化写入性能。通过调整 synchronous_commit 为 remote_write 或 off，减少 WAL 刷盘的等待，大幅提升吞吐量。同时，使用批量 INSERT 和多行 VALUES，减少网络往返和事务开销。在应用层，通过 @Transactional 保证每个批次的原子性。对于严格不能丢失的日志（如金融交易日志），则保持 synchronous_commit = on，并通过更强的硬件（如带有 BBWC 的 RAID 卡）来弥补性能。
• 追问 1：synchronous_commit = off 的风险是什么？
  • 回答：如果数据库服务器在事务提交后、WAL 真正写入磁盘之前崩溃，这部分提交的事务数据会丢失。丢失的时间窗口取决于 wal_writer_delay 设置（默认 200ms）。对于大多数业务日志和监控数据，这种丢失是可接受的；但对于核心交易，则需要更强的一致性保证。
• 追问 2：除了 synchronous_commit，还有哪些参数影响写入性能？
  • 回答：wal_buffers（增大可减少 WALWrite 等待）、commit_delay 和 commit_siblings（延迟提交以合并刷盘）、full_page_writes（关闭可提升但增加数据损坏风险）。这些参数的调整都需要仔细权衡。
• 追问 3：应用层如何感知到数据丢失？
  • 回答：通常无法直接感知，因为提交已经返回成功。如果需要确保，可以在应用层使用幂等 ID（如日志序列号），重启后回查确认最后写入的 ID，并重放可能丢失的部分。
• 加分回答：可以介绍 PostgreSQL 的同步流复制（synchronous_standby_names），将数据同步到至少一个备库后再返回提交，这样即使主库崩溃，备库仍有数据，兼顾了性能和可靠性。

8. 当 EXPLAIN ANALYZE 显示某个查询的 actual time 远大于 estimated time，可能会是什么原因，如何排查？

• 标准回答：最常见的原因是统计信息过时，导致优化器错误估计返回行数，进而选择了非最优的执行计划。其次可能是 work_mem 不足导致排序或哈希操作溢出到磁盘，实际 IO 开销远超预估。再者，锁等待或并发竞争（如 IO 瓶颈）在计划中无法体现。排查步骤：首先检查 pg_stats 确认统计信息是否最新；然后查看执行计划中的 Sort Method: external merge Disk 或 Batchn 等磁盘溢出标记；最后结合 pg_stat_activity 看是否有锁等待。
• 追问 1：如果 actual time 远大于 estimated time，但估算行数准确，可能是什么原因？
  • 回答：可能是执行期间的资源争用（CPU、IO 或锁）。例如，另一个事务持有行级锁，导致当前查询等待。或者磁盘性能波动。这时需要结合 wait_event 信息判断。
• 追问 2：EXPLAIN 中的 cost 是固定的吗？与实际时间有线性关系吗？
  • 回答：cost 是优化器内部的代价单位，通常与磁盘页面读取相关（seq_page_cost=1.0, random_page_cost=4.0）。它与实际时间不是简单的线性关系，因为实际时间受硬件、缓存、并发等影响。cost 主要用于对比不同执行计划，而非预测绝对运行时间。
• 追问 3：如何让优化器更好地估算 JSONB 列的选择率？
  • 回答：对于 JSONB 列，优化器默认估算可能不准确。可以为 JSONB 列的特定键创建统计信息：CREATE STATISTICS s_attr_userid (dependencies) ON attributes->>'userId' FROM log_entries; 然后 ANALYZE，优化器就能利用这些统计信息做出更准确的判断。
• 加分回答：介绍 auto_explain.sample_rate 参数，可以在高负载下只对部分查询记录计划，降低对生产的影响。

9. 你如何看待将 BRIN 索引用于时间序列表的优劣？它在什么场景下会成为“银弹”，什么场景下会是“陷阱”？

• 标准回答：BRIN 是时序数据的“银弹”：当数据物理存储顺序与时间列严格一致，且查询主要是范围扫描时，它以极少空间实现极好的查询性能。但在数据频繁更新、物理顺序混乱，或者查询主要是点查（精确时间）时，BRIN 会变成“陷阱”，因为它的精度不足以跳过大量块，导致扫描过多甚至全表扫描。它不能替代 B-Tree，而是一种互补。
• 追问 1：如何监控 BRIN 索引的效率？如何知道它是否跳过了足够多的块？
  • 回答：通过 EXPLAIN (ANALYZE, BUFFERS) 查看 Heap Blocks: exact 和 lossy 的数量。如果 lossy 很高，说明 BRIN 的判断不够精确，正在检查大量无用的行，可能需要减小 pages_per_range。
• 追问 2：如果表有频繁的 UPDATE，物理顺序会怎样？会影响 BRIN 吗？
  • 回答：如果 UPDATE 导致行膨胀，新版本可能会写到其他页面，破坏原有的时间顺序，导致 BRIN 的摘要信息不再准确。定期 VACUUM FULL 或 CLUSTER 可以恢复物理顺序，但代价很大。
• 追问 3：BRIN 索引是否支持多列？能否用于 (log_time, service_name)？
  • 回答：支持多列。BRIN (log_time, service_name) 会为每个列分别创建摘要，但在同一个索引中。查询 WHERE log_time ... AND service_name = 'X' 可能会用到，但效果不如 B-Tree 组合索引精确。
• 加分回答：提到 PostgreSQL 15 引入的 BRIN 索引内存优化和并行扫描能力，使得 BRIN 在大规模分析查询中表现更佳。

10. （系统设计题）如果该日志平台需要升级为支持多租户的 SaaS 服务，你会如何利用 PG 的 ROW LEVEL SECURITY (RLS) 特性来设计数据隔离方案？

• 标准回答：首先在 log_entries 表增加 tenant_id 列。然后开启 RLS：ALTER TABLE log_entries ENABLE ROW LEVEL SECURITY;。为每个租户创建策略：CREATE POLICY tenant_isolation ON log_entries USING (tenant_id = current_setting('app.current_tenant_id')::integer);。应用层在连接建立后，通过 SET LOCAL app.current_tenant_id = '123' 设定当前租户上下文。这样，所有该连接的查询都会自动带上 WHERE tenant_id = 123 条件，实现透明、强制的数据隔离，即使开发人员忘记在 SQL 中加租户条件，也不会泄露数据。
• 追问 1：RLS 的性能开销如何？会影响索引使用吗？
  • 回答：RLS 本质上是在查询计划中插入一个额外的过滤条件，如果 tenant_id 上有索引，优化器会正常使用。性能开销很低，主要是策略检查的开销。但多个策略可能增加计划时间。建议对 tenant_id 建立索引。
• 追问 2：如果某些系统级查询需要绕过 RLS 怎么办？
  • 回答：可以创建 BYPASSRLS 属性：ALTER TABLE log_entries FORCE ROW LEVEL SECURITY; 然后为 DBA 角色设置 BYPASSRLS 属性：ALTER ROLE dba BYPASSRLS;。
• 追问 3：如何审计 RLS 策略是否正常工作？
  • 回答：可以通过 EXPLAIN 查看查询计划中是否包含 Filter: (tenant_id = ...) 条件。也可以查询 pg_policies 视图检查策略定义。
• 加分回答：讨论 RLS 与分区表的结合，使用 tenant_id 做二级分区，不仅从应用层，更能从物理上隔离数据，并利用分区裁剪进一步提升性能。

---

附录：PostgreSQL 实战优化速查表

场景	优化策略	核心 SQL/配置	适用条件
海量日志写入	批量 INSERT + 异步提交	INSERT INTO ... VALUES (...), (...)...; ALTER SYSTEM SET synchronous_commit = 'remote_write';	高吞吐写入，可容忍少量数据丢失
TB级时序数据范围查询	BRIN 索引	CREATE INDEX idx ON tbl USING BRIN (ts) WITH (pages_per_range=32);	数据物理顺序与时间相关，查询为范围扫描
JSONB 属性检索	GIN 索引 (jsonb_path_ops)	CREATE INDEX idx ON tbl USING GIN (attr jsonb_path_ops);	查询使用 @> 操作符
JSONB 特定键查询	表达式 B-Tree 索引	CREATE INDEX idx ON tbl ((attr->>'key'));	频繁对某 JSON 键做等值/范围查询
全文搜索	tsvector 生成列 + GIN 索引	ALTER TABLE t ADD COLUMN tsv TSVECTOR GENERATED... STORED; CREATE INDEX ON t USING GIN (tsv);	大规模文本内容模糊搜索
定位慢 SQL	pg_stat_statements	SELECT queryid, mean_exec_time, calls, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;	任何需要发现性能瓶颈的系统
自动记录慢查询计划	auto_explain	ALTER SYSTEM SET auto_explain.log_min_duration = '200ms'; ALTER SYSTEM SET auto_explain.log_analyze = 'on';	生产环境非侵入式收集执行计划
统计信息过时	手动 ANALYZE + 降低阈值	ANALYZE table_name; ALTER TABLE t SET (autovacuum_analyze_scale_factor=0.01);	大规模数据变更后，执行计划变差
排序/聚合使用磁盘	增大 work_mem（会话级）	SET LOCAL work_mem = '256MB';	EXPLAIN 显示 Sort Method: external merge Disk
表膨胀清理	VACUUM FULL / pg_repack	VACUUM FULL table_name; 或用 pg_repack 在线处理	大量更新/删除后，表占用空间远大于实际数据
多租户数据隔离	RLS + 分区	ALTER TABLE t ENABLE ROW LEVEL SECURITY; CREATE POLICY ... USING (tenant_id = ...);	SaaS 应用，需要强制隔离用户数据
连接池优化	HikariCP 参数调整	maximum-pool-size={CPU*2+1}; leak-detection-threshold=60000	连接数不足或连接泄漏

延伸阅读

• 《高性能MySQL》第4版：虽然侧重于 MySQL，但其关于 Schema 设计、索引优化、查询性能分析的方法论是通用的，可作为对比学习资料。特别是其中关于EXPLAIN输出解读和索引选择原理的章节，值得反复阅读。
• 《PostgreSQL指南：内幕探索》：深入理解本文所涉及的 BRIN、GIN 索引内部结构，以及查询优化器决策过程的必读之作。推荐阅读其中关于“索引访问方法”和“查询计划”的章节。
• PostgreSQL 官方文档的以下章节：
  • Chapter 11. Indexes
  • Chapter 14. Performance Tips
  • Chapter 27. Monitoring Database Activity
  • Appendix F. Additional Supplied Modules (auto_explain, pg_stat_statements)