postgresql中二级索引的定义是什么？ [英] What is the definition of secondary index in postgresql?

问题描述

来自 https：//www.postgresql。 org / docs / 9.6 / static / indexes-index-only-scans.html ：

PostgreSQL中的所有索引都是次要的索引，表示每个索引都与表的主数据区分开存储（在PostgreSQL术语中称为表的堆）。

All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (which is called the table's heap in PostgreSQL terminology).

在PostgreSQL中，辅助索引是否定义为与表的主数据区分开存储的索引？

In postgresql, is a secondary index defined as an index which is stored separately from the table's main data area?

如果没有，它的定义是什么，为什么引号中提到的不是定义？

If not, what is its definition, and why does the quote mention the one which is not a definition?

类似地，什么

postgresql中的概念与 Oracle数据库？

Are the concepts in postgresql the same as in Oracle database?

谢谢。

推荐答案

primary&的定义中缺少一些精确度。二级索引。

There is some lack of precision in the definitions of primary & secondary indices.

使用两种流行的大学文字作为参考：

Using two popular university texts as reference:

数据库系统基础，Elmasri& Navathe 将它们定义为：

主索引作为有序文件上的索引，其中搜索键与排序键

A primary index as an index on an ordered file where the search key is the same as the sort key

二级索引提供了一种二级方法来访问已经存在某些一级访问权限的数据文件。数据文件记录可以有序，无序或散列。

A secondary index provides a secondary means of accessing a data file for which some primary access already exists. The data file records could be ordered, unordered, or hashed.

数据库系统：完整书，Garcia-Molina等。 al 将它们定义为：

主索引确定数据文件记录的位置

A primary index determines the location of the records of the data file

辅助索引与主索引的区别在于，辅助索引不能确定数据文件中记录的位置。相反，二级索引告诉我们记录的当前位置。该位置可能是由其他字段上的主索引决定的。

The secondary index is distinguished from the primary index in that a secondary index does not determine the placement of records in the data file. Rather, the secondary index tells us the current locations of records; that location may have been decided by a primary index on some other field

某些属性对于以上两个定义均成立：

Some properties that hold true for either definition above:

• 主键可以是主索引


• 每个表最多可以有1个主索引


• 主索引唯一确定记录在物理存储中的保留位置。


• 所有其他索引都归为二级。

• primary keys can be primary indices
• there can be at most 1 primary index per table
• primary indices uniquely determine where a record is kept in physical storage.
• All other indices are classified as secondary.

但是，如果将记录放置在数据文件不是由任何字段确定的，因此无法构造主索引。

However, if the placement of records in the data file is not determined by any field, then a primary index cannot be constructed.

因此，对于已排序的文件，谈论主索引（这是排序所基于的字段列表）是有意义的。我找不到其他可以构造主索引的物理文件结构示例。

Thus for sorted files, it makes sense to talk about the primary index (which would be the list of fields upon which the sorting is based). I can't find other examples of physical file structures where a primary index can be constructed.

Postgresql使用堆结构存储记录。堆没有排序（双关语：它们是排序的）。因此，即使主键也是使用辅助索引实现的，因此Postgresql中的所有索引都是辅助索引的。

Postgresql utilizes a heap structure for the physical storage for records. Heaps are not sorted (pun alert: they're sorta sorted). Therefore, even the primary keys are implemented using secondary indices, and as such all indices in Postgresql are secondary.

其他RDBMS系统 do 实现存储支持主索引的格式：

Other RDBMS Systems do implement storage formats that support primary indices:

• Mysql的InnoDB将此称为集群索引


• MSSQL也将primay索引称为聚集索引


• Oracle将此称为< a href = https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721 rel = noreferrer>由索引组织的表

• Mysql's InnoDB calls this clustered index
• MSSQL also refer to the primay index as a clustered index
• Oracle calls this index-organized tables

Postgres文档中的语言不准确。

The language in the Postgres Documentation is imprecise.

PostgreSQL中的所有索引都是二级索引

All indexes in PostgreSQL are secondary indexes

这是真的。

意味着每个索引都与表的主数据区分开存储

meaning that each index is stored separately from the table's main data area

这不是为什么所有索引在Postgresql中都是次要的。主索引也可以与表的主数据区分开存储。

This is not why all indices are secondary in Postgresql. Primary indices may also be stored separately from the table's main data area.

这篇关于postgresql中二级索引的定义是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！