注意缺失的外键索引：Postgres 性能陷阱

2025-06-11

注意缺失的外键索引：Postgres 性能陷阱

过去一周，我被 PostgreSQL 的一些性能问题彻底搞垮了。这很痛苦，但也让我受益匪浅。我不仅解决了问题，还对 Postgres 有了更深入的理解。现在，我能够更好地诊断下一次遇到的性能问题。我将通过这篇文章来分享我的经验教训。

我来给你演示一下它有多糟糕。我有一个查询，在大约 100 毫秒内删除了一个表上的 5 万条记录。这对我来说已经足够快了。然后我又有一个类似的查询，从另一个表中删除相同数量的记录，运行了大约 30 分钟。慢了 10,000 倍！😱 这真是太糟糕了。

在诊断并解决问题后，我将第二次删除查询的时间缩短到了大约 1 秒。好多了！在文章的最后，我们将讨论如何让它更快。

但在我重建一个重现问题的最小示例并深入了解细节之前，这里是......

总结

外键对于确保数据的结构和完整性至关重要。索引可以提高查询速度。我们可以将两者结合起来，充分利用数据库。

我通常不认为外键约束会影响性能。但正如我们即将看到的，它们确实会影响性能。

如果没有该索引，我们必然会遇到一些真正隐蔽的性能问题。

让我们深入研究一下。

一个最小的例子

这个简单的例子也是一个真实的例子。大多数软件系统都有用户，这些用户需要被赋予各种角色。这些角色帮助系统确定每个用户的访问权限。

如果您想跳过示例设置，您可以直接跳到详细信息。

数据模型

替代文本

首先是users表格。它在示例中不太突出，所以我把它放在id列中。

然后我们有了一个roles表，其中可以包含全局角色，如admin和，support以及与特定资源相关的角色，如team_owner和team_collaborator。

在两者之间建立关系是通过users_roles连接表来实现的。此表中将我的记录user与admin角色绑定的记录会告诉系统我是管理员。此表中将我的user记录与team_collaborator红队角色绑定的另一条记录会告诉您，除了管理员之外，我还是红队的合作者。

构建模式

以下是一些 SQL，它们将生成用于重现性能问题的模式。其具体工作原理超出了本文的讨论范围。如果您对此感兴趣，请告诉我，我会写一篇后续文章。

完整的查询嵌入在这些代码块中，但您也可以查看此 repo中的代码。

create table users (
  id bigint generated by default as identity primary key
);
create table roles (
  id bigint generated by default as identity primary key,
  name varchar not null,
  resource_type varchar,
  resource_id bigint
);
create table users_roles (
  user_id bigint references users,
  role_id bigint references roles
);
Enter fullscreen mode Exit fullscreen mode

我把它放在一个名为的文件中schema.sql。我从psql会话连接到一个空数据库，在那里我可以安全地进行实验。然后我告诉psql它执行脚本，它将创建这三个表及其序列。

> \i schema.sql
CREATE TABLE
Time: 2.605 ms
CREATE TABLE
Time: 2.615 ms
CREATE TABLE
Time: 1.141 ms
Time: 0.099 ms
> \d
               List of relations
 Schema |     Name     |   Type   |   Owner
--------+--------------+----------+------------
 public | roles        | table    | jbranchaud
 public | roles_id_seq | sequence | jbranchaud
 public | users        | table    | jbranchaud
 public | users_id_seq | sequence | jbranchaud
 public | users_roles  | table    | jbranchaud
(5 rows)
Enter fullscreen mode Exit fullscreen mode

生成一些数据

让我们借助generate_series函数来填充这些表格中的数据。同样，我不会在这里详细介绍。如果您对这部分的后续文章感兴趣，请留言。

-- create 50,000 users
insert into users
select from generate_series(1,50000);

-- create 100,000 roles
insert into roles (
  name,
  resource_type,
  resource_id
)
select
  (array['admin', 'support', 'member'])[floor(random() * 3 + 1)],
  null,
  null
from generate_series(1,50000);

insert into roles (
  name,
  resource_type,
  resource_id
)
select
  'team_collaborator',
  'Team',
  floor(random() * 1000 + 1)
from generate_series(1,50000);

-- create 500,000 connections between users and roles
-- start with 225,000 random global roles
insert into users_roles (
  user_id,
  role_id
)
select
  floor(random() * 50000 + 1),
  floor(random() * 50000 + 1)
from generate_series(1,225000);

-- then 50,000 for the team collaborator role
insert into users_roles (
  user_id,
  role_id
)
select
  floor(random() * 50000 + 1),
  floor(50000 + g.id)
from generate_series(1,50000) as g(id);

-- then another 225,000 random global roles
insert into users_roles (
  user_id,
  role_id
)
select
  floor(random() * 50000 + 1),
  floor(random() * 50000 + 1)
from generate_series(1,225000);
Enter fullscreen mode Exit fullscreen mode

这将创建 50,000 个用户和 100,000 个角色。然后在users_roles连接表中创建它们之间的 500,000 个连接，其中 50,000 个连接专门用于该team_collaborator角色。

所有数据都到位后，我们来找出一些特定的数据并将其删除。这时，我们会看到一个合理的查询和一个速度明显变慢的查询。

快速查询、慢速查询

在我处理的实际生产数据集中，我需要从表中清除大量数据roles。这些数据大约有 5 万条记录。由于存在外键关系，我必须首先清除users_roles表中的依赖记录。

以下内容可能感觉有点牵强，但它是基于真实场景的。

以下是我们要删除的 50k 个角色。

> select count(*)
  from roles
  join users_roles
    on roles.id = users_roles.role_id
  where roles.name = 'team_collaborator';

 count
-------
 50000
(1 row)
Enter fullscreen mode Exit fullscreen mode

为什么要删除所有team_collaborator关联数据？因为这样一来，我们就会剩下大约 5 万行数据。就像我说的，这有点牵强。不过，我们还是可以从中学到一些性能方面的知识。

快速删除

让我们首先删除相关记录users_roles。

每当我要修改或删除数据时，我都喜欢开启一个事务。这样，如果出现任何问题，就可以安全地回滚更改。这可以通过 来实现begin。

> begin;

> delete from users_roles
  using roles
  where users_roles.role_id = roles.id
    and roles.name = 'team_collaborator';

DELETE 50000
Time: 116.313 ms
Enter fullscreen mode Exit fullscreen mode

看起来不错。该查询似乎处理了users_roles我想要删除的 5 万条记录。因为我处于事务中，所以我总是可以用几个选择查询来深入研究数据以确保万无一失。而且它足够快，在这个例子中大约 100 毫秒。

我还没准备好提交此事务。接下来我想尝试删除roles记录。

缓慢删除

处理完所有外键依赖关系后，我们就可以清楚地继续我们的主要目标，删除roles记录。

这个查询比上一个更简单。我把它打出来后，确信它会运行得一样快。

> delete from roles
  where roles.name = 'team_collaborator';

-- ... keep waiting ...
Enter fullscreen mode Exit fullscreen mode

不过说实话，如果你在家跟着做，就不用太担心了。根据你的机器配置，这个过程大概需要 30 分钟左右。

怎么回事？

我们能解释一下吗？

这个查询和上一个查询没什么不同。它的目标是相同的行数。是什么原因导致速度变慢的？

让我们首先看一下explain。

> explain delete from roles
  where roles.name = 'team_collaborator';

                            QUERY PLAN
------------------------------------------------------------------
 Delete on roles  (cost=0.00..1937.00 rows=49990 width=6)
   ->  Seq Scan on roles  (cost=0.00..1937.00 rows=49990 width=6)
         Filter: ((name)::text = 'team_collaborator'::text)
(3 rows)
Enter fullscreen mode Exit fullscreen mode

这部分没什么特别的。但有一点seq scan让我印象深刻。这意味着查询规划器预期查询必须按顺序查看每一行。对于仅有约 5 万行的数据来说，这似乎不成问题。

相比之下，这是explain前一个查询的。

> explain delete from users_roles
  using roles
  where users_roles.role_id = roles.id
    and roles.name = 'team_collaborator';

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Delete on users_roles  (cost=2561.88..11577.43 rows=249950 width=12)
   ->  Hash Join  (cost=2561.88..11577.43 rows=249950 width=12)
         Hash Cond: (users_roles.role_id = roles.id)
         ->  Seq Scan on users_roles  (cost=0.00..7703.00 rows=500000 width=14)
         ->  Hash  (cost=1937.00..1937.00 rows=49990 width=14)
               ->  Seq Scan on roles  (cost=0.00..1937.00 rows=49990 width=14)
                     Filter: ((name)::text = 'team_collaborator'::text)
(7 rows)
Enter fullscreen mode Exit fullscreen mode

这个比较复杂。我注意到它有（50万行）的数据seq scan，users_roles下面嵌套着另一个（约5万行）的数据seq scan。roles就预计的工作量而言，这个数据还有很多工作要做，但它只用了100毫秒就完成了。计算机的速度真是快啊。

尽管进行了这样的比较，但从运行查询的结果来看，第一个（更简单的）查询将比第二个查询花费更长的时间。一些粗略的计算表明，用于删除数据的更简单的查询计划roles所需的时间将是第二个查询的 10,000 倍😱。

这是这篇文章中的第二个尖叫表情符号——我们必须弄清楚发生了什么。

这个“简单”的删除查询有什么作用？

揭开谜底

> delete from roles
  where roles.name = 'team_collaborator';
Enter fullscreen mode Exit fullscreen mode

这个两行、5 万行的查询的问题与表本身无关，而是与该表与其他表的roles关系有关。roles

该users_roles表依赖于roles表本身，具体来说，依赖于其id列。这种依赖roles.id是通过其自身列上的外键约束来实现的role_id。

外键导致此问题

有点。

为了证明外键约束是此性能问题的核心，让我们首先删除外键约束。

> begin;

> delete from users_roles
  using roles
  where users_roles.role_id = roles.id
    and roles.name = 'team_collaborator';

DELETE 50000
Time: 120.267 ms

> alter table users_roles
  drop constraint users_roles_role_id_fkey;

ALTER TABLE
Time: 0.561 ms

> delete from roles
  where roles.name = 'team_collaborator';

DELETE 50000
Time: 54.115 ms

> rollback;
Enter fullscreen mode Exit fullscreen mode

取消外键约束后，删除操作非常快。请注意，我立即回滚了这些更改。这是为了强调约束的影响。这不是解决方案。

保持外键约束

外键约束对于可靠的模式设计至关重要，也是我最喜欢的 Postgres 功能之一。它有助于确保两个表之间的数据关系始终完整（这称为引用完整性）。有了这种保证，我再也不用担心数据会无意中被孤立。

外键约束如何对性能产生如此大的影响？

对于您指示 Postgres 从表中删除的每一行roles，它都会首先检查所有依赖表，以确保可以执行此操作。如果关联表中的任何记录均未使用外键值，则删除操作可以继续。即使只有一条记录依赖于该值，外键约束也会标记该情况并中止删除操作。

因此，即使我们预先删除了所有相关数据users_roles，Postgres 仍然必须在删除时检查是否仍然如此。

Postgres 会遍历users_roles整个表，并按顺序扫描每一条记录。对于每条roles记录，Postgres 将检查 50 万条users_roles记录。

这就是问题的根源。

查询计划器需要更多信息

Postgres 将利用其掌握的信息来验证外键约束。目前，它没有足够的信息来做除了查看每一行之外更好的事情。但如果 Postgres 有索引，它就能获得必要的信息，从而减少很多工作。

数据库索引大致类似于图书馆使用的组织系统。书籍被分类，然后根据同一类别的其他书籍进行排序。使用这个系统，您可以快速导航到书籍应该在的位置。

没有索引users_roles.role_id就像走进一个没有组织系统的图书馆，你必须从一端开始一本一本地翻阅，可能要翻阅数万本书，直到找到你要找的东西。

通过为 添加索引，我们可以将查询时间缩短到1 秒delete左右。索引为 Postgres 提供了所需的信息，以便快速找到任何依赖行。users_roles.role_id

create index role_id_index
  on users_roles (role_id);

CREATE INDEX
Time: 342.591 ms
Enter fullscreen mode Exit fullscreen mode

这会为该列添加一个索引users_roles.role_id。它是一个B 树索引。“每当比较涉及索引列时（使用诸如=、<、>等运算符），PostgreSQL 查询规划器都会考虑使用 B 树索引”。

使用此索引，我们通常会获得更快的查询速度，尤其是在执行删除操作时。但有一个缺点，索引会占用少量磁盘空间。由于 Postgres 会在每次写入索引列时自动更新索引，因此这会增加一点点的写入时间。在绝大多数情况下，这两种缺点都是值得的。

让我们看看该指数的表现如何。

删除速度更快

有了这个索引，让我们再试一次我们的删除查询。

> begin;

> delete from users_roles
  using roles
  where users_roles.role_id = roles.id
    and roles.name = 'team_collaborator';

DELETE 50000
Time: 111.732 ms

> delete from roles
  where roles.name = 'team_collaborator';

DELETE 50000
Time: 590.668 ms
Enter fullscreen mode Exit fullscreen mode

眨眼间就搞定了。第二个删除查询从大约 30 分钟缩短到了 600 毫秒以内。这是一个巨大的进步。这就是索引能帮我们做到的。

这让我回想起这篇文章的tl;dr 。

外键对于确保数据的结构和完整性至关重要。索引可以提高查询速度。我们可以将两者结合起来，充分利用数据库。

如果没有该索引，您肯定会遇到一些真正隐蔽的性能问题。

但是我们可以走得更快吗？！

删除速度更快

由于我们处于事务中并且正在进行大量删除操作，如果我们告诉 Postgres 推迟验证外键约束直到完成删除操作，结果会怎样呢？

> begin;

> alter table users_roles
  alter constraint users_roles_role_id_fkey
    initially deferred;

ALTER TABLE
Time: 0.455 ms

> delete from users_roles
  using roles
  where users_roles.role_id = roles.id
    and roles.name = 'team_collaborator';

DELETE 50000
Time: 114.574 ms

> delete from roles
  where roles.name = 'team_collaborator';

DELETE 50000
Time: 68.433 ms
Enter fullscreen mode Exit fullscreen mode

哇，这又使其速度提高了 10 倍，达到约 68 毫秒。

即使使用索引，它也无需检查外键约束 50,000 次，只需在最后检查一次即可。太棒了！

总结

在遇到这个极其缓慢的删除查询后，我花了很长时间试图找出导致性能下降的原因。即使你每次只能理解一点点，只要你愿意深入研究一个难题并努力理解，就能学到很多东西。我最终得到了更好的结果。我希望你读完这篇文章后也能有所收获。

外键有助于确保数据的完整性。索引有助于提高查询速度。将两者结合起来，可以真正发挥 PostgreSQL 的速度和强大功能。

如果您喜欢我的作品，请考虑订阅我的时事通讯或在推特上关注我。

笔记

  1. 对此模式的一个改进是向外键约束添加一条指令on delete cascade。role_id这意味着我们可以直接删除roles记录，相应的users_roles记录也会被自动移除。但这仍然会受到相同的性能问题的影响。

  2. Postgres 允许并发添加索引，以避免锁定对整个表的写入。虽然添加索引是一个相当快速的操作，但并发添加是一个很好的经验法则。在生产环境中处理实时表时尤其如此。为了避免再引入一个概念，我选择在本文中不并发添加索引。

致谢

非常感谢几位阅读本文初稿并提出反馈的人。他们太棒了。看看他们的作品吧。

封面照片由Ryan Johnston在Unsplash上拍摄

鏂囩珷鏉ユ簮锛�https://dev.to/jbranchaud/beware-the-missing-foreign-key-index-a-postgres-performance-gotcha-3d5i
Devto 翻译
PREV
Sequelize + TypeScript - 你需要知道的
NEXT
将你的 React 应用组织成模块