在PostgreSQL中,当将多个列的组合指定为
假设PostgreSQL按照主键的顺序对记录进行排序,这是吗?
此外,在PostgreSQL的情况下,主键是否会自动创建索引?
PRIMARY KEY
时,记录是如何排序的?假设PostgreSQL按照主键的顺序对记录进行排序,这是吗?
此外,在PostgreSQL的情况下,主键是否会自动创建索引?
PRIMARY KEY
时,记录是如何排序的?这个问题有一个错误的假设,即主键会对表的顺序产生影响。实际上并不会。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;
EXPLAIN ANALYZE
命令和psql
命令行工具。这两个工具将帮助你更好地了解 PostgreSQL 的工作原理,分析查询的执行方式,测试不同的索引策略等等。在尝试理解大型和复杂的查询计划时,http://explain.depesz.com/ 也是一个有用的工具。 - Craig Ringer