具有复合主键的表中记录的顺序是什么？

Question

具有复合主键的表中记录的顺序是什么？

postgresqlsortingprimary-key

20

在PostgreSQL中，当将多个列的组合指定为PRIMARY KEY时，记录是如何排序的？

假设PostgreSQL按照主键的顺序对记录进行排序，这是吗？

此外，在PostgreSQL的情况下，主键是否会自动创建索引？

- Abhishek Jain

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Ringer · Accepted Answer

这个问题有一个错误的假设，即主键会对表的顺序产生影响。实际上并不会。PostgreSQL表没有定义的顺序，无论是否有主键；它们是排列在页面块中的行的“堆”。当需要时，可以使用查询的ORDER BY子句来强制排序。

你可能认为PostgreSQL表以索引为导向的表格形式存储在磁盘上，按照主键顺序排列，但Pg并不是这样工作的。我想InnoDB将表格按主键组织（但还没有检查过），而且其他一些供应商的数据库也可以使用一个通常称为“聚集索引”或“索引组织表”的特性进行可选设置。这个特性目前在PostgreSQL中不被支持（至少在9.3版本中）。

尽管如此，PRIMARY KEY是使用UNIQUE索引实现的，并且该索引具有排序功能。它按照从左侧列开始（因此是主键）的升序排序，就好像它是ORDER BY col1 ASC, col2 ASC, col3 ASC;。对于PostgreSQL中的任何其他b-tree（与GiST或GIN不同）索引也是如此，因为它们是使用b+trees实现的。

所以在表格中：

CREATE TABLE demo (
   a integer,
   b text, 
   PRIMARY KEY(a,b)
);

系统将自动创建相应的内容：

CREATE UNIQUE INDEX demo_pkey ON demo(a ASC, b ASC);

当您创建表格时，将向您报告此内容，例如：

regress=>     CREATE TABLE demo (
regress(>        a integer,
regress(>        b text, 
regress(>        PRIMARY KEY(a,b)
regress(>     );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "demo_pkey" for table "demo"
CREATE TABLE

当查看表格时，您可以看到此索引：

regress=> \d demo
     Table "public.demo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | not null
 b      | text    | not null
Indexes:
    "demo_pkey" PRIMARY KEY, btree (a, b)

你可以在此索引上进行CLUSTER操作，以根据主键重新排序表格，但这是一次性操作。系统不会维护该顺序-尽管如果由于非默认的FILLFACTOR而在页面中有空闲空间，它将尝试这样做。

索引固有的排序（但堆不是）的一个结果是，搜索更快：

SELECT * FROM demo ORDER BY a, b;
SELECT * FROM demo ORDER BY a;

比:

SELECT * FROM demo ORDER BY a DESC, b;

这两种方法都无法使用主键索引，除非你在b上有一个索引，否则它们将执行序列扫描：

SELECT * FROM demo ORDER BY b, a;
SELECT * FROM demo ORDER BY b;

这是因为PostgreSQL几乎可以像仅使用索引(a)一样快速地使用索引(a，b)。它不能像单独使用(b)的索引那样使用(a，b)上的索引，甚至不慢，它根本无法使用。

至于DESC条目，Pg必须执行反向索引扫描，这比普通的正向索引扫描要慢。如果在EXPLAIN ANALYZE中看到了许多反向索引扫描，并且可以承受额外索引的性能成本，则可以创建一个按DESC字段顺序排序的索引。

这对于WHERE子句而言同样适用，而不仅仅是ORDER BY。您可以使用(a，b)上的索引来搜索WHERE a = 4或WHERE a = 4 AND b = 3，但不能仅使用WHERE b = 3进行搜索。