PostgreSQL vs MySQL 系统性知识体系全解

本文从底层架构→核心机制→专项能力→选型决策全链路，全方位结构化拆解PostgreSQL（简称PG）与MySQL的核心差异，深度覆盖MVCC实现、向量索引、全文检索、JSONB类型五大核心主题，形成完整可落地的知识体系。

一、顶层定位与核心架构总览（底层逻辑根源）

所有功能与性能差异，均源于两款数据库的原生设计目标与架构底座的本质区别。

二、全维度核心区别结构化对比

2.1 事务与ACID合规性

• PostgreSQL：原生完全遵循ACID原则，默认开启完整事务日志，CHECK约束、外键约束、唯一约束全程生效，无任何语法兼容式阉割；支持完整的事务隔离级别（读未提交、读已提交、可重复读、可序列化），其中可序列化采用SSI（可序列化快照隔离），真正杜绝幻读与写偏序异常。
• MySQL：仅InnoDB存储引擎支持ACID，MyISAM等引擎不支持事务；8.0.16版本前CHECK约束仅语法兼容，无实际校验；外键约束存在大量边界限制；可重复读隔离级别通过间隙锁+临键锁规避幻读，并非纯快照实现，可序列化隔离级别本质是强制全表加锁，性能损耗极大。

2.2 并发控制基础能力

• PostgreSQL：基于MVCC实现无锁读，读不阻塞写、写不阻塞读；支持表级、行级、页级锁，支持 advisory lock（用户自定义 advisory 锁），支持死锁自动检测与回滚；无全局事务ID锁，高并发下事务分配无瓶颈。
• MySQL InnoDB：同样基于MVCC实现无锁读，但读已提交隔离级别下存在半一致性读，可能导致非预期的锁等待；锁机制依赖索引，无索引的更新会触发表锁；高并发下自增主键、事务ID分配存在潜在瓶颈。

2.3 扩展性与插件生态

• PostgreSQL：内核级插件扩展能力，可扩展存储引擎、数据类型、索引类型、函数、操作符、分词器、优化器规则等，生态覆盖全场景：
  • 时序数据：TimescaleDB
  • 列存分析：cstore_fdw
  • 地理空间：PostGIS（行业标准）
  • 向量检索：pgvector
  • 全文检索：zhparser、pg_jieba
• MySQL：插件扩展能力有限，仅支持存储引擎、UDF函数、分词器等基础扩展，核心内核逻辑无法通过插件修改，生态集中于高可用、分库分表（ProxySQL、MyCat），垂直场景能力薄弱。

2.4 原生数据类型支持

• PostgreSQL：原生支持丰富的基础与复合类型，包括：数值、字符串、日期时间、二进制、布尔、枚举、数组、范围、复合、地理信息、网络地址、UUID、JSON/JSONB、Hstore、向量等，支持自定义数据类型与操作符。
• MySQL：仅支持基础数据类型，复合类型、高级类型支持极弱，数组、范围、地理信息、向量等均需通过字符串或二进制间接实现，无原生支持。

三、MVCC（多版本并发控制）实现机制深度对比

MVCC是两款数据库并发控制的核心，实现差异直接决定了事务行为、并发性能、存储特性的本质区别。

3.1 MVCC核心设计目标

通过维护数据的多版本，实现读不阻塞写、写不阻塞读，避免高并发下的锁竞争，同时支撑事务隔离级别的实现。

3.2 PostgreSQL MVCC 实现原理

PG采用基于元组多版本的快照隔离（SI） 实现，核心是将数据的旧版本与新版本同存于表的数据页中，无独立的回滚日志。

3.2.1 核心数据结构：元组系统字段

每个数据行（元组）都自带4个核心系统字段，用于MVCC可见性判断：

3.2.2 事务快照与可见性规则

• 快照生成时机：
  • 读已提交（RC）：每条SQL语句执行前生成新快照
  • 可重复读（RR）/可序列化：事务启动时生成全局唯一快照，全程复用
• 快照核心组成：xmin（当前活跃事务最小ID）、xmax（当前已分配最大事务ID+1）、xip_list（当前活跃事务ID列表）
• 核心可见性规则：元组对当前快照可见，必须同时满足
  1. 元组xmin < 快照xmin，且不在活跃事务列表中（插入事务已提交）
  2. 元组xmax = 0，或xmax > 快照xmax，或xmax在活跃事务列表中（删除/更新事务未提交）

3.2.3 旧版本清理机制：VACUUM

旧版本元组永久存在于数据页中，需通过VACUUM机制清理：

• 自动清理：autovacuum守护进程，定时清理已提交事务的过期元组
• 手动清理：VACUUM FULL可重写整个表，释放磁盘空间，解决表膨胀问题
• 核心痛点：长事务会阻塞过期元组清理，导致表膨胀，占用额外磁盘空间，影响查询性能

3.2.4 事务隔离级别实现

• 读已提交、可重复读：纯快照隔离实现，无锁开销，可重复读级别完全杜绝幻读
• 可序列化：基于SSI可序列化快照隔离，通过检测事务间的依赖冲突，回滚可能导致串行化异常的事务，无强制锁开销，是真正的串行化隔离

3.3 MySQL InnoDB MVCC 实现原理

InnoDB采用基于Undo Log的回滚段实现，核心是将数据的旧版本存储于独立的Undo Log中，聚簇索引仅保留最新版本数据。

3.3.1 核心数据结构

• 聚簇索引行：每行数据除业务字段外，包含3个核心字段：DB_TRX_ID（最近修改该行的事务ID）、DB_ROLL_PTR（回滚指针，指向Undo Log中的旧版本数据）、DB_ROW_ID（隐藏主键）
• Undo Log：分为insert undo（插入操作，事务提交后可直接删除）和update undo（更新/删除操作，用于MVCC快照读，需等待所有相关快照过期后清理），通过回滚指针形成版本链

3.3.2 事务快照与可见性规则

• 快照生成时机：
  • 读已提交（RC）：每条SQL语句执行前生成新快照
  • 可重复读（RR）：事务内第一条SQL执行时生成快照，全程复用
• 可见性判断逻辑：从最新版本开始，沿回滚指针遍历版本链，找到第一个符合快照可见性规则的版本，返回给用户
• 核心规则：通过事务ID的活跃状态，判断版本是否对当前快照可见，仅已提交事务的版本可被读取

3.3.3 旧版本清理机制：Purge

• 后台Purge线程定时扫描Undo Log，清理已无任何快照引用的过期版本数据
• 核心痛点：长事务会导致Undo Log无法清理，引发Undo Log膨胀，占用磁盘空间，影响数据库性能

3.3.4 事务隔离级别实现

• 读已提交：纯快照读实现，无锁开销
• 可重复读：快照读+间隙锁/临键锁实现，纯快照读无法杜绝幻读，需通过加锁机制规避，存在锁竞争与死锁风险
• 可序列化：将所有普通SELECT语句强制转为SELECT ... LOCK IN SHARE MODE，全表加共享锁，完全放弃MVCC，性能损耗极大

3.4 MVCC核心差异与业务影响总结

四、向量索引与向量检索能力对比

向量检索是AI大模型时代的核心能力，用于RAG检索增强生成、语义搜索、推荐系统等场景，两款数据库的能力差距显著。

4.1 PostgreSQL 向量检索能力体系

PG是业内开源关系型数据库向量检索的标杆，核心依赖pgvector插件（行业事实标准），已被AWS、Azure、Google Cloud等云厂商原生集成。

4.2.1 核心能力与索引类型

• 原生支持：通过pgvector扩展vector数据类型，支持维度上限65535，兼容稀疏向量、量化向量，适配大语言模型嵌入需求
• 核心索引类型：
  1. IVFFLAT：倒排文件扁平索引，基于k-means聚类，构建速度快、内存占用低，适合高吞吐、大数据量的批量检索场景
  2. HNSW：层次化导航小世界索引，图结构索引，查询延迟极低、精度高，适合低延迟在线检索场景，是当前主流选型
  3. SPARSE：稀疏向量索引，适配大模型稀疏嵌入，大幅降低内存占用与计算开销
• 距离算法：原生支持L2欧氏距离、内积、余弦距离、曼哈顿距离，支持自定义距离函数
• 高级特性：
  • 支持SQ8、PQ量化，压缩向量体积，降低内存与磁盘占用
  • 与PG优化器深度融合，支持过滤+向量检索联合优化（先过滤业务条件，再执行向量检索，大幅降低计算量）
  • 支持联合索引、分区表向量索引、并行向量检索
  • 可与JSONB、全文检索、地理信息等能力融合，实现多模态混合检索

4.2 MySQL 向量检索能力体系

MySQL的向量检索能力起步极晚，8.0.37版本才原生支持向量类型与向量索引，此前仅能通过第三方插件（mysql_vector）实现，生态与成熟度极低。

4.2.1 核心能力与索引类型

• 原生支持：8.0.37+版本新增VECTOR数据类型，维度上限16383，仅支持稠密浮点向量
• 核心索引类型：仅支持HNSW图索引，无IVFFLAT、稀疏向量索引等其他类型
• 距离算法：仅支持L2欧氏距离、内积、余弦距离，不支持自定义距离函数
• 能力边界：
  • 仅支持单列向量索引，不支持与业务字段的联合索引
  • 优化器不支持过滤+向量检索的联合优化，无法先过滤再检索，性能上限极低
  • 不支持量化压缩、分区表向量索引、并行检索
  • 无法与JSON、全文检索等能力融合，仅能实现基础的向量相似度查询

4.3 向量检索能力核心对比表

五、全文检索能力体系对比

全文检索是针对非结构化文本的模糊匹配、语义检索能力，两款数据库均有原生支持，但能力边界差距显著。

5.1 PostgreSQL 原生全文检索体系

PG内置企业级全文检索引擎，无需第三方组件即可实现完整的文本检索能力，可直接替代轻量级Elasticsearch。

5.1.1 核心实现架构

1. 核心数据类型
   • tsvector：文本分词后的向量类型，存储分词后的词条、位置、权重信息
   • tsquery：检索查询类型，支持与、或、非、距离匹配等复杂逻辑
2. 分词体系
   • 原生支持多语言分词，内置英文、法文、德文等多国语言词典
   • 中文支持：通过zhparser、pg_jieba插件实现精准中文分词，支持自定义词典、停用词、同义词、近义词、主题词
   • 支持自定义分词规则、分词器、词典链，适配垂直行业场景
3. 索引支持
   • 原生支持GIN索引（通用倒排索引，检索性能最优，主流选型）、GIST索引（适合内存受限场景）
   • 支持分区表全文索引、表达式索引，可对JSONB字段内的文本、数组文本、多字段拼接文本建全文索引
4. 高级特性
   • 支持词条距离匹配、权重自定义、相关性排名（内置多种排名算法）、检索结果高亮
   • 支持并行全文检索、模糊匹配（pg_trgm插件实现trigram索引，支持前后模糊查询）
   • 可与向量检索、JSONB、事务能力深度融合，实现「全文检索+向量语义检索+业务过滤」的混合检索

5.2 MySQL 全文检索体系

MySQL仅通过FULLTEXT索引实现基础全文检索能力，核心能力集中于InnoDB引擎，功能边界严格受限。

5.2.1 核心实现架构

1. 核心实现
   • 仅支持对CHAR、VARCHAR、TEXT类型字段建FULLTEXT索引，不支持JSON、数组等复合类型
   • 内置两种查询模式：自然语言模式（默认）、布尔模式，支持基础的与、或、非逻辑
2. 分词体系
   • 原生仅支持英文分词，基于空格与标点分词，最小分词长度默认为4个字符
   • 中文支持：仅通过ngram插件实现二元分词，无精准分词能力，不支持自定义词典、停用词、同义词
3. 索引与性能
   • 仅支持单表/单分区FULLTEXT索引，不支持表达式索引、联合索引
   • 索引缓存受innodb_ft_cache_size限制，大文本、大数据量场景下构建与检索性能极差
4. 能力边界
   • 不支持词条距离匹配、权重自定义，仅内置基础相关性排名算法，无法调整
   • 不支持前后模糊查询，无trigram索引能力
   • 无法与JSON、事务、其他索引能力融合，仅能实现基础的单字段文本检索

5.3 全文检索能力核心对比表

六、JSONB类型与JSON处理能力对比

JSON类型是关系型数据库适配半结构化数据的核心能力，其中PG的JSONB是业内标杆，与MySQL的JSON类型存在本质差距。

6.1 PostgreSQL JSON/JSONB 体系

PG原生支持两种JSON类型，核心能力集中于JSONB（二进制JSON类型），是半结构化数据的首选。

6.1.1 json vs jsonb 核心区别

6.1.2 JSONB核心能力

1. 存储结构：预解析为二进制结构，JSON的key与value均为独立存储单元，支持快速定位与检索，无重复解析开销
2. 索引能力（核心优势）
   • 支持对整个JSONB列建GIN索引，原生支持@>包含、?存在、?&全包含、?|任意包含等操作符，可加速任意路径的JSON查询，无需提前定义路径
   • 支持表达式索引，可对JSON内的特定路径建B树、GIN、GiST索引，适配固定路径的高频查询
   • 支持联合索引，可将JSON路径与业务字段联合建索引，实现混合查询加速
3. 操作符与SQL融合
   • 原生支持丰富的JSON操作符：路径提取#>、文本提取#>>、包含@>、存在?、拼接||、删除-等
   • 完整支持SQL:2016标准的JSONPath语法，支持复杂嵌套路径查询、条件过滤、数组遍历
   • 可与SQL查询完全融合，支持JSON字段与普通字段的关联查询、聚合计算、窗口函数、事务控制
4. 高级特性
   • 支持JSONB嵌套结构的全文检索、向量检索，可直接对JSON内的文本内容建全文索引、生成向量
   • 支持JSON Schema校验，可通过约束实现JSON结构的合规性校验
   • 支持JSON与关系表的双向转换，可通过jsonb_to_record等函数快速将JSON转为结构化表

6.2 MySQL JSON 类型体系

MySQL 5.7版本开始支持JSON类型，8.0版本小幅增强，仅实现基础的二进制存储与JSON操作，核心能力严重受限。

6.2.1 核心特性与能力边界

1. 存储结构：二进制存储，写入时预解析JSON结构，支持快速定位顶层key，但嵌套结构的检索仍需遍历，解析开销远高于PG JSONB
2. 索引能力（核心短板）
   • 不支持直接对JSON列建索引，仅能通过生成列（Generated Column） 间接实现索引
   • 必须提前定义固定的JSON路径，创建生成列，再对生成列建二级索引，仅能加速该固定路径的查询
   • 不支持通用索引，无法加速任意路径的包含、存在查询，非预定义路径的查询无法命中索引，只能全表扫描
3. 操作符与JSONPath支持
   • 仅支持基础的JSON提取、修改函数，操作符丰富度远低于PG
   • 8.0版本支持基础JSONPath语法，不支持复杂条件过滤、数组遍历等高级特性
   • JSON查询与SQL融合度低，复杂嵌套JSON的查询语法繁琐，性能差
4. 能力边界
   • 不支持JSON结构校验、全文检索、向量检索等高级特性
   • 不支持JSON字段与普通字段的联合索引，混合查询性能极差
   • 高频更新JSON内单个字段时，需重写整个JSON二进制结构，更新性能远低于PG

6.3 JSON处理能力核心对比表

七、最终选型决策指南

7.1 优先选择PostgreSQL的场景

1. 企业级复杂业务系统，需高SQL标准兼容、强事务一致性、复杂查询与多表关联能力
2. AI大模型相关场景，需向量检索、RAG检索增强、多模态混合检索能力
3. 半结构化数据（JSON/JSONB）占比高，需灵活的索引与复杂查询能力
4. 需全文检索、地理信息、时序数据、数据分析等多模能力，希望单一数据库实现全场景能力
5. 对开源协议有严格要求，需商用闭源分发，规避GPL协议风险
6. 数据量持续增长，需高扩展性，未来可能扩展分析型、时序型等垂直能力

7.2 优先选择MySQL的场景

1. 互联网极简OLTP业务，以单表单行CRUD为主，无复杂查询与多表关联需求
2. 业务架构成熟，已有完善的MySQL高可用、分库分表生态，运维体系完善
3. 高频更新的业务场景，如订单、支付、库存等，MySQL原地更新性能更优
4. 团队技术栈以MySQL为主，学习与运维成本极低，无复杂业务扩展需求
5. 轻量级中小规模业务，无需多模能力，仅需稳定的基础事务处理能力

7.3 混合部署场景建议

核心交易链路、高频更新场景采用MySQL，保证OLTP性能与稳定性；非结构化数据检索、AI向量检索、复杂分析、半结构化数据存储场景采用PostgreSQL，通过数据同步工具实现双向数据同步，兼顾性能与功能完整性。