Postgresql、MySQL相关的四种索引类型：B-Tree，Hash，Gist，GIN

2019独角兽企业重金招聘Python工程师标准>>>

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

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的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<M+1；且K[i]< K[i+1] ；

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+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。

a) 为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？

1) B+-tree的磁盘读写代价更低

B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

2) B+-tree的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

读者点评

数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

  看完索引的整体逻辑过程（创建，插入，扫描，删除）感觉整体性很强，都是有一个总流程，之后函数+数据结构实现相关模块，逻辑性较强，涉及函数较多。

  创建索引： 将待索引的表元组封装为索引元组，并进行排序，之后填充入叶子节点，填充满则申请新的叶子节点，并在父节点中添加指针，重复该过程。

  插入过程中，主要涉及查找及可能进行的叶子节点分裂。其中，为了减少分裂操作，设置一个百分比填充因子（留下冗余量），虽然带来一定空间浪费，但可以避免过于频繁的分裂。

Hash索引

   Hash相对比较熟悉。在hash表中，有一个函数以查找键为参数，计算出0~b-1（b是桶的数目）

   B的数目是否变化，分为静态与动态。PostgreSQL使用动态hash（可扩展/线性）

实现中包含两种类型hash，一个是用作索引的外存，另一种用于内存查找

元页记录相关信息，每次对索引读写操作都要将元页读入内存

Hash表多个桶组成，每个桶含一或多个页，每个桶第一页称为桶页，其他页称为溢出页。桶页成组分配，每次分配2的幂次。0，1号初始化分配。每次分配的桶页磁盘上连续存储。溢出页分配在每次分配整体后。（例：4~7桶中，4满了，溢出页加在7后）溢出页和桶页之间双向链表，溢出页一旦分配，一直存在，不会释放物理空间（为了快速计算桶标号）

位图用于标记每个溢出页是否可用

下面举例说明hash表不断插入时各种页面分配情况：

①初始化。0号块元页，1,2号块分配给0,1号桶，并在桶后分配位图页（3号块）

②增加两个溢出页。磁盘号为45.

 

③增加一个桶。增加2号桶需要分裂，其实增加了2,3号桶，但3号先不投入使用。

④为2号桶增加溢出页

 

Hash的实现：构建，元组插入，溢出页管理，hash表扩展

构建：初始化，之后扫描要索引的表，生成索引元组，最后插入到hash

溢出页的分配回收需要考虑共享问题，设置排他锁

Hash表扩展，每次插入，计算当前记录r/桶数n，比例太大就要进行扩展和压缩。

 

GiST

通用搜索树，是一种平衡的树状结构访问方法，相当于一个基础模板，可以用它实现任意索引模式。它可以建立一种可扩展的索引结构，包括数据类型和查询谓词扩展。Gist接口提供了高层抽象，只要求实现基本语义，而不用理解数据库内部工作机制。

可以根据相关领域设定数据或谓词，多用于图像处理

 

GIN

通用倒排索引，是一个存储（key，posting list）集合的索引结构，key是键值，posting list是出现过key的位置，位置由“元组ID：位置”表示 如（”hello”,”14:17,…”）表示hello关键字在14号元组被索引属性第17个位置出现。

通过这种结构可快速查找包含关键字的元组，特别适合支持全文搜索。

GIN包含四部分：

Entry：一个词位/键值

Entry tree：在entry上构建的b树

Posting tree：在一个entry出现的物理位置上构建的b树

Postling list：entry物理位置列表

 

GIN索引适合静态的数据因为查找是迅速的. 对于动态数据, GiST 可以更快的更新. 具体来说, GiST索引在动态数据上是好用的并且如果单独的字（词位）在100,000以下也是快速的,然而GIN 索引在处理100,000词位以上时是更好的但是更新就要慢点了.

 

TSearch2 全文搜索

全文检索是指计算机索引程序通过扫描文章中的每一个词，对每一个词建立一个索引，指明该词在文章中出现的次数和位置，当用户查询时，检索程序就根据事先建立的索引进行查找，并将查找的结果反馈给用户的检索方式。这个过程类似于通过字典中的检索字表查字的过程。提供了可检索出满足某条件自然语言文档的能力，并可根据相关性排序

步骤：1）对文本预处理，得到结果，创建索引

      2）解析查询条件，使用索引查询

      3）得到结果，处理返回

预处理：文本解析，语义分析，词位存储

 

扩展：

（一）B-Tree索引和Hash索引的区别 

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

 

可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

 

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

 

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

 

（2）Hash 索引无法被用来避免数据的排序操作。

 

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

 

（3）Hash 索引不能利用部分索引键查询。

 

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

 

（4）Hash 索引在任何时候都不能避免表扫描。

 

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

 

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

 

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

 

hash相当于把key通过hash函数计算，得到key的hash值,再用这个hash值做指针，查找hash表中是否存在key，如果存在就返回 key所对应的value，选定一个好的hash函数很重要，好的hash函数可以使计算出的hash值分布均匀，降低冲突，只有冲突减小了，才会降低 hash表的查找时间。

 

b-tree完全基于key的比较，和二叉树相同的道理，相当于建个排序后的数据集，使用二分法查找算法，实际上也非常快，而且受数据量增长影响非常小。

（二）mysql索引

索引策略决定数据库快速定位数据的效率，存储策略决定数据持久化的效率。

MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略，本文将分析它们的异同和性能。

 

MySQL主要提供2种方式的索引：B-Tree（包括B+Tree）索引，Hash索引。

B树索引具有范围查找和前缀查找的能力，对于N节点的B树，检索一条记录的复杂度为O(LogN)。

哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。

显然，如果值的差异性大，并且以等于查找为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

MyISAM索引实现

 

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

 

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

 

InnoDB索引实现

 

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

 

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

 

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

 

实际上，您可以把索引理解为一种特殊的目录。微软的SQL   SERVER提供了两种索引：聚集索引（clustered   index，也称聚类索引、簇集索引）和非聚集索引（nonclustered   index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：  

   

  其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。  

   

  我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。  

   

  如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。  

   

  我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。 

转载于:https://my.oschina.net/wangchuandui/blog/1504260