postgresql中二级索引的定义是什么？

本文介绍了postgresql中二级索引的定义是什么？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 来自 https：//www.postgresql。 org / docs / 9.6 / static / indexes-index-only-scans.html ：在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 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 将它们定义为：辅助索引与主索引的区别在于，辅助索引不能确定数据文件中记录的位置。相反，二级索引告诉我们记录的当前位置。该位置可能是由其他字段上的主索引决定的。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 indicesthere can be at most 1 primary index per tableprimary 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将此称为由索引组织的表Mysql's InnoDB calls this clustered indexMSSQL also refer to the primay index as a clustered indexOracle calls this index-organized tables Postgres文档中的语言不准确。The language in the Postgres Documentation is imprecise.这是真的。这不是为什么所有索引在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中二级索引的定义是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！