PostgreSQL索引

什么是索引？

索引的目的及优缺点？

PostgreSQL中的索引及分类

索引是指按表中某些关键属性或表达式建立元组的逻辑顺序，它是由一系列表元组的标识号组成的一个列表。

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引的分类

按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“分聚集索引（非聚类索引，非簇集索引）”

按数据唯一性区分：“唯一索引”，“非唯一索引”

按键列个数区分：“单列索引”，“多列索引”。

为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

PostgreSQL中五种索引方式：唯一，主键，多属性，部分，表达式

四种索引类型：B-Tree，Hash，Gist，GIN

CREATE INDEX用于创建索引，默认创建一个B-Tree

索引相关系统表：

每种索引类型在系统表pg_am中用一个元组记录，共4个元组对应类型，其中记录了可被调用的接口函数和相关属性，共有13种函数类型。

每一个创建的索引，会在pg_class系统表中添加一个元组，同时在pg_index中添加一个元组。Pg_index用于记录与索引有关的信息。

每一种索引类型并不直接设定其所操作的数据类型，而是由操作符系统表pg_opclass 管理，而每一个操作符类引用了pg_opfamily一个元组

Pg_amop中存每个索引操作符集合(pg_opfamily)与具体操作符（pg_operator）关联信息Pg_amproc则存储集合与其相关联的支持过程或函数（pg_proc）关联信息

例：Pg_proc中一个函数属于pg_opfamily一个集合，则pg_amproc中就有一个元组记录这个对应关系。

B-Tree

M为树的阶数，B-树或为空树，否则满足下列条件：

1.定义任意非叶子结点最多只有M个儿子；且M>2；

2.根结点的儿子数为[2, M]；

3.除根结点以外的非叶子结点的儿子数为[M/2, M]；

4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字,根节点至少一个关键字）；

5.非叶子结点的关键字个数=指向儿子的指针个数-1；

6.非叶子结点的关键字：K[1], K[2], …, K[m-1]，m

7.非叶子结点的指针：P[1], P[2], …, P[m]；其中P[1]指向关键字小于K[1]的子树，P[m]指向关键字大于K[m-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

8.所有叶子结点位于同一层；

如：（M=3）

M=3的B-树

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点。

特性

1.关键字集合分布在整颗树中；

2.任何一个关键字出现且只出现在一个结点中；

3.搜索有可能在非叶子结点结束；

4.其搜索性能等价于在关键字全集内做一次二分查找；

大规模数据存储中，实现索引查询这样一个实际背景下，树节点存储的元素数量是有限的（如果元素数量非常多的话，查找就退化成节点内部的线性查找了），这样导致二叉查找树结构由于树的深度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下，那么如何减少树的深度（当然是不能减少查询的数据量），一个基本的想法就是：采用多叉树结构（由于树节点元素数量是有限的，自然该节点的子树数量也就是有限的）。

B 树是为了磁盘或其它存储设备而设计的一种多叉平衡查找树

B+树的定义

B+树是应文件系统所需而出的一种B-树的变型树。一棵m阶的B+树和m阶的B-树的差异在于：

1.有n棵子树的结点中含有n个关键字，每个关键字不保存数据，只用来索引，所有数据都保存在叶子节点。

2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的非终端结点可以看成是索引部分，结点中仅含其子树（根结点）中的最大（或最小）关键字。

通常在B+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。