PostgreSQL 复合主键 [英] PostgreSQL composite primary key
问题描述
在 MySQL 中,当我创建复合主键时,比如列 X、Y、Z
,那么所有三列都会自动成为索引.Postgres 也会发生同样的情况吗?
如果你创建了一个复合主键,在 (x, y, z)
上,PostgreSQL 在一个 (x, y, z)
的帮助下实现了这个code>UNIQUE (x, y, z)
上的多列 btree 索引.此外,所有三列都是NOT NULL
(隐式),这是PRIMARY KEY
和UNIQUE INDEX
之间的主要区别.>
除了对数据的明显限制外,多列索引与 x
、y
和 z
上的三个单独索引相比,a> 对查询性能的影响也有所不同.
dba.SE 相关讨论:
通过示例、基准测试、讨论和对 Postgres 9.2 中的索引扫描.
特别是,(x, y, z)
上的主键将加快x
, (x,y)
条件下的查询速度code> 或 (x,y,z)
最佳.它还有助于查询 y
、z
、(y,z)
或 (x,z)
但程度要小得多.
如果您需要加快对后一种组合的查询,您可能需要更改 PK 约束中的列顺序和/或创建一个或多个附加索引.见:
In MySQL, when I create a composite primary key, say with columns X, Y, Z
, then all three columns become indexes automatically. Does the same happen for Postgres?
If you create a composite primary key, on (x, y, z)
, PostgreSQL implements this with the help of one UNIQUE
multi-column btree index on (x, y, z)
. In addition, all three columns are NOT NULL
(implicitly), which is the main difference between a PRIMARY KEY
and a UNIQUE INDEX
.
Besides obvious restrictions on your data, the multi-column index also has a somewhat different effect on the performance of queries than three individual indexes on x
, y
and z
.
Related discussion on dba.SE:
With examples, benchmarks, discussion and outlook on the new feature of index-only scans in Postgres 9.2.
In particular, a primary key on (x, y, z)
will speed up queries with conditions on x
, (x,y)
or (x,y,z)
optimally. It will also help with queries on y
, z
, (y,z)
or (x,z)
but to a far lesser extent.
If you need to speed up queries on the latter combinations, you may want to change the order of column in your PK constraint and/or create one or more additional indexes. See:
这篇关于PostgreSQL 复合主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!