MySQL vs PostgreSQL：架构差异、索引体系、复杂查询、并发控制与迁移实战入坑笔记

67次阅读
没有评论

共计 4969 个字符，预计需要花费 13 分钟才能阅读完成。

摘要（TL;DR）

  • 连接与架构 ：MySQL 默认「 每连接一线程 」；高并发下易受上下文切换与资源争用影响（企业版提供线程池插件）。PostgreSQL 是「 每连接一进程（backend）」，常配合 PgBouncer 做连接池以降低连接成本。(dev.mysql.com)
  • 索引与查询 ：PostgreSQL 内置 B-Tree/GiST/SP-GiST/GIN/BRIN/Hash/ 部分索引 等多类型索引，支持 多索引位图合并 ；对 JSONB/ 数组 / 几何 等复杂类型与操作符支持成熟。MySQL 8 通过 索引合并（Index Merge）、函数 / 生成列索引 与 InnoDB FULLTEXT 等能力补足场景。(PostgreSQL)
  • 复杂查询 ：两者均已支持 CTE 与 窗口函数 ；PostgreSQL 还支持 并行查询、更丰富的执行计划与算子生态。(dev.mysql.com)
  • 并发与事务：两者皆为 MVCC。PostgreSQL 提供 SKIP LOCKED、Advisory Locks 等；MySQL 8 同样提供 NOWAIT/SKIP LOCKED 的锁读语义。(PostgreSQL)
  • 数据类型与扩展 ：PostgreSQL 原生 数组 /JSONB/ 几何 / 全文搜索 ，配合 PostGIS 扩展形成强大空间计算栈。MySQL 8 提供 JSON 类型，但通常通过 生成列 + 索引 优化检索。(PostgreSQL)
  • 迁移：从 MySQL 迁往 PostgreSQL 的常用路径：pgloader（结构 + 数据）与 AWS DMS（增量 / 在线）。(pgloader.readthedocs.io)

1. 架构与连接处理

1.1 MySQL：线程 / 连接模型与线程池

  • 默认 ：每个客户端连接对应 一个服务器线程 ；连接数升高时，线程上下文切换、内存与锁竞争会降低吞吐。官方在企业版提供 Thread Pool 插件 以缓解并发开销。(dev.mysql.com)
  • 工程含义 ：面向 大量空闲 / 短事务连接 时，应在应用侧使用连接池（如 HikariCP），或在企业版启用线程池插件以控制活动线程上限。(mysql.net.cn)

1.2 PostgreSQL：进程 / 连接模型与连接池

  • 模型 ： 进程 / 连接一对一 ，由 postmaster/fork 产生 backend 进程。每个连接的内存 / 上下文独立、隔离性强，但 建连成本高、连接过多会造成额外开销。(PostgreSQL)
  • 常见做法：在数据库前使用 PgBouncer 进行 session/transaction/statement 级连接池化，大幅降低连接抖动与建连成本。(pgbouncer.org)

2. 索引体系与查询优化

2.1 PostgreSQL 的多型索引与位图合并

  • 索引类型 ：B-Tree、GiST、SP-GiST、GIN、BRIN、Hash 等；可针对 JSONB、数组、全文、地理等 不同数据与谓词 匹配最优结构。(PostgreSQL)
  • JSONB 与 GIN：@>、?、?|、?&、@?/@@ 等操作符可结合 GIN 索引实现高效检索。(PostgreSQL)
  • BRIN：适合 时间序列 / 顺序写 的大表，通过块范围摘要快速跳过无关数据。(PostgreSQL)
  • 部分索引：仅索引满足谓词的行，降低索引体积、提高热子集查询性能。(PostgreSQL)
  • 多索引合并（位图扫描）：同一表可 AND/OR 合并多个索引 结果，覆盖更复杂筛选场景。(PostgreSQL)

2.2 MySQL 8 的重要补全

  • 索引合并（Index Merge）：单表范围内支持并集、交集与组合策略，缓解“单索引不够用”的场景。(dev.mysql.com)
  • JSON/ 函数索引 ：JSON 列本身不可直接索引，通常通过 生成列 / 函数索引 抽取键值后再索引，实现定向查询加速。(dev.mysql.com)
  • 全文索引：InnoDB 已原生支持 FULLTEXT（倒排结构），满足自然语言检索与邻近度需求。(dev.mysql.com)

实践提示：

  • 复杂过滤（JSON/ 数组 / 标签 / 全文 / 地理）→ 优先 PostgreSQL（GIN/GiST/BRIN/FTS）；
  • 单表复杂谓词但无 JSON/ 数组 → PostgreSQL 的 位图合并 通常更灵活；MySQL 8 可用 Index Merge + 生成列索引 权衡。(PostgreSQL)

3. 复杂查询与执行框架

  • CTE（WITH）：MySQL 8 与 PostgreSQL 均支持，有助于将复杂查询模块化表达与复用。(dev.mysql.com)
  • 窗口函数：两者都支持，适合排名、分组内累计、滑动窗口统计等分析型场景。(dev.mysql.com)
  • 并行查询（PostgreSQL）：对全表 / 大范围扫描且仅返回少量行的查询，可显著提速（2–4 倍及以上的量级视场景而定）。(PostgreSQL)

4. 数据类型与扩展生态

  • PostgreSQL 原生多样性 ： 数组 、JSON/JSONB、 几何 等类型与运算符一应俱全，并内置 全文搜索（tsvector/tsquery）。空间场景可直接使用 PostGIS 扩展，形成数据库内空间索引与分析能力。(PostgreSQL)
  • MySQL JSON：具备 JSON 类型与函数，但 高性能查询常依赖生成列 + 索引 或函数索引。(dev.mysql.com)

5. 并发控制、锁与事务

  • 两者皆为 MVCC：PostgreSQL 以多版本元组管理、Vacuum 维持可见性；MySQL InnoDB 依赖 undo log 构造历史版本。(PostgreSQL)
  • 跳过锁等待：PostgreSQL 的 FOR UPDATE SKIP LOCKED；MySQL 8 同样支持 NOWAIT / SKIP LOCKED 语义的锁读。(PostgreSQL)
  • Advisory Locks（建议锁）：PostgreSQL 原生提供 会话 / 事务级 的应用层命名锁机制，适用跨表 / 跨流程的互斥控制。(PostgreSQL)

6. 性能与伸缩实践建议

  • 连接管理
    • PostgreSQL：强烈建议 前置 PgBouncer，将“海量短连接 / 冷连接”收敛为少量持久连接；选择合适的 pooling 模式（session/transaction/statement）。(pgbouncer.org)
    • MySQL：应用侧连接池是基础；若为企业版，可启用 Thread Pool 控制活动线程，降低上下文切换与争用。(dev.mysql.com)
  • 索引与查询
    • PostgreSQL：充分利用 GIN/BRIN/ 部分索引 与 位图合并；针对 JSONB 采用操作符与 GIN 的组合。(PostgreSQL)
    • MySQL：复杂过滤可通过 Index Merge 与 生成列索引；全文检索考虑 InnoDB FULLTEXT。(dev.mysql.com)
  • 执行框架
    • PostgreSQL：适合 汇总 / 分析 /ETL类查询，开启 并行查询 能在大表聚合上获得显著收益。(PostgreSQL)

注：不同 workload 的 TPS/QPS 受硬件、SQL、事务模型、隔离级别、连接策略等影响很大。请基于你们的真实模型进行基准测试，不建议照搬任何“单一数字”的横向对比。

7. 迁移到 PostgreSQL：路径与要点

  • 工具链
    • pgloader：一次性导入结构 + 数据，支持类型映射与批量迁移；适合作离线或准实时批迁移。(pgloader.readthedocs.io)
    • AWS DMS：适用于 在线 / 增量 迁移（CDC）；结合 AWS SCT 完成异构架构转换。(docs.aws.amazon.com)
  • 建议流程
    1. 双写 / 灰度 ：热点表先 读双写 或同步，校验一致性；再逐步切流量。
    2. 类型与索引重塑 ：将 MySQL 的 JSON/ 标签场景改造为 JSONB+GIN 或数组 / 范围等类型；对热路径使用 部分索引 /BRIN。(PostgreSQL)
    3. 连接策略：应用改造接入 PgBouncer，控制并发连接峰值。(pgbouncer.org)
    4. SQL 等价性：CTE/ 窗口函数 / 锁语义等逐条校核，替换 SKIP LOCKED/NOWAIT 等方言差异。(dev.mysql.com)

8. 选型清单（建议）

  • 优先 PostgreSQL 的典型场景
    • 复杂过滤 / 分析：JSONB/ 数组 / 标签 + 多条件合并；需要 位图合并、并行查询 的分析类 SQL。(PostgreSQL)
    • 空间 / 地理：需要GIS/ 空间索引与计算（PostGIS）。(PostGIS)
    • 全文 / 模糊检索：需要 FTS/ 三元组相似度 等数据库内检索能力。(PostgreSQL)
  • 优先 MySQL 的典型场景
    • 以 CRUD 为主、查询相对简单，生态 / 团队经验重度依赖 MySQL；或已有企业版并可启用 Thread Pool。(dev.mysql.com)

9. 参考代码片段（示意）

PostgreSQL：JSONB + GIN + 位图合并

-- JSONB 与标签检索
CREATE INDEX idx_products_tags_gin ON products USING GIN(tags);
CREATE INDEX idx_products_attr_gin ON products USING GIN(attributes);
-- 价格区间 B-Tree
CREATE INDEX idx_products_price ON products(price);
-- 查询：标签 + 价格 +JSONB 包含，同时可触发位图合并
SELECT id, name
FROM products
WHERE tags @> ARRAY['electronics']
  AND price BETWEEN 100 AND 500
  AND attributes @> '{"color":"red"}'::jsonb;

MySQL 8：JSON 生成列 + 索引 + Index Merge

-- 从 JSON 提取 color 到生成列，并建立索引
ALTER TABLE products
  ADD COLUMN color VARCHAR(16)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))) STORED,
  ADD INDEX idx_color (color);

-- 结合 price 索引，触发 Index Merge
SELECT id, name
FROM products
WHERE JSON_CONTAINS(tags, '["electronics"]')
  AND price BETWEEN 100 AND 500
  AND color = 'red';

并发控制：SKIP LOCKED（两端对比）

-- PostgreSQL
BEGIN;
SELECT * FROM orders
WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- MySQL 8
START TRANSACTION;
SELECT * FROM orders
WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED
LIMIT 10;

（语义对齐需结合实际的隔离级别 / 业务逻辑校验。）(dev.mysql.com)

10. 进一步阅读

  • 连接 / 架构：MySQL 线程池与默认模型；PostgreSQL 连接建立流程与 PgBouncer。(dev.mysql.com)
  • 索引：PostgreSQL 索引类型、BRIN、部分索引、位图合并；MySQL Index Merge、JSON 索引策略。(PostgreSQL)
  • 复杂查询：PostgreSQL 并行查询；MySQL CTE/ 窗口函数。(PostgreSQL)
  • 全文 / 空间：PostgreSQL FTS 与 PostGIS；MySQL InnoDB FULLTEXT。(PostgreSQL)
  • MVCC/ 锁：PostgreSQL/ InnoDB MVCC；显式锁、SKIP LOCKED/NOWAIT。(PostgreSQL)
  • 迁移：pgloader 教程；AWS DMS 文档。(pgloader.readthedocs.io)

结论

  • 若你的业务 查询形态复杂 （JSON/ 数组 / 标签 / 全文 / 空间 / 分析）且对 一致性与执行器能力 有较高要求，PostgreSQL 往往能以更少的“外围方案”满足需求。
  • 以 简单 CRUD 为主或对 MySQL 生态 依赖较深的系统，继续使用 MySQL 8 并合理使用生成列 /Index Merge/ 全文索引与 应用侧连接池，同样能获得稳健表现。
  • 不要忽视 连接池与基准测试：PostgreSQL 配置 PgBouncer，MySQL 评估企业版 Thread Pool；用你们的真实 SQL 和数据分布去压测，再做定论。(pgbouncer.org)

打赏 微海报 分享
正文完
mysql postGreSQL
发表至： 数据科学
2025-10-09
 0
一诺
版权声明：本站原创文章，由 一诺 于2025-10-09发表，共计4969字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
MongoDB 入土刷题集
MySQL 入门教程 · 第一课
MySQL vs PostgreSQL：架构差异、索引体系、复杂查询、并发控制与迁移实战入坑笔记
YOLOv5从入门到部署之：网络和损失函数
评论（没有评论）
验证码