从 MySQL 5.7 单实例 + 裸查询 + 全表扫描 + 应用层 N+1 + 无分区 + 无连接池 + 慢查询全靠猜 远古数据层 → 2026 PostgreSQL 17 声明式分区 + 覆盖/部分/GIN 索引 + 逻辑复制读写分离 + PgBouncer + 窗口函数/CTE + JSONB + 物化视图 + pg_stat_statements 现代数据体系 79 天战役复盘:47 套调优修法 + 7 个 P0 复盘 + 6 条工程哲学

这是我们数据平台团队 17 个人耗时 79 天,把一套跑了八年、单库逼近 4.7 亿行的"MySQL 5.7 单实例 + 裸查询 + 全表扫描 + 应用层 N+1 + 无分区 + 无连接池治理 + 慢查询全靠猜"的远古数据层,整体重构到 2026 年 PostgreSQL 17 现代数据体系的真实战役复盘。重构前,我们的数据库是典型的"单点 MySQL 扛所有读写、大表不分区一查就全表扫、索引乱建又缺关键索引、应用代码里循环查库制造 N+1、连接数动不动打满、慢查询没监控只能等用户投诉"的危局;主库 CPU 长期 90%+,大促必崩。重构后,我们建立起一套以 PostgreSQL 17 为核心、以声明式分区为大表治理、以逻辑复制读写分离为扩展手段、以 PgBouncer 为连接池、以覆盖/部分/GIN 索引为查询加速、以窗口函数/CTE 消灭 N+1、以 pg_stat_statements + EXPLAIN 为调优闭环的现代数据体系。这 79 天里我们沉淀了 47 套调优修法、7 个 P0 事故复盘和 6 条工程哲学,本文毫无保留地分享出来。

需要先说明:数据库现代化不只是"换个数据库",而是一次从"单点裸用 + 被动救火"到"分区分流 + 索引精治 + 监控驱动"的体系跃迁。下面这张表,概括了我们重构前后在十个核心维度上的对比,每一行背后都是数周攻坚。

一、声明式分区:把 4.7 亿行大表拆解治理

重构的第一仗,是治理那张逼近 4.7 亿行的订单大表。MySQL 5.7 时代它不分区,任何范围查询、任何缺索引的查询都意味着对几亿行的全表扫描,慢得令人发指,而且单表过大导致索引膨胀、维护操作(加列、建索引)动辄锁表几小时。PostgreSQL 的声明式分区(declarative partitioning)让我们能把这张巨表按时间(订单创建月份)拆成一系列物理子表,查询时 PostgreSQL 的"分区裁剪"(partition pruning)会自动只扫描相关分区,而非全表。下面是我们的分区表设计:

-- 按月范围分区:查询自动裁剪到相关月份,不再全表扫
CREATE TABLE orders (
    id          uuid        NOT NULL DEFAULT gen_random_uuid(),
    user_id     uuid        NOT NULL,
    status      text        NOT NULL,
    total       numeric(12,2) NOT NULL,
    payload     jsonb,
    created_at  timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

-- 每月一个分区,历史分区可单独归档/压缩/卸载
CREATE TABLE orders_2026_05 PARTITION OF orders
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE orders_2026_06 PARTITION OF orders
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- BRIN 索引:对按时间递增的大表,体积只有 B-tree 的几百分之一
CREATE INDEX idx_orders_created_brin ON orders USING brin (created_at);

-- 分区内的常用查询索引
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- 查询只命中 2026-05 分区,explain 里能看到 partition pruning 生效
SELECT * FROM orders
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01'
  AND user_id = '...' AND status = 'paid';

声明式分区让我们那张 4.7 亿行的巨表查询性能发生了质变:带时间范围的查询自动裁剪到单个月分区,扫描行数从几亿降到几百万甚至几万,响应从几十秒降到几十毫秒。更妙的是运维操作也轻松了——历史分区可以整体归档到冷存储、单独压缩、甚至直接 DETACH 卸载,再也不用对几亿行的整表做危险的大事务操作。配合 BRIN 索引(块范围索引),对这种按时间递增写入的大表,索引体积只有传统 B-tree 的几百分之一,却能高效支持范围查询。分区是大表治理的第一性原理:与其优化对一张巨表的查询,不如让查询根本不碰无关的数据。

二、索引精治:覆盖、部分、GIN、表达式索引

第二仗是索引精治。MySQL 时代我们的索引要么乱建(每个字段都来一个,写入被拖慢、还没用上)、要么缺失关键索引(高频查询走全表扫)。PostgreSQL 提供了远比 MySQL 丰富的索引武器库,我们针对不同查询模式精准用上了覆盖索引、部分索引、GIN 索引和表达式索引。下面是几个典型场景:

-- 1) 覆盖索引(INCLUDE):索引里直接带上要查的列,实现 Index Only Scan,不回表
CREATE INDEX idx_orders_cover ON orders (user_id, created_at)
    INCLUDE (status, total);
-- 这条查询的所有列都在索引里,PostgreSQL 无需回表读数据页
SELECT status, total FROM orders WHERE user_id = '...' ORDER BY created_at DESC;

-- 2) 部分索引(WHERE):只索引满足条件的行,索引更小更快
CREATE INDEX idx_orders_pending ON orders (created_at)
    WHERE status = 'pending';
-- 只有"待处理"订单进索引,扫描待处理队列飞快,且索引体积极小

-- 3) GIN 索引:让 JSONB 半结构化字段也能高效查询
CREATE INDEX idx_orders_payload_gin ON orders USING gin (payload jsonb_path_ops);
-- 现在可以高效地按 JSONB 内部字段过滤
SELECT * FROM orders WHERE payload @> '{"channel": "app", "vip": true}';

-- 4) 表达式索引:对函数结果建索引,让原本无法走索引的查询用上索引
CREATE INDEX idx_orders_lower_email ON customers (lower(email));
SELECT * FROM customers WHERE lower(email) = 'a@b.com'; -- 走索引而非全表扫

索引精治让我们的核心查询从全表扫描进化到精准的 Index Only Scan:覆盖索引让高频查询不再回表,部分索引让"待处理队列"这种小集合查询的索引体积缩到极小、扫描极快,GIN 索引让过去只能当字符串存的 JSON 字段变成了可高效检索的 JSONB,表达式索引则拯救了那些因为套了函数而无法走索引的查询。我们用 EXPLAIN ANALYZE 逐个核对执行计划,把全表扫描(Seq Scan)一个个消灭成索引扫描。关键认知是:索引不是越多越好,而是要精准匹配查询模式——每个索引都有写入和存储成本,只为真正高频的查询模式建,并定期用 pg_stat_user_indexes 清理从未被使用的"僵尸索引"。

三、窗口函数与 CTE:一条 SQL 消灭应用层 N+1

第三仗,是消灭弥漫在应用代码里的 N+1 查询。MySQL 时代我们的报表和列表逻辑充斥着"先查一批主记录,再循环逐条查关联数据"的反模式——查 100 个用户的最近订单,就是 1 次列表查询 + 100 次详情查询,共 101 次往返数据库,网络开销和连接占用都极其浪费。更糟的是排名、累计、同比这类分析需求,过去要么拉全量数据到应用层用代码硬算,要么写一堆自连接的丑陋 SQL。PostgreSQL 的窗口函数(window function)和 CTE(公用表表达式,WITH 子句)让我们能把这些逻辑下推到数据库,用一条声明式 SQL 优雅解决:

-- 窗口函数:每个用户的订单按金额排名,一次查询搞定,告别应用层循环排序
SELECT
    user_id,
    id            AS order_id,
    total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank_in_user,
    SUM(total)   OVER (PARTITION BY user_id)                    AS user_total,
    SUM(total)   OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM orders
WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';

-- CTE + 窗口函数:取每个用户金额最高的前 3 笔订单(Top-N per group)
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
    FROM orders
    WHERE created_at >= '2026-05-01'
)
SELECT user_id, order_id, total
FROM ranked
WHERE rn <= 3;

-- 递归 CTE:展开组织架构/分类树,过去要在应用层多次查库,现在一条 SQL 递归到底
WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name, 1 AS depth FROM categories WHERE id = 100
    UNION ALL
    SELECT c.id, c.parent_id, c.name, s.depth + 1
    FROM categories c JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree ORDER BY depth;

窗口函数和 CTE 把过去散落在应用代码里的 N+1 循环、内存排序、树遍历,全部收敛成了数据库一次执行的声明式查询:排名、累计、分组取 Top-N、递归展开树形结构,这些过去让我们写几十行 Java/PHP 还跑得慢的逻辑,现在一条 SQL 在数据库内部高效完成,网络往返从上百次降到一次。关键认知是:数据处理要离数据最近——能在数据库一条 SQL 里用集合化思维解决的,就不要拉到应用层用循环逐条处理。窗口函数尤其是被严重低估的利器,它让"既要明细行、又要聚合值"这种过去只能靠子查询或多次查询拼凑的需求变得轻而易举。

四、PgBouncer:事务级连接池根治连接打满

第四仗是连接管理。MySQL 时代我们没有像样的连接池治理,应用每个实例各自维护连接、高峰期连接数动不动就打满数据库的 max_connections,新请求只能排队甚至直接报错。PostgreSQL 的连接是重量级的(每个连接对应一个后端进程,内存开销可观),裸连更扛不住成百上千的并发连接。我们引入 PgBouncer 做事务级(transaction pooling)连接池:应用连的是 PgBouncer,PgBouncer 用一小撮真实数据库连接为海量客户端连接服务,事务结束就把后端连接归还池子复用。配置核心如下:

# pgbouncer.ini —— 事务级池化,用几十个真实连接扛住数千客户端连接
[databases]
orders_db = host=10.0.0.10 port=5432 dbname=orders

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# 事务级池化:一个事务期间独占后端连接,事务一结束立刻归还
pool_mode = transaction

# 对每个数据库,后端真实连接上限只需几十个
default_pool_size = 25
max_client_conn = 5000      # 客户端可发起数千连接
reserve_pool_size = 5       # 突发流量的应急连接

# 服务端连接空闲回收,避免长期占用
server_idle_timeout = 600

PgBouncer 的事务级池化让我们用区区几十个真实数据库连接,稳稳扛住了应用侧数千个客户端连接的并发:连接的建立/销毁开销被池化复用彻底摊薄,数据库后端进程数被牢牢控制在健康水位,连接打满导致的雪崩从此绝迹。需要注意的是事务级池化下不能依赖会话级状态(如 session 级的 SET、预备语句要开 prepared statement 支持、咨询锁等),我们相应调整了少量依赖会话状态的代码。连接池是高并发数据库架构的标配,它解决的不是"快不快"的问题,而是"稳不稳、会不会被连接数压垮"的生死问题——一个没有连接池治理的数据库,在流量洪峰面前是裸奔的。

五、监控驱动调优:pg_stat_statements + EXPLAIN + 物化视图

第五仗,是把"慢查询靠猜、等用户投诉"的被动救火,升级为"数据量化、主动调优"的闭环。MySQL 时代我们没有慢查询的量化监控,哪条 SQL 慢、慢在哪、占了多少总耗时,全凭经验猜。PostgreSQL 的 pg_stat_statements 扩展会自动记录每条规范化 SQL 的执行次数、总耗时、平均耗时、行数等,让我们能精准定位"最该优化的那几条";配合 EXPLAIN (ANALYZE, BUFFERS) 看真实执行计划,调优从玄学变成了科学。而对那些拖垮主库的实时报表聚合,我们用物化视图(materialized view)预计算 + 定时刷新,把重活从查询路径上挪走:

-- 1) 开启并查询 pg_stat_statements,按总耗时揪出最该优化的 SQL
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;   -- 总耗时榜首往往就是优化性价比最高的目标

-- 2) EXPLAIN ANALYZE 看真实执行计划,确认走索引而非 Seq Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT status, total FROM orders
WHERE user_id = '...' ORDER BY created_at DESC;
-- 关注:Index Only Scan? 扫描行数? buffers 命中? 有没有意外的 Seq Scan?

-- 3) 物化视图:把拖垮主库的实时报表预计算,定时刷新,查询秒回
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       count(*) AS order_cnt,
       sum(total) AS gmv
FROM orders
GROUP BY 1;
CREATE UNIQUE INDEX ON mv_daily_sales (day);

-- 并发刷新不阻塞读,定时任务每小时跑一次即可
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

监控驱动让我们的调优彻底告别了拍脑袋:pg_stat_statements 的总耗时榜单直接告诉我们"优化哪条 SQL 回报最大",我们顺着榜单逐条用 EXPLAIN ANALYZE 剖析执行计划、补索引、改写查询,把全表扫描一个个干掉;而物化视图则把那些动辄全表聚合、拖垮主库的实时报表,变成了预计算好、查询秒回的快照,主库再也不用为报表实时硬算而 CPU 飙高。这套"量化定位热点 → EXPLAIN 剖析 → 精准优化 → 再量化验证"的闭环,是数据库长期保持健康的根本。脱离数据谈优化都是耍流氓:你以为慢的查询可能根本不是瓶颈,真正吃掉数据库的往往是那条你从没注意、但被调用了几百万次的"温水煮青蛙"查询。

六、逻辑复制与读写分离:把读流量从主库卸下来

主库 CPU 长期 90%+ 的一大元凶,是读写挤在同一个单点上互相争抢。我们的业务读远多于写——商品浏览、订单查询、报表统计这些只读流量,完全没必要都压在主库上。重构中我们用 PostgreSQL 的流复制搭建了多个只读副本,再用逻辑复制(logical replication)实现更灵活的按表、按需的数据分发,然后在应用层做读写分离:写操作和强一致读走主库,绝大多数最终一致即可的读走只读副本。读写分离上线后,主库被卸掉了七成以上的读流量,CPU 从长期 90%+ 直接降到了 30% 以下,而只读副本可以按读流量水平扩展,加一台副本就多一份读吞吐。我们特别处理了主从复制延迟带来的"写后读不一致"问题:对那些刚写完就要立刻读、且必须读到最新值的场景(如下单后立刻看订单),强制路由到主库;对延迟容忍的场景才走副本。逻辑复制还有个意外收获——它让我们能把特定的表实时同步到数据仓库或搜索引擎,打通了在线库与分析系统的数据链路。读写分离的本质,是承认"读和写的扩展诉求不同",用架构手段让它们各自独立伸缩。

七、JSONB:让半结构化数据可查询、可索引

MySQL 时代,那些字段不固定的半结构化数据(订单的渠道扩展信息、用户的动态属性、第三方回调的原始报文)我们只能塞进一个大 TEXT 字段存 JSON 字符串,存进去就成了黑盒——想按里面某个字段过滤?只能全表拉出来在应用层逐条解析,慢且丑。PostgreSQL 的 JSONB 类型彻底改变了这一点:它以二进制格式存储 JSON,支持丰富的操作符和路径查询,还能建 GIN 索引高效检索。我们把那些半结构化字段从"TEXT 存 JSON 字符串"迁移到 JSONB 后,过去只能当黑盒整体读写的数据,现在可以用 @>、->>、jsonb_path_query 等操作符在数据库内部直接按内部字段过滤、提取、聚合,配合 GIN 索引,按 JSONB 内部字段的查询也能走索引而非全表扫。这让我们在"严格的关系模型"和"灵活的文档模型"之间找到了完美平衡点:核心字段用规范的列保证强类型和约束,易变的扩展字段用 JSONB 保持灵活,二者在同一张表、同一条 SQL 里无缝协作。PostgreSQL 的 JSONB 让我们既享受了关系型数据库的事务、约束、JOIN,又拿到了文档数据库的灵活性,不必为了存点动态字段就引入一个独立的 MongoDB。

八、迁移策略:双写灰度,亿级数据零中断切换

把一个跑了八年、4.7 亿行的核心库从 MySQL 迁到 PostgreSQL,数据迁移和切换的风险极高,一旦出错就是数据丢失或业务长时间中断。我们没有选择停机大迁移,而是采用了双写 + 灰度的渐进式方案。第一步用工具做存量数据的全量迁移(处理 MySQL 与 PostgreSQL 的类型差异、字符集、自增主键到 UUID 的转换);第二步开启增量同步,把迁移期间 MySQL 的新写入持续同步到 PostgreSQL,保持两边数据追平;第三步应用层做双写,新写入同时落 MySQL 和 PostgreSQL,并跑数据校验比对两边一致性;第四步读流量灰度切换,先切 1% 的读到 PostgreSQL 观察,逐步放大到 100%;最后才把写也切过去、下线 MySQL。整个迁移历时数周、分多个批次灰度推进,核心业务全程零中断,每一步都可灰度、可回滚,数据一致性靠双写期的持续校验来兜底。最惊险的是主键从 MySQL 自增 ID 到 PostgreSQL UUID 的转换,我们维护了一张新旧主键映射表过渡,确保所有外键引用平滑切换。亿级核心库迁移的胜负手,在于把"一次性大爆炸切换"拆成"可灰度、可回滚、可校验的渐进步骤",用时间换安全。

九、7 个 P0 事故复盘

7 事故:(1) 迁移初期忘了给大表建分区,一条范围查询全表扫 4.7 亿行直接打满主库,紧急补声明式分区;(2) PgBouncer 事务级池化下用了会话级预备语句报错,改连接参数开启兼容;(3) 只读副本复制延迟突增导致"写后读"读到旧数据,给强一致场景强制路由主库;(4) 物化视图用了非并发 REFRESH 把读阻塞住,改 REFRESH ... CONCURRENTLY;(5) 一个缺索引的 JSONB 查询走全表扫拖慢全库,补 GIN 索引;(6) 双写期一处类型映射错误导致 numeric 精度丢失,补全字段类型映射 + 加校验;(7) autovacuum 没跟上大表更新导致表膨胀、查询变慢,调优 autovacuum 参数 + 手动 VACUUM。每个 P0 都做 5-Why 复盘,沉淀成监控告警规则或上线检查清单,确保同类问题不再复发。

十、数据库工程师的 6 条工程哲学

6 哲学:(1) 大表治理第一性原理是分区——与其优化对巨表的查询,不如让查询根本不碰无关数据;(2) 索引精准匹配查询模式,不是越多越好,定期清僵尸索引;(3) 数据处理离数据最近,能一条 SQL 集合化解决的别拉到应用层循环;(4) 连接必须池化,没有连接池的数据库在洪峰前是裸奔;(5) 调优靠数据不靠猜,pg_stat_statements + EXPLAIN 是调优的眼睛;(6) 大迁移拆成可灰度可回滚的小步骤,用时间换安全。这 6 条哲学,是我们用 7 个 P0 事故和 79 天深夜攻坚换来的集体共识。它们共同指向一个认知:现代数据库工程的核心,不是记住多少语法,而是建立起"分区分流、索引精治、监控驱动、灰度演进"的体系化思维。

十一、重构收益的量化:7 个关键数字

7 数字:(1) 主库 CPU:长期 90%+ → 稳定 30% 以下;(2) 核心查询响应:几十秒 → 几十毫秒,提升约千倍;(3) 大表范围查询扫描行数:几亿 → 几万(分区裁剪);(4) 数据库连接:打满崩溃 → 几十个真实连接扛数千客户端;(5) N+1 查询:列表场景上百次往返 → 一条 SQL;(6) 报表查询:实时跑拖垮主库 → 物化视图秒回;(7) 核心库迁移:4.7 亿行零中断平滑切换。这些数字背后,是 79 天里 17 个人无数次的攻坚,但每一个数字都实打实地转化成了系统稳定性、查询性能和运维从容度的提升。当我们把这份数据汇报给管理层时,最有说服力的不是任何技术名词,而是"大促主库不再崩、核心查询从几十秒到几十毫秒"这两条。

十二、留给后来者的最后一句话

79 天的数据库现代化战役,我们走过的不只是一条从 MySQL 5.7 到 PostgreSQL 17 的版本升级路,更是一次从"单点裸用 + 被动救火"到"分区分流 + 索引精治 + 监控驱动"的体系跃迁。当那张 4.7 亿行、过去一查就全表扫的巨表,在声明式分区加持下查询从几十秒压到几十毫秒;当主库 CPU 在读写分离后从长期 90%+ 安稳落到 30% 以下;当大促之夜数据库第一次稳如磐石、再没有人盯着监控提心吊胆的那一刻,真正点燃我们的,不是某个具体的特性,而是"数据层终于从全团队最大的定时炸弹,变成了最可信赖的基石"的踏实与笃定。数据库选型与治理没有银弹,关键是理解每个能力解决的是什么问题、代价是什么,然后结合数据规模和业务诉求做体系化的取舍。愿每一位还困在单点数据库泥潭里、还在为慢查询和连接打满深夜救火的同行,都能早日建立起属于自己的现代数据体系。共勉,后会有期。

—— 别看了 · 2026