PG 与 MySQL 内存对照：执行层排序、连接、临时表与缓冲池

上篇：PostgreSQL

1. 排序（Tuplesort / work_mem）

• 多 Sort 节点 → 可能多份 work_mem（× 并发）。
• EXPLAIN ANALYZE：quicksort vs external merge（spill）。

参数：work_mem

2. 等值连接（Hash Join）

等值 JOIN：Build 小表 HashJoinTable → Probe 大表。无索引时常用；有索引时多为 Index Nested Loop。

参数：work_mem；PG 13+ 可用 hash_mem_multiplier。

EXPLAIN：Hash Join → Hash → 子扫描。

3. 排序 vs 临时表（Tuplesort vs Materialize）

计划组合

案例 1：只排序

1
2
3
4
5

SELECT id, created_at
FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

索引满足顺序 → 可能无 Sort；否则 Index Scan + Sort，一般无 Materialize。

案例 2：只临时表/聚合

1
2
3

SELECT city, COUNT(*) AS cnt
FROM orders
GROUP BY city;

常见 HashAggregate 或 GroupAggregate，无 ORDER BY 则常无 Sort。

1
2
3

SELECT * FROM (
    SELECT user_id, SUM(amount) AS s FROM orders GROUP BY user_id
) AS t WHERE s > 1000;

子查询 → Materialize；外层过滤，无 Sort。

案例 3：临时表 + 排序

1
2
3
4

SELECT city, COUNT(*) AS cnt
FROM orders
GROUP BY city
ORDER BY cnt DESC;

HashAggregate → Sort：先按 city 聚合，再对 (city,cnt) 排序。

4. 缓冲池（Shared Buffers）

数据文件在 $PGDATA/base/ 等路径。读页：磁盘 → OS Page Cache → Shared Buffers。

执行层不在 Shared Buffers 里：Tuplesort、HashJoinTable、临时表（用 local buffers）。

命中率 SQL（须小数除法）：

1
2
3
4
5
6
7

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_percent
FROM pg_stat_database
WHERE datname = current_database();

1
2
3
4

SELECT datname,
       blks_hit::numeric / nullif(blks_hit + blks_read, 0) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

整数相除 (blks_hit)/(blks_hit+blks_read) 会得到 0。

文档：work_mem、pg_stat_database

下篇：MySQL 8.0

1. 排序（sort buffer / filesort）

Using filesort：Server 层排序；不一定写盘。

参数：sort_buffer_size

文档：10.12.3.1

2. 连接（Hash Join / Join Buffer）

一次 JOIN 只走一种主路径：

• Join Buffer：BNL 缓存驱动表行，不是 Hash Join 哈希表。
• Using join buffer (hash join) 与 (Block Nested Loop) 不同算法。

Hash 表过大 → tmpdir chunk 落盘。

文档：Hash Joins

3. sort buffer vs 内部临时表（TempTable）

Extra 组合

案例 1：仅 filesort

1
2
3
4
5

SELECT id, created_at, amount
FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

1

扫描 → sort buffer 排序 → LIMIT

有合适索引时可能无 filesort、无 temporary。

案例 2：仅 temporary

1
2
3

SELECT city, COUNT(*) AS cnt
FROM orders
GROUP BY city;

1

扫描 → TempTable 按 city 累加 → 输出

无 ORDER BY → 通常无 filesort。

案例 3：temporary + filesort

1
2
3
4

SELECT city, COUNT(*) AS cnt
FROM orders
GROUP BY city
ORDER BY cnt DESC;

1
2

阶段1：TempTable 聚合     ← Using temporary
阶段2：sort buffer 按 cnt 排序 ← Using filesort

GROUP BY 不能靠 sort buffer 累加；聚合后的行集无序，必须再 filesort。

案例 4：DISTINCT + ORDER BY

1

SELECT DISTINCT city FROM orders ORDER BY city;

常 temporary + filesort（以 EXPLAIN 为准）。

监控：Created_tmp_tables、Created_tmp_disk_tables、Sort_merge_passes

文档：10.4.4

4. 缓冲池（Buffer Pool）

表空间为 .ibd 等文件。读页：磁盘 → OS Page Cache → Buffer Pool（Linux 上常 O_DIRECT 减轻双缓存）。

不在 Buffer Pool：sort buffer、Join Buffer、Hash Join 表、TempTable 数据；undo 页读入后 在 Buffer Pool。

文档：17.5.1

对照（简表）

InnoDB 全进程内存（redo、undo、binlog）：进程内存总图