PostgreSQL中是否要创建外键索引

我们常常听说索引可以提升读的性能，通常这也是对的，但是我们也知道这会降低写的性能。我们没有经常听到的是，在有些场景下，索引不会提升任何性能。外键就是个例子。这里不是说所有外键都不好，但我看到的大多场景是不需要外键，有了只是增加系统负载而已。

比如，下面的两张表是1:N的对应关系。

 

如果我们注意本示例中的外键，相比使用“ProductID”和“ProductName”，则使用外键列（本示例中的“SupplierID”）在子表上的查找次数不会很多。主要用途是保持关系一致，并在另一个方向搜索，找到某个产品的供应商。在这种情况下，在不确保访问模式的情况下向外键子表添加索引会增加每次更新“Product”表时更新索引的额外成本。

还有一点我们需要注意的是索引基数。如果索引基数太低Postgres 将不会使用它并且索引将被忽略。有人可能会问为什么会这样，难道通过一半的索引扫描不比全表扫描快？尤其是对于像Postgres这样使用堆表的数据库。Postgres（堆表）中的全表访问主要是顺序的，这比HDD 磁盘中的随机访问要快；在 SSD 上仍然要快一点，毕竟 b+-tree 索引访问本质上是随机的。

当Postgres使用索引时，它需要打开索引文件，找到它需要的记录，然后打开表文件，使用它从索引中获得的页面地址进行查找，将访问模式从顺序更改为随机，这取决于数据分布它可能会访问大部分表页，最终以全表扫描结束，但现在使用随机访问，代价要昂贵得多。如果我们有低基数的列并且我们真的需要索引它们，我们需要使用b树索引的替代方法，例如GIN索引，但这是另一个讨论的主题。

这样一来，我们就可以认为外键索引总是不好的，永远不在子表上使用它们？嗯，也并非如此。它们在很多情况下都是有用和需要的，例如，下图还有另外两个表，“Customer”和“Order”：

 

在子表“Order->CustomerId”上建立索引会很方便，因为通常会显示来自某个用户的所有订单，并且表“Order”上的“CustomerId”列将经常用作查找键.

另一个很好的例子是提供一种更快的方法来验证参照完整性。如果需要更改父表（更新或删除任何父键），则需要检查子表以确保关系没有中断。在这种情况下，在子表一侧设置索引将有助于提高性能。然而，创建索引是否值得是依赖于负载的，如果父键有很多删除，则可能需要考虑，但是，如果它主要是静态表，或者主要是对父键列以外的其他列进行插入或更新，则不适合在子表上创建外键索引。

 

结论

这里的要点是，我们不应该不加选择地在所有外键上创建索引，因为其中许多不会被使用或很少使用，以至于它们不值得。最好是在设计的时候有外键依赖，但并不创建外键索引，需要的时候再创建。负载和数据分布也会发生变化，索引应跟随它们发生变化，而不是被视为不可变的实体。

 

 

原文：https://www.percona.com/blog/should-i-create-an-index-on-foreign-keys-in-postgresql/