大多数后端工程师和数据库打交道的方式，是这样演进的：先学会 CREATE TABLE 和 SELECT，再被某次线上慢查询教育一遍，接着是死锁、主从延迟、大表加字段锁住业务、误删数据找不到备份……每一次事故，都是对数据库“知识债”的一次还款。这篇文章希望把这些债务提前梳理一遍：不是 DBA 手册，而是写业务代码的人需要知道的那一部分，同时把 MySQL（InnoDB 引擎）和 PostgreSQL 放在一起讲，因为在实际工作中，你很可能两个都会遇到，而且两者的差异恰恰是很多坑的来源。
全文按照一个后端项目从“选型”到“上线运维”的顺序展开：
选型与建模：怎么在 MySQL 和 PostgreSQL 之间做选择；表结构、主键、数据类型、时间、字符集、范式与反范式、软删除、唯一约束。
查询与性能：索引原理与设计、事务与隔离级别、锁与死锁、EXPLAIN、慢查询、分页、N+1、批量写入、upsert、窗口函数与 CTE。
运行与运维：连接池、复制与高可用、备份与恢复、在线变更、监控、安全。
收尾工具：踩坑清单、对比表、速查表、FAQ 和小结。
在开始之前，有几条使用须知：
版本差异是真实存在的。MySQL 5.7、8.0、8.4 以及后续版本，PostgreSQL 12 到 17 乃至更新的版本，在在线 DDL、窗口函数/CTE 支持、JSON 能力、默认参数和工具上都有变化。本文凡是涉及版本的地方，我会尽量标注“大约从哪个版本开始”，但具体行为以所用版本官方文档为准。写这篇文章时我也刻意避免写入“某某操作快 N 倍”之类的数字：性能结论只能来自你自己的数据、你自己的硬件和你自己的压测。
示例 SQL 是教学用的。表名、字段名都是虚构的电商/订单场景，请在测试库中验证后再用于生产；涉及
DROP、DELETE、ALTER的语句尤其如此。EXPLAIN 的输出是示意。文中的执行计划输出只用于说明“怎么读”，数字不代表任何真实环境。
一个贯穿全文的原则：让数据库做它擅长的事（存储、约束、事务、索引检索），让应用做它擅长的事（业务编排、缓存、限流）；任何“优化”都先测量、再改动、再验证。
一、怎么选：MySQL（InnoDB）还是 PostgreSQL
1. 先说结论：两者都是靠谱的选择
现实中，“MySQL 和 PostgreSQL 哪个更好”是一个没有标准答案的问题。两者都是成熟的开源关系型数据库，都支持事务、
一个粗略的“画像”可以帮助建立直觉：
MySQL（InnoDB）：使用广泛，资料和人才储备丰富，主从复制的运维经验非常成熟；InnoDB 采用聚簇索引（数据按主键组织），点查和主键范围扫描非常直接；在很多互联网公司，围绕它形成了完整的分库分表、代理、迁移工具链。
PostgreSQL：特性更“全”、更偏标准 SQL，类型系统和索引类型丰富（数组、范围类型、JSONB、GIN/GiST/BRIN 等），支持扩展机制（PostGIS、pg_trgm、pgvector 等），在复杂查询、数据分析、地理信息等场景有优势；表是堆表（heap），索引指向元组位置。
2. 特性对比：选型时真正会用到的那些
| 维度 | MySQL（InnoDB） | PostgreSQL |
|---|---|---|
| 存储组织 | 聚簇索引，数据存放在主键 B+Tree 的叶子节点 | 堆表 + 独立索引，索引存放元组位置（ctid） |
| 默认隔离级别 | 可重复读（REPEATABLE READ） | 读已提交（READ COMMITTED） |
| 记录就地更新，旧版本放在 undo log，通过版本链读取 | 更新产生新的元组版本，旧版本留在表里，靠 VACUUM 清理 | |
| 索引类型 | 主要是 B+Tree，另有全文、空间、哈希（部分引擎/自适应哈希） | B-tree、Hash、GIN、GiST、SP-GiST、BRIN 等 |
| 部分索引/表达式索引 | 无部分索引；函数索引在 8.0.13 起支持 | 部分索引、表达式索引都支持 |
| JSON | JSON 类型，可通过生成列 + 索引加速 | JSON 与 JSONB，JSONB 可建 GIN 索引 |
| DDL 是否事务性 | 大多数 DDL 会隐式提交，不可回滚 | 绝大多数 DDL 可放在事务中回滚 |
| upsert | INSERT ... ON DUPLICATE KEY UPDATE |
INSERT ... ON CONFLICT，较新版本另有 MERGE |
| 窗口函数/CTE | 8.0 起支持（5.7 不支持） | 早已支持，CTE 的物化行为在 12 起有调整 |
| 复制 | 基于 binlog 的异步/半同步复制，组复制（MGR） | 基于 WAL 的流复制，另有逻辑复制 |
| 连接模型 | 每连接一个线程 | 每连接一个进程，连接较“重”，常配合 pgbouncer |
| 扩展机制 | 插件相对有限 | 扩展生态丰富（PostGIS、pg_trgm、pgvector 等） |
表中的内容是概括性描述，某些细节会随版本变化，例如 MySQL 8.0 之后的 DDL 能力、PostgreSQL 15/16/17 在 MERGE、逻辑复制、备份工具上的增强，请以所用版本官方文档为准。
3. 生态与运维现实
选型时容易被忽略的一点是“出了问题谁来救你”。
托管服务：主流云厂商对 MySQL 和 PostgreSQL 都有托管版本，通常会提供自动备份、主备切换、监控和只读副本。托管服务能抹平大量运维差异，但也会带来参数限制、扩展限制（例如某些 PostgreSQL 扩展不一定可用），选型前要核对清单。
工具链：MySQL 有
mysqldump、Percona XtraBackup、gh-ost、pt-online-schema-change、ProxySQL 等；PostgreSQL 有pg_dump、pg_basebackup、pgBackRest、Barman、Patroni、pgbouncer 等。两边都足够成熟。团队经验：团队里已经有人踩过某个数据库的坑，这在事故发生时价值极高。如果团队全员熟悉 MySQL，只为“PostgreSQL 特性更多”而贸然切换，常常得不偿失。
ORM 与框架支持：MyBatis、JPA/Hibernate、GORM、SQLAlchemy 等对两者都支持良好，但部分方言特性（如 PostgreSQL 的数组、JSONB 操作符、
RETURNING）需要额外适配。
4. 典型场景怎么选
| 场景 | 倾向 | 理由（均为一般性经验，需结合实际） |
|---|---|---|
| 典型 Web/电商 OLTP，读写以主键、二级索引点查为主 | 两者皆可，团队熟悉哪个选哪个 | 都能满足；MySQL 经验与工具链广泛，PostgreSQL 约束与类型更严谨 |
| 复杂报表、多表关联、窗口函数较多 | 倾向 PostgreSQL | 优化器与 SQL 特性较完整，但 MySQL 8.0 在这方面已大幅改善 |
| 地理空间（GIS） | 倾向 PostgreSQL + PostGIS | PostGIS 生态成熟；MySQL 也有空间类型但生态规模不同 |
| 半结构化数据（JSON）既要存又要查 | 倾向 PostgreSQL（JSONB + GIN） | MySQL 也可用 JSON + 生成列索引，取决于查询模式 |
| 全文检索 | 数据量不大时两者内置能力可用；复杂检索用专门的搜索引擎 | 不要强行用关系库扛复杂搜索 |
| 需要成熟的分库分表中间件、已有大量 MySQL 存量 | 倾向 MySQL | 迁移成本往往大于特性收益 |
| 需要强约束（排他约束、范围类型、自定义类型） | 倾向 PostgreSQL | 提供 EXCLUDE 约束、范围类型等 |
| 向量检索等新型需求 | 视扩展与托管支持而定 | 如 pgvector 这类扩展，需核对托管平台与版本 |
5. 选型建议
默认选团队最熟、云上托管最稳的那个。数据库不是“最先进的赢”，而是“出事时你能快速修好的赢”。
列出你真正需要的特性：部分索引？JSONB 查询？排他约束？PostGIS？如果清单里有 PostgreSQL 独有且不可替代的特性，答案就很清楚。
不要在一个系统里无谓地混用。多数据库并存会增加运维、监控、备份、人员培训的成本，除非有明确的理由（例如业务库 + 分析库）。
别为“可能的未来”过度设计。绝大多数应用在数据库选型上遇到瓶颈之前，会先遇到索引缺失、SQL 糟糕、连接池配置不当这些更低级的问题。
下文凡是两者行为不同的地方，都会分别给出 MySQL 与 PostgreSQL 的写法。
二、Schema 设计：地基打得好，后面少还债
表结构一旦上线，修改的代价远大于代码。在设计阶段多花半小时，往往能省下后面几周的迁移。
1. 一些通用的基本原则
每张表都有主键，并且主键最好是与业务无关的“代理键”（surrogate key），避免用手机号、身份证号、邮箱这类可变或敏感的业务字段当主键。
字段尽量
NOT NULL并给出合理默认值。NULL参与比较、聚合、唯一约束时语义复杂（NULL = NULL不为真），索引和查询也更难推理。真正“未知”的才用NULL。选最小够用的类型：不要所有整数都
BIGINT、所有字符串都VARCHAR(255)。但也不要为了省几个字节选过小的类型而埋下溢出风险（例如用INT作为会无限增长的流水表主键）。用约束表达业务规则：
NOT NULL、UNIQUE、CHECK、外键，能放在数据库的就放，应用层校验只是第一道防线，而不是唯一防线。MySQL 8.0.16 起才真正强制执行CHECK约束（更早的版本会解析但忽略），具体以所用版本文档为准。统一命名：表名、字段名使用小写加下划线，避免保留字（如
order、group、user在不同数据库中可能需要引用），布尔字段使用is_/has_前缀等规范，团队内部保持一致即可。每张业务表预留“审计字段”：
created_at、updated_at，必要时加created_by、updated_by。
2. 数据类型选择
整数：
| 类型 | MySQL | PostgreSQL |
|---|---|---|
| 1 字节 | TINYINT |
无（用 SMALLINT） |
| 2 字节 | SMALLINT |
SMALLINT |
| 4 字节 | INT |
INTEGER |
| 8 字节 | BIGINT |
BIGINT |
| 无符号 | 支持 UNSIGNED（8.0.17 起不推荐用于浮点/定点） |
不支持无符号 |
PostgreSQL 没有无符号整数，这意味着从 MySQL 迁移到 PostgreSQL 时，INT UNSIGNED 往往要换成 BIGINT。
金额与精确小数：不要用 FLOAT/DOUBLE（二进制浮点数有精度误差）。使用 DECIMAL(p, s)（PostgreSQL 中 NUMERIC(p, s) 等价）。另一种常见做法是以最小货币单位存整数（例如“分”），好处是计算简单、不会有舍入歧义，缺点是需要在展示层转换，并且多币种的小数位不同时要额外记录币种与精度。
布尔：PostgreSQL 有真正的 BOOLEAN；MySQL 的 BOOLEAN 是 TINYINT(1) 的别名，实际存的是整数。
枚举/状态：状态字段有几种做法，各有取舍：
数据库
ENUM（MySQL）或自定义枚举类型（PostgreSQL）：可读性好，但增减取值需要 DDL，在 MySQL 中修改枚举成员顺序还要小心。TINYINT/SMALLINT+ 代码常量：灵活，但库里看数据不直观，可以配合字典表或注释。VARCHAR+CHECK：可读，约束清晰，占用空间稍大。
在业务状态可能变化的系统里，我个人更倾向于“小整数 + 字典文档 + 可选的 CHECK 约束”，但这只是一种取舍，不是标准答案。
3. 主键选择：自增 vs UUID/ULID/雪花
这是最常被争论的话题，需要结合存储结构来理解。
InnoDB 的聚簇索引：表数据本身按主键顺序存放在 B+Tree 里；每一个二级索引的叶子节点存放的不是行位置，而是主键值。由此得出两个重要推论：
主键越短越好，因为它会被复制进每一个二级索引。
主键越接近单调递增越好，因为顺序插入总是追加到 B+Tree 的最右端，页分裂少；随机主键（如随机 UUID v4）会导致插入位置随机分布，更多的页分裂、页填充率下降和缓冲池缓存失效。
PostgreSQL 是堆表，插入顺序与主键无关，所以“聚簇导致的页分裂”问题没有 InnoDB 那么突出；但随机键对 B-tree 索引本身的局部性依然不友好，索引更大，缓存命中率更低。
| 方案 | 优点 | 缺点 | 适用 |
|---|---|---|---|
数据库自增（MySQL AUTO_INCREMENT；PG GENERATED ... AS IDENTITY / BIGSERIAL） |
简单、短、单调；对 InnoDB 友好 | 单点生成；分库分表需额外方案；ID 暴露业务量与可枚举；回滚会留空洞 | 单库单实例的大多数业务 |
| UUID v4 | 全局唯一，客户端可生成 | 36 字符或 16 字节，无序，索引膨胀；InnoDB 上插入局部性差 | 需要无中心生成且可接受代价；或不作为聚簇主键 |
| UUID v7 / ULID | 时间有序前缀 + 随机部分，兼顾唯一性与局部性 | 16 字节；ULID 通常以 26 字符 Base32 文本表示；需要库或数据库版本支持 | 分布式生成、希望大致有序 |
| 雪花（Snowflake）类 64 位 ID | 8 字节，趋势递增，可本地生成 | 依赖机器号分配；时钟回拨会带来重复或阻塞风险；前端 JS 精度问题 | 分库分表、分布式服务 |
几点实战提示：
MySQL 中存 UUID：不要用
CHAR(36)当主键。使用BINARY(16)，8.0 提供UUID_TO_BIN(uuid, swap_flag)与BIN_TO_UUID()，其中的 swap 参数可以把 v1 UUID 的时间部分调整到前面以获得更好的有序性（是否适用以所用版本文档为准）。PostgreSQL 中存 UUID：使用原生
uuid类型（16 字节），不要用字符串。gen_random_uuid()生成 v4（PG 13 起内置，更早需扩展）。新版本是否内置 UUID v7 生成函数，以所用版本官方文档为准；没有时可由应用层生成。雪花 ID 与 JavaScript：JS 的
Number只能精确表示到 2^53，64 位整数返回给前端时应当序列化成字符串，否则末尾几位会被舍入。时钟回拨：雪花实现需要处理时钟回拨（等待、报错或使用备用序列），不要假设服务器时间只会前进。
自增 ID 的空洞是正常的：事务回滚、
INSERT ... ON DUPLICATE KEY UPDATE失败、批量插入预分配等都会造成跳号，不要依赖“连续”。MySQL 8.0 起自增计数器会持久化（5.7 重启后可能回退到现有最大值加一），细节以官方文档为准。对外暴露的标识：如果不想暴露自增 ID，可以保留自增内部主键，另设一个对外的随机/有序
public_id字段，并加唯一索引。
-- MySQL：自增主键（内部）+ 对外 UUID CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, public_id BINARY(16) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, amount_cent BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (id), UNIQUE KEY uk_public_id (public_id), KEY idx_user_created (user_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO orders (public_id, user_id, amount_cent) VALUES (UUID_TO_BIN(UUID(), 1), 1001, 9900);
-- PostgreSQL：IDENTITY 主键 + 原生 uuid CREATE TABLE orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, public_id UUID NOT NULL DEFAULT gen_random_uuid(), user_id BIGINT NOT NULL, amount_cent BIGINT NOT NULL, status SMALLINT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT uk_orders_public_id UNIQUE (public_id) ); CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
注意：PostgreSQL 没有 ON UPDATE CURRENT_TIMESTAMP 这种列属性，updated_at 通常由应用或触发器维护：
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_orders_updated_at BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION set_updated_at();
4. 键长度与索引长度限制
InnoDB 的单个索引键长度有上限：在默认的 16KB 页和 DYNAMIC/COMPRESSED 行格式下，上限是 3072 字节（更老的格式是 767 字节），复合索引是各列长度之和。
utf8mb4下每个字符最多占 4 字节，所以VARCHAR(255)的完整索引最多需要 1020 字节，通常没问题；但当你给VARCHAR(1000)的列直接建索引，就可能触发Specified key was too long。具体数字以所用版本文档为准。对很长的字符串建索引，常见做法有：前缀索引（
KEY idx_url (url(64))，但无法用作覆盖索引，且需要评估前缀区分度）、哈希列（存 CRC32或SHA摘要作为索引列再回表比较）、或者重新审视需求，改用全文检索/搜索引擎。PostgreSQL 的 B-tree 索引项大小同样有上限（约为页大小的三分之一，具体以文档为准），过长的值会报错
index row size exceeds maximum。可用表达式索引对长文本做摘要，例如CREATE INDEX ON docs (md5(content))，需要注意这只能用于等值匹配。记住：主键越长，InnoDB 所有二级索引越大。用
BIGINT往往比CHAR(36)的 UUID 字符串好得多。
5. VARCHAR、TEXT 与 LONGTEXT
MySQL：
VARCHAR(n)的 n 是字符数，不是字节数；整行（所有列合计，不含大对象外存部分）的字节数有 65535 的限制。TEXT家族：TINYTEXT（255 字节）、TEXT（约 64KB）、MEDIUMTEXT（约 16MB）、LONGTEXT（约 4GB），上限还受max_allowed_packet限制。在 InnoDB 中，较长的值可能存储在行外的溢出页。把大文本和热点小字段放在同一张表，
SELECT *的代价会变高（尤其当列表接口只需要标题却把正文也读了出来）。TEXT/BLOB列建索引必须指定前缀长度；TEXT不能有表达式之外的普通默认值（8.0.13 起支持表达式默认值，具体以文档为准）。实践建议：能用
VARCHAR(n)就不用TEXT，n 取业务上限而不是随手 255；超大正文考虑拆表（如article+article_content）或放对象存储，库里只存引用。
PostgreSQL：
text、varchar(n)、char(n)在存储实现上非常接近，文档指出在性能上text与varchar没有本质差别；char(n)会补空格，通常不推荐使用。因此 PostgreSQL 里常见的写法是：不需要长度限制就直接text，需要限制就varchar(n)或text + CHECK (char_length(col) <= n)。大值通过 TOAST 机制自动压缩并存储到行外，单个字段上限约为 1GB。这意味着没有 MySQL 那样的
LONGTEXT分级，但同样需要避免SELECT *读取大字段。
6. JSON 与 JSONB
JSON 适合存放结构多变、不频繁用作过滤条件的数据，例如第三方回调原文、扩展属性、配置快照。它不是用来逃避建模的。
PostgreSQL：
json保存原始文本，jsonb保存解析后的二进制格式，支持索引和更丰富的操作符，通常是首选。MySQL：
JSON类型以二进制格式存储，提供JSON_EXTRACT、->、->>等函数；JSON 列本身不能直接建普通索引，常见做法是生成列 + 索引，8.0 另有多值索引（multi-valued index）用于 JSON 数组，具体语法与限制以所用版本文档为准。
-- PostgreSQL：JSONB + GIN 索引
CREATE TABLE events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-- 包含查询（可使用 GIN 索引）
SELECT id FROM events WHERE payload @> '{"type": "pay", "channel": "alipay"}';
-- 取值
SELECT payload->>'type' AS type FROM events WHERE id = 1;
-- MySQL：JSON + 生成列 + 索引 CREATE TABLE events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, payload JSON NOT NULL, type VARCHAR(32) GENERATED ALWAYS AS (payload->>'$.type') STORED, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), KEY idx_type (type) ) ENGINE=InnoDB; SELECT id FROM events WHERE type = 'pay';
使用 JSON 的几个提醒：
频繁出现在
WHERE/JOIN/ORDER BY的字段，提取成真正的列。JSON 内部字段没有外键、没有类型约束，数据质量要靠应用或
CHECK（PostgreSQL 可对 JSONB 写CHECK）。整个 JSON 的局部更新，在不同数据库中的成本不同（MySQL 8.0 对部分更新有优化，PostgreSQL 的 JSONB 更新通常会写出整个新值），大对象高频更新要谨慎。
7. 时间类型与时区
时间问题是线上 bug 的常客，原则是：数据库里存“绝对时刻”，展示时再转成本地时区。
MySQL：
DATETIME：不带时区，按字面值存储，范围大（1000-01-01 到 9999-12-31）。写进去什么，读出来就是什么，不受会话时区影响。TIMESTAMP：内部以 UTC 存储，读写时按会话的time_zone转换；传统范围到 2038 年（"2038 问题"），具体能否扩展取决于版本与平台，以官方文档为准。都可以带小数秒精度，如
DATETIME(3)。DATE、TIME另有各自用途。
PostgreSQL：
timestamptz（timestamp with time zone）：内部存 UTC，输出时按会话的TimeZone转换。注意它并不存储原始时区信息。timestamp（不带时区）：字面值，不做转换。除非你确切需要“墙上时钟时间”（如“每天 9:00 开会”这类），否则业务时间戳一般选timestamptz。
实践建议：
服务端统一使用 UTC 存储（或在可控范围内统一一个固定时区），应用和 JDBC 连接的时区设置要与之一致；MySQL 驱动里可通过连接参数设置时区（Connector/J 的参数名在 5.x/8.x 之间有变化，以所用驱动版本文档为准）。
不要把时间存成字符串（
'2026-10-01 12:00:00'），无法正确比较、索引和做时间运算。需要记录“用户所在时区”的业务（如日历、提醒），额外存时区标识（如
Asia/Shanghai）而不是偏移量，因为夏令时规则会变。按“天”聚合时务必明确按哪个时区的“天”，例如在 PostgreSQL 中用
date_trunc('day', created_at AT TIME ZONE 'Asia/Shanghai')。范围查询使用半开区间：
created_at >= '2026-10-01' AND created_at < '2026-10-02'，避免BETWEEN在毫秒精度上的边界问题。
8. 字符集与排序规则：统一用 utf8mb4
MySQL 里的
utf8其实是utf8mb3（最多 3 字节），存不了 Emoji 和部分生僻字。请统一使用utf8mb4。8.0 的默认字符集是utf8mb4、默认排序规则是utf8mb4_0900_ai_ci；5.7 默认是latin1，常用的是utf8mb4_general_ci或utf8mb4_unicode_ci。排序规则（collation）带
_ci表示大小写不敏感，这会影响WHERE、UNIQUE、GROUP BY的结果：'Abc'与'abc'在_ci下被认为相同，唯一索引会拒绝第二条。需要区分大小写（如 token、区分大小写的编码）时使用_bin或_cs排序规则。连接字符集也要一致：库、表、列、连接（
SET NAMES utf8mb4或驱动参数）都应是utf8mb4，否则会出现乱码或索引因字符集转换失效（见“索引失效”一节）。PostgreSQL：字符编码在创建数据库时确定，推荐
UTF8；排序规则取决于 libc 或 ICU。要注意操作系统升级可能导致 collation 版本变化，从而使既有的文本索引顺序与新规则不一致，需要按官方文档指引重建索引。
9. 范式与反范式
第三范式（3NF）的直观含义：每个非主属性只依赖于主键，且不依赖于其他非主属性。比如订单表里不应当存用户的昵称和手机号，只存
user_id。好处：避免冗余与更新异常，数据一致性好。反范式是为了读性能而有意冗余：例如订单表冗余存“下单时的商品名称、单价”，这既是性能优化，也是业务快照（商品改价不应改变历史订单金额）。
常见的合理冗余：快照类字段（价格、地址、名称）、统计计数（评论数、点赞数）、为避免跨库 JOIN 的关联字段。
冗余带来的代价是一致性维护：需要明确“谁是事实来源（source of truth）”，通过事务、事件或定期校对保证最终一致。
一个简单的经验：先按范式建模，遇到真实的性能瓶颈、并且索引和缓存解决不了时，再有针对性地反范式；不要一开始就为了“少 JOIN”把所有字段铺平。
10. 多对多与关联表（Junction Table）
多对多关系用一张关联表表示，例如“用户-角色”：
-- 通用写法（MySQL / PostgreSQL 基本相同） CREATE TABLE user_role ( user_id BIGINT NOT NULL, role_id BIGINT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, role_id), KEY idx_role_user (role_id, user_id) -- PostgreSQL 请改写为单独的 CREATE INDEX );
要点：
联合主键
(user_id, role_id)天然保证不重复，并支持“某用户有哪些角色”的查询；再加一个反向索引(role_id, user_id)支持“某角色有哪些用户”。关联表也可以带自己的属性（加入时间、有效期、排序号），此时它已经是一个有意义的实体。
是否使用外键：外键保证引用完整性，但在 MySQL 上会增加锁的范围、在大规模分库分表场景难以使用，不少互联网团队选择不建外键而在应用层保证；PostgreSQL 的外键使用更为普遍。这是团队约定问题，没有绝对答案。如果不使用外键，务必为关联列建立索引，并定期做孤儿数据校验。
外键列自身（子表一侧）需要有索引，否则删除或更新父表行时会触发对子表的全表扫描，并可能引起长时间锁等待。
11. 审计表与日志表
审计字段（
created_at、updated_at、created_by）放在业务表内；变更历史（谁在何时把什么从什么改成了什么）放在单独的审计表，写入方式可以是应用层事件、触发器，或 CDC（基于 binlog/WAL 的变更捕获）。日志/流水类表有几个特点：只追加、很少更新、数据量增长快。设计时要考虑：
按时间分区（MySQL
PARTITION BY RANGE、PostgreSQL 声明式分区），这样可以直接DROP PARTITION/DETACH过期分区，而不是对大表执行代价巨大的DELETE。谨慎建索引：每个索引都会放大写入成本。
明确保留期限，并有定期归档策略。
大字段（如请求/响应报文）考虑压缩或放对象存储。
在 PostgreSQL 中，若日志表按时间自然递增且很大，BRIN 索引体积很小，适合按时间范围扫描（见索引一节）。
12. 软删除
软删除（deleted_at 或 is_deleted）常被用来“防止误删、可恢复、保留历史关联”，但它也有明显的副作用：
所有查询都必须带
deleted_at IS NULL，漏一处就是数据泄露或脏数据；ORM 的全局过滤器可以缓解，但原生 SQL 和报表容易漏。唯一约束冲突：被软删除的用户名/手机号仍然占着唯一索引，用户无法重新注册。
表会越来越大，索引里混着大量“已死”数据。
常见的解决思路：
-- PostgreSQL：部分唯一索引，只对未删除的行唯一 CREATE UNIQUE INDEX uk_users_phone_alive ON users (phone) WHERE deleted_at IS NULL;
-- MySQL：没有部分索引，常见做法是把 deleted_at 并入唯一键， -- 并让“未删除”用固定的哨兵值而不是 NULL（因为唯一索引允许多个 NULL） ALTER TABLE users MODIFY deleted_at DATETIME(3) NOT NULL DEFAULT '1970-01-01 00:00:01.000', ADD UNIQUE KEY uk_phone_deleted (phone, deleted_at); -- 删除时写入当前时间，未删除保持哨兵值
另一类做法是不做软删除，而做“归档表”：删除时把行移到 users_archive，主表始终只含活跃数据。需要选择的话可以这样思考：数据需要频繁“恢复”吗？法务/审计是否要求保留？软删除之后是否还会被业务读取？
软删除不是免费午餐。它把“删除”这个动作的复杂度，从一次
DELETE转移到了此后的每一条查询、每一个唯一约束和每一次报表上。
13. 唯一约束与并发
“先查再插”是并发场景里的经典 bug：
线程 A: SELECT 用户名 'tom' 是否存在? -> 不存在 线程 B: SELECT 用户名 'tom' 是否存在? -> 不存在 线程 A: INSERT 'tom' -> 成功 线程 B: INSERT 'tom' -> 重复！（若没有唯一索引）
正确做法是：
一定要有数据库层的唯一索引，它才是并发下的最终裁判。
应用捕获唯一键冲突异常（MySQL 错误码 1062、SQLSTATE
23000；PostgreSQL SQLSTATE23505），转换成业务上的“已存在”。需要“有则更新，无则插入”时使用 upsert（见后文），而不是应用层先查再决定。
幂等接口（如支付回调、下单）：用业务唯一键（如
out_trade_no）做唯一索引，重复请求直接得到已有结果。注意
NULL：MySQL 和 PostgreSQL 默认都允许唯一索引中出现多个NULL。PostgreSQL 15 起可以用UNIQUE NULLS NOT DISTINCT改变这一行为（以所用版本文档为准）。注意大小写与排序规则：
_ci排序规则下'Tom'与'tom'冲突，是否符合预期要明确。注意并发下的锁：在 InnoDB 中，唯一索引上的并发插入冲突会产生锁等待甚至死锁（见事务一节），应用要有重试和超时处理。
三、索引：理解结构，才能设计出对的索引
1. B+Tree 是怎么工作的
两个数据库最常用的索引都是 B+Tree（PostgreSQL 的默认索引叫 B-tree，实现上是 B+Tree 的变体）。它的特点是：多叉、矮胖、有序、叶子节点之间互相链接。
[ 内部节点：只存键和子节点指针 ] / | \ [ 内部节点 ] [ 内部节点 ] [ 内部节点 ] / | \ / | \ / | \ [叶子]→[叶子]→[叶子]→[叶子]→[叶子]→[叶子]→[叶子]→[叶子]→[叶子] ↑ 叶子节点按键有序，并通过链表相连，范围扫描沿链表顺序走
查找一个键，从根到叶子只需要几次页读取（树高通常很低），因此等值查找、范围查找、排序都很高效。
叶子节点有序且相连，因此
ORDER BY、BETWEEN、>、<、前缀LIKE 'abc%'都能利用索引。InnoDB 聚簇索引：主键索引的叶子节点就是整行数据；二级索引叶子存的是主键值，所以通过二级索引查完整行需要回表（再用主键去聚簇索引里查一次）。
PostgreSQL：所有索引都是独立结构，叶子存的是指向堆的元组位置（ctid）。查完整行也需要访问堆；如果满足条件（可见性映射中该页“全可见”），可以做 Index Only Scan 不访问堆。
2. 组合索引与最左前缀
组合索引 (a, b, c) 按 a、b、c 的顺序排序：先按 a 排，a 相同再按 b 排，以此类推。因此：
| 查询条件 | 能否利用索引 | 说明 |
|---|---|---|
a = 1 |
可以 | 用到 a |
a = 1 AND b = 2 |
可以 | 用到 a、b |
a = 1 AND b = 2 AND c = 3 |
可以 | 全部用到 |
b = 2 |
通常不能高效利用 | 跳过了最左列；MySQL 8.0 与 PG 在某些情况下可通过跳跃扫描等方式利用，以所用版本文档为准 |
a = 1 AND c = 3 |
部分 | 只有 a 用于定位，c 只能在索引内过滤（见 ICP） |
a > 1 AND b = 2 |
部分 | a 是范围条件，范围列之后的列难以继续用于定位 |
a = 1 ORDER BY b |
可以 | 索引天然有序，无需额外排序 |
设计顺序的经验：
等值条件的列放前面，范围条件的列放后面（范围之后的列无法继续缩小扫描范围）。
区分度（选择性）高的列通常更靠前，但不绝对，要结合查询模式；一个索引应该服务于多个高频查询。
把
ORDER BY列也纳入考虑，避免额外的 filesort / Sort 节点。不要给每一列都单独建索引；多个单列索引不等于一个合适的组合索引（优化器可能做索引合并，但通常效率不如一个好的组合索引）。
索引不是越多越好：每个索引都占空间，并拖慢
INSERT/UPDATE/DELETE。定期清理未使用和重复（前缀被覆盖）的索引。
-- 常见查询：某用户最近的订单，按状态过滤 SELECT id, amount_cent, created_at FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY created_at DESC LIMIT 20; -- 合适的索引（等值列在前，排序列在后） -- MySQL ALTER TABLE orders ADD KEY idx_user_status_created (user_id, status, created_at); -- PostgreSQL CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);
3.
如果查询所需的所有列都包含在某个索引中，数据库就不需要回表，直接从索引返回结果。
MySQL：
EXPLAIN的Extra出现Using index表示使用了覆盖索引。 PostgreSQL：执行计划出现
Index Only Scan，并且要看Heap Fetches的数量；如果表经常更新、可见性映射未及时更新，仍然会回堆检查可见性，需要VACUUM来维护。PostgreSQL 11 起支持
INCLUDE，把非检索列放进索引叶子但不参与排序，适合做覆盖：
-- PostgreSQL：INCLUDE 列只用于覆盖，不参与排序和唯一性 CREATE INDEX idx_orders_user_cover ON orders (user_id, created_at) INCLUDE (amount_cent, status);
MySQL 没有 INCLUDE 语法，只能把列放进索引键里（并受索引长度限制）。
4. 索引条件下推（ICP, Index Condition Pushdown）
对于 MySQL 5.6 及以后版本，在组合索引 (a, b) 上执行 WHERE a = 1 AND b LIKE '%x' 这类查询时，b 的条件虽然无法用于定位，但可以在存储引擎层、利用索引里已有的 b 值先过滤，不满足的行不用回表。EXPLAIN 的 Extra 中显示 Using index condition。
没有 ICP 时：索引按 a 定位后，每一行都回表取整行，再由 Server 层判断 b；有了 ICP，回表次数可以减少。ICP 只对二级索引有效，对聚簇索引没有意义（行本来就在手上）。可通过 optimizer_switch 中的 index_condition_pushdown 开关控制，具体以所用版本文档为准。
PostgreSQL 中类似的思路是：索引扫描的 Index Cond 与 Filter 的区别——Index Cond 用于在索引上定位，Filter 是取回行后的过滤，Rows Removed by Filter 很大通常就是索引设计不够贴合的信号。
5. PostgreSQL 的特色索引
| 索引类型 | 适用场景 | 简例 |
|---|---|---|
| B-tree | 等值、范围、排序（默认） | CREATE INDEX ... (col) |
| 部分索引（Partial） | 只索引一部分行，如“未处理的任务” | ... (created_at) WHERE status = 0 |
| 表达式索引（Expression） | 查询条件是函数/表达式的结果 | ... (lower(email)) |
| GIN | 多值类型：数组、JSONB、全文检索（tsvector）、trigram | USING GIN (payload) |
| GiST | 几何、范围类型、近邻搜索、排他约束 | USING GIST (area) |
| SP-GiST | 非均衡的空间划分数据（如前缀树、四叉树类） | USING SPGIST (...) |
| BRIN | 物理存储顺序与键高度相关的超大表（如按时间追加的日志） | USING BRIN (created_at) |
| Hash | 仅等值查询（10 版本起 WAL 日志化，可用于生产，是否优于 B-tree 需实测） | USING HASH (col) |
-- 部分索引：只索引待处理的任务，体积小、命中快
CREATE INDEX idx_jobs_pending ON jobs (run_at) WHERE status = 'pending';
-- 表达式索引：大小写不敏感的邮箱查找；查询必须写成同样的表达式
CREATE UNIQUE INDEX uk_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower('Tom@Example.com');
-- GIN：数组和 JSONB 包含查询
CREATE INDEX idx_posts_tags ON posts USING GIN (tags); -- tags text[]
SELECT id FROM posts WHERE tags @> ARRAY['mysql', 'pg'];
-- 模糊匹配（需要 pg_trgm 扩展）：支持 LIKE '%xx%'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
-- GiST + 排他约束：同一间会议室的预订时间不得重叠
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE booking (
room_id INT NOT NULL,
during TSTZRANGE NOT NULL,
EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
-- BRIN：体积极小，适合按时间追加的日志
CREATE INDEX idx_logs_created_brin ON logs USING BRIN (created_at);
-- 在线建索引，不阻塞写入（不能放在事务块中，失败会留下 INVALID 索引需清理）
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
MySQL 方面，8.0.13 起支持函数索引（对表达式建索引），8.0 另有降序索引、不可见索引（INVISIBLE，先隐藏观察再决定是否删除，非常适合安全地下线索引）、全文索引（InnoDB FULLTEXT，支持 ngram 分词器处理中文）。
-- MySQL：不可见索引——先隐藏，观察没问题再删除 ALTER TABLE orders ALTER INDEX idx_user_status_created INVISIBLE; -- 有问题可立即恢复 ALTER TABLE orders ALTER INDEX idx_user_status_created VISIBLE; -- MySQL：函数索引（8.0.13+） ALTER TABLE users ADD INDEX idx_email_lower ((LOWER(email)));
6. 索引失效的常见情形
以下情形会让本来可用的索引无法被高效使用（是否真的“失效”最终由优化器基于成本和统计信息决定，用 EXPLAIN 验证）：
| 情形 | 示例 | 改法 |
|---|---|---|
| 对索引列使用函数/运算 | WHERE DATE(created_at) = '2026-10-01'；WHERE id + 1 = 100 |
改成范围：created_at >= '2026-10-01' AND created_at < '2026-10-02'；或建表达式索引 |
| 隐式类型转换 | 字符串列 phone 与数字比较：WHERE phone = 13800000000 |
类型保持一致：WHERE phone = '13800000000' |
| 隐式字符集/排序规则转换 | 两表 JOIN 的关联列字符集不同（如 utf8mb3 对 utf8mb4） |
统一字符集和排序规则 |
| 前导通配符 | LIKE '%abc'、LIKE '%abc%' |
用前缀匹配；PG 用 pg_trgm；MySQL 用全文索引或搜索引擎 |
| 违反最左前缀 | 索引 (a,b)，仅查 b |
调整索引或查询 |
OR 连接不同列 |
WHERE a = 1 OR b = 2 |
两列各有索引时优化器可能做索引合并；也可改写为 UNION ALL |
| 否定条件 | !=、NOT IN、NOT LIKE |
视选择性而定，通常很难高效使用索引 |
IS NULL / IS NOT NULL |
取决于 NULL 的占比 | 用 EXPLAIN 实测 |
| 区分度太低 | 性别、布尔、状态字段单独建索引 | 与其他列组合，或用 PG 的部分索引 |
| 统计信息过期 | 大批量变更后计划突然变差 | MySQL ANALYZE TABLE；PG ANALYZE（autovacuum 通常会自动做） |
| 范围条件之后的列 | (a,b,c) 中 a > 1 AND b = 2 |
调整列顺序以匹配主要查询 |
ORDER BY 方向混用 |
ORDER BY a ASC, b DESC 与索引方向不一致 |
MySQL 8.0 及 PG 可建混合方向索引 |
隐式类型转换的细节在两个库里表现不一样：MySQL 在“字符串列 = 数字”时会把列转成数字比较，索引失效，并且可能得到“意外匹配”的结果；PostgreSQL 的类型检查更严格，通常直接报错或要求显式转换。无论哪个库，都应该由应用保证参数类型与列类型一致，使用预编译语句并正确绑定类型。
7. 索引设计流程（一个可执行的步骤）
收集真实的高频/慢查询（慢查询日志、
pg_stat_statements），而不是凭想象建索引。针对每个查询，列出：等值列、范围列、排序列、返回列。
设计尽量少的索引覆盖多个查询；在预发环境用接近生产的数据量验证
EXPLAIN。上线时用在线方式创建（MySQL Online DDL；PG
CONCURRENTLY），避开高峰。上线后复查：索引实际被使用了吗？写入有没有明显变慢？（MySQL
sys.schema_unused_indexes、performance_schema；PGpg_stat_user_indexes的idx_scan。）
四、事务、隔离级别与锁
1. ACID 与事务的基本用法
事务保证一组操作要么全部成功，要么全部不生效（原子性），并且在并发下互不干扰（隔离性）、提交后不丢（持久性）、数据始终满足约束（一致性）。
-- 转账：两个更新必须在同一个事务中 START TRANSACTION; -- PostgreSQL 可写 BEGIN UPDATE account SET balance = balance - 100 WHERE id = 1 AND balance >= 100; -- 检查影响行数，为 0 则回滚 UPDATE account SET balance = balance + 100 WHERE id = 2; COMMIT; -- 出错则 ROLLBACK
实践中的原则：
事务要短：不要在事务里调用外部 HTTP、
RPC、发消息，也不要等待用户输入。长事务会占用连接、持有锁、阻碍 purge/VACUUM、拖慢主从复制。 更新用“条件更新”而不是读出来改再写回：
UPDATE ... SET balance = balance - 100 WHERE id = 1 AND balance >= 100，用影响行数判断是否成功，天然防止超扣。按固定顺序访问资源，降低死锁概率。
应用要能处理事务失败：死锁、序列化失败、超时都可能发生，对幂等操作做重试。
注意 Spring 的
@Transactional陷阱：自调用不生效、非 public 方法、异常被吞掉、默认只对运行时异常回滚等，这些是框架层面的问题，但会直接表现为数据库里的数据不一致。
2. 四种隔离级别
SQL 标准定义了四个隔离级别，用三种“异常现象”区分：
脏读：读到其他事务未提交的数据。
不可重复读：同一事务内两次读同一行，结果不同（被别的事务提交了修改）。
幻读：同一事务内两次按相同条件查询，返回的行集合不同（别的事务插入/删除了满足条件的行）。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不会 | 可能 | 可能 |
| REPEATABLE READ | 不会 | 不会 | 标准允许，实现各异 |
| SERIALIZABLE | 不会 | 不会 | 不会 |
两个数据库的实际行为与标准表格有出入，这是面试和线上排障都容易混淆的点：
MySQL InnoDB 默认
REPEATABLE READ。快照读（普通SELECT）基于事务第一次读时建立的 ReadView，同一事务内读到一致的快照；当前读（SELECT ... FOR UPDATE、UPDATE、DELETE）读取最新已提交的数据并加锁，通过临键锁（Next-Key Lock，记录锁 + 间隙锁）来防止幻读。因此 InnoDB 在 RR 下对大多数场景防住了幻读，但“快照读 + 当前读”混用时，仍可能看到意料之外的结果。PostgreSQL 默认
READ COMMITTED：每条语句看到的是语句开始时的快照。其REPEATABLE READ实际是快照隔离（整个事务使用同一快照），不会出现幻读，但可能出现“序列化失败”错误（SQLSTATE40001），需要应用重试。SERIALIZABLE使用 SSI（可序列化快照隔离）检测依赖环，同样可能报序列化失败。PostgreSQL 的READ UNCOMMITTED行为等同于READ COMMITTED，不存在脏读。
-- MySQL：查看和设置隔离级别（8.0 变量名为 transaction_isolation） SELECT @@transaction_isolation; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- PostgreSQL SHOW transaction_isolation; BEGIN ISOLATION LEVEL REPEATABLE READ; -- ... COMMIT;
隔离级别的选择：不少互联网团队把 MySQL 调成 READ COMMITTED，以减少间隙锁带来的锁冲突和死锁（同时需要 binlog 格式使用 ROW）。是否这么做取决于业务对一致性读的要求，切换前务必评估。
3.
InnoDB：就地更新 + undo log 版本链 聚簇索引中的行（最新版本） │ DB_TRX_ID, DB_ROLL_PTR ▼ undo log: 版本 N-1 ──► 版本 N-2 ──► ... 读事务按 ReadView 沿版本链回溯，找到自己可见的版本 旧版本由 purge 线程在没有事务需要时清理 PostgreSQL：追加新元组 + VACUUM 回收 堆页中：[元组 v1 (xmax=100)] [元组 v2 (xmin=100)] ... UPDATE = 标记旧元组失效（xmax）+ 写入新元组（xmin） 可见性由事务快照与 xmin/xmax 判断 死元组由 (auto)VACUUM 回收
InnoDB：
更新时直接修改聚簇索引中的行，把旧值写入 undo log，行里的回滚指针指向它。
长事务的危害：只要有一个很老的事务（或长时间未提交的快照）存在，undo 就无法被 purge，
history list length增长，回溯版本链的读也会变慢。二级索引更新会涉及 change buffer（对非唯一二级索引的写入缓冲）等机制，细节以文档为准。
PostgreSQL：
UPDATE实际是“插入新版本 + 标记旧版本过期”。旧版本（死元组）留在表里，需要 VACUUM 回收空间并更新可见性映射；autovacuum 默认开启，但在高更新负载的大表上需要调参（阈值、cost limit 等）。表膨胀（bloat）：如果 autovacuum 跟不上，或有长事务/长时间运行的复制槽阻止了死元组清理，表和索引就会膨胀，扫描变慢。
HOT 更新（Heap-Only Tuple）：当更新不涉及索引列，且同一页有空间时，可以避免产生新的索引条目，降低更新开销；表的
fillfactor可以预留页内空间。事务 ID 回卷：PG 的事务 ID 是有限的 32 位计数，需要 VACUUM 周期性“冻结”旧元组，否则在极端情况下会强制停写。监控
age(datfrozenxid)是 DBA 的基本功，开发者至少要知道长事务和禁用 autovacuum 是危险的。
-- PostgreSQL：查看死元组与 autovacuum 情况 SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; -- PostgreSQL：找出长事务 SELECT pid, now() - xact_start AS xact_age, state, left(query, 80) AS q FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 10;
-- MySQL：InnoDB 状态（含 history list length）与长事务 SHOW ENGINE INNODB STATUS\G SELECT trx_id, trx_started, trx_mysql_thread_id, trx_rows_locked FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;
4. InnoDB 的锁：记录锁、间隙锁、临键锁
在 RR 级别下，InnoDB 的当前读会对扫描到的索引范围加锁：
记录锁（Record Lock）：锁定索引上的一条记录。
间隙锁（Gap Lock）：锁定索引记录之间的“空隙”，阻止其他事务向该间隙插入。
临键锁（Next-Key Lock）：记录锁 + 该记录之前的间隙，是 RR 下默认的加锁单位。
意向锁（Intention Lock）：表级的“我要在表里加行锁”的声明，用于和表锁快速兼容性判断。
插入意向锁：插入前在间隙上设置的一种特殊间隙锁，多个事务插入同一间隙的不同位置时互不阻塞。
一个直观的例子（RR 级别，假设 t 表的 idx_a 索引上有 a = 10, 20, 30）：
-- 会话 A BEGIN; SELECT * FROM t WHERE a BETWEEN 15 AND 25 FOR UPDATE; -- 加锁范围大致覆盖 (10, 20] 与 (20, 30) 附近的间隙（具体取决于索引、等值/范围、是否唯一） -- 会话 B INSERT INTO t (a) VALUES (18); -- 被阻塞，因为 18 落在被锁的间隙里
要点：
加锁是加在索引上的。如果条件没有走索引，InnoDB 可能扫描并锁住大量记录，效果近似锁表。没有合适索引的
UPDATE/DELETE是线上锁等待的头号来源。等值查询命中唯一索引的存在行，通常只加记录锁；未命中则会加间隙锁。
具体的加锁规则很复杂，且随版本略有变化。排查时不要靠记忆，使用
performance_schema.data_locks（8.0）观察。
-- MySQL 8.0：查看当前锁与等待关系 SELECT * FROM performance_schema.data_locks\G SELECT * FROM performance_schema.data_lock_waits\G -- 或使用 sys 视图 SELECT * FROM sys.innodb_lock_waits\G
PostgreSQL 的锁：行级锁（FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE）、表级锁（共 8 种模式，例如 ACCESS SHARE、ROW EXCLUSIVE、ACCESS EXCLUSIVE）。PG 没有 InnoDB 那样的间隙锁，但 SERIALIZABLE 使用谓词锁（SIREAD）检测冲突。DDL 往往需要 ACCESS EXCLUSIVE 锁，会和所有读写冲突，这是“在线变更”要特别注意的地方（见后文）。
实用的并发控制语法：
-- 任务队列：多个 worker 各取一批，互不阻塞（MySQL 8.0+ 与 PG 9.5+ 均支持） SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED; -- 不想等待，直接失败 SELECT * FROM account WHERE id = 1 FOR UPDATE NOWAIT;
5. 死锁
死锁是两个或多个事务互相持有对方需要的锁，形成循环等待。
事务 A: UPDATE account SET ... WHERE id = 1; -- 持有 id=1 的锁 事务 B: UPDATE account SET ... WHERE id = 2; -- 持有 id=2 的锁 事务 A: UPDATE account SET ... WHERE id = 2; -- 等待 B 事务 B: UPDATE account SET ... WHERE id = 1; -- 等待 A → 死锁
InnoDB 会主动检测死锁，选择一个“代价较小”的事务回滚（错误码 1213，
Deadlock found when trying to get lock; try restarting transaction）。可用SHOW ENGINE INNODB STATUS的LATEST DETECTED DEADLOCK部分查看最近一次死锁，或开启innodb_print_all_deadlocks把所有死锁写入错误日志。PostgreSQL 在等待锁超过
deadlock_timeout（默认 1 秒）后触发死锁检测，被选中的事务报错deadlock detected（SQLSTATE40P01），详情在服务器日志中。
降低死锁概率的做法：
多行更新时按固定顺序（例如按主键升序）加锁。
缩短事务，尽早提交。
给
WHERE条件建合适的索引，减少锁范围。评估使用
READ COMMITTED以减少间隙锁（InnoDB）。批量更新时对 ID 排序后再操作。
应用层对死锁错误做有限次数的重试（幂等前提下），并加随机退避。
6. 锁等待与超时
InnoDB：
innodb_lock_wait_timeout（默认 50 秒）控制行锁等待时间，超时只回滚当前语句而不是整个事务（除非开启innodb_rollback_on_timeout），应用需要明确处理。错误码 1205。PostgreSQL：
lock_timeout控制获取锁的等待时间，statement_timeout控制语句总时间，idle_in_transaction_session_timeout终止“开启事务后什么也不做”的会话——这个参数对于避免应用 bug 造成的长事务非常有用。
-- PostgreSQL：为迁移、DDL 设置保护 SET lock_timeout = '3s'; SET statement_timeout = '60s'; ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min'; SELECT pg_reload_conf();
-- MySQL：会话级调整 SET SESSION innodb_lock_wait_timeout = 5;
定位谁在阻塞谁：
-- PostgreSQL：查看阻塞关系 SELECT a.pid AS blocked_pid, pg_blocking_pids(a.pid) AS blocked_by, a.query FROM pg_stat_activity a WHERE cardinality(pg_blocking_pids(a.pid)) > 0; -- MySQL 8.0：通过 sys 视图 SELECT waiting_pid, waiting_query, blocking_pid, blocking_query FROM sys.innodb_lock_waits;
7. 乐观锁与悲观锁
悲观锁：先加锁再改，
SELECT ... FOR UPDATE。适合冲突概率高、临界区短的场景，要注意事务长度与锁范围。乐观锁：用版本号（或更新时间）做条件更新，冲突时重试或报错。适合冲突较少的场景（如用户编辑资料）。
-- 乐观锁：version 条件更新，影响行数为 0 表示被别人改过 UPDATE product SET stock = stock - 1, version = version + 1 WHERE id = 1 AND version = 7 AND stock > 0;
库存扣减场景更简单的写法是直接 UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock > 0，用影响行数判断；热点行（秒杀）的高并发争抢会造成行锁排队，需要配合缓存预扣、队列削峰等架构手段，这已超出单纯 SQL 的范畴。
五、SQL 编写与执行计划
1. 写 SQL 的基本习惯
不要
SELECT *：多读无用列、无法使用覆盖索引、表结构变化时容易出现隐性问题。 明确列出
INSERT的列名。WHERE条件尽量让索引能用（见上一节失效情形）。JOIN 的关联列要有索引且类型/字符集一致。
小表驱动大表 是常见说法，但优化器会自己选择连接顺序；更重要的是保证被驱动表的关联列有索引。
IN列表不要过长；很长的列表改用临时表/JOIN，或分批。COUNT(*)：InnoDB 与 PG 都需要扫描（MVCC 导致无法直接存储精确总数），大表的精确计数代价高。如需大致数量，PG 可以参考 pg_class.reltuples（估算值），业务上则考虑维护计数器或缓存。ORDER BY+LIMIT：能走索引顺序时非常快；否则要排序全部匹配行。DISTINCT/GROUP BY的代价：可能产生临时表或排序，检查执行计划。避免在循环里发 SQL（见 N+1）。
2. 读懂 EXPLAIN（MySQL）
EXPLAIN SELECT o.id, o.amount_cent FROM orders o WHERE o.user_id = 1001 AND o.status = 1 ORDER BY o.created_at DESC LIMIT 20;
示意输出（数值仅用于说明格式）：
+----+-------+-------+-------------------------+---------+------+------+-------------+ | id | table | type | key | key_len | ref | rows | Extra | +----+-------+-------+-------------------------+---------+------+------+-------------+ | 1 | o | ref | idx_user_status_created | 9 | const,const | 120 | Backward index scan | +----+-------+-------+-------------------------+---------+------+------+-------------+
重点字段：
| 字段 | 含义与关注点 |
|---|---|
type |
访问类型，从好到差大致：system > const > eq_ref > ref > range > index > ALL。ALL 是全表扫描，index 是全索引扫描，都要警惕 |
possible_keys / key |
可能用到的索引 / 实际选择的索引 |
key_len |
实际使用的索引长度，用来判断组合索引用到了前几列 |
rows |
优化器估算要扫描的行数（不是实际值） |
filtered |
估算有多少比例的行在表条件过滤后留下 |
Extra |
关键提示：Using index（Using index condition（ICP）、Using where、Using filesort（额外排序）、Using temporary（临时表） |
Using filesort 不一定是“文件排序”，它表示没有利用索引顺序，需要额外排序；数据量小时在内存里完成。Using temporary 常见于 GROUP BY/DISTINCT 无法利用索引时。
MySQL 8.0.18 起提供 EXPLAIN ANALYZE，会真正执行语句并给出实际耗时和行数；EXPLAIN FORMAT=TREE 以树形展示。对 UPDATE/DELETE 做 EXPLAIN ANALYZE 请格外小心（它会真的执行，是否支持写语句以所用版本为准，可放入事务中测试后 ROLLBACK）。
3. 读懂 EXPLAIN (ANALYZE, BUFFERS)（PostgreSQL）
EXPLAIN (ANALYZE, BUFFERS) SELECT id, amount_cent FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY created_at DESC LIMIT 20;
示意输出：
Limit (cost=0.43..45.10 rows=20 width=16) (actual time=0.030..0.080 rows=20 loops=1) Buffers: shared hit=24 -> Index Scan Backward using idx_orders_user_status_created on orders (cost=0.43..270.00 rows=120 width=16) (actual time=0.028..0.070 rows=20 loops=1) Index Cond: ((user_id = 1001) AND (status = 1)) Buffers: shared hit=24 Planning Time: 0.150 ms Execution Time: 0.110 ms
怎么读：
从最内层、缩进最深的节点往外读；每个节点有
cost=启动成本..总成本、rows（估算）、width（每行平均字节数）。加了
ANALYZE后有actual time、实际rows、loops。估算 rows 与实际 rows 相差悬殊，通常说明统计信息不准或存在相关性列，是计划不佳的头号线索。注意
loops：节点的actual time和rows是每次循环的平均值，总耗时要乘以 loops。Buffers: shared hit=…, read=…：命中缓存的页数与从磁盘（或 OS 缓存）读取的页数。常见节点：
Seq Scan（顺序扫描）、Index Scan、Index Only Scan、Bitmap Index Scan+Bitmap Heap Scan、Nested Loop、Hash Join、Merge Join、Sort、HashAggregate、Gather（并行）。Rows Removed by Filter很大说明索引没把范围缩小到位。Sort Method: external merge Disk: …说明排序溢出到磁盘，可考虑索引或调整work_mem（会话级谨慎调整，注意它是每个排序/哈希节点各自使用的上限）。
EXPLAIN ANALYZE会真正执行语句。对INSERT/UPDATE/DELETE使用时，务必放在事务中并ROLLBACK：BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;
4. 慢查询分析流程
打开并采集：
MySQL：
slow_query_log=ON、long_query_time（如 1 秒，按业务调）、可开启log_queries_not_using_indexes（噪声较大，谨慎）。用pt-query-digest或performance_schema.events_statements_summary_by_digest聚合。PostgreSQL：
log_min_duration_statement；启用扩展pg_stat_statements按总耗时、平均耗时、调用次数排名；auto_explain可自动记录慢语句的执行计划。先抓“总耗时”最高的，而不是“单次最慢”的：一个每次 5 毫秒但每秒调用上万次的语句，可能比偶发的 3 秒报表更值得优化。
EXPLAIN 看计划，确认是否走了期望的索引、是否有大量回表/过滤/排序。
检查统计信息与数据分布：必要时
ANALYZE；PG 可对倾斜列调整ALTER TABLE ... ALTER COLUMN ... SET STATISTICS；MySQL 8.0 支持直方图（ANALYZE TABLE ... UPDATE HISTOGRAM）。改写 SQL 或调整索引，在接近生产的数据量下验证。
回归验证：上线后确认慢查询确实消失，且没有拖慢写入。
留意非 SQL 因素：锁等待、连接池耗尽、网络、磁盘、参数、ORM 生成了你没想到的 SQL。
-- PostgreSQL：pg_stat_statements 排名（需 shared_preload_libraries 配置并 CREATE EXTENSION） SELECT calls, round(total_exec_time::numeric, 1) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 100) AS q FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
（pg_stat_statements 的列名在 PG 13 前后有变化：13 起为 total_exec_time/mean_exec_time，更早为 total_time/mean_time，以所用版本文档为准。）
-- MySQL：按摘要查看总耗时最高的语句（耗时单位为皮秒） SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_sec, ROUND(AVG_TIMER_WAIT/1e12, 6) AS avg_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
5. 分页与深分页
最常见的分页写法：
SELECT id, title FROM article ORDER BY id DESC LIMIT 20 OFFSET 100000;
问题在于 OFFSET 100000 需要先找到并丢弃前 10 万行，偏移越大越慢，且页数大时对数据库压力明显。优化思路：
方案一：键集分页（Keyset / Seek Pagination）——记住上一页最后一条的排序键，下一页直接从它之后开始：
-- 第一页
SELECT id, title FROM article ORDER BY id DESC LIMIT 20;
-- 下一页：带上上一页最后一条的 id
SELECT id, title FROM article WHERE id < 98765 ORDER BY id DESC LIMIT 20;
-- 排序键不唯一时，用 (created_at, id) 复合排序保证稳定
-- PostgreSQL 与 MySQL 8 均支持行值比较，但 MySQL 对行值比较的索引利用请用 EXPLAIN 验证
SELECT id, title FROM article
WHERE (created_at, id) < ('2026-10-01 12:00:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;
优点：性能与页码无关，翻页稳定（不会因为插入造成重复/遗漏）。缺点：不能直接跳到第 N 页，只适合“上一页/下一页”或“无限滚动”。
方案二：延迟关联（Deferred Join）——先用
SELECT a.id, a.title, a.content_summary FROM article a JOIN ( SELECT id FROM article ORDER BY id DESC LIMIT 20 OFFSET 100000 ) t ON a.id = t.id ORDER BY a.id DESC;
子查询只扫描索引（不回表），仍然需要跳过 10 万个索引项，但比跳过 10 万个完整行轻得多。
方案三：限制深度与产品层面妥协——不允许跳到很深的页（如最多 100 页），搜索类场景用搜索引擎；导出类需求走异步任务。
方案四：总数统计——SELECT COUNT(*) 在大表上很昂贵，可以不显示精确总数（只显示“有下一页”），或使用缓存/估算值。
6. N+1 查询
典型表现：先查一个列表（1 次），再对每个元素各发一次查询（N 次）。
SELECT * FROM orders WHERE user_id = 1001; -- 1 次，返回 50 条 for each order: SELECT * FROM order_item WHERE order_id = ?; -- 50 次
在 ORM（JPA/Hibernate 的懒加载、MyBatis 的嵌套查询）中非常隐蔽。修复方式：
批量查询：收集 ID 后一次
IN查询，再在内存中组装。JOIN：一次查出（注意一对多会放大结果行数，分页时尤其要小心）。
ORM 提供的预加载：如 JPA 的
JOIN FETCH、@EntityGraph、@BatchSize；MyBatis 使用resultMap的collection以 JOIN 方式加载，或自己做批量查询。本地/分布式缓存：对小而稳定的字典数据。
-- 批量查询代替 N 次查询 SELECT * FROM order_item WHERE order_id IN (101, 102, 103, /* ... */ 150);
发现办法：在测试环境开启 SQL 日志，观察一次接口调用产生了多少条 SQL；用 APM 或 pg_stat_statements 查看“调用次数异常多”的简单语句。
7. 批量操作
批量插入：一条多行
INSERT，而不是循环单行：
INSERT INTO order_item (order_id, sku_id, qty) VALUES (101, 1, 2), (101, 2, 1), (101, 3, 5);
单条语句不要无限大：受
max_allowed_packet（MySQL）等限制，且过大的事务会占用大量日志与锁。常见做法是每批几百到几千行，根据实际行大小与环境测试决定。JDBC 批处理：使用
addBatch/executeBatch。MySQL Connector/J 需要开启rewriteBatchedStatements=true才能把批量语句改写成多行插入；PostgreSQL JDBC 驱动有reWriteBatchedInserts=true参数达到类似效果。参数名以所用驱动版本文档为准。PostgreSQL 的
COPY是大批量导入的首选；MySQL 有LOAD DATA INFILE（受权限与安全配置限制，local_infile默认可能关闭，且有安全风险，需谨慎）。大批量更新/删除要分批：一次性
DELETE千万行会产生巨大的事务、锁、undo/WAL 日志和主从延迟。分批做，每批提交，批次间可短暂休眠：
-- MySQL：支持 DELETE ... LIMIT DELETE FROM log_event WHERE created_at < '2026-01-01' LIMIT 5000; -- 循环执行直到影响行数为 0 -- PostgreSQL：DELETE 不支持 LIMIT，用子查询（CTE） WITH doomed AS ( SELECT id FROM log_event WHERE created_at < '2026-01-01' ORDER BY id LIMIT 5000 ) DELETE FROM log_event l USING doomed d WHERE l.id = d.id;
清理历史数据优先考虑分区表按分区删除。
8. Upsert：存在则更新，否则插入
MySQL：INSERT ... ON DUPLICATE KEY UPDATE，当主键或任一唯一索引冲突时执行 UPDATE。
INSERT INTO user_stat (user_id, login_count, last_login_at) VALUES (1001, 1, NOW()) ON DUPLICATE KEY UPDATE login_count = login_count + 1, last_login_at = VALUES(last_login_at);
注意：VALUES() 函数在 MySQL 8.0.20 起被标记为不推荐，推荐使用行别名写法（INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE col = new.col），具体以所用版本文档为准。另外，如果表上存在多个唯一索引，冲突的可能是任意一个，行为不易推理；并发下还可能引发间隙锁/死锁。REPLACE INTO 是“先删后插”，会改变自增 ID、触发删除与插入两套逻辑、对外键不友好，一般不推荐用作 upsert。
PostgreSQL：INSERT ... ON CONFLICT (冲突目标) DO UPDATE / DO NOTHING，必须指明冲突的唯一索引或约束。
INSERT INTO user_stat (user_id, login_count, last_login_at)
VALUES (1001, 1, now())
ON CONFLICT (user_id)
DO UPDATE SET
login_count = user_stat.login_count + 1,
last_login_at = EXCLUDED.last_login_at;
-- 仅忽略重复
INSERT INTO event_dedup (event_id) VALUES ('e-1') ON CONFLICT DO NOTHING;
-- 配合 RETURNING 拿到结果
INSERT INTO tag (name) VALUES ('mysql')
ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
RETURNING id;
PostgreSQL 15 起新增标准的 MERGE 语句，能处理更复杂的条件分支；17 起对 MERGE 又有增强（如 RETURNING），具体以所用版本文档为准。ON CONFLICT 的优点是语义明确、并发行为有保证（基于唯一索引的插入冲突检测）。
9. 窗口函数与 CTE
窗口函数在不折叠行的前提下做“分组内计算”。MySQL 8.0 起支持，5.7 不支持；PostgreSQL 早已支持。
-- 每个用户最近的 3 笔订单（TOP-N per group） SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn FROM orders o ) t WHERE rn <= 3; -- 排名与累计 SELECT user_id, amount_cent, RANK() OVER (ORDER BY amount_cent DESC) AS rk, SUM(amount_cent) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total, LAG(amount_cent) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount FROM orders;
常用函数：ROW_NUMBER、RANK、DENSE_RANK、LAG/LEAD、SUM/AVG ... OVER、FIRST_VALUE、NTILE。注意 ROW_NUMBER（无并列）、RANK（并列后跳号）、DENSE_RANK（并列后不跳号）的区别。窗口函数需要排序，大数据量下关注执行计划里的 Sort 与内存。
CTE（公用表表达式） 用 WITH 提高可读性，并支持递归：
-- 非递归：分步表达 WITH paid AS ( SELECT user_id, SUM(amount_cent) AS total FROM orders WHERE status = 2 GROUP BY user_id ) SELECT u.id, u.name, paid.total FROM users u JOIN paid ON paid.user_id = u.id WHERE paid.total > 100000; -- 递归：组织树 / 分类树遍历 WITH RECURSIVE tree AS ( SELECT id, parent_id, name, 1 AS depth FROM dept WHERE id = 1 UNION ALL SELECT d.id, d.parent_id, d.name, t.depth + 1 FROM dept d JOIN tree t ON d.parent_id = t.id ) SELECT * FROM tree;
版本差异：PostgreSQL 12 之前 CTE 总是“物化”（相当于优化屏障），12 起如果 CTE 无副作用且只被引用一次，默认会被内联，也可以用 MATERIALIZED / NOT MATERIALIZED 显式控制。MySQL 8.0 的 CTE 实现与优化策略则有自己的规则。递归 CTE 一定要确保有终止条件（环状数据要防止无限递归），MySQL 有 cte_max_recursion_depth 限制，PG 可用 CYCLE 子句（14+）检测环，具体以所用版本文档为准。
PostgreSQL 的 RETURNING 配合 CTE 可以写出“一条语句完成移动数据”的写法：
WITH moved AS ( DELETE FROM jobs WHERE status = 'done' AND finished_at < now() - interval '30 days' RETURNING * ) INSERT INTO jobs_archive SELECT * FROM moved;
六、连接池
1. 为什么需要连接池
建立数据库连接代价不小（TCP/TLS 握手、认证、会话初始化），而数据库端为每个连接也要消耗资源：MySQL 每连接一个线程，PostgreSQL 每连接一个进程（内存开销与上下文切换更明显）。如果每次请求都新建连接，或者应用实例成百上千个、每个实例开几十个连接，数据库会先被连接数拖垮，而不是被查询拖垮。
2. 应用侧连接池：HikariCP
Java 生态里最常用的是 HikariCP（Spring Boot 2.x 起默认）。几个核心参数：
| 参数 | 含义 | 建议 |
|---|---|---|
maximumPoolSize |
池中最大连接数 | 不是越大越好。从较小值起步，结合数据库核数、查询耗时、并发度压测；多实例时要乘以实例数 |
minimumIdle |
最小空闲连接 | 一般与最大值一致（固定大小池）更可预测，具体按负载 |
connectionTimeout |
获取连接的等待超时 | 要明显小于上游请求超时，快速失败比无限排队好 |
idleTimeout |
空闲连接回收时间 | 仅在 minimumIdle < maximumPoolSize 时生效 |
maxLifetime |
连接最长存活时间 | 应小于数据库/中间件/防火墙的连接断开时间，避免用到“已被对端掐断”的连接 |
keepaliveTime |
保活探测间隔 | 防止空闲连接被网络设备回收（新版本提供） |
leakDetectionThreshold |
连接泄漏检测阈值 | 开发/测试期有助于发现忘记归还的连接 |
# Spring Boot 配置示意（数值需按实际压测确定，不是推荐值）
spring:
datasource:
url: jdbc:mysql://db-host:3306/shop?useUnicode=true&characterEncoding=utf8&connectionTimeZone=UTC
username: app_rw
password: ${DB_PASSWORD}
hikari:
maximum-pool-size: 20
minimum-idle: 20
connection-timeout: 3000
max-lifetime: 1500000
leak-detection-threshold: 20000
关于池大小，一个有用的思路：数据库真正能并行处理的查询数量受 CPU 核数与磁盘 I/O 限制，更多的连接不会带来更多的吞吐，只会带来更多排队与切换。池太大还会让一次故障（如慢 SQL）把所有连接占满、雪崩得更快。HikariCP 官方 wiki 有关于池大小的讨论，可以作为阅读材料，但数值仍应由你自己的压测得出。
常见连接池问题：
连接泄漏：手写 JDBC 忘记
close()；事务方法里长时间持有连接做外部调用。事务里调用外部服务：连接被占用却在等 HTTP 响应，池耗尽。
连接被服务端或中间件主动断开：
maxLifetime配置不当，出现Communications link failure/connection reset。池耗尽表现：线程阻塞在
getConnection，抛出超时异常；排查要看活跃/空闲/等待线程数指标，同时检查是否有慢 SQL 占着连接。
3. 服务端连接池：pgbouncer 与 ProxySQL
PostgreSQL 每连接一个进程，连接数通常控制在几百以内更稳妥（取决于硬件），当应用实例很多时，常在应用与数据库之间放置 pgbouncer，把大量客户端连接复用到少量服务端连接上。
pgbouncer 有三种池模式：
| 模式 | 含义 | 注意事项 |
|---|---|---|
| session | 客户端连接期间独占一个服务端连接 | 与直连语义最接近，复用效果有限 |
| transaction | 仅在事务期间占用服务端连接，事务结束即归还 | 最常用，但会话级特性不可靠：SET、会话级 advisory lock、LISTEN/NOTIFY、临时表、会话级预编译语句等 |
| statement | 每条语句后归还 | 不允许多语句事务，限制较多 |
在 transaction 模式下使用预编译语句：传统上 PgBouncer 不支持协议级预编译语句跨事务复用，较新版本做了支持，而 JDBC 驱动默认会在多次执行后自动切换为服务端预编译（prepareThreshold），可能导致 prepared statement does not exist 一类错误。具体要看所用的 pgbouncer 版本与驱动版本，以官方文档为准，必要时调整驱动参数（例如 prepareThreshold=0）。
; pgbouncer.ini 示意 [databases] shop = host=10.0.0.10 port=5432 dbname=shop [pgbouncer] listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 2000 default_pool_size = 20
MySQL 方面，常见方案是应用侧连接池 + 必要时 ProxySQL（读写分离、连接复用、查询路由）。云厂商托管数据库也常提供自带的代理/连接池服务。
4. 一个简单的算账方式
假设有 20 个应用实例，每个实例连接池最大 20，则数据库最多可能同时接到 400 个连接；再加上定时任务、运维工具、监控等。把这个总数与数据库的 max_connections（MySQL 与 PostgreSQL 均有）对照，并预留余量给管理员连接。扩容应用实例前，先算连接数。
七、复制与高可用基础
1. 为什么要复制
复制把数据变更同步到其他节点，用于：高可用（主库故障时切换）、读扩展（只读副本分担查询）、备份（从库上做备份）、异地容灾。
2. MySQL 复制
基于 binlog：主库把数据变更写入二进制日志（binlog），从库的 I/O 线程拉取并写入 relay log，SQL 线程（或并行回放线程）重放。
binlog 格式：
STATEMENT、ROW、MIXED。现代业务通常使用ROW（记录行变更，准确、对非确定性函数友好，但日志量更大）。GTID：全局事务标识，让主从切换时定位同步位点更简单，推荐启用。
同步方式：默认异步；半同步要求至少一个从库确认收到后才返回；MySQL Group Replication / InnoDB Cluster 提供基于组通信的多节点一致性方案。适用性与限制以所用版本文档为准。
主从延迟：从库回放跟不上主库。常见原因：大事务、从库配置较低、单线程回放（可配置并行回放）、长时间运行的查询或 DDL 阻塞回放。
3. PostgreSQL 复制
物理流复制（Streaming Replication）：基于 WAL（预写日志）的字节级复制，从库是主库的整库拷贝，可以只读（hot standby）。可配置同步复制（
synchronous_commit、synchronous_standby_names）。逻辑复制：基于发布/订阅，可以按表复制、跨大版本复制、与不同结构的目标对接。DDL 一般不自动同步，需额外处理；序列、大对象等需要额外关注。具体限制随版本演进（15/16/17 都有增强），以所用版本官方文档为准。
复制槽（Replication Slot）：保证主库保留从库尚未消费的 WAL。需要警惕：从库掉线而槽仍在，主库的 WAL 会持续堆积直到磁盘写满。务必监控复制槽延迟，PG 13 起可设置
max_slot_wal_keep_size限制。Hot standby 查询冲突：从库上的长查询可能与主库的 VACUUM 清理冲突，被取消或延迟回放，可用
hot_standby_feedback等参数权衡（会反过来影响主库膨胀）。
4. 读写分离的坑
写 ──► [ 主库 ] ──复制──► [ 从库1 ] ◄── 读 └─────► [ 从库2 ] ◄── 读
读到旧数据：刚写入就在从库读取，可能读不到（复制延迟）。常见处理：写后短时间内强制读主；关键业务读主；按业务容忍度使用延迟阈值路由；使用 GTID/LSN 等位点等待从库追上。
事务内读写要一致：同一个事务里不能一部分走主一部分走从。
不要把所有读都放到从库：对一致性敏感的查询（如下单前库存校验、支付状态确认）应读主。
从库也要监控：延迟、复制线程/进程状态、磁盘。
5. 高可用与故障切换
MySQL：常见组合有 MHA、Orchestrator、InnoDB Cluster（MySQL Router + Group Replication）、云厂商托管的主备。
PostgreSQL：常见组合有 Patroni（基于 etcd/Consul/ZooKeeper）、repmgr、pg_auto_
failover，以及云托管主备。 切换要关心的几个问题：
脑裂（两个节点都认为自己是主）、数据丢失窗口（异步复制切换可能丢失尚未同步的事务，即 RPO）、切换时长（RTO）、应用如何感知新主（VIP、DNS、代理、客户端重连）、旧主恢复后如何重新加入。 高可用不是备份。主从都会同步
DROP TABLE。
把“主库挂了，从库顶上”当作高可用的开始而不是结束：真正的高可用需要定期演练切换、验证应用重连、检查监控和告警。
八、备份与恢复
没有验证过能恢复的备份，不叫备份。
1. 先搞清楚几个概念
逻辑备份：导出 SQL 或数据文件（
mysqldump、pg_dump）。可读、跨版本较灵活、可只备份部分对象；但大库备份与恢复较慢。物理备份：直接复制数据文件（Percona XtraBackup、
pg_basebackup）。速度快，适合大库，但通常要求同版本/兼容的恢复环境。全量 / 增量 / 差异：全量备份是完整快照；增量只备份上次以来的变化；不同工具对增量的支持方式不同。
RPO 与 RTO：RPO（可容忍丢失多长时间的数据）、RTO（多久内必须恢复服务）。备份方案由它们决定，而不是反过来。
PITR（时间点恢复）：全量备份 + 持续归档的日志（MySQL binlog / PostgreSQL WAL），可以把数据恢复到某个具体时间点，例如“误删之前的那一秒”。
2. MySQL 备份
mysqldump（逻辑备份）：
# 一致性备份（InnoDB）：--single-transaction 使用一致性快照，不锁表 # --routines/--triggers/--events 包含存储过程、触发器、事件 # --source-data=2 记录 binlog 位点（旧版本参数名为 --master-data） mysqldump --single-transaction --routines --triggers --events \ --set-gtid-purged=OFF --source-data=2 \ -u backup_user -p shop > shop_$(date +%F).sql # 恢复 mysql -u root -p shop < shop_2026-10-01.sql
参数名随版本略有不同（例如 --master-data 在较新版本改名为 --source-data），使用 GTID 时还要考虑 --set-gtid-purged，以所用版本官方文档为准。--single-transaction 只对 InnoDB 保证一致，非事务表（MyISAM）不保证。
Percona XtraBackup（物理热备）：
# 全量备份 xtrabackup --backup --target-dir=/backup/full --user=backup_user --password=*** # 准备（应用 redo log，使备份一致） xtrabackup --prepare --target-dir=/backup/full # 恢复：停止 MySQL、清空 datadir 后拷回，再修正权限 xtrabackup --copy-back --target-dir=/backup/full chown -R mysql:mysql /var/lib/mysql
XtraBackup 的版本需要与 MySQL 版本匹配（具体对应关系以 Percona 文档为准）。MySQL 企业版另有自家的备份工具。
MySQL 的 PITR：全量备份 + binlog。恢复时先还原全量备份，再用 mysqlbinlog 回放到目标时间点之前：
mysqlbinlog --start-position=1234 --stop-datetime="2026-10-01 10:29:59" \ binlog.000123 binlog.000124 | mysql -u root -p
前提是：binlog 已开启并被持续归档到安全的地方，且保留时间覆盖了备份间隔。
3. PostgreSQL 备份
pg_dump（逻辑备份）：
# 自定义格式，可并行恢复、可选择性恢复 pg_dump -Fc -U backup_user -d shop -f shop_$(date +%F).dump # 目录格式支持并行备份 pg_dump -Fd -j 4 -U backup_user -d shop -f shop_dir # 恢复 createdb -U postgres shop_restore pg_restore -U postgres -d shop_restore -j 4 shop_2026-10-01.dump # 全局对象（角色、表空间）需要另行导出 pg_dumpall --globals-only -U postgres > globals.sql
pg_basebackup（物理备份）：
pg_basebackup -h primary-host -U replicator -D /backup/base \ -Ft -z -P --wal-method=stream
WAL 归档与 PITR：
# postgresql.conf（示意，生产环境请使用成熟的归档方案或 pgBackRest/Barman） wal_level = replica archive_mode = on archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
恢复到某个时间点（PG 12 起通过 recovery.signal 文件与配置参数；更早版本使用 recovery.conf，以所用版本为准）：
# 1. 停止服务，保留损坏的数据目录以便分析，取出 base backup 作为新的数据目录 # 2. 配置 restore_command 与 recovery_target_time # postgresql.conf（或 postgresql.auto.conf）： # restore_command = 'cp /archive/%f %p' # recovery_target_time = '2026-10-01 10:29:59+08' # recovery_target_action = 'promote' # 3. 创建 recovery.signal 后启动 touch $PGDATA/recovery.signal pg_ctl -D $PGDATA start
生产环境建议用 pgBackRest、Barman 这类成熟工具管理全量/增量备份、WAL 归档、保留策略与校验，而不是手写 cp 脚本。
4. 备份的纪律
3-2-1 原则：至少 3 份副本、2 种介质、1 份异地。
定期恢复演练：在隔离环境真的还原一次，并校验关键数据与应用能否启动。记录恢复耗时，评估 RTO。
备份也要监控和告警：备份任务失败、备份文件大小异常、归档中断（WAL 或 binlog 断档）都应该告警。
加密与访问控制：备份包含全部数据，要加密存储，访问权限最小化。
别把备份放在同一块盘/同一台机器。
误删恢复的现实：全量备份加日志回放能恢复到误操作前，但需要时间；很多团队也会配置延迟从库（例如延迟几小时）作为“后悔药”。
云托管的自动备份：了解保留时长、是否支持 PITR、恢复是新建实例还是原地覆盖。
九、在线 DDL 与迁移安全
大表变更是线上事故的高发区。核心问题是：这条 DDL 会不会长时间持有阻塞读写的锁？会不会重写整张表？会不会造成复制延迟？
1. MySQL 的 Online DDL
InnoDB 在 5.6 之后支持多种在线 DDL，通过 ALGORITHM 与 LOCK 子句显式声明：
-- 明确指定算法和锁级别，如果该操作不被支持就会直接报错，而不是悄悄变成锁表 ALTER TABLE orders ADD COLUMN remark VARCHAR(255) NULL, ALGORITHM=INPLACE, LOCK=NONE; -- 8.0 起部分操作支持 INSTANT（如在表末尾加列，只改元数据） ALTER TABLE orders ADD COLUMN remark2 VARCHAR(255) NULL, ALGORITHM=INSTANT;
INSTANT：仅修改元数据，几乎瞬时（8.0.12 起支持部分操作，后续版本逐步扩展；支持范围以所用版本文档为准）。 INPLACE：不拷贝整表，但可能需要重建表（如修改列类型、删除主键），期间并发 DML 通过日志追加，最后有短暂的元数据锁。 COPY：创建临时表拷贝全部数据，期间阻塞写入，应避免。元数据锁（MDL）陷阱：DDL 开始和结束时需要获取表的 元数据锁。如果该表上有一个长事务或长查询持有 MDL 共享锁，DDL 会等待；而 DDL 在排队时，后续所有对该表的查询又会被它阻塞（因为 MDL 请求排队是先来先服务），于是业务全部卡住。因此 DDL 前先检查长事务，并设置 lock_wait_timeout让 DDL 自己快速失败。
SET SESSION lock_wait_timeout = 5; -- DDL 等待元数据锁最多 5 秒 ALTER TABLE orders ADD INDEX idx_x (x), ALGORITHM=INPLACE, LOCK=NONE;
对于必须重建的超大表，业界常用 gh-ost（基于 binlog，无触发器）或 pt-online-schema-change（基于触发器）：创建影子表 → 拷贝数据 → 同步增量 → 原子切换。使用时要评估对主从延迟、磁盘空间、外键与触发器的影响。
2. PostgreSQL 的 DDL 安全
PostgreSQL 的 DDL 可以在事务中执行并回滚，但许多 ALTER TABLE 需要 ACCESS EXCLUSIVE 锁，会与所有读写冲突。同样存在“排队效应”：DDL 在等待锁时，后续的普通查询也会排在它后面。
保护手段：
-- 在迁移脚本开头设置，拿不到锁就快速失败，由迁移工具重试 SET lock_timeout = '3s'; SET statement_timeout = '5min';
常见操作的安全做法（是否需要重写表与具体行为随版本不同，以所用版本官方文档为准）：
| 操作 | 注意事项与安全做法 |
|---|---|
| 添加可空列 | 通常很快（仅改目录 |
| 添加带默认值的列 | PG 11 起，常量默认值不再重写整表；更早版本会重写；易变默认值（如 random()）仍可能重写 |
| 创建索引 | 用 CREATE INDEX CONCURRENTLY，不阻塞写入；不能在事务块内；失败会遗留 INVALID 索引需手动 DROP INDEX 后重试 |
添加 NOT NULL |
直接 SET NOT NULL 会全表扫描并持有强锁。先 ADD CONSTRAINT ... CHECK (col IS NOT NULL) NOT VALID，再 VALIDATE CONSTRAINT（只需较弱的锁），PG 12 起在已有有效 CHECK 约束时 SET NOT NULL 可跳过扫描 |
| 添加外键 | ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID，再单独 VALIDATE CONSTRAINT |
| 修改列类型 | 许多情况需要重写整表（部分类型转换如放宽 varchar 长度不需要）；大表采用“新增列 → 双写 → 回填 → 切换 → 删除旧列” |
| 删除列 | 仅标记删除，速度快，但应用需先停止使用该列 |
| 重命名列/表 | 瞬间完成，但会使应用中引用旧名称的 SQL 立刻失败，需分阶段发布 |
-- PostgreSQL：安全地给大表加 NOT NULL ALTER TABLE orders ADD CONSTRAINT orders_remark_nn CHECK (remark IS NOT NULL) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT orders_remark_nn; ALTER TABLE orders ALTER COLUMN remark SET NOT NULL; -- 有已验证的 CHECK 时可跳过全表扫描（PG 12+） ALTER TABLE orders DROP CONSTRAINT orders_remark_nn;
3. 迁移的通用安全守则：扩展—收缩（Expand / Contract）
不要在一次发布里同时改表结构和代码。把变更拆成向后兼容的多步：
1. 扩展：新增列/新表（可空或有默认值），不影响旧代码 2. 发布新代码：双写（新旧都写），读仍读旧 3. 回填：分批把历史数据迁移到新结构（控制批大小与速度） 4. 发布新代码：读新结构 5. 观察一段时间后收缩：删除旧列/旧表、旧索引
其他守则：
迁移脚本纳入版本管理（Flyway、Liquibase、golang-migrate、Alembic 等），可重复、可审查，不要手工在生产上敲 DDL。
回滚方案要提前准备：哪些变更是可回滚的？数据回填是否可逆？
先在与生产相似的数据量上演练，估算耗时与锁影响。
选择低峰、有人值守的时间窗口，并有监控（锁等待、复制延迟、错误率）。
分批回填，每批控制事务大小，并观察复制延迟，必要时限速。
兼容性：旧版本应用实例和新版本同时存在（滚动发布期间），表结构必须同时兼容两者。
十、监控：知道数据库“现在怎么样”
不要等用户反馈慢才看数据库。至少应该监控以下几类指标：
| 类别 | 指标 | MySQL 来源 | PostgreSQL 来源 |
|---|---|---|---|
| 可用性 | 实例存活、主从角色、复制状态 | SHOW REPLICA STATUS（旧版 SHOW SLAVE STATUS） |
pg_stat_replication、pg_is_in_recovery() |
| 复制延迟 | 延迟秒数/字节数 | Seconds_Behind_Source（要结合实际理解，不总是准确） |
pg_stat_replication 的 replay_lag、LSN 差值 |
| 连接 | 当前连接/最大连接、活跃线程 | Threads_connected、Threads_running |
pg_stat_activity |
| 吞吐与延迟 | QPS/TPS、慢查询数、语句耗时分布 | Questions、慢日志、performance_schema |
pg_stat_statements、pg_stat_database |
| 锁与事务 | 锁等待、死锁数、长事务 | innodb_trx、sys.innodb_lock_waits |
pg_locks、pg_stat_activity、pg_stat_database.deadlocks |
| 缓存 | 缓冲池命中、读盘情况 | InnoDB buffer pool 相关状态 | pg_statio_*、blks_hit/blks_read |
| 存储 | 磁盘使用与增长趋势、表和索引大小 | information_schema.tables |
pg_total_relation_size() |
| 维护 | purge 延迟/History list length；VACUUM/膨胀、事务 ID 年龄 | SHOW ENGINE INNODB STATUS |
pg_stat_user_tables、age(datfrozenxid) |
| 日志归档 | binlog / WAL 是否持续归档，复制槽积压 | binlog 文件与磁盘 | pg_replication_slots、归档状态 pg_stat_archiver |
工具方面：Prometheus + mysqld_exporter / postgres_exporter + Grafana 是常见组合；Percona Monitoring and Management（PMM）同时支持 MySQL 与 PostgreSQL；云厂商也提供自带的监控。告警要分级且可执行：磁盘将满、复制中断、归档失败、连接数接近上限、长事务超过阈值，这些应该有人被叫醒；而 CPU 抖动一下不必。
一份最小化的“日常体检”SQL：
-- PostgreSQL：数据库大小、Top 表大小 SELECT pg_size_pretty(pg_database_size(current_database())); SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10; -- 未被使用的索引（注意统计重置时间和从库上的使用情况） SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;
-- MySQL：库表大小排名 SELECT table_schema, table_name, ROUND((data_length + index_length)/1024/1024, 1) AS size_mb FROM information_schema.tables ORDER BY (data_length + index_length) DESC LIMIT 10; -- 当前正在执行的会话 SHOW FULL PROCESSLIST;
十一、安全
1. 最小权限
应用账号与管理账号分离：应用使用的账号只授予它需要的权限（通常是对业务库的
SELECT/INSERT/UPDATE/DELETE），不要给DROP、ALTER、GRANT、SUPER/超级用户权限；迁移用单独的账号，只在发布时使用。读写账号分离：只读报表、BI 使用只读账号，最好连接只读副本。
按来源限制：MySQL 的账号包含主机部分（
'app'@'10.0.%'）；PostgreSQL 通过pg_hba.conf限制来源地址、数据库与认证方式。不要使用 root/postgres 账号跑应用。
凭据管理：密码不要写进代码仓库，使用密钥管理系统或环境变量注入，定期轮换。
-- MySQL CREATE USER 'app_rw'@'10.0.%' IDENTIFIED BY '********'; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_rw'@'10.0.%'; CREATE USER 'app_ro'@'10.0.%' IDENTIFIED BY '********'; GRANT SELECT ON shop.* TO 'app_ro'@'10.0.%';
-- PostgreSQL CREATE ROLE app_rw LOGIN PASSWORD '********'; GRANT CONNECT ON DATABASE shop TO app_rw; GRANT USAGE ON SCHEMA public TO app_rw; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw; -- 对以后新建的表也生效 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw; -- PG 15 起，普通用户默认不再拥有 public 模式的 CREATE 权限，旧版本需按需收回 -- REVOKE CREATE ON SCHEMA public FROM PUBLIC;
2. SQL 注入
SQL 注入的本质是把用户输入当作 SQL 代码的一部分拼接执行。
// 危险：字符串拼接
String sql = "SELECT * FROM users WHERE name = '" + name + "'";
// 输入 ' OR '1'='1 就会改变语义；更恶意的输入可能执行任意语句
// 安全：参数化查询（PreparedStatement）
PreparedStatement ps = conn.prepareStatement("SELECT id, name FROM users WHERE name = ?");
ps.setString(1, name);
防护要点：
一律使用参数化查询/预编译语句；ORM 的参数绑定同理。MyBatis 中
#{}是参数绑定，${}是字符串拼接——${}只能用于经过白名单校验的标识符。动态表名、列名、排序字段无法用参数绑定，必须使用白名单映射：
Map<String, String> sortable = Map.of("time", "created_at", "amount", "amount_cent");
String col = sortable.getOrDefault(req.getSort(), "created_at"); // 不在白名单就用默认值
String dir = "desc".equalsIgnoreCase(req.getDir()) ? "DESC" : "ASC";
String sql = "SELECT id FROM orders WHERE user_id = ? ORDER BY " + col + " " + dir;
LIKE参数中的%、_也要按需转义，避免被用来构造“全匹配”查询造成性能问题。最小权限是最后一道防线：即使被注入，应用账号也无法
DROP表或读取其他库。不要把数据库错误细节（SQL 语句、表名）直接返回给用户。
3. 加密与敏感数据
传输加密：开启 TLS，客户端校验服务端证书；MySQL 可用
require_secure_transport，PostgreSQL 在pg_hba.conf使用hostssl。存储加密：磁盘/卷加密，或数据库自身的静态加密（MySQL 的 InnoDB 表空间加密、PostgreSQL 原生并不提供透明数据加密，通常靠文件系统/云盘加密或第三方方案；各版本和发行版是否提供以官方文档为准）。托管服务一般提供 KMS 集成的静态加密。
敏感字段：手机号、身份证号、银行卡号等，按合规要求脱敏展示、必要时应用层加密或使用专用服务；密码只存加盐的慢哈希（bcrypt、scrypt、Argon2），绝不可存明文或简单 MD5/SHA。
日志脱敏：SQL 日志、慢日志、应用日志中避免打印敏感参数。
备份同样需要加密，访问权限同样需要审计。
审计：对高权限账号的操作、DDL、敏感表访问做审计；MySQL 企业版/Percona 有审计插件，PostgreSQL 有
pgaudit扩展，是否可用取决于发行版与托管平台。认证方式：PostgreSQL 推荐
scram-sha-256（10 起支持，14 起默认密码加密为 scram）；MySQL 8.0 默认认证插件为caching_sha2_password，旧客户端可能不兼容，需关注驱动版本。以所用版本文档为准。
十二、常见坑清单
下面是一份可以直接在评审时对照的清单，分成设计、查询、事务、运维四组。
设计
☐ 每张表有主键；主键短且尽量趋势递增；没有把业务字段当主键。
☐ 字符集统一
utf8mb4（MySQL）/ UTF8（PG），连接字符集一致。☐ 金额使用
DECIMAL或整数最小单位，没有用浮点数。☐ 时间字段类型和时区策略明确（
timestamptz/ UTC），范围查询用半开区间。☐ 业务唯一性由唯一索引保证，而不是“先查后插”。
☐ 软删除与唯一约束的冲突有处理方案。
☐ 大文本/JSON 不在列表查询里被
SELECT *带出。
查询与索引
☐ 高频查询都用
EXPLAIN验证过，没有意外的全表扫描、filesort或大量回表。☐ 没有对索引列做函数/运算、隐式类型或字符集转换。
☐ 组合索引顺序与查询匹配，没有冗余、重复索引。
☐ 深分页使用键集分页或延迟关联。
☐ 没有 N+1，循环里没有 SQL。
☐ 批量写入分批，大删除/大更新分批提交。
☐ 外键列（或关联列）有索引。
事务与并发
☐ 事务短小，事务内没有远程调用。
☐ 多行更新有固定的加锁顺序；对死锁和序列化失败有重试。
☐
UPDATE/DELETE的条件走索引，避免锁住过多行。☐ 设置了合理的锁等待与语句超时。
☐ 库存、余额等使用条件更新或乐观锁，而不是读-改-写。
☐ 监控并处理长事务（PG：
idle in transaction）。
运维与安全
☐ 连接池大小经过压测，总连接数不超过数据库上限。
☐ 有备份、有 PITR、做过恢复演练。
☐ 复制延迟、复制槽、归档、磁盘空间有告警。
☐ DDL 变更经过评审与演练，使用在线方式，设置了锁超时。
☐ 应用账号最小权限，使用参数化查询，传输加密开启。
☐ 慢查询日志/
pg_stat_statements已启用并定期复盘。☐ 主要版本升级有计划：兼容性、驱动、参数默认值变化已阅读发布说明。
十三、MySQL 与 PostgreSQL 综合对比表
下表是一个“速览”，细节会随版本变化，请以所用版本官方文档为准。
| 方面 | MySQL（InnoDB） | PostgreSQL |
|---|---|---|
| 默认事务隔离级别 | REPEATABLE READ | READ COMMITTED |
| RR 下的幻读 | 当前读用临键锁防止；快照读靠 ReadView | RR 为快照隔离，无幻读，但可能序列化失败 |
| undo log，由 purge 清理 | 表内死元组，由 VACUUM 清理 | |
| 主键与行存放 | 聚簇索引，二级索引存主键 | 堆表，索引存元组位置 |
| 自增 | AUTO_INCREMENT |
IDENTITY / SERIAL（序列） |
| 布尔类型 | TINYINT(1) 别名 |
原生 BOOLEAN |
| 无符号整数 | 支持 | 不支持 |
| 字符串类型 | VARCHAR/TEXT 系列，TEXT 不能有普通默认值 |
text/varchar，差别很小；大值走 TOAST |
| 时间类型 | DATETIME、TIMESTAMP |
timestamp、timestamptz |
| JSON | JSON，生成列/多值索引加速 |
JSONB + GIN，操作符丰富 |
| 数组/范围类型 | 无原生数组 | 原生数组、范围类型 |
| 部分索引 / 表达式索引 | 无部分索引；8.0.13+ 函数索引 | 都支持 |
| 其他索引类型 | 全文、空间、哈希（有限） | GIN、GiST、SP-GiST、BRIN、Hash |
| 事务性 DDL | 基本没有（隐式提交） | 绝大多数 DDL 可回滚 |
| 在线建索引 | Online DDL | CREATE INDEX CONCURRENTLY |
| upsert | ON DUPLICATE KEY UPDATE |
ON CONFLICT，较新版本有 MERGE |
| 返回修改后的行 | 无 RETURNING（MariaDB 有） |
RETURNING |
| 窗口函数 / CTE | 8.0+ | 完整支持 |
| 查询计划工具 | EXPLAIN、EXPLAIN ANALYZE（8.0.18+） |
EXPLAIN (ANALYZE, BUFFERS) |
| 连接模型 | 线程 | 进程，常配 pgbouncer |
| 复制 | binlog 异步/半同步/MGR | WAL 流复制 + 逻辑复制 |
| 物理备份工具 | XtraBackup | pg_basebackup、pgBackRest、Barman |
| PITR | 全量 + binlog | base backup + WAL 归档 |
| 扩展生态 | 相对有限 | 丰富（PostGIS、pg_trgm、pgvector 等） |
| 日常维护关注点 | 长事务、undo、主从延迟、MDL | VACUUM/膨胀、事务 ID、复制槽、长事务 |
十四、速查表：常用命令与 SQL
1. 连接与基本信息
| 目的 | MySQL | PostgreSQL |
|---|---|---|
| 命令行连接 | mysql -h host -P 3306 -u user -p db |
psql -h host -p 5432 -U user -d db |
| 查看版本 | SELECT VERSION(); |
SELECT version(); |
| 列出数据库 | SHOW DATABASES; |
\l |
| 切换数据库 | USE db; |
\c db |
| 列出表 | SHOW TABLES; |
\dt |
| 查看表结构 | DESC t; / SHOW CREATE TABLE t\G |
\d+ t |
| 查看索引 | SHOW INDEX FROM t; |
\di 或 \d t |
| 当前连接/会话 | SHOW FULL PROCESSLIST; |
SELECT * FROM pg_stat_activity; |
| 终止会话 | KILL <id>;（KILL QUERY <id> 仅终止语句） |
SELECT pg_cancel_backend(pid);（取消查询）/ pg_terminate_backend(pid)（终止连接） |
| 查看参数 | SHOW VARIABLES LIKE '%timeout%'; |
SHOW ALL; / SHOW work_mem; |
| 当前时间 | SELECT NOW(); |
SELECT now(); |
| 当前库的大小 | 查 information_schema.tables |
pg_database_size(current_database()) |
2. 常用诊断 SQL
-- MySQL SHOW ENGINE INNODB STATUS\G -- 死锁、事务、缓冲池 SHOW GLOBAL STATUS LIKE 'Threads%'; -- 连接/线程 SHOW VARIABLES LIKE 'transaction_isolation'; SHOW CREATE TABLE orders\G ANALYZE TABLE orders; -- 更新统计信息 EXPLAIN ANALYZE SELECT ...; -- 8.0.18+ SELECT * FROM sys.statement_analysis LIMIT 10; -- 语句统计（sys schema）
-- PostgreSQL
SELECT * FROM pg_stat_activity WHERE state <> 'idle';
SELECT * FROM pg_locks WHERE NOT granted;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;
ANALYZE orders; -- 更新统计信息
VACUUM (VERBOSE, ANALYZE) orders; -- 手动清理 + 统计
REINDEX INDEX CONCURRENTLY idx_name; -- 在线重建索引（12+）
SELECT pg_size_pretty(pg_relation_size('orders'));
3. 备份恢复速查
| 目的 | MySQL | PostgreSQL |
|---|---|---|
| 逻辑备份 | mysqldump --single-transaction db > db.sql |
pg_dump -Fc db -f db.dump |
| 逻辑恢复 | mysql db < db.sql |
pg_restore -d db db.dump |
| 物理备份 | xtrabackup --backup |
pg_basebackup -D dir -Ft -z |
| 日志（用于 PITR） | binlog（mysqlbinlog 回放） |
WAL 归档（restore_command + recovery_target_time） |
4. 语法差异速查
| 需求 | MySQL | PostgreSQL |
|---|---|---|
| 限制行数 | LIMIT n OFFSET m |
LIMIT n OFFSET m（或 FETCH FIRST n ROWS ONLY） |
| 字符串连接 | CONCAT(a, b) |
concat(a, b)，也支持标准的双竖线连接运算符 |
| 引用标识符 | 反引号 `name` |
双引号 "name" |
| 当前时间加减 | NOW() - INTERVAL 1 DAY |
now() - interval '1 day' |
| 空值替换 | IFNULL(a, b) / COALESCE |
COALESCE(a, b) |
| 日期格式化 | DATE_FORMAT(d, '%Y-%m-%d') |
to_char(d, 'YYYY-MM-DD') |
| 自增获取 | LAST_INSERT_ID() |
INSERT ... RETURNING id |
| 字符串比较大小写 | 取决于 collation，默认常不敏感 | 默认区分大小写；ILIKE 不区分 |
| 布尔字面量 | 1/0、TRUE/FALSE |
TRUE/FALSE |
| 分组拼接 | GROUP_CONCAT(col) |
string_agg(col, ',') |
| 类型转换 | CAST(x AS SIGNED) |
x::int 或 CAST(x AS integer) |
十五、FAQ
Q1：新项目到底选 MySQL 还是 PostgreSQL？没有放之四海皆准的答案。团队熟悉度、托管服务、生态工具、是否需要 PG 特有能力（JSONB+GIN、部分索引、PostGIS、排他约束等）是主要决策因素。没有强需求时，选团队最熟悉的那一个。
Q2：主键用自增还是 UUID？单库业务默认用 BIGINT 自增，简单且对 InnoDB 友好。需要分布式生成就用时间有序的方案（雪花、UUID v7、ULID），并处理好时钟回拨与前端精度问题。对外暴露建议另设不可预测的公开标识。避免用随机 UUID v4 字符串作 InnoDB 主键。
Q3：金额用 DECIMAL 还是整数分？都可以。DECIMAL 直观，整数“分”计算简单。关键是别用浮点数，并在整个系统中统一约定。
Q4：为什么我的索引没生效？常见原因：对列做了函数或运算、隐式类型转换、前导通配符、违反最左前缀、统计信息过期、优化器认为全表扫描更便宜（数据量小或返回行占比高）。用 EXPLAIN 看，而不是猜。
Q5：MySQL 的 COUNT(*) 慢怎么办？InnoDB 因为
Q6：PostgreSQL 为什么总说要 VACUUM？因为
Q7：读写分离后出现读不到刚写入的数据怎么办？这是复制延迟导致的正常现象。对一致性敏感的读走主库，或者写后短时间内读主，或等待从库追上指定位点。
Q8：线上大表要加字段/索引，怎么做才安全？先看你的数据库与版本支持哪种在线方式（MySQL Online DDL/INSTANT、gh-ost；PG 的 CONCURRENTLY、NOT VALID + VALIDATE），设置锁超时，避开长事务，低峰执行，分阶段发布，并准备回滚方案。
Q9：死锁一定是 bug 吗？不一定。并发事务偶尔死锁是正常现象，数据库会回滚其中一个。重点是降低频率（固定顺序、缩短事务、走索引）并让应用可重试。
Q10：要不要用外键？取决于团队规范和规模。外键提供引用完整性，PG 社区更常用；在 MySQL 的高并发或分库分表场景，不少团队选择不用并由应用保证。无论如何，被引用/关联的列都应有索引。
Q11：软删除好还是物理删除好？软删除便于恢复和审计，代价是所有查询和唯一约束都要处理。需要审计的数据可用归档表或变更日志代替。按业务需求选，不要默认全部软删除。
Q12：版本升级要注意什么？阅读发布说明中的不兼容变化与默认值变化（MySQL 5.7→8.0 的默认字符集、认证插件、保留字等；PostgreSQL 大版本升级需要 pg_upgrade 或逻辑复制等方式），先在预发环境演练，并确认驱动、ORM、工具链兼容。以所用版本官方文档为准。
Q13：EXPLAIN 里的行数、成本是准的吗？它们是基于统计信息的估算。要看真实情况使用 EXPLAIN ANALYZE（会真正执行）。估算与实际差距很大时，优先检查统计信息。
Q14：数据库该不该存文件、图片？一般不建议把大文件放在数据库里，放对象存储，库里存路径和
十六、小结
选型：没有绝对赢家。看团队熟悉度、托管支持、生态和特性需求；在两者都满足时，优先降低运维与学习成本。
建模：主键短且趋势递增、字段
NOT NULL、类型最小够用、utf8mb4、金额不用浮点、时间明确时区；用唯一索引兜住并发；软删除要同时设计唯一约束的解法。索引：理解 B+Tree、最左前缀、
覆盖索引、ICP；PG 额外有部分、表达式、GIN、GiST、BRIN；警惕函数、隐式转换、前导通配符等导致的失效，一切以 EXPLAIN为准。事务：事务要短；理解两库默认隔离级别与
MVCC 实现的差异（undo log + purge vs 死元组 + VACUUM）；用固定顺序、索引和重试对付死锁与锁等待。 查询：避免
SELECT *、N+1；深分页用键集分页或延迟关联；批量分批；用 upsert 代替先查后写；善用窗口函数与 CTE。运行：连接池大小靠压测而不是越大越好；复制不是备份；备份必须演练恢复；DDL 要设置锁超时并采用在线、分阶段的方式；监控长事务、复制延迟、磁盘、归档与膨胀。
安全：最小权限、参数化查询、传输与存储加密、敏感数据脱敏。
如果只带走一件事，那就是：先测量，再优化；先备份，再变更；先理解机制，再依赖经验。 把本文的 SQL 在测试库里逐条跑一遍，对着 EXPLAIN 的输出看一看，比读十遍文字更有用。再次提醒：版本相关的细节，请以所用版本官方文档为准。
2026-10-01鱼鱼