POSTGRES 索引为何比 MYSQL 更高效

Source: Dev.to

（请提供需要翻译的正文内容，我才能为您完成简体中文的翻译。）

问题设置（两种数据库内部索引扫描工作原理）

表定义（MySQL 与 PostgreSQL）

CREATE TABLE "user" (
  id   BIGINT PRIMARY KEY,
  name VARCHAR(100),
  age  INT
);

索引

CREATE INDEX idx_user_name ON "user"(name);

查询

SELECT age FROM "user" WHERE name = 'Rahim';

• 表大小：1000 万行
• 索引类型：B‑Tree
• name 不是唯一的
• age 不在索引中

MySQL 执行

重要的 InnoDB 规则

二级索引存储的是 PRIMARY KEY，而不是物理行位置。
idx_user_name 条目形如：(name, primary_key_id)。

步骤说明

1. 遍历二级索引（name）
   MySQL 在 B‑Tree 中搜索 'Rahim'。
   成本：O(log N)

   示例叶子条目：('Rahim', id=73482)

2. 遍历 PRIMARY KEY 索引（聚簇索引）
   在 InnoDB 中，主键索引 就是 表本身；行按 PK 顺序存储。
   MySQL 使用 id = 73482 在 PK B‑Tree 中查找。
   成本：O(log N)

   叶子条目返回完整行：(id=73482, name='Rahim', age=29)

总结

• ✅ 聚簇索引提供良好的局部性
• ❌ 需要两次 B‑Tree 遍历
• ❌ PK 查找成本随表大小增长

PostgreSQL 执行

重要的 PostgreSQL 规则

索引存储 TID（元组 ID）——指向堆位置的指针。
idx_user_name 条目类似于：(name, (block_id, offset))。

步骤说明

1. 遍历 B‑Tree 索引（name）
   PostgreSQL 在索引中搜索 'Rahim'。
   成本：O(log N)

   叶子条目：('Rahim', TID=(block=102345, offset=7))

2. 堆获取（直接指针）
   PostgreSQL 直接跳到堆页 102345 并读取偏移量 7 处的行。
   成本：O(1)（概念上）

   返回的行：(id=73482, name='Rahim', age=29)

总结

• ✅ 仅一次 B‑Tree 遍历
• ✅ 堆获取为常数时间
• ❌ 堆页可能分散（局部性差）
• ❌ 由于 MVCC 需要额外的可见性检查

性能考虑（Big‑O 并非全部）

MySQL 更快的情况

• 主键较小
• 数据能够放入缓冲池
• 行按顺序访问
• 读取负载较重（OLTP）

➡️ 聚簇索引提供更好的局部性，使 MySQL 在这些情况下占优势。

PostgreSQL 更快的情况

• 表非常大
• 二级索引很多
• 随机访问模式
• 可以进行仅索引扫描

➡️ 基于指针的访问和常数时间的堆获取为 PostgreSQL 带来优势。

仅索引扫描示例

如果你将索引修改为包含 age：

CREATE INDEX idx_user_name_age ON "user"(name, age);

运行相同的查询：

SELECT age FROM "user" WHERE name = 'Rahim';

• PostgreSQL 可以在 不访问堆表 的情况下返回结果（仅索引扫描）。
• MySQL 由于其二级索引不包含所需的列，无法以同样的方式执行真正的仅索引扫描。

比较摘要

结论

• MySQL 遵循 “查找索引 → 查找主键 → 查找行” 的路径，对小型到中型、顺序工作负载效率较高。
• PostgreSQL 的基于指针的方法（O(log N) + O(1)）为大型、随机访问工作负载提供了更好的可扩展性，并支持真正的仅索引扫描。