关于postgres中的聚集索引

Question

我正在使用 psql 访问 postgres 数据库。在查看表的元数据时，有什么办法可以看出表的索引是否是聚集索引？

我听说表的主键会自动关联聚集索引，是真的吗？

Answer 1

请注意，PostgreSQL 使用术语“聚集索引”来使用与 SQL Server 大致相似但又非常不同的内容。

如果某个特定索引已被指定为表的聚簇索引，则 psql 的 \d 命令将指示聚簇索引，例如，

Indexes:
    "timezone_description_pkey" PRIMARY KEY, btree (timezone) CLUSTER

PostgreSQL 默认情况下不指定索引作为聚簇索引。即使如此指定，它也不会自动排列表数据以与聚集索引相关：必须使用 CLUSTER 命令来重新组织表数据。

Answer 2

在 PostgreSQL 中，聚集属性保存在相应索引的元数据中，而不是关系本身。它是 pg_index 目录中的 indisclustered 属性。但请注意，postgres 中的集群关系是一次性操作：即使该属性为 true，对表的更新也不会保持数据的排序性质。迄今为止，数据集群的自动维护仍然是一个流行的TODO项目。

聚集和集成索引之间经常会产生混淆，特别是因为流行的教科书使用了相互冲突的名称，而且 postgres 和 SQL Server 手册中的术语也不同（命名为只有两个）。当我谈论集成索引（也称为主索引或主索引）时，我指的是其中关系数据包含在索引的叶子，而不是外部或辅助索引，其中叶子包含指向表记录的索引条目。前一种类型必然总是聚集的。不幸的是 postgres 仅支持后一种类型。无论如何，集成（主）索引总是聚集的这一事实可能会引起“表的主键自动与聚集索引相关联”的信念。这两种说法听起来相似，但又有所不同。

Answer 3

PostgreSQL 不像 Microsoft SQL Server 那样直接实现 CLUSTER 索引。

参考此博客：

在 PostgreSQL 中，我们有一个类似于 Cluster Index 的 CLUSTER 命令。

创建表主键或任何其他索引后，您可以通过指定索引名称来执行 CLUSTER 命令，以实现表数据的物理顺序。

当表被聚集时，它会根据索引信息进行物理重新排序。集群是一种一次性操作：当表随后更新时，更改不会集群。也就是说，不会尝试根据索引顺序存储新的或更新的行。

集群语法：

第一次必须使用索引名称执行CLUSTER。

CLUSTER table_name USING index_name;

对表进行聚类：

一旦您执行了带有索引的CLUSTER，下次您应该只执行CLUSTER TABLE，因为它知道哪个索引已经定义为CLUSTER。

CLUSTER table_name;

Answer 4

有什么方法可以查看表的索引是否是聚集索引

PostgreSQL 没有聚集索引，因此您将无法看到它们。

听说表的主键会自动关联聚集索引，是真的吗？

不，这不是真的（见上文）

您可以沿着索引手动聚集表，但这是没有任何内容会自动维护（例如使用 SQL Server 的聚集索引）。

有关更多详细信息，请参阅 CLUSTER 命令的说明手动的。

Answer 5

---

簇索引

簇索引意味着告诉数据库在磁盘上存储实际上彼此接近的接近值。它们可以唯一地标识 SQL 表中的行。每个表只能有一个聚集索引。聚簇索引可以覆盖多个列。默认情况下，具有主键的列已经具有聚集索引。

字典

字典本身就是一个带有聚集索引的表。因为所有数据在物理上都是按字母顺序存储的。

---

非聚集索引

非聚集索引就像一本书的简单索引。它们仅用于快速检索数据。不确定是否有独特的数据。非聚集索引包含非聚集索引键及其相应的数据位置指针。例如，书籍的内容索引包含主题或章节的关键字及其页面位置。

图书内容索引

图书的内容表保存内容名称及其页面位置。不确定数据是否唯一。因为相同的段落或文本行或单词可以放置多次。

---

PostgreSQL 索引

PostgreSQL 自动为表的PRIMARY KEY 和每个UNIQUE 约束创建索引。在 PostgreSQL 终端中登录数据库并输入 \d table_name。所有存储的索引都将被可视化。如果存在聚集索引，那么它也会被识别。

创建表

CREATE TABLE IF NOT EXISTS profile(
    uid serial NOT NULL UNIQUE PRIMARY KEY,
    username varchar(30) NOT NULL UNIQUE,
    phone varchar(11) NOT NULL UNIQUE,
    age smallint CHECK(age>12),
    address text NULL
);

3时会自动创建索引。所有这些索引都是非聚集索引

"profile_pkey" PRIMARY KEY, btree (uid)
"profile_phone_key" UNIQUE CONSTRAINT, btree (phone)
"profile_username_key" UNIQUE CONSTRAINT, btree (username)

用uid和username创建我们自己的索引

CREATE INDEX profile_index ON profile(uid, username);

这实际上创建了一个非聚集索引。要使其成为集群，请运行下一部分。

将非聚集索引转换为聚集索引

ALTER TABLE profile CLUSTER ON profile_index;

使用\d profile检查表。它将是这样的：

 表“public.profile”
  专栏 |类型 |整理 |可空 |默认
----------+------------------------+------------+--- -------+----------------------------------------
 uid |整数 | |不为空 | nextval('profile_uid_seq'::regclass)
 用户名 |字符变化(30) | |不为空 |
 电话 |字符变化(11) | |不为空 |
 年龄 |小整数 | | |
 地址 |文字| | |
索引：
    “profile_pkey”主键，btree（uid）
    “profile_phone_key”唯一约束，btree（电话）
    “profile_username_key”唯一约束，btree（用户名）
    “profile_index”btree（uid，用户名）集群
检查约束：
    “profile_age_check”检查（年龄 > 12）

请注意，profile_index 现在是“CLUSTER”

现在，重新对表进行集群，以便该表可以遵循集群索引角色

CLUSTER profile;

Answer 6

如果您想知道给定的表是否使用 SQL 进行集群化，可以使用以下查询来显示正在使用的索引（在 Postgres 版本 9.5 和 9.6 中测试）：

SELECT
  i.relname AS index_for_cluster
FROM
  pg_index AS idx
JOIN
  pg_class AS i
ON
  i.oid = idx.indexrelid
WHERE
  idx.indisclustered
  AND idx.indrelid::regclass = 'your_table_name'::regclass;