postgresql 索引类型

postgresql提供了B-tree,R-tree,GiST和hash索引类型。不同的索引类型适合特定的查询类型。
绝大多数数据库都支持B-tree索引类型，postgresql默认的create index语句也是创建B-tree索引。更具体内容请查看官方中文版文档:http://www.php100.com/manual/PostgreSQL8/

R-tree:
R树是一种用于处理多维数据的数据结构，用来访问二维或者更高维区域对象组成的空间数据.R树是一棵平衡树。树上有两类结点：叶子结点和非叶子结点。每一个结点由若干个索引项构成。对于叶子结点，索引项形如(Index，Obj_ID)。其中，Index表示包围空间数据对象的最小外接矩形MBR，Obj_ID标识一个空间数据对象。对于一个非叶子结点，它的索引项形如(Index，Child_Pointer)。 Child_Pointer 指向该结点的子结点。Index仍指一个矩形区域，该矩形区域包围了子结点上所有索引项MBR的最小矩形区域。
postgresql提供了支持空间存储的数据类型：

Name Storage Size Representation Description
point 16 bytes Point on the plane (x,y)
line 32 bytes Infinite line (not fully implemented) ((x1,y1),(x2,y2))
lseg 32 bytes Finite line segment ((x1,y2))
box 32 bytes Rectangular box ((x1,y2))
path 16+16n bytes Closed path (similar to polygon) ((x1,...)
path 16+16n bytes Open path [(x1,...]
polygon 40+16n bytes Polygon (similar to closed path) ((x1,...)
circle 24 bytes Circle <(x,y),r> (center and radius)

参考文档：http://www.postgresql.org/docs/8.3/static/datatype-geometric.html
在这些数据类型的列上可以创建R-tree类型的索引。举例说明：
wyz=# CREATE TABLE abc (shape polygon);
CREATE
wyz=# CREATE INDEX spacial_idx ON abc USING RTREE (shape);
CREATE

相对R-tree做深一步了解可以看以下Guttman的《R-Trees - A Dynamic Index Structure for Spatial Searching》。

Hash
查看文档说hash类型的索引常用于进行"="操作的数据列。其中也著名hash类型索引相对B-tree类型的索引不管是存储还是性能都比较差，不建议使用hash类型索引。
创建hash类型索引的过程实质是对数据进行hash函数操作，然后存储hash值。从hash函数原理的角度去考虑就能很快发现hash类型索引的劣势在哪了。对在一个OLTP的数据库中采用hash类型索引的表进行DML操作时，不仅增大CPU的开销，也没有节省存储的消耗。


GiST
通用搜索树（Generalized Search Tree），与其说是一种索引类型，不如说是建立索引的平台或者模板。利用Gist可以建立B-tree,R-tree或者其他的类型索引。
对GiST感兴趣可以参考GiST的项目网站http://gist.cs.berkeley.edu/

postgresql的索引创建语法：
CREATE [ UNIQUE ] INDEX indexname ON table
[ USING indextype ] ( functionname ( column [,...] ) [ opclass ] )

转载自：http://www.cnblogs.com/daduxiong/archive/2010/08/18/1802145.html

-------------------------------------------------------------------------------------------------------

附：适用场景浅析

B-tree 适合处理那些能够按顺序存储的数据之上的等于和范围查询。特别是在一个建立了索引的字段涉及到使用

仅当模式是一个常量，并且锚定在 字符串开头的时候，优化器才会把 B-tree 索引用于模式匹配操作符LIKE和~，比如：col LIKE 'foo%'或col ~ '^foo'，但是col LIKE '%bar'就不行。同时，如果你的服务器未使用 C 区域设置，那么你需要用一个特殊的操作符类创建索引来支持模式匹配查询上的索引。 还有可能将 B-tree 索引用于ILIKE和~*，但是仅当模式以非字母字符(不受大小写影响的字符)开头才可以。

Hash 索引只能处理简单的等于比较。当一个索引了的列涉及到使用=操作符进行比较的时候，查询规划器会考虑使用 Hash 索引。下面的命令用于创建 Hash 索引：

CREATE INDEX <tt class="REPLACEABLE"><em>name</em></tt> ON <tt class="REPLACEABLE"><em>table</em></tt> USING hash (<tt class="REPLACEABLE"><em>column</em></tt>);

GiST 索引不是单独一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。因此，可以使用 GiST 索引的特定操作符类型高度依赖于索引策略(操作符类)。作为示例，PostgreSQL 的标准发布中包含用于二维几何数据类型的 GiST 操作符类，它支持

GIN 索引是反转索引，它可以处理包含多个键的值(比如数组)。与 GiST 类似，GIN 支持用户定义的索引策略，可以使用 GIN 索引的特定操作符类型根据索引策略的不同而不同。作为示例，PostgreSQL 的标准发布中包含用于一维数组的 GIN 操作符类，它支持

转载自：http://blog.csdn.net/jdream314/article/details/6770110

---------------------------------------------------------------------------------------------------------------------

数据结构--B 树

B 树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。

B树又叫平衡多路查找树。一棵m阶的B树(m叉树)的特性如下：

1. 树中每个结点最多含有m个孩子（m>=2）；
2. 除根结点和叶子结点外，其它每个结点至少有[ceil(m / 2)]个孩子（其中ceil(x)是一个取上限的函数）；
3. 若根结点不是叶子结点，则至少有2个孩子（特殊情况：没有孩子的根结点，即根结点为叶子结点，整棵树只有一个根节点）；
4. 所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息(可以看做是外部接点或查询失败的接点，实际上这些结点不存在，指向这些结点的指针都为null)；
5. 每个非终端结点中包含有n个关键字信息： (n，P0，K1，P1，K2，P2，......，Kn，Pn)。其中：
   a) Ki (i=1...n)为关键字，且关键字按顺序升序排序K(i-1)< Ki。
   b) Pi为指向子树根的接点，且指针P(i-1)指向子树种所有结点的关键字均小于Ki，但都大于K(i-1)。
   c) 关键字的个数n必须满足： [ceil(m / 2)-1]<= n <= m-1。

来模拟下查找文件29的过程：

(1)根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作1次】

(2)此时内存中有两个文件名17，35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35，因此我们找到指针p2。

(3)根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作2次】

(4)此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30，因此我们找到指针p2。

(5)根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作3次】

(6)此时内存中有两个文件名28，29。根据算法我们查找到文件29，并定位了该文件内存的磁盘地址。

插入操作

生 成从空树开始，逐个插入关键字。但是由于B_树节点关键字必须大于等于[ceil(m/2)-1],所以每次插入一个关键字不是在树中添加一个叶子结点， 而是首先在最底层的某个非终端节点中添加一个“关键字”，该结点的关键字不超过m-1,则插入完成；否则要产生结点的“分裂”，将一半数量的关键字元素分裂到新的其相邻右结点中，中间关键字元素上移到父结点中。

1、咱们通过一个实例来逐步讲解下。插入以下字符字母到一棵空的B树中（非根结点关键字数小了（小于2个）就合并，大了（超过4个）就分裂）：CNGAHEKQMFWLTZDPRXYS，首先，结点空间足够，4个字母插入相同的结点中，如下图：

2、当咱们试着插入H时，结点发现空间不够，以致将其分裂成2个结点，移动中间元素G上移到新的根结点中，在实现过程中，咱们把A和C留在当前结点中，而N放置新的其右邻居结点中。如下图：

3、当咱们插入E,K,Q时，不需要任何分裂操作

4、插入M需要一次分裂，注意M恰好是中间关键字元素，以致向上移到父节点中

5、插入F,W,L,T不需要任何分裂操作

6、插入Z时，最右的叶子结点空间满了，需要进行分裂操作，中间元素T上移到父节点中，注意通过上移中间元素，树最终还是保持平衡，分裂结果的结点存在2个关键字元素。

7、插入D时，导致最左边的叶子结点被分裂，D恰好也是中间元素，上移到父节点中，然后字母P,R,X,Y陆续插入不需要任何分裂操作（别忘了，树中至多5个孩子）。

8、最后，当插入S时，含有N,P,Q,R的结点需要分裂，把中间元素Q上移到父节点中，但是情况来了，父节点中空间已经满了，所以也要进行分裂，将父节点中的中间元素M上移到新形成的根结点中，注意以前在父节点中的第三个指针在修改后包括D和G节点中。这样具体插入操作的完成。

删除操作

首先查找B树中需删除的元素,如果该元素在B树中存在，则将该元素在其结点中进行删除，如果删除该元素后，首先判断该元素是否有左右孩子结点，如果有，则上移孩子结点中的某相近元素到父节点中，然后是移动之后的情况；如果没有，直接删除后，移动之后的情况。

删除元素，移动相应元素之后，如果某结点中元素数目（即关键字数）小于ceil(m/2)-1，则需要看其某相邻兄弟结点是否丰满（结点中元素个数大于ceil(m/2)-1）（还记得第一节中关于B树的第5个特性中的c点么?：c)除根结点之外的结点（包括叶子结点）的关键字的个数n必须满足： （ceil(m / 2)-1）<= n <= m-1。m表示最多含有m个孩子，n表示关键字数。在本小节中举的一颗B树的示例中，关键字数n满足：2<=n<=4），如果丰满，则向父节点借一个元素来满足条件；如果其相邻兄弟都刚脱贫，即借了之后其结点数目小于ceil(m/2)-1，则该结点与其相邻的某一兄弟结点进行“合并”成一个结点，以此来满足条件。那咱们通过下面实例来详细了解吧。

以上述插入操作构造的一棵5阶B树（树中最多含有m（m=5）个孩子，因此关键字数最小为ceil(m / 2)-1=2。还是这句话，关键字数小了（小于2个）就合并，大了（超过4个）就分裂）为例，依次删除H,T,E。

1、首先删除元素，当然首先查找，H在一个叶子结点中，且该叶子结点元素数目3大于最小元素数目ceil(m/2)-1=2，则操作很简单，咱们只需要移动K至原来的位置，移动L至的位置（也就是结点中删除元素后面的元素向前移动）

2、下一步，删除T,因为T没有在叶子结点中，而是在中间结点中找到，咱们发现他的继承者W(字母升序的下个元素)，将W上移到T的位置，然后将原包含W的孩子结点中的进行删除，这里恰好删除后，该孩子结点中元素个数大于2，无需进行合并操作。

3、下一步删除R，R在叶子结点中,0)">但是该结点中元素数目为2，删除导致只有1个元素，已经小于最小元素数目ceil(5/2)-1=2,而由前面我们已经知道：如果其某个相邻兄弟结点中比较丰满（元素个数大于ceil(5/2)-1=2），则可以向父结点借一个元素，然后将最丰满的相邻兄弟结点中上移最后或最前一个元素到父节点中（有没有看到红黑树中左旋操作的影子?），在这个实例中，右相邻兄弟结点中比较丰满（3个元素大于2），所以先向父节点借一个元素W下移到该叶子结点中，代替原来S的位置，前移；然后X在相邻右兄弟结点中上移到父结点中，最后在相邻右兄弟结点中删除X，后面元素前移。

4、最后一步删除E，删除后会导致很多问题，因为E所在的结点数目刚好达标，刚好满足最小元素个数（ceil(5/2)-1=2）而相邻的兄弟结点也是同样的情况，删除一个元素都不能满足条件，所以需要该节点与某相邻兄弟结点进行合并操作；首先移动父结点中的元素（该元素在两个需要合并的两个结点元素之间）下移到其子结点中，然后将这两个结点进行合并成一个结点。所以在该实例中，咱们首先将父节点中的元素D下移到已经删除而只有F的结点中，然后将含有的结点和含有A,C的相邻兄弟结点进行合并成一个结点。

5、也许你认为这样删除操作已经结束了，其实不然，在看看上图，对于这种特殊情况，你立即会发现父节点只包含一个元素G，没达标（因为非根节点包括叶子结点的关键字数n必须满足于2=<n<=4，而此处的n=1），这是不能够接受的。如果这个问题结点的相邻兄弟比较丰满，则可以向父结点借一个元素。假设这时右兄弟结点（含有Q,X）有一个以上的元素（Q右边还有元素），然后咱们将M下移到元素很少的子结点中，将Q上移到M的位置，这时，Q的左子树将变成M的右子树，也就是含有NP结点被依附在M的右指针上。所以在这个实例中，咱们没有办法去借一个元素，只能与兄弟结点进行合并成一个结点，而根结点中的唯一元素下移到子结点，这样，树的高度减少一层。

为了进一步详细讨论删除的情况，再举另外一个实例：

这里是一棵不同的5序B树，那咱们试着删除C

于是将删除元素C的右子结点中的D元素上移到C的位置，但是出现上移元素后，只有一个元素的结点的情况。

又因为含有E的结点，其相邻兄弟结点才刚脱贫（最少元素个数为2），不可能向父节点借元素，所以只能进行合并操作，于是这里将含有A,B的左兄弟结点和含有E的结点进行合并成一个结点。

这样又出现只含有一个元素F结点的情况，这时，其相邻的兄弟结点是丰满的（元素个数为3>最小元素个数2），这样就可以想父结点借元素了，把父结点中的J下移到该结点中，相应的如果结点中J后有元素则前移，然后相邻兄弟结点中的第一个元素（或者最后一个元素）上移到父节点中，后面的元素（或者前面的元素）前移（或者后移）；注意含有K，L的结点以前依附在M的左边，现在变为依附在J的右边。这样每个结点都满足B树结构性质。

从以上操作可看出：除根结点之外的结点（包括叶子结点）的关键字的个数n满足：（ceil(m / 2)-1）<= n <= m-1，即2<=n<=4。这也佐证了咱们之前的观点。删除操作完。

在B_树中关键字分布在整个B_树，并且在上层结点中出现过的关键字不再出现在最底层的结点中。顺序链中所有的关键字不能连接在一起。

一颗m阶的B+树和m阶的B_树的差异在于：

1.有n棵子树的结点中含有n个关键字；(而B树是n棵子树有n-1个关键字)

2.所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。(而B树的叶子节点并没有包括全部需要查找的信息)

3.所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。(而B 树的非终节点也包含需要查找的有效信息)

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

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

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

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!