深入解析两款主流数据库的存储引擎、索引类型、缓冲池及 WAL 机制,并覆盖 AI 负载下的 HNSW 向量索引实践。
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 的内容之前阅读链接的文档。
PostgreSQL 的超能力是它的扩展系统。很多看起来像"核心"功能实际上是以扩展形式交付的,它们使用公开的插入点:
在启动时挂载到服务器的扩展必须列在 shared_preload_libraries 中,这需要一次重启。查看已安装的扩展用 \dx,查看可用扩展用 SELECT * FROM pg_available_extensions;。
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。
这里是大多数性能提升或损失发生的地方。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
这些按表或按列设置,改变字节在磁盘上的布局方式。
fillfactor 和 HOT 更新
表的默认值为 100。设为 80–90 会在每个页面上留下空闲空间。
更新时就可以在同一页上写入新行版本,且如果没有索引列被更改,就不会被触碰索引。这就是 heap-only tuple(HOT)更新。
ALTER TABLE accounts SET (fillfactor = 85);
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
预写日志是使提交在崩溃后存活的原因。它也是写入速度最大的杠杆。除非另有链接,否则所有这些都在 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 版本起移除)。
InnoDB 将行数据存储在主键 B 树中,每个二级索引都把主键作为其行指针。因此:
UUID_TO_BIN(UUID(), 1) 将时间位重新排序,使新值大致按插入顺序排列。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 限制。
相关变量位于 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 日志记录被禁用期间发生崩溃可能导致整个实例无法恢复。它仅适用于将数据加载到新实例中。
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 的临时排序文件放在更大或更快的磁盘上。
使用 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
MySQL HeatWave:Oracle 的内存中、列式、横向扩展加速器,位于 InnoDB 旁边。HeatWave Lakehouse 查询对象存储中的 CSV、Parquet、Avro 和 JSON 文件,并与 InnoDB 表进行连接。它是一项托管云服务(Oracle Cloud、AWS 等),不属于 MySQL Community 的一部分。