PostgreSQL 与 MySQL 存储引擎可插拔架构详解

TL;DR：PostgreSQL 和 MySQL 并非黑箱。两者都支持替换存储引擎、索引类型、持久性保证、数据物理位置，甚至可以添加向量搜索和机器学习，且常常可以按表粒度（有时按事务粒度）进行配置。本文按每个选项所解决的存储或检索问题来梳理这些配置选项，并附上每个知识点的官方文档链接。

🤖 想了解 AI 工作负载？直接跳转第 3 部分：AI 和机器学习工作负载，涵盖向量类型、HNSW/IVFFlat/DiskANN 调优、混合搜索、数据库内机器学习，以及完整的 RAG SQL 查询。

如何阅读本文

第 2 部分：MySQL（含 MariaDB 和 Percona）

第 3 部分：AI 和机器学习工作负载 🤖

速查表：问题 → 引擎

如何阅读本文

无论数据库如何营销，它们的层级结构都是一样的。每一层都有可以插入（swap，换掉组件）或调优（turn a knob，转动旋钮）的东西：

            ┌───────────────────────────────┐
 clients →  │ Connection pooler             │  PgBouncer, thread pool
            ├───────────────────────────────┤
            │ Parser / planner / executor   │  hooks, JIT, parallelism, hash joins
            ├───────────────────────────────┤
            │ Access methods                │  ← storage engines + index types
            │  (tables and indexes)         │     heap, columnar, InnoDB, MyRocks, HNSW...
            ├───────────────────────────────┤
            │ Buffer cache                  │  shared_buffers, innodb_buffer_pool_size
            ├───────────────────────────────┤
            │ Write-ahead log / redo log    │  durability vs speed knobs
            ├───────────────────────────────┤
            │ Files, disks, object storage  │  tablespaces, SSD tuning, S3, Iceberg
            └───────────────────────────────┘

两部分内容按照上述层级从顶层向下延伸到磁盘逐一展开。第 3 部分则覆盖跨越两个数据库的 AI 工作负载。

版本很重要。"PG15+" 表示 PostgreSQL 15 或更高版本。MySQL 相关知识除非特别说明，否则基于 8.4 LTS 手册。VECTOR 类型需要 MySQL 9.x；9.7（2026 年 4 月）是最新的 LTS。

Restart 表示该设置需要服务器重启。其他所有设置都可以通过 reload 或按会话更改。

查看当前值的方法：在 PostgreSQL 中运行 SELECT name, setting, unit, context FROM pg_settings;，在 MySQL 中运行 SHOW VARIABLES LIKE 'innodb%';。

⚠️ 此处的每一个旋钮都是权衡，而非免费的加速。每次只改一件事，用自己的负载进行基准测试，并在触碰任何标记为 unsafe 的内容之前阅读链接的文档。

1.1 PostgreSQL 如何插入组件

PostgreSQL 的超能力是它的扩展系统。很多看起来像"核心"功能实际上是以扩展形式交付的，它们使用公开的插入点：

在启动时挂载到服务器的扩展必须列在 shared_preload_libraries 中，这需要一次重启。查看已安装的扩展用 \dx，查看可用扩展用 SELECT * FROM pg_available_extensions;。

1.2 存储引擎（表访问方法）

PostgreSQL 内置存储引擎只有一个：heap。其他通过表访问方法 API 插入：

CREATE TABLE orders (...) USING heap;              -- 默认
CREATE TABLE events (...) USING columnar;          -- 来自扩展
ALTER  TABLE events SET ACCESS METHOD heap;        -- PG15+，重写整个表
SET default_table_access_method = 'columnar';      -- 新表的默认访问方法

ALTER TABLE ... SET ACCESS METHOD 在 PG15 引入。它会在排他锁下重写整个表，所以要像迁移一样规划。默认值来自 default_table_access_method。

💡 在我自己的测试中（见我的 Database Engines 101 文章的实验），同样的 500 万行数据在 heap 中占用 868 MB，在 columnar 中占用 127 MB，一次分析查询读取的页数减少了 35 倍。Columnar 格式直接拒绝 UPDATE。

1.3 索引类型（索引访问方法）

这里是大多数性能提升或损失发生的地方。PostgreSQL 自带六种索引类型（概览），扩展还可以添加更多：

值得了解的 B-tree 特性

• 去重（Deduplication，PG13）：重复的键只存储一次，因此低基数列上的索引会大幅缩小。默认开启。pg_upgrade 携带过来的索引需要执行 REINDEX 才能受益。文档

• **覆盖索引与 INCLUDE（PG11）**使额外的列无需成为键的一部分就能实现索引仅扫描：CREATE INDEX ON orders (customer_id) INCLUDE (total); 文档

• Skip scan（PG18）：对 (region, created_at) 的多列索引现在可以服务 WHERE created_at > ...，即使 region 上没有条件。PG18 release notes

• uuidv7()（PG18）：时间有序的 UUID 会落在 B-tree 的末尾，而不是随机页面。这意味着比随机生成的 gen_random_uuid() 值更少的页面分裂。PG18 release notes

适用于所有索引类型的特性

• 部分索引只索引你查询的行：CREATE INDEX ON orders (created_at) WHERE status = 'open'; 文档

• 表达式索引索引计算后的值：CREATE INDEX ON users (lower(email)); 文档

• CONCURRENTLY 创建索引构建索引时不阻塞写操作。它不能在事务内运行，如果失败会留下一个 INVALID 索引，必须删除。文档

• 操作符类改变索引能回答的问题：text_pattern_ops 使 LIKE 'abc%' 在非 C 排序规则下可索引。文档 jsonb_path_ops 提供更小、更快的 GIN 索引，但只支持包含（@>）和 jsonpath 查询。文档

• GIN pending list。启用 fastupdate（默认）时，新条目进入 pending list，稍后合并。这使写入更便宜，但触发合并的读取者要付出代价。要保持读取延迟稳定，可以缩小 gin_pending_list_limit（默认 4 MB）或关闭 fastupdate。文档

• BRIN 只为每个块范围存储 min 和 max（默认 pages_per_range = 128），因此它只有几千字节而 B-tree 需要几 GB。PG14 添加了 minmax_multi 操作符类，可以容忍异常值，以及 bloom 操作符类用于无序等值查找。文档

CREATE INDEX ON logs USING brin (ts timestamptz_minmax_multi_ops) WITH (pages_per_range = 32);

PG18 还可以并行构建 GIN 索引。B-tree（PG11）和 BRIN（PG17）的构建早已支持并行。PG18 release notes

1.4 行和页布局旋钮

这些按表或按列设置，改变字节在磁盘上的布局方式。

fillfactor 和 HOT 更新

表的默认值为 100。设为 80–90 会在每个页面上留下空闲空间。

更新时就可以在同一页上写入新行版本，且如果没有索引列被更改，就不会被触碰索引。这就是 heap-only tuple（HOT）更新。

ALTER TABLE accounts SET (fillfactor = 85);

HOT 文档 · 存储参数

TOAST（大值如何存储）（文档）

大于约 2 KB 的值会被压缩和/或离线移出到 TOAST 表中。

toast_tuple_target（默认 2032 字节）控制触发时机。

按列存储模式：

• PLAIN：仅内联，不压缩。
• MAIN：压缩，且仅作为最后手段才离线存储。
• EXTERNAL：离线存储，不压缩。对大文本的高速 substring()。
• EXTENDED：压缩，然后离线存储。大多数类型的默认值。

ALTER TABLE docs ALTER COLUMN body SET STORAGE EXTERNAL;

压缩算法（PG14）

按列选择 pglz（默认）或 lz4，或通过 default_toast_compression 设置服务器级默认值。

lz4 压缩和解压缩速度快很多。它需要服务器在构建时带有 lz4 支持。

修改只影响新写入的值。

ALTER TABLE docs ALTER COLUMN body SET COMPRESSION lz4;

虚拟生成列（PG18）：在读取时计算，因此不占用磁盘空间。PG18 之前，生成列总是占用空间（STORED）。PG18 release notes

1.5 WAL 和持久性旋钮

预写日志是使提交在崩溃后存活的原因。它也是写入速度最大的杠杆。除非另有链接，否则所有这些都在 WAL 设置页面上。

按事务设置持久性是这里最未被充分利用的技巧：

BEGIN;
SET LOCAL synchronous_commit = off;   -- 仅对这个事务
INSERT INTO page_views ...;           -- 崩溃时丢失可接受
COMMIT;

（异步提交文档）

Unlogged 表完全跳过 WAL。这使写入速度更快，但崩溃后会被清空，且不会被复制。适合预发环境和缓存表。用 ALTER TABLE t SET LOGGED | UNLOGGED 在两种模式间切换。文档

备份、归档和复制

• archive_library（PG15）通过可加载模块归档 WAL，而不是为每个文件运行一条 shell 命令。文档

• 增量备份（PG17）：设置 summarize_wal = on，然后运行 pg_basebackup --incremental 并与 pg_combinebackup 合并。

Quorum 同步复制：synchronous_standby_names = 'ANY 1 (replica_a, replica_b)' 等待任意一个副本确认，因此一个慢副本不会阻塞提交。文档

官方 Non-Durable Settings 页面将所有以安全性换取速度的开关集中在一处。

除另有链接外，均在资源消耗页面。

保持热数据常驻：pg_prewarm 可按需将表加载到缓存中。其 autoprewarm 工作进程保存缓存块的列表，并在重启后重新加载，这样就不会以冷缓存启动。pg_buffercache 可查看当前缓存中的内容。

1.7 磁盘、SSD 与异步 I/O

告诉优化器你用的是 SSD。

random_page_cost 默认为 4.0，这是假设旋转磁盘的值。

在 NVMe 或云 SSD 上，设为 1.1–1.5 可使优化器在应该选索引扫描时正确选择。

也可以按表空间单独设置（见 1.8）。

异步 I/O（PG18）。这是近年来最大的存储变化之一。

新的 io_method 设置允许后端为顺序扫描、位图堆扫描和 VACUUM 批量排队读取。需重启。

选项：sync（旧行为）、worker（后台 I/O 工作进程）和 io_uring（Linux，需用 liburing 构建）。

新的 pg_aios 视图显示进行中的 I/O。

io_method = io_uring            # 或 'worker'（默认）
effective_io_concurrency = 256  # PG18 默认值为 16
io_combine_limit = 256kB        # PG17+：最大合并读取大小

PG18 还将 effective_io_concurrency 和 maintenance_io_concurrency 的默认值提高到 16。PG18 发布说明

数据校验和现在默认启用（PG18 initdb），因此在读取页面时能检测到静默磁盘损坏。使用 --no-data-checksums 可选择退出。pg_upgrade 需要新旧集群的设置匹配。PG18 发布说明 · pg_checksums

1.8 热数据与冷数据：表空间和分区

表空间将表映射到磁盘。将热表放在 NVMe 上，归档数据放在廉价磁盘上，并告知优化器每块磁盘的真实成本：

CREATE TABLESPACE fast LOCATION '/nvme/pg'
  WITH (random_page_cost = 1.1, effective_io_concurrency = 256);
CREATE TABLESPACE slow LOCATION '/hdd/pg'
  WITH (random_page_cost = 4.0);

ALTER TABLE orders_2019 SET TABLESPACE slow;   -- 锁定并复制表

（CREATE TABLESPACE · managing tablespaces）

声明式分区使热数据和冷数据成为元数据问题（文档）：

RANGE、LIST 和 HASH 分区。分区裁剪在计划时和运行时都会跳过无关分区。

每个分区可以放在自己的表空间中。

DETACH PARTITION ... CONCURRENTLY（PG14）可以在不阻塞查询的情况下移除旧分区。之后可以归档、移动或删除它。

CREATE TABLE events (id bigint, ts timestamptz, payload jsonb) PARTITION BY RANGE (ts);
CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01') TABLESPACE fast;
CREATE TABLE events_2025 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01') TABLESPACE slow;

pg_partman 创建未来分区并应用保留策略：删除旧分区，或将它们分离到归档 schema 中。它可以作为后台工作进程运行，也可以通过 CALL partman.run_maintenance_proc() 调用。

TimescaleDB 自动将旧 chunk 压缩为其列存储，例如 CALL add_columnstore_policy('metrics', after => INTERVAL '7 days');（文档）。在其托管云上，还可以将旧 chunk 分层到 S3。（该公司现在叫 TigerData；扩展名仍是 TimescaleDB。）

1.9 Lakehouse、对象存储和外部数据

这是 PostgreSQL 变化最快的地方。Postgres 正在变成数据湖的前门。

-- pg_duckdb：用 S3 上的 Parquet 文件与本地表连接
SELECT c.name, count(*)
FROM read_parquet('s3://my-bucket/reviews/*.parquet') r
JOIN customers c ON c.id = r['customer_id']
GROUP BY c.name;

💡 模式：热数据保留在 heap 中，冷数据发送到数据湖。近期行保留在常规分区中。定期将旧分区复制到对象存储上的 Parquet 或 Iceberg（用 pg_duckdb 或 pg_lake），然后在本地分离并删除它们。仍可查询全部数据。

1.10 改变数据存储和查找方式的数据类型

选择正确的类型会同时影响存储大小和可用索引。

-- "同一房间不能有重叠的预订"，由数据库强制执行
CREATE EXTENSION btree_gist;
CREATE TABLE bookings (
  room int,
  during tstzrange,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

1.11 VACUUM 和 MVCC 相关开关

PostgreSQL 为 MVCC 保留旧行版本，autovacuum 负责清理。autovacuum 跟不上是表膨胀的最常见原因。所有设置均在 vacuuming settings 页面。

触发公式：当死元组数超过 autovacuum_vacuum_threshold（50）+ autovacuum_vacuum_scale_factor（0.2）× 表行数时，表会被 vacuum。在一张 10 亿行的表上，这意味着 2 亿个死元组。按表降低比例因子：

  ALTER TABLE big_events SET (autovacuum_vacuum_scale_factor = 0.01);

纯插入表（PG13+）现在也会被 vacuum，通过 autovacuum_vacuum_insert_threshold。这保持了可见性映射的最新状态，而索引只读扫描依赖于此。

PG18 新增 autovacuum_vacuum_max_threshold，为超大表设置了触发上限。它还新增了 autovacuum_worker_slots，因此可以在不重启的情况下提高 autovacuum_max_workers。PG18 发布说明

速度：在 SSD 上，提高 autovacuum_vacuum_cost_limit（实际默认为 200），使 vacuum 能跟上节奏。

回收空间：VACUUM FULL 会重写表但在运行期间阻塞所有访问。pg_repack 以相同方式在线完成，仅需短暂锁定。

1.12 查询执行和连接

并行查询（文档）

max_parallel_workers_per_gather（默认 2）控制单个查询可以使用多少 CPU 核心。

分析型场景可以调高。高并发 OLTP 应保持较低值。

JIT 编译（文档）

默认开启，在查询成本超过 jit_above_cost（100000）时触发。

编译可能比中型查询本身耗时更长，因此许多 OLTP 系统设置 jit = off。

对于估计偏差很大的倾斜列，按列提高统计信息：ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000;

CREATE STATISTICS 教优化器了解相关列（如城市和邮政编码）。

连接池。每个 PostgreSQL 连接都是一个独立进程，因此数千客户端需要在前面加一个池化器：

PgBouncer：pool_mode = transaction 是常见选择。

PgCat：池化加负载均衡和分片。

Supavisor：云原生多租户方案。

1.13 可观测性插件

无法看到的东西就无法调优：

pg_stat_statements：按总时间排序的热门查询。首个应安装的扩展。

auto_explain：记录任何慢于 N 毫秒的查询的执行计划。

pg_stat_io（PG16）：按后端类型和上下文细分的 I/O。文档

track_io_timing = on：在 EXPLAIN (ANALYZE, BUFFERS) 和统计视图中加入 I/O 时间。

1.14 PostgreSQL 入门配置

以下为 PG18、64 GB 内存、16 vCPU、NVMe 服务器的起点。请务必做基准测试。PGTune 和官方调优 wiki 是很好的合理性检查。

# (a) NVMe 上的 OLTP
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 16MB
maintenance_work_mem = 2GB
huge_pages = on
wal_compression = lz4
checkpoint_timeout = 15min
max_wal_size = 16GB
random_page_cost = 1.1
effective_io_concurrency = 256
io_method = io_uring              # PG18 + liburing 构建；否则用 'worker'
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_scale_factor = 0.05
jit = off
shared_preload_libraries = 'pg_stat_statements,auto_explain,pg_prewarm'

# (b) 批量加载 / ETL（牺牲部分持久性，绝不会损坏）
synchronous_commit = off
wal_level = minimal               # 此机器无副本 / PITR
max_wal_senders = 0
archive_mode = off
max_wal_size = 64GB
checkpoint_timeout = 30min
maintenance_work_mem = 4GB
max_parallel_maintenance_workers = 8
# + UNLOGGED 暂存表 + COPY，然后 ALTER TABLE ... SET LOGGED

# (c) 分析 / 报表
work_mem = 256MB                  # 每个操作、每个工作进程！
hash_mem_multiplier = 4.0
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
max_worker_processes = 16
random_page_cost = 1.1
effective_io_concurrency = 256
default_statistics_target = 500
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

第 2 部分：MySQL（加 MariaDB 和 Percona）

2.1 MySQL 如何插入组件

MySQL 使"可插拔存储引擎"闻名。服务器层（解析器、优化器、复制）通过 handler API 与存储引擎通信。在此之上是一个通用插件系统，再加上较新的组件系统：

SHOW ENGINES;                                          -- 存储引擎
SHOW PLUGINS;                                          -- 所有已插入的组件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';          -- 经典插件
INSTALL COMPONENT 'file://component_validate_password'; -- 较新的组件模型

（Plugin loading · plugin types · components）

💡 与 PostgreSQL 的最大区别：MySQL 的存储引擎是一整套解决方案。它自带索引格式、锁机制、崩溃恢复和缓存。PostgreSQL 则将表引擎和索引类型拆分为独立的层，可以自由组合。

CREATE TABLE t (...) ENGINE=InnoDB;
ALTER TABLE t ENGINE=MyISAM;          -- full table copy

默认值来自 default_storage_engine。disabled_storage_engines 用于禁止你永远不会使用的引擎。

库存 MySQL 中的引擎（概览）

MariaDB 和 Percona Server 中的额外引擎

⚠️ MyRocks 的权衡取舍：不支持外键、不支持 FULLTEXT 或 SPATIAL 索引，且要求 binlog_format=ROW。局限性。TokuDB，另一款写优化引擎，已从 Percona 和 MariaDB 中移除（10.6 版本起移除）。

2.3 InnoDB 布局：聚簇索引、页大小和行格式

聚簇主键（文档）

InnoDB 将行数据存储在主键 B 树中，每个二级索引都把主键作为其行指针。因此：

• 主键要短。 36 字节的 UUID 字符串会被复制到每个二级索引中。
• 保持插入顺序。 随机 UUID 会导致页面分散在树的各处。UUID_TO_BIN(UUID(), 1) 将时间位重新排序，使新值大致按插入顺序排列。
• 没有主键时， InnoDB 会生成一个隐藏的 6 字节主键。sql_generate_invisible_primary_key（8.0.30）使其可见且明确。

innodb_page_size：4 KB 到 64 KB，默认 16 KB。

它只能在数据目录初始化时设置。较小的页适合 SSD 上的点查询；较大的页适合扫描。

DYNAMIC 是默认格式。它将长的 BLOB、TEXT 和 VARCHAR 值完全存储在页外，只在行中保留一个 20 字节的指针。

COMPRESSED 添加 zlib 压缩（见 2.8）。

ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT; 只修改元数据。自 8.0.29 起支持在任意位置添加列和删除列。

优化器使用的工具（替代或配合索引）

直方图（8.0）：ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 64 BUCKETS; 为优化器提供数据分布信息，而无需索引的写入开销。8.4 版本增加了 AUTO UPDATE。

Hash join（8.0.18）：取代了无法使用索引时的块嵌套循环连接。内存受 join_buffer_size 限制。

2.5 持久性：redo log、双写缓冲和 binlog

相关变量位于 InnoDB 参数和二进制日志选项页面。

批量加载的终极方案：ALTER INSTANCE DISABLE INNODB REDO_LOG（8.0.21）关闭整个实例的 redo 日志记录。

ALTER INSTANCE DISABLE INNODB REDO_LOG;   -- ONLY on a fresh instance you can rebuild
LOAD DATA INFILE '/data/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',';
ALTER INSTANCE ENABLE INNODB REDO_LOG;

❌ 在 redo 日志记录被禁用期间发生崩溃可能导致整个实例无法恢复。它仅适用于将数据加载到新实例中。

2.6 内存与缓冲池

innodb_buffer_pool_size：最重要的单一调优参数。默认值为 128 MB；在专用服务器上目标值约为 RAM 的 70–75%。它可以在线调整大小。

innodb_dedicated_server：根据机器的 RAM 和 CPU 数量来设置缓冲池和 redo log 的大小。在专用主机上这是一个很好的默认配置。

缓冲池预热：innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup（默认均开启）在关闭时保存最热门的页面，并在启动时重新加载。这是 MySQL 对应 pg_prewarm 的功能。（文档）

MySQL 8.4 更改了许多 InnoDB 默认值。如果你从 8.0 升级后性能发生变化，原因就在这里。（8.4 新特性）

innodb_flush_method = O_DIRECT（Linux 上 8.4 的默认值）绕过 OS 页面缓存，避免数据被缓存两次。

innodb_io_capacity / innodb_io_capacity_max 告诉 InnoDB 可以使用多少 IOPS 进行后台刷新。在快速 NVMe 上提高这些值。

innodb_flush_neighbors = 0（8.0 以来的默认值）阻止 InnoDB 一起刷新相邻页面。这个技巧只对机械硬盘有帮助。

innodb_read_io_threads / innodb_write_io_threads：后台 I/O 线程。需要重启。

innodb_tmpdir 将在线 ALTER TABLE 的临时排序文件放在更大或更快的磁盘上。

2.9 热数据与冷数据放置

将表放在不同的磁盘上

使用 innodb_file_per_table（默认设置），每个表都是自己的 .ibd 文件，DATA DIRECTORY 可以将其放在另一个磁盘上。

该目录必须列入 innodb_directories。

CREATE TABLE logs_archive (...) DATA DIRECTORY = '/mnt/hdd/mysql';

通用表空间将多个表分组到一个文件中，放在选定的磁盘上（文档）：

CREATE TABLESPACE ts_cold ADD DATAFILE '/mnt/hdd/ts_cold.ibd' ENGINE=InnoDB;
ALTER TABLE logs_2019 TABLESPACE ts_cold;

分区类型：RANGE、LIST、HASH 和 KEY，以及 COLUMNS 变体。

分区裁剪可以在 EXPLAIN 的 partitions 列中看到。

在 8.0+ 版本中，只有 InnoDB 和 NDB 支持分区。

经典的归档模式使用 EXCHANGE PARTITION，它能即时交换分区和表：

CREATE TABLE orders_2019 LIKE orders;
ALTER TABLE orders_2019 REMOVE PARTITIONING;
ALTER TABLE orders EXCHANGE PARTITION p2019 WITH TABLE orders_2019;  -- instant swap
ALTER TABLE orders DROP PARTITION p2019;
ALTER TABLE orders_2019 ENGINE=ARCHIVE;     -- or ENGINE=S3 on MariaDB → cold storage on S3

2.10 Lakehouse 与分析桥接

MySQL HeatWave：Oracle 的内存中、列式、横向扩展加速器，位于 InnoDB 旁边。HeatWave Lakehouse 查询对象存储中的 CSV、Parquet、Avro 和 JSON 文件，并与 InnoDB 表进行连接。它是一项托管云服务（Oracle Cloud、AWS 等），不属于 MySQL Community 的一部分。